Data Migration Reconciliation Report Template

Load identification, record count balance, control totals, field sampling, rejected records, defects, tolerance assessment, business validation, trend, sign-off

Управління проєктамиПовнийDAMA-DMBOK2 Revised (2024) data quality knowledge areadata migration reconciliation mathОновлено 22 жовт. 2026 р.

Відкрити в редакторіЗавантажити .md

Як використовувати: Use it after every mock load, dress rehearsal and the production load to prove that migrated data is complete and correct. Fill the record count table first: extracted must equal excluded plus loaded plus rejected, with zero unaccounted. The main pitfall is reporting the load tool's success counts instead of independent source and target queries.

Перегляд

Data Migration Reconciliation Report: [Load ID] for [Programme]

Evidence report for one data migration load (a mock load, a dress rehearsal or the production load). It proves that every in-scope record is accounted for, that control totals and balances agree, that sampled records match field by field after mapping, and that business owners have validated the result, and it records rejected records, defects, the tolerance assessment and sign-off. Data quality checks follow the data quality knowledge area of DAMA-DMBOK 2nd Edition Revised (March 2024); the load design comes from the Data Migration Strategy (architecture/data-migration-strategy). Replace every [bracketed] value and every row marked Example.

Document control

Field	Value
Document ID	[DMR-MCK03-001]
Load ID	[MCK-03]
Load type	[Mock load / Dress rehearsal / Production load / Delta load]
Programme	[Programme name]
Strategy reference	[DMS-001 version]
Source extract taken	YYYY-MM-DD hh:mm [time zone]
Target environment	[Environment name, client or tenant]
Version	[0.1]
Status	[Draft / In review / Signed / Superseded]
Owner	[@data-migration-lead]
Approvers/reviewers	[@business-data-owners], [@finance-controller], [@qa-lead]
Last updated	YYYY-MM-DD
Classification	[Confidential: contains record-level samples]

Approvals

Role	Name	Decision	Date
Data migration lead	[@name]	[Submit for sign-off]	YYYY-MM-DD
Business data owner, customer and sales objects	[@name]	[Accept / Accept with conditions / Reject]	YYYY-MM-DD
Business data owner, supply chain objects	[@name]	[Decision]	YYYY-MM-DD
Finance controller	[@name]	[Decision]	YYYY-MM-DD
Quality assurance	[@name]	[Decision]	YYYY-MM-DD

Revision history

Version	Date	Author	Change summary
0.1	YYYY-MM-DD	[@name]	First issue after load completion
0.2	YYYY-MM-DD	[@name]	Example: Root cause of 4 unaccounted material records added
1.0	YYYY-MM-DD	[@name]	Example: Signed as Fail on MO-001 and MO-003, conditions DMR-C-01 to DMR-C-03; targeted MCK-04 approved by the steering group

Load identification
Scope of this load
Method and tolerances
Record count reconciliation
Control totals and balances
Field-level comparison
Rejected and exception records
Defects
Tolerance assessment
Business validation
Trend across loads
Conclusion and sign-off
Glossary and references

1. Load identification

Item	Value
Load window	Example: Start YYYY-MM-DD 18:00, end YYYY-MM-DD 10:30 (16.5 h)
Planned window	Example: 20 h or less (strategy objective DMO-003)
Source systems and extract method	Example: SRC-01 legacy ERP read-only replica, extract views v3.2; SRC-02 AP spreadsheet upload
Pipeline version	Example: Migration repository tag `dm-release-3.0`, mapping workbook v2.4, crosswalks VM-001 to VM-018 at v2.1
Target configuration baseline	Example: Configuration transport set [ID], frozen YYYY-MM-DD
Executed by	[@migration-dev-1], [@migration-dev-2]
Reconciled by	[@data-steward-1], [@finance-analyst] (independent of the load team)
Query scripts used	[Repository path and commit]

Deviations from the planned run: Example: MO-007 price conditions were rerun once after a lock timeout; the rerun is the one reported.

2. Scope of this load

Object ID	Object	In-scope rule (from strategy)	Change since previous load
MO-001 (Example)	Customer master	Active, or any transaction in the last 24 months	None
MO-002 (Example)	Vendor master	Active, or open item, or payment in the last 24 months	None
MO-003 (Example)	Material master	Not flagged for deletion, and stock, open order or sale in the last 36 months	None
MO-004 (Example)	Open receivable items	Open on cutover date	None
MO-005 (Example)	Open sales orders	Not flagged for deletion	Deletion flag now read from header and line (CR-DM-007)
[MO-NNN]	[Object]	[Rule]	[Change]

