Meta data interview pack

The Meta loop

1. SQL

   Rolling unique users, COUNT DISTINCT cost, and a metric definition that cannot fork.

2. Metrics

   One definition of DAU. What happens when ads and feed teams both own a dashboard.

3. Spark

   A shuffle that OOM’d. Levers before you buy more nodes.

SQL (4)

1. medium · SQL

   Running total of daily revenue. Why is SUM(SUM(amount)) OVER (...) valid?

   The inner SUM is the GROUP BY aggregation (daily revenue). The window SUM then walks those grouped rows. You need both: group to the day first, then window. A bare SUM(amount) OVER (ORDER BY day) without grouping double-counts every order row.

2. medium · SQL

   You need a 7-day rolling unique users. What breaks if you use COUNT DISTINCT in a window carelessly?

   Many engines restrict DISTINCT in windows or make it expensive. A better story is a daily uniques table then a rolling sum of an inclusion flag — or a bitmap if the warehouse has one. Say the grain first.

3. medium · SQL

   You grouped by DATE_TRUNC(week, ts) and compared to last week. Why are week 1 and week 53 lying?

   ISO vs locale weeks, incomplete first/last weeks, and time zones. Compare on a date spine with is_partial_week, or use a 7-day trailing window from today instead of calendar weeks.

4. hard · SQL

   You must count distinct users in a 30-day window every day. Sketch a table design that does not scan 30 days of events nightly.

   Daily unique-user fact (user_id, day). Then for each day, count distinct over the last 30 daily rows — still heavy — or HyperLogLog / bitmap sketches if approximate is allowed. Say the SLA: exact vs 2% error.

Coding (2)

1. medium · Spark

   Broadcast join vs shuffle join. What is the failure mode of a hint?

   Broadcast sends the small side to every executor. If it is not actually small, you OOM workers. AQE can convert automatically. A forced broadcast hint on a growing dimension is a time bomb. Measure the small side after filters.

2. easy · Spark

   Read an explain plan: how do you confirm a broadcast?

   BroadcastHashJoin (or AQE converted) in the physical plan / SQL tab. Do not guess from a hint you leftover last year.

Orchestration (1)

1. easy · Airflow

   TaskFlow vs operators. When do you still write a classic Operator?

   TaskFlow is fine for Python that stays in the worker. Use a dedicated Operator (SnowflakeOperator, DatabricksSubmit) when the work should run in the warehouse, not on the Airflow box. The tell: if the function downloads a table into pandas, you already lost.

Streaming (3)

1. hard · Kafka

   partition=user_id and one celebrity user melts a partition. Redesign in two sentences.

   Hash a finer key (user_id + shard) or isolate hot keys to a dedicated topic. Adding consumers cannot split a partition. Measure key cardinality first.

2. medium · Apache Flink

   A hot key is melting one task. First move?

   Name the key, salt or pre-aggregate, or isolate the skew. Adding task managers without fixing the key just moves the hotspot. Same answer as Spark skew.

3. medium · Apache Flink

   Event time is 03:00; processing time is 09:00 after a source outage. What does a processing-time window do to DAU?

   It buckets into 09:00 and lies. Event-time windows + watermarks put counts on 03:00. Dashboards that use processing time after an outage show a fake spike.

Warehouse (1)

1. medium · Trino

   Cost-based optimizer picked a broadcast of a 200 GB table. What do you check?

   Stats missing or stale on Iceberg, so NDV looked tiny. ANALYZE / snapshot stats, or hint, or raise the broadcast threshold only with a story. Hints without stats are superstition.

System design (1)

1. medium · System design

   Design clickstream → daily active users for 50 analysts at 09:00. 5-minute lag is enough. Sketch layers and one SLA.

   Events → Kafka/Kinesis → cheap land (S3/Iceberg bronze) → 15–60 min silver sessionization → gold DAU table partitioned by day. Analysts hit gold, not Kafka. SLA: gold partitioned yesterday complete by 08:30 UTC. Do not stream the CFO dashboard.

   Hint: Batch gold, not a live dashboard on Kafka.

