dbt (data build tool)

What is dbt?

dbt, short for data build tool, is a SQL-first transformation tool. You write SELECT statements. dbt adds the structure around them: dependencies, materialisation, testing, documentation, lineage, and repeatable runs inside your data warehouse or lakehouse.

The practical result is that analysts can build production-quality transformations without turning every SQL change into an IT ticket. dbt helped popularise the analytics engineer role: someone who writes SQL but works with software habits such as Git, code review, tests, CI/CD, and documentation.

dbt is not an ingestion tool. It does not usually pull data out of your CRM or accounting package. It transforms data after it has landed in a warehouse or lakehouse.

How does dbt work?

A dbt project is usually a Git repository with SQL files, YAML configuration, macros, documentation, and tests.

Models as SELECT statements
Each model is a SQL file. dbt compiles that model into SQL for the target platform and creates the output as a view, table, incremental table, ephemeral model, or materialized view, depending on configuration and adapter support.

ref and source
Instead of hard-coding table names, you use ref to point to another dbt model and source to point to raw source tables. From those references, dbt builds a dependency graph and runs models in the right order.

Jinja templating
dbt uses Jinja to make SQL reusable: macros, loops, environment-specific logic, and repeated column patterns. This is powerful, but it also means teams need conventions before every model becomes a little program.

Materialisations
A model can become a view when you want cheap, always-current logic. It can become a table when users need speed. It can be incremental when only new or changed rows should be processed. One config line can change how the model is persisted.

Tests and documentation

Two features separate dbt from a folder of SQL scripts.

Tests
Common dbt data tests include unique, not_null, accepted_values, and relationships. You declare them in YAML, and dbt turns them into SQL queries that return failing rows. Custom tests can check business rules such as positive margin, expected row counts, or allowed date ranges.

Documentation
In the same YAML files, you can describe models and columns in plain language. dbt can generate a documentation site with model descriptions, column descriptions, tests, and lineage. That site is useful only if someone keeps the descriptions meaningful; empty docs are just a prettier catalog of table names.

dbt Core, dbt Cloud, and Fusion

dbt Core
The open-source command-line tool. It is commonly run locally, in CI, or by an orchestrator. Teams choose Core when they want to own scheduling, hosting, and deployment themselves.

dbt Cloud
The managed platform from dbt Labs. It adds a hosted development experience, scheduler, jobs, environments, CI, documentation hosting, alerts, and integrations. It reduces operations work, but introduces a paid platform.

dbt Fusion
dbt Fusion is dbt Labs' newer Rust-based engine and developer experience. It is designed for faster parsing and deeper SQL understanding, with editor features powered by the dbt language server. Adapter support, feature availability, and product packaging continue to evolve, so teams should check the current dbt docs before planning a migration.

Where does dbt fit?

dbt sits in the transformation layer.

1. Source systems feed raw data into a warehouse or lakehouse through ingestion tools such as Fivetran, Airbyte, Azure Data Factory, Fabric Data Pipelines, or custom connectors.

2. dbt transforms raw data into cleaned, tested, documented models.

3. BI tools, semantic models, reverse ETL jobs, AI applications, or data products consume those models.

dbt works with many analytical platforms through adapters, including Snowflake, BigQuery, Databricks, Redshift, Postgres, DuckDB, and Microsoft Fabric Warehouse through the Fabric adapter.

What to watch out for

Not every transformation belongs in SQL
Heavy Python logic, web scraping, complex ML inference, and file parsing may fit better in Spark, Python jobs, notebooks, or an ingestion layer.

Model sprawl
A dbt project can grow from twenty models to a thousand. Use naming conventions, owners, exposures, lineage review, and cleanup work before the project becomes its own data swamp.

Docs can rot
dbt can generate a documentation site, but it cannot write the business meaning for you. Empty or stale descriptions create false confidence.

CI without tests is theatre
A dbt run that compiles is not the same as data being correct. Add tests for keys, relationships, accepted values, and business assumptions from the beginning.

dbt is not governance by itself
dbt documents and tests transformations. Access control, classification, retention, and policy enforcement live in the warehouse, catalog, governance platform, or surrounding process.