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."