Workforce Intelligence

Background

Innovate Solutions, a technology consulting firm operating across London, New York, and Singapore, relied on manual SQL exports and Excel spreadsheets to generate HR workforce reports, resulting in 48-hour turnarounds and data that was often stale by the time it reached decision-makers.

This case study documents the design and build of the Workforce Intelligence Engine on Microsoft Fabric: a Bronze-to-Silver lakehouse pipeline that incrementally ingests employee records from Azure SQL, then applies a PySpark notebook to quarantine invalid data, deduplicate records, enrich the dataset with derived attributes, and join in department context while maintaining full auditability and row-level reconciliation.

Problem

No analytical layer over a transactional system - Employee data lived in a well-maintained Azure SQL database, but with no lakehouse or reporting layer on top, every HR report required a manual export.
48-hour reporting turnaround - HR teams pulled data via ad-hoc SQL exports, cleaned it manually in Excel, and emailed spreadsheets — a process slow enough that data was stale before it reached anyone.
No governance over sensitive data - Employee salary and personal information moved through Excel attachments and Outlook emails, with no access controls or audit trail.
Inconsistent, error-prone data quality - The source data contained 18 invalid salary records (including one at $9,999,999 and one at -$5,000), 10 null hire dates, 8 null salaries, and 5 duplicate employee IDs from re-extraction after updates.
Unextracted information buried in raw fields - Roughly 36 employee records carried a name prefix (Mr., Mrs., Ms., Dr.) mixed directly into the name field, with no clean way to separate or use it.

Solution

Built the Case Study 04 workspace in Microsoft Fabric with two core components: Pipeline_CS_04 to handle the automated data movement from Azure SQL into the lakehouse, and Notebook_CS04 to run the PySpark logic that cleans, checks, and prepares the data for reporting.

Set up a Copy Data activity in the pipeline to pull employee records from the Azure SQL Database (InnovateSolutions.employees table) as the source.
Configured the destination to land this data into the Bronze lakehouse as a table called CS04.Employees, using Overwrite mode to refresh the full dataset on each run.

In Notebook_CS04, loaded the department reference data (departments.csv) into the notebook and previewed it, confirming all 10 departments with their names, locations, and regions came through cleanly and ready to be joined with employee data later.

Loaded the raw employee data from the bronze_employees table into the notebook, so the Silver transformation logic has the untouched source dataset to work from.

Imported PySpark's built-in function library, which powers all the cleaning, quarantining, and enrichment logic used throughout the notebook.

Identified all records with a missing hire date or missing salary, and tagged each one with a quarantine_reason of NULL_FIELDS, so they can be flagged and set aside instead of silently breaking the reporting data.

Caught records with an impossible salary — negative values or the placeholder figure of 9,999,999 — and tagged them with a quarantine_reason of INVALID_SALARIES, so they get filtered out before reaching HR reporting.

Combined the null-field records and the invalid-salary records into a single quarantine_df, bringing all rejected records together in one place with their reasons attached, ready to be written out for audit.

Wrote all the rejected records to the silver_quarantine table, creating a permanent, queryable audit trail so nothing is silently dropped and every rejection can be traced back to its reason.

Built the clean working_df by keeping only the records that passed all checks — valid hire date, valid salary, and a salary within a sensible range — giving the rest of the pipeline a trustworthy dataset to build on.

Removed duplicate employee records by sorting on the most recent updated_at timestamp and keeping only the latest version of each employee, so every employee now appears exactly once.

Enriched the clean dataset in one step: separated out name prefixes (Dr., Mr., Mrs., Ms.) into their own column, extracted a cleaned employee name, calculated hire year and tenure in years, classified each employee into a salary band (Senior, Mid, Junior), joined in full department context, and stamped each record with transformed_at, employee_source, and dept_source for full traceability.

Wrote the fully cleaned, deduplicated, and enriched dataset to the silver_employees table, delivering the trusted, HR-ready employee dataset that downstream reporting can rely on.

Closed the loop with a row reconciliation check, printing the Bronze input count, Silver quarantine count, and Silver output count together, so every record entering the pipeline can be accounted for and nothing is silently lost.

Business Impact

From 48-hour reports to same-day trust. HR no longer waits on manual SQL exports and Excel cleanup — the pipeline runs on a schedule and delivers a ready-to-use dataset automatically.
Every record accounted for. Row reconciliation between Bronze, quarantine, and Silver means no employee record is ever silently lost or dropped without a trace.
A cleaner, more complete employee dataset. Duplicate records are resolved to the latest update, invalid or missing data is separated out rather than corrupting reports, and each employee now appears exactly once.
Richer data for HR decision-making. New fields like tenure, salary band, hire year, and full department context give HR the ability to slice and analyze workforce data in ways the raw source never allowed.
A governed, auditable process replacing an ungoverned one. Sensitive salary data no longer moves through email attachments — every transformation is timestamped, sourced, and traceable, giving compliance and audit teams a defensible process to point to.