Forum Discussion
T-SQL: CTAS with all nullable columns?
Hello sam51 you're right, this is default behavior - CTAS infers nullability from the expression of each selected column.
Workaround:
Since you mentinoed you use dbt you can use macros to get around the issue.
Macro:
-- macros/nullable.sql{% macro nullable(expr, sql_type) -%}CASE WHEN 1 = 0 THEN CAST(NULL AS {{ sql_type }}) ELSE {{ expr }} END{%- endmacro %}The CASE WHEN expression used above now has a possible NULL outcome, so the CTAS result column is created NULL.
Model:
select{{ nullable('src.order_id', 'bigint') }} as order_id,{{ nullable('src.customer_name', 'varchar(200)') }} as customer_name,{{ nullable('src.order_date', 'datetime2(3)') }} as order_datefrom {{ ref('stg_orders') }} as srcsam51 I look at your challenge from a different angle. My first question would be: what is your primary goal in your approach using CTAS: to land data in a table exactly in the same shape as it's origin or create a table with a precise schema that you need and also conveniently populate it with the data in one step? In my view, those are two different goals.
If your goal is a table with a predefined schema, then CTAS is simply not the right tool.
Fabric Warehouse’s CTAS behavior is not a limitation—it’s a predictable outcome of a pattern where the schema is inferred from the result set, as is common in many modern cloud engines (Synapse, Snowflake, BigQuery, Spark SQL, etc.).
So, an alternative, when the correct schema is a requirement, is to create a table first and then use INSERT/SELECT to populate the table instead of CTAS. Your experience looks to me as an outcome of an attempt to simplify the solution without enforcing the requirements.
Just my 2 cents, hope it could be a food for thoughts.CTAS infers nullability from the data. To make all columns nullable:
- Option 1: Create the table first with all columns defined as NULL, then INSERT SELECT the data.
- Option 2 (dbt): Wrap each column in a macro or CASE that allows NULL, e.g.:
CASE WHEN 1 = 0 THEN CAST(NULL AS INT) ELSE src.col1 END AS col1
This ensures CTAS creates the columns as nullable.
In microsoft fabric that is known behaviour
so you have two options ,you need to define null explicity
Instead using this
CREATE TABLE my_table AS
SELECT
customer_id,
amount,
order_date
FROM source_table;
use
CREATE TABLE my_table AS
SELECT
CAST(customer_id AS INT) AS customer_id,
CAST(amount AS DECIMAL(18,2)) AS amount,
CAST(order_date AS DATETIME2) AS order_date
FROM source_table;When you use CAST, Fabric treats the expression as nullable by default, unless explicitly constrained.
Option # 2
CREATE TABLE my_table AS
SELECT
CASE WHEN 1=1 THEN customer_id ELSE NULL END AS customer_id,
amount + NULL AS amount
FROM source_table;Because SQL now sees a possible NULL path, so it must create the column as nullable.
if this helps mark as kudos 👍 | Mark as solution | Help Others
7 Replies
Hello sam51 you're right, this is default behavior - CTAS infers nullability from the expression of each selected column.
Workaround:
Since you mentinoed you use dbt you can use macros to get around the issue.
Macro:
-- macros/nullable.sql{% macro nullable(expr, sql_type) -%}CASE WHEN 1 = 0 THEN CAST(NULL AS {{ sql_type }}) ELSE {{ expr }} END{%- endmacro %}The CASE WHEN expression used above now has a possible NULL outcome, so the CTAS result column is created NULL.
Model:
select{{ nullable('src.order_id', 'bigint') }} as order_id,{{ nullable('src.customer_name', 'varchar(200)') }} as customer_name,{{ nullable('src.order_date', 'datetime2(3)') }} as order_datefrom {{ ref('stg_orders') }} as srcCTAS infers nullability from the data. To make all columns nullable:
- Option 1: Create the table first with all columns defined as NULL, then INSERT SELECT the data.
- Option 2 (dbt): Wrap each column in a macro or CASE that allows NULL, e.g.:
CASE WHEN 1 = 0 THEN CAST(NULL AS INT) ELSE src.col1 END AS col1
This ensures CTAS creates the columns as nullable.
sam51 I look at your challenge from a different angle. My first question would be: what is your primary goal in your approach using CTAS: to land data in a table exactly in the same shape as it's origin or create a table with a precise schema that you need and also conveniently populate it with the data in one step? In my view, those are two different goals.
If your goal is a table with a predefined schema, then CTAS is simply not the right tool.
Fabric Warehouse’s CTAS behavior is not a limitation—it’s a predictable outcome of a pattern where the schema is inferred from the result set, as is common in many modern cloud engines (Synapse, Snowflake, BigQuery, Spark SQL, etc.).
So, an alternative, when the correct schema is a requirement, is to create a table first and then use INSERT/SELECT to populate the table instead of CTAS. Your experience looks to me as an outcome of an attempt to simplify the solution without enforcing the requirements.
Just my 2 cents, hope it could be a food for thoughts.To ensure all columns are nullable in a T-SQL CTAS in Fabric DW, you can’t alter the table afterward. Two main approaches:
- Create table first, then insert
CREATE TABLE my_table ( col1 INT NULL, col2 VARCHAR(100) NULL, col3 DATETIME NULL ); INSERT INTO my_table SELECT col1, col2, col3 FROM source_table;- Use a macro or CASE trick in CTAS to force nullability (works with dbt):
CASE WHEN 1 = 0 THEN CAST(NULL AS INT) ELSE src.col1 END AS col1
This way, CTAS sees a possible NULL and creates the column as nullable.
Basically, CTAS infers nullability; adding a “possible NULL” ensures all columns become nullable.
Hi sam51,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community supportIn microsoft fabric that is known behaviour
so you have two options ,you need to define null explicity
Instead using this
CREATE TABLE my_table AS
SELECT
customer_id,
amount,
order_date
FROM source_table;
use
CREATE TABLE my_table AS
SELECT
CAST(customer_id AS INT) AS customer_id,
CAST(amount AS DECIMAL(18,2)) AS amount,
CAST(order_date AS DATETIME2) AS order_date
FROM source_table;When you use CAST, Fabric treats the expression as nullable by default, unless explicitly constrained.
Option # 2
CREATE TABLE my_table AS
SELECT
CASE WHEN 1=1 THEN customer_id ELSE NULL END AS customer_id,
amount + NULL AS amount
FROM source_table;Because SQL now sees a possible NULL path, so it must create the column as nullable.
if this helps mark as kudos 👍 | Mark as solution | Help Others
Hi @sam51,
We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.
Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support
