dbt vs Snowflake: what each one does and why teams use both

Snowflake stores your data and runs the compute. dbt builds the SQL models that run inside Snowflake. They are not alternatives, and teams often use both.

Written by Linus Tse, founder of Greyfield Data

ยท 9 min read

On this page 12 sections

dbt and Snowflake are not competitors. Snowflake is the data platform: it stores your tables and runs the compute. dbt is the transformation tool: you write SQL select statements as models, and dbt runs them inside Snowflake to build the tables and views your reports read.

We build and clean up dbt projects on Snowflake as part of our dbt consulting, such as the eight source systems we modeled for a national legal organization. We also publish some of that work as open source, like our Klaviyo dbt models for Snowflake, which the examples below draw on.

Key takeaways

  • Snowflake stores data and runs queries on warehouses billed in credits. dbt does not store data and has no compute of its own; every model it builds runs on a Snowflake warehouse.
  • dbt does not load your sources. An ingestion tool or script puts raw data in Snowflake, and dbt transforms what is already there.
  • dbt connects to Snowflake through the dbt-snowflake adapter and a profiles.yml connection, which can use key-pair authentication.
  • dbt models are views by default. On Snowflake you can also build tables, incremental tables, ephemeral CTEs and dynamic tables.
  • A small team with a handful of reports may not need dbt yet. Version-controlled SQL views can be enough until logic repeats or breaks.

Quick answers

  • Is dbt a database? No. It compiles SQL and sends it to Snowflake.
  • Does Snowflake replace dbt? No. Snowflake runs the SQL; dbt organizes, tests, documents and orders it.
  • What connects them? The dbt-snowflake adapter and a profiles.yml connection.
  • When to add dbt: when the same logic lives in several places, or a bad load reaches a report before anyone notices.

What is the difference between dbt and Snowflake?

Snowflake is where the data lives and where the work runs, and dbt is how you define that work. Snowflake bills you for storage and for warehouse time, while dbt is a framework that turns your SQL files into objects in Snowflake, in the right order, with tests.

dbt's own introduction says it "transforms raw warehouse data into trusted data products" and "works alongside your ingestion, visualization, and other data tools." It does not extract or load data.

Snowflake dbt
What it is Cloud data platform Transformation framework
Stores data Yes, in databases, schemas and tables No
Runs compute Yes, on virtual warehouses billed per second No, it sends SQL to Snowflake
Loads sources Yes, with COPY INTO, Snowpipe, or a connector you add No
You write SQL, run in worksheets, scripts or tools SQL select models, YAML tests and docs
Dependencies between steps You manage the order ref() builds a graph and runs models in order
Testing You write your own checks Built-in data tests such as unique and not_null
Cost Credits and storage Free to run locally; a paid hosted platform is optional

How does dbt work with Snowflake?

dbt compiles each model into SQL, connects to Snowflake as a user, and runs that SQL on a warehouse. The output is a view or table in a schema you choose.

The flow in a typical project looks like this, and it matches the bronze, silver and gold layers we walk through with an ecommerce order:

  1. A loader writes raw tables into a RAW database in Snowflake, like our open-source Klaviyo to Snowflake sync.
  2. dbt staging models clean and rename those tables, one per source table.
  3. dbt reporting models join them and define each metric once.
  4. BI tools such as Sigma read the reporting models.

In our Klaviyo project, two staging views clean raw profiles and events, and four mart tables build a profiles dimension, an events fact, engagement flags and a mailable list. Every model has tests in the YAML file next to it.

What are dbt Core, dbt v2 and the dbt platform?

dbt now has two generations and a hosted platform. Per dbt's introduction, v1 is "the original Python-based CLI", long known as dbt Core, and v2 is the current Rust-based generation and the default when you install dbt.

Option What it is Where it runs
dbt v1 (dbt Core) Original Python CLI, installed with the adapter, for example pip install dbt-core dbt-snowflake Your laptop, a server, or a CI job
dbt v2 Current Rust-based engine, the default install Your laptop, a server, or a CI job
dbt platform Hosted scheduling, CI/CD, docs, monitoring and alerting; works with v1 and v2, with plans from a free Developer tier up (pricing) dbt Labs' cloud
dbt Projects on Snowflake Runs dbt inside Snowflake with EXECUTE DBT PROJECT or snow dbt execute, scheduled with Snowflake tasks (docs) Snowflake, on your warehouse

Our open-source Klaviyo models install with pip install dbt-core dbt-snowflake, the v1 route. Whichever you pick, the models are the same SQL files, and the compute is always your Snowflake warehouse.

How do you connect dbt to Snowflake with key-pair authentication?

You add a Snowflake target to profiles.yml with the account, user, role, warehouse, database and schema, plus private_key_path instead of a password. These field names come from dbt's Snowflake setup page; the values are placeholders.

my_project:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: myorg-myaccount
      user: DBT_SVC
      role: TRANSFORMER
      private_key_path: /home/you/.snowflake/keys/dbt_svc.p8
      private_key_passphrase: "{{ env_var('DBT_KEY_PASSPHRASE') }}"
      database: DEV
      warehouse: TRANSFORM_WH
      schema: DBT_YOURNAME
      threads: 4

