dbt data tests error with 'with' clause usage

[wongio]

-- assert count of rows

with cte as (
select count(distinct([category])) as cnt
from table
)
select cnt
from cte
where cnt <> 2

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. (156) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. (319); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")

I then add a semi-colon before the with and I get this error.

('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ';'. (102) (SQLExecDirectW); [42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near ')'. (102)")

Any thoughts on why this is occurring?

[wongio]

using dbt 0.15.2

If I don't put a with clause it works fine.

[b-per]

I have the same error on my side, using dbt 0.15.2 as well.
WITH works fine in models but not in data tests

[b-per]

I updated the connections.py file as per this commit and it is showing me the query that is failing.

The problem seems to be that dbt uses a different function to run data tests (here) and the SQL query that is called is in the form of:

select count(*) as errors from (
<query for the test>
) sbq

And SQL Server throws errors with CTEs if the WITH statement is not the first one in the query. So fixing this issue would require changing things in the dbt code base or making dbt-sqlserver call data tests differently than the way dbt does it.

As dbt-sqlserver is not officially supported I don't know if it would be useful to raise an issue in the dbt repo directly.

[yp-s19tech]

Also experiencing the same issue

[mikaelene]

Hi all, this is a known limitation for me. I also don’t know how important it is to fix? Can you use a sub query instead?

There are some issues with the sql server dialect that makes it hard to create this adapter. The most annoying is that you can’t use ‘create table as’. Like in all other dbs. Right now there is an issue in dbt core to make it possible to implement tests at all in versions greater then v0.15.x. Which will make it possible to upgrade this adapter along with dbt.

If no one objects, I am prioritising that first and the possibility’s for test with ‘with’ later.

[yp-s19tech]

@mikaelene I managed to work around the issue by using a subquery, at the cost of readability:

select my_subquery.COL_A, my_subquery.a_counts from (select COL_A, count(*) as a_counts from {{ ref('FIRST_TBL') }} group by COL_A ) as my_subquery WHERE my_subquery.a_counts = 0

[ghost]

It would probably be possible to support WITH by creating a view, running the view and then dropping the view again, but that would require to run multiple SQL queries or to build everything into a procedure returning results of a temporary table ...

[dataders]

fixed by #167!

[dodangquyen22]

Hi all, I have same issue. I using dbt test and get this error: "Incorrect syntax near the keyword 'with'" . Env i use: Python 3.12, dbt-sqlserver 1.8.4. I would like to know how to solve it, thanks everyone.

[markstiles]

@dodangquyen22 I hit the same issue but when I looked at the compiled code I noticed that dbt nests the 'with' clauses. I don't think SQL Server supports that which is what's causing the error. Even though dbt convention says you can use CTEs in each query file, it's probably because Snowflake and other warehouses support the nested syntax (Snowflake With Clause). When I removed all the 'with' clauses from all the query files, the dbt compiles it so sources are wrapped in 'with' clauses that SQL Server would run. In queries that require 'with' you might need to break convention and structure your query file hierarchy in such a way that compiles to SQL Server standards and use go statements to terminate the queries at the top level.

The materializations also play a role here. I didn't materialize the stage layer as views which resulted in the queries being imported as-is instead of referencing a view in the for/join statements. If you materialize everything in the DAG it will have a database object to reference instead needing to embed the query and may work around the issue.