The brief
Keep a trustworthy history of how customer attributes change over time. Your design should meet these requirements:
- Ingest customer records from multiple operational systems.
- Analysts can query both current values and historical values as of a date.
- Late, duplicate, or corrected records must not silently corrupt history.
Scale to design for
Customer records from several operational systems (CRM, billing, support) land daily or near-real-time. Millions of customers; analysts need current and as-of-date history. Sources disagree, arrive late, and send duplicates or corrections.
State these assumptions out loud in the interview, then let them drive your choices: they decide what needs caching, sharding or asynchronous processing.
Before you start
You'll get more out of this round if you're comfortable with:
- Dimensional modeling (facts and dimensions)
- Slowly changing dimensions
- Business vs surrogate keys
- Idempotent batch loads
Questions an interviewer may ask
Expect follow-ups like these once your high-level design is on the board. Have an answer, and a reason, for each:
- A correction for last month arrives today: how does history change?
- How do you guarantee no overlapping intervals per key?
- How do you rebuild the dimension from raw if logic changes?
- How do you query "customer address as of March 1"?
Common mistakes to avoid
- Using load timestamp as effective date
- Creating a new version on every load even without changes
- No handling of late or out-of-order records
- Overlapping or gapped validity intervals
- Non-idempotent loads
Concepts this round covers
- Business keys
- Effective dates
- Late-arriving changes
- Warehousing
What a complete answer covers
A dimensional model with SCD Type 2 validity columns, a repeatable change-detection flow, and explicit late-data handling.
There is no single correct design. A strong answer meets the requirements at the stated scale and explains the trade-offs behind each choice. New to the format? Read how to prepare for a system design interview.