ERP Finance Data Migration: What to Load and How to Reconcile It

Quick answer

Plan to migrate the configuration, master data, opening balances and open transactions needed to operate the new ERP. Decide separately which historical transactions belong in the live system and which can remain in an accessible archive. Prove completeness through source-to-target record counts, monetary control totals, exception tracking and business testing—not an import status alone. Microsoft’s migration guidance distinguishes configuration from migration data and calls for repeated validation before cutover. (learn.microsoft.com)

This guide provides a finance-team control framework, with Dynamics 365 documentation illustrating import behavior. It is not a configuration procedure for every ERP. The retention section addresses United States federal tax records, not every state, industry or contractual requirement.

1. Define the migration boundary before extracting data

Start with a written scope register. For each dataset, record the legal entity, source, cutoff, selection rule, destination, owner and reconciliation method. Microsoft’s implementation guidance calls for documented scope, mapping, extraction, testing and validation activities. (learn.microsoft.com)

Use the following as a planning template, not a universal loading prescription:

Dataset	Proposed treatment	Finance acceptance check
Configuration	Establish currencies, periods, accounts, dimensions and posting rules required by the design	Approved configuration supports test postings
Master data	Load customers, suppliers and other records needed for ongoing activity	Required fields, relationships and duplicate decisions verified
Opening balances	Load the approved cutover financial position	Target balances reconcile to the mapped source trial balance
Open transactions	Load unsettled items needed for collection, payment or completion	Outstanding amounts, dates and ownership reconcile
Historical transactions	Load only the approved reporting or operational scope	No overlap with opening balances or other history loads
Retained evidence	Preserve required documents and history in the ERP or an accessible archive	Retrieval and readability tested

Choose a precise cutoff, including time zone where relevant. Specify how transactions entered after extraction will be captured: a controlled freeze, a final incremental extract, or another tested method.

Avoid vague scope descriptions such as “all active suppliers.” Define active: recent use, an outstanding balance, an open order, or another approved business rule.

2. Clean master data and make mapping decisions explicit

Build a mapping register before preparing final import files. Microsoft’s field-mapping example compares source data with target fields, including data type, length and whether a field is mandatory. It also asks business stakeholders why each field is needed. (devblogs.microsoft.com)

For each important field, document:

• Source field and target field.
• Transformation or cross-reference rule.
• Required value and treatment of blanks.
• Validation rule.
• Business owner and approval status.

Keep account mapping separate from dimension mapping. A correct account with an incorrect department can preserve the company total while misclassifying management reporting.

Treat duplicate supplier detection as a review queue, not an automatic merge instruction. Compare identity evidence, addresses, tax identifiers where appropriate, and existing obligations. Similar names alone should not establish that two records represent the same supplier.

When a merge is approved, retain a cross-reference from every legacy supplier ID to the surviving target ID. Redirect associated invoices and other in-scope relationships through that mapping.

Do not let cleanup silently change payment instructions. Route bank-detail changes through the organization’s approved verification process.

3. Design opening balances and open invoices together

Write down the expected accounting entries before choosing a loading sequence. For each opening-balance or subledger load, show which accounts it should debit and credit, including any migration clearing account.

Use this design question:

Does loading the open-item detail also create general-ledger entries?

If it does, calculate the remaining opening journal after considering those entries. If it does not, document how the general ledger will receive the corresponding balance. The approved outcome should be one complete financial position—not two overlapping loads.

For open receivables and payables, propose a field-level acceptance checklist covering:

• Legacy reference and target customer or supplier.
• Invoice date and original due date.
• Currency and remaining unsettled amount.
• Credits, unapplied payments and partial settlements.
• Required reporting dimensions.
• Traceability to supporting documents.

Test the selected import mechanism rather than assuming that two ERPs—or two import entities—behave identically. Dynamics 365’s import process supports entity sequencing, mapping validation, staging and execution-history review. (learn.microsoft.com)

If cutting over during a financial year, also decide how users will obtain year-to-date results and comparative reporting. Keep any historical posting scope explicitly separate from the opening-balance design.

4. Rehearse in a test environment and account for every rejection

Use repeated trial loads before production. Microsoft’s go-live checklist calls for multiple migration tests, checks for duplication and corruption, and confirmation that performance fits the cutover window. It also calls for user acceptance testing with migrated data. (learn.microsoft.com)

A practical rehearsal sequence is:

1. Structural trial: test representative records and mapping rules.
2. Full-volume trial: test dependencies, processing time and reconciliation.
3. Cutover rehearsal: execute the proposed runbook, including final extracts, exceptions and business approval.

Keep test payment outputs and external connections isolated from live execution. Restrict access to sensitive migration files and use approved protections for test data.

In Dynamics 365, staging data and execution logs help investigate failed records. The documentation allows valid records to proceed while errors are corrected later; therefore, a partially successful import still needs a completeness check. (learn.microsoft.com)

Maintain an exception register containing the source key, batch, rejection reason, amount, owner, correction and reload result. Before retrying, verify whether the target already contains the record and how the chosen entity handles a repeated key.

For a one-record-per-item dataset, use:

Expected items = successfully loaded unique items + unresolved items

