01 - dbt Core Concepts

13 min read

dbt Core Concepts

Quote

“Data engineering is much closer to software engineering than it is to data science.”

Source: Maxime Beauchemin | “The Rise of the Data Engineer” (2017)

What dbt Is (and Is Not)

dbt is the T in ELT. It does not extract data from sources. It does not load data into the warehouse. It transforms data that is already in the warehouse using SQL.

dbt Doesdbt Does Not
Compile Jinja + SQL into executable SQLConnect to source APIs or files
Execute SQL against the warehouseMove data between systems
Build a dependency graph (DAG) from ref() callsSchedule itself (needs Airflow, cron, or CI)
Run tests against dataReplace stored procedures (but can supersede them)
Generate documentation and lineageHandle real-time/streaming data

dbt Core vs dbt Cloud

Factordbt Core (open source)dbt Cloud (SaaS)
CostFree$100+/seat/month
ExecutionCLI, runs anywhereManaged cloud environment
SchedulingYou provide (Airflow, cron)Built-in scheduler
IDEYour editor + CLIBrowser-based IDE
CIYou build (GitHub Actions)Built-in slim CI
State managementYou manage manifest.jsonAutomatic
Best forTeams with Airflow, cost-consciousTeams without orchestration

For this stack

We use dbt Core because Airflow already owns orchestration and GitHub Actions already owns CI/CD. That keeps dbt focused on transformation logic while the surrounding platform handles scheduling, secrets, and deployment workflow.

dbt Compilation Architecture

dbt compiles before executing:

This flow shows how dbt renders Jinja into warehouse-specific SQL before any database object is created.

Your model (Jinja + SQL)
    --> dbt compile
Compiled SQL (pure SQL)
    --> dbt run
Warehouse executes the SQL
    --> Table/view created

Example model stg_daily_prices.sql:

This staging model casts raw columns, filters impossible values, and keeps source-aligned cleanup separate from downstream business logic.

{{ config(materialized='view') }}
 
SELECT
    instrument_isin,
    CAST(price_date AS DATE) AS price_date,
    CAST(close_price AS DECIMAL(18,4)) AS close_price,
    CAST(volume AS BIGINT) AS volume
FROM {{ source('bronze', 'raw_daily_prices') }}
WHERE close_price > 0

After compilation, target/compiled/ contains pure SQL with {{ source() }} resolved to the actual table name.

The dbt DAG

Every dbt project is a Directed Acyclic Graph built automatically from two functions:

  • ref(‘model_name’) — references another dbt model (creates a dependency edge)
  • source(‘source_name’, ‘table_name’) — references an external table (entry point)

This graph example shows how source() anchors the raw-data boundary and how successive ref() calls define the staging-to-mart execution order.

-- stg_daily_prices.sql (reads from source)
SELECT * FROM {{ source('bronze', 'raw_daily_prices') }}
 
-- int_daily_returns.sql (depends on stg_daily_prices)
SELECT *,
    (close_price - LAG(close_price) OVER (
        PARTITION BY instrument_isin ORDER BY price_date
    )) / NULLIF(LAG(close_price) OVER (
        PARTITION BY instrument_isin ORDER BY price_date
    ), 0) AS daily_return
FROM {{ ref('stg_daily_prices') }}
 
-- fct_index_performance.sql (depends on int_daily_returns + int_constituent_weights)
SELECT
    w.index_code,
    r.price_date,
    SUM(r.daily_return * w.weight_pct) AS weighted_return
FROM {{ ref('int_daily_returns') }} r
JOIN {{ ref('int_constituent_weights') }} w
    ON r.instrument_isin = w.instrument_isin
    AND r.price_date = w.price_date
GROUP BY w.index_code, r.price_date

dbt knows to run staging first, then intermediate, then marts — mirroring the medallion-architecture progression from bronze to silver to gold. You never specify execution order — ref() handles it.

Contrast with Airflow

In Airflow, you explicitly define task_a >> task_b >> task_c. In dbt, dependencies are implicit from ref(). Airflow orchestrates when dbt runs; dbt manages the order inside the run itself.

dbt Materializations Overview

MaterializationCreatesWhen to UseStorage Cost
viewSQL viewStaging models, light transformsNone
tablePhysical table (rebuilt each run)Intermediate, small datasetsModerate
incrementalAppends/merges new rows onlyLarge fact tables, daily dataLowest at scale
ephemeralCTE (no object)Helper logic, no persistence neededZero
snapshotSCD Type 2 historyTracking dimension changesModerate

This incremental configuration rebuilds the target once, then applies a date-based cutoff on later runs to limit warehouse work to new facts.

{{ config(
    materialized='incremental',
    unique_key=['instrument_isin', 'price_date']
) }}
 