Example: Objects MO-006 to MO-008 were also loaded and are omitted from the example rows for brevity. A real report lists every object in the load.

3. Method and tolerances

3.1 Check levels

Level	Method in this load
L1 Record counts	Count queries on the source extract, the exclusion log, the target and the exception store
L2 Control totals	Sums by company code and currency on the source extract and on target reports, compared to the cent
L3 Field-level comparison	Random sample per object (full population for objects under 5,000 records and for all open financial items) compared after applying the mapping to source values. Critical fields per object are taken from the mapping workbook (strategy section 11.2)
L4 Business validation	Owners checked named records and ran day-one transactions (section 10)

3.2 Tolerances applied

Object type	Rejected	Control total	Field mismatches
Master data (Example)	0.1% or lower of expected	Not applicable	0 on critical fields, 0.5% or lower of field comparisons on others
Open financial items (Example)	Listed with value; 0 at production load	Unexplained variance USD 0.00	0
Open orders (Example)	0.1% or lower of expected	Unexplained variance USD 0.00	0 on quantities and prices

4. Record count reconciliation

Definitions used in the table:

Extracted: records in the source extract for the object.
Excluded: records removed by the agreed in-scope rule, listed in the exclusion log.
Expected: extracted minus excluded.
Loaded: records present in the target, counted by an independent target query.
Rejected: records refused by validation or by the target, present in the exception store.
Unaccounted: expected minus loaded minus rejected. Must be zero.

Object ID	Extracted	Excluded	Expected	Loaded	Rejected	Unaccounted	Rejection rate
MO-001 (Example)	52,400	4,190	48,210	48,198	12	0	0.025%
MO-002 (Example)	9,850	2,530	7,320	7,320	0	0	0.000%
MO-003 (Example)	31,600	3,660	27,940	27,905	31	4	0.111%
MO-004 (Example)	18,455	0	18,455	18,452	3	0	0.016%
MO-005 (Example)	3,120	85	3,035	3,035	0	0	0.000%
Total (Example)	115,425	10,465	104,960	104,910	46	4	0.044%
[MO-NNN]	[n]	[n]	[n]	[n]	[n]	[n]	[%]

Example check: 104,910 loaded plus 46 rejected plus 4 unaccounted equals 104,960 expected, and 115,425 extracted minus 10,465 excluded equals 104,960. The overall load rate is 104,910 / 104,960 = 99.952%.

The rejection rate is rejected divided by expected:

r_{\text{rej}} = \frac{N_{\text{rejected}}}{N_{\text{extracted}} - N_{\text{excluded}}}

Example: for MO-003, 31 / 27,940 = 0.111%, which exceeds the master data tolerance of 0.1%.

Unaccounted records found: Example: the 4 MO-003 records were extracted but neither loaded nor logged as rejected. Root cause: the load tool dropped records whose description contained a control character without writing an error. Defect DEF-DM-031 (section 8).

5. Control totals and balances

Control ID	Object	Measure	Source value	Target value	Difference	Explained by	Unexplained
CT-001 (Example)	MO-004	Open receivables, company 1000, USD	14,862,407.35	14,859,912.10	2,495.25	3 rejected items: 1,250.00 + 845.25 + 400.00 = 2,495.25	0.00
CT-002 (Example)	MO-001	Sum of credit limits of in-scope customers, USD	412,350,000.00	412,310,000.00	40,000.00	Credit limits of the 12 rejected customers	0.00
CT-003 (Example)	MO-004	Count of open items by ageing bucket over 90 days	1,204	1,204	0	Not applicable	0
CT-004 (Example)	MO-005	Open order value, USD	6,918,442.80	6,918,442.80	0.00	Not applicable	0.00
[CT-NNN]	[Object]	[Measure]	[Value]	[Value]	[Difference]	[Explanation]	[Value]

Rules applied:

Reconcile by company, currency and, for ledgers, by account; a match of grand totals alone is not sufficient.
Compare absolute differences per line before netting.
Explain each difference by listing the records that cause it; the sum of the listed records must equal the difference exactly.
For the general ledger, confirm that debits equal credits in the target opening balance journal and that each balance-sheet account agrees with the legacy trial balance at cutover.

6. Field-level comparison

Object ID	Population	Sample size	Fields compared per record	Field comparisons	Mismatches	Mismatch rate	Critical-field mismatches
MO-001 (Example)	48,198	500	18	9,000	3	0.033%	3
MO-002 (Example)	7,320	300	16	4,800	0	0.000%	0
MO-003 (Example)	27,905	500	22	11,000	0	0.000%	0
MO-004 (Example)	18,452	Full population	9	166,068	0	0.000%	0
MO-005 (Example)	3,035	Full population	12	36,420	0	0.000%	0
[MO-NNN]	[n]	[n]	[n]	[n]	[n]	[%]	[n]

