On a Dynamics 365 implementation, we went through three full reconciliation cycles before Go-Live to clean up migrated financial data. The root cause was not volume. It was scope.

The client had asked us to migrate historical GL, closed AR, and closed AP transactions. The request sounded reasonable in scoping, and we agreed to it. The problems surfaced during testing, when the sub-ledger to GL ties would not balance. Each iteration closed some variances and exposed others, and it took three passes before the numbers held.

Alongside the financial data, we had also agreed to migrate transactional attachments from the legacy system — every invoice, receipt, and supporting document tied to a migrated record. The volume ended up being substantial, and most of those attachments have not been opened since.

The migration succeeded. The reconciliation balanced. But the lesson was not in the cleanup work. It was in the question we should have asked earlier and did not: did we need to migrate this at all?

Why The Question Gets Skipped

In almost every Dynamics 365 Finance & Supply Chain implementation I have seen, the default migration scope is wider than it needs to be. Two patterns show up again and again.

The first is a concern about losing access to legacy data. Business users assume that when the legacy system is turned off, their ability to look up historical context goes with it. This concern is rarely raised directly in a scoping workshop, but it shapes how users answer every question. When the assumption is that every past transaction might be needed someday, the safest answer is “bring it all over.”

The second is more reasoned, and harder to push back on because it has genuine merit. Finance teams want Dynamics 365 to become the single source of truth for reporting, forecasting, and trend analysis. A single source of truth is valuable. What tends to get underestimated is the cost of building that source of truth out of migrated historical data rather than archived analytical data. The two are not the same thing, and conflating them is how migration scopes grow.

Neither of these drivers is wrong. But neither one is addressed by migrating more data. They are addressed by negotiating the scope honestly, and by offering alternatives that meet the real underlying need.

The Discovery Sequence Starts Earlier Than You Think

By the time a scoping workshop happens, the migration conversation is usually already too late. The client has an expectation. The partner has given a price. And nobody wants to be the person who reopens the commercial conversation.

The honest answer is that data migration discovery should start during pre-sales. Not in detail — you are not building a mapping document on a sales call — but in principle. The core migration scope should be named, negotiated, and effectively frozen before the statement of work is signed. Entities can be added later as necessary, but the center of gravity should be set early.

Inside the workshop itself, the first three questions I ask are simple:

What data elements do you want to migrate? Open, unfiltered. Let the client list everything.

For anything closed or historical on that list — why? This is the question that does the real work. It forces the client to articulate the underlying need, which is almost never “I need this data in Dynamics 365.” It is usually “I need to be able to answer this question” or “I need to close this period.”

If the need is reporting or reference, can we meet it without migrating? Read-only access to the legacy system for a defined period. A reporting database fed from legacy with a Power BI layer. A shared folder with linked attachments instead of migrated ones. Most historical data needs are reporting needs in disguise, and reporting needs have better answers than migration.

My working recommendation to most clients is to keep the legacy system available in read-only mode for one year after Go-Live — long enough to validate attachments, to tie out transactional data at the GL level, and to build confidence. In parallel, archive the GL data to a reporting database and point an analytical tool at it. By the end of that year, most users have stopped logging into legacy entirely. They did not need the data migrated. They needed to know they would not lose access.

On attachments specifically, my recommendation now is almost always the same: do not migrate them. Leave them in the legacy shared folder, and put a link in the Dynamics 365 record that points to the file. The user experience is nearly identical. The storage cost and migration risk disappear.

Where AI Is Genuinely Changing This

Data migration has always been a reconciliation-heavy discipline. Source to destination, row counts, control totals, sub-ledger ties. Historically this work has been done by business users and client IT teams reviewing spreadsheets line by line. It is slow, it is expensive, and it is the single biggest reason migration timelines slip.

The place where AI has made a real, measurable difference on my projects is validation rule generation. Custom scripts running in notebooks, with AI-assisted rule generation against the source and destination schemas, can catch classes of variance that a human reviewer would miss — or would take weeks to find. Field-level completeness checks, referential integrity across related entities, statistical profiling to flag distributions that shifted unexpectedly between source and staging. These used to be rules a senior data engineer wrote by hand. They can now be drafted in a fraction of the time and refined iteratively.

This does not replace business validation. A human still has to sign off on the reconciliation. But the manual review shrinks from “read every row” to “review the exceptions the validation surfaced,” which is where the time savings come from.

For teams that are not ready to stand up notebooks, Copilot in Excel is a reasonable starting point for profiling and spotting anomalies in extract files. The ceiling is higher with custom scripts, but the floor is accessible to any team with a Microsoft 365 license.

A Few Technical Choices Worth Being Deliberate About

A handful of small decisions compound across a migration. Two worth naming.

File format. CSV is more performative on import — parsing is faster and the Data Management Framework handles it efficiently at volume. XML handles text qualifiers and special characters more gracefully, which matters a great deal if your source data includes addresses, free-text notes, or any international content. Pick deliberately, per entity, based on what the data actually looks like. Do not default to CSV because it is familiar.

Transformation rules as a living artifact. Every migration accumulates transformation logic — mappings, defaults, conditional rules, cleansing steps. On too many projects, this lives in Excel formulas scattered across working files. When the lead analyst leaves, the logic leaves with them. The rules should live in a single tracker, indexed by both data entity and source system table, so that any rule can be traced from either direction. A SharePoint-hosted Excel tracker works. A dedicated table in the extraction database works better, because it makes the rules executable rather than documentary. Either way, it should be treated as a deliverable, not a byproduct.

And wherever possible, automate the extraction itself. Excel formulae in a working file are a liability — they break silently, they do not version, and they make every extract a manual act. SQL procedures, or scripts written in whatever general-purpose language your team is comfortable maintaining, feeding staged XML or CSV files into Dynamics 365 are stronger patterns. Tie the runs to Scrum gated checkpoints, and the reconciliation evidence produces itself as a side effect of the pipeline.

The Takeaway

Walk into the next migration conversation with questions, not a migration plan.

Ask what the client wants to migrate. Ask why. Ask whether the underlying need can be met a cheaper, safer way — with read-only legacy access, with a reporting database, with a link instead of an attachment. Define your transformation rules explicitly, maintain them as a living document, and build your extraction pipeline so the logic lives in code, not in formulas.

The strongest migrations are the ones where client and partner land on the scope together — with a clear, shared understanding of what is moving and why. Every row that moves should have a reason that both sides signed off on, and every reason should be written down.