Test that a migrated report or pipeline matches the old one
A parity-testing plan for migrations that compares old and new outputs at the right level, with tolerances and sign-off criteria.
The prompt
Help me plan parity testing to prove a migrated report, model, or pipeline produces the same results as the legacy version.
What is being migrated: [e.g. "SSRS reports to Power BI", "on-prem SQL ETL to dbt on Snowflake", "SAS model to Python"]
Key outputs: [tables, metrics, reports, model scores]
Known intentional changes: [logic fixes, new definitions, timezone or rounding changes]
Data volume and history: [rows, years of history]
Go-live date and who signs off: [DETAILS]
Steps:
1. Define comparison levels, from coarse to fine: row counts, control totals per period, totals by key dimensions, and row-level key matching with column-by-column comparison.
2. Set tolerances per metric (exact match vs. small rounding differences) and state them before testing begins.
3. Choose the test periods: recent data, a month-end or year-end, edge cases (leap days, restatements, late-arriving data, deletions).
4. Describe how to compare: the query pattern for full outer join on keys, classifying rows as matched, missing in new, missing in old, or values differ.
5. Plan how to triage differences: expected (from intentional changes, documented), bug in new, or bug in old.
6. Define sign-off criteria and a parallel-run period before switching off the old system.
Return:
- A test plan table: Output | Comparison level | Method | Tolerance | Period | Owner.
- Template SQL for the comparison queries in [DIALECT].
- A difference log template: ID | Output | Description | Root cause | Category | Resolution.
- A go-live checklist.
Highlighted [brackets] are blanks to replace with your own details.
Fill in the blanks and use this promptMore Data & Analysis prompts
What to fill in
- tables, metrics, reports, model scores
- logic fixes, new definitions, timezone or rounding changes
- rows, years of history
- DETAILS
- DIALECT
How this prompt is built
This prompt follows the RTF structure (Role, Task, Format). RTF tells the model who to be, what to do and what shape the answer should take. It is the quickest structure that still gives noticeably better results than a bare request.
Related prompts
- Plan a category assortment with a clear good-better-best rangeKeep, add, drop, or online-only decisions for every SKU, a price ladder, and store clustering, based on sales, margin, and space.
- Clean a messy datasetA step-by-step cleaning plan for duplicates, missing values, formats, and outliers.
- Explain, debug, or speed up a SQL, Python, or DAX snippetExplains what an existing query or script does, fixes errors, and optimizes it.
- Explain my Excel model to a non-technical executiveTurns a complex spreadsheet or model into a plain-English walkthrough an executive can follow, trust, and question.