Creating a dev environment quickly on Snowflake

If your organization has a large dbt project, and you need to run dbt with a new target schema (for example, a new employee joined and wants to start contributing. Or, you borked your own target schema and want to start from scratch), here’s a handy macro to help you out.

Note: it only works on Snowflake, and assumes that you don’t use mutliple schemata.


{% macro clone_prod_to_target(from) %}

    {% set sql -%}
        create schema if not exists {{ target.database }}.{{ target.schema }} clone {{ from }};
    {%- endset %}

    {{ dbt_utils.log_info("Cloning schema " ~ from ~ " into target schema.") }}

    {% do run_query(sql) %}

    {{ dbt_utils.log_info("Cloned schema " ~ from ~ " into target schema.") }}

{% endmacro %}


{% macro destroy_current_env() %}

    {% set sql -%}
        drop schema if exists {{ target.database }}.{{ target.schema }} cascade;
    {%- endset %}

    {{ dbt_utils.log_info("Dropping target schema.") }}

    {% do run_query(sql) %}

    {{ dbt_utils.log_info("Dropped target schema.") }}

{% endmacro %}

{% macro reset_dev_env(from) %}
{#-
This macro destroys your current development environment, and recreates it by cloning from prod.

To run it:
    $ dbt run-operation reset_dev_env --args '{from: analytics}'

-#}
    {% if target.name == 'dev' %}

    {{ destroy_current_env() }}

    {{ clone_prod_to_target(from) }}

    {% else %}

    {{ dbt_utils.log_info("No-op: your current target is " ~ target.name ~ ". This macro only works for a dev target.", info=True) }}

    {% endif %}

{% endmacro %}

Run it with:

$ dbt run-operation reset_dev_env --args '{from: analytics}'

Then, you can go about working on the models you want to develop without having to rebuild from scratch.

We also use the clone_prod_to_target macro as an operation in our CI runs to make them faster – by cloning our prod schema, our incremental models only process new data.

8 Likes

Oh yeah! Thanks for sharing, @claire, this is awesome.