Nested CTEs fail on dbt-fabric versions >= 1.9.2

[matthewpaul-us]

There is an issue around CTEs that popped up around dbt-fabric 1.9.2. The issue is that the DBT adapter tries to wrap CTEs in another CTE when running it, which fails because Microsoft has a known limitation around nested CTEs. See documentation here: https://learn.microsoft.com/en-us/sql/t-sql/queries/nested-common-table-expression?view=sql-server-ver17.

This model works in older versions, including dbt-fabric 1.9.0.

MVP:

Create a model with the following SQL. Add a yaml file that enforces the contract.

test_nested_cte.sql

WITH block1
AS (SELECT 1 AS column_a),
     block2
AS (SELECT 1 AS column_b),
     block3
AS (SELECT *
    FROM block2 b2
    WHERE NOT EXISTS
    (
        SELECT 1 FROM block1 b1 WHERE b2.column_b = b1.column_a
    )
 )
SELECT *
FROM block3

test_nested_cte.yml

models:
- name: test_nested_cte
  description: Test model demonstrating nested CTE functionality with EXISTS clause
  config:
    unique_key: column_b
    contract:
      enforced: true
    tags:
      - test
      - silver layer
  columns:
  - name: column_b
    data_type: int
    description: Column B from block2, filtered by NOT EXISTS condition against block1
    data_tests:
    - not_null

Run the model with dbt-fabric >= 1.9.2. It should throw an error like the following:
('42S02', "[42S02] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'block1'. (208) (SQLExecDirectW)")

Expected
The model should be created without issue

[pradeepsrikakolapu]

@matthewpaul-us , we have to wrap the cte with another cte to handle all edge cases. without this, the schema dry run is not possible before materialization. The final materialization itself is not problem, but the schema dry run is.

I tried to handle as many scenarios as possible but trying to parse and rewrite SQL to support all cases is not possible without full nested CTE support from engine.