dbt-labs/dbt-core · warning

Nested CTE warning: Nested CTE's do not support CTAS…

Error message

Nested CTE warning: Nested CTE's do not support CTAS. However, 2-level nested CTEs are supported due to a code bug. Please expect this fix in the future.

What it means

In the Fabric adapter's CTAS materialization, `check_for_nested_cte(sql)` detects multi-level nested CTEs, which are not officially supported with CTAS on Fabric. dbt warns that only 2-level nested CTEs work today (due to a code quirk) and that a fix is coming. It signals your model SQL uses a construct that may break in future adapter versions.

Solutions

1. Rewrite the model SQL to inline the inner CTE or use temp objects instead of nested CTEs
2. Split the model into two models so each has a flat CTE chain
3. If nesting is 2-level only and it works today, acknowledge the warning and pin the adapter version, planning a rewrite
4. Validate compiled SQL against Fabric after adapter upgrades

Example fix

-- before
with a as (select 1 as x),
b as (select * from a),
c as (select * from b)
select * from c
-- after (flat)
with a as (select 1 as x),
b as (select * from a)
select * from b

# detect nested CTEs (a CTE referencing another CTE) before compiling for Fabric
def has_nested_cte(sql):
    import re
    ctes = re.findall(r'with\s+([a-zA-Z_]\w*)\s+as\s*\(', sql, re.I)
    body = re.sub(r'with.*?(?=(select|insert))', '', sql, flags=re.S | re.I)
    return any(re.search(rf'\b{c}\b', body) for c in ctes)

When it happens

Trigger: A Fabric model whose compiled SQL contains a CTE that references another CTE (nested), compiled through `fabric__get_create_table_as_sql`; the warning fires during model materialization whenever `is_nested_cte` is true.

Common situations: Large refactor-heavy models where a CTE selects from an earlier CTE; dbt utilities/macros generating layered CTE SQL; upgrading Fabric DW runtimes where the 2-level loophole gets closed.

Understand the failure class

Background: "query failed", "%w: SQL error" — wrapped database query errors in Go libraries explained — this error's family across 3 libraries.

Related errors

• clean_sql
• Detected columns with numeric type and unspecified…
• events_last_year should compile without error
• invalid return value
• no information-schema table named

AI-assisted analysis of dbt-labs/dbt-core@0267ce9170 (2026-09-07). Data as JSON: /api/errors/2957053f8817d786. Report an issue: GitHub.

Appendix: source

Thrown at crates/dbt-loader/src/dbt_macro_assets/dbt-fabric/macros/materializations/models/unit_test/unit_test_create_table_as.sql:21

        {% set cleaned_sql = sql | lower | replace("\n", " ") %}  {# Convert to lowercase and remove newlines #}
        {% set cte_count = cleaned_sql.count("with ") %}  {# Count occurrences of "WITH " #}
        {% if cte_count > 1 %}
            {{ return(True) }}
        {% else %}
            {{ return(False) }}  {# No nested CTEs found #}
        {% endif %}
    {% else %}
        {{ return(False) }}  {# Return False during parsing #}
    {% endif %}
{% endmacro %}

{% macro fabric__get_create_table_as_sql(temporary, relation, sql) -%}
    {% set query_label = apply_label() %}
    {% set contract_config = config.get('contract') %}
    {% set is_nested_cte = check_for_nested_cte(sql) %}

    {% if is_nested_cte %}
        {{ exceptions.warn(
            "Nested CTE warning: Nested CTE's do not support CTAS. However, 2-level nested CTEs are supported due to a code bug. Please expect this fix in the future."
        ) }}
    {% endif %}

    {% if is_nested_cte and contract_config.enforced %}

        {{ exceptions.raise_compiler_error(
            "Unit test Materialization error: Since the contract is enforced and the model contains a nested CTE, unit tests cannot be materialized. Please refactor your model or unenforce model and try again."
        ) }}

    {%- elif not is_nested_cte and contract_config.enforced %}

        CREATE TABLE {{relation}}
        {{ build_columns_constraints(relation) }}
        {{ get_assert_columns_equivalent(sql)  }}

        {% set listColumns %}
            {% for column in model['columns'] %}

View on GitHub (pinned to 0267ce9170)