Data & Pipelines for HR

HR data sources — the landscape

Common pattern: extract from each source, land in a warehouse (BigQuery / Snowflake / Redshift / Workday Prism), build a curated People model with dbt or equivalent, expose for analytics and as agent tool inputs.

Batch ETL vs streaming — when each fits

HR is mostly batch. Events are not high-throughput; most workflows tolerate hours-of-latency. Default to batch.

When "streaming" comes up in interview, push back gently: "For HR, I default to batch with event-driven kickoff. Continuous streaming is rarely the right answer here — the data isn't high-throughput, and the cost of latency in HR is rarely worth the complexity premium."

Snapshots vs change log — the decision

You can model worker history two ways. Both have a place.

Daily snapshots

Every day, take a full extract of "as of today" worker state. Append to a snapshot table partitioned by date.

• Pros: dead simple, dead reliable, point-in-time queries trivial (filter by snapshot_date)
• Cons: doesn't capture intra-day changes; storage scales with population × time

Change log (event sourcing)

Every change is an event row: {worker_id, field, old_value, new_value, effective_date, change_date, change_kind}. Reconstruct state at any moment by replaying events.

• Pros: full fidelity, including effective-date vs change-date distinction, intra-day capture
• Cons: more complex queries; reconstructing "current state" is a query, not a scan

The pragmatic answer

Effective date vs change date — why both matter

Workday is effective-dated; a comp change recorded on 2026-05-01 may be effective 2026-06-01 or 2026-04-15 (retro). A snapshot taken on 2026-05-15 reflects the change-date view, not the effective-date view. For retro changes, the change log is the only honest source.

People analytics warehouse design

A reference layering (dbt-style):

• raw — extracts as-is from each source, untransformed (workday_raw, ashby_raw, ...)
• staging — type-cleaned, renamed, light deduplication; one staging model per source table
• core — conformed business entities: dim_worker, fact_worker_event, fact_payroll_run, dim_position, dim_supervisory_org
• marts — consumer-facing models: headcount-by-org-by-month, attrition cohort views, hiring funnel

For agents, expose marts via RaaS or warehouse views — never raw. The agent reads the conformed model; updates to source-system shape only require changes in staging.

Workday Prism — when it fits

Prism is Workday's data-lake extension: ingest external data, join with Workday, expose through Workday reports. The pitch: keep analytics inside the Workday security model.

• Pros: consumers stay in Workday; Workday security inherits; calculated fields work on combined data
• Cons: less developer ergonomic than a real warehouse; tooling smaller

Listed as a nice-to-have. If you haven't used it: "I haven't built in Prism specifically. I've done the equivalent work in BigQuery + dbt. The model is the same — ingest, conform, expose. The trade-off is whether you want analytics to live inside Workday's security and report ecosystem or in a general warehouse with dbt-style modeling."

Change data capture from Workday

Workday doesn't expose a generic CDC stream. Common patterns:

• Scheduled outbound EIB with a "changed since last run" filter (via calc field on "last modified")
• Core Connector integration types that support incremental delivery
• Workday Studio with a watermark
• RaaS reports with a parameter for changes since timestamp
• Outbound webhooks via Workday's external integration framework

The interview-ready framing: "There's no pure CDC from Workday. Pragmatically I'd use a scheduled RaaS or EIB with a 'modified since' filter, watermark-tracked in the warehouse. For low-latency triggers (a hire, a termination), webhooks from the BP completion fire the event-driven workflows."

Slowly changing dimensions — the worker model

The classic data warehousing problem applies hard in HR. SCD types you should know:

• SCD Type 1 — overwrite. Loses history. Wrong for HR.
• SCD Type 2 — new row per change, with valid_from / valid_to. The standard for worker / position / sup-org.
• SCD Type 4 / 6 — separate current vs history tables, or combined hybrid. Sometimes used for ergonomics.

For point-in-time queries (headcount on 2025-12-31), SCD2 with effective-dating is the answer. Joining a payroll event to the worker dimension uses WHERE worker.valid_from <= event.effective_date AND (worker.valid_to IS NULL OR worker.valid_to > event.effective_date).

Privacy in pipelines

• Restricted columns separated — salary, equity, performance ratings live in tables with stricter access than the directory.
• Tokenization at ingest — government IDs, SSNs are tokenized at extract; the lookup table is in a separately-controlled service.
• Region-aware materialization — EU worker data materialized in EU; cross-region views require explicit DPA reference.
• Audit on read — queries against Restricted tables are logged with the calling user and purpose.
• DSAR pipelines — a request triggers a job that aggregates all data for a subject across the warehouse; the artifact is reviewable and exportable.

Data quality & tests

The minimum dbt-test (or equivalent) suite for People models:

