First model + test
Create a model that selects from a staging table using ref(), then add not_null and unique tests on its primary key.
Data / Knowledge library
dbt (data build tool)
sql-first transformation workflow for analytics
Why learn dbt (data build tool)?
dbt turns raw warehouse tables into trusted analytics models using version-controlled SQL. It adds testing, documentation, lineage, and deployment patterns so data teams can ship reliable transformations faster.
Where it came from
2016: dbt was open-sourced by Fishtown Analytics. 2019: Fishtown Analytics rebranded to dbt Labs. Early 2020s: dbt helped define the analytics engineering role. Later releases added semantic-layer and mesh-oriented governance patterns.
- 2016: dbt was open-sourced by Fishtown Analytics.
- 2019: Fishtown Analytics rebranded to dbt Labs.
- Early 2020s: dbt helped define the analytics engineering role.
- Later releases added semantic-layer and mesh-oriented governance patterns.
Build your understanding
Work through these exercises in order. Try the task before opening a reference.
- Create a model that selects from a staging table using ref(), then add not_null and unique tests on its primary key.
- Build an incremental model for daily orders and explain when full-refresh is required.
- Design a CI job that only runs modified nodes using state/defer, and list failure gates before merge.
- Define one business metric, document assumptions, and wire it to two downstream models with tests.
- Given a failing relationship test, identify root cause, fix model SQL, and prove pass in dbt build output.
More ways to practice
Incremental pattern
Build an incremental model for daily orders and explain when full-refresh is required.
CI slim build
Design a CI job that only runs modified nodes using state/defer, and list failure gates before merge.
Metric governance mini-project
Define one business metric, document assumptions, and wire it to two downstream models with tests.
Debugging drill
Given a failing relationship test, identify root cause, fix model SQL, and prove pass in dbt build output.
Documentation and sources
- Official docs (docs.getdbt.com)
- dbt Core GitHub (github.com)
- Build dbt models (docs.getdbt.com)
- dbt command reference (docs.getdbt.com)
Catalog research notes
- Recent dbt Core releases expanded unit testing and stronger project validation.
- State-aware selection and deferral workflows improved CI speed for large projects.
- dbt Cloud continued semantic layer adoption for reusable metrics across BI tools.
- Python model support matured across major warehouse adapters.
Connect the ideas
Explore next
Real World Example
Standardizing Regional Logistics Reporting
A global logistics firm managing disparate regional supply chain databases.
The business problem
The firm suffers from inconsistent reporting logic where regional analysts define 'transit time' differently, leading to conflicting executive dashboards and high maintenance overhead for manual SQL scripts.
How this topic solves it
The firm adopts dbt to implement a modular, SQL-first transformation workflow. By centralizing logic into version-controlled models, the team enforces a single source of truth for metrics, ensuring that every regional dashboard consumes the same underlying transformation logic.
- Define raw source tables using dbt source configurations to map regional database schemas.
- Create staging models to perform initial data cleaning and column renaming for consistency.
- Develop intermediate models to join regional transit data and apply standardized business logic.
- Define final mart models that aggregate data for specific executive reporting requirements.
- Utilize dbt state-aware orchestration to ensure only modified models are rebuilt during daily refreshes.
This flow shows how raw regional data is refined into standardized business metrics.
Raw database tables
Cleaned and renamed columns
Standardized business logic
Final reporting tables
- Regional Sources → extracts → Staging Models
- Staging Models → joins → Intermediate Models
- Intermediate Models → aggregates → Executive Marts
Expected business outcome
Improved data consistency across regional teams and reduced time spent debugging conflicting reports, as logic is now centralized and auditable.
Limitations and checks
A significant tradeoff is the complexity of managing dependency graphs as the project scales; developers must monitor the lineage to avoid circular references, which can be checked by reviewing the generated DAG in the dbt Catalog.