Snowflake — Module 17

Snowflake with dbt

dbt project structure, sources, models, tests, snapshots, incremental models, environments, and CI/CD.

85 min September 2026

Data Sharing and Marketplace Production Operations and Monitoring

// Part 01 — Plain-English foundation

Snowflake with dbt From Scratch

dbt helps teams manage Snowflake transformations as version-controlled SQL models with tests, documentation, environments, and deployment workflows.

Why this matters: As SQL logic grows, copy-pasted scripts become risky. Teams need reviews, tests, lineage, reusable models, and clear promotion from dev to prod.

Mental model

Snowflake runs the SQL. dbt organizes the factory: model files, dependency graph, tests, docs, macros, environments, and CI/CD.

// Part 02 — Core concepts

The Concepts You Must Own

✓Sources describe raw inputs and freshness expectations.
✓Models are SELECT statements materialized as views, tables, incremental tables, or ephemeral logic.
✓Tests enforce assumptions such as unique, not_null, relationships, and accepted_values.
✓Snapshots track slowly changing dimensions.
✓Environments separate development, CI, staging, and production targets.

Concept	Meaning	Why it matters
source()	Raw input reference.	Documents ingestion boundaries and freshness.
ref()	Model dependency reference.	Builds lineage and correct run order.
Test	Data assertion.	Stops broken assumptions from silently shipping.
Materialization	How model is built.	View/table/incremental decisions affect cost and performance.
Snapshot	Historical change capture.	Useful for slowly changing dimensions.

// Part 03 — How the work actually flows

Step-by-Step Workflow

✓Define sources for raw Snowflake tables.
✓Create staging models that standardize names and types.
✓Create intermediate and mart models for business logic.
✓Add tests and documentation beside models.
✓Run dbt in CI before deploying production transformations.

Snowflake with dbt example

-- 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,
  total_usd::NUMBER(12,2) AS total_usd
FROM {{ source('raw', 'orders') }}

-- models/marts/fct_daily_revenue.sql
SELECT
  DATE(order_ts) AS order_date,
  SUM(total_usd) AS revenue_usd
FROM {{ ref('stg_orders') }}
GROUP BY 1

-- schema.yml
models:
  - name: stg_orders
    columns:
      - name: order_id
        tests: [not_null, unique]

Do not read the example as magic syntax to memorize. Read it as a production habit: name the objects clearly, make assumptions visible, preserve enough metadata to debug later, and keep the business promise attached to the SQL.

// Part 04 — Mistakes and debugging

Common Mistakes That Break Snowflake Projects

Watch these carefully

✓Writing every model as a table and increasing storage/compute without reason.
✓Having no tests on primary business keys.
✓Letting analysts edit production SQL directly in Snowflake outside Git.
✓Mixing staging cleanup and business metrics in one model.
✓Running dbt with a role that has far more privileges than needed.

How to debug this topic

Start by asking what promise failed: freshness, correctness, access, speed, or cost. Then inspect the Snowflake evidence: query history, warehouse metering, task history, copy history, grants, row counts, and sample records. Good Snowflake debugging is not guessing. It is reading the platform metadata until the failure has a shape.

// Part 05 — Production depth

Production Notes

✓Use separate schemas for dev users to avoid collisions.
✓Use CI to run changed models and tests before merge.
✓Treat exposures and docs as part of the product for BI users.
✓Tune incremental models carefully; wrong unique keys can corrupt facts.

Production standard: A Snowflake design is not complete when the query returns rows. It is complete when the team knows who owns it, how fresh it should be, how access is controlled, what it costs, how to detect failure, and how to recover safely.

// Part 06 — Interview and project readiness

Explain It Like a Professional

dbt with Snowflake brings software engineering practice to warehouse SQL: version control, DAG, refs, sources, tests, docs, macros, snapshots, and deployment. Snowflake executes the SQL while dbt manages transformation structure.

Mini project

Build a dbt project with raw orders/customers sources, staging models, fct_orders, dim_customers, fct_daily_revenue, tests, docs, and a CI command that blocks merges on failed tests.

Questions you should answer out loud

✓How would you explain Snowflake with dbt to a non-technical manager?
✓Which Snowflake objects, roles, or SQL statements does this topic use?
✓What can fail in production and which metadata view would you inspect first?
✓What is the cost or security risk if this is implemented carelessly?
✓How would you test that the result is correct and rerunnable?

🎯 Key Takeaways

✓dbt organizes Snowflake SQL into a tested DAG.
✓Sources and refs make lineage explicit.
✓Tests protect business trust.
✓Materialization choice affects cost and speed.
✓Production dbt needs roles, environments, and CI/CD.

Data Sharing and Marketplace

Production Operations and Monitoring

Discussion

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub