Home › Data engineering › Gold data modelling › Silver to gold › The fact in SQL and dbt

Silver to gold · Decide · 10 min read

The same fact in SQL and in dbt, and our harness against dbt

The business rule is a few lines of SQL. This page shows it in SQL and in dbt, what each tool does around it, and where they differ. This is one page of Silver to gold.

Part D · 1

Which part of the harness uses each setting

To keep this table simple, every harness step before the build counts as reader and every step after it as writer. That puts key lookup, completeness, checks and restatements on the writer side, because they all happen between the build and the write. ✓ means the part reads the setting. ○ means the part must honour it but is never given it.

SettingWhat it says hereReaderProcessorWriterHow it is used
nameWhich table this is✓✓Reader finds its bookmarks under this name; writer writes the run log and moves them
grainOne row per order line○✓Writer checks it is unique before writing; the build must produce it but is never given it
window_colRebuild by promised date✓✓Reader turns changed rows into days; writer replaces exactly those days
lookback_days35 days✓Reader refuses windows older than this; they need a correction plan
driverorder_lines carries the window column✓Reader reads this input by window
inputs → bookmark_seq on each input✓✓Reader reads changes after it; writer moves it to the high mark, last
inputs → window_viaA delivery's window is its line's✓Reader maps a changed delivery to its line's day
filtersNo test dealers✓✓Reader applies and counts them; writer's ledger balance uses the count
measuresqty_ordered, qty_delivered, otif✓Writer compares them per window to spot restatements
dimensiondim_dealer, by dealer_id, as of promised_date✓✓Writer looks up keys after the build; reader re-opens days whose unknown dealer has arrived
owed_feedsWH-A and WH-B send a file per day✓✓Writer sets _is_complete; reader re-opens incomplete days next night
max_unknown_rate25%✓Writer stops the run if more rows than this have key -1
paramsSettings for the rule (none here)✓The only setting the processor ever sees, next to its input frames

The thing to notice: the processor column is almost empty. The build sees only its input tables and params. Everything that makes the run safe (bookmarks, windows, keys, completeness, checks) lives in the reader and writer, and those are shared by every fact. That is why a build is only a few lines, and why it can be written in SQL.

Part D · 2

Can the fact be written in SQL? Yes

Here is the same OTIF rule as a SQL file. The harness hands the build its inputs as two views, order_lines and deliveries, already limited to the days being rebuilt and already filtered. The SQL only has to state the rule. Each step is a named CTE, so you can run any step by itself in a SQL editor.

-- PROCESSOR in SQL: the OTIF rule. The harness registers two views, already limited to the windows
-- being rebuilt: order_lines (after the declared filters) and deliveries. Pure: reads only those views.
WITH per_line AS (            -- one row per line FIRST, so the join below can never multiply lines
  SELECT line_id,
         SUM(qty)            AS qty_delivered,
         MAX(delivered_date) AS last_delivered
  FROM deliveries
  GROUP BY line_id
),
flags AS (
  SELECT l.line_id, l.dealer_id, l.promised_date, l.qty_ordered,
         COALESCE(p.qty_delivered, 0)                                         AS qty_delivered,
         CASE WHEN p.last_delivered <= l.promised_date THEN 1 ELSE 0 END      AS on_time,
         CASE WHEN COALESCE(p.qty_delivered, 0) >= l.qty_ordered THEN 1 ELSE 0 END AS in_full
  FROM order_lines l
  LEFT JOIN per_line p ON p.line_id = l.line_id
)
SELECT line_id, dealer_id, promised_date, qty_ordered, qty_delivered, on_time, in_full,
       on_time * in_full AS otif
FROM flags

Tested: the SQL file and the PySpark build return the same rows on all six table-driven cases and on the whole story's data (same rows). The harness runs either; you pick per fact.

def build_otif_sql(lines: DataFrame, deliveries: DataFrame, params: dict) -> DataFrame:
    from pathlib import Path
    lines.createOrReplaceTempView("order_lines")
    deliveries.createOrReplaceTempView("deliveries")
    return lines.sparkSession.sql((Path(__file__).parent / "fact_otif.sql").read_text())

Part D · 3

The same fact in dbt: does reader, processor, writer still work?

Yes. We built the same fact in dbt (dbt-core 1.12.7 with DuckDB, run on 14 Nov 2026) and fed it the same three nights. All three parts are there, written in dbt's own way:

