cleandataengineering.aiData Platform, from the big picture to the code
You are here: Start here › Operations › Reconciliation and profiling

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

PartFileWhat it doesWhen it runs
Measures between hopssrc/dataplatform/reconcile.pyCompares named measures counted at each hop with the first hop.In the first example domain's check task
Live source against silversrc/dataplatform/silver/live_recon.pyCompares 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 reportssrc/dataplatform/hop_reports.pyTurns each layer's run report into plain data and back.When ingestion, silver and gold run as separate processes or jobs
Profilingsrc/dataplatform/profiling.pyProfiles 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 out

The 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

Live source tableall rows nowSilvercurrent rows16 bucketsrows per bucket16 bucketsrows per bucketCompare totalsbucket by bucketDiffering bucketsonly: list the keys
Both sides are cut into the same 16 buckets. Only buckets whose totals differ are opened.

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) % buckets
src/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.

SideRowsResult
Live source40
Silver37Three 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 exc

The 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
FindingRule in the code
Volume movedThe row count is at least 3 times the last one, or at most one third of it.
A column went emptyThe share of empty values in a column rose by 20 percentage points or more.
A column is newThe column is in this profile and was not in the last.
A column stopped comingThe 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 profileThis profileFinding
100 rows, defect_code empty in 5100 rows, defect_code empty in 60defect_code empty in 60% of rows, was 5%
100 rows10 rowsvolume moved from 100 to 10 rows
100 rows, 110 rows before100 rowsNothing

The settings that drive it

SettingWhereMeaningDefault
reconciliation.measuresThe dataset manifestThe measures to count: a name, and a column to sum or none for a row count.None
reconciliation.toleranceThe dataset manifestHow far a measure may differ. The gold plan and the release gate read it. The live comparison counts rows and applies no tolerance.0
BucketsA constant in the codeHow many buckets the live comparison uses.16
Drift limitsArguments of driftThe 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()),
]
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"),
]
Not built yet, or known gaps
  • 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.