Data PlatformZoom level 2
Reconciliation and profiling: is the data the same at every hop
Reconciliation means counting the same thing in two places and checking that the two counts agree. If the source holds 2,410 deliveries and silver holds 2,403, seven rows were lost somewhere, and no job failed to say so.
A pipeline can lose rows silently. A change feed can skip changes during maintenance. A filter can drop more than intended. Reconciliation is the only check that finds a loss nobody raised an error for. Profiling is its companion: it notices when the shape of the data moves.
The four parts on this page
| Part | File | What it does | When it runs |
|---|---|---|---|
| Measures between hops | src/dataplatform/reconcile.py | Compares named measures counted at each hop with the first hop. | In the first example domain's check task |
| Live source against silver | src/dataplatform/silver/live_recon.py | Compares the source table as it is now with silver's current rows, and names the missing keys. | After each day's silver run, for database sources |
| Hop reports | src/dataplatform/hop_reports.py | Turns each layer's run report into plain data and back. | When ingestion, silver and gold run as separate processes or jobs |
| Profiling | src/dataplatform/profiling.py | Profiles every loaded batch and compares the run's profile with the last one. | In every ingestion run, at the lander |
Gold has its own checks that no row or quantity is lost between its build and its write. They are part of the gold harness, which the gold harness page explains.
Measures between hops
A hop is one step of the path from source to gold. Each dataset's manifest lists its reconciliation measures: a row count, and sums of named columns.
data-platform-catalog/slices/02-tyre/manifest/datasets/erp.sales_order_line.yaml:36The order line dataset's reconciliation block in its manifest.reconciliation:
hops: [sources, landing, bronze, silver]
measures:
- {name: row_count}
- {name: net_value_total, column: net_value}
tolerance: {row_count: 0, net_value_total: 0.0}src/dataplatform/reconcile.py:7Every hop is compared with the first hop, measure by measure.def compare(run_id: str, day: int, measures: dict[str, dict[str, float]], tolerance: dict[str, float],
obligation: str = "REC-01") -> list[CheckResult]:
"""measures = {hop: {measure: value}}, hops in spine order. Each hop is compared with the first hop.
A hop with no value for a measure fails: "nothing to compare" is never a pass (GATE-01).
"""
hops = list(measures)
base = hops[0]
out = []
for hop in hops[1:]:
for m, expected in measures[base].items():
observed = measures[hop].get(m)
tol = tolerance.get(m, 0)
ok = observed is not None and abs(observed - expected) <= tol
out.append(CheckResult(run_id, day, f"reconcile.{m}.{base}->{hop}", obligation, ok, expected, observed,
"" if ok else ("no value at this hop" if observed is None else
f"differs by {observed - expected} (tolerance {tol})")))
return outThe line to notice is the one that sets ok. A hop with no value for a measure fails. Having nothing to compare is never treated as a pass.
The live source against silver
Comparing two large tables key by key is expensive. So both sides first add up their rows per bucket. A bucket is a group of keys chosen by a hash of the business key, so the same key falls in the same bucket on both sides. Sixteen totals are compared. Only for buckets that differ are the keys listed on both sides, and the difference is the exact set of keys missing from silver or extra in it.
src/dataplatform/reconcile.py:31The bucket of a key. The engine computes the same number on silver's side.def bucket_of(key_values: tuple[object, ...], buckets: int) -> int:
"""The bucket of a business key: the first 8 hex digits of sha256 over the key's text parts, mod `buckets`. The
engine side computes the same (sha2 over concat_ws of the parts cast to text), so both sides agree on buckets."""
import hashlib # noqa: PLC0415 -- keeps the module's import light for compare()
# shiftleft: allow SL-COST-CODE-14 -- str.join of key parts, not a DataFrame join
text = SEP.join("" if v is None else str(v) for v in key_values)
return int(hashlib.sha256(text.encode()).hexdigest()[:8], 16) % bucketssrc/dataplatform/silver/live_recon.py:56Buckets first, then keys only where buckets differ.def reconcile_live(dataset: str, source_rows: list[Mapping[str, Any]], plan: SilverPlan, silver: BucketedSilver,
buckets: int = BUCKETS) -> LiveRecon:
keys = plan.keys
left = bucket_totals([dict(r) for r in source_rows], keys, {"rows": None}, buckets)
right = silver.bucket_totals(plan, {"rows": None}, buckets)
total = int(sum(b["rows"] for b in right.values()))
bad = sorted({b for b, _, _, _ in compare_buckets(left, right)})
if not bad:
return LiveRecon(dataset, len(source_rows), total)
wanted = set(bad)
src = {_text(r.get(k) for k in keys) for r in source_rows
if bucket_of(tuple(r.get(k) for k in keys), buckets) in wanted}
sil = silver.keys_in_buckets(plan, buckets, wanted)
return LiveRecon(dataset, len(source_rows), total, tuple(bad), tuple(sorted(src - sil)), tuple(sorted(sil - src)))Walk through it. The left side is the source's rows, bucketed in memory. The right side is asked of silver through a port, and only sixteen totals come back. When no bucket differs the result is returned with the two row counts. Otherwise the source's keys in the differing buckets are collected, silver is asked for its keys in the same buckets, and the two set differences are the missing and the extra keys.
A worked example
Forty deliveries are in the source. The change feed skipped three of them. The values are the test's.
| Side | Rows | Result |
|---|---|---|
| Live source | 40 | |
| Silver | 37 | Three buckets differ at most. Missing from silver: DL-0003, DL-0017, DL-0031. Extra: none. |
When keys are missing and none are extra, the message names the hop as sources to landing. The result is written to the run log as the step recon.live_source_vs_silver, with status failed when any bucket differs, so the alert rule for failed checks picks it up.
src/dataplatform/silver/live_recon.py:42The message a failed reconciliation carries. It names the hop and up to ten keys. def message(self) -> str:
if self.ok:
return f"live source {self.source_rows} rows = silver {self.silver_rows} rows in every bucket"
hop = "sources to landing" if self.missing and not self.extra else "between source and silver"
show = ", ".join("/".join(k) for k in self.missing[:10])
return (f"live source {self.source_rows} rows, silver {self.silver_rows}; {len(self.buckets_differing)} "
f"bucket(s) differ; {len(self.missing)} key(s) missing from silver, {len(self.extra)} extra; hop: "
f"{hop}; missing: {show}")Hop reports
On a laptop one process runs ingestion, silver and gold and passes each report along as an object. Deployed, the three are separate jobs. Each one writes its report as plain data next to the run, and whoever judges the day reads it back.
src/dataplatform/hop_reports.py:57Reading a report back. The kind asked for must be the kind that was written.def load(kind: str, data: dict[str, Any]) -> Any:
"""The report object back from `dump`'s data. The kind asked for must be the kind that was written."""
if kind not in _LOADERS:
raise HopReportError(f"unknown hop '{kind}'; known: {', '.join(KINDS)}")
if data.get("kind") != kind:
raise HopReportError(f"asked for a {kind} report, found '{data.get('kind')}'")
if data.get("version") != VERSION:
raise HopReportError(f"hop report version {data.get('version')} is not {VERSION}")
try:
return _LOADERS[kind](data["report"])
except (KeyError, TypeError) as exc:
raise HopReportError(f"{kind} report is not in the expected shape: {exc}") from excThe promise is exact: loading what was dumped gives back an equal object of the same type. A report of another kind, of another format version, or with a missing or unknown field is refused with a message.
Profiling
A profile is a summary of a batch: the number of rows, and for each column the number of empty values and of distinct values. For numbers, dates and times it also keeps the smallest and the largest. For text columns it keeps counts only, so a profile never holds a value from a text column. Drift is a profile that moved too far from the last one.
src/dataplatform/profiling.py:63What counts as drift. Each finding is one plain sentence.def drift(now: dict[str, Any], before: dict[str, Any] | None, null_jump: float = 0.2,
volume_ratio: float = 3.0) -> list[str]:
"""Findings, each one plain sentence; empty when nothing moved beyond the limits or there is no earlier profile."""
if not before or not before.get("rows") or not now.get("rows"):
return []
found = []
ratio = now["rows"] / before["rows"]
if ratio >= volume_ratio or ratio <= 1 / volume_ratio:
found.append(f"volume moved from {before['rows']} to {now['rows']} rows")
for name in sorted(now["columns"]):
if name not in before["columns"]:
found.append(f"{name} is new")
continue
a, b = null_share(before, name), null_share(now, name)
if b - a >= null_jump:
found.append(f"{name} empty in {b:.0%} of rows, was {a:.0%}")
for name in sorted(set(before["columns"]) - set(now["columns"])):
found.append(f"{name} no longer delivered")
return found| Finding | Rule in the code |
|---|---|
| Volume moved | The row count is at least 3 times the last one, or at most one third of it. |
| A column went empty | The share of empty values in a column rose by 20 percentage points or more. |
| A column is new | The column is in this profile and was not in the last. |
| A column stopped coming | The column was in the last profile and is not in this one. |
src/dataplatform/ingest/runner.py:62Where ingestion logs the profile and keeps it for next time.def _profile_step(store: MetadataStore, run_id: str, spec: Any, result: Any, normal: bool) -> None:
"""The run's profile (B9, DPC-0051): logged as a step and compared with the last one the store kept; drift makes
the step `warned` and names what moved. A normal run's profile is kept for the next comparison."""
loaded = [o.profile for o in result.outcomes if o.status == "loaded" and o.profile]
if not loaded:
return
now = profiling.merge(loaded)
found = profiling.drift(now, store.last_profile(spec.source, spec.dataset))
counts = {"rows": now["rows"], **{f"{c}.{k}": v for c, m in now["columns"].items() for k, v in m.items()
if k in ("nulls", "distinct")}}
store.log_step(StepRecord(run_id, spec.source, spec.dataset, "profile.batches", "warned" if found else "succeeded",
counts, "; ".join(found)[:4000]))
if normal: # a reload or backfill is not the dataset's usual shape: it is not kept as the next baseline
store.save_profile(run_id, spec.source, spec.dataset, now)The step is named profile.batches. It is warned when drift was found and names what moved. Only a normal run's profile is kept as the next one to compare with. A reload or a backfill does not have the dataset's usual shape.
A worked example
| Last profile | This profile | Finding |
|---|---|---|
100 rows, defect_code empty in 5 | 100 rows, defect_code empty in 60 | defect_code empty in 60% of rows, was 5% |
| 100 rows | 10 rows | volume moved from 100 to 10 rows |
| 100 rows, 110 rows before | 100 rows | Nothing |
The settings that drive it
| Setting | Where | Meaning | Default |
|---|---|---|---|
reconciliation.measures | The dataset manifest | The measures to count: a name, and a column to sum or none for a row count. | None |
reconciliation.tolerance | The dataset manifest | How far a measure may differ. The gold plan and the release gate read it. The live comparison counts rows and applies no tolerance. | 0 |
| Buckets | A constant in the code | How many buckets the live comparison uses. | 16 |
| Drift limits | Arguments of drift | The jump in empty share, and the volume ratio. | 0.2 and 3.0 |
How it is tested
tests/dataplatform/test_reconcile_buckets.py:33The bucket comparison's cases: what silver holds, the tolerance, and what must be found.CASES = [
("equal sides: nothing found", ROWS, {}, 0, set()),
("a row missing in silver: its bucket, both measures", _drop(ROWS, "SO7"), {}, 2, {"lines", "net_value"}),
("a changed value: its bucket, the value only", _change(ROWS, "SO7", 99.0), {}, 1, {"net_value"}),
("within tolerance: nothing found", _change(ROWS, "SO7", 7.4), {"net_value": 0.5}, 0, set()),
]tests/dataplatform/test_profiling.py:37The drift cases.DRIFT_CASES = [
("no earlier profile: nothing to compare", _p(100, qty=0), None, []),
("steady: nothing found", _p(100, qty=2), _p(110, qty=3), []),
("a column goes mostly empty", _p(100, qty=60), _p(100, qty=5), ["qty empty in 60% of rows, was 5%"]),
("volume collapses", _p(10, qty=0), _p(100, qty=0), ["volume moved from 100 to 10 rows"]),
("volume triples", _p(300, qty=0), _p(100, qty=0), ["volume moved from 100 to 300 rows"]),
("a new column", _p(100, qty=0, colour=0), _p(100, qty=0), ["colour is new"]),
("a column stops coming", _p(100, qty=0), _p(100, qty=0, colour=0), ["colour no longer delivered"]),
]tests/dataplatform/test_live_recon.py covers the live comparison with four cases and checks that only differing buckets are opened. tests/dataplatform/test_reconcile_engine.py checks that the engine computes the same buckets as Python. tests/dataplatform/test_hop_reports.py has a round trip table and a refusal table.
tests/dataplatform/test_hop_reports.py:51What a hop report loader refuses.REFUSED = [
("unknown hop", "bronze", dump(INGEST), "unknown hop 'bronze'"),
("the other hop's report", "silver", dump(INGEST), "asked for a silver report, found 'ingest'"),
("a newer format", "ingest", {**dump(INGEST), "version": 2}, "version 2 is not 1"),
("a field missing", "ingest", {"kind": "ingest", "version": 1, "report": {"run_id": "x"}}, "expected shape"),
("an unknown field on a dataset", "silver",
{"kind": "silver", "version": 1, "report": {"run_id": "x", "status": "succeeded", "datasets": [{"nope": 1}]}},
"expected shape"),
]- The live comparison counts rows only. It passes one measure, the row count, so a value that changed without a row going missing is not found by it.
- The live comparison runs only for database sources, where the run script can read the live table. Files, the dealer interface and the stream are not compared this way.
- The simulated company plants seven delivery rows that the change feed skips. The code that judges this exists, but neither recorded design note shows its verdict, and neither slow test passes the live reader. Planted incidents says more.
- Reconciliation entries with their own tables, tolerances signed off by a person, and a screen with the hop and the keys belong to the data expectation services, which are not built.
- The drift limits are arguments with defaults in code. No setting changes them per dataset.