"Other Shapes" — Data Vault, Wide Tables and Modeling Trade-offs

Skim Card

One thing to remember: no model is best in general; each one makes a different kind of change cheap, and none of them works without exact money and explicit time.

• Data Vault makes new sources and audits cheap: insert-only, rows stamped with load time and source. This refunds question took five joins and four latest-row filters.
• Wide tables became viable because columnar engines read only the columns a query names; you pay by rebuilding them whenever an upstream fact changes.
• Measure a balance at one instant everywhere: counting stock at each store’s close of business took availability from 91.1% to 88.5%.
• Money: integer minor units or DECIMAL, the currency on every row, one rounding rule written down. minor / 100 gave a DOUBLE ending in …0000034.
• A business date needs an instant and a time zone. Cutting the UK quarter in London, not UTC, moved Q3 by £298.10 and every day.
• Convert at the rate in force on the business date (Friday’s on weekends): worth £8,797.38. Both fixes land exactly on £29,790,674.06.
• Keep valid time and transaction time apart, and a report filed on 1 October stays reproducible after late data arrives.

Use …	When …
Normalised (3NF) integration layer	many source systems, one enterprise model, a team to maintain it
Data Vault	sources change often, audit says “show me what you were told and when”
Star schema	shared, conformed reporting across business processes
Wide table	one consumer or dashboard, columnar engine, rebuilt from a model below it
Activity schema	customer-journey questions over many event types

Verified against: DuckDB 2.0.0 (ASOF JOIN, AT TIME ZONE) · docs: Activity Schema 2.0, Data Vault Alliance, gov.uk clock changes, ECB reference rates, Iceberg 1.12, PostgreSQL 18, Spark 4.1, Delta / Databricks, SQL Server / Fabric, Snowflake, BigQuery, Redshift, Athena · 28 October 2026

Jump to: Concepts · Hands-On · Landscape · Design Drill

Story Opening

Farah’s two questions from the end of Part 4 were still on the whiteboard: whose midnight cuts the quarter, and which rate converts the euro orders?

Rohan had a plan for those, and a bigger one for the model. “The star works, but the audit team wants every number traceable to a source file. Let’s pick the right schema once, the one that does both, and migrate everything to it.”

“There isn’t one,” Ingrid said. “There are shapes, and each makes a different change cheap: new sources, new questions, or a new engine. Build the refunds two ways and see what each costs you. Then fix the things no shape can fix for you: money, clocks and exchange rates.”

The Quick Map

Concepts, with use cases

Where integration happens: Inmon, Kimball, Data Vault

Every warehouse has to integrate sources that disagree. The three classic answers differ in where that happens.

What breaks without a deliberate choice. Integration happens inside every report query, each slightly differently: the Part 0 incident. Larkspur: Part 4 chose Kimball for reporting; this part adds a vault under the refunds, where the audit question lives. Elsewhere: a telecom with 40 billing systems from acquisitions keeps an Inmon-style normalised customer model, because “one customer” is its hard problem.

Data Vault

Data Vault 2.0 is Dan Linstedt’s method, set out in Linstedt and Olschimke’s 2015 book. “2.1” is the Data Vault Alliance’s CDVP2.1 course and certification (2024), with no public specification; this part follows the 2.0 book. It splits every source into three kinds of table (definitions per the 2.0 book and Scalefree):

• Hubs hold unique business keys: one row per basket or order ID, refund ID, store ID. The Data Vault Alliance puts it as “a Hub holds a unique list of business keys”.
• Links hold relationships between business keys: this refund pays back that sale; that sale happened in this store.
• Satellites hold descriptive attributes and their history: amount, reason, status. A new satellite row is written only when the attributes change.

Every row carries a load_ts (when the vault learned it) and a record_source (where from). Keys are usually hash keys, commonly MD5 or SHA-1 of the normalised business key, so hubs, links and satellites can load in parallel without looking up a sequence. Nothing is updated or deleted.

