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.
- Which part of the harness uses each setting
- Can the fact be written in SQL? Yes
- The same fact in dbt: does reader, processor, writer still work?
- Our harness against dbt: pros and cons
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.
| Setting | What it says here | Reader | Processor | Writer | How it is used |
|---|---|---|---|---|---|
| name | Which table this is | ✓ | ✓ | Reader finds its bookmarks under this name; writer writes the run log and moves them | |
| grain | One row per order line | ○ | ✓ | Writer checks it is unique before writing; the build must produce it but is never given it | |
| window_col | Rebuild by promised date | ✓ | ✓ | Reader turns changed rows into days; writer replaces exactly those days | |
| lookback_days | 35 days | ✓ | Reader refuses windows older than this; they need a correction plan | ||
| driver | order_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_via | A delivery's window is its line's | ✓ | Reader maps a changed delivery to its line's day | ||
| filters | No test dealers | ✓ | ✓ | Reader applies and counts them; writer's ledger balance uses the count | |
| measures | qty_ordered, qty_delivered, otif | ✓ | Writer compares them per window to spot restatements | ||
| dimension | dim_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_feeds | WH-A and WH-B send a file per day | ✓ | ✓ | Writer sets _is_complete; reader re-opens incomplete days next night | |
| max_unknown_rate | 25% | ✓ | Writer stops the run if more rows than this have key -1 | ||
| params | Settings 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:
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().otif_rule(), holding the same SQL as the file above.incremental_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_dateTests: 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
| night | dbt run | tests (4 data + 1 unit) | rows in fact_otif | bookmarks stored in the fact (lines / deliveries) |
|---|---|---|---|---|
| r0 | 0 = ok | PASS=5 | 1 | 1001 / 2101 |
| r1 | 0 = ok | PASS=5 | 6 | 1046 / 2205 |
| r2 | 0 = ok | PASS=5 | 6 | 1046 / 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.
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
| Topic | Our harness | dbt | Better |
|---|---|---|---|
| Writing the business rule | SQL file or PySpark function | SQL model or macro (Python models on some platforms) | Tie: the same SQL ran in both |
| Bookmarks | Per input in a metadata store, moved last, with history | max() of a column in the target table, written in the same step as the data | dbt is simpler; the harness keeps a history and survives a full refresh |
| Working out which days to rebuild | Generic, from settings: changes, unknown members, incomplete days | Hand-written per model (our CTE), or microbatch, which rebuilds the last N days by time whether they changed or not | Harness |
| Replacing windows | Replaces the listed days, so a day emptied at source is cleared | delete+insert removes only the days present in the new output; a day whose rows all vanished stays | Harness |
| Checks before readers see data | Checks run before the write; on failure nothing is written | Data tests run after the table is written (shown above); downstream models are skipped; blocking needs a write-audit-publish setup | Harness |
| Table-driven tests of the rule | pytest, on Spark and DuckDB | Unit tests in YAML: given rows, expected rows (passed above) | Tie |
| Keys as of the event, unknown member | One generic function | A join you write in each model, or a macro you write once | Harness |
| Surrogate keys | Key map: stable 1, 2, 3 | A hash (dbt_utils); SCD2 from snapshots | Harness where integer keys matter |
| Completeness, restatements, correction plans, row ledger | Built in | Not built in; macros and hooks you write | Harness |
| Lineage, docs, model selection, IDE support | Run log only | Built in and excellent | dbt |
| Engines | Spark and DuckDB through adapters | 30 or more adapters | dbt |
| Hiring, community, maturity | New and ours | Very large and mature | dbt |