dbt: SQL Transformations with Version Control and Tests

Overview

My first data pipeline was a directory of SQL files run by a cron job. daily_revenue.sql, user_cohorts.sql, and a shell script that ran them in order and dropped the results into tables. It worked until it didn't — a source table changed a column name, the pipeline ran silently, and the dashboard showed zeros for a week before anyone noticed.

dbt replaces that pattern with something that treats SQL like software: version control, dependency management, tests, documentation, and the ability to run just the models that changed. It's the tool I wish I'd had then.

What dbt actually is

dbt is a framework that takes SQL SELECT statements, wraps them in CREATE TABLE AS or CREATE VIEW AS, and runs them against your data warehouse in dependency order. You write SELECT statements; dbt handles the DDL.

Without dbt	With dbt
Write CREATE TABLE statements	Write SELECT statements, dbt wraps them
Manually order execution	dbt reads `ref()` calls, builds the DAG
No tests	Declarative tests on columns
Documentation in a wiki that drifts	Docs generated from the schema files
Full rebuilds	Incremental models with `WHERE` filters
Environments by copying SQL	One codebase, different targets

The ref() function is the core of it. When model B references model A, dbt knows to run A first. You never worry about ordering.

Project structure

my_dbt_project/
├── dbt_project.yml
├── models/
│   ├── staging/
│   │   ├── _sources.yml
│   │   ├── stg_orders.sql
│   │   └── stg_customers.sql
│   ├── marts/
│   │   ├── _models.yml
│   │   ├── dim_customers.sql
│   │   └── fct_orders.sql
│   └── schema.yml
├── tests/
│   └── assert_positive_revenue.sql
├── macros/
│   └── cents_to_dollars.sql
└── profiles.yml (usually in ~/.dbt/)

The staging/marts split is a convention worth following. Staging models clean up raw data — renaming columns, casting types, filtering deleted rows. Marts are the business-facing tables that analysts query. Keeping them separate means you can change the raw schema without breaking downstream models.

Installation and setup

pip install dbt-core dbt-postgres
# or dbt-snowflake, dbt-bigquery, dbt-duckdb, etc.

dbt init my_project
cd my_project

The profiles.yml file holds connection details:

my_project:
  target: dev
  outputs:
    dev:
      type: postgres
      host: localhost
      user: analyst
      password: "{{ env_var('DBT_PASSWORD') }}"
      port: 5432
      dbname: analytics
      schema: dev
      threads: 4

    prod:
      type: postgres
      host: prod-db.internal
      user: dbt
      password: "{{ env_var('DBT_PROD_PASSWORD') }}"
      dbname: analytics
      schema: prod
      threads: 8

Note the use of env_var for passwords. Never commit credentials to profiles.yml — even if it's outside the project directory, someone will eventually copy it into a repo.

Your first models

-- models/staging/stg_orders.sql
SELECT
    id AS order_id,
    customer_id,
    status,
    amount_cents / 100.0 AS amount,
    created_at::date AS order_date,
    updated_at
FROM {{ source('raw', 'orders') }}
WHERE deleted_at IS NULL

-- models/marts/fct_orders.sql
SELECT
    o.order_id,
    o.customer_id,
    c.name AS customer_name,
    c.region,
    o.amount,
    o.status,
    o.order_date
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('stg_customers') }} c
    ON o.customer_id = c.customer_id
WHERE o.status != 'cancelled'

Two functions doing the heavy lifting:

source() refers to raw tables that dbt doesn't manage. You declare them in a _sources.yml file, and dbt can test their freshness.
ref() refers to other dbt models. This is what builds the dependency graph.

# models/staging/_sources.yml
version: 2

sources:
  - name: raw
    schema: public
    tables:
      - name: orders
        loaded_at_field: updated_at
        freshness:
          warn_after: { count: 6, period: hour }
          error_after: { count: 24, period: hour }
      - name: customers

Freshness tests are worth setting up on day one. They alert you when a source table hasn't been updated, which catches upstream pipeline failures before anyone sees stale dashboards.

Running

# Run all models
dbt run

# Run one model and its dependencies
dbt run --select fct_orders+

# Run everything downstream of a changed staging model
dbt run --select stg_orders+

