{"record":{"id":"2957053f8817d786","repo":"dbt-labs/dbt-core","slug":"nested-cte-warning-nested-cte-s-do-not-support-ct","errorCode":null,"errorMessage":"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.","messagePattern":"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\\.","errorType":"console","errorClass":null,"httpStatus":null,"severity":"warning","filePath":"crates/dbt-loader/src/dbt_macro_assets/dbt-fabric/macros/materializations/models/unit_test/unit_test_create_table_as.sql","lineNumber":21,"sourceCode":"        {% set cleaned_sql = sql | lower | replace(\"\\n\", \" \") %}  {# Convert to lowercase and remove newlines #}\n        {% set cte_count = cleaned_sql.count(\"with \") %}  {# Count occurrences of \"WITH \" #}\n        {% if cte_count > 1 %}\n            {{ return(True) }}\n        {% else %}\n            {{ return(False) }}  {# No nested CTEs found #}\n        {% endif %}\n    {% else %}\n        {{ return(False) }}  {# Return False during parsing #}\n    {% endif %}\n{% endmacro %}\n\n{% macro fabric__get_create_table_as_sql(temporary, relation, sql) -%}\n    {% set query_label = apply_label() %}\n    {% set contract_config = config.get('contract') %}\n    {% set is_nested_cte = check_for_nested_cte(sql) %}\n\n    {% if is_nested_cte %}\n        {{ exceptions.warn(\n            \"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.\"\n        ) }}\n    {% endif %}\n\n    {% if is_nested_cte and contract_config.enforced %}\n\n        {{ exceptions.raise_compiler_error(\n            \"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.\"\n        ) }}\n\n    {%- elif not is_nested_cte and contract_config.enforced %}\n\n        CREATE TABLE {{relation}}\n        {{ build_columns_constraints(relation) }}\n        {{ get_assert_columns_equivalent(sql)  }}\n\n        {% set listColumns %}\n            {% for column in model['columns'] %}","sourceCodeStart":3,"sourceCodeEnd":39,"githubUrl":"https://github.com/dbt-labs/dbt-core/blob/0267ce9170576975b76b64ce856b2e5848e96617/crates/dbt-loader/src/dbt_macro_assets/dbt-fabric/macros/materializations/models/unit_test/unit_test_create_table_as.sql#L3-L39","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["Rewrite the model SQL to inline the inner CTE or use temp objects instead of nested CTEs","Split the model into two models so each has a flat CTE chain","If nesting is 2-level only and it works today, acknowledge the warning and pin the adapter version, planning a rewrite","Validate compiled SQL against Fabric after adapter upgrades"],"exampleFix":"-- before\nwith a as (select 1 as x),\nb as (select * from a),\nc as (select * from b)\nselect * from c\n-- after (flat)\nwith a as (select 1 as x),\nb as (select * from a)\nselect * from b","handlingStrategy":"validation","validationCode":"# detect nested CTEs (a CTE referencing another CTE) before compiling for Fabric\ndef has_nested_cte(sql):\n    import re\n    ctes = re.findall(r'with\\s+([a-zA-Z_]\\w*)\\s+as\\s*\\(', sql, re.I)\n    body = re.sub(r'with.*?(?=(select|insert))', '', sql, flags=re.S | re.I)\n    return any(re.search(rf'\\b{c}\\b', body) for c in ctes)","typeGuard":null,"tryCatchPattern":null,"preventionTips":["Keep model SQL to flat, single-level CTE chains on Fabric","Inline small CTEs or split models instead of nesting","Pin dbt-fabric versions and retest nested-CTE models on upgrades","Treat this warning as tech debt to remove before the adapter fix lands"],"tags":["fabric","cte","ctas","sql"],"backgroundTag":"sql-query-failed","analyzedSha":"0267ce9170576975b76b64ce856b2e5848e96617","analyzedAt":"2026-09-07T21:53:39.732Z","contentChangedAt":"2026-09-07T21:53:39.732Z","schemaVersion":2},"datasetVersion":"2026-09-17T15:17:12.973Z"}