dbt Explained: Transforming Data With SQL and Software Engineering Practices

Before dbt, analytics transformations often lived in a tangle of stored procedures, scheduled queries and BI tool logic. Nobody knew which table depended on which, changes weren't reviewed, and nothing was tested. dbt (data build tool) brought software engineering practices (version control, modularity, testing, documentation) to SQL transformations, and it became a cornerstone of the modern ELT stack.

#Key takeaways

• dbt handles the T in ELT: it transforms data already loaded into your warehouse, using SQL SELECT statements (ETL vs ELT).
• Models reference each other with ref(), which lets dbt build a dependency graph and run models in the right order.
• Materializations control how models are built: views, tables, incremental tables, ephemeral CTEs.
• Built-in tests, documentation and lineage make data trustworthy and discoverable.
• dbt doesn't extract or load data, and it doesn't schedule itself; pair it with ingestion tools and an orchestrator (or a hosted scheduler).

#How dbt works

A dbt model is a .sql file containing a SELECT statement. dbt wraps it in the DDL needed to create a view or table in your warehouse.

sql-- models/staging/stg_shop__orders.sql
SELECT
    id                              AS order_id,
    customer_id,
    LOWER(status)                   AS status,
    total_cents / 100.0             AS order_total,
    CAST(created_at AS TIMESTAMP)   AS created_at
FROM {{ source('shop', 'orders') }}
WHERE _is_test_order = FALSE

sql-- models/marts/fct_orders.sql
SELECT
    o.order_id,
    o.customer_id,
    c.customer_segment,
    o.order_total,
    o.created_at
FROM {{ ref('stg_shop__orders') }} o
LEFT JOIN {{ ref('dim_customers') }} c USING (customer_id)

{{ ref('...') }} and {{ source('...') }} are Jinja functions. They resolve to the right schema and table names per environment and tell dbt about dependencies. From those references, dbt builds a DAG of your models:

textsource: shop.orders ──▶ stg_shop__orders ──┐
source: crm.customers ─▶ stg_crm__customers ─▶ dim_customers ─▶ fct_orders

dbt build runs models and tests in dependency order.

#Materializations

#Incremental models

For large tables, rebuilding everything each run is slow and expensive. Incremental models process only new data:

sql{{ config(materialized='incremental', unique_key='event_id') }}

SELECT event_id, user_id, event_type, occurred_at
FROM {{ source('app', 'events') }}
{% if is_incremental() %}
  WHERE occurred_at > (SELECT MAX(occurred_at) FROM {{ this }})
{% endif %}

With unique_key, dbt merges updated rows rather than duplicating them. Watch for late-arriving data: use a lookback window (for example, reprocess the last 3 days) when events can arrive late (batch vs stream processing).

#Tests

Declare tests in YAML next to your models:

yamlmodels:
  - name: fct_orders
    description: "One row per order. Excludes test orders."
    columns:
      - name: order_id
        description: "Primary key."
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id

Built-in generic tests cover uniqueness, not-null, accepted values and relationships. Singular tests are custom SQL queries that return failing rows. Packages add many more checks. Tests run as part of dbt build, so bad data can stop the pipeline before it reaches dashboards. See our data quality guide.

#Documentation and lineage

dbt generates a documentation site from your project: model descriptions, column definitions, tests and an interactive lineage graph. When someone asks "where does this revenue number come from?", the answer is a click away, and it's always current, because it's generated from the code.

#Structuring a dbt project

A widely used layout:

textmodels/
  staging/          # 1:1 with source tables: rename, cast, light cleaning
    shop/
      _shop__sources.yml
      stg_shop__orders.sql
  intermediate/     # reusable joins and business logic
    int_orders__joined.sql
  marts/            # business-facing models (facts & dimensions)
    finance/
      fct_revenue.sql
    core/
      dim_customers.sql
      fct_orders.sql

Conventions that help:

• Staging models: one per source table, no joins, consistent naming (stg_<source>__<table>).
• Marts: organized by business domain, modeled as facts and dimensions (star schema modeling).
• Only marts are exposed to BI tools.
• Every model has a primary key test and a description.

#Environments and CI

• Each developer works in their own dev schema.
• Pull requests run changed models and their downstream dependents in a CI schema. "Slim CI" compares against production state to build only what changed.
• Production runs on a schedule via dbt's hosted scheduler, an orchestrator like Airflow (building data pipelines with Airflow), or another scheduler.

This workflow (branch, PR, automated tests, review, merge, deploy) is the core of analytics engineering.

#Macros and packages

Macros are reusable Jinja functions, for example to standardize currency conversion or generate date spines. Packages share macros, tests and models across projects. Use them judiciously: heavy Jinja can make SQL hard to read.

#Semantic layer and metrics

Defining metrics (revenue, active users, churn) once, centrally, and serving them consistently to BI tools prevents "two dashboards, two numbers" problems. dbt's semantic layer and similar tools address this. Even without one, document metric definitions in your marts.

#Best practices

1. Keep business logic in dbt, not in BI tools.
2. Test primary keys and relationships everywhere.
3. Use incremental models for large tables, with lookback windows for late data.
4. Review SQL like application code: readability, naming, performance.
5. Monitor run times and warehouse cost; optimize the slowest models first.
6. Document as you go: descriptions in YAML are cheap and pay off constantly.

#FAQ

#Is dbt an ETL tool?

dbt handles only the transformation step inside your warehouse. Extraction and loading are done by other tools (managed connectors, CDC, custom scripts). Together they form an ELT pipeline.

#Do I need to know Python to use dbt?

No. dbt is primarily SQL plus YAML configuration and some Jinja templating. Some warehouses support Python models in dbt for cases SQL handles poorly, but most projects are SQL.

#Which databases does dbt support?

dbt works through adapters for many warehouses and databases, including major cloud warehouses, lakehouse engines and PostgreSQL. Check adapter maturity for your platform.

#What's the difference between dbt Core and hosted dbt?

dbt Core is the open-source command-line tool. Hosted offerings add a web IDE, scheduling, CI integration, documentation hosting and other features. Many teams run Core with their own orchestrator.