# Run only staging models in the dev target
dbt run --select staging.* --target dev

# Full refresh of incremental models
dbt run --full-refresh

The + syntax is what makes dbt practical on large projects. Change one staging model, run dbt run --select stg_orders+, and only the models that depend on it get rebuilt. On a project with 200 models, this can turn a 30-minute run into a 2-minute one.

Tests

dbt has four built-in tests that cover most needs, plus the ability to write custom tests as SQL queries.

# models/marts/_models.yml
version: 2

models:
  - name: fct_orders
    description: "All non-cancelled orders, one row per order."
    columns:
      - name: order_id
        description: "Primary key"
        tests:
          - unique
          - not_null
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: amount
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: ">= 0"
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered']

dbt test
# or a specific model
dbt test --select fct_orders

The relationships test is the one I get the most value from. It catches foreign key violations that would otherwise produce silent wrong results in downstream reports.

Custom tests as SQL

When the built-in tests don't cover what you need, write one as a query that returns failing rows:

-- tests/assert_revenue_positive.sql
SELECT
    order_id,
    amount
FROM {{ ref('fct_orders') }}
WHERE amount < 0

If the query returns any rows, the test fails. That's the whole contract.

Incremental models

Rebuilding a 500-million-row fact table on every run is wasteful. Incremental models process only new or changed data:

{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='append_new_columns'
) }}

SELECT
    order_id,
    customer_id,
    amount,
    order_date,
    updated_at
FROM {{ ref('stg_orders') }}

{% if is_incremental() %}
    -- only process rows newer than the latest in the target table
    WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

The {% if is_incremental() %} block is what makes this work. On a full refresh, the filter is skipped. On a normal run, only changed rows are processed.

The unique_key is what makes the merge deterministic. If a row with the same key already exists, dbt updates it instead of inserting a duplicate. This requires the warehouse to support merge semantics — Postgres, Snowflake, BigQuery, and Redshift all do; some others don't.

Macros: reusable SQL

Macros are Jinja functions that generate SQL. They're how you avoid repeating the same logic across models.

-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name, precision=2) %}
    ({{ column_name }} / 100.0)::numeric(10, {{ precision }})
{% endmacro %}

-- Usage in a model
SELECT
    order_id,
    {{ cents_to_dollars('amount_cents') }} AS amount
FROM {{ ref('stg_orders') }}

Once you have three or four models doing the same date manipulation or currency conversion, extract it to a macro. It's the difference between fixing a bug once and fixing it in six places.

Documentation

dbt docs generate
dbt docs serve

This generates a website with every model, its columns, its tests, and the full lineage graph. The lineage graph is the useful part — you can click on any model and see what depends on it and what it depends on.

Keeping the descriptions in the YAML files means they're versioned alongside the code. When a PR changes a model, the reviewer sees the description change too. This is the single biggest improvement over keeping documentation in a wiki that goes stale.

The thing I'd set up first

# dbt_project.yml
name: my_project
version: '1.0.0'
config-version: 2
profile: my_project

model-paths: ["models"]
test-paths: ["tests"]
macro-paths: ["macros"]

models:
  my_project:
    staging:
      +materialized: view
      +schema: staging
    marts:
      +materialized: table
      +schema: marts

The +materialized setting per folder is what most projects get wrong initially. Staging models should be views (they're cheap and always fresh). Marts should be tables (they're queried often and should be fast). Setting this at the folder level means you don't repeat it on every model.

What dbt doesn't do

Extract or load data. dbt assumes data is already in the warehouse. Use Fivetran, Airbyte, or custom scripts for the EL part.
Orchestrate. dbt runs a DAG, but for scheduling across systems, pair it with Airflow, Dagster, or Prefect.
Visualization. dbt builds tables; you still need a BI tool on top.
Real-time processing. dbt is for batch transformations. Streaming is a different problem.

For a team of analysts writing SQL, dbt is transformative. The jump from scripts to versioned, tested, documented transformations is the same jump from shell scripts to a proper application, and it changes what's possible to maintain.

Start with one staging model and one mart. Add tests to the mart. Once you see the value, expand. The learning curve is mostly Jinja syntax and the ref/source distinction, and it's a few hours to get comfortable.