Validation Beats Conversion: The SSRS Migration Lesson That Saves Your Week

Here's the SSRS migration war story I'd take back if I could: we shipped a converted report that passed every syntactic check, ran cleanly in production, and produced numbers that looked completely reasonable — until someone on the business side noticed the totals were off by 8%.

The bug was one character: a TOP 1 in a T-SQL correlated subquery had been stripped during conversion, and the compensating LIMIT 1 never got added. The subquery quietly started returning multiple rows. PostgreSQL picked one arbitrarily. The report still ran. The answer was just wrong.

That's the bug that taught me validation matters more than conversion.

Two kinds of conversion failures

There are two failure modes in automated SQL conversion. Only one of them is visible:

Mode 1: Hard failure
  → Syntax error, missing function, bad reference
  → Easy to detect: the query won't compile
  → Shows up in the conversion dashboard immediately
  → Gets fixed before shipping

Mode 2: Silent failure
  → Compiles fine. Runs fine. Returns wrong data.
  → Easy to ship accidentally
  → Shows up weeks later when a user notices
  → Gets blamed on "data quality issues"

Every conversion pipeline catches Mode 1. The market-differentiator is how well you catch Mode 2. A converter that reports "95% pass rate" but ships with undetected silent failures is worse than one that reports 80% and catches them — because the first one creates false confidence.

The three-layer validation model

After getting burned on the TOP 1 bug, we built a three-layer validation pass that runs after every conversion. Each layer catches a different class of silent failure.

Layer 1: EXPLAIN validation (cheap, fast)

Run EXPLAIN on the converted PostgreSQL before execution. No data returned, no full scan. This catches malformed plans, missing indexes referenced by hints, and type-system errors that the parser missed.

EXPLAIN
SELECT e.name,
  (SELECT r.review_date FROM reviews r
   WHERE r.emp_id = e.id
   ORDER BY r.review_date DESC) AS last_review
FROM employees e;

-- ERROR: more than one row returned by a subquery
-- used as an expression

-- Layer 1 catches this without running the query.
-- Takes ~5ms. Filters out ~40% of silent failures.

Layer 2: Row-count comparison (medium cost)

Run the original T-SQL against SQL Server and the converted PostgreSQL against PostgreSQL, on the same source data. Compare row counts. Differences are immediate red flags.

Original T-SQL:    1,247 rows
Converted PG:      1,249 rows
Difference:        +2 rows  ← FLAG

Typical causes:
  • JOIN type mismatch (INNER vs LEFT)
  • NULL handling in WHERE clause
  • Missing or additional duplicate-removal
  • Date-range filter boundary difference

Row-count divergence catches the largest class of silent failures. It's not perfect — two different queries can return the same row count but different data — but it's a cheap and high-signal check.

Layer 3: Checksum comparison on pinned samples (expensive, rare)

For a curated set of sample records, compute a deterministic hash of the output rows (sorted by primary key) from both systems. Compare. Divergence here catches type coercion bugs, sort-order issues, and subtle numeric precision differences.

-- T-SQL: CAST(amount AS DECIMAL(10,2))
-- PG:    amount::numeric(10,2)
--
-- Both return numbers that LOOK the same.
-- One uses banker's rounding, the other doesn't.
-- Checksum diverges on the row where amount ends in .5.

SHA256(
  SELECT string_agg(col1||col2||col3, '|' ORDER BY pk)
  FROM converted_output
)
vs.
SHA256(
  -- same output from T-SQL
)

Layer 3 is expensive — you're running two full queries and comparing their output. Reserve it for a pinned sample set of 5-20 reports that represent the critical business metrics. Don't run it against everything.

The reframe: conversion is a hypothesis, validation is evidence

Once you have the three layers in place, the mental model shifts. Converted SQL isn't a finished artifact — it's a hypothesis that the original query and the new query are equivalent. Validation is the evidence that confirms or rejects the hypothesis.

This changes what "migration complete" means. In a conversion-only world, complete means "the pipeline ran without errors." In a validation-first world, complete means "every converted query has evidence of equivalence on sample data."

The second definition is slower to reach. It's also the only one that actually ships without ticking time bombs.

If you're starting a migration right now

The conversion vendors who win the long game are the ones who invest in validation first. Fast conversion that ships wrong answers is a liability; slower conversion with evidence of correctness is an asset. Pick the asset every time.

FAQ