Reconciliation patterns: counts, totals and key-level diffs

Reconciliation is the core technique of data-layer testing. Course sap-dmg-302 taught count, value and attribute levels for migration. The data layer uses the same levels, with two additions: reconciliation must be repeatable every cycle, and it must work while data keeps changing. This lesson shows the three patterns Contoso uses, from cheap to precise.

Pattern 1: counts by dimension

Count rows in source and target, grouped by the dimensions that matter: company code, fiscal period, document type, ledger. A total count hides offsetting errors (500 missing rows in 1710 and 500 duplicates in 1810 still match), so always count by group. For billing items, Contoso counts VBRP items per company code (via the billing header), billing type and billing date month; for journal entries, ACDOCA lines per company code, ledger (0L) and fiscal period.

Pattern 2: totals by dimension

Sum the key measures by the same groups: net value and quantity for billing items; amounts in company code currency and transaction currency for journal entries. Totals catch value errors that counts cannot: wrong sign, wrong currency field, truncated decimals. Reconcile the measure in the currency in which it is stored before you compare translated values, so translation rules do not hide load errors.

Pattern 3: key-level diffs

When a group differs, find the exact keys. Extract the keys (billing document and item) from both sides for the failing group and compare them: keys only in the source are missing, keys only in the target are extra or stale, keys in both with different values are wrong updates. For large volumes, compare a hash of the relevant fields per key rather than every field. The 21 missing items in 1810 turn out to be billing type F8 (pro forma) excluded by a filter Noor added to remove pro forma invoices from margin, which is correct for the model but should not apply to the base table.

Reconciling while data moves

For open periods, use a cut-off: note the last delta run's timestamp, filter the source to documents created or changed before it, and compare. Differences in documents changed after the cut-off are timing, not defects. For production monitoring after go-live, Contoso schedules a daily count-and-total check for the previous day's postings, run after the morning delta, with alerts on any non-zero difference.

Tooling does not need to be exotic. On the SAP S/4HANA side, use a CDS-based query, SE16H with grouping and totals, or an analytical app with the same selection; on the Datasphere side, a SQL view or the data preview in the Data Builder, or a database user with read access to the space schema. What matters is that both sides run the same selection, the queries are stored, and the outputs are kept with timestamps. For volumes in the millions, push the comparison into the database (group and hash there) rather than exporting rows to spreadsheets, which truncate and round.

Reconciliation also has a third level, inherited from migration work: attributes. Counts and totals cannot tell you that a customer’s country, a product’s profit center or a document’s billing date arrived wrong. For attributes, compare hashes per key as above, or sample: Contoso samples 60 random billing items per cycle and checks every field against the source, using the sampling rules from sap-dmg-302. A zero-error sample of 60 gives roughly 95% confidence that the error rate is below 5%; anything found means fixing the cause for every record and sampling again.