System designMediumsd-g428

Subject Data warehouseLevel Mid–Senior~35 minCommon in Distributed systems interviewsIndustries Technology, Software development

Question

Design the dimensional model for an HR/payroll analytics warehouse where employee attributes change over time — salary, department, manager, level, and location all change, and analysts must answer historical questions like 'what was total comp by department as of last fiscal year-end' and 'how many people did manager X have on the day of the reorg.' Fact tables (timesheets, payroll runs) must join to the dimension state that was true at the event's date, not the current state. Design the dimension and fact schema and the join semantics.

What a strong answer looks like

Clarify scale and constraints first. Propose a clean component breakdown, then go deep on the hard parts — data model, bottlenecks, consistency, failure modes — and name the trade-offs you are making.

Learn the concepts

Narrate your design

Loading whiteboard…

Run or narrate your approach, then ask the coach.