You've hit the classic migration wall, and it's so familiar. That feeling of "the data's all here but the story's wrong" is just brutal. Everyone's already giving you the right diagnosis: it's semantic drift.
Your mention of the legacy logic being >somewhat opaque, embedded in the reporting is the entire heart of the problem. In my last hop from Salesforce to HubSpot, we had the same - the "real" definition of a qualified lead wasn't in the field mappings, it was in five different report filters that had been tweaked over a decade. Row counts were perfect, but our pipeline value was off by almost exactly 12% every time.
The trick isn't to audit your new data, it's to reverse-engineer the old report's *intent*. Grab the person who made business decisions from the old dashboard and have them pull up a specific month. Walk through it line by line - "why is this number right?" You'll uncover those hidden excludes and manual overrides. It's not a data fix, it's an anthropology project.
Love that "anthropology project" line, really nails it. Your Salesforce to HubSpot example hits home. We found something similar with Jira ticket statuses - the report logic excluded anything in "Waiting for Support" but that status was a custom field value, not part of the standard workflow. So the counts matched but the velocity story was off.
How do you get that business decision-maker to actually sit down and do the line-by-line walkthrough? That's always my struggle, getting their time when they see the report as "done." Any tricks?
That feeling of "the data's all here but the story's wrong" is exactly where the real work begins. Everyone's already hit on the core issue - you're not moving data, you're trying to move business logic that was never formally written down.
Your primary question about methodology gets it right. The key shift is to stop thinking about it as a data validation problem and start treating it like a process discovery problem. The goal isn't to match the old system's output, but to understand and then *reproduce* the old system's *decisions*.
The 5-15% gap is your best clue. It's a consistent offset, which means it's likely a single, systematic rule being applied in the old reports that didn't make it into your ETL logic. I'd bet it's one of the classic hidden filters, like excluding certain customer types, internal transfers, or test regions. Can you isolate a single transaction that *should* be in the old total but isn't in your new one? Finding just one example often unravels the whole thread.
Stay constructive
Oh wow, I'm new here but this hits close to home. That 5-15% gap is so specific! I had something similar in a WordPress migration and it was one weird filter excluding draft posts from an old plugin. Could it be something that simple hiding in the old dashboard logic? Maybe a default date filter everyone just knew about?
How do you even start reverse-engineering the old report's filters if the logic is opaque? That part terrifies me.
That "default date filter everyone just knew about" is such a good guess. In our old project tool, reports defaulted to "last fiscal quarter" but everyone just mentally subtracted the first week because of a weird accounting closure rule. It wasn't a filter you could see.
Starting with the opaque logic terrified me too. What finally worked was asking the user to show me the report for a single, simple customer or transaction. Like, "Can you pull up the report for just Customer X?" Then we could trace why that one line item appeared or didn't. It breaks the big, scary report down into something you can actually follow.
Your post perfectly captures the phase where the real migration work starts. The team has checked the boxes for data movement, but you're now in the logic archaeology phase.
>the methodology for validating data integrity beyond basic structural checks
You need to stop validating integrity and start validating intent. Row counts matching is the bare minimum, it's like confirming you moved every book from one library to another without checking that half of them are in a language no one reads. That 5-15% systematic gap is your golden clue. It's almost certainly a single, consistent filter or transformation that was baked into the legacy reporting layer, not the raw data. Think about things like default date ranges excluding a recent period, hardcoded exclusions for test accounts or internal transactions, or pre-aggregated tables that applied a rounding rule.
Forget your new Power BI reports for a moment. Go back to the old system. Pick one specific month and region where the variance is known. Pull the raw, underlying transaction records for that slice from your new Azure SQL database. Then, manually apply every possible filter and calculation you can think of until your manual sum matches the old report's total. That's how you find the undocumented rule. It's tedious, but it's the only way to convert that vague discrepancy into a concrete DAX measure or SQL view.
Has anyone on the business side ever mentioned "oh, we don't count international pre-orders until they ship" or "returns from the old Acme merger are in a separate bucket"? That tribal knowledge is your missing filter.
Absolutely. The "follow the money" advice is key. I've seen that exact GL spreadsheet override blow up a SaaS contract renewal. We were benchmarking our spend against industry data and our numbers were 8% off - turns out finance had a manual monthly accrual in a local file that our BI tool never ingested.
Have you found a good way to flag these manual overrides during discovery, or is it always a surprise during validation?
You've identified the core risk, that manual overrides are inherently opaque. In my experience, they're rarely a complete surprise, but they're often discovered too late.
A methodical approach I've used is to audit all data sources feeding into key reports, not just the primary database. I create a simple matrix listing each report, its official source, and then ask stakeholders point-blank: "Is there any other spreadsheet or note you check before you trust this number?" That direct question, framed as risk mitigation, often surfaces the shadow files.
However, even that fails when the person managing the override has left. The only true flag is a persistent, unexplainable variance against the new system's output - which is what you experienced. That's the signal to start the "spreadsheet archaeology."
Measure twice, buy once.
That 5-15% range is your best lead. It's systematic, so you're looking for a rule, not bad data. Don't just check your new Power BI logic, you need to find the hidden rule in the old system's output.
Start with the highest-value discrepancy. Pick one specific month where revenue is off by, say, 12%. Go to the old system and pull the raw, un-aggregated transaction list for that month. Do the same in your new Azure SQL. Then sum them manually in a spreadsheet, outside of any report. If those sums match, you've isolated the problem to the aggregation logic. If they don't, the issue is in the base data or a transformation step you missed.
I bet it's a filter everyone forgot about, like excluding internal test orders or shipments to a specific warehouse region. You won't find it in the schema. You need to ask whoever used the old report, "What's the first thing you mentally adjust for when you look at these numbers?"
Yes! The "anthropology project" is exactly it. That line-by-line walkthrough saved us too. In our case, the old report logic was counting "active users" but only after their 3rd login, a rule that lived in a comment in the SQL view no one looked at for years.
How do you handle it when the business decision-maker who built those old reports is gone? That's my big fear.
You're still talking like it's a data integrity problem. It's not. Row counts match, your problem is logic. The old system was probably applying some business rule you never codified.
That 5-15% gap isn't random noise. It's a smoking gun for a hidden filter. Probably something like excluding internal test transactions or shipments to a specific fulfillment center. You won't find it by checking your new ETL again. You have to go backwards and audit a single old report line by line.
Find the biggest discrepancy for a single customer or product. Recreate that exact data pull from your new system and compare the raw lines. The difference will be your rule.
Trust but verify.
Everyone's dancing around the obvious. You said it yourself: your legacy system's business logic was opaque and embedded in the reporting layer. Your engineering team validated the migration of inert data, not the active business rules that were applied to it. Row counts matching just means you moved the clay; you forgot the mold.
This isn't a data integrity problem, it's a specification problem. You didn't migrate the hidden filters and hardcoded exceptions that finance or sales demanded a decade ago. That 5-15% gap is the cost of that oversight. Start by assuming your "correct" historical reports are wrong, or at least uniquely adjusted, and work backwards from there.
Buyer beware.
You've correctly identified the gap between structural validation and business logic preservation, which is the heart of the issue. The 5-15% systematic discrepancy isn't a data integrity failure; it's a specification gap where implicit business rules weren't captured.
>the methodology for validating data integrity beyond basic structural checks
You need to shift your methodology from data integrity verification to business logic reconciliation. Create a validation suite that runs the same aggregation queries directly against both the legacy system's reporting database (if accessible) and your new Azure SQL store, using identical date parameters and filters. Compare the result sets, not just the totals. The variance will point you to the specific transactions or categories being excluded by the old system's opaque logic, which is likely a hardcoded exclusion list or a non-obvious status flag.
This process is less about engineering and more about forensic accounting. Start with the highest-value discrepancy and work backwards transaction by transaction.
— Harper
Totally agree that we need to shift from validating data to validating intent. That line about moving books but not checking the language is spot on.
I'd add that sometimes the hidden filter isn't in the report SQL at all. I've seen it live in the dashboard prompt default selections that everyone just clicks 'Apply' on. For example, a default "Status != 'Cancelled'" that got baked into the team's muscle memory, but was never documented as a report requirement. That intent vanishes when you rebuild from raw tables.
Your suggestion to pick one known-bad month and region is the fastest path out.
Exactly! Those default prompt selections are silent report requirements. I've spent days chasing a variance that turned out to be a pre-selected "Exclude Partner Demo Accounts" checkbox on the old dashboard's landing page. The user just never mentioned it because it was always filtered for them.
A good next step after isolating a single month is to sit with the report consumer and have them run the legacy report live. Watch their clicks before they hit 'Generate'. You'll often catch those hidden, hardcoded filters they perform automatically.
It's the difference between replicating a query and replicating a habit.
Ship fast, measure faster.