Home › Data engineering › Gold data modelling › Silver to gold › When it goes wrong, and the tests
Silver to gold · Learn it · 7 min read
Silver to gold: a crash, a careless join, and how the build is tested
The worked example continued: a crash halfway, a careless join that the checks stop, and the small-tables-in, expected-rows-out tests behind every build. This is one page of Silver to gold.
- What if the machine dies halfway?
- What if someone writes a careless join?
- How the build is tested: small tables in, expected rows out
Part A · 5
What if the machine dies halfway?
We made run r2 crash on purpose, after it had written gold but before it moved the bookmark ("machine lost"). Then we ran it again.
Bookmark last, windows replaced whole: a crash means repeated work, never lost or doubled numbers.
Result: the table after the rerun is identical to the table the crashed run wrote: yes. Both restatements are logged once. Final watermarks: order_lines 1046, deliveries 2206.
Part A · 6
What if someone writes a careless join?
We swapped the build for a careless one that joins every delivery row straight onto the order lines. L3 has two deliveries now, so it became two rows.
One line with two deliveries becomes two rows unless deliveries are added up first.
The harness refused to write. The exact message from the run:
['grain not unique: 1 keys appear more than once', "ledger does not balance: {'read': 7, 'filtered': 1, 'after_join': 7, 'rejected': 0, 'unknown_keys': 0, 'written': 7}", 'a lookup join changed the row count: 6 in, 7 out']
Wrote anything? No. Three separate checks caught it: the grain (L3 twice), the ledger balance, and the join row count. In a notebook without these checks, the dashboard would have shown 8 extra tyres ordered, and nobody would have known why.
Part A · 7
How the build is tested: small tables in, expected rows out
Because the build is pure, a test is just a few rows in and the expected flags out, written as a table. These run in about ten seconds on a laptop:
OTIF_CASES = [
("on time and in full", (10, 10), [(10, 10)], (10, 1, 1, 1)),
("delivered a day late", (5, 10), [(5, 11)], (5, 0, 1, 0)),
("on time but short", (8, 11), [(6, 11)], (6, 1, 0, 0)),
("two deliveries: no double counting", (8, 11), [(6, 11), (2, 14)], (8, 0, 1, 0)),
("nothing delivered yet", (4, 13), [], (0, 0, 0, 0)),
("early delivery counts as on time", (4, 13), [(4, 12)], (4, 1, 1, 1)),
]
def test_otif(spark, name, line, dels, expected):
lines = spark.createDataFrame([("L1", "O1", "D1", "R15", line[0], D(2026, 11, line[1]), 1)], LINES_S)
d = spark.createDataFrame([(f"X{i}", "L1", "WH-A", q, D(2026, 11, day), i) for i, (q, day) in enumerate(dels)], DELS_S)
rows = B.build_otif(lines, d, {}).collect()
assert len(rows) == 1, "one row per order line, always"
r = rows[0]
assert (r.qty_ordered, r.qty_delivered, r.on_time, r.in_full, r.otif) == (line[0],) + expected
Writing this page, one of these table tests caught a real bug in the stock build (Part B): days before the first stock movement were dropped instead of showing zero.
The whole run, as tested (harness.py, run_fact)
def run_fact(spark, meta: dict, store: Meta, silver: dict, gold_path: str, build, run_id: str, run_date, files: DataFrame):
name, wcol, dm = meta["name"], meta["window_col"], meta["dimension"]
driver = silver[meta["driver"]]
# 1-2 READER: watermarks, fixed high marks, changed rows only
marks = {i: store.watermark(name, i) for i in meta["inputs"]}
highs = {i: high_mark(silver[i], c["bookmark"]) for i, c in meta["inputs"].items()}
changed = {i: read_changes(silver[i], c["bookmark"], marks[i], highs[i]) for i, c in meta["inputs"].items()}
# 3 changed rows -> windows (a child row, like a delivery, belongs to the window of its driver row)
w = changed[meta["driver"]].select(wcol)
for i, c in meta["inputs"].items():
if "window_via" in c:
w = w.union(changed[i].join(driver, c["window_via"]).select(wcol))
v = store.data.get("versions", {}).get(name) # plan from the last COMMITTED version, never from a
if v is not None: # crashed run's half-done write
g = spark.read.format("delta").option("versionAsOf", v).load(gold_path)
# also: windows holding unknown members who have now arrived, and windows not yet complete
w = w.union(g.where(f"{dm['key']} = -1").join(silver[dm["table"]], dm["business_key"], "left_semi").select(wcol))
w = w.union(g.where("NOT _is_complete").select(wcol))
windows = sorted({r[0] for r in w.distinct().collect()})
too_old = [d for d in windows if (run_date - d).days > meta["lookback_days"]]
windows = [d for d in windows if d not in too_old] # older changes need a correction plan
if not windows:
return {"run": run_id, "windows": [], "note": "nothing changed"}
# 4 read ALL current silver rows for those windows (not just the changed ones), then the declared filters
inputs = {meta["driver"]: driver.where(F.col(wcol).isin(windows))}
for i, c in meta["inputs"].items():
if "window_via" in c:
inputs[i] = silver[i].join(inputs[meta["driver"]].select(c["window_via"]), c["window_via"], "left_semi")
n_read = inputs[meta["driver"]].count()
for rule in meta["filters"].values():
inputs[meta["driver"]] = inputs[meta["driver"]].where(rule)
n_kept = inputs[meta["driver"]].count()
# 5 PROCESSOR: the pure build
built = build(*inputs.values(), meta["params"])
# 6 keys as of the event
out = lookup_keys(built, silver[dm["table"]], dm["business_key"], dm["key"], dm["as_of"])
# 7 completeness: every owed feed has sent its file for the window's day
got = files.groupBy("file_date").agg(F.collect_set("warehouse").alias("got"))
owed = F.array(*[F.lit(x) for x in meta["owed_feeds"]])
out = (out.join(got, out[wcol] == got["file_date"], "left")
.withColumn("_is_complete", F.coalesce(F.size(F.array_except(owed, F.col("got"))) == 0, F.lit(False)))
.drop("file_date", "got").withColumn("_run_id", F.lit(run_id)))
# 8 CHECKS with the row ledger
ledger = {"read": n_read, "filtered": n_read - n_kept, "after_join": built.count(), "rejected": 0,
"unknown_keys": out.where(f"{dm['key']} = -1").count(), "written": out.count()}
problems = check(out, ledger, meta)
if problems:
raise RuntimeError(f"{run_id} stopped before writing: {problems}") # nothing written, watermark unchanged
# 9 restatements: compare with the last COMMITTED version (a crashed run's half-done write does not count)
new_s = window_summary(out, meta)
old_s = window_summary(g.where(F.col(wcol).isin(windows)), meta) if v is not None else {}
rest = [{"run": run_id, "window": k, "old": old_s[k], "new": v} for k, v in new_s.items() if k in old_s and old_s[k] != v]
# 10 WRITER: replace only those windows
replace_windows(spark, out, gold_path, wcol, windows)
version = DeltaTable.forPath(spark, gold_path).history(1).first()["version"]
# 11 run log, then move the watermarks LAST
run = {"run": run_id, "read_from": marks, "read_to": highs, "windows": [str(d) for d in windows],
"refused_too_old": [str(d) for d in too_old], "ledger": ledger, "restatements": rest, "gold_version": version}
store.commit(name, highs, run)
return runReading and the metadata store (harness.py)
class Meta:
def __init__(self, path: Path):
self.path = path
self.data = json.loads(path.read_text()) if path.exists() else {"watermarks": {}, "runs": [], "restatements": []}
def watermark(self, fact, inp):
return self.data["watermarks"].get(f"{fact}.{inp}", 0)
def commit(self, fact, high_marks, run):
"""Called LAST, only after the write and the checks succeeded."""
for inp, hm in high_marks.items():
self.data["watermarks"][f"{fact}.{inp}"] = hm
self.data.setdefault("versions", {})[fact] = run["gold_version"] # the last version readers may trust
self.data["runs"].append(run)
self.data["restatements"] += run["restatements"]
self.path.write_text(json.dumps(self.data, indent=1, default=str))
def high_mark(df: DataFrame, col: str) -> int:
"""Fixed once at the start of the run: changes arriving later wait for the next run."""
return df.agg(F.max(col)).first()[0] or 0
def read_changes(df: DataFrame, col: str, low: int, high: int) -> DataFrame:
"""Only the rows changed after the watermark, up to the high mark: a closed range."""
return df.where((F.col(col) > low) & (F.col(col) <= high))
def assign_keys(history: DataFrame, keymap: DataFrame, bk: str, key: str) -> DataFrame:
"""Stable surrogate keys: existing versions keep their key; new versions get the next numbers."""
known = history.join(keymap, [bk, "valid_from"], "left")
start = keymap.agg(F.coalesce(F.max(key), F.lit(0))).first()[0]
rows = sorted(known.where(F.col(key).isNull()).select(bk, "valid_from").collect())
add = [(r[0], r[1], start + i + 1) for i, r in enumerate(rows)]
added = history.sparkSession.createDataFrame(add, f"{bk} string, valid_from date, {key} int")
return keymap.unionByName(added)Checks and the ledger (harness.py)
def window_summary(df: DataFrame, meta: dict) -> dict:
"""Per window: row count, total of each measure, unknown keys. Used for restatements."""
key, cols = meta["dimension"]["key"], ["rows"] + meta["measures"] + ["unknown_keys"]
aggs = [F.count("*").alias("rows")] + [F.sum(m).alias(m) for m in meta["measures"]]
aggs.append(F.sum((F.col(key) == -1).cast("int")).alias("unknown_keys"))
s = df.groupBy(meta["window_col"]).agg(*aggs)
return {str(r[0]): {c: r[c] for c in cols} for r in s.collect()}
def check(out: DataFrame, ledger: dict, meta: dict) -> list:
problems = []
dupes = out.groupBy(*meta["grain"]).count().where("count > 1").count()
if dupes:
problems.append(f"grain not unique: {dupes} keys appear more than once")
if ledger["read"] != ledger["filtered"] + ledger["rejected"] + ledger["written"]:
problems.append(f"ledger does not balance: {ledger}")
if ledger["after_join"] != ledger["read"] - ledger["filtered"]:
problems.append(f"a lookup join changed the row count: {ledger['read'] - ledger['filtered']} in, {ledger['after_join']} out")
if ledger["written"] and ledger["unknown_keys"] / ledger["written"] > meta["max_unknown_rate"]:
problems.append("too many unknown dealers")
return problemsAll files: _guides/code/silver_to_gold/ in the site's repository (builds.py, harness.py, data.py, test_builds.py, e2e.py).