dbt, short for data build tool, is an open-source transformation framework that lets analysts and engineers define data models as SQL SELECT statements, which it compiles and runs inside a data warehouse or lakehouse to create tables and views. In data engineering, dbt covers the T in ELT: data is first loaded raw into the platform, and dbt transforms it in place.
dbt brings software engineering practices to analytics code: version control, modular code, automated tests, documentation and environments for development and production. It does not extract or load data and does not store data itself; all computation happens in the target database engine.
How a dbt project works
A dbt project is a directory of SQL and YAML files that describe models, sources, tests and configuration. Each model is a single .sql file containing one SELECT statement, and dbt decides how to persist its result according to the model’s materialization.
Models reference each other with the ref() function and raw tables with source(). From these references, dbt builds a directed acyclic graph (DAG) of dependencies, compiles Jinja templating into plain SQL for the target platform and executes models in dependency order, running independent models in parallel threads.
The main building blocks of a project are summarized below.
| Component | Defined in | Role |
|---|---|---|
| Model | SQL file with one SELECT | Creates a table or view |
| Source | YAML declaration | Names raw tables loaded by other tools |
| Test | YAML or SQL file | Asserts expectations about data |
| Macro | Jinja file | Reusable SQL logic and functions |
| Seed | CSV file | Small static reference data |
| Snapshot | SQL with snapshot config | Records history of changing rows |
Materializations and incremental models
A materialization defines how dbt writes a model to the warehouse. The choice balances freshness, build time and storage cost, and it can be set per model or per folder.
| Materialization | What dbt creates | Best suited for |
|---|---|---|
| view | A database view, no stored data | Light logic, small data, always fresh |
| table | A table rebuilt on every run | Moderate data, heavy downstream queries |
| incremental | A table updated with new or changed rows | Large, append-heavy event data |
| ephemeral | No object; inlined as a CTE | Small helper steps reused by models |
| materialized view | A platform-managed materialized view | Platforms with automatic refresh |
Incremental models process only rows that arrived since the last run, using a filter inside an is_incremental() block and a strategy such as append, merge or delete+insert. They can cut run times from hours to minutes on large fact data, but they require a reliable unique key and an occasional full refresh to repair drift.
Testing, documentation and lineage
dbt treats data tests as part of the codebase. Generic tests such as unique, not_null, accepted_values and relationships are declared in YAML next to the model; each one compiles to a query that returns failing rows, and the test passes when that query returns none.
Custom tests are written as SQL files, and packages add many more. Newer versions also support unit tests, which check model logic against small mocked inputs rather than production data. Tests can be set to warn or error, so critical checks block a deployment while minor ones only alert.
From the same project files, dbt generates documentation with a lineage graph, showing every source, model and downstream exposure. This makes data lineage visible at the column and table level without a separate tool, and the metadata can feed data catalogs.
dbt in the data engineering stack
dbt occupies one layer of the data stack: transformation inside the warehouse. Ingestion tools load data, an orchestrator schedules runs, and BI tools consume the resulting models.
| Concept | Main job | Relationship to dbt |
|---|---|---|
| ETL tool | Transforms before loading | dbt follows ELT and transforms after loading |
| Data ingestion | Copies data from sources | Supplies the raw sources dbt reads |
| Data orchestration | Schedules and monitors jobs | Triggers dbt runs and handles retries |
| Medallion architecture | Bronze, silver, gold layers | Often implemented as dbt model folders |
| Data contract | Agreed schema and guarantees | Enforced with model contracts and tests |
dbt exists as dbt Core, the open-source command-line tool, and as a commercial managed service with a web editor, scheduling and hosted documentation. Adapters connect it to platforms such as Snowflake, BigQuery, Redshift, Databricks and PostgreSQL, and the SQL it emits follows each platform’s dialect.
Practices and common pitfalls
Well-run dbt projects follow a layered model structure, commonly staging, intermediate and marts. Staging models clean and rename one source each, intermediate models combine logic, and marts expose business-ready tables for reporting.
| Problem | Symptom | Remedy |
|---|---|---|
| Hard-coded table names | Broken lineage, wrong environment | Always use ref() and source() |
| Too many full rebuilds | Long runs and high compute cost | Incremental models for large data |
| Missing tests on keys | Silent duplicates in reports | unique and not_null on every key |
| Logic hidden in macros | Hard to read and debug | Keep macros small and documented |
| Unbounded model sprawl | Hundreds of unused models | Ownership, exposures and regular cleanup |
dbt is not a replacement for a streaming engine or an orchestrator, and it adds little value when transformations are a handful of simple queries. Its benefits grow with the number of models and contributors, where shared conventions and tests prevent conflicting definitions of the same metric.
The simple explanation
Picture a kitchen where raw ingredients arrive every morning. dbt is the written set of recipes that turns those ingredients into finished dishes. Each recipe says which ingredients and earlier dishes it needs, so the kitchen always cooks things in the right order.
Every recipe is stored, reviewed and tested before it reaches the customers. The result is that everyone serves the same dish the same way, and mistakes are caught in the kitchen instead of at the table.
How it is used in practice
An online subscription business loads raw data from its billing system, product database and support tool into a cloud warehouse every hour. Before dbt, revenue figures were calculated in three different dashboards with three different results.
The data team builds a dbt project with about 120 models: staging models for each source, intermediate models that join subscriptions and invoices, and a monthly recurring revenue mart. Every key column has unique and not_null tests, and the large event table is an incremental model that processes only the last day of events.
An orchestrator runs the project hourly and stops publication when a critical test fails. Finance, product and sales now read one shared definition of recurring revenue, and a schema change in the billing system is caught by a failing test before any report shows wrong numbers.