Chapter 13 · Data Quality Engineering: Validation, Standardization, Matching, Reconciliation, and Quarantine
Build Reconciliation Tests from Source Counts/Sums/Checksums to Warehouse Facts and Dimensions
Reconcile source partitions, accepted facts, dimensions, rejects, and duplicates using counts, sums, and canonical checksums, and prove reruns reproduce the same accepted warehouse state.
Learning outcomes
Reconcile source rows into mutually exclusive accepted, quarantined, and duplicate states.
Reconcile additive accepted measures and dimensions at their declared grain rather than comparing incompatible totals.
Use canonical checksums as reproducibility evidence while understanding their limitations.
Prove an authorized repair changes only the intended acceptance state and that reruns are stable.
Build a production-ready acceptance checklist with owners, severity, observability, rollback, and known limitations.
Use only the lesson’s synthetic/local fixture for destructive setup, reset, correction, backfill, or cleanup steps. Record the stated runtime/version and assumptions, preserve the AtlasMart control totals, and never point cleanup commands at production data or credentials.
1. Reconciliation is a conservation argument
Quality engineering is incomplete until the pipeline can explain
where source evidence went. For the five-row order test batch
before repair, exactly two rows are accepted unique facts, two
are quarantined, and one is duplicate delivery. The row-state
equation is therefore 5 = 2 + 2 + 1. No row
disappears.
| Control | Before repair | After QO2 repair | Interpretation |
|---|---|---|---|
| Source raw order rows | 5 | 5 raw + 1 derived repair candidate | Raw evidence remains immutable; a repair candidate is derived, not a new source event. |
| Accepted unique facts | 2 | 3 | QO2 moves from quarantine to accepted. |
| Duplicate deliveries | 1 | 1 | QO1 redelivery remains evidence, not a second fact. |
| Remaining quarantined | 2 | 1 | QO5 remains blocked by unknown unit conversion. |
| Accepted units | 3 | 4 | Only semantically valid canonical item quantities are summed. |
| Accepted amount USD | 100 | 150 | QO2 contributes 50 USD after timestamp semantics are authorized. |
2. Counts, sums, and checksums answer different questions
A count detects missing/extra rows at a chosen grain. A sum detects measure drift when the measure is additive and comparable. A checksum fingerprints canonical values so two reruns can be compared efficiently. None replaces the others. Two datasets can have the same count and sum but different row assignments; a checksum can match only if the serialization and canonicalization contract is stable.
| Evidence | Catches | Can miss / caveat |
|---|---|---|
| Row count | lost or extra rows | value corruption that preserves count |
| Distinct business keys | duplicate/missing keys | measure changes under same keys |
| Additive sum | amount/quantity drift | offsetting errors can cancel |
| Canonical checksum | row/value changes under fixed serialization | does not prove business accuracy; sensitive to canonicalization/version |
| Reject/duplicate partition | silent row loss | does not by itself verify accepted values |
3. Reconciliation script
# Expected deterministic controls for the isolated Q batch.before = { "source_rows": 5, "accepted_unique": 2, "quarantined": 2, "duplicates": 1, "accepted_units": 3, "accepted_amount_usd": 100.0,}assert before["source_rows"] == ( before["accepted_unique"] + before["quarantined"] + before["duplicates"])after_repair = { "accepted_unique": 3, "remaining_quarantined": 1, "duplicates": 1, "accepted_units": 4, "accepted_amount_usd": 150.0,}assert after_repair["accepted_amount_usd"] - before["accepted_amount_usd"] == 50.0# Replay contract: canonical accepted rows hash identically after the same repair is redelivered.assert repaired_semantic_checksum == replay_semantic_checksumThe 50-USD change is explainable by one authorized record moving from quarantine to certified state. QO5 does not enter the measure until the unit owner supplies an approved conversion or a permanent rejection policy.
4. Dimension reconciliation
Fact reconciliation must be paired with dimension/key checks. Customer rows should partition into accepted identities, rejects, and entity-match decisions. In this fixture, Q-C006 is quarantined for a missing required name; Q-C001 and Q-C001-DUP are accepted source rows that map to one durable customer under the lab’s exact-email rule; Q-C007 remains a separate durable customer. Comparing raw customer row count directly with durable dimension row count would therefore be a category error.
| Layer / grain | Rows | Why count differs |
|---|---|---|
| Raw customer records | 4 | Source-record grain. |
| Customer rejects | 1 | Q-C006 blocked. |
| Standardized accepted source records | 3 | Three source records have valid required representation. |
| Durable customer entities | 2 | Q-C001 and Q-C001-DUP deterministically map to one entity; Q-C007 is another. |
5. Controlled failure: checksum-only acceptance
A team compares one aggregate hash and declares the load correct. If the hash was built from the wrong columns, wrong ordering, or already-corrupted canonicalization, it can faithfully fingerprint the wrong state. Worse, matching a prior wrong state proves only reproducibility. The repair is layered evidence: schema/contract checks, row-state partition, key uniqueness, referential integrity, additive controls, targeted samples, and checksums under versioned canonicalization.
6. Source-to-warehouse reconciliation matrix
| Artifact | Control grain | Acceptance test | Owner |
|---|---|---|---|
| Raw order batch | one delivered order-line payload | every row classified; raw hash retained | Order Platform + Analytics Engineering |
| Accepted fact candidate | one unique valid order-line business event | key unique; customer resolved; timestamp/unit valid | Analytics Engineering |
| Customer entity map | one source identity → durable identity decision | no accepted fact references unresolved identity | Customer Data Steward |
| Quarantine | one failed raw payload + rule version | reason/owner/status present; no silent drops | Domain owner + Data Quality |
| Certified measure | one additive amount at accepted fact grain | sum reconciles to accepted canonical rows only | Metric owner / Finance |
| Rerun | same inputs + rule versions + repair manifests | same semantic checksum and control totals | Platform / Data Reliability |
7. Production acceptance, observability, and rollback
Before certifying a batch, record rule version, reference-data version, source/batch identity, accepted/reject/duplicate counts, measure controls, checksum algorithm/canonicalization version, and any repair manifests. Alert on unexpected shifts in rejects, duplicates, timeliness, or distributions. A rule change that reclassifies historical rows needs an impact preview and rollback path, not an in-place edit with no lineage.
Security applies end to end: raw/quarantine data can contain sensitive or malformed content, match evidence can reveal identity relationships, and debug exports can bypass warehouse policy. Use least privilege, synthetic data in tests, and organization/jurisdiction-specific policies for production.
8. Chapter acceptance checklist and next step
- Production continuity from Chapter 12 remains 690 USD; the Q batch is isolated.
- Six quality dimensions are measured separately with explicit denominators.
- Standardization preserves raw evidence and rejects unproved unit/time semantics.
- Deterministic duplicate/entity rules retain evidence; uncertain matching is stewarded.
- Quarantine has reason codes, owners, raw hashes, rule versions, and repair lineage.
- Before repair: 5 = 2 accepted + 2 quarantine + 1 duplicate; accepted amount 100 USD.
- After authorized QO2 repair: 3 accepted, 1 remaining quarantine, 1 duplicate; accepted amount 150 USD.
- Replay produces the same semantic checksum.
Chapter 14 now decides where and when these controls run in ETL versus ELT architectures, how raw/staging/integration/presentation layers preserve replayability, and who owns each transform.
Knowledge check
Check your understanding
- Why should accepted facts not be reconciled directly to raw transport rows?
- What does an additive sum control add beyond row counts?
- What does a checksum prove?
- Why can raw customer rows outnumber durable customer entities?
- What is the bridge to Chapter 14?
Review the answers
1. Raw transport can include duplicates and quarantined invalid records, so reconciliation must account for state transitions and declared grains.
2. It detects measure drift even when the number of rows is unchanged.
3. Under a fixed canonicalization it proves the serialized accepted state is identical; it does not prove semantic accuracy.
4. Multiple accepted source identities can intentionally map to one governed durable entity.
5. Chapter 14 places validation, raw preservation, transformations, and presentation models into ETL/ELT layers with explicit ownership and replayability.
Authoritative references
- ISO/IEC 25012 — Data quality modelOfficial ISO catalogue entry for a general data-quality model; use it as background, not as a substitute for AtlasMart-specific acceptance rules.
- Unicode Standard Annex #15 — Unicode Normalization FormsAuthoritative normalization guidance used to explain NFC normalization without transliterating or overwriting the raw value.
- RFC 3339 — Date and Time on the InternetAuthoritative timestamp syntax reference supporting the rule that source timestamps carry an explicit offset before normalization to UTC.
- IANA — Time Zone DatabaseAuthoritative source for named civil time-zone identifiers when business semantics require a region-based zone rather than a fixed offset.
- Python documentation — unicodedataStandard-library Unicode database access used by the free/local lab.
- Python documentation — hashlibStandard-library hashing used for evidence fingerprints; hashes demonstrate identity of serialized evidence, not semantic correctness.