Define integrity before counting rows
Reconciliation between an operational source and a warehouse should not begin and end with total row counts. The source may continue changing during extraction, while deduplication, history tracking, timezone conversion, and business rules can create legitimate differences downstream. Start by defining the comparison boundary: the source object, business-time interval, source version or snapshot, and warehouse layer being tested. Without that boundary, equal counts offer weak assurance because missing and duplicated records can cancel each other out.
It helps to separate integrity into three layers. Transport integrity asks whether source records reached the landing area. Transformation integrity tests mappings, types, joins, and calculations. Business integrity checks whether results such as order value, inventory movement, or customer status remain meaningful. Each layer should produce its own evidence. Otherwise, when a dashboard looks wrong, the team cannot quickly distinguish an upstream defect from an extraction gap, transformation error, or reporting-definition problem.
Create a reconciliation ledger around stable batch boundaries
Every extraction should register a batch in a reconciliation ledger. Useful fields include the source, dataset, batch identifier, extraction window, source watermark, completion time, status, and relationship to any retry. A watermark may be an increasing key, update timestamp, or CDC position, but each option has limitations. Timestamps can collide or be backfilled; increasing keys do not reveal changes to older rows; CDC preserves change order but introduces log-retention, schema-evolution, and checkpoint-recovery concerns.
When a source remains mutable, do not compare its current state with a warehouse batch loaded yesterday. Prefer a source snapshot, a repeatable point-in-time query, or retained evidence of the keys and watermark range included in the batch. Reconciliation should be granular enough to isolate a problem without forcing a full field-by-field comparison every time. Common controls by date, tenant, location, or partition include:
- Row and distinct-key counts:use both to expose omissions, duplicates, and weak source keys.
- Insert, update, and delete counts:do not validate new records while ignoring corrections and removals.
- Null and type-error counts:surface parser failures, shifted fields, and unexpected schema changes.
- Additive business totals:compare amounts or quantities only after aligning currency, sign, and rounding rules.
- Partition or row hashes:useful for wide records, provided nulls, encoding, column order, decimals, and timestamps are canonicalized first.
Separate explainable differences from defects
Reconciliation rules must understand the target model. One customer row may become several historical dimension versions. A cancelled order may remain in the warehouse instead of being physically deleted. Timezone conversion can move events across calendar-day boundaries. These cases should not be hidden behind generous thresholds. Define their expected relationships explicitly: first verify one-to-one delivery from the source into the landing layer, then apply separate checks for dimension validity ranges, fact-table foreign keys, and aggregate outcomes.
Late-arriving data deserves a deliberate strategy. If users can backdate transactions or offline devices synchronize later, a simple high-water-mark query may permanently miss records. One option is an overlapping extraction window followed by an idempotent merge using a stable business key and version value. This costs more source reads and compute but is straightforward to reason about. CDC reduces repeated reads, yet still benefits from periodic lookback reconciliation to detect log gaps or source changes that failed to emit events. Deletions also need a consistent contract, such as soft-delete flags, tombstone events, or periodic full key-set comparisons.
Make discrepancies diagnosable and repairable
A useful alert says more than “counts differ.” It includes the batch, partition, metric, expected value, actual value, and a small sample of mismatched keys. Store these results in dedicated, queryable control tables rather than only in scheduler logs. Severity should reflect business impact: missing primary keys or unbalanced financial measures will often block publication; formatting differences in noncritical descriptions may be quarantined; delayed data that remains inside its agreed service window may only require observation.
Recovery must be idempotent. Engineers should be able to re-extract, re-transform, and recheck a specific batch or partition without creating duplicates. Retain raw landing data long enough to cover the investigation and replay window, and record the code, schema, and rule versions applied to each batch. Finally, assign ownership and response expectations. The data platform team can own transport and batch state, domain teams can validate business rules, and source-system teams can correct upstream defects. Without clear ownership, even technically excellent controls become a growing queue of ignored alerts. For complex estates, an integration team can help define these contracts across system boundaries, but the controls should remain transparent and operable by the teams that own the data.