How to - run a list of statements in a single run_query block

[bennieregenold7]

The dbt run_query macro allows you send multiple SQL statements to your data platform at once. This can help improve speed, and give you more control over operations you'd like to run in a single transaction (checkout the docs linked above for more on transactions).

This is especially useful for instances where you're auto-generating DDL/DML statements, and want to send them all the data platform at once. For example, if you'd like to replicate a sub-set of your production database into dev, you could use the information schema to create DDL statements for those objects. Once you have the list of statements, you can use the code below to execute them in a single block.

{% macro run_multiple_queries() %}

    {# Provide a list of commands to run #}
    {% set statements_to_run_list = [
        'create table dev.my_schema.my_awesome_table1 (id int)', 
        'create table dev.my_schema.my_awesome_table2 (id int)'
        ] 
    %}

    {# Join each list element into a string, and separate them with a semi-colon #}
    {% set statements_to_run %}
    {{ statements_to_run_list|join(';') }}
    {% endset %}

    {{ dbt_utils.log_info("Statements to run: " ~ statements_to_run) }}

    {# Run the commands #}
    {% if execute %}
    {{ run_query(statements_to_run) }}
    {{ dbt_utils.log_info("Commands executed.") }}
    {% endif %}

{% endmacro %}