What breaks without it. When a source changes shape, a modelled warehouse needs its tables migrated, and an UPDATE destroys the evidence of what the old value was. Larkspur: refunds come from one extract, sales from till uploads and a nightly order poller. Each source gets its own satellite; a fourth source (marketplace refunds, say) would be a new satellite, with no existing table altered. Elsewhere: in this series’ judgement (not a survey), banks and insurers choose Data Vault most often: many acquired systems, and regulators who ask how an old figure was built. The cost is query complexity, so nobody reports straight from the vault.

Wide tables (one big table)

A wide table pre-joins everything a set of questions needs into one table: one row per refund with the store’s city, the purchase date, the amount in GBP. It’s a practice, not a standard.

Columnar engines made it viable (Part 3): a query reads only the columns it names, and the repeated city name compresses to almost nothing with dictionary encoding. BigQuery’s documentation goes further and recommends nested and repeated fields “to denormalize data storage and increase query performance”, while noting that a star schema is usually already fast.

What breaks without it. Every dashboard re-does the same joins, and someone gets them wrong (Part 4’s discount bug). What breaks with it. It holds no history and must be rebuilt when a dimension restates or a late fact lands; without a declared grain it becomes Part 4’s anti-pattern. Larkspur: wide_refund, rebuilt from the vault on every load. Elsewhere: an adtech company serves advertiser dashboards from one impression-level wide table in a columnar store; every campaign and creative attribute is on the row.

Activity schema

The Activity Schema spec (version 2.0, Ahmed Elsamadisi at Narrator, December 2022) models a business as one stream of customer activities in eight columns, three of them optional: activity_id, ts, customer, activity, anonymous_customer_id, feature_json, revenue_impact, link. Questions become “for each customer who did A, what did they do next?” instead of joins between fact tables.

What breaks without it. Every funnel or journey question becomes a join between fact tables of different grains (orders, refunds, sessions), each written by hand with its own chance of a grain bug.

Larkspur: with placed order and requested refund in one stream, “what share of refunding customers bought again within 30 days?” is one self-join. Elsewhere: a SaaS team asks “did accounts that invited a teammate in week one renew?”. The trade-off: attributes sit in feature_json, so finance questions still want a star.

Medallion is a lifecycle, and semantic models sit on top

Part 2’s bronze, silver and gold describe how far data has been processed, not what shape it has. Larkspur’s raw zone is bronze; the vault is a silver-layer integration model; the star and wide_refund are gold. A semantic model (Part 31) sits above all of them: it names the metric (“net revenue”) once, whatever shape answers it. Elsewhere: a media streaming company keeps its raw playback events as bronze and serves “minutes watched” from one metric definition, whether a star or a wide table answers it.

Event data: events, snapshots and slowly changing reference data

Most sources arrive in one of three shapes, and each wants different handling:

What breaks without it. Treat a snapshot as events and every unchanged row counts again each night; update events in place and the history is gone. Elsewhere: a logistics company receives scan events (immutable), nightly depot inventory files (snapshots) and fuel-surcharge tables (reference data, applied as of the shipment date).

Correctness primitives: money

