dbt (data build tool) lets you build a warehouse by writing SELECT statements. You describe each table as a query, dbt works out the order to build them in, wraps each query in the right CREATE VIEW or CREATE TABLE, and runs it in Snowflake. In this part we install dbt Core, connect it to Snowflake, declare the RAW tables from part 3 as sources and build our first model.
Code for this part: dbt_project.yml, ci/profiles.yml, _tpch__sources.yml, macros/. Full project: tpch_analytics on GitHub.
Step 1: install dbt Core and the Snowflake adapter
dbt Core is the free, open-source command-line tool. (dbt also sells a hosted platform; everything in this series uses Core.) Install it in a virtual environment and pin the versions so every developer, CI and Airflow run the same code:
requirements.txtdbt-core==1.12.5
dbt-snowflake==1.12.1bashpython -m venv .venv
source .venv/bin/activate # Windows: .venv\Scripts\activate
pip install -r requirements.txt
dbt --versionStep 2: the project file
dbt_project.yml names the project and sets defaults per folder: staging models become views (cheap, always current), marts become tables (fast to query). The +schema settings send each layer to its own schema, and +query_tag labels every query dbt runs so you can find them in Snowflake's query history (part 10).
dbt_project.ymlname: tpch_analytics
version: "1.0.0"
profile: tpch_analytics
model-paths: ["models"]
test-paths: ["tests"]
snapshot-paths: ["snapshots"]
macro-paths: ["macros"]
models:
tpch_analytics:
+query_tag: dbt_tpch_analytics
staging:
+materialized: view
+schema: staging
intermediate:
+materialized: view
+schema: intermediate
marts:
+materialized: table
+schema: martsWe also use the dbt_utils package for a few helpers. Install packages with dbt deps:
packages.ymlpackages:
- git: "https://github.com/dbt-labs/dbt-utils.git"
revision: 1.3.0Step 3: connect to Snowflake
profiles.yml holds connection details, kept separate from the project so credentials never enter git. For development you log in as yourself through the browser (authenticator: externalbrowser), which works with SSO and multi-factor authentication. Each developer gets a personal schema, so you can build and break things without affecting anyone else.
ci/profiles.yml (dev target)tpch_analytics:
target: dev
outputs:
dev: # your laptop: SSO login in the browser
type: snowflake
account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
user: "{{ env_var('SNOWFLAKE_USER') }}"
authenticator: externalbrowser
role: TRANSFORMER
warehouse: TRANSFORM_WH
database: ANALYTICS
schema: "DBT_{{ env_var('SNOWFLAKE_USER') | upper }}"
threads: 8Part 9 adds ci and prod targets to the same file, using the AIRFLOW_SVC key pair from part 2. Point dbt at the folder and check the connection:
bashexport DBT_PROFILES_DIR=ci
export SNOWFLAKE_ACCOUNT=<your_account_identifier> # e.g. myorg-myaccount
export SNOWFLAKE_USER=<your_user>
dbt deps
dbt debug Connection test: [OK connection ok]
All checks passed!Step 4: clean schema names in production
By default dbt combines the target schema and the folder's schema, so your staging models land in DBT_ARAVIND_STAGING. That is exactly right in development. In production we want plain STAGING and MARTS. A small macro overrides dbt's naming rule:
macros/generate_schema_name.sql{#
dev / CI: every model lands in <your schema>_<folder schema>, e.g. DBT_ARAVIND_MARTS
prod: clean names, e.g. MARTS, STAGING
#}
{% macro generate_schema_name(custom_schema_name, node) -%}
{%- if custom_schema_name is none -%}
{{ target.schema }}
{%- elif target.name == 'prod' -%}
{{ custom_schema_name | trim }}
{%- else -%}
{{ target.schema }}_{{ custom_schema_name | trim }}
{%- endif -%}
{%- endmacro %}| Target | Staging models | Marts |
|---|---|---|
| dev (you) | DBT_ARAVIND_STAGING | DBT_ARAVIND_MARTS |
| ci (pull request 42) | CI_PR_42_STAGING | CI_PR_42_MARTS |
| prod | STAGING | MARTS |
Step 5: declare the sources
Models should never hard-code RAW.TPCH.ORDERS. Instead you declare raw tables once as sources and refer to them with source(). That gives you one place to change if a table moves, a lineage graph that starts at the raw data, and freshness checks (configured here, explained in part 6).
models/staging/tpch/_tpch__sources.ymlversion: 2
sources:
- name: tpch
database: raw
schema: tpch
description: TPC-H retail data loaded into the RAW database (part 3).
tables:
- name: orders
loaded_at_field: _loaded_at
freshness:
warn_after: {count: 24, period: hour}
error_after: {count: 48, period: hour}
- name: lineitem
loaded_at_field: _loaded_at
freshness:
warn_after: {count: 24, period: hour}
error_after: {count: 48, period: hour}
- name: customer
- name: nation
- name: region
- name: part
- name: supplierStep 6: the first model
A staging model takes one source table and makes it pleasant to use: clear column names, decoded codes, nothing else. TPC-H's one-letter order status becomes readable values:
models/staging/tpch/stg_tpch__orders.sqlwith source as (
select * from {{ source('tpch', 'orders') }}
),
renamed as (
select
o_orderkey as order_key,
o_custkey as customer_key,
o_orderstatus as order_status_code,
case o_orderstatus
when 'F' then 'fulfilled'
when 'O' then 'open'
when 'P' then 'partially_fulfilled'
end as order_status,
o_totalprice as total_price,
o_orderdate as order_date,
o_orderpriority as order_priority,
o_clerk as clerk_name,
o_shippriority as ship_priority,
_loaded_at
from source
)
select * from renamedbashdbt run --select stg_tpch__orders1 of 1 START sql view model dev_staging.stg_tpch__orders ....................... [RUN]
1 of 1 OK created sql view model dev_staging.stg_tpch__orders .................. [OK in 0.08s]
Finished running 1 view model in 0 hours 0 minutes and 0.21 seconds (0.21s).
Completed successfully
Done. PASS=1 WARN=0 ERROR=0 SKIP=0 NO-OP=0 REUSED=0 TOTAL=1This output is from the local DuckDB run used to test this series, where the dev schema is called dev. On Snowflake you will see your own schema, dbt_aravind_staging or similar, and different timings. To see exactly what dbt sends to Snowflake, use dbt compile: the Jinja is gone and source() has become a real table name. (DuckDB quotes identifiers, as shown; on Snowflake dbt writes them unquoted, raw.tpch.customer.)
bashdbt compile --select stg_tpch__customersselect
c_custkey as customer_key,
c_name as customer_name,
c_address as customer_address,
c_nationkey as nation_key,
c_phone as phone_number,
c_acctbal as account_balance,
c_mktsegment as market_segment
from "raw"."tpch"."customer"Everyday commands
| Command | What it does |
|---|---|
dbt run | Build models |
dbt test | Run data tests |
dbt build | Run, test, snapshot and seed in dependency order; stops downstream of failures |
dbt run --select stg_tpch__orders+ | A model and everything downstream of it |
dbt compile | Show the SQL without running it |
dbt docs generate && dbt docs serve | Browse documentation and lineage (part 6) |
Next
We have one staging model. In part 5 we add the rest of the staging layer, join it into an intermediate model with the business calculations, and build a star schema of facts and dimensions.
The series
- The stack and what we will build
- Setting up Snowflake for dbt
- Loading raw data into Snowflake
- Your first dbt project on Snowflake (this post)
- Modeling with dbt: staging to marts
- Testing and documenting with dbt
- Incremental models and snapshots
- Orchestrating dbt with Airflow
- CI/CD for dbt with GitHub Actions
- Running the pipeline in production
All parts: Data Pipeline Series. Every file shown in this series is in the companion project tpch_analytics on GitHub; clone it to follow along, or run it locally on DuckDB without a Snowflake account.
Comments
Post a Comment