Data system design

1.You are designing the sales pipeline for a grocery chain. The product manager asks for real-time store sales. What do you ask first?

2.Grocery store managers check a restock dashboard about once an hour. Point-of-sale files arrive every 5 minutes. A teammate proposes a streaming job with per-second updates. What do you recommend?

3.A card payments team wants to block fraudulent charges at authorization, within 200 milliseconds. They also want monthly fraud loss reports. How do you split the work?

4.Your team runs a streaming job for live counts and a separate batch job that recomputes the same counts nightly in different code. The two disagree by 2 percent and nobody trusts either. What is the better fix?

5.Engineers want an alert when wind turbine vibration stays above a limit for 30 seconds. They also want 5 years of readings for failure analysis. Which design fits?

6.Finance at a subscription box company needs revenue that matches invoices to the cent, signed off monthly. Product wants a live signups counter. What should the design reflect?

7.You are asked to design analytics for a new podcast app. Before drawing any boxes, which set of questions gives you the most design leverage?

8.A team wants a streaming pipeline for a weekly marketing email list. The list is built once a week from 3 years of orders. What is the strongest argument against streaming here?

9.A food delivery app shows restaurants an orders-today number. It must be live during the day and exact by the next morning. What is a reasonable design?

10.Stakeholders ask for fresh ride trip data. What should the freshness agreement for the trips table state?

11.You are designing ingestion for a fitness wearable. 2 million devices each send one reading every 10 seconds. What is the average event rate?

12.A mobile game emits 400 million events a day at about 1 KB of raw JSON each. Columnar files compress it about 8 times. Roughly how much compressed storage does one year take?

13.Ad clicks average 30,000 events per second with a 3x peak. Each event is 2 KB. Topics use replication factor 3. What disk write rate must the broker cluster handle at peak?

14.An ad click stream averages 60 MB per second. You keep 7 days in the log with replication factor 3. About how much broker disk do you need before headroom?

15.A consumer instance processes 5,000 events per second from one partition. Peak load is 90,000 events per second and grows 50 percent a year. How many partitions do you plan for?

16.A production Postgres orders database commits about 2,000 row changes per second, all day. Each change event is about 1 KB. About how much change data lands per day?

17.A nightly job must scan 6 TB within one hour. One worker scans about 200 MB per second. What is the minimum worker count, ignoring skew?

18.A clickstream table gets 20 GB a day. It is partitioned by day, by customer (500 customers), and by hour. If each partition gets one file, what is the average file size?

19.At a parcel company, 300 warehouse supervisors keep a dashboard open during the morning peak. It has 12 tiles and refreshes every 5 minutes. What query rate does the serving layer see?

20.A recommendation API needs features for 50 million users. Each user's feature row is about 2 KB in the online store. About how much memory does one copy take?

21.You must backfill 3 years of daily partitions. Each partition takes 20 minutes, and you can run 10 at once without hurting production. About how long does it take?

22.A dashboard has 10 tiles. Each tile scans 2 TB and refreshes every 15 minutes, all day. The warehouse charges $5 per TB scanned. About what does it cost per day?

23.A stream keeps 5-minute windows per device for 2 million devices and accepts events up to 1 hour late. Each open window's state is about 100 bytes. About how much state is held?

24.Parcel scanners upload 5 billion scans a month to your ingestion API, at about 500 bytes each. What average inbound bandwidth is that?

25.A video site stores 2 PB of raw logs. It wants SQL analytics and ML training on the same data, without copying it into one vendor's private format. Which layout fits?

26.Your lake is folders of Parquet files. Readers sometimes see half-written partitions, and two writers corrupted a table last month. What does an open table format such as Iceberg or Delta add?

27.An analyst reran a model on a lakehouse table and got different numbers than last Tuesday. The table keeps 30 days of snapshots. What helps most?

28.Your table format keeps every snapshot forever. Storage has tripled, and deleted user rows still exist in old files. What do you set up?

29.Analysts query the raw lake layer directly because it has every field. Dashboards break whenever a producer changes a payload. What design fixes this?

30.A 40-person startup has 3 TB of data, a few analysts, and no platform team. They ask whether to build a lakehouse. What do you recommend?

31.Three engines read the same open-format tables. Each engine tracks its own pointer to the current snapshot, and one of them reads stale data. What is missing?

32.A lakehouse table fed by change data capture gets updates every minute, and readers query it a few times an hour. Rewriting whole files for each update is costly. Which write approach suits it?

33.A news app's event table gets 5 TB a day. Queries filter by event date and often by article id, and there are 2 million articles. How do you lay it out?