• Integer minor units or DECIMAL, never float. Binary floats can’t represent most decimal fractions; sums drift (Hands-On step 4).
• Store the currency code on every amount. The number of minor units comes from ISO 4217: two for GBP and EUR, zero for JPY, three for BHD and KWD. Dividing by 100 everywhere is a bug waiting for a new market.
• Write the rounding rule down. Languages disagree: Python’s round(2.5) is 2 (half to even), Java’s Math.round(-2.5) is −2, DuckDB’s round(-2.5) is −3 (half away from zero). Larkspur converts a transaction’s total once, rounding half up on the absolute value, then reapplies the sign, so converting −x gives exactly minus converting x at the same rate. The generator does it in integers ((amount * rate_e8 + 50_000_000) // 100_000_000); the SQL agrees to the penny.

Elsewhere: a payments company that rounds per line on the invoice and per total in the ledger reconciles with a permanent “rounding difference” account nobody can explain.

Correctness primitives: time

• Store instants in UTC (TIMESTAMPTZ), and keep event time and ingestion time in separate columns.
• A business date is a function of the instant and a time zone: the store’s, or the legal entity’s. Larkspur’s day starts at 23:00 UTC in London in summer, 18:30 UTC in India and 16:00 UTC in Singapore. India and Singapore don’t change clocks.
• DST makes days unequal. UK clocks went back at 02:00 on Sunday 25 October 2026: that business day lasted 25 hours and local 01:30 happened twice. Hourly reports need the offset, not just the local time.

What breaks without it. Cutting days at UTC midnight moves every sale between 23:00 and 24:00 UTC into the wrong UK day in summer. Elsewhere: an airline counts departures by each airport’s local date; a UTC cut moves late-evening US departures into tomorrow.

FX as of the event date

Convert at the rate in force on the transaction’s business date: the latest rate published on or before it. The ECB publishes its reference rates “around 16:00 CET every working day, except on TARGET closing days”, so a Saturday order uses Friday’s rate. Implement it as an as-of join (Part 4) or by densifying the rates to one row per calendar day and equi-joining.

What breaks without it. “Today’s rate” changes last quarter’s revenue every time someone re-runs the report. Larkspur: 7% of UK online orders are paid in euros. Elsewhere: an e-commerce marketplace pays sellers in their own currency at the rate of the order date, and has to show that rate on every statement.

Bitemporal data

Martin Fowler’s “Bitemporal History” (2021) separates two histories: actual history, what happened and when (also called valid time), and record history, when we learned it (transaction time). Larkspur already has both clocks: event_ts is when the sale happened; the vault’s load_ts is when Larkspur learned of it.

What breaks without it. A report filed on Monday can’t be reproduced on Friday: late data, corrections and restatements have changed the tables underneath. Larkspur: offline tills upload late, so “30 September as reported on 1 October” and “30 September as we know it now” are different, both correct numbers (step 3). Elsewhere: an insurer back-dates an address change: March’s premium changes (valid time) on 10 May (transaction time).

Under the Hood

How an insert-only satellite loads

A satellite load compares, it never updates. For each key in the new batch (sql/part05/vault_load.sql):

1. Hash the descriptive attributes into a hash_diff.
2. Line the new rows up after the key’s existing satellite rows, ordered by load_ts.
3. Insert only rows whose hash_diff differs from the previous row’s, and skip any (key, load_ts) already present.

Step 3 makes the load idempotent (a re-run inserts nothing) and makes a satellite a list of changes, not of extracts (Hands-On step 1 counts them).

What the vault costs on disk

Hash keys let every table load independently, but they’re wide, and the vault repeats them: each sale’s key is in its hub, two links and a satellite, and the satellites copy 2.3 million sale rows the star already had. With 32-character text keys and a primary-key index on each of nine tables, the vault grew the DuckDB file from 387 MiB (the whole star, plus this part’s FX and stock changes) to 1.6 GiB. How much of that is the key format alone wasn’t measured; binary hashes are the usual saving.

Hands-On

The code is in the every-record-counts folder of the companion-workspaces repository (tag every-record-counts/part-5). No containers: scripts/part05_shapes.py copies Part 4’s star to data/lab/part05/larkspur.duckdb and adds the vault, the wide table, daily FX rates and closing stock. The build peaked at 2.8 GiB resident on the host (MacBook Pro M3 Pro). Outputs drop DuckDB’s column-type and border lines.

Step 1: build, and load the vault twice

uv run python scripts/part04_star.py build --local      # Part 4's star, if you don't have it
uv run python -m scripts.part05_shapes build --local

The vault loads twice, plus a re-run: first with what had landed by 06:00 UTC on 1 October, when the daily report for 30 September ran, then with everything.

FX by day and closing stock              0.9s
vault loads                             13.7s

== rows inserted per load (as filed: landed by 2026-10-01 06:00 UTC; re-run: everything again)
table               as filed  everything      re-run
hub_sale           2,426,683      83,064           0
hub_refund                 0      63,780           0
hub_store                603           0           0
lnk_refund_sale            0      63,780           0
lnk_sale_store     2,426,683      83,064           0
sat_sale_pos       2,252,383      76,975           0
sat_sale_order       317,765      11,014           0
sat_refund                 0      63,780           0
sat_store                603           0           0

wide_refund: 63,780 rows (13.9s)

The refunds extract lands on 5 October, so the first load has none. The re-run inserts nothing. sat_sale_pos holds 2,329,358 rows for 2,338,812 till events: the 9,454 re-sends hashed the same as their originals. That’s a side effect, not a duplicate policy; a re-send with one changed field would add a version (Part 6 finds the duplicates).

sql/part05/vault_history.sql shows what a satellite keeps: the poller’s nightly extracts against the satellite rows they became.

│ extract_rows │ extracts │ satellite_rows │ orders │
│       356057 │      127 │         328779 │ 180389 │

│       history       │ orders │
│ placed -> fulfilled │ 148390 │
│ fulfilled           │  27278 │
│ placed              │   4721 │

One row per change of status, not per extract. 27,278 orders were placed and fulfilled between two poller runs, so the vault never saw them placed; and most of the 4,721 placed orders were cancelled and hard-deleted (the manifest counts 3,043 the poller saw), which no satellite row can show (Part 16, CDC).

Step 2: one question, two shapes

UK refunds in Q3, by the month they happened and the month of the purchase, in GBP at the rate of the refund’s London business date. Against the vault (sql/part05/refunds_two_shapes.sql), “current” means the latest satellite row per key, and every relationship is a link:

WITH refund AS (
    SELECT * FROM sat_refund QUALIFY row_number() OVER (PARTITION BY refund_hk ORDER BY load_ts DESC) = 1
),
sale AS (...),    -- the same for sat_sale_pos and sat_sale_order, UNION ALL
store AS (...),   -- the same for sat_store
gb AS (
    SELECT (r.refund_ts AT TIME ZONE st.timezone)::DATE    AS refund_date,
           (s.purchased_at AT TIME ZONE st.timezone)::DATE AS purchase_date,
           r.currency, r.amount_minor
    FROM refund r
    JOIN lnk_refund_sale rs ON rs.refund_hk = r.refund_hk
    JOIN sale s ON s.sale_hk = rs.sale_hk
    JOIN lnk_sale_store ss ON ss.sale_hk = rs.sale_hk
    JOIN store st ON st.store_hk = ss.store_hk
    WHERE st.country = 'GB'
)
SELECT strftime(refund_date, '%Y-%m') AS refund_month, strftime(purchase_date, '%Y-%m') AS purchase_month,
       count(*) AS refunds, major(sum(convert_minor(amount_minor, fx.rate))) AS refunds_gbp
FROM gb
JOIN dim_fx_rate fx ON fx.base_currency = gb.currency AND fx.quote_currency = 'GBP' AND fx.date = gb.refund_date
WHERE refund_date BETWEEN DATE '2026-07-01' AND DATE '2026-09-30'
GROUP BY ALL
ORDER BY ALL;

Against the wide table it’s the same SELECT … GROUP BY ALL over wide_refund WHERE country = 'GB', with no joins.

uv run python -m scripts.part05_shapes query --local sql/part05/refunds_two_shapes.sql

Both print the same eight rows (shown once):

│ refund_month │ purchase_month │ refunds │  refunds_gbp  │
│ 2026-07      │ 2026-06        │    1678 │      59456.19 │
│ 2026-07      │ 2026-07        │    4342 │     157212.79 │
│ 2026-08      │ 2026-06        │      47 │       2170.20 │
│ 2026-08      │ 2026-07        │    1662 │      60746.88 │
│ 2026-08      │ 2026-08        │    4352 │     159853.66 │
│ 2026-09      │ 2026-07        │      40 │       1710.57 │
│ 2026-09      │ 2026-08        │    1801 │      65461.31 │
│ 2026-09      │ 2026-09        │    4101 │     144946.24 │

Five joins and four latest-row filters against none; 0.07 s against under 0.01 s on 63,780 refunds. At this size the speed doesn’t matter; the query you have to get right does. The third statement shows what only the vault can say: everything it was told about one refunded euro order, and when:

│   sale_id   │         load_ts          │  record_source   │      status       │ total_minor │ currency │
│ OGB00000096 │ 2026-06-02 01:00:00+00   │ oltp/orders_poll │ fulfilled         │        6678 │ EUR      │
│ OGB00000096 │ 2026-10-05 01:00:00+00   │ oltp/refunds     │ refunded: quality │        6678 │ EUR      │

The wide table has this refund too, but not when Larkspur learned of it, or from which file.

Step 3: as filed, and as of now

sql/part05/as_filed.sql asks the vault for UK till sales on business date 30 September at two transaction times: what had been loaded by 06:00 UTC on 1 October, when the daily report ran, and everything known now. Each basket’s latest satellite row loaded by then counts once:

│          version          │ baskets │  revenue_gbp  │
│ as filed, 1 Oct 06:00 UTC │    5625 │     264816.30 │
│ as of now                 │    5646 │     265838.13 │

21 offline baskets (£1,021.83) uploaded after the report ran. Both numbers are right; they answer different questions, and the vault never overwrote the first.

Step 4: money and time, measured

sql/part05/money_and_time.sql:

│    double_gbp     │  decimal_gbp  │ double_type │
│ 3321809.470000034 │    3321809.47 │ DOUBLE      │

│       tz       │   oct_1_starts_at    │
│ Europe/London  │ 2026-09-30 23:00 UTC │
│ Asia/Kolkata   │ 2026-09-30 18:30 UTC │
│ Asia/Singapore │ 2026-09-30 16:00 UTC │

│ oct_25_length  │ utc_00_30_in_london │ utc_01_30_in_london │
│ 1 day 01:00:00 │ 01:30               │ 01:30               │

The DOUBLE carries float noise; the integer sum shown as DECIMAL is exact. The other two results are the time primitives from the Concepts, measured.

Step 5: FX as of the transaction date (waterfall step 4)

sql/part05/fx_daily.sql densifies the rates: every calendar day gets the latest rate published on or before it, with a flag. The first statement of sql/part05/uk_revenue_fx_dates.sql shows a weekend:

│    date    │   day    │ rate_date  │     rate      │ carried_forward │
│ 2026-09-25 │ Friday   │ 2026-09-25 │    0.81482463 │ false           │
│ 2026-09-26 │ Saturday │ 2026-09-25 │    0.81482463 │ true            │
│ 2026-09-27 │ Sunday   │ 2026-09-25 │    0.81482463 │ true            │
│ 2026-09-28 │ Monday   │ 2026-09-28 │    0.81130205 │ false           │

The window has 36 such days (the manifest’s count). The second statement starts from the star, where steps 1–3 are already fixed, and converts each euro transaction’s total with convert_minor three ways: at the report day’s rate, at its UTC date’s rate, at its London date’s rate:

│               uk_net_revenue                │      gbp      │  change_gbp   │
│ steps 1-3 fixed: UTC dates, report-day rate │   29781578.58 │          NULL │
│ + step 4: rate of the transaction date      │   29790375.96 │       8797.38 │
│ + step 5: London business date              │   29790674.06 │        298.10 │

The first row is £29,999,232.43 minus steps 1–3 (£118,076.98, £57,064.96, £42,511.91). 5 October’s rate understated euro revenue by £8,797.38 (manifest step 4); London dates add £298.10 (step 5), landing on £29,790,674.06, Part 0’s correct figure, to the penny.

Step 6: business dates, taken apart (waterfall step 5)

The third statement splits step 5 into sales and refunds, both converted as of their own dates:

│  side   │ rows_dates_differ │    q3_utc     │   q3_london   │  change_gbp   │
│ refunds │               964 │    -652037.52 │    -651557.84 │        479.68 │
│ sales   │              2696 │   30442413.48 │   30442231.90 │       -181.58 │
│ both    │              3660 │   29790375.96 │   29790674.06 │        298.10 │

This is where Part 4’s £538.15 went. It’s an interaction, not a sixth defect: refunds near a quarter edge are moved by both refund dating and business dates, and a waterfall gives an interaction to the later step, which is why the manifest fixes the order. In Part 4, London dates cut the refund-dated total by £479.93 (at the report-day rate) and raised the purchase-dated one by £58.22. By step 5 the purchase-dated version is gone, so only the refund side moves: £479.68, the £0.25 being euro refunds converted at their own dates’ rates instead of 5 October’s. Sales move by −£181.58. Net: £298.10.

The quarter barely moves because only its two edges, plus euro rows that cross midnight and pick up another day’s rate, can move it. Daily figures move much more (fourth statement, sterling sales only):

│ q3_days │ days_that_differ │ largest_daily_change_gbp │ sum_of_daily_changes_gbp │
│      92 │               92 │                  1264.16 │                 34881.01 │

Every one of the 92 days had a different total in UTC, by up to £1,264.16: the “yesterday’s revenue keeps changing” complaint, explained.

Step 7: stock counted at the close of the business day

sql/part05/stock_close.sql adds closing_on_hand_qty to the snapshot fact. The rule reads timestamps, not a store list: if the snapshot was taken after the business day ended in the store’s time zone, it includes the next morning’s delivery, so subtract received_qty. sql/part05/stock_close_of_day.sql shows why:

│ wms_snapshot_schedule │ local_time │ days_after_business_date │ stores │
│ after_delivery_06_00  │ 06:00      │                        1 │    216 │
│ close_23_30           │ 23:30      │                        0 │    384 │

│ store_id │    sku    │    date    │  counted_at_local   │ on_hand_qty │ received_qty │ closing_on_hand_qty │
│ GB-0001  │ SKU-00007 │ 2026-08-07 │ 2026-08-08 06:00:00 │           2 │            0 │                   2 │
│ GB-0001  │ SKU-00007 │ 2026-08-08 │ 2026-08-09 06:00:00 │           5 │            5 │                   0 │
│ GB-0001  │ SKU-00007 │ 2026-08-09 │ 2026-08-10 06:00:00 │           3 │            0 │                   3 │

│ per_day_as_reported_pct │ per_day_at_close_pct │
│                    91.1 │                 88.5 │

216 stores count at 06:00 the next morning. GB-0001 sold out of SKU-00007 on 8 August; the overnight delivery of 5 made the snapshot say 5. Counting every store at its close of business takes Q3 availability from 91.1% to 88.5%, Part 0’s correct figure: the empty shelves store managers reported.

Step 8: the tests

tests/test_part05.py checks every claim above against the small generated world’s manifest (including a second vault loaded only up to the filing time, which matches the “as filed” row exactly), then rebuilds from data/raw and checks this post’s figures.

uv run pytest -q tests/test_part05.py

.......                                                                  [100%]
7 passed in 36.21s

The Landscape

Product names as of 28 October 2026. Cells that couldn’t be verified say so.

Tips & Gotchas

Gotcha — ::DATE on a TIMESTAMPTZ uses the session’s time zone. DuckDB’s defaults to your laptop’s. The lab’s views set TimeZone = 'UTC', and business dates always name a zone with AT TIME ZONE.

Gotcha — an inner equi-join to a working-day rate table drops weekends. No error, just missing Saturday sales. Densify rates to every calendar day or use an as-of join, and assert row counts before and after.

Gotcha — rounding rules differ by language. Pick one rule, convert once per transaction, and test a case that ends in exactly half a penny.

Tip — normalise business keys the same way before hashing, in every loader. Upper-case, trim, and put a delimiter between the parts of a composite key (vault_load.sql uses ||). A hub loaded by two sources that hash differently splits one business key in two.

Tip — a vault is an integration layer, not a reporting layer. Build marts (a star, a wide table) on top and point reports there; step 2’s five-join query is what every analyst would otherwise write.

Troubleshooting

Design Drill

The question: “A bank files regulatory returns every quarter. A supervisor can ask it to reproduce any return exactly as it was filed, up to three years later, and to explain every difference from today’s restated figures. Source systems correct data after filing, reference data changes, and the reporting code evolves. How do you model the data?”

Clarify first

• Is “exactly as filed” the stored output, or a re-computation that must match it? (Usually both.)
• Which corrections trigger a resubmission, and which only flow into the next period?
• Which reference data feeds the returns (FX, counterparty hierarchies, product mappings), and how often does it change?
• Retention requirements, how they meet privacy erasure, and how late source corrections arrive.

The shape of a strong answer

• Two time axes on every record: valid time (the reporting date) and transaction time (when the platform learned it). Corrections are new rows, never updates.
• An immutable raw landing zone kept for the retention period, so any figure traces back to its file.
• An insert-only integration layer with load_ts and record_source on every row: a Data Vault fits, a bitemporal normalised model works too.
• Reference data bitemporal too: FX rates and counterparty hierarchies as known at filing time.
• A filing manifest per return: reporting date, transaction-time cutoff, code version (git commit), reference-data versions and a hash of the stored output.
• Reproduction filters load_ts <= cutoff, runs the recorded code version and must match the stored hash. On a lakehouse, also tag the snapshots the filing read (Iceberg tags are kept forever unless given a retention).
• Restatement is a new filing with a later cutoff, with a waterfall attributing each change to late data, correction or code.
• DECIMAL money with currency codes and one rounding rule; UTC instants with the entity’s business date.

Trade-offs to say out loud

• Storing every version costs storage and query complexity; marts for analysts sit on top, and only the filing path queries bitemporally.
• Recomputing proves the lineage but depends on old code still running; storing outputs is cheap but proves nothing on its own. Do both.
• Privacy erasure conflicts with immutability: pseudonymise identities so a deletion doesn’t break the history.

Common weak answers

• “We’ll use time travel”: retention is days or weeks, not three years, and it holds no code or reference-data versions.
• Nightly copies of the reporting tables: you can reproduce the number but not explain what changed or why.

Further Reading

1. Martin Fowler, “Bitemporal History” (2021). https://martinfowler.com/articles/bitemporal-history.html
2. Daniel Linstedt and Michael Olschimke, Building a Scalable Data Warehouse with Data Vault 2.0 (Morgan Kaufmann, 2015).
3. Activity Schema specification, version 2.0. https://github.com/ActivitySchema/ActivitySchema/blob/main/2.0.md
4. ECB, “Euro foreign exchange reference rates”. https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html
5. Apache Iceberg, “Branching and Tagging” (historical tags for audits). https://iceberg.apache.org/docs/latest/branching/

Story Closing

Farah got two answers. London’s midnight cuts the quarter, and euros convert at the rate in force on the business date, Friday’s on a weekend. UK revenue on the star now equals the correct figure exactly. Three of the five revenue defects are closed (refund dating, FX, business dates); the figure is exact because the star already keeps one row per basket and reads the current orders table. Stock availability reads 88.5%, the shelf at closing time. Of the five promises, this part kept correctness.

The vault stays under the refunds for the auditors, the wide table feeds the refunds dashboard, and the star stays. “Three shapes, one company,” Rohan said. “Three kinds of change,” said Ingrid.

In Part 6, Rohan writes the analytical SQL that the rest of the series leans on: window functions, sessions and gaps-and-islands. It finds the 2,455 re-sent UK baskets behind the largest revenue step, and settles why product and marketing count conversion differently.

This is Part 5 of “Every Record Counts: Data Engineering for Backend Engineers” — Season 1: Foundations & the Lakehouse.