Case study 05 · Fabric data engineering
HR Data Platform & 12-Month Workforce Cost Forecasting in Microsoft Fabric
Automating workforce data ingestion and cost forecasting through a Bronze/Silver/Gold Lakehouse.
The challenge
HR and Finance needed a reliable way to forecast employer costs across companies, functions and future time periods. Manual reporting and source-system extracts made it difficult to maintain history, model joiners and leavers consistently, and produce a repeatable 12-month cost view.
Data goal
Automate ingestion of employee and vacancy data from an HR REST API into a structured, reusable Fabric platform.
Planning goal
Create forward-looking salary and employer-cost forecasts while preserving historical salary changes for temporal analysis.
What was delivered
- An end-to-end Microsoft Fabric data platform using a Bronze/Silver/Gold Lakehouse architecture.
- Incremental REST API ingestion with watermarking, retry logic and Azure Key Vault integration.
- Scalable transformation pipelines using Fabric Data Pipelines, PySpark, Spark SQL, T-SQL and Delta Lake.
- SCD Type 2 historisation to preserve salary and employee changes over time.
- Forecasting logic for salaries, bonuses, statutory contributions, vacancies, joiners, leavers and multi-currency payroll.
- Business-ready outputs supporting 12-month rolling workforce cost forecasts.
Business value
- Enabled HR and Finance to forecast employer costs 12 months ahead.
- Removed manual HR cost reporting through automated ingestion and transformation.
- Preserved full salary history, enabling more accurate trend and temporal analysis.
- Created a reusable Fabric foundation for workforce analytics rather than a one-off report.
Technology
- Microsoft Fabric
- Lakehouse
- Delta Lake
- PySpark
- REST API
- Azure Key Vault
Commercial takeaway
A source-to-outcome Fabric story: API ingestion, medallion architecture, historisation and planning logic all connect directly to a measurable business use case.
Related case studies
Planning data you can forecast from
We build the ingestion, historisation and modelling layers that turn operational HR and finance data into forward-looking plans.