SQL & reporting · field note
The reports had been wrong for two years and nothing was broken.
No exceptions. No failed jobs. No red dashboard. The reports opened on time, the totals looked believable, and six to eight columns were still telling the wrong story.
This is the unnerving category of software failure: the system does exactly what the code says, while the code no longer says what the business means.
In this case, the error had survived for more than two years. People had made decisions from the output. Support tickets had not revealed it. Traditional uptime monitoring could never have found it, because from the infrastructure's point of view, nothing was broken.
01 / Technical health
A green pipeline can deliver the wrong answer perfectly
Most production monitoring asks operational questions: Did the job complete? Did the API return 200? Did the query stay under its timeout? Did the dashboard render?
Those checks matter. They also stop one layer too early.
A database engine can verify that a column exists and a comparison is legal. It cannot know that category 7 stopped meaning “completed programme” after a data model change. It cannot know the chief executive expects withdrawn learners to be excluded. It cannot know a report labelled “this quarter” uses the wrong business timezone.
Availability asks whether the report is there. Correctness asks whether the report represents the intended reality. One can be excellent while the other quietly fails.
02 / The defect
The business rule had been copied into the plumbing
The immediate defect was familiar: identifiers typed directly into SQL views. They represented categories meaningful to the application at one moment in time. As the product evolved, the meaning moved. The numbers in the views did not.
Worse, the shortcut had multiplied. The same identifiers appeared eight to twelve times in a report, across four principal views and another five to seven dependent views. That created roughly 70 to 130 places where someone could update the rule incompletely.
The result was not an empty report. Empty would have been helpful. It was a populated report with hardcoded IDs silently selecting the wrong records.
The SQL compiled and executed.
Users could open every report.
Rows, columns, and totals looked normal.
Several fields classified data against an obsolete rule.
03 / The camouflage
Bad numbers survive when they look ordinary
A report that suddenly shows zero attracts attention. A report that moves from 1,842 to 1,796 does not—especially when nobody knows the exact number in advance. Plausibility is the camouflage.
Reporting systems also acquire social authority. Once a dashboard has been used in enough meetings, people stop asking where the number comes from. New team members learn the output, not the definition. Small discrepancies are explained as timing, caching, or “how the system has always counted it.”
Then there is diffusion of ownership. The business owns the definition, data owns the warehouse, engineering owns the views, and operations owns the scheduled job. Everyone owns a layer. Nobody owns the answer.
04 / The investigation
You do not prove a report wrong by staring harder at the dashboard
We traced representative outputs backwards: displayed column to report query, query to SQL view, view to dependent views and source tables, then source value back to the current business definition. That lineage turned a vague suspicion into a testable defect.
- Choose known records. Find cases the business can classify with confidence.
- Trace one field end to end. Do not begin with the entire report.
- List every embedded business rule. IDs, status lists, exclusions, date logic, null handling, and fallback values.
- Compare current reference data. Identify values that were renamed, replaced, expanded, or retired.
- Quantify the blast radius. Search every view, procedure, job, export, and application query for the same assumption.
The full sequence and measured scope are in the reporting data correctness remediation case study.
05 / The remediation
Fix the source of truth, not 130 copies of the symptom
A search-and-replace can make today's report look right. It also preserves the architecture that created the error. The durable fix is to move classification into governed tables or explicit mappings, reference those mappings from reporting logic, and make ownership visible.
We corrected historical results, not only future rows. That required comparing old and new output over representative periods, investigating unexpected deltas, and making sure the corrected logic did not erase valid historical meaning.
- Freeze and document the intended business definition.
- Inventory every implementation of that definition.
- Create one governed source of truth with constraints.
- Run old and new logic side by side.
- Reconcile differences with business owners.
- Deploy through versioned migrations and retain rollback evidence.
06 / The guardrail
Test meaning, not only mechanics
A useful reporting test does not merely assert that the view returns rows. It asserts business invariants: a withdrawn record cannot appear as completed; a total equals the sum of its governed categories; an unmapped status is surfaced rather than quietly omitted; a known historical record keeps its expected classification.
Add reconciliation queries, unmatched-record checks, representative golden cases, and change review whenever reference data evolves. If a category can be added without a test failing or an owner being notified, the system is waiting to become wrong again.
The hardest reporting failures do not crash. They earn trust. That is why correctness must be designed, monitored, and owned as deliberately as uptime.
Primary references
Sources and further reading
- Microsoft: CREATE VIEW (Transact-SQL) — official SQL Server view definition reference
- Microsoft: sys.sql_modules — official reference for inspecting deployed SQL module definitions
- Magrid reporting data correctness remediation — the first-hand case evidence behind this article
Questions teams ask
Frequently asked questions
How can a SQL report be wrong without producing an error?
SQL validates syntax and object references, not business meaning. A query can execute successfully, return rows, and produce plausible totals while using an obsolete ID, incomplete join, wrong date boundary, or duplicated business rule.
What are silent data errors?
Silent data errors produce valid-looking output without a runtime failure. Users receive a number, so monitoring stays green, but the number does not represent the intended business definition.
How do you test reporting data correctness?
Test business invariants and known cases, reconcile aggregates to trusted sources, compare old and new logic over historical periods, inspect unmatched records, and involve the people who understand what each metric is meant to mean.
Should reporting logic live in SQL views?
Views can be appropriate for stable, well-defined read models. The problem is not the view itself; it is duplicated and undocumented business logic, magic values, weak ownership, and no tests around the definition.