Reference

Modeling and System Design Questions

Interviewers often test architecture reasoning, not only SQL syntax.

Practice explaining:

OLTP vs OLAP boundaries
star schema vs vault tradeoffs
freshness, cost, and governance choices

Example Prompt #

Design an analytics system for an insurance company that needs monthly claims ratio by product, near-daily refresh, and auditable history.

Answer Framework #

Clarify business KPIs and grain.
Propose target architecture and why.
Define key tables/models and refresh strategy.
Explain validation and reconciliation controls.
Discuss migration and failure handling.

Timebox #

5 min: clarify scope and constraints
10 min: propose architecture and modeling choices
5 min: cover risks, fallback plan, and tradeoffs

Runnable Design Drill #

Build one candidate serving output for the insurance prompt: month x product_line claims ratio, defined as incurred claims / earned premium (the same contract the gold capstone grades).

Loading SQL editor...

sql

-- Reconciliation check: policies in premium table with no policy dimension match
SELECT pf.policy_id
FROM ins_premium_facts pf
LEFT JOIN ins_policies p
  ON p.policy_id = pf.policy_id
WHERE p.policy_id IS NULL;

Evaluation Rubric #

architecture fit to business requirements (30%), strong: ties every component choice back to a stated requirement (refresh cadence, auditability, ratio grain)
explicit grain/metric contracts (25%) — strong: writes the metric down (incurred / earned, month x product_line) before proposing tables
validation and operational controls (25%), strong: names concrete reconciliation checks and where they run in the pipeline
tradeoff clarity (cost, latency, complexity, governance) (20%) — strong: presents at least one rejected alternative and why it lost

Tip

Strong candidates show tradeoffs and risks, not just one preferred architecture.