Why Every Data Engineer Should Practice Explaining Their Case Study Aloud

Interviewer: Design a platform for daily sales reporting
and real-time fraud alerts.

Candidate: Okay, so we will use Kafka for ingestion,
then Spark Streaming, then store in S3, then Redshift,
then Power BI on top. Airflow for scheduling.

Interviewer: Why Kafka for the daily report?

Candidate: ...because it is scalable.

Interviewer: Design a platform for daily sales reporting
and real-time fraud alerts.

Candidate: Before I draw anything, I see two different needs.
The sales report is read once a day, so a few hours of delay
is fine. Fraud alerts need seconds, not hours.

Can I ask roughly how many orders per day, and where the
orders live today? A transactional database, or events?

If it is a database, I would batch-load the report path
and only stream the fraud path. That keeps the expensive,
always-on part as small as possible.

Spoken sizing chain (assumptions first):

"Let me assume 2 million orders a day.
 Each order event is roughly 1 KB as JSON.
 So that is about 2 GB of raw JSON per day.
 Over a year that is around 730 GB raw.
 In Parquet with compression it is usually much smaller,
 so a few hundred GB per year in the lake.

 That is comfortable for a cloud warehouse,
 so I do not need a big Spark cluster for the report path.
 I would keep Spark for heavier joins if they appear."

Notice: every number is an assumption I can change.

-- Incremental upsert of yesterday's orders into the fact table
-- (standard MERGE, works in Snowflake, BigQuery, Databricks SQL)

MERGE INTO analytics.fact_orders AS t
USING (
    SELECT order_id, customer_id, order_ts, amount, status, updated_at
    FROM staging.orders_raw
    WHERE updated_at >= DATEADD(day, -1, CURRENT_DATE)
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at > t.updated_at THEN
    UPDATE SET status = s.status,
               amount = s.amount,
               updated_at = s.updated_at
WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, order_ts, amount, status, updated_at)
    VALUES (s.order_id, s.customer_id, s.order_ts, s.amount, s.status, s.updated_at);

-- Keep only the latest version of each order
WITH ranked AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY order_id
               ORDER BY updated_at DESC
           ) AS rn
    FROM staging.orders_raw
)
SELECT order_id, customer_id, order_ts, amount, status, updated_at
FROM ranked
WHERE rn = 1;

-- Spoken version:
-- "I partition by the business key, order by the update time
--  descending, and keep row number one. If two rows tie on
--  updated_at I would add an ingestion timestamp as a tiebreaker."

from pyspark.sql import functions as F

orders = (spark.readStream.format("kafka")
          .option("kafka.bootstrap.servers", "broker:9092")
          .option("subscribe", "orders")
          .load()
          .select(F.from_json(F.col("value").cast("string"),
                  "order_id STRING, amount DOUBLE, event_ts TIMESTAMP").alias("o"))
          .select("o.*"))

per_window = (orders
              .withWatermark("event_ts", "2 hours")
              .groupBy(F.window("event_ts", "10 minutes"))
              .agg(F.sum("amount").alias("total")))

# Spoken: "The watermark tells Spark to keep window state for
# two hours of event time. Events later than that are dropped,
# so I would also run a daily batch correction from the source."

PICK ONE PROMPT PER SESSION

1. A food delivery app wants hourly order counts per city
   and an alert when a restaurant's cancellations spike.
2. A bank wants a daily customer 360 table built from
   loans, cards and app logins.
3. A ride app wants trip data in a lake for analysts,
   plus driver earnings that must never be double-counted.
4. A retailer moves from nightly CSV drops to CDC from
   its order database. Design the migration.
5. An edtech platform wants video watch-time events
   joined with course enrolments for weekly reports.

25-MINUTE RUN (record audio)

00:00-03:00  Clarify: consumers, freshness, volume, sources
03:00-06:00  Size: rows/day, bytes/row, yearly storage
06:00-14:00  Design: ingest -> land -> transform -> serve
14:00-20:00  Failures: rerun, dupes, late data, schema change
20:00-23:00  Monitoring: row counts, freshness, alerts
23:00-25:00  Summary: 'If I had more time I would...'

Rule: say every assumption out loud before using it.

SCORE YOUR RECORDING (1-5 each)

[ ] Asked about freshness before naming tools
[ ] Gave at least one sizing number with assumption
[ ] Named one rejected alternative and why
[ ] Explained idempotent reruns clearly
[ ] Handled duplicates and late data
[ ] Mentioned at least one data quality check
[ ] Ended with a short summary

Lowest score: ______  Fix it in the next run.