dbt Fundamentals — Transform Data the Modern Way

Before dbt existed, data transformation was painful. Teams wrote thousands of lines of stored procedures, maintained fragile ETL scripts, and had zero version control over their SQL. If a transformation broke, there was no test suite, no lineage graph, and no way to know what downstream reports were affected.

dbt changed everything. It brought software engineering best practices — version control, testing, documentation, modularity — to the SQL transformations that power analytics. Today, dbt is the de facto standard for the “T” in ELT, used by thousands of companies from startups to enterprises.

What dbt actually does

Think of dbt as a compiler for your analytics SQL. You write SELECT statements. dbt handles everything else — creating tables, managing dependencies, running tests, generating docs.

Here is the mental model: your data warehouse already has raw data loaded by ingestion tools (Fivetran, Airbyte, custom scripts). dbt sits on top of the warehouse and transforms that raw data into clean, modeled tables that analysts and dashboards consume.

Raw Data (landing zone)
    ↓  dbt models (SQL + Jinja)
Staging Layer (cleaned, renamed)
    ↓  dbt models
Intermediate Layer (business logic)
    ↓  dbt models
Marts Layer (final tables for consumption)

dbt does not extract or load data. It only transforms data that is already in your warehouse. This is a deliberate design decision — it lets dbt focus on doing one thing exceptionally well.

dbt Core vs dbt Cloud

dbt Core is the open-source command-line tool. You install it with pip, write your models locally, and run them with dbt run. You manage your own scheduling (Airflow, cron, GitHub Actions) and infrastructure.

dbt Cloud is the managed SaaS platform built on top of Core. It adds a web IDE, job scheduling, CI/CD integration, semantic layer, and a hosted documentation site.

Feature	dbt Core	dbt Cloud
Cost	Free (open-source)	Free tier + paid plans
IDE	Your editor (VS Code)	Web IDE + VS Code extension
Scheduling	BYO (Airflow, cron)	Built-in scheduler
CI/CD	Manual setup	Slim CI built-in
Documentation	Self-hosted	Hosted docs site
Semantic layer	Not included	MetricFlow integration

Which should you pick? If you are a solo engineer or small team comfortable with the command line and already have an orchestrator, dbt Core gives you full control at zero cost. If you want managed infrastructure, CI on pull requests, and a web IDE for analysts, dbt Cloud reduces operational overhead significantly.

Project structure

A dbt project is a directory with a specific structure. Here is what a well-organized project looks like:

my_dbt_project/
├── dbt_project.yml          # Project configuration
├── profiles.yml             # Connection credentials (not in repo)
├── models/
│   ├── staging/             # 1:1 with source tables
│   │   ├── _staging.yml     # Source + model definitions
│   │   ├── stg_orders.sql
│   │   └── stg_customers.sql
│   ├── intermediate/        # Business logic joins
│   │   └── int_orders_enriched.sql
│   └── marts/               # Final consumption tables
│       ├── fct_orders.sql
│       └── dim_customers.sql
├── seeds/                   # CSV files loaded as tables
│   └── country_codes.csv
├── snapshots/               # SCD Type 2 tracking
│   └── snap_customers.sql
├── macros/                  # Reusable Jinja functions
│   └── cents_to_dollars.sql
├── tests/                   # Custom data tests
│   └── assert_positive_revenue.sql
└── analyses/                # Ad-hoc queries (not materialized)
    └── monthly_revenue.sql

Each directory serves a clear purpose. This is not arbitrary — it reflects a layered transformation approach that scales from 10 models to 1,000.

Models — the core of dbt

A model is a SQL file containing a single SELECT statement. That is it. No CREATE TABLE, no INSERT INTO, no DDL. dbt generates all the DDL for you based on the materialization strategy.

-- models/staging/stg_orders.sql
WITH source AS (
    SELECT * FROM {{ source('raw', 'orders') }}
),

renamed AS (
    SELECT
        id              AS order_id,
        user_id         AS customer_id,
        created_at      AS ordered_at,
        status,
        amount / 100.0  AS total_amount_dollars
    FROM source
)

SELECT * FROM renamed

This model does three things that matter: it references the source with {{ source() }} instead of hardcoding a table name, it renames columns to follow a consistent convention, and it converts cents to dollars. Simple, readable, testable.

Sources — declaring your raw data

Sources tell dbt where your raw data lives. You define them in YAML:

# models/staging/_staging.yml
version: 2