Data quality (1)

1. medium · Data quality

   A check is red every day and nobody cares. What did you do wrong?

   The threshold is wrong or the owner is missing. Fix or delete. A permanently red test is worse than no test — it trains the team to ship on red.

AI / ML (4)

1. hard · AI / RAG / MLOps

   Where does a data engineer stop in MLOps?

   Reliable feature tables, training-data DAGs, point-in-time joins, and serving logs. Model architecture and eval design belong with ML. Interviewers want the boundary, not a PyTorch lecture. If you cannot explain training/serving skew, you are not done.

2. medium · AI / RAG / MLOps

   Online vs offline features for the same model.

   Same definition, two stores, a point-in-time story. If online computes a different join, you shipped skew.

3. hard · AI / RAG / MLOps

   Offline feature table has 40 columns; online store has 12. The model’s AUC collapsed after deploy. First check?

   Training-serving skew: missing features defaulted, or a different join grain online. Diff a production request against the training row for that entity_id and time. Then freeze a schema contract.

4. medium · AI / RAG / MLOps

   Eval for a classifier in the warehouse vs a Python job. What must both have?

   A labeled holdout that was not used to tune, a threshold policy, and a timestamp. Warehouse UDF metrics are fine; leaking the holdout through a JOIN is not. Say the positive class rate.

Behavioral (3)

1. medium · Behavioral

   Describe a design you were wrong about. What did you change?

   Pick a real miss: Type-1 when you needed Type-2, Streams+Tasks when you needed Airflow, pandas on a 40 GB join. Say the incident that proved it, the rollback, and the rule you wrote down. Humility plus a written policy beats a perfect résumé of wins.

2. medium · Behavioral

   A PM wanted the metric by Friday without a source contract. How did you negotiate?

   Offer a dated prototype on bronze with a watermark in the title, not gold. Gold needs a key and an owner. Give them a number they can use with a caveat, or a date you can certify. “No” without an alternative is brittle; “certified Friday” without a contract is how you get fired later.

3. medium · Behavioral

   A project you led was cancelled after two months. What was still valuable?

   The decision record, the prototype that showed cost, and the people you unblocked. Do not pretend it shipped. Interviewers want learning and shutdown hygiene, not a fairy tale.

SQL coding labs

Problem statements only. Run them against tests on DataLane; solutions live in the pad.

1. medium · about 6 min · tables: employees

   Who reports to Ben

   The employees table is an org tree: manager_id points back at employee_id in the same table. Return Ben’s direct reports — one level down, not the whole subtree.

   https://www.datalane-data.blog/coding/problems/reports-to-ben/

2. medium · about 10 min · tables: orders

   Daily completed revenue

   Roll completed orders up to a calendar day. ordered_at is a timestamp, so grouping on it raw gives you one group per second instead of one per day.

   https://www.datalane-data.blog/coding/problems/daily-completed-revenue/

3. medium · about 12 min · tables: orders

   Revenue share by country

   Each country needs its percentage of total completed revenue on the same row as its own revenue. A second query for the grand total works but scans the table twice — do it with one window instead.

   https://www.datalane-data.blog/coding/problems/revenue-share-by-country/

4. hard · about 14 min · tables: orders

   Day-over-day revenue growth

   Put yesterday’s revenue and the percent change beside each day. The first day has no prior row, and the answer must leave it NULL rather than inventing a zero.

   https://www.datalane-data.blog/coding/problems/day-over-day-growth/

5. medium · about 10 min · tables: events

   Event funnel by stage

   Product wants funnel volume per stage: how many events fired and how many distinct customers reached each one. Total events overstates reach because one customer can fire the same event repeatedly.

   https://www.datalane-data.blog/coding/problems/event-funnel-stages/

6. hard · about 18 min · tables: events

   First-touch traffic source

   Growth wants each customer’s first event source. First means earliest occurred_at; tie-break on event_id. Source lives in the JSON payload. Return customer_id and source, smallest customer_id first.

   https://www.datalane-data.blog/coding/problems/first-touch-source/