A few notes from the docs and from running it:

  • Key format. dbt recommends PKCS#8 with AES-256 encryption, and dbt v2 does not support legacy 3DES or headerless keys. Snowflake's key-pair guide has the openssl commands.
  • Inline key. You can pass private_key as a string instead of private_key_path.
  • Passwords. dbt's docs note that Snowflake is deprecating single-factor password login, so use key pairs for service users and MFA or SSO for people.
  • Separate dev and prod. Each developer builds into their own schema, like DBT_YOURNAME above, and only scheduled runs write to production schemas.

For the role and warehouse layout behind TRANSFORMER and TRANSFORM_WH, see how I set up a new Snowflake account. Then run dbt debug to check the connection, and dbt build to run models and tests.

Which dbt materializations work on Snowflake?

On Snowflake you choose between view, table, incremental, ephemeral and dynamic table. dbt's materializations page says models are views by default, and the adapter's dynamic tables follow dbt's materialized view pattern on Snowflake.

Materialization What Snowflake gets Use it for
view (default) A view, rebuilt each run Staging models and light logic
table A table, rebuilt each run Reporting models that many dashboards query
incremental A table where each run adds or updates only new rows, with merge as the default strategy Large event tables, such as our Klaviyo events fact
ephemeral Nothing; the SQL is inlined as a CTE Small helper steps
dynamic_table A Snowflake dynamic table with a target_lag Tables Snowflake should keep fresh on a schedule

Two Snowflake details from dbt's Snowflake configs page are worth knowing. Tables dbt creates on Snowflake are transient by default, and a dynamic_table model needs a snowflake_warehouse config.

{{ config(
    materialized='dynamic_table',
    snowflake_warehouse='TRANSFORM_WH',
    target_lag='1 hour'
) }}

select * from {{ ref('stg_orders') }}

Our Klaviyo events fact is incremental and reprocesses the last few days each run, so late events still land. If you change the logic, a dbt build --full-refresh rebuilds older rows.

Why use dbt with Snowflake instead of plain SQL?

dbt adds the things plain SQL scripts lack once a project grows: run order, tests, documentation and code review. Snowflake runs SQL very well, but it does not know that your revenue model depends on three staging tables.

  • Order. ref('stg_orders') tells dbt what to build first, so you never run scripts by hand in sequence.
  • Tests. A unique test on order_id catches a duplicate load before a dashboard shows double revenue.
  • One definition. Each metric lives in one reporting model, so Sigma, Excel and AI tools read the same number. A semantic layer or Snowflake semantic views can sit on top of those models.
  • Review. Models are files in git, so changes get reviewed like code.

When does a small team not need dbt yet?

A team with one or two sources and a few reports can wait. If the logic fits in a handful of Snowflake views that one person maintains in git, dbt adds setup and a tool to learn before it saves time.

Situation Start with Add dbt when
One source, a few dashboards SQL views in Snowflake, saved in git The same logic is copied into a second place
Logic lives in BI tool formulas Move it into views Several views depend on each other
A load breaks reports silently A row-count check after each load You need tests on many tables
Daily refresh of a few tables Snowflake tasks or dynamic tables Run order across many models gets hard to manage

The signs that it is time are the ones on our dbt consulting page: the same metric disagrees between dashboards, business logic sits in one person's SQL, or a source change breaks reports and nobody finds out. To plan when loads and models should run each morning, the daily data pipeline schedule template lays out the order.

How do you start a dbt project on Snowflake?

Install dbt with the Snowflake adapter, create a profile, and build one staging model and one reporting model with tests. These are the steps our Klaviyo project's README uses.

python3 -m venv .venv && . .venv/bin/activate
pip install dbt-core dbt-snowflake
dbt init my_project        # or write profiles.yml by hand
dbt debug                  # checks the Snowflake connection
dbt build                  # runs models and tests in order

Our Klaviyo dbt models are a working example to copy from: staging views, incremental facts, seeds and tests. They also run on a local DuckDB target with synthetic data, so you can see every model's output before you touch Snowflake.

What to do next

If you are on Snowflake and the same metrics are calculated in several places, start with one staging model and one tested reporting model for that metric. Read how to use Snowflake for the platform side, or clone our Klaviyo project to see a full dbt layout. If you want a project set up or cleaned up, see dbt consulting or book a call.

Frequently asked questions

Is dbt an alternative to Snowflake?

No. Snowflake stores the data and runs the compute, and dbt writes and orders the SQL that runs inside Snowflake. Teams on Snowflake use dbt to build the tables their reports read.

What is dbt in Snowflake?

dbt is a transformation framework that connects to Snowflake through the dbt-snowflake adapter and builds views and tables from SQL select models. Snowflake also offers dbt Projects on Snowflake, which runs dbt inside Snowflake.

Why use dbt for Snowflake?

dbt adds run order, tests, documentation and git review to SQL that would otherwise live in loose scripts. It helps most when several models depend on each other or the same metric shows up in several reports.

Does dbt load data into Snowflake?

No. An ingestion tool, Snowpipe or a script loads raw data, and dbt transforms data that is already in Snowflake. dbt seeds can load small CSV lookup files, such as a metric map.

Is dbt free to use with Snowflake?

Running dbt yourself is free, and the dbt platform has a free Developer plan plus paid plans, per dbt's pricing page. Either way, every model runs on your Snowflake warehouse, so you pay Snowflake for the compute.

Part of the How to Use Snowflake, a reading path through related articles.

Linus Tse

Founder of Greyfield Data. Built 400+ dashboards. Certified in Sigma, Snowflake, Tableau, and Power BI.

About Linus

โ† All articles