Design a Pipeline: Intermediate

Debezium on Kafka Connect reads the Postgres write-ahead log through a logical replication slot and emits 1 event per row change, with the after image and the operation type. The orders table takes 1.2 million inserts and roughly 3 million status updates a day, about 4.2 million change events, each in Kafka under a second after commit. The primary carries 1 slot reading the WAL instead of a polling query scanning updated_at every minute, and deletes arrive as events, which a polling extract never sees.

The cost is operational, and naming it unprompted is what scores. A replication slot pins WAL on the primary's disk until the connector confirms it has read it, so a connector that stops on Friday evening leaves the primary holding every segment written over the weekend, and a full disk takes the production database down with it. Cap the damage with max_slot_wal_keep_size (Postgres 13 and later) and alert on slot lag.

Stripe's REST endpoint pages with a starting_after cursor and returns at most 100 objects per page. Refreshing the full charge history every hour fails within weeks, because the request count grows with the account's age until the job hits the rate limit. Use a high-water mark: store the largest created timestamp from the last successful run, request charges with created[gte] set to it, and page with the cursor so a restart resumes mid-list. That paging only finds new charges, and a charge changes after it is created, when it is captured and sometimes when it is refunded or disputed. Those changes come from /v1/events (charge.captured, charge.refunded, kept 30 days), pulled with the same loop on the event's created, and that feed gives finance its capture dates.

Do the division out loud. Stripe's live mode allows 100 read requests a second, so today's 300,000 charges take 3,000 requests, which is around 30 seconds. A 5 million charge backfill is 50,000 requests, roughly 8 minutes if nothing throttles. A 429 gets exponential backoff with jitter, capped near 30 seconds, so 20 workers sharing the limit do not retry together. created[gte] rather than created[gt] re-reads the boundary second on purpose, and the transformation section's merge absorbs the overlap.

The courier partner has no reachable database and no API: they drop around 2 GB of CSV into an S3 prefix at 06:00 UTC, some days as 3 part files. S3 shows each object only once its upload completes, but nothing in a listing says the set for that dt is complete, so an Airflow sensor waits for the partner's _SUCCESS marker: a load that starts after file 1 of 3 parses cleanly and books a third of Tuesday. A day of latency suits a 07:00 report. The failure worth raising first is duplicate delivery: the partner re-uploads Tuesday's file on Wednesday under a new name, and every courier count for Tuesday doubles.

The app sends 40 million events a day: 463 a second on average, around 5,000 at the Friday dinner peak. Producers write to a Kafka topic keyed by user_id, so a user's events stay ordered on 1 partition, and 12 partitions at several thousand messages a second each cover the peak and give a consumer group 12 workers. Seven days of retention lets a consumer reset its offset and replay the week, which answers "why Kafka and not a Lambda per event."

Parquet stores each column contiguously with min and max statistics per row group, so a query reading 3 of 40 event columns skips the other 37 and opens only row groups whose range covers the filter. A day of 40 million JSON events at about 1 KB each is 40 GB raw and near 4 GB as Snappy Parquet. Avro is row-oriented and carries its schema, which suits the Kafka path: Debezium publishes Avro against the Confluent Schema Registry, so a new column on orders becomes a new schema version. Zstd compresses about 15% tighter than Snappy for a little more decode CPU, and silver takes that trade because Athena bills bytes scanned, so every read gets 15% cheaper; in bronze, read only for rebuilds, the gain is storage alone.

Raw Parquet in S3 is a folder of files with no transactions: a Spark job that dies after 300 of 500 files leaves 300 files that look complete to every reader. Iceberg adds a metadata layer, as do Delta Lake and Hudi: a write produces new files and a new snapshot, and the table's current pointer moves to it in 1 atomic swap, so readers see the whole commit or none. Snapshot history also gives time travel: FOR VERSION AS OF reads gold as it was before yesterday's bad run, the first thing finance asks for during an incident.

The operations query filters by time and city; finance filters by day. Partition silver events by day of event_ts, so a day's query opens 1 of 365 partitions. Over a year the table is about 1.5 TB of Parquet, and at Athena's $5 per TB scanned a 1-day query costs roughly 2 cents while the same query on an unpartitioned copy costs over $7. At 200 analyst queries a day that is $1,400 against $4, and the interviewer wants that subtraction done at the whiteboard.

"Partition by the filter column" leads candidates to over-partition. By user_id, each 5-minute commit writes a file for every active user among 3 million: millions of tiny files a day, manifests that grow with them, and planning that outlasts the query. Keep the partition at day. city has 60 values and would slice each day into 70 MB pieces. Put it in the sort order instead, and the per-file min and max statistics still let the planner skip files.

The 5-minute micro-batch writes a file per Spark task per commit: 288 commits at 12 tasks leave 3,456 files in each day's partition at around 1.2 MB apiece, and per-request S3 latency on 3,456 objects outweighs the bytes read. Iceberg's rewrite_data_files runs hourly toward a 128 MB target. Name the target: "we would run compaction" without a number sounds like a word heard in a talk.

MERGE makes the run idempotent. When a replayed change event matches its key, the updated_at guard rejects it, leaving the row and every downstream count unchanged, where a plain INSERT would add a second row and double them. The ROW_NUMBER step matters as much: 1 order can change 3 times inside 5 minutes, and Spark with Iceberg and Snowflake by default reject a MERGE in which several source rows match 1 target row. Filtering on bronze ingestion time rather than updated_at keeps a late event from being skipped forever, because it carries an old updated_at and a new ingested_at. Once you have covered all 3 in that order, the interviewer stops probing.

