Closed JustGitting closed 1 week ago
Are you using ephemeral materialization in customers_addresses_joined?
Hi @bayees,
No, I'm using view materializations for staging and intermediate models. I'll use table materializations for the final/public facing tables.
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...
@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.
Closing this as it was solved with ephemeral models being replaced with view in the config.
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 (https://github.com/dbt-msft/dbt-sqlserver/issues/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 theorders
in int_order.sqlwhere
int_orders.sql
:This produces the error '[SQL Server]Incorrect syntax near the keyword 'with'.' because dbt generates nested CTE's because the resulting SQL is:
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.