Employee master data system: you are not building a second HRIS

You already have an HRIS. It stores every employee, it cost twice what anyone expected, and it works. So when somebody says you also need an employee master data system, the fair question is what the second system is actually for.

The answer is not storage. Your HRIS stores people well, and a second table of the same people is not an improvement on one. It does not answer who needed to know about a change, by when, and whether they were told. That is the system you are being asked to build, and it has a different shape from the one most teams start with.

The build that adds a fifth copy

The default first attempt is a nightly job. It reads an HRIS extract, upserts into dbo.Employee, and downstream systems are invited to query that table instead of the HRIS. It goes live, it is tidier than what came before, and within a year nobody trusts it, because nobody can say how stale it is on any given morning.

The design has a specific flaw rather than a general one. The table holds current values, so it records that something differs from yesterday but not when the difference took effect. Three questions come up in the first audit and it cannot answer any of them:

• This person transferred on 1 September. Which systems are still allocating them to the old cost center?
• This person left on Friday and it is now Wednesday. Is their approval authority in the ERP gone?
• This contractor has a record. Where did it come from and who approved it?

It is a propagation problem

Reframe the scope and the architecture falls out of it. An employee master data system converts an HR event into a governed, dated change and gets that change to the systems that depend on it. Storing the person is incidental. The HRIS already did that.

The practical consequence is that you stop measuring the project by how complete the employee table is, and start measuring it by lag. Count the days between the effective date of a change and the downstream system reflecting it. Most companies have never put a number on that, which is the most useful thing the first workshop produces.

Four events, and the fourth changes everything

The last row is the one that decides the design. If a contractor never enters the HRIS, then the employee master cannot be a downstream copy of an HRIS feed. Records have to be able to originate in it, which means it needs an authoring screen, a validation rule set and an approval path of its own. A sync job has none of those, and no amount of scheduling turns one into the other.

This is also where the two-system argument gets won internally. Nobody signs off a project to duplicate the HRIS. Plenty of people will sign off a project that finally puts the three hundred contractors somewhere countable.

The mover, and why current values lose

A transfer is agreed in August, effective 1 September. HR enters it on the 12th. The nightly job picks it up on the 13th, and finance has already booked twelve days of this person against a cost center they no longer belong to.

The twelve days are annoying. The real damage is that the September allocation, rerun in October, now returns a different answer, because the table only ever holds today. Nothing in it is wrong, exactly, and that is what makes the disagreement so expensive to investigate.

Holding assignment as dated rows fixes it. The allocation query becomes date-scoped, and it gives the same answer in October that it gave in September:

-- Who was in this cost center at period end, as at period end
SELECT  w.WorkerCode, w.DisplayName, a.CostCenterCode
FROM    dbo.Worker w
JOIN    dbo.WorkerAssignment a ON a.WorkerId = w.WorkerId
WHERE   @PeriodEnd >= a.ValidFrom
  AND  (@PeriodEnd  < a.ValidTo OR a.ValidTo IS NULL);

Backdating is the part that stays awkward. HR will occasionally date a transfer into a period finance has closed, and no schema prevents that. What a governed layer can do is make it visible, so a closed period reopening is a notification rather than something discovered during the next audit. Slowly changing dimensions in master data covers the shapes and what each one costs to query.

The leaver gap, honestly

Leavers are how these projects get funded, and the pitch is usually oversold. This is the version that survives contact with a security architect.

If your requirement is disabling accounts quickly, that is an identity problem, not a master data one. Entra ID should be reading the termination event more or less directly, and putting a nightly master data hop in front of it makes revocation slower rather than faster. Say so early, because proposing otherwise is the fastest way to lose the room.

Where the master data layer earns its place is everything the identity platform does not carry. Approval authority in the ERP, cost center, org unit, manager, delegation rules. Those are the attributes that quietly stay wrong for weeks, they are the ones an auditor asks about, and no identity platform is going to fix them for you.

Who owns what

Keeping the distributed slice thin is also the easier position to defend to a privacy officer. A governed set of six attributes beats uncontrolled copies in nine systems, which is what most companies have today. Erasure requests are less painful when you know where the copies are.

The tables

Two of them, and the split is the single most useful structural decision here. The person is one row. The org assignment is a dated row that points at them.

