ELT transformations with dbt

Generated by Claude through GigAI 0.1.11.10 on 2026-10-11.

Concept: dbt · also covers ETL, Data engineering · Step 24 of 34 in AI forward deployed engineer · version 1

On this page

ELT and where dbt fits
Models, ref, sources and materializations
Project structure: staging, intermediate, marts
Data tests

In ELT, raw data is loaded into the warehouse first and transformed there with SQL. dbt is the standard tool for that transformation step: each model is a select statement, dbt works out the dependency graph, builds the tables and views in order, and runs tests against the results. It brings software engineering practice, meaning version control, modularity, testing and documentation, to analytics code.

docWhat is dbt? (docs.getdbt.com)
docdbt: About dbt models (docs.getdbt.com)
docdbt: Add data tests to your DAG (docs.getdbt.com)

How it is done in practice

A team keeps the dbt project in git with one branch per change. Each developer builds into a personal schema, and a pull request triggers a CI job that builds only modified models and their dependents in a temporary schema and runs their tests. Merges deploy to production, where a scheduler or orchestrator runs the build after ingestion completes. On customer engagements the first weeks often go into staging models and tests over existing raw tables, which surface inconsistencies quickly and give the customer a documented, tested base that outlives the engagement. Test failures route to a channel with a named owner, and generated docs with lineage are published for analysts.

Technologies

dbt Coredbt platformJinjaSQLSnowflakeDatabricksBigQueryFivetranAirbyte

Diagram

flowchart LR
  R1[raw.crm_accounts] --> S1[stg_crm__accounts]
  R2[raw.billing_invoices] --> S2[stg_billing__invoices]
  S1 --> I1[int_accounts_with_invoices]
  S2 --> I1
  I1 --> M1[dim_customers]
  I1 --> M2[fct_invoices]
  T[Data tests: unique, not_null, relationships] -.-> S1
  T -.-> M1
  T -.-> M2

flowchart LR
  R1[raw.crm_accounts] --> S1[stg_crm__accounts]
  R2[raw.billing_invoices] --> S2[stg_billing__invoices]
  S1 --> I1[int_accounts_with_invoices]
  S2 --> I1
  I1 --> M1[dim_customers]
  I1 --> M2[fct_invoices]
  T[Data tests: unique, not_null, relationships] -.-> S1
  T -.-> M1
  T -.-> M2

Code

sql

-- models/staging/crm/stg_crm__accounts.sql
with source as (
    select * from {{ source('crm', 'accounts') }}
)
select
    id as account_id,
    trim(lower(email)) as email,
    cast(created_at as timestamp) as created_at,
    status
from source

yaml

# models/staging/crm/_crm__models.yml
models:
  - name: stg_crm__accounts
    columns:
      - name: account_id
        data_tests:
          - unique
          - not_null
      - name: status
        data_tests:
          - accepted_values:
              values: ['active', 'churned', 'trial']

Subtopics

ELT and where dbt fits

Transform inside the warehouse

Older ETL tools transformed data on a separate server before loading it. Cheap, elastic warehouse compute reversed the order: an ingestion tool lands raw data unchanged, and transformations run as SQL in the warehouse, where they are easy to inspect and rerun. dbt handles only the T. It does not extract or load data, and it does not hold data itself; it compiles your project into SQL and sends it to the platform through an adapter for Snowflake, Databricks, BigQuery, Postgres and others. The open-source dbt Core runs from the command line, and the hosted dbt platform adds scheduling, an IDE and a catalog.

Why it matters for customer work

Transformations become reviewable code in git, not logic hidden in BI tools or stored procedures. A new engineer can read the project, see lineage, and trust tested models, which is exactly what a handover to the customer's team requires.

Models, ref, sources and materializations

A model is a select statement

A SQL model is one file containing one select statement; the file name becomes the model name. You never write create table or insert statements, because dbt wraps the select according to the configured materialization. Reference another model with the ref function and raw tables with the source function. dbt uses those references to build a directed acyclic graph, run models in dependency order, and resolve the correct schema for each environment, so the same code builds into a developer's sandbox or into production. Models can also be written in Python on platforms that support it.

Materializations

View is the default and stores only the query. Table rebuilds the whole result each run. Incremental processes only new or changed rows, which is necessary for large fact tables but requires a reliable unique key and filter. Ephemeral models are inlined as common table expressions. Start with views and tables, and move to incremental only when build time demands it.

Project structure: staging, intermediate, marts

Three layers with distinct jobs

The recommended structure moves data from source-conformed to business-conformed shapes. Staging models map one to one onto source tables and do only light work: renaming, type casting, basic computations and categorization. They are the single place each raw table is touched. Intermediate models combine staging models into purpose-built steps, such as pivoting payments or joining order lines, and are usually not exposed to end users. Marts are the wide, entity-grained tables that consumers query, organized by business area, for example customers and orders in a finance or marketing folder.

Conventions that pay off

Use consistent prefixes such as stg, int, fct and dim, keep folder structure aligned with the layers, and set materialization defaults per folder in the project file. The discipline matters most in a messy warehouse: fixes to a source quirk are made once in staging, and every downstream model inherits them.

Data tests

Assertions that run against real data

A dbt data test is a query that returns failing rows; zero rows means the test passes. Generic tests are parameterized and declared in YAML on a model or column. Four ship with dbt: unique, not_null, accepted_values and relationships, the last checking referential integrity to another model. Singular tests are one-off SQL files in the tests directory for a specific rule, such as no order having a negative total. You can write your own generic tests and install packages of them. Severity can be set to warn or error, and failing rows can be stored for inspection.

What to test first

Put unique and not_null on the primary key of every model, relationships on foreign keys in marts, and accepted_values on status columns. Add source freshness checks so stale loads are caught before models run. The build command runs models and tests together in graph order and skips downstream models when an upstream test fails.

Sources

docWhat is dbt? (docs.getdbt.com)
docdbt: About dbt models (docs.getdbt.com)
docdbt: Add data tests to your DAG (docs.getdbt.com)
guidedbt: How we structure our dbt projects (docs.getdbt.com)

Related lessons

Practice this

Working in customer data platforms: Snowflake and Databricks Data pipeline orchestration with Airflow and Dagster