dbt test creating nested WITH clause throwing error

[ashrodan]

When doing a simple dbt test aka 1.8 unit test the test it creates is creating a nested WITH statement. In the dbt_internal_unit_test_actual its building another WITH statement. I think this usually worksin other databases but Fabric t-SQL its not supported.

dbt-core: 1.8 dbt-fabric: 1.8.6

Error:

13:44:16.668701 [debug] [Thread-1 (]: On unit_test.dbt_fabric_poc.slv_country_first_letter.test_is_valid_first_letter: /* {"app": "dbt", "dbt_version": "1.8.1", "profile_name": "dbt_fabric_poc", "target_name": "fabric-dev", "node_id": "unit_test.dbt_fabric_poc.slv_country_first_letter.test_is_valid_first_letter"} */
-- Build actual result given inputs
with dbt_internal_unit_test_actual as (
  select
    country_region,first_letter, 'actual' as "actual_or_expected"
  from (
    with __dbt__cte__src_fabric_poc_bing_covid_data as (

-- Fixture for src_fabric_poc_bing_covid_data
select try_cast(null as int) as id, try_cast(null as date) as updated, try_cast(null as int) as confirmed, try_cast(null as bigint) as confirmed_int, try_cast(null as int) as confirmed_change, try_cast(null as int) as deaths, try_cast(null as smallint) as deaths_change, try_cast(null as int) as recovered, try_cast(null as int) as recovered_change, try_cast(null as float) as latitude, try_cast(null as float) as longitude, try_cast(null as varchar(8000)) as iso2, try_cast(null as varchar(8000)) as iso3, 
    try_cast('Australia' as varchar(8000))
 as country_region, try_cast(null as varchar(8000)) as admin_region_1, try_cast(null as varchar(8000)) as iso_subdivision, try_cast(null as varchar(8000)) as admin_region_2, try_cast(null as datetime2) as load_time
) select --distinct 
    country_region,
    left(country_region, 1) as first_letter
from __dbt__cte__src_fabric_poc_bing_covid_data
  ) _dbt_internal_unit_test_actual
),
-- Build expected result
dbt_internal_unit_test_expected as (
  select
    country_region, first_letter, 'expected' as "actual_or_expected"
  from (
    select 
    try_cast('Australia' as varchar(8000))
 as country_region, 
    try_cast('A' as varchar(8))
 as first_letter
  ) _dbt_internal_unit_test_expected
)
-- Union actual and expected results
select * from dbt_internal_unit_test_actual
union all
select * from dbt_internal_unit_test_expected
13:44:16.698546 [debug] [Thread-1 (]: fabric adapter: Database error: ('42000', "[42000] [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW)")

I have managed to get it working when putting the WTIH statements on one level.

working version

``` with __dbt__cte__src_fabric_poc_bing_covid_data as ( -- Fixture for src_fabric_poc_bing_covid_data select try_cast(null as int) as id, try_cast(null as date) as updated, try_cast(null as int) as confirmed, try_cast(null as bigint) as confirmed_int, try_cast(null as int) as confirmed_change, try_cast(null as int) as deaths, try_cast(null as smallint) as deaths_change, try_cast(null as int) as recovered, try_cast(null as int) as recovered_change, try_cast(null as float) as latitude, try_cast(null as float) as longitude, try_cast(null as varchar(8000)) as iso2, try_cast(null as varchar(8000)) as iso3, try_cast('Australia' as varchar(8000)) as country_region, try_cast(null as varchar(8000)) as admin_region_1, try_cast(null as varchar(8000)) as iso_subdivision, try_cast(null as varchar(8000)) as admin_region_2, try_cast(null as datetime2) as load_time ), _dbt_internal_unit_test_actual as ( select --distinct country_region, left(country_region, 1) as first_letter from __dbt__cte__src_fabric_poc_bing_covid_data ), dbt_internal_unit_test_actual as ( select country_region,first_letter, 'actual' as "actual_or_expected" from _dbt_internal_unit_test_actual ), -- Build expected result dbt_internal_unit_test_expected as ( select country_region, first_letter, 'expected' as "actual_or_expected" from ( select try_cast('Australia' as varchar(8000)) as country_region, try_cast('A' as varchar(8)) as first_letter ) _dbt_internal_unit_test_expected ) -- Union actual and expected results select * from dbt_internal_unit_test_actual union all select * from dbt_internal_unit_test_expected ```

[daihuynh]

macros.zip Hey @ashrodan, I have the same issue and can fix it by override 2 macros: fabric__get_unit_test_sql in helpers.sql and fabric__get_columns_in_query in columns.sql. My solution is to detect if the sql query has CTEs and extract the final selection using a regular expression, which is "re" module in dbt jinja.

You can try my solution by replace those macros files in.

• /Lib/site-packages/dbt/fabric/macros/adapters/columns.sql
• /Lib/site-packages/dbt/fabric/macros/materializations/tests/helpers.sql

Hopefully this is supported in the future.

[thebroberts]

Fingers crossed: https://learn.microsoft.com/en-us/fabric/release-plan/data-warehouse#nested-cte

[prdpsvs]

@ashrodan , Nested CTE is not currently supported by Microsoft Fabric and it is part of release plan and should be available soon. @daihuynh , Thanks for sharing the temporary solution, which could work for others.

For now, I am closing this because the adapter support will be available once the nested-cte is supported by Fabric DW. Please re-open if there is adapter specific issue.