Incremental everywhere is its own trap. Gold daily_revenue holds 365 rows after a year and depends on refunds that arrive up to 30 days after the charge. Rebuilding its last 45 days every morning reads around 54 million order rows plus the charges joined to them and finishes in a few minutes. It needs neither a watermark nor a merge key, and late data needs no special logic, because every run recomputes the window. The rule: incremental for the fact with hundreds of millions of rows, full refresh over a trailing window when that window's input rescans in minutes. dbt expresses either as 1 config line, and the interviewer wants to hear that you picked per model rather than per pipeline.

Forty operators keep a map open that refreshes every 30 seconds: 115,200 queries a day, each asking for 1 city's order counts by status over the last 2 hours. Athena adds a planning round trip and a few seconds per query; Snowflake keeps its smallest warehouse awake all day, roughly $48 at $2 a credit, for reads that touch 144 rows. Instead, the 5-minute job writes orders_by_city_5m to Iceberg gold and upserts the same rows into Postgres with a primary key on (city, status, bucket_start). Sixty cities times 6 statuses times 288 buckets is 103,680 rows a day, and the 144-row read for 1 city's 2 hours returns in single-digit milliseconds. Postgres keeps 30 days; Iceberg keeps the history.

Finance reads daily_revenue once a morning and compares it to Stripe's payout report, so the number has to match Stripe. The model joins silver orders to silver charges on order_id and books a charge on the UTC day Stripe captured it, taken from the charge.captured events, rather than the day the order was placed; the 2 definitions differ by a few hundred orders around midnight, and reconciling to the wrong one costs a controller an afternoon. It runs at 06:30 behind the courier sensor, 30 minutes before the 07:00 report. It rebuilds a 45-day window for refunds and carries a computed_at column, and when a controller asks why Tuesday changed, the Iceberg snapshot they first read answers. Serve it through Athena on the Iceberg table, because 3 people run 20 queries a day and a second copy is a second thing to reconcile.

Every serving table has a freshness target the pipeline measures: max(bucket_start) in the operations table within 10 minutes of the clock, yesterday's daily_revenue by 07:00, each checked by a query the monitor runs every minute. "How do you know the dashboard is current?" wants that query, and the DAG in the next section shows where the alert attaches.

Every Airflow task retries 3 times with exponential backoff from 2 minutes, and a retry is safe only if running the task twice leaves the same table. The Stripe pull re-reads the boundary second into bronze, and the silver merge on charge_id collapses the overlap. The Spark micro-batch records its Kafka offsets in a checkpoint and tags each Iceberg commit with its batch id, so a job killed between the 2 replays the batch and Iceberg skips a batch it already committed. Where no natural key exists, the run id becomes a column and the load deletes that run id's rows before inserting, the sentence to say when asked about a source with no primary key.

At the dinner peak Debezium can fall 10 to 15 minutes behind the primary, so order changes reach silver with an updated_at older than buckets gold has already written. The gold job recomputes from the hour boundary 2 hours back on every run, so lag under 2 hours is repaired on the next run, and the 45-day daily_revenue rebuild catches anything later. App events arrive late another way: a phone that loses signal in a stairwell delivers on reconnect, and about 0.3% of events arrive more than 10 minutes late. The micro-batch lands them by arrival with their true event_ts, so they reach the right day partition in silver; a streaming aggregate with a 10-minute watermark would drop that 0.3%, which is why every aggregate here runs in batch over silver.

One row in the 2 GB courier file carries a delivery timestamp of 31/02/2026. A parser that raises halts the load, and the whole day's finance report waits on 1 row. Rows that fail typing go to bronze/courier_deliveries_rejected/dt=.../ with the raw line and the error, and the load counts them and keeps going; an alert fires above 0.1% of the file, the threshold between a typo and a partner who changed their export. Kafka events get a dead letter topic, with the original headers kept so the record can be replayed after the fix.

The orders team adds a nullable tip_cents column on a Tuesday afternoon without telling anyone. Debezium registers a new Avro schema version with the field optional and a null default, which every compatibility mode accepts, and the silver model, selecting columns by name, ignores it until someone adds it. A rename is different. Under the registry's default BACKWARD mode, dropping amount passes. Adding amount_cents is rejected only if it is required with no default, so the rename usually goes through and silver's SELECT amount fails or reads null. Set the CDC subjects to FULL, which rejects removing a required field such as the NOT NULL amount: the connector stops and the slot holds the WAL until the mapping is fixed. A contract test on silver's columns catches whatever the registry still accepts. Iceberg tracks columns by id, so adding one to silver is an ALTER TABLE ... ADD COLUMN with no rewrite; the courier CSV has no registry, so the load asserts its header row first.

Three monitors cover the pipeline. Freshness: max(ingested_at) per bronze table against the clock, paging at 15 minutes for Kafka sources and 90 minutes past expected arrival for the daily file. Volume: today's row count against the trailing 7-day median for the same hour, alerting below 50% or above 200%, which catches a partial file set or a replayed topic before a consumer does. Slot lag: retained WAL bytes on the replication slot, alerting at 10 GB and paging at 30, because the end of that curve is the production database. A silent run, where the DAG succeeds and gold gains 0 rows, trips the volume monitor, which is why it runs on the output table rather than task status.