Skip to main content

Snowflake-Specific dbt Configs

dbt is built to be agnostic. The materialized='table' syntax works perfectly whether you are on Snowflake, BigQuery, or Redshift.

However, Snowflake has several incredibly powerful, platform-specific features (like Secure Views, Clustering, and Multi-Warehouse routing). If you don't utilize these features, you are leaving massive performance gains and cost savings on the table.

Because you are using the dbt-snowflake adapter, you can pass these platform-specific instructions directly into your dbt config() blocks!

Topics Covered​

  • Dynamic Warehouse Routing
  • Setting Cluster Keys
  • Transient Tables
  • Secure Views

1. Dynamic Warehouse Routing​

Imagine you have 100 models in your dbt project. 99 of them are small tables that build in 10 seconds. You want to build them using a cheap, X-SMALL Snowflake warehouse to save money.

However, the 100th model (fct_historical_transactions) is a massive 10-billion row join. If you run that on an X-SMALL warehouse, it will take 5 hours to complete.

You can configure dbt to automatically switch to a massive warehouse just for that specific model!

-- models/marts/fct_historical_transactions.sql

{{ config(
materialized='table',
snowflake_warehouse='COMPUTE_HEAVY_XL_WH' -- Switch warehouse!
) }}

SELECT * FROM ...

When dbt reaches this node in the DAG, it will dynamically execute USE WAREHOUSE COMPUTE_HEAVY_XL_WH, run the massive query in 2 minutes, and then switch back to the cheap warehouse for the remaining 99 models.

2. Setting Cluster Keys​

Snowflake automatically partitions data using micro-partitions. However, for massive fact tables, you often need to explicitly define a Clustering Key to improve Tableau query performance.

You do not need to run ALTER TABLE ... CLUSTER BY manually. You just tell dbt!

-- models/marts/fct_orders.sql

{{ config(
materialized='incremental',
cluster_by=['order_date', 'store_state'] -- Snowflake Clustering!
) }}

SELECT * FROM ...

When dbt creates this table, it will automatically append CLUSTER BY (order_date, store_state) to the DDL statement.

3. Transient Tables​

By default, Snowflake protects all standard tables with Time Travel and Fail-safe. This is great for raw data, but it costs extra money because Snowflake stores hidden historical copies of the table for up to 90 days.

If you have an intermediate dbt model (int_daily_aggregates) that gets completely dropped and rebuilt every single night, paying for Fail-safe storage is a massive waste of money. If the table is accidentally deleted, you just run dbt run to recreate it!

You can save massive storage costs by marking your dbt models as Transient.

# dbt_project.yml
models:
beems_retail:
marts:
intermediate:
# Make all intermediate models Transient to save storage costs!
+transient: true

4. Secure Views​

If you are building a model that contains highly sensitive PII (e.g., Indian PAN card numbers or user passwords), and you want to share that view with external analysts, you must ensure they cannot see the underlying DDL logic (otherwise they could reverse-engineer your data masking policies).

Snowflake solves this with Secure Views. You can easily configure this in dbt.

-- models/marts/dim_secure_users.sql

{{ config(
materialized='view',
secure=true -- Snowflake Secure View!
) }}

SELECT
user_id,
SHA2(pan_card_number) as masked_pan_card
FROM ...

Summary​

  • dbt allows you to use specific features built into the Snowflake data warehouse.
  • Use snowflake_warehouse to dynamically route massive models to larger compute clusters to speed up build times.
  • Use cluster_by to optimize query performance on massive fact tables.
  • Use transient: true on intermediate tables to save Fail-safe storage costs.
  • Use secure: true on views to hide the underlying DDL logic from end-users.