Data engineering · 6 min read
How to reconcile data after migration and CDC
Choose reconciliation checks by risk and complexity, validate migrated and CDC data step by step, and report coverage, exceptions and acceptance.
Start simple, deepen checks where errors matter
Start with row counts per table, then compare partition coverage and partition row counts. Add column totals, min/max, nulls and distinct dimensions to find specific defects. Use key, hash and field comparison when record-level correctness matters. Every method has blind spots. Choose a combination rather than treating one matching number as proof.
For reproducible staging data, counts and profiles may be sufficient with accepted residual risk. For critical migration cutovers, add complete key/value comparison and business-rule acceptance. For ongoing CDC, check changed scope frequently and schedule broader drift scans. Sampling only establishes what was checked in the sample.
Define a comparable boundary first
- Specify the contract: tables, filters, keys, exclusions, expected transformations, comparison columns, tolerances and owners. For transformed targets, derive the expected output at the target grain rather than demand raw equality.
- Align the data state: for cutover, quiesce writes, record the source commit position, drain CDC through it and preserve both comparison views. Alternatively use coordinated snapshots or reconstructable historical states.
- Record the evidence: snapshot identifiers, log positions or per-partition offsets, extraction times, mapping version and query version. A wall-clock timestamp alone does not establish equivalent transaction state.
Compare methods: complexity, pros and cons
Complexity means implementation effort, not guaranteed runtime. Even an exact row count can scan a large table. Grouping and distinct checks may require sorting or shuffling; hashes add CPU; full comparisons add joins and data movement. Combine compatible profiles in one scan where supported.
| Method / effort | Pros | Cons / blind spots |
|---|---|---|
| Table row count · Low | Simple completeness signal | Missing and extra rows can cancel |
| Partition inventory · Low | Finds missing periods or ranges | Existence says nothing about contents |
| Rows per partition · Low–medium | Localises volume differences | Wrong values can retain identical counts |
| Column sums · Low–medium | Useful amount and quantity controls | Offsetting errors and swapped values can pass |
| Min/max and nulls · Low | Finds boundary and missing-value changes | Does not inspect values between extremes |
| Distinct values and frequencies · Medium | Finds category loss and distribution changes | Does not prove correct row assignments |
| Native checksum · Medium | Compact change signal | Collisions and engine-specific semantics |
| Row/partition hashes · Medium–high | Compares many columns with compact output | Canonicalisation, scan cost and collision risk |
| Key-set comparison · Medium–high | Identifies missing and unexpected records | Does not verify non-key values |
| Full field comparison · High | Pinpoints every compared value difference | More compute, transfer and exception output |
How to run each method and when to use it
- Table row count: run
COUNT(*)per table on both sides; report source, target and delta. Use for every load. Catalogue estimates are unsuitable for acceptance; equal counts do not prove equal records. - Partition count and inventory: compare the number and identities of expected partitions. Twelve months on both sides can still mean one missing month and one unexpected month. Include empty partitions from metadata or an expected manifest. Compare logical date/key ranges when physical partitioning differs.
- Partition row count: group by the same month, tenant or key range; compare counts with a full outer join so missing groups remain visible. Use for partitioned loads and backfills; report each failing partition rather than only a grand total.
- Sum of value columns: compare
SUM(amount)or quantities by period, entity and currency. Use for numeric business measures. Agree decimal precision, rounding and tolerances; never sum unrelated currencies. Errors of +100 and −100 cancel. - Min/max and null profile: compare
MIN,MAXandCOUNT(*) - COUNT(column)for dates and important fields. Use to catch truncated date coverage or lost values. Distinguish empty groups from zero totals; many aggregates ignore nulls. - Distinct dimension comparison: compare
COUNT(DISTINCT status), then actual status sets in both directions and row counts per status. Use for codes, countries and product dimensions. Sets {A,B} and {A,C} have equal cardinality; matching frequencies still cannot detect categories assigned to the wrong records. Check nulls separately. - Checksum: calculate a documented checksum over selected columns, then compare by key or aggregate per partition using an agreed algorithm. Use as an optional screening check within compatible engines.
- Hash comparison: encode columns consistently, hash each row with the same algorithm such as SHA-256, then compare by key. Include keys, ordering and duplicate multiplicity in any partition digest. Use for wide tables; drill failed partitions into rows and fields. Matching hashes are probabilistic evidence, not proof.
- Key-set comparison: anti-join source against target and target against source. Report missing, extra, null and duplicate keys separately. Use when counts disagree or record completeness matters; a duplicate can conceal a missing key in a total count.
- Full field comparison: join on a verified unique key and compare each mapped field with null-safe equality. Without unique keys, use a duplicate-preserving multiset comparison. Use for critical fields and unexplained hash differences; record key, column, expected and actual values.
For hashes and direct comparison, agree types, column order, null-versus-empty, timestamp zones, decimal representation and unambiguous field boundaries. Preserve meaningful case and whitespace. Compare transformed expected values at the target grain. Chunk scans, limit concurrency and record excluded columns as coverage gaps.
Deliver reports that support a decision
- Acceptance summary: run ID, scope, boundary, required depth, checked/eligible rows and columns, excluded objects, failures, pending checks and go/no-go decision.
- Technical evidence: method, table/partition, column, source result, target result, delta, tolerance and status; retain source/target identifiers, rule/query versions, duration and evidence location.
- Business acceptance: rule outcomes, report tie-outs, tolerances and owner approval.
- CDC operations: freshness, backlog, replay/delete results, drift trend and age of unresolved exceptions.
- Remediation register: severity, affected keys, root cause, owner, correction, rerun ID and closure evidence. Restrict raw sensitive values; use masked examples and controlled drill-down.
Illustrative status: 98 of 100 required partitions passed, one failed and one pending means incomplete acceptance, even if checked rows mostly match. Define mismatch-rate denominators and never count untested data as passed.
Block cutover on unexplained critical discrepancies or incomplete required coverage. Time-bound any accepted exception with an owner. Correct the cause, repair or replay the affected scope, then rerun reconciliation and downstream checks before closing it. Coordinate repairs with the active CDC writer so newer changes are not overwritten.
For wider planning, see the migration readiness guide and production CDC checklist.