ReaderThe changed_windows CTE inside {% if is_incremental() %}. dbt has no metadata store, so the bookmark is stored as two columns in the fact and read back with max().
ProcessorA macro, otif_rule(), holding the same SQL as the file above.
Writerincremental_strategy='delete+insert' with unique_key='promised_date': dbt deletes those days and inserts the new rows.
The processor: macros/otif_rule.sql
{# PROCESSOR: the OTIF rule, the same SQL as fact_otif.sql, as a macro that takes its two inputs. #}
{% macro otif_rule(order_lines, deliveries) %}
  with per_line as (            -- one row per line FIRST, so the join can never multiply lines
    select line_id, sum(qty) as qty_delivered, max(delivered_date) as last_delivered
    from {{ deliveries }}
    group by line_id
  ),
  flags as (
    select l.line_id, l.dealer_id, l.promised_date, l.qty_ordered,
           coalesce(p.qty_delivered, 0)                                              as qty_delivered,
           case when p.last_delivered <= l.promised_date then 1 else 0 end           as on_time,
           case when coalesce(p.qty_delivered, 0) >= l.qty_ordered then 1 else 0 end as in_full
    from {{ order_lines }} l
    left join per_line p on p.line_id = l.line_id
  )
  select line_id, dealer_id, promised_date, qty_ordered, qty_delivered, on_time, in_full,
         on_time * in_full as otif
  from flags
{% endmacro %}
Reader, keys, completeness and writer: models/fact_otif.sql
{# WRITER: dbt replaces every promised_date present in this run's output (delete, then insert). #}
{{ config(materialized='incremental', incremental_strategy='delete+insert', unique_key='promised_date') }}

-- READER 1: which windows changed? dbt keeps no bookmark of its own, so the bookmark is stored
-- in the fact itself (_lines_seq, _dels_seq) and read back with max().
with changed_windows as (
    select promised_date from {{ ref('order_lines') }}
    {% if is_incremental() %} where _seq > (select max(_lines_seq) from {{ this }}) {% endif %}
    union
    select l.promised_date
    from {{ ref('deliveries') }} d join {{ ref('order_lines') }} l on l.line_id = d.line_id
    {% if is_incremental() %} where d._seq > (select max(_dels_seq) from {{ this }}) {% endif %}
    {% if is_incremental() %}
    union   -- windows still waiting: an unknown dealer, or a missing warehouse file
    select promised_date from {{ this }} where dealer_key = -1 or not _is_complete
    {% endif %}
),

-- READER 2: every row of those windows, then the declared filter
order_lines_in as (
    select * from {{ ref('order_lines') }}
    where promised_date in (select promised_date from changed_windows)
      and dealer_id not in ('TEST01')
),
deliveries_in as (
    select d.* from {{ ref('deliveries') }} d
    where d.line_id in (select line_id from order_lines_in)
),

-- PROCESSOR
built as ( {{ otif_rule('order_lines_in', 'deliveries_in') }} ),

-- keys as of the event date; a missing dealer gets -1
keyed as (
    select b.*, coalesce(d.dealer_key, -1) as dealer_key
    from built b
    left join {{ ref('dim_dealer') }} d
      on d.dealer_id = b.dealer_id and b.promised_date >= d.valid_from and b.promised_date < d.valid_to
),

-- completeness: both warehouses have sent their file for the day
complete as (
    select file_date, count(distinct warehouse) = 2 as ok
    from {{ ref('delivery_files') }} group by file_date
)

select k.*, coalesce(c.ok, false) as _is_complete,
       (select max(_seq) from {{ ref('order_lines') }}) as _lines_seq,
       (select max(_seq) from {{ ref('deliveries') }})  as _dels_seq
from keyed k
left join complete c on c.file_date = k.promised_date
Tests: models/schema.yml (data tests and a table-driven unit test)
version: 2
models:
  - name: fact_otif
    columns:
      - name: line_id
        data_tests: [unique, not_null]        # the grain check
      - name: dealer_key
        data_tests: [not_null]
      - name: otif
        data_tests:
          - accepted_values: {values: [0, 1], quote: false}
  - name: fact_otif_careless
    columns:
      - name: line_id
        data_tests: [unique]

unit_tests:                                    # table-driven: rows in, rows out (dbt 1.8 and later)
  - name: otif_late_short_and_split_delivery
    model: fact_otif
    overrides:
      macros: {is_incremental: false}
    given:
      - input: ref('order_lines')
        rows:
          - {line_id: L2, dealer_id: D1, qty_ordered: 5, promised_date: 2026-11-10, _seq: 1}
          - {line_id: L3, dealer_id: D7, qty_ordered: 8, promised_date: 2026-11-11, _seq: 2}
      - input: ref('deliveries')
        rows:
          - {line_id: L2, qty: 5, delivered_date: 2026-11-11, _seq: 1}
          - {line_id: L3, qty: 6, delivered_date: 2026-11-11, _seq: 2}
          - {line_id: L3, qty: 2, delivered_date: 2026-11-14, _seq: 3}
      - input: ref('dim_dealer')
        rows:
          - {dealer_key: 1, dealer_id: D1, valid_from: 2024-01-01, valid_to: 9999-12-31}
          - {dealer_key: 2, dealer_id: D7, valid_from: 2024-03-01, valid_to: 2026-11-12}
      - input: ref('delivery_files')
        rows:
          - {warehouse: WH-A, file_date: 2026-11-10}
    expect:
      rows:
        - {line_id: L2, qty_delivered: 5, on_time: 0, in_full: 1, otif: 0, dealer_key: 1}
        - {line_id: L3, qty_delivered: 8, on_time: 0, in_full: 1, otif: 0, dealer_key: 2}

What happened over the three nights

nightdbt runtests (4 data + 1 unit)rows in fact_otifbookmarks stored in the fact (lines / deliveries)
r00 = okPASS=511001 / 2101
r10 = okPASS=561046 / 2205
r20 = okPASS=561046 / 2206

After night r2, dbt's fact_otif is identical to the harness's, row for row: same OTIF flags, the same keys (L3 → 2, L5 → 4) and the same completeness.

Where dbt behaves differently: when the checks run

We gave dbt a careless model (every delivery joined) with a unique test on line_id and ran dbt build. dbt created the table first, then the test failed. The careless table was left behind, holding L3 2 times and 16 tyres "ordered" instead of 8. Models downstream of a failed test are skipped, but anything reading that table directly sees the bad rows. The harness checks first and writes nothing on failure (Part A · 6). In dbt you get the same safety by building into a staging schema and swapping only after the tests pass (write-audit-publish), which you set up yourself.

Two more dbt details that matter here. (1) delete+insert deletes only the days that appear in the new output. If every line of a day is deleted at source, the new output has no row for that day, so the old rows stay. The harness replaces the list of days it planned, so an emptied day is cleared. (2) dbt's built-in microbatch strategy rebuilds the last N days by the clock, not by what changed. To catch L3's late delivery for 11 Nov on the night of 15 Nov, its lookback must be at least 4 days. We describe microbatch from the dbt docs; we ran the hand-written reader above, not microbatch.

Part D · 4

Our harness against dbt: pros and cons

TopicOur harnessdbtBetter
Writing the business ruleSQL file or PySpark functionSQL model or macro (Python models on some platforms)Tie: the same SQL ran in both
BookmarksPer input in a metadata store, moved last, with historymax() of a column in the target table, written in the same step as the datadbt is simpler; the harness keeps a history and survives a full refresh
Working out which days to rebuildGeneric, from settings: changes, unknown members, incomplete daysHand-written per model (our CTE), or microbatch, which rebuilds the last N days by time whether they changed or notHarness
Replacing windowsReplaces the listed days, so a day emptied at source is cleareddelete+insert removes only the days present in the new output; a day whose rows all vanished staysHarness
Checks before readers see dataChecks run before the write; on failure nothing is writtenData tests run after the table is written (shown above); downstream models are skipped; blocking needs a write-audit-publish setupHarness
Table-driven tests of the rulepytest, on Spark and DuckDBUnit tests in YAML: given rows, expected rows (passed above)Tie
Keys as of the event, unknown memberOne generic functionA join you write in each model, or a macro you write onceHarness
Surrogate keysKey map: stable 1, 2, 3A hash (dbt_utils); SCD2 from snapshotsHarness where integer keys matter
Completeness, restatements, correction plans, row ledgerBuilt inNot built in; macros and hooks you writeHarness
Lineage, docs, model selection, IDE supportRun log onlyBuilt in and excellentdbt
EnginesSpark and DuckDB through adapters30 or more adaptersdbt
Hiring, community, maturityNew and oursVery large and maturedbt
Verdict. For writing builds, dbt and the harness are equal: it is the same SQL. dbt wins on everything around the team: lineage, docs, model selection, adapters, hiring. The harness wins on everything around the numbers: choosing the right days, checking before writing, completeness, restatements and stable keys. If your team already uses dbt, keep dbt and add the harness parts as a dbt package (macros for changed days, as-of keys and completeness, a post-hook for restatements, write-audit-publish for checks). If you have no dbt yet and need an audit trail, use the harness with SQL builds. Either way the rule stays one SQL file, so moving later is cheap.