sources:
  - name: raw
    database: analytics
    schema: raw_stripe
    tables:
      - name: orders
        loaded_at_field: _etl_loaded_at
        freshness:
          warn_after: { count: 12, period: hour }
          error_after: { count: 24, period: hour }
      - name: customers
      - name: payments

The freshness block is powerful — dbt source freshness checks whether your raw tables have been updated recently. If orders have not been loaded in 24 hours, dbt raises an error before any transformations run. This catches ingestion failures early.

Seeds — small reference data

Seeds are CSV files that dbt loads into your warehouse as tables. Use them for small, rarely-changing reference data:

# seeds/country_codes.csv
code,name,region
US,United States,North America
GB,United Kingdom,Europe
DE,Germany,Europe
JP,Japan,Asia Pacific

Run dbt seed to load them. Do not use seeds for large datasets — they are meant for lookup tables with a few hundred rows, not production data loads.

Snapshots — tracking changes over time

Snapshots implement Slowly Changing Dimension Type 2. They track how a record changes by creating new rows with validity timestamps:

-- snapshots/snap_customers.sql
{% snapshot snap_customers %}

{{
    config(
        target_schema='snapshots',
        unique_key='customer_id',
        strategy='timestamp',
        updated_at='updated_at',
    )
}}

SELECT * FROM {{ source('raw', 'customers') }}

{% endsnapshot %}

After running dbt snapshot, you get a table with dbt_valid_from and dbt_valid_to columns. You can query what a customer’s address was on any historical date.

The ref() function — dependency management

The ref() function is the backbone of dbt. When you write {{ ref('stg_orders') }}, dbt does two things:

1. Resolves the table name — handles schema prefixes, environment-specific schemas, and database references automatically.
2. Builds a dependency graph — dbt knows that if model A references model B, B must run first.

-- models/marts/fct_orders.sql
SELECT
    o.order_id,
    o.ordered_at,
    o.total_amount_dollars,
    c.customer_name,
    c.customer_segment,
    p.payment_method,
    p.payment_status
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('dim_customers') }} c
    ON o.customer_id = c.customer_id
LEFT JOIN {{ ref('stg_payments') }} p
    ON o.order_id = p.order_id

dbt parses all ref() calls to build a Directed Acyclic Graph (DAG). When you run dbt run, it executes models in the correct topological order. If stg_orders and dim_customers have no dependency between them, dbt runs them in parallel. fct_orders waits until both are complete.

This is why you must never hardcode table names in dbt models. Always use ref() for models and source() for raw tables.

Materialization strategies

Materialization determines how dbt persists your model in the warehouse. This decision has major performance and cost implications.

View (default)

-- models/staging/stg_orders.sql
{{ config(materialized='view') }}

SELECT * FROM {{ source('raw', 'orders') }}

A view is just a saved query — no data is stored. Every time someone queries stg_orders, the warehouse re-executes the SQL. Views are free to create but cost compute on every read.

Use for: Staging models, simple transformations, tables queried infrequently.

Table

{{ config(materialized='table') }}

Creates a physical table. Data is materialized once during dbt run. Reads are fast because the data is pre-computed, but the entire table is rebuilt on every run.

Use for: Marts and fact tables queried frequently, models with complex transformations.

Incremental

-- models/marts/fct_events.sql
{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge'
) }}

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    properties
FROM {{ source('raw', 'events') }}

{% if is_incremental() %}
WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }})
{% endif %}

Incremental models only process new or changed data. The is_incremental() block adds a WHERE clause that filters to records newer than what already exists. The unique_key handles upserts — if an event_id already exists, it gets updated.

Use for: Large fact tables (billions of rows), event data, anything too expensive to rebuild fully.

Watch out: Incremental models add complexity. Late-arriving data, schema changes, and backfills all require careful handling. Use dbt run --full-refresh to rebuild from scratch when needed.

Ephemeral

{{ config(materialized='ephemeral') }}

Ephemeral models are not materialized at all — they are inlined as CTEs into downstream models. Think of them as reusable SQL snippets.

Use for: Simple transformations referenced by multiple models, when you want DRY SQL without creating warehouse objects.

Testing in dbt

dbt tests are SQL queries that should return zero rows. If rows are returned, the test fails.

Schema tests (declarative)

# models/marts/_marts.yml
version: 2