Example: for MO-001, 500 records times 18 fields gives 9,000 comparisons; 3 mismatches give 3 / 9,000 = 0.033%. All three were in the tax number field, where the source held spaces that the mapping should remove (DEF-DM-028). The tax number is a critical field for MO-001 because invoicing cannot run without it (strategy PRF-001), so the 3 mismatches count against the zero tolerance on critical fields even though the overall rate is low. For MO-004, 18,452 records times 9 fields gives 166,068 comparisons.

Confidence from a clean sample: with $n$ sampled records and no mismatched records, the approximate 95% upper bound on the record mismatch rate is $3/n$ (the rule of three). Example: MO-002 with 300 clean records bounds the rate below about 1.0%; MO-003 with 500 clean records bounds it below about 0.6%. If that bound is too wide for the business, increase the sample or compare the full population.

Sampling method: Example: simple random sample drawn by [@data-steward-1] with a recorded seed, stratified by company code.

7. Rejected and exception records

Exception ID	Object	Records	Reason	Root cause category	Fix	Owner	Fix by
EXC-001 (Example)	MO-001	12	Tax number fails target format check	Source data quality	Correct at source (rules DQ-003 and DQ-005)	[@ar-lead]	Before production extract
EXC-002 (Example)	MO-003	31	Unit of measure not in crosswalk VM-012	Mapping gap	Add 6 legacy values to VM-012	[@supply-chain-lead]	Mapping v2.5
EXC-003 (Example)	MO-004	3	Customer rejected in MO-001, so the item has no customer	Dependency on EXC-001	Resolves with EXC-001	[@ar-lead]	Before production extract
[EXC-NNN]	[Object]	[n]	[Reason]	[Source data / Mapping / Target configuration / Tool]	[Fix]	[@owner]	[Date]

Example: the 46 rejected records in section 4 are fully listed here: 12 + 31 + 3 = 46.

8. Defects

Defect ID	Title	Object	Severity	Found by check	Status	Owner	Target fix
DEF-DM-028 (Example)	Spaces not removed from tax numbers	MO-001	Medium	L3	Fixed in mapping v2.5	[@migration-dev-1]	MCK-04 or production
DEF-DM-031 (Example)	Load tool drops records with control characters without error	MO-003	Critical	L1	Open; workaround: strip control characters in transformation	[@migration-dev-2]	Before production
DEF-DM-032 (Example)	Price condition load lock timeout at high concurrency	MO-007	Low	Run log	Fixed: thread count reduced from 8 to 4	[@migration-dev-2]	Done
[DEF-DM-NNN]	[Title]	[Object]	[Critical / High / Medium / Low]	[L1 to L4 / Log]	[Status]	[@owner]	[Load]

9. Tolerance assessment

Object ID	Unaccounted	Rejected vs tolerance	Control totals	Field level	Result	Conditions
MO-001 (Example)	0, pass	0.025% vs 0.1%, pass	CT-002 unexplained 0, pass	3 critical (tax number) vs 0, fail; others pass	Fail	DMR-C-03
MO-002 (Example)	0, pass	0.000%, pass	Not applicable	0, pass	Pass	None
MO-003 (Example)	4, fail	0.111% vs 0.1%, fail	Not applicable	0, pass	Fail	DMR-C-01
MO-004 (Example)	0, pass	3 items listed with value, pass for a mock	CT-001 unexplained 0, pass	0, pass	Pass with conditions	DMR-C-02
MO-005 (Example)	0, pass	0.000%, pass	CT-004 unexplained 0, pass	0, pass	Pass	None
[MO-NNN]	[Result]	[Result]	[Result]	[Result]	[Pass / Pass with conditions / Fail]	[IDs]

Conditions:

Condition ID	Condition	Owner	Due
DMR-C-01 (Example)	Close DEF-DM-031 and extend VM-012; prove in a rerun of MO-003 with zero unaccounted records and a rejection rate of 0.1% or lower	[@data-migration-lead]	YYYY-MM-DD, before production extract
DMR-C-02 (Example)	Clear EXC-001 and EXC-003 at source so that zero open items are rejected at the production load	[@ar-lead]	YYYY-MM-DD
DMR-C-03 (Example)	Deploy the DEF-DM-028 fix (mapping v2.5) and prove it in MCK-04 with zero critical-field mismatches on a fresh 500-record MO-001 sample	[@data-migration-lead]	YYYY-MM-DD, in MCK-04
[DMR-C-NN]	[Condition]	[@owner]	[Date]

