[CT-1805] [Regression] Transaction behaviour change in dbt-snowflake >=1.2 results in an error when macros call run_query('begin;')

[jeremyyeo]

Is this a regression in a recent version of dbt-snowflake?

• I believe this is a regression in dbt-snowflake functionality
• I have searched the existing issues, and I could not find an existing issue for this regression

Current Behavior

A summary of the issue is available here #304

With our 1.2 release - we "fixed" a previous regression that did not show the number of affected rows - this caused users who had macros that contained singular transaction keywords like the following to error:

{% do run_query('commit;') %}

dbt version 1 releases prior to 1.2 (i.e. 1.0, 1.1) had worked fine with the above calls (albeit with the wrong number of affected rows for incrementals) so we might want to do something about gracefully handling this "breaking" but net-improvement change.

Expected/Previous Behavior

dbt version >=1.0,<1.2 had no errors when macros had {% do run_query('commit;') %} - we should continue to support this behaviour gracefully as long as we're in v1.

Steps To Reproduce

1. Project setup.

# dbt_project.yml
name: my_dbt_project
profile: snowflake
config-version: 2
version: 1.0

models:
  my_dbt_project:
    +materialized: table

-- models/foo.sql
{{
    config(
        post_hook = "{{ my_macro() }}"
    )
}}
select 1 as id

-- macros/my_macro.sql
{% macro my_macro() %}
    {% do run_query('begin;') %}
    {% do run_query('create table if not exists development.dbt_jyeo.tester (dbt_version varchar, updated_at timestamp);') %}
    {% set query %}
       insert into development.dbt_jyeo.tester values ('{{ dbt_version }}', current_timestamp()); 
    {% endset %}
    {% do run_query(query) %}
    {% do run_query('commit;') %}
{% endmacro %}

2. Run with a few different versions.

$ dbt --version && dbt run
installed version: 1.0.8
   latest version: 1.3.2

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.0.1 - Update available!
  Your version of dbt-snowflake is out of date! You can find instructions for upgrading here:
  https://docs.getdbt.com/dbt-cli/install/overview
22:02:06  Running with dbt=1.0.8
22:02:07  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 181 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
22:02:07  
22:02:15  Concurrency: 8 threads (target='dev')
22:02:15  
22:02:15  1 of 1 START table model dbt_jyeo.foo........................................... [RUN]
22:02:21  1 of 1 OK created table model dbt_jyeo.foo...................................... [SUCCESS 1 in 5.23s]
22:02:21  
22:02:21  Finished running 1 table model in 13.90s.
22:02:21  
22:02:21  Completed successfully
22:02:21  
22:02:21  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

$ dbt --version && dbt run
Core:
  - installed: 1.1.2
  - latest:    1.3.2 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.1.0 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation
22:03:52  Running with dbt=1.1.2
22:03:52  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 183 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
22:03:52  
22:03:58  Concurrency: 8 threads (target='dev')
22:03:58  
22:03:58  1 of 1 START table model dbt_jyeo.foo .......................................... [RUN]
22:04:03  1 of 1 OK created table model dbt_jyeo.foo ..................................... [SUCCESS 1 in 5.21s]
22:04:03  
22:04:03  Finished running 1 table model in 10.90s.
22:04:03  
22:04:03  Completed successfully
22:04:03  
22:04:03  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Core:
  - installed: 1.2.4
  - latest:    1.3.2 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.2.0 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation
22:04:49  Running with dbt=1.2.4
22:04:49  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 269 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
22:04:49  
22:04:55  Concurrency: 8 threads (target='dev')
22:04:55  
22:04:55  1 of 1 START table model dbt_jyeo.foo .......................................... [RUN]
22:04:59  1 of 1 ERROR creating table model dbt_jyeo.foo ................................. [ERROR in 3.84s]
22:04:59  
22:04:59  Finished running 1 table model in 0 hours 0 minutes and 9.30 seconds (9.30s).
22:04:59  
22:04:59  Completed with 1 error and 0 warnings:
22:04:59  
22:04:59  Runtime Error in model foo (models/foo.sql)
22:04:59    Tried to run an empty query on model 'model.my_dbt_project.foo'. If you are conditionally running
22:04:59    sql, eg. in a model hook, make sure your `else` clause contains valid sql!
22:04:59    
22:04:59    Provided SQL:
22:04:59    
22:04:59        begin;
22:04:59      
22:04:59  
22:04:59  Done. PASS=0 WARN=0 ERROR=1 SKIP=0 TOTAL=1

Relevant log output

No response

Environment

- OS: macOS
- Python: 3.8.2
- dbt-core (working version): <1.2
- dbt-snowflake (working version): <1.2
- dbt-core (regression version): >=1.2
- dbt-snowflake (regression version): >=1.2

Additional Context

No response

[jeremyyeo]

