Serving the Consumer

Concepts covered: Medallion Architecture, Columnar vs Row Formats

The question named operations and finance, and data science appears the moment you ask who else reads the orders. The 3 readers want data at different freshness, and their queries differ in shape and in how many run at once, so 1 gold table cannot serve them all. The interviewer is listening for a serving layer designed backward from each reader rather than forward from the warehouse already drawn.

Operations: a small aggregate in a row store

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.

INSERT OVERWRITE gold.orders_by_city_5m
SELECT city,
       status,
       timestamp_seconds(
         floor(unix_timestamp(updated_at) / 300) * 300
       )                 AS bucket_start,
       count(*)          AS orders,
       sum(amount_cents) AS amount_cents
FROM silver.orders
WHERE updated_at >= date_trunc('HOUR', current_timestamp()) - INTERVAL 2 HOURS
GROUP BY 1, 2, 3;

Finance: a daily table that reconciles to the cent

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.

Data science: the silver tables, and what you do not build

Data science wants every order and event from the last 2 years to train a delivery-time model, scanned in full a few times a week. That is the silver fact, read through Spark with the day partition and city sort doing the pruning. The move that costs the offer is proposing a feature store or a third aggregate before anyone has asked for a feature: silver serves this reader, and a gold table appears when a query pattern repeats.

Say the freshness number for each reader

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.

About this lesson

This section is part of the Design a Pipeline: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.