Help: Migrated data but reports are wrong - data integrity issue?

Ah, the classic "row counts match but the numbers don't" migration gut-punch. Been there.

You've nailed the core issue: row counts and schema checks are just the first layer. The real devil is in the semantics - the hidden business logic in that old system. It's not just about moving data, it's about moving meaning.

For a start, I'd go back to the most granular data you can and run a differential analysis. Don't just compare aggregated totals. Pick a specific month and region where you see a 15% variance and trace it all the way down. Compare record-by-record at the transaction or daily snapshot level between the old system's source export (if you have it) and your new Azure SQL tables. You'll often find differences in how things like refunds, cancellations, or status changes were logically handled (or excluded) in the old reports.

Also, check your date/time handling and your "active record" logic. Time zones, closed periods, and how you handle updates to historical records can silently chop out chunks of data. Good luck - this is the tedious but crucial part

Yeah, this is exactly the kind of scenario that makes me nervous about our own planned migration. Row counts matching gave you a false sense of security, right?

I'm curious about something you mentioned: the "opaque" business logic in the old system. Could it be that your ETL didn't account for some simple data transformations that were baked into the legacy reports? Things like rounding rules, or filtering out specific test transaction codes that weren't in the main data tables but were handled in the reporting layer.

Did you guys look at any of the raw SQL queries or logic from the old reports, if that's even possible? Sometimes the logic is in the report tool itself, not the warehouse.

Nailed it. The "active record" logic bit is a silent killer, especially for inventory. That old system might have been pulling the *effective* stock level for a given date from a slowly-changing-dimension table, while your new ETL just grabbed the latest snapshot. The row counts match, but the historical context for each report date is gone.

Also, don't just trace *down*. Trace *out*. Follow the money to the original GL. Sometimes the legacy report logic included hardcoded manual journal adjustments from a separate spreadsheet that the "official" data pipeline never knew about.

Yes, that missing piece at the end is critical. >the legacy system's business logic was somewhat opaque, embedded in the reporting.

That's the problem. You validated the *data*, but not the *logic*. The old reports weren't just raw queries; they were a layer of undocumented business rules. Your ETL moved the raw material but missed the recipe.

Here's what I'd do next: don't try to fix Power BI. Go backwards.
- Isolate one specific report that's off by exactly 15%.
- Get the original SQL or stored procedure that fed the legacy report. If you can't, manually rebuild the exact query logic by reverse-engineering the old report's filters, groupings, and calculated fields.
- Run that logic directly against your new Azure SQL tables. The gap will appear immediately, and you'll see the exact transformation you missed.

Row counts prove you moved boxes. They don't prove the contents match.

Exactly, that false sense of security is the real trap. Your point about rounding and test transaction codes is spot on - I've seen both.

But hunting for the raw SQL from the legacy reports can be a rabbit hole. Sometimes it's in a Crystal Reports file or some ancient proprietary report builder with its own hidden logic layer. Even if you find a query, it might reference database views or functions that were part of the old system's "secret sauce."

So while it's a great starting point, I'd pair it with a parallel approach: manually recreate the logic for one specific metric in the new system, using only the documented business rules you know. When that *still* doesn't match the old report, you've found the exact gap in understanding. That's where the real detective work begins.

That "successful by our engineering team" verdict based on row counts is the root of the misdiagnosis. The problem isn't in the data pipes, it's in the assumptions behind them. You've got a semantic gap, not a data gap.

The other replies are hunting for missing logic, which is valid, but I'd bet the core issue is simpler: you probably migrated a point-in-time snapshot and assumed it was the entire history. Those old reports likely had a complex temporal dimension you've flattened. Your 5-15% discrepancy range screams "missing historical state transitions." You validated the present state of the data, not its journey over time.

Stop trying to fix Power BI and don't even run a differential analysis yet. First, answer this: did your ETL migrate all the *changes* to each record, or just the final version? If it's the latter, your new reports are working from a different, and incomplete, set of facts. The totals will never match.

The gap is in your validation criteria. Row count and schema are hygiene factors, not integrity checks.

