The challenge
The agency ran fourteen separate reporting pipelines feeding the monthly executive pack — a mix of legacy SQL extracts, Excel models maintained by individuals, and a handful of Power BI workspaces. Numbers reconciled “most months.” Closing the period took nine working days, and every Cabinet-paper request triggered a fresh hand-cut analysis.
What we did
Working with the agency’s platform and analytics teams, we designed a target architecture on Azure Synapse backed by a medallion lakehouse pattern. We migrated the fourteen pipelines in three increments, building data contracts at each interface and instrumenting end-to-end lineage in Microsoft Purview.
Critically, we wrote the runbooks and embedded with the team for the final two months — so the people running the system after we left had built it with us.
Outcome
The monthly close runs in under thirty-six hours. The executive team sees the same numbers as the operations team, on the same cadence. Cabinet-paper requests are answered from the same single source — with full lineage attached, ready for any OIA challenge.