dbt for Data Engineering: A Practical Guide to Building Reliable ELT Pipelines

ELT, Not ETL: Why the Order Flipped

Traditional ETL transforms data in a separate processing layer before loading it into the warehouse, because early warehouses weren't powerful enough to do heavy transformation work themselves. Modern cloud warehouses — Snowflake, BigQuery, Redshift — have made that constraint obsolete: they can execute complex SQL transformations across billions of rows faster than a dedicated ETL server, and they already have the data once it's loaded. ELT loads raw source data into the warehouse first (via Fivetran, Airbyte, or custom extractors), then transforms it in place using SQL.

dbt owns exactly that second step — the 'T' — and deliberately does not handle extraction or loading. This division of labor is why dbt pairs well with a dedicated ingestion tool rather than trying to replace one: dbt's entire value is treating the transformation layer as software, and it does that one job well.

Model Layering: Staging, Intermediate, and Marts

A dbt project without a layering convention turns into a directory of SQL files with no clear ownership or blast radius — one model referencing raw source columns directly, another referencing three other models, with no visible structure. The standard pattern is three layers. Staging models sit directly on top of raw sources: one staging model per source table, doing only light cleanup — renaming columns, casting types, no joins.

Intermediate models join and reshape staging models into reusable building blocks that aren't meant to be queried directly by end users. Mart models are the final, business-facing tables — fct_orders, dim_customers — built for a specific reporting or analytics use case, and these are what BI tools actually query. This layering means a change to a raw source only requires updating one staging model, and its effects propagate predictably through dbt's dependency graph instead of requiring a manual audit of every downstream query.

-- models/staging/stg_orders.sql
select
  id as order_id,
  customer_id,
  cast(order_date as date) as order_date,
  status,
  amount_cents / 100.0 as amount_usd
from {{ source('raw', 'orders') }}

-- models/marts/fct_orders.sql
select
  o.order_id,
  o.customer_id,
  c.customer_name,
  o.order_date,
  o.amount_usd
from {{ ref('stg_orders') }} o
left join {{ ref('stg_customers') }} c on o.customer_id = c.customer_id

Choosing Materializations: View, Table, or Incremental

Every dbt model is materialized as a view, table, or incremental table, and picking the wrong one is a common source of both slow dashboards and runaway warehouse costs. Views recompute on every query — zero storage cost, but every downstream query pays the full transformation cost each time it runs, which is fine for lightweight staging models but painful for anything joining large tables. Tables materialize the full result on every dbt run — fast to query, but every run rebuilds the entire table from scratch, which becomes prohibitively slow and expensive once the source data reaches tens of millions of rows.

Incremental models solve that by only processing new or changed rows on each run, appending or merging them into an existing table instead of rebuilding it — the right choice for large, append-heavy fact tables like events or orders where a full rebuild would take hours and cost real warehouse credits every single run.

Incremental Models Done Correctly

Incremental models are also where most production dbt bugs live, because the is_incremental() logic only runs on subsequent runs, not the first build — meaning a filter that looks correct in testing can behave completely differently in production once the table already exists. The classic mistake is filtering only on the source's updated_at column without a lookback window: if a row's updated_at was set five days ago but the row didn't actually land in the source system until today (common with lagging replication or backfilled data), it silently never gets picked up, and nobody notices until a downstream number doesn't reconcile. A lookback window — reprocessing the last few days on every incremental run, not just strictly new rows — catches late-arriving data at the cost of a small amount of redundant reprocessing, which is almost always worth the correctness guarantee.

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

select
  event_id,
  user_id,
  event_type,
  occurred_at
from {{ source('raw', 'events') }}

{% if is_incremental() %}
  -- lookback window catches late-arriving/backfilled rows,
  -- not just rows strictly newer than the last run
  where occurred_at > (select max(occurred_at) - interval '3 days' from {{ this }})
{% endif %}

Testing: Catching Bad Data Before the Dashboard Does

dbt's built-in generic tests — unique, not_null, accepted_values, and relationships — cover the majority of data quality issues that actually occur in practice: a supposedly unique ID that isn't, a foreign key referencing a customer that doesn't exist, a status column with an unexpected value from an upstream schema change nobody announced. These take one line per column in the model's YAML file and run automatically on every dbt build. Beyond generic tests, custom singular tests — a SQL query that should return zero rows if the data is healthy — catch business-logic violations generic tests can't, like an order total that doesn't match the sum of its line items.

The discipline that matters most: tests should fail the build, not just log a warning that nobody reads, and CI should run dbt test on every pull request so a broken model never merges into the schedule that feeds executive dashboards.

# models/marts/schema.yml
models:
  - name: fct_orders
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: status
        tests:
          - accepted_values:
              values: ['pending', 'shipped', 'delivered', 'cancelled']

Orchestration: dbt Doesn't Schedule Itself

dbt runs transformations; it does not decide when to run them or wait for upstream data to actually be ready. Source freshness checks (dbt source freshness) tell you whether raw data landed on schedule before you waste a run transforming stale data, but something external still needs to trigger the run. Airflow, Dagster, or dbt Cloud's own scheduler are the common choices — Airflow and Dagster suit teams that already orchestrate other jobs (Python ETL scripts, ML pipelines) and want dbt as one node in a larger DAG; dbt Cloud's scheduler is the simpler option for teams whose orchestration needs begin and end with dbt itself.

Whichever is used, the run should be gated on source freshness checks passing first, so a delayed upstream load doesn't silently propagate incomplete data through every downstream model on a fixed schedule regardless of whether the input was actually ready.