[feature] unit testing a recursive CTE fails about dbt-bigquery

[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

[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