What "clean" means for a workforce data pipeline
The rostering to ESR to payroll pipeline is one of the highest volume integrations in any Trust and one of the least monitored. Hours, absence and allowances flow from rostering into ESR, ESR drives payroll, and the first anyone hears of a problem is often a member of staff querying their payslip.
"Clean" doesn't mean the pipeline never fails. Rejections, late changes and edge cases are normal in workforce data. It means every failure is visible to a named person before the payroll cut-off, with enough information to fix it. The checklist below is built around that definition, and it is the same reasoning behind why leave entitlement in ESR keeps breaking.
The checklist
| Checkpoint | What to check | What breaks if you skip it |
|---|---|---|
| 1. Field ownership | Each field has one agreed system of record | Two systems overwrite each other and both look right |
| 2. Identity | Records match at assignment level, not just person level | Hours land on the wrong post for staff with more than one |
| 3. Effective dates | Changes carry effective dates and respect the leave year | Off-by-one errors that compound through the year |
| 4. Row validation | Every row is validated before load and rejections go to a person | A partial load that looks like a success |
| 5. Lifecycle events | Starters, movers and leavers are handled explicitly | Stale entitlements and overrides on leavers |
| 6. Reconciliation | Rostering, ESR and payroll figures are compared before cut-off | Variance discovered on the payslip |
| 7. Field-level audit | Every change is logged with before and after values | No answer to "why did this change?" |
| 8. Transport | A plan for moving from file drops to APIs where available | Batch timing that hides errors until the next run |
Ownership, identity and dates
Agree field ownership first. ESR typically owns contract, pay and entitlement. Rostering typically owns what was worked and what was booked. Payroll owns what was paid. Write that down per field, because the integration should surface disagreement between systems, not settle it by whichever ran last.
Match at assignment level. A person can hold more than one assignment, and bank staff often do. An integration that matches on the person rather than the assignment will put hours on the wrong post. That error is usually invisible until someone looks at a specific payslip.
Carry effective dates, and respect the leave year. Workforce changes rarely take effect on the day they are entered. The NHS leave year typically runs from 1 April to 31 March, and override elements carry their own effective dates. A pipeline that batches on calendar months without that awareness produces off-by-one errors that compound.
Validation and reconciliation
Validate every row before load. Monitoring that reports a job as successful because it ran is not monitoring. A file of thousands of rows that rejects a handful will look healthy at job level, and those few rows are the ones that become disputes. Validate at row level and send every rejection to a named person with the reason attached.
Reconcile before the payroll cut-off. A scheduled reconciliation compares rostering hours, ESR records and payroll output, and lists every variance ranked by size. It doesn't fix anything automatically. It gives someone time to investigate before the figure is paid rather than after.
Starters, movers and leavers
Lifecycle events are where workforce data drifts most. A leaver whose override elements were never cleared keeps an entitlement nobody intended. A mover whose new assignment starts mid-period has hours split across two posts. A transfer between Trusts arrives with history that lands after the start date.
Handle each event type explicitly in the pipeline, with its own checks, rather than treating them as ordinary updates. Inter-authority transfer in ESR covers the transfer case in detail.
Audit, and the move from files to APIs
Log every change at field level. When a figure changes, the question that always follows is why. If the pipeline only logs that a job ran, nobody can answer it. Field-level logging with before and after values turns that question into a lookup, and makes IG and audit conversations short.
Plan the move from files to APIs. Many workforce integrations still run on scheduled file exchanges. Files are not wrong, but batch timing hides errors until the next run, and a failed file is often noticed late. Where an interface supports API-based exchange, moving to it shortens the time between a change and its validation. The hidden benefits of moving from ESR CSV to NHS APIs covers the case for that move.
Where WeHub Studio fits
WeHub Studio is built for pipelines like this one. Its ESR processors read from ESR, and validation, reconciliation and routing are configured steps in the flow rather than scripts around a file transfer. A sample file runs through every step before anything is live, every run stays on record afterwards, and a rejected row goes to the person who owns it with the reason attached.
If you want a starting point, take checkpoint 6. A reconciliation between rostering, ESR and payroll before each cut-off is the single change that most often stops the payslip disputes, and it can be built without touching the rest of the pipeline.