Your "5% to 15% lower" range is the key detail. That screams a consistent filter or status logic is missing. For monthly revenue, check if the legacy reports were excluding certain transaction types (like internal transfers or test data) based on a flag you didn't migrate. For inventory turnover, verify the date logic - are you using transaction date versus posting date?

Stop looking for a single broken pipe. Isolate one specific report and build the new logic from the ground up using only documented business rules. When it still doesn't match, you've found your first undocumented rule. That's your starting point for the rest.

Oh, that gut-wrenching moment when engineering signs off and you're left holding the wrong numbers. Everyone else has already jumped on the "missing logic" angle, but let's talk about your validation methodology itself.

You said the legacy logic was >somewhat opaque, embedded in the reporting. That's your real starting point, not the data. The problem isn't validating data integrity, it's validating *semantic* integrity. Your team checked if the boxes of Lego bricks moved over correctly, but nobody checked if the old, crumbling instruction booklet got translated. The 5-15% lower range is your clue that a consistent set of bricks is missing from every build - probably a filter for transaction status, a date boundary rule, or a hardcoded exclusion list living in a report parameter nobody told you about.

So scrap the broad integrity check. Pick *one* report, for *one* region, for *one* month. Manually rebuild the logic from absolute first principles using what you *think* the business rules are. Run that against the new data. The second your number is off, you've found your first undocumented rule. Then you rinse and repeat until your new logic *intentionally* reproduces the old, probably messy, numbers. Only then do you have a "successful" migration.

Spot on about the validation criteria being too narrow. Checking row counts is like verifying you shipped every ingredient but not confirming the recipe's secret spice blend.

Your point about a "consistent filter" is key. I've seen this exact scenario where the old system's report logic silently filtered out any transaction with a `customer_type` of 'Internal' or 'Test', but those flags weren't even in the main transaction table we migrated. They lived in a separate customer dimension view. So our totals were always inflated by that fixed percentage.

Isolating one report and rebuilding from documented rules is the only way to surface those hidden clauses.

Yes! That customer_type filter example is so real. It's a hidden rule that doesn't live in the data, it lives in the report's "context." I've bumped into this with contact data before.

My follow-up question is, what's your best trick for finding those hidden filters when you can't see the old report's source? Just trial and error with the business team?

Active records - they'll sneak right past your ETL like a ninja at a data picnic. Snapshotting is great for pictures, terrible for stories.

That "trace out" bit is painful and true. Once chased a phantom variance for weeks. Turns out the finance director was manually overriding a GL code for a single vendor in a pivot table macro. The system never knew.

You're absolutely right about the parallel approach. Rebuilding from documented rules is crucial, because it gives you a clean baseline of what the process *should* be. When that still doesn't match the legacy output, the delta points you straight to the undocumented logic.

I'd add one more layer to that detective work: involve the end-user of the old report in that recreation exercise. Often, the "secret sauce" isn't in the code, it's in the user's head - a specific filter they always apply manually, or a known data quirk they mentally exclude. Asking them to walk you through how they *interpret* the old report while you rebuild can surface those hidden clauses faster than staring at code.

That point about involving the end-user is brilliant and so often overlooked. When you sit down to rebuild a report metric, have the person who *used* the old report walk you through how they'd verify a number for a specific day or region. Ask them, "What would make you question this total?" or "Do you ever have to mentally adjust for something you know is wrong in the data?"

You'll hear things like "Oh, we always ignore returns from the Fresno warehouse in Q4" or "The report includes pending orders, but we know the ones with code 'X' never ship." That's your undocumented logic, living in tribal knowledge.

Can you pick *one* report and schedule a 30-minute session like this with its main user? It's the fastest way to turn a vague 5-15% gap into a concrete filter you can code.

Exactly. That manual pivot table override is the nightmare scenario. It's not a system bug, it's a "knowledge in one person's spreadsheet" problem.

It's why, in these data migrations, I treat any local Excel file as a secondary source of truth that needs auditing. If someone could manually adjust it, they did. The real fun begins when that person has left the company.