Renewal reporting, re-architected
A slow seasonal reporting pipeline rebuilt around a historical snapshot and a materialized current-state layer, without changing the numbers people already trusted.
Context
The report was doing too much at query time.
The dashboard depended on a large live SQL transformation. Even a simple query against the reporting view could trigger the full chain again, and the BI workbook added more calculations on top of it.
A source-system transition gave me a chance to redesign the pipeline instead of carrying the same bottleneck forward.
Before
Live recomputation
Legacy source
Large live transformation
BI workbook
After
Separate history from current state
Frozen historical snapshot
Materialized current logic
Stable BI interface
Decision
History and current reporting had different needs.
Historical seasons did not need to be rebuilt every time someone opened a dashboard. I froze the trusted historical output and rebuilt only the current-state logic as a materialized Snowflake layer.
The two were then combined behind a stable reporting interface, so the dashboard did not need to know which part of the result came from history and which part was current.
Validation
Faster only mattered if it still reconciled.
I checked the historical and current boundaries, overlap between the two populations, source counts, category distributions and record-level mappings before treating the new pipeline as complete.
I also kept exception handling deterministic and designed temporary exceptions so they could stop applying once the source system became authoritative.
Result
Minutes became milliseconds.
The reporting query moved from roughly three minutes to about 110 milliseconds, a performance improvement of more than 1,000×, while preserving the historical behavior required by the report.