• worker_id is unique in dim_worker (current view)
• hire_date is not null and not future-dated beyond 90 days
• termination_date >= hire_date when present
• country in approved list
• fte in [0, 1.0] (or 1.5 for double-position-allowed tenants)
• position assignments don't overlap for the same worker
• compensation_amount in plausible band for role+country
• referential integrity for supervisor_id, position_id, sup_org_id
• row counts within ± X% of yesterday — anomaly detection on extract volume

When a test fails: halt the downstream pipeline. A bad worker table breaks every agent that depends on it; better to lag a day than serve agents corrupt data.

Interview framings worth memorizing

• "HR defaults to batch. Streaming is rarely warranted; the data isn't high-throughput and the latency requirements rarely justify the complexity."
• "I run daily snapshots for analytics and a change log for compliance / replay. Different consumers, different fidelity."
• "SCD Type 2 on worker / position / sup-org; without effective-dating, retro changes silently corrupt history."
• "Restricted columns live in restricted tables. Agents read marts that are conformed and class-aware, never raw."
• "Workday CDC is pragmatic — RaaS or EIB with a modified-since watermark for analytics; webhooks from BP completion for event-driven agent kickoffs."
• "A failed dbt test on a worker table halts the downstream pipeline. Better to lag a day than serve agents corrupt data."

✓ Check yourself

Answer from memory before opening each one.

1. A product manager asks for streaming ingestion of Workday changes "so the dashboard is real time." What do you ask, and what is your default?

   Show answer

   Ask who reads the dashboard and what decision needs data fresher than yesterday. Most People analytics reads are "as of yesterday" and volumes are small, so batch with event-driven kickoff for workflows like a hire is the default. Streaming adds complexity that HR rarely needs. See Batch ETL vs streaming.

2. A comp change is recorded on 1 May but effective 15 April. Which model shows that a payroll adjustment is owed, daily snapshots or a change log, and why?

   Show answer

   The change log. A snapshot taken before 1 May shows the old value, and a later one shows the new value, but neither records the effective date was in the past when the change arrived. A log with effective_date and change_date makes the retro change visible. Keep snapshots for simple analytics. See Snapshots vs change log.

3. You join payroll events to dim_worker for a headcount report. Write in words the join condition that places each event against the correct worker version, and name what breaks without it.

   Show answer

   Join where the worker's valid_from is on or before the event's effective date and valid_to is null or after it. Without that range join, an event matches the current version of the worker, so retro and past events pick up today's job, org and location. See Slowly changing dimensions.

4. A backfill rewrites last month's mart tables overnight, and an agent's replay of last week's run no longer matches. What design rule did the pipeline break?

   Show answer

   Models feeding agents should be append-mostly, with effective_date for business time and recorded_at for system time, so you can reconstruct what the agent saw. Mutating past outputs breaks replay and audit. Late-arriving data is fine because it appends. See People analytics warehouse design.

5. Workday offers no generic change stream. How do you keep the warehouse current and also trigger an onboarding agent within minutes of a hire completing?

   Show answer

   Use a scheduled RaaS report or EIB with a modified-since filter and a watermark stored in the warehouse for analytics, and a webhook from business process completion for low-latency triggers. They serve different consumers, so both exist. See Change data capture from Workday.

6. A nightly test shows today's worker extract has 30% fewer rows than yesterday. What does the pipeline do, and why is that preferable to loading it?

   Show answer

   The row-count anomaly test fails and the pipeline halts downstream models. A bad worker table would feed every agent that depends on it, so lagging a day is cheaper than acting on corrupt data. The same suite checks uniqueness, date order and referential integrity. See Data quality & tests.

Takeaway

• HR data is low volume and tolerant of delay, so default to batch with event-driven kickoff and push back on streaming without a stated need.
• Use daily snapshots for simple analytics and a change log with effective and change dates for compliance, replay and retro changes.
• Worker, position and sup-org history belong in SCD Type 2 tables, and point-in-time joins use valid ranges against the event's effective date.
• Layer raw, staging, core and marts, and expose conformed marts to agents, never raw tables.
• Workday has no pure CDC, so use watermarked RaaS or EIB for analytics and business-process webhooks for triggers.
• Tokenise sensitive IDs at ingest, separate restricted columns, keep region-aware storage, and halt downstream pipelines when data-quality tests fail.

With data in order, the last build question is how agents ship and run safely. → Deployment & Ops

Further reading

• How we structure our dbt projects — dbt Labs docs. Staging, intermediate and marts layering that matches the warehouse design here.
• Snapshots — dbt Labs docs. Type 2 history capture from mutable source tables.
• Data tests — dbt Labs docs. Uniqueness, not-null, accepted-values and relationship tests.
• Type 2: Add New Row — Kimball Group. The canonical description of the technique used for worker history.
• Understanding SCIM — Okta Developer. The provisioning standard behind identity data feeds.
• Webhooks — Ashby developer docs. Event delivery for the event-driven kickoffs.