Transformation & Orchestration

dbt as the T in ELT

In the ELT pattern this guide commits to (chapter 03), raw data lands first and is transformed in place by the warehouse or query engine. dbt is the tool that owns that T. Its central, almost-too-simple idea: a model is a SELECT statement in a file, and dbt materializes it as a table or view. You never write CREATE TABLE or INSERT; you describe the shape you want and dbt manages the DDL/DML, the dependency order, and the build.

The reason this reorganized the entire field is governance, not convenience. Before dbt, transformation logic lived in stored procedures, hand-scheduled SQL scripts, and BI-tool "custom SQL" boxes — un-versioned, untested, and impossible to reason about as a system. dbt put transformations in git: code-reviewed, tested in CI, documented, and assembled into a dependency graph that the tool derives automatically. SQL became software.

That graph comes from two functions that are the whole magic:

• source('billing', 'rental_meter') — references a raw landed table declared in a sources: block. The indirection lets you point staging at the real raw schema in one place.
• ref('stg_billing__rental_meter') — references another model. Every ref() is an edge in the DAG. dbt reads the refs, topologically sorts them, and builds in order. You never hand-maintain dependency order; you just write ref() and the graph emerges.

Layering: staging → intermediate → marts, mapped onto medallion

dbt's conventional project layering is the same medallion idea from chapter 06, expressed as folders. Bronze is the raw landed data dbt does not own; staging/intermediate are silver; marts are gold.

• Staging (stg_): exactly one model per source table. Rename to the project's conventions, cast types, coalesce nulls, light dedup. No joins, no business logic. These are the clean atoms.
• Intermediate (int_): the messy middle — fan-out joins, window functions, reusable sub-grains that more than one mart needs. Materialized as ephemeral or views unless reused heavily.
• Marts (fct_, dim_): the business-grain facts and dimensions from chapter 06 — fct_rental_meter, fct_telemetry_hourly, dim_machine, dim_account. This is what analysts, the semantic layer, and ML features read.

A model: building fct_rental_meter

Here is the mart for the billing atom from chapter 06 — one row per rental-hour — built by joining the staged meter to its dimensions. Note there is no DDL: just a config block, refs, and a SELECT.

{{
  config(
    materialized   = 'incremental',
    incremental_strategy = 'merge',
    unique_key     = 'rental_meter_id',
    partition_by   = {'field': 'meter_hour', 'data_type': 'timestamp', 'granularity': 'day'},
    cluster_by     = ['machine_key'],
    on_schema_change = 'append_new_columns'
  )
}}

with meter as (
    select * from {{ ref('stg_billing__rental_meter') }}
    {% if is_incremental() %}
      -- only reprocess recent hours; lookback absorbs late-arriving meter rows
      where meter_hour >= (select dateadd(hour, -36, max(meter_hour)) from {{ this }})
    {% endif %}
),

machine as ( select * from {{ ref('dim_machine') }} ),
account as ( select * from {{ ref('dim_account') }} )

select
    m.rental_meter_id,
    m.meter_hour,
    m.rental_id,
    mc.machine_key,
    ac.account_key,
    m.gpu_seconds,
    m.gpu_seconds / 3600.0                       as gpu_hours,
    m.usd_per_hour,
    round(m.gpu_seconds / 3600.0 * m.usd_per_hour, 4) as gross_revenue_usd,
    m.is_interruptible,
    current_timestamp()                          as _loaded_at
from meter m
left join machine mc on m.machine_id = mc.machine_id and mc.is_current
left join account ac on m.account_id = ac.account_id and ac.is_current

And its companion schema.yml — where dbt's tests and documentation live. The tests run as part of every build and in CI; the descriptions render into the auto-generated docs site and feed the catalog (chapter 09).

models:
  - name: fct_rental_meter
    description: "One row per rental per hour — the billing atom. Reconciles to the ledger."
    columns:
      - name: rental_meter_id
        description: "Surrogate grain key (rental_id + meter_hour)."
        data_tests:
          - not_null
          - unique
      - name: machine_key
        data_tests:
          - relationships:
              to: ref('dim_machine')
              field: machine_key
      - name: gross_revenue_usd
        data_tests:
          - not_null
          - dbt_utils.accepted_range: { min_value: 0 }
      - name: is_interruptible
        data_tests:
          - accepted_values: { values: [true, false] }

Incremental models & strategies

