Establishment Data Platform Rebuild
A department-wide employee and org-structure pipeline fed every downstream business application, and it had been failing quietly for two years — until I noticed the pattern in the support tickets and rebuilt it.
The challenge
Nobody asked me to look at it. I noticed the pipeline generating two to three support tickets a week, around four hours each to resolve, and steadily eroding the business areas' confidence in the platform itself.
It couldn't be optimised out of trouble. The pipeline was distributed across every environment, so a single fault had to be fixed repeatedly. It was throttled by Dataverse API limits, had no batch updates and no retry logic, handled edge cases badly, and could take 36 hours to process a single day of data. Any replacement also had to clear tenant governance in an organisation where Azure-native tooling sits behind a central IT function the department doesn't control.
The approach
I made the case for Microsoft Fabric — partly because it sat on the department's own side of that governance boundary, and partly because it started at a capacity small enough to fail cheaply. Dataflow Gen2 was the feature that led me to Fabric, but I stress-tested it against PySpark notebooks and chose notebooks for their ALM story and their granular control over batching and retries.
Approvals were staged so each was funded by the last: a Director approved the smallest available capacity, those results funded the CIO conversation, then Enterprise Architecture and Change Advisory. Cutover ran by environment tier with number-level reconciliation at each stage.
Architecture
What I built was a medallion architecture. Data lands from on-premises SQL into a Fabric lakehouse, then moves through cleansing and deduplication into a validated silver layer — where the interesting work happens. Referential integrity is enforced explicitly: a record naming a manager's employee number is checked against a real matching record, and managers sitting in adjacent business units are detected before they violate Dataverse's business unit hierarchy rules.
The gold layer carries the analytical joins, and PySpark notebooks push into Dataverse in batches, retrying failed batches record by record rather than failing the run. A variance threshold halts processing and escalates for manual review if the data swings further than expected. Everything sits behind Fabric pipelines across dev, test and production workspaces, with variable libraries for configuration and Activator watching for sustained failures.
Outcome
Three months after go-live there were no implementation errors at all, against two to three tickets a week before — and the pipeline started surfacing genuine upstream data quality problems that the old architecture had been masking. Twelve hours a week of my own time came back.
Capacity has since grown from F2 to F8, mostly from data the department had never previously had anywhere to put, and it was the first Fabric implementation to clear governance in that tenant.