models:
  - name: fct_orders
    description: "One row per order with enriched customer and payment data"
    columns:
      - name: order_id
        description: "Primary key"
        tests:
          - unique
          - not_null
      - name: total_amount_dollars
        tests:
          - not_null
          - dbt_utils.accepted_range:
              min_value: 0
              max_value: 100000
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id

The four built-in tests — unique, not_null, accepted_values, relationships — cover most common quality checks. The dbt_utils package adds range checks, recency tests, and more.

Custom data tests

For business-specific rules:

-- tests/assert_orders_have_positive_revenue.sql
-- This test fails if any completed order has zero or negative revenue
SELECT
    order_id,
    total_amount_dollars
FROM {{ ref('fct_orders') }}
WHERE status = 'completed'
  AND total_amount_dollars <= 0

Test severity and thresholds

Not every test failure should block your pipeline:

- name: email
  tests:
    - not_null:
        severity: warn    # Log warning, don't fail the run
    - unique:
        severity: error   # Fail the run
        error_if: ">100"  # Only fail if more than 100 duplicates
        warn_if: ">10"    # Warn if more than 10

Run tests with dbt test. In production, always run tests after dbt run — catch quality issues before dashboards refresh.

Documentation generation

dbt generates a complete documentation website from your YAML descriptions and model SQL:

models:
  - name: fct_orders
    description: >
      Order fact table. One row per order. Joins orders with customer
      dimensions and payment data. Grain: one row per order_id.

      **Business rules:**
      - Only includes orders with status != 'cancelled'
      - Revenue is in USD, converted from cents at extraction time
    columns:
      - name: order_id
        description: "Unique order identifier from the source system"
      - name: total_amount_dollars
        description: "Order total in USD. Converted from cents during staging."

Run dbt docs generate followed by dbt docs serve to launch an interactive site with:

• Searchable model catalog
• Column-level descriptions
• Full DAG visualization (lineage graph)
• Source freshness status

This is one of dbt’s killer features. Documentation lives next to the code, updates automatically, and includes lineage for free. No separate wiki to maintain.

Jinja templating — SQL with superpowers

dbt uses Jinja, a Python templating language, to make SQL dynamic. This is what separates dbt from plain SQL files.

Variables and control flow

-- Dynamic date filtering
SELECT *
FROM {{ ref('stg_events') }}
WHERE event_date >= '{{ var("start_date", "2024-01-01") }}'

-- Conditional logic
SELECT
    order_id,
    {% if target.name == 'prod' %}
        customer_email,        -- Include PII in production
    {% else %}
        MD5(customer_email) AS customer_email_hash,  -- Hash in dev
    {% endif %}
    total_amount
FROM {{ ref('stg_orders') }}

Macros — reusable SQL functions

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

Use it in any model:

SELECT
    order_id,
    {{ cents_to_dollars('amount_cents') }} AS amount_dollars,
    {{ cents_to_dollars('tax_cents') }} AS tax_dollars
FROM {{ source('raw', 'orders') }}

Loops for DRY SQL

-- Generate CASE WHEN for multiple payment methods
SELECT
    order_id,
    {% for method in ['credit_card', 'debit_card', 'paypal', 'bank_transfer'] %}
    SUM(CASE WHEN payment_method = '{{ method }}'
         THEN amount ELSE 0 END) AS {{ method }}_amount
    {{ "," if not loop.last }}
    {% endfor %}
FROM {{ ref('stg_payments') }}
GROUP BY order_id

Jinja is powerful but use it sparingly. Overly-templated SQL becomes hard to read and debug. If your model has more Jinja than SQL, consider whether a Python model or macro refactor would be cleaner.

Running dbt in production

A typical production workflow:

# Full pipeline run
dbt deps          # Install packages
dbt seed          # Load reference data
dbt snapshot      # Capture SCD changes
dbt run           # Execute all models
dbt test          # Validate data quality

# Targeted runs
dbt run --select marts.*           # Only run mart models
dbt run --select +fct_orders       # fct_orders and all upstream
dbt run --select fct_orders+       # fct_orders and all downstream
dbt run --select tag:daily         # Only models tagged 'daily'

The --select syntax is essential for large projects. Running 500 models takes time — targeted runs let you execute only what changed.

Next steps

• Pipeline Testing — go deeper into testing strategies beyond dbt’s built-in tests.
• Pipeline CI/CD — automate dbt runs with CI/CD pipelines.
• Data Quality & Governance — the quality framework dbt tests are part of.
• Data Observability — monitor your dbt models in production.