Sage People Data Validation — Counts, Sums, Hashes, Periods

Sage People data validation is the post-load reconciliation discipline that proves every Sage People record successfully landed in Oracle Fusion HCM — to the row, to the cent, to the minute. It runs at three levels: row counts (Sage People Worker__c count = Fusion Worker count, Sage People Salary__c count = Fusion Salary count), sum totals (Sage People total annualised salary = Fusion total annualised salary, Sage People total leave balance hours = Fusion total leave balance hours), and content hashes per record (stable hash of business-relevant fields excluding system audit attributes). Sage People data validation is what converts 'we migrated the data' from a claim into signed evidence — the audit pack that survives HMRC inspection, ICO inquiry, internal SOX testing, and external auditor probing for years after go-live.

Because each layer catches different classes of error. Row counts catch outright load failures — if you extracted 5,247 Worker__c records and loaded 5,235 Workers, twelve records are missing and the count tells you immediately. But counts don't catch silent data corruption: a Salary__c record could load with the salary amount in the wrong currency, and counts would still tie. Sum totals catch that — if total annualised salary in Sage People is £487M and in Fusion is £412M, you have a £75M problem even though the count tied. But sums don't catch swaps: if two workers' salaries were swapped during load, counts and sums both tie but two specific records are wrong. Content hashes catch swaps — by hashing each record's business-relevant fields, any per-record corruption surfaces immediately. The three layers together provide defence-in-depth that no single layer can match.

UK payroll has strict per-period reconciliation requirements driven by HMRC RTI submission rules. During a Sage People to Fusion migration that includes payroll, every pay period's totals have to reconcile: gross-to-net pay totals per pay period, PAYE deducted per period, NI employer + employee per period, pension contributions per period (auto-enrolment + AVCs), salary sacrifice deductions per period, year-to-date totals per worker per tax year. Syntra ETL's period reconciliation engine pulls the Sage People payroll calculation records (or the Sage 50 / third-party RTI provider's output), pulls the Fusion Payroll Run results for the parallel-run period, and reconciles pence-by-pence per worker per period. Any variance over £0.01 is flagged for investigation before sign-off. This is the evidence customers show HMRC and external auditors.

A structured, signed deliverable issued at end of validation. Section 1: Executive summary — total records migrated by object, reconciliation status (counts/sums/hashes), exceptions and resolutions. Section 2: Per-object reconciliation reports — row counts Sage People vs Fusion, sum totals (salary, hours, balances), variance analysis. Section 3: Per-worker reconciliation — every worker with active employment reconciled across Worker, Assignment, Salary, Absence with sign-off per worker. Section 4: Period reconciliation (payroll) — per-period gross-to-net, PAYE, NI, pension, salary sacrifice for the parallel-run period. Section 5: Exception register — any record that failed validation with root cause and resolution. Section 6: Sensitive-field unmasking log — every audit-time field unmasking with justification. Section 7: Sign-off signatures from HR, payroll, IT, audit, DPO. Section 8: Evidence artifacts — extraction timestamps, load timestamps, hash files, error logs.

By validating not just the current state but every effective-dated version. Sage People's Employment_Record__c history (every promotion, transfer, FTE change, manager change going back 7-10 years) has to be present in Fusion HCM as a chain of HDL Assignment events. The validation engine walks every worker's Sage People history, walks the equivalent Fusion Assignment effective-dated chain, and confirms each historical version reconciles. Any 'as of' date can be reconstructed in either system and the active record set has to match. Same approach for Salary__c history → HDL Salary chain. Customers preserving 7-10 years of historical detail use this validation to prove every historical record landed correctly, not just the current snapshot. The 'as-of' reconciliation report is a key part of the audit pack.

Three-tier exception handling. Tier 1 (auto-resolvable): rounding differences below tolerance threshold (typical £0.01 on currency, 0.01 hours on time), known transformation effects (currency conversion, picklist normalisation), source data quality issues already flagged in pre-extract validation. These are auto-resolved with logged justification. Tier 2 (business-owner review): variances within material thresholds that need HR or payroll judgement — typically <£100 per worker variance, or single-record variances not exceeding 0.5% of total. Routed to the appropriate business owner with full context (source vs target values, suggested resolution). Tier 3 (escalation): material variances over threshold that demand investigation before sign-off. Held in the exception register with escalation to project sponsor and audit committee. No sign-off issued until Tier 3 exceptions are zero.

1-3 days of compute time for the validation runs themselves, but 1-2 weeks of total elapsed time including human review and sign-off. The validation engine processes 100,000+ records per minute for row count and sum reconciliation, ~10,000 records per minute for hash comparison, and ~1,000 records per minute for full effective-dated history reconciliation. For a 5,000-worker Sage People migration with 7 years of history, that's ~8 hours of total compute. The remaining elapsed time goes to business owner review of any flagged exceptions, period reconciliation review (payroll team typically takes 3-5 days for a full parallel-run pay period validation), and stakeholder sign-off cycles. Customers running tight cutover timelines pre-schedule reviewer availability for 5 consecutive days post-load to compress this window.

Yes, the audit pack is structured to satisfy GDPR Article 5 (accuracy, integrity), Article 25 (data protection by design), Article 30 (records of processing), and Article 32 (security of processing) evidence requirements. Article 5 accuracy: row-level reconciliation proves no data was corrupted in transit. Article 25 design: the validation framework runs as a built-in part of every migration, not as an afterthought. Article 30 records: every extract, transform, and load step is logged with timestamp, operator, record count, and outcome — providing the processing activity record. Article 32 security: hash-based integrity verification, sensitive-field masking during validation, role-based access to the audit pack, and full audit logging of who accessed which validation artefact when. The signed audit pack becomes part of the GDPR/DPA 2018 evidence file for the ICO.