CREATE TABLE dbo.Worker (
    WorkerId      INT IDENTITY(1,1) NOT NULL,
    WorkerCode    NVARCHAR(20)  NOT NULL,  -- opaque, issued once, never reissued
    WorkerType    VARCHAR(12)   NOT NULL,  -- employee | contractor | temp
    SourceSystem  VARCHAR(20)   NOT NULL,  -- HRIS name, or MDM if originated here
    SourceKey     NVARCHAR(50)  NULL,      -- their key; null for externals
    DisplayName   NVARCHAR(150) NOT NULL,
    WorkEmail     NVARCHAR(150) NULL,      -- an attribute, never the key
    FirstDay      DATE          NOT NULL,
    LastDay       DATE          NULL,      -- null while active
    CONSTRAINT PK_Worker PRIMARY KEY CLUSTERED (WorkerId),
    CONSTRAINT UQ_Worker_Code UNIQUE (WorkerCode)
);

CREATE TABLE dbo.WorkerAssignment (
    AssignmentId    INT IDENTITY(1,1) NOT NULL,
    WorkerId        INT          NOT NULL,
    OrgUnitId       INT          NOT NULL,
    CostCenterCode  VARCHAR(20)  NOT NULL,
    ManagerWorkerId INT          NULL,     -- a Worker, so externals can manage
    JobRoleCode     VARCHAR(20)  NULL,
    ValidFrom       DATE         NOT NULL, -- the effective date, not the entry date
    ValidTo         DATE         NULL,
    CONSTRAINT PK_WorkerAssignment PRIMARY KEY CLUSTERED (AssignmentId),
    CONSTRAINT FK_WorkerAssignment_Worker FOREIGN KEY (WorkerId)
        REFERENCES dbo.Worker (WorkerId),
    CONSTRAINT CK_WorkerAssignment_Validity
        CHECK (ValidTo IS NULL OR ValidTo > ValidFrom)
);

-- One open assignment per worker. Drop this if you allow split allocation,
-- and carry an AllocationPct column instead.
CREATE UNIQUE INDEX UX_WorkerAssignment_Open
    ON dbo.WorkerAssignment (WorkerId)
    WHERE ValidTo IS NULL;

ManagerWorkerId points at Worker rather than at an employee-only table, which sounds like a detail until a contract project lead has six people reporting to them and the approval routing has nowhere to send anything. SourceSystem is what lets you tell an HRIS-fed record from one a steward created, and you will want that column the first time somebody asks why a person exists.

Rehires break email as an identifier

Email is the identifier people reach for, because it is unique, human-readable and already there. It survives until the first rehire.

Someone leaves in 2024 and comes back in 2026. The mail platform will not give them their old address, because it is archived or held for legal reasons, so they arrive as j.meyer2@. To every system keyed on email they are a new human being with no history, no prior service and no trace of the access they used to hold. Marriage produces the same break, more often and with less warning.

Keep WorkerCode opaque, issue it once, and never reissue it, including to the same person on a second contract. They get their old code and a new assignment row. Choosing a master data identifier goes through the trade-offs, and the short version is that any identifier carrying meaning will eventually have to change.

Getting it out again

Distribution is where these projects stall, because the governed record is finished and nothing consumes it. Two patterns cover almost everything.

A dated view per consumer works for the ERP and for reporting, which want a set and can pull it on their own schedule. Events work for the identity platform and for anything with a clock on it, because a leaver at 16:40 on a Friday should not wait for a batch window. Most implementations need both, and the integration patterns and distributing master data downstream go through the choice properly.

Measure the lag or the project has no scoreboard

One number, per consuming system: days between the effective date of a change and the system reflecting it. It will be unflattering the first time you run it. That is also the only evidence you will have at the end that any of this worked.

Building it without the DDL

The SQL above is the right shape if you are extending a database you own. In a master data platform you express the same model as two entities: a Worker entity, and a WorkerAssignment entity with a domain attribute pointing back at it, plus DateTime attributes for the validity window. Derived columns pull the worker name through the domain hop, so a steward reads a grid that names the person without that name living in two places.

Two limitations are worth knowing before you start. Primentra enforces uniqueness on the entity Code rather than across a combination of attributes, so an assignment Code has to encode the pairing itself, along the lines of W-00412-20260901. And the validity window is ordinary date attributes rather than a temporal table, so the date predicate is on you and on every query that reads it. Neither is a blocker. Both are easier to plan for than to discover.

What comes with it is the part that is tedious to hand-build: approval on the fields that need it, and a field-level audit trail on all of them. The audit trail matters more on worker data than on most domains, because the question asked afterwards is rarely what the cost center is. It is who changed it, when, and on whose say-so.

If you are still at the stage of arguing that employee data is a domain at all, the case for treating it as one is the post to start with. This one assumes that argument is already won and you have been asked what to build.

Common questions

If you are scoping one of these and want a second opinion on where the HRIS boundary should sit, do get in touch. Happy to share what we have seen, including the contractor question that tends to resize the project.