If necessary, as a temporary workaround (which I don't recommend) - we can try replacing the default run_query() which is bundled with dbt-core (https:/dbt-labs/dbt-core/blob/main/core/dbt/include/global_project/macros/etc/statement.sql#L46) and swapping out those transaction keywords with dummy SQL.

-- macros/run_query.sql
{% macro check_version() %}
    {% set min_version = [1, 2, 0] %}
    {% set cur_version = dbt_version.split(".") | map("int") | list %}
    {{ return(cur_version >= min_version) }}
{% endmacro %}

{% macro run_query(sql) %}
  {% set keywords = ["begin;", "BEGIN;", "commit;", "COMMIT;"] %}

  {% if sql | trim in keywords and check_version() %}
    {% if execute %}
        {% do log('Replacing transaction keyword `' ~ sql ~ '` with `select 1;`.', True) %}
    {% endif %}
    {% set sql = "select 1;" %}
  {% endif %}
  
  {% call statement("run_query_statement", fetch_result=true, auto_begin=false) %}
    {{ sql }}
  {% endcall %}

  {% do return(load_result("run_query_statement").table) %}
{% endmacro %}

Basically we have a check_version() macro that returns true if the dbt version used is >= 1.2. We use that flag in our overriden run_query() macro to swap out any sql text that contains any of our keywords (["begin;", "BEGIN;", "commit;", "COMMIT;"]) with a simple select 1;.

Testing:

$ dbt --version && dbt run
Core:
  - installed: 1.2.4
  - latest:    1.3.2 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.2.0 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation
22:17:48  Running with dbt=1.2.4
22:17:49  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 270 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
22:17:49  
22:17:55  Concurrency: 8 threads (target='dev')
22:17:55  
22:17:55  1 of 1 START table model dbt_jyeo.foo .......................................... [RUN]
22:18:01  Replacing transaction keyword `begin;` with `select 1;`.
22:18:03  Replacing transaction keyword `commit;` with `select 1;`.
22:18:04  1 of 1 OK created table model dbt_jyeo.foo ..................................... [SUCCESS 1 in 9.55s]
22:18:04  
22:18:04  Finished running 1 table model in 0 hours 0 minutes and 15.47 seconds (15.47s).
22:18:04  
22:18:04  Completed successfully
22:18:04  
22:18:04  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

$ dbt --version && dbt run
Core:
  - installed: 1.3.2
  - latest:    1.3.2 - Up to date!

Plugins:
  - databricks: 1.3.2 - Up to date!
  - bigquery:   1.3.0 - Up to date!
  - snowflake:  1.3.0 - Up to date!
  - redshift:   1.3.0 - Up to date!
  - postgres:   1.3.1 - Update available!
  - spark:      1.3.0 - Up to date!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation
22:19:01  Running with dbt=1.3.2
22:19:02  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 306 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
22:19:02  
22:19:08  Concurrency: 8 threads (target='dev')
22:19:08  
22:19:08  1 of 1 START sql table model dbt_jyeo.foo ...................................... [RUN]
22:19:11  Replacing transaction keyword `begin;` with `select 1;`.
22:19:12  Replacing transaction keyword `commit;` with `select 1;`.
22:19:13  1 of 1 OK created sql table model dbt_jyeo.foo ................................. [SUCCESS 1 in 5.60s]
22:19:13  
22:19:13  Finished running 1 table model in 0 hours 0 minutes and 11.05 seconds (11.05s).
22:19:13  
22:19:13  Completed successfully
22:19:13  
22:19:13  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

Also tested with <1.2:

$ dbt --version && dbt run
installed version: 1.0.8
   latest version: 1.3.2

Your version of dbt is out of date! You can find instructions for upgrading here:
https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.0.1 - Update available!
  Your version of dbt-snowflake is out of date! You can find instructions for upgrading here:
  https://docs.getdbt.com/dbt-cli/install/overview
22:20:37  Running with dbt=1.0.8
22:20:38  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 182 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
22:20:38  
22:20:44  Concurrency: 8 threads (target='dev')
22:20:44  
22:20:44  1 of 1 START table model dbt_jyeo.foo........................................... [RUN]
22:20:50  1 of 1 OK created table model dbt_jyeo.foo...................................... [SUCCESS 1 in 6.63s]
22:20:50  
22:20:50  Finished running 1 table model in 12.54s.
22:20:50  
22:20:50  Completed successfully
22:20:50  
22:20:50  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

$ dbt --version && dbt run
Core:
  - installed: 1.1.2
  - latest:    1.3.2 - Update available!

  Your version of dbt-core is out of date!
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation

Plugins:
  - snowflake: 1.1.0 - Update available!

  At least one plugin is out of date or incompatible with dbt-core.
  You can find instructions for upgrading here:
  https://docs.getdbt.com/docs/installation
22:21:17  Running with dbt=1.1.2
22:21:18  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 184 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
22:21:18  
22:21:23  Concurrency: 8 threads (target='dev')
22:21:23  
22:21:23  1 of 1 START table model dbt_jyeo.foo .......................................... [RUN]
22:21:30  1 of 1 OK created table model dbt_jyeo.foo ..................................... [SUCCESS 1 in 6.70s]
22:21:30  
22:21:30  Finished running 1 table model in 12.27s.
22:21:30  
22:21:30  Completed successfully
22:21:30  
22:21:30  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

[jtcohen6]

Thanks for the detailed writeup @jeremyyeo!

Agree on both counts:

• We should aim to avoid breaking changes (new errors) wherever possible during minor version upgrades.
• The custom code triggering this error is technically incorrect for achieving its desired results, given the latest guidance on how to define transactional logic in Snowflake (one multi-statement query, semicolon-delimited). It's worth warning about this.

I think there are two paths we can take here:

1. Avoid "executing" the query entirely in cases where we've redirected a BEGIN; + COMMIT;. That is, indent these lines so they get skipped over entirely in the BEGIN/COMMIT special case. While this would work, I think it would require us to mock a cursor + response so that the execute + get_response logic still works.
2. Create a special case (within the special case) for BEGIN; and COMMIT; statements that are provided all by their lonesome, as statement calls or passed into run_query(), rather than wrapping around a DML statement (as intended).

(1) is scarier to me. (2) I was able to get working, and it gives us the opportunity to raise a warning as described above, by identifying that the user is probably not using BEGIN + COMMIT in the recommended manner.