WITH RECURSIVE is only allowed at the top level

[jameshorrocks001]

Getting the following error when trying to run unit tests with model having a recursive CTE

Runtime Error
  Database Error in model_example (tests/unit/model_example.sql)
    WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.

Within the generated test SQL, the recursive CTE is no longer at the top level which invalidates the query.

Is there a way to handle this?

[psousa50]

Hi @jameshorrocks001, I'm afraid this will be a tough one. 'dbt-unit-testing' relies heavily on CTEs and it's hard to make it work if you can only have recursive CTEs at the top level. Which database are you using?

[jameshorrocks001]

Hi, we're using Big Query

[psousa50]

Hi @jameshorrocks001,

Sorry for the late response. I'm afraid it's not possible right now to use recursive CTEs with dbt-unit-testing. Thank you very much for your comment; we'll try to think of a way to solve this.

[cdiniz]

We should note this as a limitation of our current approach.
Since we create a CTE for mocking purposes - the model under test will always be inside this top-level CTE.