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
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:
The CASE WHEN expression used above now has a possible NULL outcome, so the CTAS result column is created NULL.
Model:
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.