How to Connect Snowflake to dbt

Galaxy Glossary

How do I connect Snowflake to dbt?

Creates a dbt profile that allows the dbt CLI to run transformations against Snowflake.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What packages must be installed?

Run pip install dbt-snowflake. The adapter supplies the Snowflake connection and pushes compiled SQL back to Snowflake’s warehouse.

How do I declare my Snowflake profile?

Create ~/.dbt/profiles.yml. Provide account, user, password or authenticator, role, warehouse, database, and schema. Keep secrets in environment variables for security.

Sample `profiles.yml` entry

snowflake:
target: dev
outputs:
dev:
type: snowflake
account: {{ env_var('SNOWFLAKE_ACCOUNT') }}
user: {{ env_var('SNOWFLAKE_USER') }}
password: {{ env_var('SNOWFLAKE_PASSWORD') }}
role: ANALYST
warehouse: TRANSFORMING_WH
database: ANALYTICS
schema: PUBLIC
threads: 4

How do I reference ecommerce tables in a model?

Create a model file such as models/orders_enriched.sql. Use dbt’s Jinja ref() to stay database-agnostic.

orders_enriched.sql

{{ config(materialized='table') }}
select
o.id as order_id,
c.name as customer_name,
o.order_date,
o.total_amount,
datediff('day', c.created_at, o.order_date) as customer_age_days
from {{ ref('Orders') }} o
join {{ ref('Customers') }} c on o.customer_id = c.id;

How do I test the connection?

Inside the project root, execute dbt debug --target dev. A green check confirms dbt reaches Snowflake with the supplied credentials.

How do I run my first model?

Compile and execute SQL with dbt run --select orders_enriched. The resulting table appears in ANALYTICS.PUBLIC using the warehouse TRANSFORMING_WH.

Best practices for production?

Store secrets in a password manager or CI variable store. Use separate warehouses for dev and prod. Apply role-based access so dbt can only write to its target schema.

Why How to Connect Snowflake to dbt is important

How to Connect Snowflake to dbt Example Usage


-- Sample model: customers_with_lifetime_value.sql

with order_totals as (
    select customer_id, sum(total_amount) as lifetime_value
    from 
    group by customer_id
)
select
    c.id,
    c.name,
    c.email,
    coalesce(ot.lifetime_value,0) as lifetime_value
from  c
left join order_totals ot on c.id = ot.customer_id;

How to Connect Snowflake to dbt Syntax


# profiles.yml skeleton for Snowflake
dbt_profile_name:
  target: dev
  outputs:
    dev:
      type: snowflake          # required
      account: <account_id>    # xyz12345.us-east-1
      user: <your_user>
      password: <your_password>   # or use authenticator=externalbrowser
      role: <role_name>        # optional but recommended
      database: <database>     # e.g., ANALYTICS
      warehouse: <warehouse>   # e.g., TRANSFORMING_WH
      schema: <schema>         # e.g., PUBLIC or DEV_<initials>
      threads: 4               # parallelism
      client_session_keep_alive: false
      query_tag: dbt           # appears in Snowflake history

# Environment variable example (Unix)
export SNOWFLAKE_ACCOUNT=xyz12345.us-east-1
export SNOWFLAKE_USER=dw_user
export SNOWFLAKE_PASSWORD=SuperSecret123!

# dbt CLI commands
pip install dbt-snowflake     # install adapter
dbt debug --target dev        # test connection
dbt run --select orders_enriched

Common Mistakes

Hard-coding passwords in profiles.yml. This exposes credentials in Git. Fix by using {{ env_var('SNOWFLAKE_PASSWORD') }} or a Snowflake key-pair authenticator.
Pointing dbt to the wrong database or schema, leading to accidental writes in production. Always set separate dev and prod targets and verify with dbt debug.

Frequently Asked Questions (FAQs)

Do I need separate warehouses for development?

Yes. Isolating dev workloads prevents long-running transformations from consuming production credits and allows easier cost attribution.

Can I use key-pair authentication instead of passwords?

Absolutely. Replace password with private_key_path and private_key_passphrase in profiles.yml, then set authenticator: snowflake_jwt.

How do I switch between environments?

Create multiple outputs (dev, prod) inside the profile and change target: or pass --target prod to dbt commands.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie