[Feature] unit testing a recursive CTE fails

[HarlanH]

Is this a new bug in dbt-bigquery?

• I believe this is a new bug in dbt-bigquery
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I'm trying to unit test a model with a recursive CTE (a use case specifically called out in the docs), but it's failing with this error in dbt Cloud:

WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.

Examining the generated code, I can confirm that it starts with select * from (WITH RECURSIVE.

Expected Behavior

I expect to be able to use unit tests with recursive CTEs on BigQuery.

Steps To Reproduce

1. Add the attached 3 files to a BigQuery dbt environment with version 1.8. (I used dbt Cloud.)
2. dbt build --select +recursive_cte+

Relevant log output

13:32:41 Began running node unit_test.edsights.recursive_cte.test_recursive_cte
13:32:41 2 of 3 START unit_test recursive_cte::test_recursive_cte ....................... [RUN]
13:32:41 Acquiring new bigquery connection 'unit_test.edsights.recursive_cte.test_recursive_cte'
13:32:41 Began compiling node unit_test.edsights.recursive_cte.test_recursive_cte
13:32:41 Timing info for unit_test.edsights.recursive_cte.test_recursive_cte (compile): 13:32:41.383097 => 13:32:41.383103
13:32:41 Began executing node unit_test.edsights.recursive_cte.test_recursive_cte
13:32:41 Opening a new connection, currently in state init
13:32:41 Writing injected SQL for node "unit_test.edsights.recursive_cte.test_recursive_cte"
13:32:41 Writing injected SQL for node "unit_test.edsights.recursive_cte.test_recursive_cte"
13:32:41 On unit_test.edsights.recursive_cte.test_recursive_cte: /* {"app": "dbt", "dbt_version": "2024.0.0", "profile_name": "user", "target_name": "default", "node_id": "unit_test.edsights.recursive_cte.test_recursive_cte"} */

    
  
    

    create or replace table `production-one-405017`.`dbt_harlan`.`test_recursive_cte__dbt_tmp`
      
    
    

    OPTIONS(
      expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 12 hour)
    )
    as (
      select * from (
        WITH RECURSIVE
 __dbt__cte__recursive_cte_given as (

-- Fixture for recursive_cte_given
select safe_cast('1' as INT64) as x
), asdf AS (
    SELECT *
    FROM __dbt__cte__recursive_cte_given
)

SELECT *
FROM asdf
    ) as __dbt_sbq
    where false and current_timestamp() = current_timestamp()
    limit 0

    );
  
  
13:32:41 BigQuery adapter: https://console.cloud.google.com/bigquery?project=production-one-405017&j=bq:us-central1:a993367b-a621-4a41-8bf7-23d7275b7e5a&page=queryresults
13:32:42 BigQuery adapter: Retry attempt 1 of 1 after error: BadRequest('WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.; reason: invalidQuery, location: query, message: WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.')
13:32:43 BigQuery adapter: https://console.cloud.google.com/bigquery?project=production-one-405017&j=bq:us-central1:16d300ec-588c-4414-aafd-9583fc051629&page=queryresults
13:32:43 BigQuery adapter: https://console.cloud.google.com/bigquery?project=production-one-405017&j=bq:us-central1:16d300ec-588c-4414-aafd-9583fc051629&page=queryresults
13:32:43 Timing info for unit_test.edsights.recursive_cte.test_recursive_cte (execute): 13:32:41.384037 => 13:32:43.609843
13:32:43 Runtime Error in unit_test test_recursive_cte (models/intermediate/recursive_cte.yml)
  An error occurred during execution of unit test 'test_recursive_cte'. There may be an error in the unit test definition: check the data types.
   Database Error
    WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.
13:32:43 2 of 3 ERROR recursive_cte::test_recursive_cte ................................. [�[31mERROR�[0m in 2.23s]
13:32:43 Finished running node unit_test.edsights.recursive_cte.test_recursive_cte
13:32:43   Runtime Error in unit_test test_recursive_cte (models/intermediate/recursive_cte.yml)
  An error occurred during execution of unit test 'test_recursive_cte'. There may be an error in the unit test definition: check the data types.
   Database Error
    WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.

Environment

dbt Cloud with version 1.8

Additional Context

See also dbt Labs support ticket 64761.

[HarlanH]

recursive_cte.zip

ZIP file includes two SQL files and a YML file.

[graciegoheen]

Hey - thanks so much for opening! Since this is currently broken, we're going to remove that callout from our docs site. But I will sync with our engineers to see how we can fix this.

• We should investigate if we can remove the outer select * from ( entirely.
• We should implement this fix for all adapters that support recursive SQL

[graciegoheen]

Just adding the reproducible example Harlan shared.

I have a model I want to unit test:

# models/recursive_cte.sql

WITH RECURSIVE
asdf AS (
    SELECT *
    FROM {{ ref('recursive_cte_given') }}
)

SELECT *
FROM asdf

I add a unit test:

unit_tests:
  - name: test_recursive_cte
    model: recursive_cte
    given:
      - input: ref('recursive_cte_given')
        format: csv
        rows: |
          x
          1
    expect:
      format: csv
      rows: |
        x
        1

[MichelleArk]

It looks like WITH RECURSIVE is supported for most of the dbt Labs supported adapters:

• ✅ BigQuery: * https://cloud.google.com/bigquery/docs/recursive-ctes
  ✅ Snowflake: https://docs.snowflake.com/en/sql-reference/constructs/with#syntax
• ❌ Spark:
  • can't find any documented support
  • this databricks forum indicates no support?
• ✅ Postgres: https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE
• ✅ Redshift: https://docs.aws.amazon.com/redshift/latest/dg/r_WITH_clause.html

[MichelleArk]

For implementation:

• It looks like the extraneous select * is coming from the unit materialization, where we create a temp empty table of the model being tested using get_create_table_as_sql(True, temp_relation, get_empty_subquery_sql(sql))
• Then, the get_empty_subquery_sql wrapper adds the select * and where false limit 0 wrapper around the user-provided sql.

I'm not sure if it's possible to remove the select * but maintain the where false limit 0 filter. If we don't have the where false limit 0 filter, obtaining the column schema of the tested model isn't possible without actually running the query which could be costly...

[MichelleArk]

Additionally, we also expect to be able to wrap the user-provided sql in a subquery when constructing the statement that unions actual and expected results for comparison here.

It seems that being able to wrap the user-provided SQL in a subquery is an assumption held by the unit testing framework in multiple places currently.

[MichelleArk]

It looks like the dbt-unit-testing package also experiences this limitation, which is unsurprising given the CTE-based approach: EqualExperts/dbt-unit-testing#198

I think the only way to solve this generally is to use a seed-based strategy for ephemeral models, similar to what we'd need for being able to test incremental model upsert/merge logic: dbt-labs/dbt-core#8499

[graciegoheen]

We're going to call this out as a known limitation for the 1.8 release. But this is something we will revisit for 1.9 as an outcome of dbt-labs/dbt-core#8499