A clean compensation dataset should give each worker a stable analytical record, preserve the original source values and document every transformation used to prepare pay, job and working-time data for analysis. Start by identifying authoritative HRIS and payroll sources, remove or resolve duplicates, align effective dates, separate basic from variable pay, standardise currencies and units, verify hours or FTE, and flag missing values instead of silently filling them. Create transformed analytical fields separately from source fields and maintain a data dictionary and transformation log. This does not create a legal safe harbour, but it makes Article 9 reporting and category-level investigation more reproducible and easier to review.
Jurisdiction: European Union
Define the Analytical Unit Before Cleaning
Before changing any values, decide what one row in the analytical dataset represents. For many pay-equity projects, the practical unit is one worker for one measurement period, but organisations with job changes, multiple assignments or complex payroll arrangements may need a more detailed model. The key is consistency. If one worker appears once while another appears three times because of payroll transactions, simple averages can be distorted. Document the row definition, the measurement period and how multiple assignments are handled. This structural decision should be made before duplicate removal because two apparently duplicate records may actually represent legitimate separate jobs or periods.
Preserve Source Values Before Standardising Them
A strong cleaning process does not overwrite the original data. Keep the source field and create a separate cleaned or normalised field. For example, preserve the original job title while creating a standardised job-title field, or retain the payroll currency amount alongside a converted analytical amount. This makes the transformation visible and reversible. It also allows reviewers to determine whether a surprising result came from the source system or the analytical rule. When a dataset will support recurring reporting, preserving source values is especially important because methods may change and the organisation may need to rerun an earlier period using a revised rule.
Resolve Duplicate and Conflicting Worker Records
Duplicates are common when HR and payroll extracts are joined. A stable worker identifier should be used to find repeated records, but duplicates should not simply be deleted. Analysts should determine whether the records reflect multiple assignments, a mid-period job change, a correction, separate legal entities or a true duplication error. Conflicting fields also need resolution rules. If payroll and HRIS show different grades or locations, the project should define which system is authoritative for the relevant date. Each resolution rule should be documented because inconsistent duplicate handling can change group sizes, averages and category-level gaps.
Align Pay, Job and Working-Time Dates
Compensation data often represents a full year while job data is extracted as a current snapshot. That mismatch can assign historical pay to a job the worker did not hold for the whole period. Analysts should therefore identify the effective date for job, grade, location, hours and pay fields. Where workers changed role during the measurement period, the chosen method should be documented and applied consistently. The correct approach can depend on the metric and national methodology. The main control is to avoid silently joining data from different dates as though they described the same employment state.
Standardise Compensation Components Without Losing Detail
Payroll systems can contain dozens or hundreds of earning codes. For pay-equity analysis, those codes may need to be mapped into basic salary, complementary or variable components and other relevant categories. The mapping should be rule-based and reviewed by payroll or compensation specialists. Do not collapse all earnings into one annual total if the analysis needs separate variable-pay measures under Article 9. At the same time, there is rarely value in carrying every technical payroll code into the final analytical table. A controlled mapping table can preserve source-level traceability while producing a manageable set of analytical components.
Treat Missing Values as an Analytical Finding
A blank FTE, missing job level or absent variable-pay field should not be filled automatically with a convenient default. Missing data can indicate a source-system problem, a population that follows different rules or a join failure. Analysts should profile missingness by field and worker group, determine whether the value can be recovered from an authoritative source and document any imputation or exclusion rule. The decision can affect both the calculated gap and who remains in the analysis. A missing-data log makes those decisions visible and helps HR or payroll teams fix recurring source problems before the next reporting cycle.
Validate the Clean Dataset Against Source Totals
Before calculating pay gaps, reconcile the cleaned dataset with known source totals. Useful checks include worker counts, payroll totals, total base salary, total variable pay, headcount by legal entity and the number of workers with missing category or sex data. The purpose is not to force every analytical total to match payroll exactly, because inclusion rules may legitimately differ. It is to explain the difference. Analysts should also run range checks for impossible hourly rates, negative FTE, invalid dates and unexpected currencies. Saving the validation output with the dataset creates evidence that the analytical file was tested before results were produced.
Version the Dataset and Methodology Together
A clean dataset is only reproducible when its methodology is versioned with it. Record the source extraction date, code or spreadsheet version, mapping tables, currency rates, category logic and manual exceptions used to create the file. If a correction is made, create a new version rather than silently replacing the earlier output. This allows the organisation to reproduce a reported figure and understand why a later rerun changed. Version control is particularly valuable where several teams contribute data or where pay transparency reporting, employee information requests and internal pay-equity reviews use the same underlying compensation records.
Frequently Asked Questions
Should I delete duplicate compensation records automatically?
No. First determine whether the records are true duplicates or legitimate multiple assignments, pay periods or job changes. Document the resolution rule before removing records.
Can missing FTE values be assumed to equal 1.0?
That assumption can materially distort results. Missing working-time data should be investigated and recovered from an authoritative source where possible before any documented fallback rule is applied.
Why keep both source and cleaned fields?
Keeping both preserves data lineage. It lets reviewers see exactly how the analytical value was created and makes later corrections or methodology changes easier to reproduce.
Related Guides
Official Sources
Requirements and practices differ by jurisdiction and organisation. Check current local law, official guidance and professional advice for a specific situation.