[ADAP-846] [Bug] Inline comments (--) on the last line of an incremental model (partition replacement) results in invalid SQL statement

[jeremyyeo]

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

If an incremental model (with static partition replacement) has an inline SQL comment, this results in an invalid SQL statement.

Expected Behavior

We should generate a valid SQL statement.

Steps To Reproduce

1. The model:

-- models/inc.sql
{% set partitions_to_replace = [
    'current_date', 
    'date_sub(current_date, interval 1 day)'
] %}

{{
    config(
      materialized = 'incremental',
      incremental_strategy = 'insert_overwrite',
      partition_by = {'field': 'updated_at', 'data_type': 'date'},
      partitions = partitions_to_replace
    )
}}

select 1 as id
, current_date() as updated_at -- tis but a date

2. Run:

$ dbt run -s inc --full-refresh
02:32:41  Running with dbt=1.5.4
02:32:42  Registered adapter: bigquery=1.5.5
02:32:43  Found 3 models, 0 tests, 0 snapshots, 0 analyses, 564 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
02:32:43  
02:33:40  Concurrency: 1 threads (target='bq')
02:33:40  
02:33:40  1 of 1 START sql incremental model dbt_jyeo.inc ................................ [RUN]
02:33:45  1 of 1 OK created sql incremental model dbt_jyeo.inc ........................... [CREATE TABLE (1.0 rows, 0 processed) in 5.00s]
02:33:45  
02:33:45  Finished running 1 incremental model in 0 hours 1 minutes and 1.43 seconds (61.43s).
02:33:45  
02:33:45  Completed successfully
02:33:45  
02:33:45  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

$ dbt run -s inc
02:35:06  Running with dbt=1.5.4
02:35:07  Registered adapter: bigquery=1.5.5
02:35:09  Found 3 models, 0 tests, 0 snapshots, 0 analyses, 564 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
02:35:09  
02:36:10  Concurrency: 1 threads (target='bq')
02:36:10  
02:36:10  1 of 1 START sql incremental model dbt_jyeo.inc ................................ [RUN]
02:36:13  BigQuery adapter: https://console.cloud.google.com/bigquery?project=cse-sandbox-319708&j=bq:US:5679dd53-3a7c-499d-9288-894eb1d446cc&page=queryresults
02:36:14  1 of 1 ERROR creating sql incremental model dbt_jyeo.inc ....................... [ERROR in 3.87s]
02:36:14  
02:36:14  Finished running 1 incremental model in 0 hours 1 minutes and 4.46 seconds (64.46s).
02:36:14  
02:36:14  Completed with 1 error and 0 warnings:
02:36:14  
02:36:14  Database Error in model inc (models/inc.sql)
02:36:14    Syntax error: Expected ")" but got keyword ON at [16:9]
02:36:14    compiled Code at target/run/my_dbt_project/models/inc.sql
02:36:14  
02:36:14  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Relevant log output

�[0m14:36:12.403547 [debug] [Thread-1  ]: Writing runtime sql for node "model.my_dbt_project.inc"
�[0m14:36:12.404481 [debug] [Thread-1  ]: On model.my_dbt_project.inc: /* {"app": "dbt", "dbt_version": "1.5.4", "profile_name": "all", "target_name": "bq", "node_id": "model.my_dbt_project.inc"} */

   
      

      
      

    merge into `cse-sandbox-319708`.`dbt_jyeo`.`inc` as DBT_INTERNAL_DEST
        using (



select 1 as id
, current_date() as updated_at -- tis but a date) as DBT_INTERNAL_SOURCE
        on FALSE

    when not matched by source
         and date(DBT_INTERNAL_DEST.updated_at) in (
              current_date, date_sub(current_date, interval 1 day)
          ) 
        then delete

    when not matched then insert
        (`id`, `updated_at`)
    values
        (`id`, `updated_at`)



  

  

    
�[0m14:36:12.805985 [debug] [Thread-1  ]: BigQuery adapter: Retry attempt 1 of 1 after error: BadRequest('Syntax error: Expected ")" but got keyword ON at [16:9]')
�[0m14:36:13.991102 [error] [Thread-1  ]: BigQuery adapter: https://console.cloud.google.com/bigquery?project=cse-sandbox-319708&j=bq:US:5679dd53-3a7c-499d-9288-894eb1d446cc&page=queryresults
�[0m14:36:13.993585 [debug] [Thread-1  ]: Timing info for model.my_dbt_project.inc (execute): 14:36:10.167726 => 14:36:13.992958
�[0m14:36:14.017251 [debug] [Thread-1  ]: Database Error in model inc (models/inc.sql)
  Syntax error: Expected ")" but got keyword ON at [16:9]
  compiled Code at target/run/my_dbt_project/models/inc.sql
�[0m14:36:14.019011 [debug] [Thread-1  ]: Sending event: {'category': 'dbt', 'action': 'run_model', 'label': '42741f67-bab5-4f2b-8f65-72b69e135fe9', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x127701e20>]}
�[0m14:36:14.020366 [error] [Thread-1  ]: 1 of 1 ERROR creating sql incremental model dbt_jyeo.inc ....................... [�[31mERROR�[0m in 3.87s]
�[0m14:36:14.021417 [debug] [Thread-1  ]: Finished running node model.my_dbt_project.inc
�[0m14:36:14.024091 [debug] [MainThread]: Connection 'master' was properly closed.
�[0m14:36:14.024657 [debug] [MainThread]: Connection 'model.my_dbt_project.inc' was properly closed.
�[0m14:36:14.025172 [info ] [MainThread]: 
�[0m14:36:14.025836 [info ] [MainThread]: Finished running 1 incremental model in 0 hours 1 minutes and 4.46 seconds (64.46s).
�[0m14:36:14.026800 [debug] [MainThread]: Command end result

Environment

- OS: macOS
- Python: 3.9.13
- dbt-core: 1.5.4
- dbt-bigquery: 1.5.5

Additional Context

, current_date() as updated_at -- tis but a date) as DBT_INTERNAL_SOURCE
                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

[dbeatty10]

Thank you for reporting this @jeremyyeo! 🏆

This looks similar to dbt-labs/dbt-core#6781. I've created dbt-labs/dbt-core#8487 so the base test cases get created and then they can be inherited by adapters.