The default materialization rebuilds the whole table every run — fine for dim_account (a small dimension), ruinous for fct_telemetry_hourly built off ~150 M telemetry samples a day (chapter 00's firehose). Incremental models solve this: on the first run dbt builds the full table; on every run after, it processes only new or changed rows and merges them in.

The mechanics are two pieces working together: the is_incremental() macro gates a WHERE clause that filters the source to a recent window, and the incremental_strategy decides how those filtered rows combine with what's already in the table. The unique_key tells merge what counts as "the same row."

Choosing a strategy

Orchestration

dbt builds the graph; something has to run it — on time, in order, with retries, and tell you when it breaks. That is the orchestrator. Its job has five parts:

• Schedule — trigger work on a cadence (cron) or on an event (a file landed, an upstream finished).
• Dependencies — run B only after A succeeds, across tools (CDC → dbt → semantic-layer refresh → ML feature build).
• Retries & alerting — re-run transient failures with backoff; page a human on real ones.
• Backfills — re-run a date range on demand, without hand-editing SQL.
• Observability — run history, durations, lineage, and which run produced which data.

DAG / asset-graph design

Whatever the tool, the graph design principles are the same: explicit dependencies, the right scheduling trigger, partitioned units of work, and — above all — no monolith. A single giant DAG that ingests everything, runs all of dbt, and refreshes every downstream is the most common orchestration anti-pattern: one slow source stalls everything, a retry re-runs work that already succeeded, and the blast radius of any failure is the entire platform.

The GridDP graph is layered to match the data's journey, with each layer a separately schedulable, separately retryable set of assets:

• Dependencies are the ref() edges, surfaced to the orchestrator (Dagster ingests the dbt manifest so each model is an asset with its true upstreams).
• Sensors trigger on events rather than time — e.g. the ledger-reconciliation asset fires when a new billing CDC batch lands, not on a fixed cron, so reconciliation is never stale relative to the data.
• Partitioned assets — fct_telemetry_hourly is partitioned one partition per day. The orchestrator tracks each partition's materialization state independently, so "rebuild June 3rd" is a first-class operation and a failed day doesn't force a full rebuild.
• Data-aware scheduling — attach a freshness policy to the marts and let the orchestrator materialize upstreams on demand, instead of one cron firing the whole chain.

Here is the telemetry rollup as a Dagster partitioned asset — the silver layer that turns the firehose into the per-GPU-hour grain the warehouse can afford to store (chapters 05 and 14):

import dagster as dg

daily = dg.DailyPartitionsDefinition(start_date="2026-01-01")

@dg.asset(
    partitions_def=daily,
    deps=["raw_telemetry_sample"],                      # upstream asset edge
    automation_condition=dg.AutomationCondition.eager(), # materialize when upstream updates
    freshness_policy=dg.FreshnessPolicy(maximum_lag_minutes=90),  # ML wants hourly
    group_name="silver",
)
def fct_telemetry_hourly(context: dg.AssetExecutionContext, clickhouse):
    """One row per GPU per hour: utilization, power, uptime, throttle flags."""
    day = context.partition_key                          # e.g. "2026-06-03"
    # Idempotent by partition: replace this day's rows, never append.
    clickhouse.run(f"""
        INSERT INTO fct_telemetry_hourly
        SELECT machine_id, gpu_index,
               toStartOfHour(event_time)       AS gpu_hour,
               avg(gpu_util_pct)               AS avg_util_pct,
               max(temp_c)                     AS max_temp_c,
               avg(power_w)                    AS avg_power_w,
               countIf(gpu_util_pct IS NULL)   AS missing_samples
        FROM   raw_telemetry_sample
        WHERE  toDate(event_time) = '{day}'              -- one partition
        GROUP  BY machine_id, gpu_index, gpu_hour
    """)
    context.add_output_metadata({"partition": day})

Freshness SLAs & cadence

"How often should this run?" is the wrong first question. The right one is: how stale can each consumer tolerate this data being? Freshness is a property of the consumer's need, and it should drive the schedule — not the other way around. The same lineage serves consumers with wildly different tolerances:

This is exactly where data-aware scheduling pays off. Rather than encode these as five cron expressions you keep in sync by hand, you attach a FreshnessPolicy to each asset (as in the telemetry code above) and let the orchestrator compute the schedule. Finance's daily mart and ML's hourly features can share the same staging models while expressing different SLAs on the marts — the orchestrator builds shared upstreams just often enough to satisfy the tightest downstream consumer.

Backfills & reprocessing

Every long-lived pipeline eventually needs to rebuild the past: a logic bug shipped 30 days ago, a source backfilled corrected data, a new column was added to a mart, or an upstream schema changed. The property that makes this survivable is idempotency: re-running a transform for a given period produces the same result whether it's the first time or the fifth — no double-counting, no drift.

Idempotency is not free; it is designed in. The patterns from earlier sections are exactly what buy it: merge on a unique_key (a re-run overwrites, never duplicates) and insert_overwrite on partitions (a re-run replaces a whole day). The telemetry asset above is idempotent by partition — re-materializing 2026-06-03 cleanly replaces that day's rows. Contrast with a naïve append model: re-running it doubles the data. The rule is blunt: if a transform isn't safe to re-run, it isn't done.

• Partitioned backfills turn "rebuild 30 days" into 30 independent units the orchestrator can run in parallel, retry individually, and track to completion — not one monstrous query.
• Schema-change reprocessing: a new column on fct_rental_meter needs a full backfill to populate history; on_schema_change = 'append_new_columns' (see the model config) lets the column appear without a manual ALTER, then you backfill to fill it.
• Separate logic from schedule: because the transform is parameterized by partition (context.partition_key), backfilling is just running the same code over a different date range — no forked "backfill script" to drift from production.

Testing in the pipeline

Transformation code is code, so it gets tested like code — but data pipelines need a second axis of testing: the data flowing through them, not just the logic. Both run in CI on every pull request and again on every production build.

The data-test pyramid

• Built-in dbt tests — the four generic tests cover most ground: not_null, unique, relationships (foreign-key integrity to another model), and accepted_values (enum guard). They live in schema.yml (see the fct_rental_meter example) and turn assumptions into assertions.
• dbt_utils & packages — dbt_utils.accepted_range, recency (a freshness test), equal_rowcount, and expression tests cover the next tier without writing SQL.
• Custom singular tests — a SQL file that returns failing rows. The classic for GridDP: "sum of ledger debits = sum of credits", the money-correctness assertion promised in chapter 01.
• Unit tests for SQL logic — dbt unit tests feed fixed input rows to a model and assert the output, so you can prove the gross_revenue_usd formula is right against tiny fixtures, without touching warehouse data.

Tests in the pipeline are the bridge into chapter 10: the same assertions, run on a schedule against production and wired to alerts, become the data-quality monitors and SLOs. A test you run in CI to block a bad merge is the same test you run in prod to detect a bad source.

How the three stacks transform & orchestrate

The principles are stack-agnostic; the tooling differs. dbt is the near-universal transform layer across all three (chapter 00's stack table) — what changes is where it runs and what schedules it.

Two patterns are worth noting. In Stack C, Snowflake Dynamic Tables push the data-aware idea into the warehouse itself: you declare a target lag ("refresh to within 1 hour") and Snowflake schedules the incremental refresh — freshness-driven scheduling without a separate orchestrator, at the cost of warehouse lock-in. In Stack B, Delta Live Tables blurs the transform/orchestrate line similarly. The trade-off is the recurring theme of chapter 03: Stack A keeps the orchestrator (Dagster) explicit and portable; B and C absorb more of it into the platform for less operational burden and more lock-in. Ingestion-side, a lightweight tool like DLT (data load tool) can cover the EL for third-party sources in Stack B before dbt does the T.

Takeaway

• dbt makes transformation version-controlled software: models are SELECTs, ref()/source() derive the DAG, and staging→intermediate→marts maps directly onto the medallion layers of chapter 06.
• Incremental models with the right strategy (merge for restating facts, insert_overwrite for partitioned rollups) plus a lookback window are what make the firehose-scale rollups affordable and correct.
• The orchestrator schedules, sequences, retries, and observes — and the modern shift is from task/cron scheduling (Airflow) to asset/freshness-aware scheduling (Dagster): declare the data and its SLA, not the job.
• Design the graph in partitioned, separately schedulable layers; avoid the monolithic DAG. Partitions make backfills first-class.
• Freshness SLAs are downstream of consumer need and become the SLOs of chapter 10; idempotency makes backfills and reprocessing safe; tests in CI are the same assertions that become production monitors.

We now have trustworthy, fresh, well-tested marts. The next question is how internal users — and AI agents — actually consume them without re-deriving every metric. → Serving & the Semantic Layer