[ADAP-435] [Bug] Cannot use begin/end scripting statement with snowflake_dml_explicit_transaction

[OSalamaPret]

Is this a new bug in dbt-snowflake?

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

Current Behavior

I am writing a custom incremental materialization which calls {{ strategy_sql_macro_func(strategy_arg_dict) }} within a Snowflake scripting begin/end block.
Example:

  {%- call statement('main') -%}
    CREATE OR REPLACE TASK batch.test schedule = 'USING CRON 0 17 * * * Europe/London' AS
    EXECUTE IMMEDIATE
    $$
    BEGIN
      {{ snowflake__create_view_as_with_temp_flag(tmp_relation, compiled_code, True) }}
      {{ strategy_sql_macro_func(strategy_arg_dict) }}
    END;
    $$;
  {%- endcall -%}

When this runs, it generates the following error syntax error line 43 at position 7 unexpected ';'.

Expected Behavior

I expect that we should be able to mix Snowflake begin/end scripting blocks with transactions, as this is fully supported in Snowflake.

The issue is due to the snowflake_dml_explicit_transaction macro using begin to start the transaction, which, in this context, Snowflake is seeing as a begin/end block.
The fix is to use the explicit begin transaction instead of begin

I have overriden the snowflake_dml_explicit_transaction to use begin transaction and confirm that my issue is resolved.

Steps To Reproduce

Quickest route to reproduce:

1. Modify the Snowflake incremental materialization and change the {%- call statement('main') -%} to following:

{%- call statement('main') -%}
    CREATE OR REPLACE TASK test_db.test_schema.test_task AS
    EXECUTE IMMEDIATE
    $$
    BEGIN
      {{ snowflake__create_view_as_with_temp_flag(tmp_relation, compiled_code, True) }}
      {{ strategy_sql_macro_func(strategy_arg_dict) }}
    END;
    $$;
  {%- endcall -%}

2. Create a Snowflake incremental model, eg test_model.sql:

{{
    config(
        materialized='incremental'
    )
}}

select 'test' as test_query, '2023-04-10' as created_date 
{% if is_incremental() %}
  WHERE created_date > (select max(created_date) from {{ this }})
{% endif %}

3. Run dbt run --select test_model.sql

Relevant log output

No response

Environment

- OS:Ubuntu 20.04 (running under Windows using WSL)
- Python: 3.10.5
- dbt-core: 1.4.5
- dbt-snowflake: 1.4.2

Additional Context

• You can see my fix here - https://github.com/dbt-labs/dbt-snowflake/compare/main...OSalamaPret:dbt-snowflake:use-explicit-begin-transaction?expand=1
• Snowflake recommends BEGIN TRANSACTION - https://docs.snowflake.com/en/sql-reference/transactions#explicit-transactions

[dbeatty10]

Thanks for reaching out @OSalamaPret !

Would you be interested in raising a PR with the fix you proposed?

[OSalamaPret]

Thanks for reaching out @OSalamaPret !

Would you be interested in raising a PR with the fix you proposed?

I tried, but not sure I'm able to sign the CLA on behalf of my company! I guess I could create a personal account and use that though. Will try it out when I get some free time.

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[dbeatty10]

The suggested fix is here, namely:

-     begin;
+     begin transaction;

I'm labeling this as a "good first issue" if anyone wants to pick it up.

[shreyadatar]

@dbeatty10 I'd be happy to pick this up. Will start going through the contributing guidelines!

[lokofoko]

Any updates here? Issue is still persist and it is impossible to create procedure in snowflake from dbt.

[amychen1776]

I'm closing this issue because at this time, we do not intend to support this use case in our roadmap.