Headcount as of any date

What was headcount on the last day of June, two years ago? The system that runs your workforce can answer for today, and for a rolling window of month ends. Beyond the window, the answer is gone.

RuleKeep one dated headcount snapshot per day in a warehouse you own. Any as-of question then becomes one filter on one table.

Neverbuild a board trend on a store that purges its own past. A window that deletes is a cache, not history.

◆ What goes wrongthe plain version, before any technical detail.

Someone asks for headcount as it stood on a past date. If the date is recent, a delivered worker report with an as-of prompt answers it, one date per run. If the date is a month end within roughly the last three years, the trending store has a snapshot for it, at monthly grain only.

If the date is older than the window, or falls mid-month, or the organization has been restructured since, there is no delivered answer. The history was never stored at that grain, or was stored and then purged. Teams discover this the week a board pack asks for a five-year trend.

The delivered path ends in a purge. The owned path ends in a table that keeps every date.

◆ The three delivered paths, and where each stopsas-of prompts, the trending store, the rented insight layer.

Delivered path	What it answers	Where it stops
As-of prompt	A worker report re-run with an effective date. One date per run.	Manual, one date at a time, and only as good as the security and population choices of that report.
Trending store	Month-end workforce snapshots for period-over-period reporting. Covered in detail on the trending store page.	Monthly grain only, a window of about 36 periods, and older snapshots purged. Both figures practitioner-observed.
Insight layer	Delivered workforce dashboards inside the vendor's walls.	Rented, not owned. No mid-month dates, no joins to payroll or finance, and the same retention window underneath.

None of the three is wrong. Each is a view of the present, or a short cache of the past, doing its job. The mistake is asking any of them to be an archive. History is a storage decision, and the delivered paths decided against storing yours at full grain.

◆ The owned answera daily snapshot fact, and the trend it makes routine.

The build is one table: one row per worker per day, written by the pipeline that already extracts workers for the Core HR star. Storage is cheap; a decade of daily rows for ten thousand workers is small by warehouse standards. Once the table exists, month ends, quarter ends, and any Tuesday in between are the same one-line filter.

Month, 2026	Hires	Exits	Ending headcount
February, ending			344
March	6	3	347
April	5	2	350
May	5	4	351

Each ending headcount is the prior month plus hires minus exits, and the arithmetic is checked by the same battery that ties this catalog's money pages. May's 351 splits into the departments the labor cost report prices: Sales at 143, Technology Delivery at 112, Support at 96. People and cost, one population. Sample values are illustrative, never client data.

Department, May 2026	Headcount
Sales	143
Technology Delivery	112
Support	96
Total, ties to the cost pages	351

-- headcount as of any date: one filter on the daily snapshot
SELECT d.department_name,
       COUNT(*) AS headcount
FROM fct_worker_day f
JOIN dim_department d ON f.department_key = d.department_key
WHERE f.snapshot_date = DATE '2024-06-30'
  AND f.is_active
GROUP BY 1
ORDER BY 2 DESC

Swap the date and the same query answers any other day, from go-live forward. For dates before go-live, history has to be rebuilt from events rather than read from snapshots, which is its own problem with its own page in the spine plan. The worker dimension carries the effective-dated attributes; the snapshot carries the dates.

Use case

Problem

A five-year headcount trend is requested and the delivered stores hold three years of month ends at best, so analysts rebuild history by hand from old exports, and no two rebuilds agree.

What we build

A daily worker snapshot in your warehouse, written by the same pipeline that feeds the Core HR star, retained for as long as you choose because you own the storage.

What you get

Headcount as of any date, at any grain, joinable to payroll cost and finance, answered by one filter instead of an archaeology project.

Want headcount history that outlives the window?

We ship the snapshot table and the reports on it, in your cloud, in 10 days.

Talk to us

Terms on this page

HR: Human resources. Core HR is the module of record for people data.
as-of date: The past date a question is asked about, as the data stood then.
snapshot: A dated copy of a table's state, kept so the past stays queryable.
grain: What one row means. Here, one worker on one date.
trending store: Workday's delivered store of month-end workforce snapshots, the Trended Worker data source.
purge: Deletion of data that has aged past a retention window.
fact: A table of measured rows. Here, worker days.
practitioner-observed: A limit seen consistently in the field but not published by the vendor. Labeled until tenant-verified.