Workday · The time model
Effective dating, and why extracts drift
Every change in Workday carries two dates: when it takes effect, and when someone entered it. Extracts that track only one of them drift from the truth.
◆The time model, left to right
01 · CHANGE
A business event
hire · raise · transfer · termination
›
02 · TWO DATES
Effect and entry
effective date · entry date
›
03 · RETRO
The past can change
retro change · correction · rescind
›
04 · EXTRACT
Pull by both dates
delta by entry date · as-of by effective date
›
05 · OWN
History rebuilt
SCD2 · snapshot facts
◆ The two dates— effective date answers when it happened, entry date answers when Workday learned about it.
| Date | What it means | What it answers |
|---|---|---|
| Effective date | When the change takes business effect. A raise effective January 1 changes pay from January 1, whenever it was entered. | What was true on a given date. Headcount as of March 31. Pay as of the review date. |
| Entry date | When the change was keyed into Workday. Often days or weeks after the effective date. | What changed since my last extract. The only reliable delta filter. |
One worked example. A hire takes effect January 1 and is entered on January 15. An extract on January 10 does not contain the worker. An extract on January 20 shows the worker employed since January 1. Ask for January headcount on January 10 and on February 1 and the same system gives two different answers. Neither is wrong; they were asked at different entry times. A warehouse that keeps both dates can reproduce either answer.
◆ Retro changes, corrections, rescinds— three ways the past changes after you extracted it, and the reload pattern that survives all three.
| Kind | What happens | What your extract sees |
|---|---|---|
| Retro change | A new event entered now, effective in the past. A missed raise, a late transfer. | New rows appear inside date ranges you already pulled. An entry-date delta catches them; an effective-date filter alone does not. |
| Correction | An existing event is fixed in place. The amount or date changes; the event does not multiply. | A row you already landed is now different at source. Only a re-pull of that record shows it. |
| Rescind | An event is removed as if it never happened. | A row you landed no longer exists at source. Nothing arrives to tell you; only a reconciling reload reveals it. |
The reload pattern that survives all three: a daily delta filtered by entry date, a weekly sweep re-pulling a trailing window, and a periodic full rebuild that reconciles the warehouse to source. Corrections and rescinds are why the sweep and the rebuild exist; deltas alone cannot see them.
◆ Rebuilding time in your warehouse— three builds: the SCD2 dimension, the snapshot fact, and the as-of join.
| Build | What it is | What it answers |
|---|---|---|
| SCD2 worker dimension | One row per effective-dated change, with valid-from and valid-to dates. The worker's full history as data. | What was this person's job, pay, org on any date. |
| Snapshot fact | The state of every worker captured daily or monthly. Workday does not keep this table; you build it and keep it. | Headcount, FTE, and cost trends, instantly, without recomputing history per query. |
| As-of join | Facts join the dimension row valid on the fact's date, not the current row. | Events reported against the org and job that were true at the time, so reorgs do not rewrite old reports. |
◆ What to watch— future-dated events, payroll retro results, and reorgs that never notify the people they move.
| Watch for | Why it matters |
|---|---|
| Future-dated events | A termination entered today, effective next month, already exists in Workday. Decide whether "current" extracts include it, and apply the choice consistently. |
| Payroll retro results | Retro pay changes generate recalculation results in later pay periods. Payroll facts must carry both the pay period and the period being corrected, or period totals mislead. |
| Reorgs and direct reports | A supervisor transfer is an event on the supervisor, not on the workers underneath. Rebuild org assignment from effective-dated worker data rather than from event feeds. |
| Time zones | Entry timestamps are moments, effective dates are dates. Convert consistently or day-boundary records fall into the wrong delta. |
Want history that stops drifting?
We build the SCD2 dimension, the snapshot fact, and the layered reload, reconciled against Workday, and you own every line.
Terms on this page
- effective date
- The date a change takes business effect.
- entry date
- The date the change was keyed into Workday, sometimes long after the effective date.
- retro change
- A change entered now, effective in the past.
- correction
- Fixing an existing event in place. The row changes; it does not multiply.
- rescind
- Removing an event as if it never happened. Nothing arrives to tell your extract.
- future-dated event
- A change entered now, effective later. It already exists in Workday.
- bitemporal
- Data with two clocks: business time and system time. Workday's model, in one word.
- SCD2
- Slowly changing dimension, type 2: one row per change, with validity dates.
- snapshot fact
- A table capturing the state on each day or month.
- as-of join
- Joining a fact to the dimension row valid on the fact's date.
- delta
- Pulling only what changed since the last extract, filtered by entry date.
- reconciling reload
- A periodic full re-pull that trues the warehouse up to source, catching corrections and rescinds.