Use run_query to execute a multiple-statement block with BEGIN-END (i.e. create procedure)

[lucianmoldovanu]

I am trying to have run_query in Jinja create a procedure in the Snowflake target warehouse (conditional on some inputs being satisfied).

The code of the procedure is generic:

CREATE OR REPLACE PROCEDURE p()
RETURNS INTEGER
AS BEGIN
    <statement 1>;
    
    <statement 2>;
END;

The problem is that when using run_query the semicolon after <statement 1> is taken as a separator and the following code is attempted to be run (which obviously fails because it's incomplete).

CREATE OR REPLACE PROCEDURE p()
RETURNS INTEGER
AS BEGIN
    <statement 1>;

I have tried using the lower-level approach, yet this encounters the same issue:

call statement('create_statement', fetch_result=False)

I am using DBT core ~1.8 & dbt-snowflake adapter.

Am I missing something in DBT docs that would enable me to run such code without it being broken down? This is applicable to i.e. create procedure, create task etc in Snowflake. In general wherever a BEGIN-END block makes sense to define a block of multiple statements.

[JarroVGIT]

Running into this as well, especially unfortunate as this would've allowed us to use the new ASYNC code blocks in macro's :(

[JarroVGIT]

Just in case anybody runs into this issue as well, this can be solved by using an anonymous block code block.

I found that dbt would split queries by using the Snowflake Connector provided split_statements() (dbt-snowflake code ref). Looking at the implementation, I noticed it would ignore ; if the block was within $$'s.

So this macro performs as expected:

{% macro test_macro() %}
    {% set query %}
    EXECUTE IMMEDIATE
    $$
    BEGIN
        ASYNC(SELECT CURRENT_ROLE() AS CURRENT_ROLE);
        ASYNC(SELECT CURRENT_USER() AS CURRENT_USER);
        AWAIT ALL;
        RETURN 'Execution completed successfully.';
    END;
    $$;
    {% endset %}

    {% set result = run_query(query) %}
    {% do log("Result of query: " ~ result[0][0], True) %}

{% endmacro %}

Output:

C:\\dbt>dbt run-operation test_macro
14:11:58  Running with dbt=1.9.1
14:11:59  Registered adapter: snowflake=1.9.0
14:12:01  Found 14 models, 4 snapshots, 3 seeds, 5 operations, 5 sources, 702 macros
14:12:03  Result of query: Execution completed successfully.

[lucianmoldovanu]

That sounds great, thanks so much for the detailed example and explanation, @JarroVGIT!