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.

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.

r2 startsbookmark 2205writes windows11 and 13 Novcrash!before the bookmarkrerun reads2206 againplans from the lastcommitted versionsame table,2 restatementsBecause the bookmark moves last and windows are replaced whole, a crash repeats work. It never skips or doubles it.

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.

A real lesson from writing this example: our first version planned the rerun by looking at gold as the crashed run had left it. The crashed run had already fixed L5's key and the 13 Nov flag, so the rerun thought 13 Nov needed nothing. It rebuilt only 11 Nov and lost the 13 Nov restatement. The fix: a run always plans and compares against the last committed version of gold (the version recorded when the bookmark last moved), never against whatever is in the table now. Delta and Iceberg keep old versions, which makes this one line.

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.

careless: join every delivery rowL38 orderedX3: 6 tyresX6: 2 tyresL3, 8 orderedL3, 8 orderedtwo rows for one line: 16 tyres 'ordered'stopped by the grain and ledger checksright: one row per line firstX3 + X6sum = 8 tyresL38 orderedL3, 8 and 8one rowadd deliveries up per line, then joina lookup join can never add 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 run
Reading 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 problems

All files: _guides/code/silver_to_gold/ in the site's repository (builds.py, harness.py, data.py, test_builds.py, e2e.py).