dbt hooks and multiple sql statements

Help
1

Hello

I am using dbt Core on Snowflake. I have some SQL that I am trying to execute via on on-run-start hook.

The SQL uses Snowflake SQL scripting because it contains some conditional logic. I have embedded the SQL in a macro that looks like:

{% macro example_on_run_start() %}

{% set sql %}

DECLARE
    EXAMPLE_VAR INT;
BEGIN
    SELECT COUNT(*) INTO :EXAMPLE_VAR
    FROM ...
    WHERE ...;

    IF EXAMPLE_VAR = 0 THEN
        ...
        ...
    END IF;
END;

{% endset %}

{{ return(sql) }}

{% endmacro %}

When dbt executes the above, it treats the SQL as multiple standalone statements, splitting the SQL at each semi-colon. It first executes:

DECLARE
    EXAMPLE_VAR INT

Clearly this isn’t a valid standalone SQL statement so it fails. The whole block of SQL needs to be executed in one query to Snowflake.

Is there any way to get dbt to execute the entire SQL block in one go? I.e. not split up the SQL.

Thanks

2

This might not be what you are looking for - move the conditional scripting to a snowflake procedure and make a call to it in dbt on-run-start. Unless you are using some jinja as well in the macro, looks unlikely.

3

Hi seems someone found workaround using $$:

https://github.com/dbt-labs/dbt-core/discussions/10810

{% 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 %}

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.
1 Like