OLTP vs OLAP Design Drills

This drill tests architecture boundaries between transactional and analytical systems.

A business needs sub-second writes for checkout and hourly analytics for executive dashboards. Propose architecture, replication strategy, and reconciliation controls.

A classic OLTP-vs-OLAP divergence is return attribution. The transactional system restates history: a refund is netted back against the original order, so January's net revenue changes retroactively when a January item is returned in February. The serving model is append-only: it recognizes the return in the month it happened.

Both views are "correct" (they answer different questions), but if a dashboard mixes them, monthly numbers will not reconcile. Build both and measure the delta:

• correctness of OLTP/OLAP separation (35%) — strong: assigns each workload to the right system and explains why returns attribution differs between them
• tradeoff clarity (latency, cost, complexity) (30%) — strong: quantifies what each design buys and costs, not just labels them pros/cons
• data quality/reconciliation controls (20%) — strong: proposes a concrete delta check like the drill, with an alert threshold and an owner
• rollback/failure planning (15%), strong: states what happens when replication lags or the serving model is wrong mid-quarter