Work around for nested CTE error: Incorrect syntax near the keyword 'with'.

[JustGitting]

Not sure where this should go, please move to an appropriate repo/issue.

I have to work with a MS SQL Server, hence I'm stuck with DBT 1.4 as the dbt-sqlserver adapter is EOL and will be replaced by dbt-fabric in the future (#441).

However, dbt-fabric doesn't support sql server (yet?) (https://github.com/microsoft/dbt-fabric) and it does not support dbt-utils (https://docs.getdbt.com/reference/resource-configs/fabric-configs).

I've just stumbled into the lack of support for nested CTE's in Microsoft's T-SQL, luckily dbt-msft have a nice article describing the problem.

Nesting queries with WITH clauses in TSQL, a treatise
https://dbt-msft.github.io/dbt-msft-docs/docs/nested_CTES

In my case, I have two intermediate models, where one is joined with the other. In this pseudo example, I add the customers details to the orders in int_order.sql

./intermediate/
int_orders.sql  <-- int_orders is joined with int_users.
int_customers_addresses_joined.sql  <-- join with address table to make a clean intermediate customers table.

where int_orders.sql:

select 
  orders.*,
  customers.name,
  customers.street
from {{ref(stg_orders)}} as orders
inner join {{ref(int_customers_addresses_joined)}} as users

This produces the error '[SQL Server]Incorrect syntax near the keyword 'with'.' because dbt generates nested CTE's because the resulting SQL is:

create view int_orders as (
  with __dbt__cte__int_users_addresses_joined as (
    with customersas (
      select * from dim_customers
      ),
        addresses as (
        select * from stg_addresses
      ),
      users_joined as (
        select customers.*
          addresses.street
        from customers
        left join address on customers.street = address.street
      )
      select * from customers_joined
    )
    select orders.*,
      customers.name,
      customers.street
    from orders
    left join __dbt__cte__int_customers_addresses_joined as customers on orders.customers_id = customers.user_id
<SNIP>

Q1. Is it known if Microsoft is planning to support nested CTE's int T-SQL? The https://dbt-msft.github.io/dbt-msft-docs/docs/nested_CTES article doesn't say.

Q2. How to re-write/restructure the DBT sql/yml to workaround this problem?

Thank everyone.

[bayees]

Are you using ephemeral materialization in customers_addresses_joined?

[JustGitting]

Hi @bayees,

No, I'm using view materializations for staging and intermediate models. I'll use table materializations for the final/public facing tables.

[JustGitting]

I found where people can request nested CTE's in MS SQL server from the tsql_utils docs (https://hub.getdbt.com/dbt-msft/tsql_utils/latest).

dbt-expectations

Read more about these macros in the dbt-expectations package repo.

use at your own risk! it was supported at once point, but the code base has evolved significantly since to include many nested CTEs, which aren't suported today in TSQL. Click here to upvote and get the feature supported!

support nested WITH statements (i.e. nesting of SELECT statements with WITH clauses inside of a CTE)
https://feedback.azure.com/d365community/idea/ae896b78-7c37-ec11-a819-000d3ae2b306

Requested 2 years ago by Anders Swanson... no response from Microsoft...

[JustGitting]

@bayees

You were right regarding "ephemeral materialization". I had commented it out in my dbt_project.yml file, but I didn't have a trailing space after the hash. Which the parser ignores, hence this problem.

Changing the following in the dbt_project.yml file from:

20_intermediate:
    #+materialized: ephemeral
    +materialized: view

to

20_intermediate:
    # +materialized: ephemeral
    +materialized: view

fixed the problem.

I guess the problem will be when needing to use ephemeral materialization.