dbt

dbt (data build tool) is an open source data transformation framework that brings software engineering best practices to the world of data analytics. By enabling analysts and data engineers to write modular, reusable SQL transformations with built-in testing, documentation, and version control, dbt has become one of the most transformative tools in the modern data stack.

The core concept of dbt is simple but powerful: write SELECT statements, and dbt handles the rest. Each dbt model is a SQL SELECT statement that defines how to transform source data into a useful table or view. dbt compiles these models and runs them against the data warehouse, materializing the results as tables, views, incremental tables, or ephemeral CTEs based on the configured materialization strategy. This SQL-first approach means that analysts can contribute transformations using skills they already have, without learning a new programming language.

Modularity is a defining strength of dbt. Models can reference each other using the ref() function, which creates explicit dependency relationships between transformations. dbt analyzes these dependencies and executes models in the correct order automatically, ensuring that upstream transformations are always up to date before downstream models run. This DAG-based execution model makes it practical to build complex transformation pipelines that are understandable, maintainable, and parallelizable.

Data testing is built into dbt through a declarative testing framework. Generic tests including not_null, unique, accepted_values, and relationships can be applied to model columns with a single line of YAML configuration. Custom SQL tests can be written for more complex validation logic. When dbt runs, it executes all configured tests and reports failures, enabling data quality gates in production pipelines.

The documentation system in dbt generates a rich, interactive data catalog automatically from the project's models, sources, and tests. Developers add descriptions for models, columns, and metrics in YAML files, and dbt compiles this into a browsable website with lineage graphs, column-level documentation, and test coverage information. This living documentation makes it much easier for data consumers to understand where data comes from and how it is calculated.

dbt sources allow teams to declare the raw tables from external systems that feed their transformations. Source freshness checks can be configured to alert when source data becomes stale, and the lineage graph shows the full data flow from raw sources through all transformation stages to final models. This end-to-end lineage is invaluable for understanding data provenance and diagnosing pipeline issues.

Macros in dbt bring Jinja2 templating and macro definitions to SQL, enabling parameterized SQL, DRY patterns, and custom utility functions that work across any supported database. The dbt package ecosystem on dbt Hub provides hundreds of community-contributed macros and model packages for common data patterns.

dbt Core is the open source command-line tool that runs locally or in CI/CD pipelines. dbt Cloud is the managed service that adds a web-based IDE, scheduled job execution, a visual DAG explorer, audit logging, and enterprise features. dbt is compatible with Snowflake, BigQuery, Redshift, Databricks, PostgreSQL, DuckDB, Spark, and many other data platforms.