Track approved exclusions separately. Do not use raw file-row counts when one business document spans several rows.

5. Worked example: duplicate suppliers and a rejected invoice

Illustrative data only: The following amounts and identifiers demonstrate control logic. They are not a customer case study or measured result.

Assume one legal entity, USD only, no foreign-exchange differences and three wholly unpaid invoices.

The source contains four supplier records. Review confirms that S010 and S011 represent the same supplier, and an authorized owner approves their consolidation into V100.

The supplier bridge is:

4 source records − 1 approved duplicate consolidation = 3 target suppliers

Keep both legacy IDs in the cross-reference.

The invoice extract is:

Invoice	Legacy supplier	Target supplier	Outstanding USD	First trial
INV-A	S010	V100	6,000	Loaded
INV-B	S011	V100	3,500	Loaded
INV-C	S020	V200	2,500	Rejected: missing required dimension
Total			12,000

After the first trial:

• Expected: 3 invoices, $12,000.
• Loaded: 2 invoices, $9,500.
• Unresolved: 1 invoice, $2,500.

The totals explain the incomplete load:

$12,000 = $9,500 + $2,500

Correct the approved dimension mapping and reload only the rejected item, after confirming it does not already exist. The expected final position is three unique invoices totaling $12,000.

Now test an illustrative accounting design:

• The open-invoice load debits migration clearing $12,000 and credits accounts payable $12,000.
• The remaining opening journal credits migration clearing $12,000, while its other entries establish the remaining financial position.
• The final payable control balance is therefore $12,000 credit, and migration clearing is zero.

This works only if the chosen ERP import actually produces those entries. Confirm that behavior in testing. Posting another $12,000 credit directly to payables would double the intended liability.

Finally, verify that consolidating supplier records did not consolidate or discard distinct invoices.

6. Reconcile records, balances and operational behavior

Build a reconciliation pack that follows the data through extraction, transformation, import and posting. Import execution details can show successful and failed record counts, but finance acceptance should also compare target financial outputs with the approved source position. (learn.microsoft.com)

Use these proposed checks:

Control layer	Practical check
Extraction	Confirm cutoff, filters, entity coverage and source totals
Transformation	Explain merges, exclusions, splits and value changes
Import	Tie unique accepted and rejected items to the expected population
General ledger	Compare mapped accounts and required dimensions; check debits and credits separately
Subledgers	Tie detailed outstanding items to the relevant ledger control accounts
Operations	Test collection, settlement, aging, reporting and document retrieval

Compare amounts separately by currency; do not add unlike currencies into a single unexplained total.

Require an explanation for every difference. Where rounding is expected, approve its basis and tolerance before loading. Do not classify an unidentified missing invoice as an acceptable rounding difference.

Common mistakes to challenge include:

• Checking only the grand total: inspect accounts and dimensions too.
• Comparing different cutoffs: align report dates and extraction boundaries.
• Counting staging records as completed items: verify target and posting status.
• Reloading an entire file without checking existing records: test repeat-load behavior first.
• Validating balances but not usability: include migrated-data business tests.

These operational tests complement the repeated migration and user-acceptance testing required by Microsoft’s readiness guidance. (learn.microsoft.com)

7. Separate historical access from US record retention

Deciding not to load closed transactions into the operational ERP is not permission to discard them.

IRS Publication 583 says electronic storage must preserve complete, accurate records and support indexing, retrieval and legible reproduction. An archive therefore needs more than an untested export file. (irs.gov)

For US federal tax purposes, Publication 583 explains that:

• Records supporting income or deductions generally remain necessary until the applicable limitation period expires.
• Its table includes a three-year period in specified ordinary circumstances, with longer or unlimited periods in other cases.
• Employment tax records generally must be kept at least four years after the tax becomes due or is paid, whichever is later.
• Property records may remain necessary through disposition and the relevant limitation period afterward. (irs.gov)

Do not convert these rules into a blanket “delete everything after three years” policy.

Before retiring the legacy system, test whether an authorized user can retrieve a historical transaction, its supporting document and the relevant account description. Confirm other applicable retention duties and legal holds with the appropriate advisers.

8. Require evidence-based sign-off and a workable recovery plan

Set the go/no-go criteria before final cutover. Microsoft’s checklist calls for sequenced cutover activities, exit criteria and stakeholder sign-off. (learn.microsoft.com)

Use a sign-off pack that requires:

• Approved scope, mappings and final source extracts.
• Explained record-count bridges.
• Reconciled ledger and subledger balances.
• No unresolved critical rejected items.
• Completed business tests and accessible retained records.
• Named owners for accepted noncritical exceptions.

Assign finance approval to the controller or designated finance owner; assign process acceptance to the relevant operational owners.

As a project safeguard, document the recovery decision-maker, decision deadline, supported restore or reset method, integration restart sequence and treatment of transactions created after cutover.

A backup alone is not a complete recovery plan. Once live activity begins, returning to the legacy system also requires a controlled way to preserve and re-enter that activity. Test the recovery approach in nonproduction; do not improvise production deletions or overwrite posted transactions.

The final question is not “Did the files import?” It is: Can finance explain the complete migrated position, trace its exceptions and operate safely from it?