10. Business validation

Check ID	Object	Check performed	Records or transactions	Result	Validated by
BV-001 (Example)	MO-001	Top 50 customers by revenue: addresses, payment terms, credit limits, contacts	50	Passed	[@sales-ops-lead]
BV-002 (Example)	MO-004	Customer statements for 20 customers agree with legacy statements at cutover	20	Passed	[@ar-lead]
BV-003 (Example)	MO-005	Deliver and invoice 10 migrated open orders end to end	10	9 passed; 1 price difference traced to MO-007 validity date	[@order-mgmt-lead]
BV-004 (Example)	MO-006	Trial balance in target agrees with legacy trial balance by account	1,140 accounts	Passed	[@finance-controller]
[BV-NNN]	[Object]	[Check]	[n]	[Result]	[@owner]

11. Trend across loads

Load ID	Expected records	Rejected	Rejection rate	Unaccounted	Load duration	Open critical defects
MCK-01 (Example)	104,700	1,284	1.23%	57	31.5 h	6
MCK-02 (Example)	104,880	297	0.28%	0	22.0 h	2
MCK-03 (Example)	104,960	46	0.044%	4	16.5 h	1
[Load]	[n]	[n]	[%]	[n]	[h]	[n]

Example: rejection rate fell from 1,284 / 104,700 = 1.23% to 297 / 104,880 = 0.28% and then to 46 / 104,960 = 0.044%. Load duration is now inside the 20-hour objective. The new unaccounted records in MCK-03 come from a source data pattern not present in earlier extracts, which shows why every mock needs a fresh full extract.

12. Conclusion and sign-off

Overall result: Example: Fail. The MCK-03 exit criteria in the strategy (DMS-001 section 10: all objects within tolerance) are not met. MO-001 fails on 3 critical-field mismatches (tax number, DEF-DM-028) and MO-003 fails on 4 unaccounted records and a rejection rate of 0.111% (DEF-DM-031, EXC-002). MO-002 and MO-005 pass, MO-004 passes with condition DMR-C-02, and the 16.5-hour duration meets DMO-003. Root causes are known and fixes are in progress.

Recommendation: Example: Per DMS-001 section 10, run MCK-04 limited to MO-001 and MO-003 and the objects that depend on them (MO-004, MO-005 and MO-007), from a fresh full extract, to close DMR-C-01 and DMR-C-03; carry MO-002, MO-006 and MO-008 forward from MCK-03. Steering group approval of the reduced scope is required. Cutover planning continues on MCK-03 timings, and the timed steps for the rerun objects are re-measured in MCK-04.

Input to go or no-go: [For the production load: state whether data migration supports the go decision, with reference to the Go-Live Readiness Review (project/go-live-readiness-review) and the Cutover Plan (project/cutover-plan).]

Sign-off is recorded in the Approvals table. A business data owner who rejects must give the reason and the condition for acceptance.

13. Glossary and references

13.1 Glossary

Term	Meaning
Expected records	Extracted records minus those excluded by the agreed in-scope rule
Unaccounted record	An expected record that is neither loaded nor rejected; always a defect
Control total	A sum of an amount or quantity compared between source and target
Explained difference	A difference fully accounted for by listed records or an agreed rounding rule
Rule of three	Approximation that zero events in n trials gives a 95% upper bound of about 3/n on the event rate
Pass with conditions	Result accepted only if named conditions are met by a named date

13.2 References

Reference	Version or date checked	Use
Data Migration Strategy (`architecture/data-migration-strategy`), [DMS-001]	[Version]	Objects, rules, tolerances
DAMA-DMBOK: Data Management Body of Knowledge, 2nd Edition Revised, DAMA International, dama.org	Revised edition released March 2024; checked 2026-10-23	Data quality knowledge area structure
Hanley, J. A. and Lippman-Hand, A., "If nothing goes wrong, is everything all right? Interpreting zero numerators", JAMA 249(13), 1983	Published article	Rule of three for clean samples
[Defect tracker, exception store, query repository]	[Location]	Evidence

Standard and licence note

Structure aligned with common data migration practice in packaged application programmes and with the data quality knowledge area of DAMA-DMBOK 2nd Edition Revised (DAMA International, March 2024). DMBOK is a copyrighted body of knowledge: only knowledge area names are used and no text, definitions, diagrams or tables are reproduced. This template is original text written for this collection; all tolerances, numbers and results are examples, not normative values. Check the current official versions before use. Template licence: MIT.

Схожі шаблони

Управління проєктами