dbt-transformation-patterns
Write and test dbt models in SQL, with sources and freshness, staging, intermediate and mart layers, incremental and microbatch models, data tests, unit tests and macros, in YAML that is valid for dbt Core 1.10+ and dbt v2. Use when the task is a dbt project or a SQL transformation that runs inside the warehouse. For scheduling dbt runs use airflow-dag-patterns; for Great Expectations or data contracts use data-quality-frameworks.
- License 许可
- MIT
- 最近更新
- 2026-11-10
- Stars
- ★ 0
- Forks
- 0
01 前言 Front Matter
- name
- dbt-transformation-patterns
- description
- Write and test dbt models in SQL, with sources and freshness, staging, intermediate and mart layers, incremental and microbatch models, data tests, unit tests and macros, in YAML that is valid for dbt Core 1.10+ and dbt v2. Use when the task is a dbt project or a SQL transformation that runs inside the warehouse. For scheduling dbt runs use airflow-dag-patterns; for Great Expectations or data contracts use data-quality-frameworks.
- last_verified
- 1793059200000
02 资源文件 1
- 参考文档references/details.md8.3 KB
03 SKILL.md 正文
dbt Transformation Patterns
Build dbt models that are layered, tested and cheap to rebuild. Examples are checked against dbt Core 1.12 with dbt-duckdb.
Procedure
- Check the version. Run
dbt --version, thendbt parse. Fix every deprecation warning it prints: dbt v2 turns them into errors. The YAML here uses the 1.10+ forms:data_tests:instead oftests:, generic test arguments underarguments:, andfreshness,metaandtagsunderconfig:. Use--select;--modelsand-mare gone in v2. - Declare every raw table as a source with a
loaded_at_fieldandfreshness. Models read raw data only through{{ source() }}, and everything else through{{ ref() }}. - Staging: one
stg_<source>__<table>model per source table. Rename, cast, convert units and nothing else: no joins, no filters that drop business rows. Materialize as views. - Intermediate:
int_models hold joins and business logic shared by more than one mart. Skip the layer when a mart is the only consumer. - Marts:
fct_tables hold one row per event anddim_tables one row per entity. State the grain in the model description and test it withuniqueon the key. - Go incremental only when a full rebuild is too slow or costly, measured on the real
table. Pick a
unique_key, reprocess a lookback window for late rows, and setincremental_strategyexplicitly, since the default differs by adapter. For large event tables on dbt 1.9+, preferincremental_strategy='microbatch'. - Test every model:
uniqueandnot_nullon its key,relationshipson foreign keys,accepted_valueson status columns. Add a unit test for any CASE, window or date logic. - Build and verify: run
dbt build --select <model>+in a dev target, then rebuild an incremental model with--full-refreshand compare row counts with the incremental result. - Stop when
dbt buildpasses with no warnings, every model has a key test, and source freshness passes withdbt source freshness.
Report back with the models added or changed and their materialization, the tests on each, and the build commands with their results.
Layers
dbt's own convention is staging, intermediate and marts, a split by purpose. Medallion (bronze, silver, gold) is a Databricks lakehouse convention for storage zones, and the two don't map one to one: a raw source loaded by Fivetran is closest to bronze, and staging models are views over it, not a cleaned copy. Name layers the way the project already does and don't relabel one as the other.
sources (raw, loaded by EL tools)
└─ staging/ stg_stripe__payments view, 1:1 with a source table
└─ intermediate/ int_customer_payments joins and shared logic
└─ marts/ fct_payments, dim_customers tables for BI and consumers
Quick Start
# dbt_project.yml
name: analytics
version: "1.0.0"
profile: analytics
models:
analytics:
staging:
+materialized: view
intermediate:
+materialized: view
marts:
+materialized: table
models/
├── staging/stripe/
│ ├── _stripe__sources.yml
│ ├── _stripe__models.yml
│ ├── stg_stripe__customers.sql
│ └── stg_stripe__payments.sql
├── intermediate/
│ └── int_customer_payments.sql
└── marts/
├── _marts__models.yml
├── dim_customers.sql
└── fct_payments.sql
-- models/staging/stripe/stg_stripe__payments.sql
with source as (
select * from {{ source('stripe', 'payments') }}
)
select
id as payment_id,
customer_id,
cast(amount as numeric(18, 2)) / 100 as amount,
status as payment_status,
cast(created as timestamp) as created_at,
_loaded_at
from source
Best practices
- One source of truth per concept. If two marts compute revenue, move the logic into one intermediate model.
- Keep business logic out of staging. Staging that filters or joins hides data from every downstream model.
- Use
{{ var() }}or the run's context for dates, never a hardcoded date in a model. - Limit dev builds, for example with a
target.name == 'dev'filter on large sources. - Don't test in production. Build in a dev schema and use
--defer --stateto read unchanged parents from production.
References
references/details.md: sources with freshness, the staging, intermediate and mart models of the example project, tests and unit tests in 1.10+ YAML, macros, incremental strategies and microbatch, and the command reference.
04 同仓库更多 Skills
no-description
暂无简介
good-skill
Fixture skill that satisfies every rule.
broken-link
Links to a reference file that does not exist.
Bad_Name
Name violates the kebab-case rule and does not match the directory.
skill-authoring
Write, review, and validate portable agent skills in SKILL.md format. Use when creating a new skill, editing an existing one, or asked whether a skill follows the Agent Skills spec.
weigh-bags
Record scale readings for each bag — empty (tare), outbound, current, final — and reconcile them against the itemised inventory to find unaccounted weight. Use when the user gives a bag weight, says how heavy something is, weighs their luggage, or asks how close to the limit they are. Appends to bags.yaml and runs scripts/reconcile.py.