How-to: Configure mirrored environments in dbt Cloud and Snowflake

Context

By default, dbt builds all development models flatly within a user's development schema, as shown below:

DEV_DB
├── dev_tom
│   ├── stg__model_a
│   ├── stg__model_b
│   └── int__model_c
└── dev_jerry
    ├── stg__model_a
    ├── stg__model_b
    └── int__model_c

This is different to production where models are built nestedly, being built in schemas specified in schema.yml files. https://docs.getdbt.com/reference/resource-configs/schema for more details.

PROD_DB
├── staging
│   ├── stg__model_a
│   └── stg__model_b
└── intermediate
    └── int__model_c

While this does fulfil the need of separated dev environments where devs can dev without worrying about stepping on the toes on other devs, if your project meets uses dev dataset that:

1. Limits data to a certain number of rows e.g. 10,000 records per source table
2. Does not have relational integrity e.g. the transaction table has 10,000 records that aren't necessarily related to the 10,000 records in the customer table)

then pre-deployment regression testing becomes very tedious when done manually and especially more so for automated pipelines such as CI/CD.

Let's go into why. If you wanted to test your dev code against prod source data manually, you'd do the following:

1. Compile the dbt code into SQL
2. Copy and paste the compiled SQL into a Snowflake worksheet
3. Change dev references e.g. dev_db.dev_tom.<table_name> into their prod equivalent
4. Run and compare numbers against prod transformed data

It is in step 3 that things get problematic with the dbt default configuration. Because dev models are all built under the same schema, the dev reference does not tell you which equivalent prod schema they belong to. You would require prior knowledge of the model or would have to reference the schema.yml files. In an automated solution, the former would not be possible and the latter would require the development of a script to read the schema.yml files. Mirrored environment databases would get around this issue. Consider below:

DEV_TOM_DB
├── staging
│   ├── stg__model_a
│   └── stg__model_b
└── intermediate
    └── int__model_c
--------------------
DEV_JERRY_DB
├── staging
│   ├── stg__model_a
│   └── stg__model_b
└── intermediate
    └── int__model_c
--------------------
PROD_DB
├── staging
│   ├── stg__model_a
│   └── stg__model_b
└── intermediate
    └── int__model_c

Under a mirrored environment scheme, dbt would build both development and production models in their respective databases under the same schema specified in dbt_project.yml. With this you'd be able to do a simple find & replace on the dev database name into prod_db and the resulting references will be valid e.g. dev_db.intermediate.int__model_c maps to prod_db.intermediate.int__model_c

The main drawback of a mirrored environment setup is there will be additional configuration required to ensure that the models are built in the right locations with the right permissions.

Setting Up Steps

1. Override generate_schema_name.sql macro

{% macro generate_schema_name(custom_schema_name, node) %}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{% endmacro %}

2. Snowflake object setup

Create databases for PROD, DEV_
Create DEV/PROD roles
Grant roles usage of corresponding databases
Create service users if not already created
Grant roles to users

3. Configure dbt Cloud connection to Snowflake

{% macro grant_select_and_schema_usage_to_roles(relation, roles) %}

    {% for role in roles %}

        grant usage on schema {{ relation.database }}.{{ relation.schema }} to role {{role}};
        grant select on {{ relation }} to role {{role}};

    {% endfor %}

{% endmacro %}

{% macro grant_select_to_team_abc(relation) %}

    {% if target.name == 'prod' %}

        {{ grant_select_to_roles(relation
                               , ['platform__prod_read_write_role'
                                , 'platform__prod_read_only_role'
                                , 'platform__dev_read_write_role'
                                , 'platform__dev_read_only_role']) }}

    {% elif target.name == 'dev' %}

        {{ grant_select_to_roles(relation
                               , ['platform__dev_read_write_role'
                                , 'platform__dev_read_only_role']) }}

    {% endif %}

{% endmacro %}

4. Create post-hook to grant select and schema usage to specified role

5. Test

Post-hooks to grant permissions as tables/views are rebuilt in snowflake, destroying existing permissions

Connections for dbt Cloud

When should you use it

Fresh/rebuilding db

How-to

{% macro generate_schema_name(custom_schema_name, node) %}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{% endmacro %}

Create databases for PROD, DEV_

Create DEV/PROD roles

Grant roles usage of corresponding databases

Create service users if not already created

Grant roles to users

Add dbt connection and enter dbt cloud service account environment-specific credentials

`{% macro grant_select_and_schema_usage_to_roles(relation, roles) %}

{% for role in roles %}

    grant usage on schema {{ relation.database }}.{{ relation.schema }} to role {{role}};
    grant select on {{ relation }} to role {{role}};

{% endfor %}

{% endmacro %}

{% macro grant_select_to_team_abc(relation) %}

{% if target.name == 'prod' %}

    {{ grant_select_to_roles(relation
                           , ['platform__prod_read_write_role'
                            , 'platform__prod_read_only_role'
                            , 'platform__dev_read_write_role'
                            , 'platform__dev_read_only_role']) }}

{% elif target.name == 'dev' %}

    {{ grant_select_to_roles(relation
                           , ['platform__dev_read_write_role'
                            , 'platform__dev_read_only_role']) }}

{% endif %}

{% endmacro %}`

Add post-hook to schema/model config

Build models and observe where they are built, ensuring that users are able to see what they should be able to see