34.A table gets 1 TB a day. Writers produce 50,000 files of about 20 MB each, and queries spend most of their time opening files. What do you aim for?

35.A ticket sale site partitions sales by event id. One stadium concert sells 300,000 tickets in an hour while most events sell dozens. Jobs stall on a single task. What do you change?

36.A team partitions a 200 GB table by user id, creating 3 million folders. Planning a query takes minutes before any data is read. What is the root problem?

37.A table was partitioned by month when it held 1 GB a month. It now gets 2 TB a month, and queries usually ask for one day. The table format supports partition evolution. What do you do?

38.A streaming job commits small files every minute, and a compaction job rewrites them hourly. Compaction keeps failing with commit conflicts. What helps?

39.Queries on a trips table filter on pickup zone or on driver id, about equally often. Sorting by one column helps only that filter. What layout helps both?

40.Orders and payments are joined on order id every hour, and the shuffle moves 3 TB each run. Your team writes both tables. What layout reduces the shuffle?

41.Log-based change data capture reads from a Postgres replication slot. The connector was down all weekend. On Monday the primary's disk is nearly full. What happened?

42.The orders service commits to its database and then publishes an event to Kafka. Sometimes the commit succeeds and the publish fails, so analytics misses orders. What pattern fixes this?

43.You land change data capture events for a bank's accounts table. Analysts want current balances, and auditors want every change. How do you model it?

44.A transfer debits one account and credits another in one database transaction. Your change data capture (CDC) consumer applies events in small batches, and a dashboard briefly shows money missing. What helps?

45.A reference table of 300 country rows changes a few times a year. A teammate wants log-based change data capture (CDC) for it, like the big tables. What do you suggest?

46.What is the most useful freshness signal for a change data capture pipeline feeding a lakehouse?

47.A delivery app counts orders per 1-minute window by event time. Phones send events up to 20 minutes late, but 99 percent arrive within 2 minutes. Dashboards want results within 3 minutes. What do you choose?

48.A stream reads 24 Kafka partitions. Two partitions get no traffic at night, the job's watermark stops advancing, and no windows close. What is the fix?

49.A music app defines a listening session as ending after 30 minutes of inactivity. The watermark trails event time by 10 minutes. When can a session be emitted as final?

50.A daily batch for a bike share computes rides per station. About 3 percent of ride events arrive over the next 3 days. Finance needs stable numbers and operations needs early ones. What design fits?

51.A parcel tracker builds status from scan events. A delivered scan arrives before the out-for-delivery scan. The table now shows the parcel back out for delivery. What rule fixes it?

52.A streaming job updates hourly totals when late events arrive. A downstream job copied each hour once and never sees the updates. What should the interface between them be?

53.Raising allowed lateness from 1 hour to 24 hours would capture 0.4 percent more events, but stream state would grow about 24 times. What is the better call?

54.A stream joins ad impressions with clicks that can come up to 1 hour later. How long must impression state be kept?

55.A stream job reads Kafka and writes to a lakehouse table. After a crash you need no duplicates and no gaps. What gives effectively exactly-once results?

56.A bug in an enrichment step corrupted 3 days of output. Which design choice makes recovery a replay instead of an incident?

57.During a 30-day replay, the job would also re-emit events to a topic that a billing service consumes. What do you do?

58.Producers assign a random UUID on each send, including retries. Downstream dedupe by event id still sees duplicates. What should change?

59.You dedupe a payment stream by event id in stream state. Retries arrive within 10 minutes, but a partner redelivers the whole day each night. What do you do?

60.Your job uses Kafka transactions end to end. It also calls an external pricing API and writes results to Redis. Which part does the transaction not cover?

61.A metrics pipeline for app crash counts can tolerate a tiny overcount. Making it exactly-once would double its cost. What do you choose?

62.You change a sessionization rule and must recompute a year of output while the live job keeps running. How do you cut over?

63.A 2-year backfill will compete with nightly production jobs for the same warehouse. How do you run it?

64.Before publishing a backfilled year of data, which check gives the most confidence?

65.You need to backfill a new column for 3 years, but raw logs older than 1 year were deleted. What do you tell stakeholders?

66.A chunked backfill fails on chunk 41 of 120. How should it be designed so you can resume safely?

67.You backfill a core events table for last quarter. Twenty downstream tables were built from it. What do you do?

68.An app team will add a column to a large production orders table. Your change data capture (CDC) pipeline lands it into a lakehouse table. What should already be in place?

69.A producer team renames a field in the checkout event, and your pipeline breaks at 2 a.m. What arrangement prevents this class of break?

