What is dbt (Data Build Tool)?

dbt (data build tool) changed analytics engineering the way React changed frontend — it didn't invent the primitives but it packaged them into a workflow that actually worked. Before dbt, SQL transformations lived in stored procedures, Airflow BashOperators running psql files, BI-tool "custom SQL" fields, or personal notebooks — fragmented, untested, undocumented. dbt's move was deceptively simple: treat SQL models as version-controlled code, compile them into a DAG based on {{ ref() }} dependencies, run them in order, and wrap tests + docs around the whole thing. That one conceptual shift turned analysts into analytics engineers and made dbt the de-facto transformation tool in the modern data stack.

What dbt actually is on day-to-day

Day-to-day, dbt is: a folder of .sql files (each a SELECT statement), a dbt_project.yml, a profile config pointing to your warehouse, and the dbt CLI. You run dbt build; it compiles each model, figures out dependencies from ref() calls, materializes models as tables/views/incrementals in the order the DAG demands, runs tests, and produces run results. That's it. No orchestration, no scheduling (use Airflow/Dagster/dbt Cloud), no ingestion (use Fivetran/Airbyte). The narrow scope is the strength — dbt does one thing well.

Field story

A 40-person analytics team at a retail client had 800 models in BigQuery .sql files scattered across a Confluence wiki, with transformations duplicated across dashboards, no tests, and a weekly cadence where the "real" numbers diverged from the "approximate" numbers shown on a Monday morning. We migrated to dbt over three months: consolidated duplicated logic into reusable staging + intermediate + mart layers, added ~2000 tests (unique, not_null, relationships, accepted_values), and wired CI to run dbt build --select state:modified+ on every PR. Ramp time for new analysts dropped from 6 weeks to 1 week (docs site was the biggest reason), three production data bugs were caught by tests in the first month, and dashboard-divergence incidents went to zero because everyone queried the same marts. dbt isn't magic; it's just software engineering applied to SQL. That's enough.

dbt (data build tool) is an open-source command-line tool that enables data analysts and engineers to transform data in their data warehouse more effectively. It brings software engineering best practices like version control, testing, and documentation to analytics workflows.

What Makes dbt Different

Traditional ETL tools require you to write transformations in proprietary languages or drag-and-drop interfaces. dbt takes a different approach:

• SQL-first: Write transformations in pure SQL (or Python in dbt Core 1.3+)
• ELT, not ETL: Assumes data is already loaded; focuses only on the T (transform)
• Modular: Build reusable models that reference each other
• Version Controlled: Store all code in Git for collaboration
• Tested: Define tests to validate data quality
• Documented: Auto-generate documentation from your code

Core Concepts

1. Models: SQL files that define transformations. Each model compiles to a SELECT statement and creates a table or view.

1. Sources: Declare raw tables loaded by your ingestion tools (Fivetran, Airbyte, etc.)

1. Tests: Assertions about your data (unique, not null, relationships)

1. Documentation: Describe models and columns; dbt generates a searchable doc site

1. Macros: Reusable SQL snippets (like functions) using Jinja templating

dbt Core vs dbt Cloud

• dbt Core: Free, open-source CLI tool you run locally or in CI/CD
• dbt Cloud: Managed platform with IDE, scheduling, and collaboration features

Common dbt Commands

dbt run          # Execute all models
dbt test         # Run all tests
dbt docs generate  # Generate documentation
dbt build        # Run + test in dependency order

Why Teams Adopt dbt

• Speed: Analysts can own transformations without waiting for engineers
• Quality: Tests catch data issues before they reach dashboards
• Collaboration: Git-based workflow enables code review and teamwork
• Maintainability: Modular, documented code is easier to understand and update

dbt pitfalls I see at almost every client:

• Overusing view materialization on frequently-queried models. Views re-execute the full SQL on every query; for a model sitting under a dashboard, that's dozens of identical recomputations per hour. Materialize as table or incremental for anything queried often. The inverse mistake — materializing everything as table — wastes compute rebuilding cold models.
• Incremental models that lose late-arriving data. The default is_incremental() filter (WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})) silently misses rows where updated_at is upstream-of-watermark. Use a lookback window (> MAX(updated_at) - interval '3 days') and a merge strategy that handles duplicates.
• {{ ref() }} vs {{ source() }} confusion. ref points to another dbt model; source points to raw ingested tables. Using the wrong one breaks the DAG or creates hardcoded table names. Always source() at the staging layer, always ref() afterwards.
• Tests that always pass. unique on an empty table passes. not_null on an all-null column returns zero failures if the column is added but nothing populates it. Combine tests with expect_table_row_count_to_be_between (via dbt-expectations package) to fail-fast.
• SELECT * in staging models. Feels convenient; means that when a source adds a new PII column, it silently flows downstream without review. Explicit column lists in staging layers force deliberate schema changes.
• Running dbt run without dbt test. dbt build does both in dependency order and stops on test failure, preventing downstream corruption. Using dbt run alone means a broken model's downstreams get rebuilt with bad inputs.
• Jinja macro overuse. It's tempting to abstract everything. Teams end up with {{ my_custom_macro(table, schema, custom_arg, another_arg) }} where a regular SELECT would be clearer. Use macros for genuine DRY wins (pivot helpers, surrogate keys, config-aware filters), not for abstraction's sake.
• Missing on_schema_change on incremental models. Default is ignore; adding a column to an incremental model doesn't backfill it. Set on_schema_change='sync_all_columns' or handle full refreshes explicitly.
• State-based CI without baseline refresh. dbt build --select state:modified+ is great until the baseline manifest in CI is stale and the "modified" selector picks up everything. Refresh state periodically; document the expected failure mode.
• Docs without ownership metadata. dbt docs are a fantastic wiki, but without an owner meta on every model, nobody knows who to call. Add meta: {owner: team-name} as a convention from day one.