System Design Studio

Practice data architecture like an interview.

Turn system design theory into architecture reps: choose trade-offs, review a design, explain your choices, get specific AI feedback, and improve your reasoning.

Start designing Unlock premium cases

Cases

Free

Completed

Guided practice · 30–40 minutes · Free

Design a reliable daily revenue pipeline

Practise requirements, data grain, safe retries and late corrections. Bring basic SQL and pipeline knowledge. Leave with a design you can explain—not a job-readiness score.

Draft saved with this case on this device. Reviewing a reference or writing a revision does not prove mastery.

Case reference · explicit exercise assumptions

Finance needs yesterday’s certified revenue by 8 AM IST. Design for 100,000 orders/day, two years of history and a two-person data team. An existing warehouse and object storage are available; justify additional services and their operating cost.

Sources expose paginated orders, payments and refunds plus an updated_at filter. Extracts may repeat. Refunds may arrive seven days late. An order can contain multiple items, payments and refunds.

Finance defines revenue here as captured payments minus successful refunds, grouped by original order business date. Corrected days must be restated. Reconcile against provider totals before publishing; alert Finance if reconciliation fails.

You may challenge assumptions. State additional requirements rather than inventing certainty. SQL or Python can be defensible; Spark is not required.

Clarification answers

Source IDs are stable. updated_at changes for corrections. The source retains records for backfill. Finance accepts a delayed, clearly labelled report rather than silently incorrect totals. You must define ownership, alerting and recovery.

First draft not captured · Revision pending · Follow-up pending · Reference not viewed

Understand the business before choosing tools

What must be correct by 8 AM? What is the grain of each source? Which assumptions would you clarify with Finance? Name an explicit freshness/correctness trade-off.

One defensible design · opening records reference use

Land versioned raw extracts with run metadata. Incrementally ingest by updated_at with overlap; deduplicate stable source IDs using the newest version. Keep payments and refunds at their own grain before aggregating to orders and business dates.

Use SQL transformations in the existing warehouse. Merge corrected entities, identify impacted order dates and rebuild their revenue totals. Publish atomically only after uniqueness, row-count and payment/refund reconciliation checks pass. Retain the last certified report if checks fail and alert the named owner.

A small Python extract plus scheduled SQL can satisfy this daily workload. Spark adds operational complexity unless measured volume or processing limits justify it. Full daily rebuilds are simpler but become expensive with two years of history; targeted rebuilds require reliable changed-record tracking.

Replay a batch twice and compare keys and totals. Introduce a late refund and check the original business date changes once. Interrupt publication and verify readers never see a partial report. Five-minute updates require reassessing source freshness, ingestion frequency and cost—not automatically adding Kafka.