Register of Title

On Friday the auditor sent one line: reproduce, from the warehouse, the Q2 commission figure the board approved on 14 July. Not the right figure — that figure. Nobody could.

The gap is €2,811.85, 0.2683% of the quarter — under every materiality threshold the company has, which is why it has been signed off eight quarters running. The problem is not the money. The problem is that a number was published, people acted on it, and no query returns it any more.

And the dimension is not missing history. dim_seller_commission is a textbook SCD Type 2: 30,809 versions, unique key per version, no overlaps, no gaps, exactly one current row per seller. All four Lesson 09 dimension-integrity tests pass — and they are not asleep: inject a 40-day overlap and the overlap test fires immediately.

• Valid time (application time) — when the fact was true in the world.
• Knowledge time (transaction / system time) — when the database came to hold that belief.

SQL:2011 gives each its own construct (Kulkarni & Michels, SIGMOD Record 41(3)): an application-time period table carries a user-named PERIOD FOR; a system-versioned table carries the reserved SYSTEM_TIME plus WITH SYSTEM VERSIONING; a table may be both, and then a query about it takes two AS OF clauses. The standard’s own motivating example is the lesson:

“While employed, an employee may change names. Typically the name changes legally at a specific time (for example, a marriage) but the name is not changed in the database concurrently with the legal change. In that case, the system-time period automatically records when a particular name is known to the database, and the application-time period records when the name was legally effective.”

A Type 2 dimension records valid time only. So:

An as-of join on valid time asks a question whose answer the future can still change. An as-of join on knowledge time asks a question whose answer is already closed. The two look identical in SQL and they are not the same question.

dbt-core 1.12.5. SnapshotConfig has exactly one time field, updated_at; there is no second. The staging macro writes {{ strategy.updated_at }} as dbt_valid_from — and the two shipped strategies fill it from two different clocks:

• snapshot_timestamp_strategy: updated_at = config.get('updated_at') — the source’s column. Its change test is snapshotted.dbt_valid_from < current.updated_at, a strict inequality.
• snapshot_check_strategy: updated_at = config.get('updated_at') or snapshot_get_time() — the run clock.

Also from source: dbt_valid_to_current defaults to None; hard_deletes defaults to ignore.

A real three-seller dbt-duckdb project was built and run. One business event — on 20 June the rate becomes 16%, effective 1 April — produced four different histories:

And the order matters, which nothing records: two corrections (16% eff. 1 Mar, 18% eff. 1 May) fed in the two possible arrival orders leave two versions one way and three the other. Both tables agree exactly on today’s rate, so no current-state test can tell them apart.

Contrast, from MariaDB sql/sys_vars.cc (10.11): system_versioning_insert_history DEFAULT(FALSE) — you cannot write into ROW_START/ROW_END, i.e. you cannot back-date the knowledge axis; system_versioning_alter_history DEFAULT(VERS_ALTER_HISTORY_ERROR) — altering a versioned table fails until somebody decides what happens to history. SQL:2011: “users are not allowed to update or delete historical system rows.” The knowledge axis is only evidence if nobody can write on it.

5,242 of 31,245 versions (16.78%) took effect before the day they were written — median 21 days, p90 39, worst 399. 4,262 of 14,000 sellers are affected. 3,975 lower the rate, 1,267 raise it.

None of these is a bug. Every one makes the current answer more nearly true. They are only a problem because the table has one time column and the world has two.

Until 2025-10-14 the mart joined on dbt_valid_from — knowledge time. Someone filed a perfectly good ticket (“the as-of join ignores the effective date”), it was reviewed, and it shipped. The morning after, the thirteen already-closed months went from €3,016,025.85 to €3,006,231.65: €9,794.20 (0.3247%) of closed-period commission moved in one deployment, with no row changed. The gap to the ledger went from €844.76 to €10,638.96 — 12.6× worse.

24 of 24 published monthly figures no longer reproduce, by a median of 0.2367% and a worst of 0.5692%.

Why the “wrong” join reconciled: over 24 months the knowledge-time join differs from the ledger by €2,484.53 (0.0389%), all of it the two-day settlement gap. Not luck — a knowledge-time answer can never move, because no version can arrive carrying a knowledge stamp in the past. Asserted in the generator and re-run in DuckDB.

