Snowflake with dbt
dbt project structure, sources, models and materializations, tests, snapshots, incremental strategies with Snowflake MERGE, environments, and CI/CD for dbt on Snowflake.
Quick answer
dbt organises Snowflake transformations into version-controlled models, tests, snapshots and documentation, while Snowflake runs every statement. Incremental models compile to Snowflake MERGE, and separate targets keep dev, CI and production apart.
- Use source() for raw tables and ref() between models.
- Incremental merge models compile to MERGE INTO.
- Configure Snowflake options such as cluster_by in dbt.
freshcart:
target: dev
outputs:
dev:
type: snowflake
account: acme-xy12345
user: "{{ env_var('SNOWFLAKE_USER') }}"
authenticator: externalbrowser
role: transformer
warehouse: etl_wh
database: analytics
schema: dbt_avaSnowflake Runs the SQL; dbt Organizes the Factory
The medallion architecture module introduced Raw, Silver, and Gold as a way to organize transformations inside Snowflake, and showed dbt-style SQL as an illustration of the pattern. This module goes deep on the tool itself. dbt (data build tool) does not run your data anywhere — Snowflake still executes every statement. What dbt provides is structure around that SQL: version-controlled model files, an automatically inferred dependency graph, built-in and custom tests, generated documentation, environment-aware connection profiles, and a CLI (or dbt Cloud) that turns "run this pile of SQL scripts in the right order" into a repeatable, reviewable engineering workflow.
Plain-English definition: a dbt model is a SELECT statement saved as a `.sql` file. dbt compiles it, figures out its dependencies from other models it references, and runs `CREATE TABLE`/`CREATE VIEW`/`MERGE` statements against Snowflake to materialize it — you write SELECTs, dbt handles the DDL and the ordering.
Your Snowflake account
RAW schema <- loaded by Snowpipe/COPY/connectors (outside dbt)
SILVER schema <- built by dbt staging models
GOLD schema <- built by dbt mart models
Your dbt project (version controlled in Git)
models/staging/*.sql -> compiles to SILVER views/tables
models/marts/*.sql -> compiles to GOLD views/tables
schema.yml -> tests + documentation attached to models
dbt_project.yml -> project-wide config
profiles.yml -> which Snowflake warehouse/database/role each run usesA dbt Project Is a Directory of SQL, YAML, and Config
Every dbt project follows a similar shape. `dbt_project.yml` declares project-wide settings — the project name, default materialization per directory, variables. `models/` holds your SQL files, typically organized into `staging/` (one model per raw source, light cleaning), `intermediate/` (reusable business logic that isn't meant to be queried directly by BI tools), and `marts/` (business-ready Gold-layer outputs). `profiles.yml`, usually kept outside the Git repo for security, holds the actual Snowflake connection details per environment.
my_dbt_project/
dbt_project.yml
models/
staging/
sources.yml
stg_orders.sql
stg_customers.sql
intermediate/
int_orders_enriched.sql
marts/
finance/
fct_orders.sql
daily_revenue.sql
product/
dim_customers.sql
snapshots/
customers_snapshot.sql
tests/
assert_positive_revenue.sql
macros/
cents_to_dollars.sql
seeds/
country_codes.csvname: 'my_dbt_project'
version: '1.0.0'
profile: 'my_dbt_project'
model-paths: ['models']
snapshot-paths: ['snapshots']
seed-paths: ['seeds']
models:
my_dbt_project:
staging:
+materialized: view
+schema: silver
marts:
+materialized: table
+schema: goldmy_dbt_project:
target: dev
outputs:
dev:
type: snowflake
account: xy12345.us-east-1
user: DBT_DEV_USER
password: "{{ env_var('DBT_PASSWORD') }}"
role: DBT_TRANSFORMER
database: ANALYTICS_DEV
warehouse: WH_TRANSFORM_XS
schema: DBT_DEV_ASIL
threads: 4
prod:
type: snowflake
account: xy12345.us-east-1
user: DBT_PROD_SERVICE_USER
password: "{{ env_var('DBT_PASSWORD') }}"
role: DBT_TRANSFORMER_PROD
database: ANALYTICS
warehouse: WH_TRANSFORM_M
schema: DBT_PROD
threads: 8| File | Purpose |
|---|---|
| dbt_project.yml | Project-wide config: name, default materializations, folder-level settings. |
| profiles.yml | Snowflake connection details per environment/target — account, warehouse, role, database. |
| sources.yml | Declares raw tables dbt can reference with source() and their freshness expectations. |
| schema.yml (per model folder) | Attaches tests and documentation to specific models and columns. |
| models/*.sql | The actual SELECT statements that become views/tables/incremental tables. |
source() Declares and Documents Your Raw Inputs
A dbt source is a declared reference to a raw table that dbt itself did not create — typically something loaded by Snowpipe, a COPY INTO job, or a Fivetran/Airbyte-style connector, landing in a RAW schema exactly as described in the medallion architecture module. Declaring sources in `sources.yml` rather than hardcoding schema.table names throughout your SQL gives you two things: a single place to change the underlying location if it moves, and dbt-native freshness checks.
version: 2
sources:
- name: raw
database: ANALYTICS
schema: RAW
tables:
- name: orders
loaded_at_field: loaded_at
freshness:
warn_after: {count: 12, period: hour}
error_after: {count: 24, period: hour}
- name: customers
loaded_at_field: loaded_at
freshness:
warn_after: {count: 24, period: hour}-- models/staging/stg_orders.sql
SELECT
order_id::STRING AS order_id,
customer_id::STRING AS customer_id,
TRY_TO_TIMESTAMP_NTZ(order_ts) AS order_ts,
LOWER(status) AS status,
TRY_TO_NUMBER(total_usd, 12, 2) AS total_usd,
loaded_at
FROM {{ source('raw', 'orders') }}
WHERE order_id IS NOT NULL| Command | What it checks |
|---|---|
| dbt source freshness | Compares CURRENT_TIMESTAMP() against loaded_at_field for each declared source table, flags warn/error thresholds. |
| dbt run --select stg_orders | Rebuilds only the stg_orders model and its dependencies as configured. |
| dbt test --select source:raw | Runs any tests attached to source tables (e.g. not_null on a key column). |
Models Are SELECTs; ref() Builds the Dependency Graph
A model is a `.sql` file containing a single SELECT statement — no `CREATE TABLE`, no `INSERT`, just the query. dbt wraps it in the appropriate DDL/DML based on the model's configured materialization. When one model's SQL references another model with `{{ ref('model_name') }}` instead of hardcoding its schema.table name, dbt can build the full dependency graph (the DAG) automatically — it knows `fct_orders` must run after `stg_orders`, without you writing any orchestration logic.
-- models/staging/stg_orders.sql (compiles to SILVER.STG_ORDERS)
SELECT
order_id::STRING AS order_id,
customer_id::STRING AS customer_id,
TRY_TO_TIMESTAMP_NTZ(order_ts) AS order_ts,
LOWER(status) AS status,
TRY_TO_NUMBER(total_usd, 12, 2) AS total_usd
FROM {{ source('raw', 'orders') }}
WHERE order_id IS NOT NULL
-- models/marts/finance/daily_revenue.sql (compiles to GOLD.DAILY_REVENUE)
SELECT
DATE(order_ts) AS order_date,
COUNT(*) AS order_count,
SUM(total_usd) AS revenue_usd
FROM {{ ref('stg_orders') }}
WHERE status NOT IN ('cancelled', 'fraud')
GROUP BY 1Because `daily_revenue.sql` never writes the literal string `SILVER.STG_ORDERS`, dbt is free to compile that reference to whatever schema `stg_orders` actually lives in for the current target — `DBT_DEV_ASIL.STG_ORDERS` in dev, `DBT_PROD.STG_ORDERS` in prod. This is the mechanism that makes environments (Part 09) work at all.
View, Table, or Incremental — dbt Picks the DDL For You
A model's materialization controls what SQL dbt actually runs against Snowflake. A `view` materialization compiles the model into `CREATE OR REPLACE VIEW ... AS (your select)` — cheap to create, recomputed on every query. A `table` materialization compiles into `CREATE OR REPLACE TABLE ... AS (your select)` — a full rebuild every run, but fast to query afterward. An `incremental` materialization is the interesting one: on the first run it behaves like a table, but on subsequent runs it processes only new/changed rows instead of rebuilding everything.
| Materialization | Compiles to | Use when | Avoid when |
|---|---|---|---|
| view | CREATE OR REPLACE VIEW | Logic is light, or the model is a thin staging layer few queries hit directly. | Many downstream queries re-run expensive logic repeatedly. |
| table | CREATE OR REPLACE TABLE ... AS SELECT | Output is reused often and a full rebuild is affordable. | Source data is large and mostly unchanged run to run. |
| incremental | INSERT/MERGE of only new or changed rows (config-dependent) | Large, append-heavy or slowly-changing fact tables. | The unique key or "what changed" logic is unclear. |
| ephemeral | Inlined as a CTE into whatever references it — never its own table/view | Small reusable snippets only ever used by one or two downstream models. | The logic is expensive or reused by many models (gets recomputed every time). |
-- models/marts/finance/fct_orders.sql
{{ config(materialized='table') }}
SELECT
order_id,
customer_id,
order_ts,
status,
total_usd
FROM {{ ref('stg_orders') }}is_incremental() and Snowflake's Native MERGE
An incremental model's SQL needs to behave differently depending on whether it's the very first run (build the whole table) or a subsequent run (process only what's new). dbt provides the `is_incremental()` macro specifically for this: it evaluates to `false` on the first run or a full `--full-refresh`, and `true` on every normal subsequent run, letting you write one model file that branches its own filter logic.
-- models/marts/finance/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id',
incremental_strategy='merge'
)
}}
SELECT
order_id,
customer_id,
order_ts,
status,
total_usd,
updated_at
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
-- only true on runs after the first: filter to rows newer than what's already in the target
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}The `incremental_strategy='merge'` config is what pairs this pattern directly with Snowflake's native MERGE, covered in depth in the merge-idempotency module. Under the hood, dbt compiles the incremental run into roughly the same shape of `MERGE INTO target USING (new rows) ON unique_key ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT` statement you'd hand-write yourself — dbt is just generating that MERGE for you from the `unique_key` and the compiled SELECT.
MERGE INTO GOLD.FCT_ORDERS AS target
USING (
SELECT order_id, customer_id, order_ts, status, total_usd, updated_at
FROM SILVER.STG_ORDERS
WHERE updated_at > (SELECT MAX(updated_at) FROM GOLD.FCT_ORDERS)
) AS src
ON target.order_id = src.order_id
WHEN MATCHED THEN UPDATE SET
status = src.status,
total_usd = src.total_usd,
updated_at = src.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, customer_id, order_ts, status, total_usd, updated_at)
VALUES (src.order_id, src.customer_id, src.order_ts, src.status, src.total_usd, src.updated_at);| Incremental strategy | Behavior on Snowflake | Use when |
|---|---|---|
| merge | Compiles to a native Snowflake MERGE keyed on unique_key. | Mutable source rows — the default and most common choice on Snowflake. |
| append | Compiles to a plain INSERT of new rows, no update/dedupe. | Strictly insert-only event/log data with no updates. |
| delete+insert | Deletes matching rows in target, then inserts new/changed set. | Reprocessing full partitions/batches rather than row-by-row updates. |
Generic Tests Enforce Assumptions on Every Run
dbt ships four built-in generic tests you attach to columns declaratively in YAML: `unique`, `not_null`, `accepted_values`, and `relationships`. Each compiles to a SELECT that should return zero rows; if it returns any rows, the test fails and `dbt build`/`dbt test` reports the failure with the offending rows discoverable. This is the same discipline the medallion architecture module described as "tests are part of the architecture" — dbt just gives you a standard, declarative way to define them instead of hand-writing ad hoc SELECT checks every time.
version: 2
models:
- name: fct_orders
columns:
- name: order_id
data_tests:
- unique
- not_null
- name: status
data_tests:
- accepted_values:
values: ['completed', 'cancelled', 'refunded', 'fraud']
- name: customer_id
data_tests:
- relationships:
to: ref('dim_customers')
field: customer_id-- Custom (singular) test: any file under tests/ that returns rows is a failure.
SELECT order_id, total_usd
FROM {{ ref('fct_orders') }}
WHERE total_usd < 0| Test type | Example | Compiles to |
|---|---|---|
| unique | order_id is unique in fct_orders. | GROUP BY with HAVING COUNT(*) > 1. |
| not_null | customer_id is never null. | SELECT ... WHERE customer_id IS NULL. |
| accepted_values | status is one of a known set. | SELECT ... WHERE status NOT IN (...). |
| relationships | Every fct_orders.customer_id exists in dim_customers. | LEFT JOIN with WHERE parent.customer_id IS NULL. |
| custom SQL test | Anything you can express as "these rows are bad." | Runs the file's SELECT as-is; any returned rows fail the test. |
Snapshots Track Slowly Changing Dimensions Over Time
A regular dbt model always represents the current state of its source — run it again and old values are simply overwritten. A snapshot is different: it's dbt's mechanism for capturing a Type 2 slowly changing dimension, meaning it preserves history of how a row's values changed over time, each with a valid-from and valid-to timestamp, rather than just the latest value.
{% snapshot customers_snapshot %}
{{
config(
target_schema='snapshots',
unique_key='customer_id',
strategy='timestamp',
updated_at='updated_at'
)
}}
SELECT
customer_id,
email,
segment,
updated_at
FROM {{ source('raw', 'customers') }}
{% endsnapshot %}Running `dbt snapshot` compares the current source rows against what the snapshot table already holds. When a tracked customer's `segment` changes, the snapshot closes out the old row (stamping a `dbt_valid_to`) and inserts a new row with `dbt_valid_from` set to now — exactly the Type 2 SCD pattern used in traditional dimensional modeling.
| Mechanism | What it tracks | Trigger | Where the history lives |
|---|---|---|---|
| dbt snapshot | Row-level history of column value changes over time (SCD Type 2). | Manually or scheduled runs of `dbt snapshot`. | A dedicated snapshot table with dbt_valid_from/dbt_valid_to columns. |
| Snowflake stream | Row-level change events (insert/update/delete) since the stream was last consumed. | Any DML against the source table. | Not stored long-term — consumed by a task/MERGE and then the offset advances. |
dev, staging, and prod Targets Point at Different Warehouses
`profiles.yml` (shown in Part 02) defines multiple targets — typically `dev`, sometimes `staging`, and `prod` — each with its own Snowflake account credentials, role, database, and warehouse. Because every model references its dependencies through `ref()`/`source()` rather than hardcoded schema names, the exact same SQL compiles differently per target: a developer running `dbt run --target dev` builds into their own personal dev schema on a small XS warehouse, while the production job runs `dbt run --target prod` and builds into the shared prod database on a right-sized warehouse.
| Target | Typical database | Typical warehouse | Who runs it |
|---|---|---|---|
| dev | ANALYTICS_DEV, schema scoped per developer. | Small (XS) — cheap, isolated experimentation. | Individual developers, local dbt runs. |
| staging / CI | A CI-scoped database, often built and torn down per PR. | Small, shared, short-lived. | CI pipeline on every pull request. |
| prod | ANALYTICS (the real Gold layer BI tools query). | Right-sized for full-project runtime. | A scheduled job or orchestrator — never a person by hand. |
dbt build Belongs in a Pipeline, Not Just a Laptop
`dbt build` runs models, tests, snapshots, and seeds together in dependency order, stopping downstream work on a failed test where configured to. Wiring this into CI means every pull request that changes a model gets built and tested against a scoped Snowflake schema before it can merge — the same "block on failed tests" discipline the medallion architecture module described as part of the architecture itself, now automated.
# .github/workflows/dbt-ci.yml (excerpt)
- name: Run dbt build against CI schema
run: |
dbt deps
dbt build --target ci --select state:modified+ --state ./prod-manifest
env:
DBT_PASSWORD: ${{ secrets.DBT_CI_PASSWORD }}The `--select state:modified+` pattern is worth calling out: rather than rebuilding the entire project on every PR, dbt can compare against a previous "state" (a saved manifest, often from the last successful prod run) and select only the models that changed plus everything downstream of them. This keeps CI fast and keeps compute cost proportional to the size of the actual change.
| Approach | What it is | Tradeoff |
|---|---|---|
| dbt Core, self-hosted CI | You run `dbt` via GitHub Actions/GitLab CI/Airflow on your own compute. | Full control and no extra cost, but you own the orchestration and scheduling. |
| dbt Cloud | A hosted service that runs jobs, hosts docs, and manages scheduling/CI for you. | Less infrastructure to maintain, but it's a separate paid product with its own environment. |
Staging Models Are Your Silver Layer; Marts Are Your Gold Layer
Bringing this full circle to the medallion architecture module: in a real dbt-on-Snowflake project, the layers aren't just a conceptual diagram, they're literal folders and schema configs. Raw stays exactly as that module described it — loaded outside dbt, referenced only through `source()`. `models/staging/` models, materialized as views or lightly-typed tables into a SILVER-configured schema, are the Silver layer: typed, deduped, standardized, still close to source shape. `models/marts/` models, materialized as tables (or incremental tables for large fact tables) into a GOLD-configured schema, are the Gold layer: business-ready facts, dimensions, and marts with tests and documented owners.
Medallion concept (elt-medallion module) dbt implementation (this lesson)
------------------------------------------- -------------------------------------------
RAW schema, loaded outside the warehouse source() declarations in sources.yml
SILVER: typed, deduped, standardized models/staging/*.sql -> view/table materialization
GOLD: business-ready facts/dims/marts models/marts/**/*.sql -> table/incremental materialization
"Tests are part of the architecture" schema.yml generic tests + custom SQL tests
"Every Gold table needs an owner" model owners documented via meta/docs in schema.yml
"Lineage explains where a number came from" dbt-generated DAG (dbt docs generate) from ref()/source()This is why teams that already understand medallion architecture tend to pick up dbt quickly: dbt didn't invent a new mental model, it gave the existing one version control, automatic dependency ordering, tests-as-code, and a generated lineage graph you don't have to draw by hand.
Common dbt-on-Snowflake Anti-Patterns
- ✓Materializing every model as a table out of habit, inflating storage and compute for no benefit.
- ✓Writing hardcoded schema.table references instead of ref()/source(), which breaks environment isolation and lineage.
- ✓Having no unique/not_null tests on primary keys — the highest-leverage tests in the whole project.
- ✓Choosing incremental materialization without a real, well-defined unique_key, silently corrupting the merge.
- ✓Letting analysts hand-edit compiled tables directly in Snowflake outside of Git and dbt.
- ✓Running dbt with a role that has far more privileges than the transformation actually needs.
- ✓Confusing a dbt snapshot with an incremental-loading mechanism — they solve different problems.
- ✓Skipping CI and running dbt only from someone's laptop before merging to prod.
Hands-On Lab: Build a Small Staging-to-Mart dbt Flow
This lab is the minimum practical dbt project on Snowflake: a source, a staging model, a mart model with an incremental merge strategy, and tests wired to the primary key.
# models/staging/sources.yml
version: 2
sources:
- name: raw
database: DBT_LAB
schema: RAW
tables:
- name: orders
loaded_at_field: loaded_at
-- models/staging/stg_orders.sql
SELECT
order_id::STRING AS order_id,
customer_id::STRING AS customer_id,
TRY_TO_TIMESTAMP_NTZ(order_ts) AS order_ts,
LOWER(status) AS status,
TRY_TO_NUMBER(total_usd, 12, 2) AS total_usd,
loaded_at AS updated_at
FROM {{ source('raw', 'orders') }}
WHERE order_id IS NOT NULL-- models/marts/fct_orders.sql
{{
config(
materialized='incremental',
unique_key='order_id',
incremental_strategy='merge'
)
}}
SELECT order_id, customer_id, order_ts, status, total_usd, updated_at
FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}
# models/marts/schema.yml
version: 2
models:
- name: fct_orders
columns:
- name: order_id
data_tests: [unique, not_null]
- name: status
data_tests:
- accepted_values:
values: ['completed', 'cancelled', 'refunded', 'fraud']Lab questions
- ✓On the very first dbt run, what does is_incremental() evaluate to, and what SQL does dbt run as a result?
- ✓On the second run, after one new order lands in RAW.ORDERS, what does the compiled MERGE statement look for?
- ✓What would happen to fct_orders if unique_key were left off the config?
- ✓Which test would catch a status value like "returned" that nobody expected?
- ✓How would you rerun this lab against a --target prod that points at a different Snowflake database and warehouse?
How to Explain dbt on Snowflake in an Interview
A strong answer sounds like this: dbt lets a team manage Snowflake transformations as version-controlled SQL models instead of ad hoc scripts. Sources declare raw inputs with source() and freshness checks; models are plain SELECT statements that reference each other with ref(), which lets dbt build the dependency graph automatically and compile the right materialization — view, table, or incremental — into Snowflake DDL/DML. For large mutable fact tables I'd use an incremental model with the merge strategy, which compiles into a native Snowflake MERGE keyed on a unique_key, guarded by is_incremental() so the first run does a full build and later runs process only new or changed rows. Generic tests like unique, not_null, accepted_values, and relationships enforce assumptions on every run, and snapshots give Type 2 slowly changing dimension history when I need to track how a row changed over time — a different problem from what a Snowflake stream solves. Environments in profiles.yml point dev, CI, and prod at different Snowflake databases and warehouses so the same compiled SQL never touches production by accident, and CI runs dbt build on every pull request so broken models or failed tests never reach the Gold layer. In practice, dbt's staging models are the Silver layer and its mart models are the Gold layer — it's the tool that actually implements medallion architecture in most real Snowflake projects.
Questions you should answer out loud
- ✓What is the difference between source() and ref(), and why does that difference matter for the dependency graph?
- ✓What SQL does a table materialization compile to? A view? An incremental model with the merge strategy?
- ✓What does is_incremental() evaluate to on the first run versus later runs?
- ✓How is a dbt snapshot different from a Snowflake stream, given both are about detecting change?
- ✓Name the four generic dbt tests and what each one checks.
- ✓Why should profiles.yml keep dev, staging/CI, and prod pointed at different databases and warehouses?
- ✓How do dbt's staging and mart models map onto the Raw/Silver/Gold layers from the medallion architecture module?
🎯 Key Takeaways
- ✓dbt organizes Snowflake SQL into version-controlled models, tests, docs, and environments — Snowflake still executes every statement.
- ✓source() declares raw inputs with freshness checks; ref() builds the model dependency graph automatically.
- ✓Materialization (view/table/incremental/ephemeral) controls what DDL/DML dbt compiles a model into.
- ✓Incremental models with the merge strategy compile into Snowflake's native MERGE, guarded by is_incremental().
- ✓Generic tests (unique, not_null, accepted_values, relationships) plus custom SQL tests enforce data assumptions on every run.
- ✓Snapshots track Type 2 slowly changing dimension history — conceptually related to but distinct from Snowflake streams.
- ✓Environments in profiles.yml keep dev/CI/prod pointed at different databases and warehouses; CI runs dbt build before merge.
- ✓dbt's staging models are usually the practical implementation of the Silver layer, and mart models of the Gold layer, from medallion architecture.
Quick check
What does a dbt incremental model with the merge strategy compile to on Snowflake?
HELP US IMPROVE
How was this lesson?
Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
