[Bug] Enforced contracts break recursive CTE rules

[christineberger]

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

Recursive CTEs work in a model because can exist at the top-level. When enforcing contracts, a select * from () is wrapped around the model query which breaks usage rules of BQ recursive CTEs.

Expected Behavior

Models with recursive CTEs are able to build with enforced contracts

Steps To Reproduce

1. Go to dbt Cloud > develop
2. Create a model called test_model_v1.sql
3. Paste this code into the model:

    {{
     config(
             materialized='table'
         )
     }}
     
     WITH RECURSIVE
       CTE_1 AS (
         (SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
         UNION ALL
         SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
       )
     SELECT iteration FROM CTE_1
     ORDER BY 1 ASC

4. Create a _models.yml file and add configurations for the test_model:

     models:
       - name: test_model
         latest_version: 1
         # config:
         #   contract:
         #     enforced: true
         columns:
           - name: iteration
             data_type: int
             tests:
                 - unique
                 - not_null
         versions:
           - v: 1

5. Run the model: dbt run -s test_model
6. Inspect the output:
   
7. Go back to the _models.yml and uncomment the config for contracts
8. Rerun the model and inspect the output:
   

Relevant log output

19:55:39 Began executing node model.dbt_bigquery_demo.test_model.v1
19:55:39 Opening a new connection, currently in state closed
19:55:39 On model.dbt_bigquery_demo.test_model.v1: select * from (
        

WITH RECURSIVE
  CTE_1 AS (
    (SELECT 1 AS iteration UNION ALL SELECT 1 AS iteration)
    UNION ALL
    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration < 3
  )
SELECT iteration FROM CTE_1
ORDER BY 1 ASC
    ) as __dbt_sbq
    where false and current_timestamp() = current_timestamp()
    limit 0
19:55:39 BigQuery adapter: https://console.cloud.google.com/bigquery?project=sales-demo-project-314714&j=bq:US:106ef029-371d-4337-80a3-80b4650e88de&page=queryresults
19:55:39 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.')
19:55:40 BigQuery adapter: https://console.cloud.google.com/bigquery?project=sales-demo-project-314714&j=bq:US:9efd3b4e-62f1-48c1-bd00-0131d23f6321&page=queryresults
19:55:40 BigQuery adapter: https://console.cloud.google.com/bigquery?project=sales-demo-project-314714&j=bq:US:9efd3b4e-62f1-48c1-bd00-0131d23f6321&page=queryresults
19:55:40 Timing info for model.dbt_bigquery_demo.test_model.v1 (execute): 19:55:39.340818 => 19:55:40.648146
19:55:40 Database Error in model test_model (models/test_model_v1.sql)
  WITH RECURSIVE is only allowed at the top level of the SELECT, CREATE TABLE AS SELECT, CREATE VIEW, INSERT, EXPORT DATA statements.
19:55:40 1 of 1 ERROR creating sql table model dbt_cberger_bigquery.test_model_v1 ....... [ERROR in 1.37s]
19:55:40 Finished running node model.dbt_bigquery_demo.test_model.v1

Environment

- OS: Mac
- Python:
- dbt-core: 1.7.7
- dbt-bigquery: 1.7.4

Additional Context

No response

[Fleid]

@jtcohen6 do you remember if this is an expected limitation on BQ?

[jtcohen6]

@Fleid Not intended/expected, I didn't know this limitation existed until just now (thanks @christineberger!), worth documenting here: https://docs.getdbt.com/docs/collaborate/govern/model-contracts#where-are-contracts-supported

This feels like a limitation of BQ's SQL syntax, more than something I'm motivated to try to work around. Do you feel the same or different?

[Fleid]

I agree. I'll update this as a feature request, and update the doc. I'm also moving it to 'help_wanted', as this is not something that we will be able to address soon.

Thanks a lot for surfacing @christineberger, sorry we can't get you a quicker solution on this.
Let us know if you found a workaround.