70.Consumers of a topic often upgrade weeks after producers do. Which compatibility rule should the schema registry enforce for the topic?

71.You write a data contract for an orders feed used by finance and by ML. What belongs in it beyond the field list?

72.A producer must change amount from dollars to cents. Many teams consume it. How do you roll it out?

73.A product team ships new event fields every week. Analysts want them fast, but curated tables need review. What layout balances this?

74.A lakehouse id column is a 32-bit integer, and new values will exceed 2.1 billion. What is the least disruptive change?

75.Finance complains about bad data in the revenue table. The cause is an upstream service that started sending test orders. Where should the fix live?

76.Dashboards for 2,000 users query a lake engine that scans Parquet, and p95 latency is 40 seconds. They show daily metrics by region and product. What do you add?

77.A merchant portal shows each merchant its last 90 days of sales, with p99 latency under 200 milliseconds at 3,000 requests per second. Where should the API read from?

78.A fraud model uses charges in the last hour, computed in SQL for training and in Java for serving. Offline accuracy is high, but online it drops. What is the likely cause and fix?

79.Training data for a churn model joins each label to customers' current feature values. The model looks great offline and fails in production. What is wrong?

80.A ride app's operations team needs per-zone trip counts updated every few seconds, filtered and sliced live by many users. Which serving choice fits?

81.Three dashboards show three different revenue numbers for the same week. Each one defines revenue in its own SQL. What do you build?

82.An API reads features loaded hourly from the warehouse into a key-value store. A load fails halfway, leaving some keys new and some old. What design avoids this?

83.Your pipeline lands raw data, cleans it, and publishes curated tables. Where do you place data quality checks?

84.A hotel booking feed passes all row count and null checks, but the average nightly price fell 90 percent overnight. Finance found it a week later. What check was missing?

85.A source column turns out to hold wrong values since March 3. Which capability tells you fastest which reports are affected?

86.A critical quality check fails on the daily orders table at 05:00. The executive dashboard refreshes at 07:00. What should happen?

87.How do you show that the lake copy of a payroll database is complete?

88.A user asks to be deleted. Their events are spread across 2 years of a 200 TB lakehouse table. Which design makes deletion practical?

89.Your raw event archive is immutable for audit. Deletion requests must still make a user's personal data unreadable. What approach fits?

90.Deletion requests arrive daily, and each one rewrites files across many partitions. Compute costs spike. What do you change?

91.Hundreds of tables contain email columns, and new ones appear weekly. Granting access table by table is falling behind. What scales?

92.Engineers debug production pipelines by copying real customer data into a development bucket. What do you set up instead?

93.Legal says clickstream rows with IP addresses may be kept for 90 days, but aggregates can be kept forever. How do you design the tables?

94.Regulators ask who read patient lab results in a clinic's analytics platform last quarter. What must the platform already have?

95.A daily pipeline has 200 tasks. When one fails at 03:00, the team reruns the whole pipeline, which takes 6 hours. What design fixes this?

96.The finance mart must be ready by 08:00. Upstream data is running late, and the mart will miss the deadline. What should the platform do automatically?

97.Your lakehouse runs in one cloud region. The business wants dashboards back within 4 hours if the region fails, and can lose 1 hour of data. What do you design?

98.Ten teams' pipelines depend on each other through table names, and nobody knows the full graph. A schema change broke four teams. What helps most?

99.One malformed event makes a stream job crash, restart, read the same event, and crash again. What should the design include?

100.A dashboard costs about $9,600 a day because each tile scans 2 TB every 15 minutes. Which change cuts the most cost?

101.The data platform bill doubled in a quarter, and nobody knows why. What do you set up first?

102.Event data older than 13 months is queried a few times a year, and it is 80 percent of storage cost. What do you do?

103.Nightly batch jobs run on on-demand machines, checkpoint their progress, and can be retried. Which cost lever fits them?

104.Analysts run SELECT * on a 300-column table to look at 5 columns. The warehouse bills per byte scanned. What helps most?

105.You are migrating 400 reports from a legacy on-premises warehouse to a lakehouse. What is the safest plan?

106.Before a migration, how do you decide which of 3,000 legacy tables to move?

107.A migrated revenue report differs from the legacy one by 0.3 percent. What do you do before cutover?

108.During a 6-month migration, both warehouses must hold the same data. Which feed approach avoids drift?

109.The legacy warehouse runs 2,000 stored procedures holding business logic. What do you do with them?

110.On cutover day, the new pipeline produces wrong numbers. What should your plan already contain?