T-SQL: CTAS with all nullable columns?

[sam51]

I have a question for the T-SQL experts amongst you:

We use Create Table As Select very intensively in our data transformations (dbt - but irrelevant for the question). Apparently, when you run this with columns that are not null in the source or with expressions that would logically never become null, the column type becomes a non-nullable column.

Fabric DW does not support alter table alter column to change those columns to nullable columns.

What could I do to make sure that all columns in my table are always nullable?

[deborshi_nag · Accepted solution]

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_date

from {{ ref('stg_orders') }} as src

[apturlov · Accepted solution]

@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.

[trillionaires · Accepted solution]

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.

[v-prasare]

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

[Murtaza_Ghafoor · Accepted solution]

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

[v-prasare]

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

[trillionaires]

To ensure all columns are nullable in a T-SQL CTAS in Fabric DW, you can’t alter the table afterward. Two main approaches:

1. 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;

1. 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.

[apturlov · Accepted solution]

@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.

[deborshi_nag · Accepted solution]

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_date

from {{ ref('stg_orders') }} as src

[trillionaires · Accepted solution]

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.