How would you validate that an optimized SQL query gives exactly the same scientific cohort as the original?

Why

A cohort query defines who is in a study, so a rewrite that loses three patients changes results downstream. Comparing counts alone is not enough, because one patient dropped and another added leave the count unchanged. The comparison must be row by row and must respect duplicates.

Set operations do this directly. The PostgreSQL documentation explains that EXCEPT returns rows in the first result that are absent from the second and removes duplicates unless EXCEPT ALL is used, so EXCEPT ALL is the version that notices a patient appearing twice in one result and once in the other. Equivalence on data is evidence, not proof, so the check is paired with fixtures that exercise the boundaries the rewrite touched.

How to work it through

1. Freeze a snapshot of the source tables so both queries see identical data.
2. Run the original and the optimized query and save both results with the same column order and types.
3. Compute original EXCEPT ALL optimized and optimized EXCEPT ALL original, and require both to be empty, because ALL keeps duplicates that plain EXCEPT would hide.
4. Check row counts and that the cohort key is unique in both outputs.
5. Build a small fixture with edge cases the live data may lack, such as NULL dates, patients on boundary dates, duplicates and missing visits, and compare again.
6. Record the snapshot, both query versions and the comparison output so the equivalence claim can be audited.

Where the answer stops holding

Equal output on one snapshot shows equivalence for that data, not for every possible input. Edge-case fixtures reduce but cannot remove that gap. Floating-point columns may differ in the last digits between plans, so compare them with a tolerance or round explicitly.

Results that depend on the current time or on non-deterministic ordering need those inputs fixed first.