Q2 2026 was asked as of every date from 1 March to today. The ledger is a constant and the warehouse answer is a decreasing function of the knowledge date, so of course it crosses:

The crossing is six weeks before the quarter ended and slides €517.66 a week, so the agreement holds about half a day. A crossing is not a reproduction.

And no single date can work, structurally: the ledger froze each row at its own instant. A table-level as-of — all a snapshot id or a time-travel clause can give you — is one instant for every row at once. It is the wrong shape of answer, not a less accurate one.

-- what we billed: valid on the order date, known by the settlement instant
with picked as (
  select o.order_id, o.price_cents, r.rate_bp
  from   sale_order o
  join   rate_event r
    on   r.seller_id  = o.seller_id
   and   r.valid_from <= o.order_date     -- AS OF valid time
   and   r.known_at   <= o.settled_at     -- AS OF knowledge time
  qualify row_number() over (partition by o.order_id
                             order by r.valid_from desc, r.known_at desc) = 1
)
select sum((price_cents * rate_bp + 5000) // 10000) / 100.0 as commission_eur
from   picked;

Run over 1,392,595 orders in DuckDB it returns the ledger’s own total to the cent, for the quarter and for the whole 24 months.

Note it is not an ASOF JOIN. DuckDB’s and Snowflake’s take a single inequality — which is why Lesson 29 found Feast’s Snowflake store declining the knowledge-time cut-off with the reason in a comment: “the ASOF JOIN cannot express a created_timestamp cutoff.” Bitemporal correctness needs two inequalities and the fast primitive every engine ships takes one.

Every assertion anyone knows how to write about a slowly-changing dimension is an assertion about the valid-time axis, and the valid-time axis is in perfect order.

The profile monitor misses for a different reason. Of a closed month’s total post-close movement, 37.1% lands within a week, 83.5% within a month, and 95% by day 139 — after which the figure essentially stops. A monitor on night-over-night change can only see a number while it is still moving.

Lesson 24’s law, again: the bias is -0.3284% ± 0.0931 pp across all 24 months. Month-on-month growth from the warehouse differs from growth from the ledger by at most 0.2427 pp with 0 sign flips in 23 months. Every trend chart is fine. Every number anyone has to defend is not.

Two timestamps on the dimension and one on the fact: 0.4701 MiB over 30,809 versions and 10.625 MiB over 1,392,595 orders. BigQuery bills a table reference at a 10 MiB floor whatever it reads, so on the dimension the second clock is free by rounding.

The estate: of 23 dimension and reference tables, 7 already carry a knowledge axis (they are dbt snapshots); 8 keep valid time only; 8 keep nothing. 9 of the 10 that price something can be back-dated, and 8 of them have no knowledge axis at all. The raw material is mostly already in the warehouse — the marts discard it in the join.

Fix ladder

1. R1 — journal the answer. One row per (metric, period, as-of date, value, query, commit) whenever a figure is published. Answers what did we say? and nothing else. The only rung that also covers the code moving (Lesson 30).
2. R2 — stop discarding dbt_valid_from. Keep both columns, join on both in the marts that price things. Answers what did we bill? to the cent. No new storage.
3. R3 — add a written-at instant to the hand-built Type 2 dimensions and append logs.
4. R4 — system-version the sources. WITH SYSTEM VERSIONING, inserts into the knowledge axis forbidden. A source-system project, not a warehouse one.

The twenty catalogued failure shapes, scored: prevents 4 (L12 drift, L17 transient, L22 absent, L29 anachronistic), explains 2 (L14 reversible, L26 unsettled), does nothing for 14. The four it prevents share a property: their unit of failure is already a timestamp.

A bitemporal warehouse is not a correctness mechanism. It is an evidence mechanism. It will not make you right more often. It will let you show, afterwards, what you said and why.

-- any table that prices something and cannot say when it learned anything
select t.table_name,
       count(*) filter (where c.column_name in ('valid_from','effective_from')) as valid_axis,
       count(*) filter (where c.column_name in ('known_at','dbt_valid_from',
                                                'sys_start','row_start'))       as knowledge_axis
from   information_schema.columns c
join   catalog.pricing_tables t using (table_name)
group  by 1
having knowledge_axis = 0;        -- every row here is a figure you cannot reproduce

Answer here: 8 of 10. Cost $0.00. Its limit, said out loud: the running measurement (count versions written into the past) can only be computed on a table that already has the column — so on the tables that matter most it is not available either.

Every version retained, every test green, every figure defensible, every amendment an improvement — and a statement of the form “on 14 July we believed X” cannot be evaluated.

• Unit of failure: a second time axis that was never added.
• Signature: the question is not is this wrong? but what did we say?
• Detector: none. Not at any retention length, from any test, lineage walk, replay or backup.
• Distinct from L30’s irrecoverable: there the bytes expired; here every byte is present, correct and complete, and keeping them for a century would not help.
• It is the only shape whose sole defence is representational, and representational choices are made once, early, by whoever writes the first dimension.

1. “When our dimension says a seller was on 16% in April, does that mean it was true in April, or that we knew it in April?”
2. “Show me, from the warehouse, the number in last quarter’s board pack.” Not the right number — that one. Time it.
3. “Which of our dimensions can be back-dated, and by whom?”
4. “When finance and the warehouse disagree, which one is allowed to be right?”
5. “Does dbt_valid_from on our snapshots come from the source or from the run clock?”

pip install dbt-core dbt-duckdb duckdb

-- snapshots/snap_commission.sql
{% snapshot snap_commission %}
{{ config(unique_key='seller_id', strategy='timestamp', updated_at='updated_at') }}
select * from source_commission
{% endsnapshot %}

Seed three identical sellers, dbt snapshot, then make one business event three ways:

• seller 1: rate 16, effective_from 2026-04-01, updated_at 2026-06-20 09:14 (the clock)
• seller 2: rate 16, effective_from 2026-04-01, updated_at 2026-04-01 (the effective date)
• seller 3: rate 16, effective_from 2025-11-01, updated_at 2025-11-01 (moved backwards)

Run dbt snapshot again and read the table. Which seller’s history now claims the warehouse knew before it did? Which seller’s change is not in the table at all, and what in snapshot_timestamp_strategy caused that? For each seller, which column would you read to find out when you learned? Then add a check-strategy snapshot of the same source and ask both tables for the rate as of a date before you started snapshotting.

Your dimension already has history. It is the history of when things were true, and it is being rewritten, legitimately, 16.78% of the time. Nothing in it records when you found out — so every figure you have published is a claim you can no longer evidence, and the gap between your warehouse and your ledger is not an error to chase but two clocks being read as one. Adding the second clock costs 0.4701 MiB and a second inequality in a join. Not adding it costs nothing at all, until somebody asks.

• Valid time (application time) — when a fact was true in the world.
• Knowledge time (transaction / system time) — when the database came to hold the belief.
• Bitemporal table — carries both periods; a row is a rectangle, and a query takes two AS OF.
• Retroactive amendment — a version whose valid time begins before its knowledge time.
• Reproducible figure — one a query can return again; needs the data and the code as of the publication instant.
• The unprovable — a failure whose unit is a second time axis that was never added.

• The dbt findings are a real run (dbt-core 1.12.5 + dbt-duckdb, four snapshot runs). Macro quotations from the installed package; config defaults from dbt/artifacts/resources/v1/snapshot.py. MariaDB defaults from sql/sys_vars.cc at 10.11. SQL:2011 quotations from Kulkarni & Michels, Temporal features in SQL:2011, SIGMOD Record 41(3).
• Everything with a euro sign is synthetic and deterministic: 1,392,595 orders, 14,000 sellers, 31,245 rate versions over 24 months, built with a splitmix64 finaliser over each element’s own identity — no seed, no hash(). (seller_id, valid_from, known_at) uniqueness is asserted (a real bitemporal primary key), as is that the per-month step functions sum exactly to today’s answer.
• The SQL was run, not described: every printed query executed in DuckDB over the generator’s own 1,392,595-row order table — 56 checks, 56 matched, 0 failed, including the bitemporal join reproducing the ledger to the cent, the four L09 tests both passing on the real table and firing on a deliberately broken copy, and the knowledge-time answer proving immutable under an added cut-off.
• Round-trip: the generator and verification script were extracted from the rendered page, run in a clean directory and diffed — 17 top-level keys, 1,376 scalar values, 0 differences, figures.json byte-identical by md5.
• Not measured: the 23-table estate and the twenty-shape scoring are judgements, not measurements.