SELECT ...
FROM {{ ref('stg_daily_prices') }}
 
{% if is_incremental() %}
WHERE price_date > (SELECT MAX(price_date) FROM {{ this }})
{% endif %}

See dbt-materializations for the deep dive with decision matrices.

Required env vars fail at compile time

If SQL_PASSWORD is not set, {{ env_var('SQL_PASSWORD') }} raises a compilation error before dbt opens a warehouse connection. That fail-fast behavior is safer than hiding the problem behind a later authentication error. Only provide a default when fallback behavior is genuinely intended, and keep secret values in environment variables rather than hard-coding them in profiles.yml.

Fail fast on required credentials

Leave required secrets as {{ env_var('SQL_PASSWORD') }} so missing values stop compilation immediately. For non-secret settings that genuinely need a fallback, use an explicit default and cast it to the expected type. Keep dbt debug in CI as a preflight so profile, target, and connectivity problems surface before a real build starts.

dbt Profiles and Targets

profiles.yml defines where dbt connects. Each profile has multiple targets (environments):

This profile maps one project to separate SQL Server and BigQuery targets so the same dbt codebase can compile against different backends without manual relation rewrites.

financial_platform:
  target: dev
  outputs:
    dev:
      type: sqlserver
      server: localhost
      port: 1433
      database: analytics_db
      schema: dbt_dev
      user: "{{ env_var('SQL_USER') }}"
      password: "{{ env_var('SQL_PASSWORD') }}"
      driver: "ODBC Driver 18 for SQL Server"
      trust_cert: true
      threads: 4
 
    prod:
      type: sqlserver
      server: sql-vm.internal
      database: analytics_db
      schema: gold
      threads: 8
 
    bigquery:
      type: bigquery
      method: service-account
      project: data-platform-prod
      dataset: analytics
      threads: 16
      location: EU

Switch targets: dbt run --target prod or dbt run --target bigquery.

dbt Adapters

AdapterPackageDatabase
dbt-sqlserverpip install dbt-sqlserverSQL Server 2016+
dbt-bigquerypip install dbt-bigqueryGoogle BigQuery
dbt-postgrespip install dbt-postgresPostgreSQL

Each adapter handles SQL dialect differences. See dbt-sqlserver-adapter and dbt-bigquery-adapter.

dbt Packages

Declare in packages.yml, install with dbt deps:

This package manifest pins shared macro and testing dependencies so every environment resolves the same project behavior during dbt deps.

packages:
  - package: dbt-labs/dbt_utils
    version: ">=1.0.0"
  - package: calogica/dbt_expectations
    version: ">=0.10.0"
  - package: elementary-data/elementary
    version: ">=0.15.0"

See dbt-packages for the full package guide.

The dbt_project.yml

This root configuration sets project-wide paths, variables, and layer defaults so model behavior stays consistent unless a narrower folder or model override is intentional.

name: financial_platform
version: '1.0.0'
profile: financial_platform
 
model-paths: ["models"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]
 
vars:
  index_universe: ['EURO_STOXX_50', 'GLOBAL_ESG_100']
 
models:
  financial_platform:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: table
      +schema: intermediate
    marts:
      +materialized: table
      +schema: gold

Full refresh rebuilds incremental state

Running dbt run --full-refresh on an incremental model drops the existing relation and rebuilds it from scratch. If the non-incremental path does not select the complete historical dataset, the rebuilt table becomes incomplete even though the command succeeds. Always test full-refresh behavior in a dev target before you use it to recover production drift or schema changes.

Validate the non-incremental path first

Run dbt run --full-refresh --target dev against a representative dataset, then compare row counts, date ranges, and key uniqueness with the trusted relation or source. Gate production full refreshes behind a manual approval step so a broad rebuild is never triggered by an ordinary deployment.

CI should use dbt build

dbt run executes models but does not run tests. dbt build runs seeds, snapshots, models, and tests in dependency order, so a failing upstream test can stop downstream work before more warehouse state is written.

Keep build and test in one DAG-aware command

Replace dbt run && dbt test in CI with dbt build, and combine it with narrow selectors such as state:modified+ when you need slim CI behavior. That keeps the execution graph, test gating, and failure reporting in one command instead of reconstructing the workflow in your orchestrator.

dbt Anti-Patterns

Anti-PatternProblemBetter Approach
Business logic in stagingStaging should be 1:1 with sourceMove logic to intermediate
SELECT * in modelsSchema changes propagate silentlyExplicitly list columns
No ref() (hardcoded tables)Breaks DAG, no dependency trackingAlways use ref() and source()
One giant modelImpossible to test or debugSplit into staging/intermediate/mart
Running without testsBad data reaches productionUse dbt build (runs + tests together)

Graph View

Backlinks