Netflix data interview pack

The Netflix loop

1. SQL / Spark

   Sessionize playback events with a gap. Event time vs processing time. Why floored timestamps are wrong.

2. Streaming

   Consumer lag SLO, late events, and what you drop when the watermark moves.

SQL (3)

1. easy · SQL

   Sessionize click events with a 30-minute inactivity gap.

   Flag a new session when occurred_at − lag(occurred_at) over the customer is greater than 30 minutes (or null for the first event). Then session_id = a running sum of that flag. That is islands-and-gaps. Do not group by a floored timestamp — that splits real sessions and merges strangers.

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.

Coding (6)

1. medium · Spark

   Driver vs executor. Where does your Python UDF actually run?

   The driver builds the plan. Executors run tasks on partitions. A Python UDF runs in a Python worker next to the executor — it is not “free SQL.” Prefer Spark SQL / built-in functions. collect() on a large frame is how you OOM the driver.

2. medium · Spark

   Kryo vs Java serialization. When do you care?

   When the UI shows time in serialization and shuffle bytes are huge. Register classes. It does not fix a bad join.

3. hard · Spark

   S3 vs HDFS locality. Why was the same job faster on HDFS?

   Object stores have weaker locality and more LIST/GET. File sizing and partition pruning matter more on S3.

4. 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.

5. hard · Spark

   AQE is on. You still got a 2 TB shuffle. What did AQE not fix?

   A missing broadcast because the small table was estimated huge, or a explode that created the 2 TB. AQE helps skew and coalesces — it does not rewrite a bad join key. Look at the SQL plan after AQE, not the one you hoped for.

6. medium · Spark

   repartition(200) before a write. When is that the wrong knob?

   When 200 files are still tiny (KB) or still huge (10 GB). Aim for file size (128–512 MB) from table needs, not a lucky integer. coalesce after a wide shuffle if you only need fewer files.

Data modeling (3)

1. medium · Python & modeling

   Where do you put data-quality checks — in Python, in dbt, or in the orchestrator?

   Row-level and schema checks closest to the transform (dbt tests, Great Expectations, or assertions in the Spark job). Orchestrator checks are for “did the file arrive / is row count 0.” Python pandas checks on a 200 GB extract are the wrong layer. Say the failure mode: fail the run vs quarantine vs warn.

2. easy · Python & modeling

   Pickle a 20 GB frame to S3. What do you say?

   No. Parquet/Arrow with a schema. Pickle is a security and compatibility smell.

3. easy · Python & modeling

   pickle vs parquet for a 20 GB frame between tasks. What do you store?

   Parquet (or Arrow IPC) with a schema. pickle is Python-version brittle and not a lake format. If Spark is downstream, write partitioned Parquet and let Spark read it.

Orchestration (3)

1. hard · Airflow

   A load task appends. The retry doubled yesterday. What was missing?

   Idempotency: delete-then-insert for the partition, or MERGE on a business key. Passing a 50 MB dataframe through XCom is the other fail — XCom is for small metadata (paths, watermarks), not data.

2. medium · Airflow

   You need a backfill of 400 days. How do you not strand the 08:00 DAG?

   A separate backfill DAG with a low pool/priority, partitions in batches, and a freeze on overlapping writes to gold. Same DAG with a huge start_date is how Monday pages.

3. medium · Dagster

   Asset checks vs dbt tests. Who owns which?

   dbt tests grain and relationships in the warehouse. Asset checks are the orchestrator saying this materialization is not fit for downstream. Both. Checks without dbt leave SQL untested. dbt without checks leave a green DAG on an empty table.

Streaming (6)

1. medium · Recent signal, 2026-09-05

   The source was down six hours. Processing-time windows then show a spike. What actually happened?

   Events were bucketed when they were processed, not when they occurred. Event-time plus watermarks put counts on the real hour. The spike is a lie.

2. easy · Kafka

   Kafka vs Kinesis — what is the real trade?

   Kafka: you own brokers, retention, and ops (or pay for a managed cluster). Kinesis: less ops, shard math, AWS lock-in, 24h–365d retention knobs. Pick from ops budget and existing cloud, not a blog benchmark.

3. easy · Apache Flink

   Event time vs processing time. When does the dashboard lie?

   Processing time windows close on wall clock. Late Kafka events fall in the wrong window. Event time plus watermarks is the honest window. If you cannot explain watermarks, you are not ready to ship the job.

4. easy · Apache Flink

   Checkpoint vs savepoint. One sentence each, then when you use savepoint.

   Checkpoint is automatic recovery state. Savepoint is an operator-triggered, versioned snapshot for upgrade/rewrite. You take a savepoint before a job graph change that would break checkpoints.

5. hard · Apache Flink

   A keyed state store is 80 GB and checkpoints take 12 minutes. First two levers?

   TTL on state, tighter windows, incremental checkpoints, and whether you stored the whole event instead of an aggregate. Scaling TaskManagers helps after you stop keeping forever.

6. 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 (8)

1. medium · Snowflake

   Micro-partitions and clustering: when does a clustering key pay for itself?

   Natural ingestion order already clusters some tables (time-series). A clustering key helps when queries filter a column that is scattered across partitions (high clustering depth). It costs background credits. Check SYSTEM$CLUSTERING_INFORMATION before you turn it on. Search Optimization is a different product for point lookups.

2. easy · Databricks

   A shuffle-heavy join OOMs the worker. Name three levers before you “just scale the cluster.”

   Broadcast the small side if it fits. Repartition both on the join key. AQE / skew join hints. Filter before the join. If it is still huge, rethink the grain — you may be exploding a many-to-many. Scaling the cluster last is the expensive answer.

3. medium · Databricks

   You VACUUMed with a 7-day retention and a reader is on an 8-day-old notebook. What happens?

   The reader can fail on missing files. Time Travel is not a promise beyond retention. Tell analysts the retention SLA; do not VACUUM 0 HOURS to “save storage.”

4. easy · Trino

   What does Trino store?

   Nothing durable of yours. Coordinator + workers query catalogs. Gold tables live in Iceberg, Hive, or a warehouse. Calling Trino a warehouse is a fail.

5. hard · Trino

   Trino or Spark SQL on the same Iceberg table?

   Trino for interactive federation. Spark for heavy ETL. Same table, different engines — that is the lakehouse pitch. Two writers is still forbidden.

6. easy · Trino

   SELECT * on a 400-column Iceberg fact in Trino. What happens to the plan?

   It still reads every column from Parquet footers/row groups you need — column pruning helps only if you list columns. Teach BI to select metrics. SELECT * is a cost bug.

7. medium · Trino

   Hive connector vs Iceberg connector on the same files. Why does Iceberg win for concurrent writes?

   Iceberg snapshots and optimistic commit; Hive table is files + a metastore that races. Two writers on Hive is corrupt partitions. Iceberg is the contract.

8. medium · Trino

   When is Trino the wrong engine vs Spark?

   Heavy shuffles, iterative ML, and huge writes with custom committers. Trino shines at interactive SQL on the lake. Spark shines at jobs. Do not run nightly 8-hour ETL only in Trino because “SQL is simpler” if the plan is a disaster.

Cloud (3)

1. hard · AWS

   S3 + Glue + Athena vs Redshift — how do you choose in sixty seconds?

   Ad-hoc and cheap scans: Athena on partitioned Parquet with a Glue catalog. Concurrent BI and predictable SLAs: Redshift (or Redshift Serverless). Glue is the job runner / catalog, not the warehouse. “Everything in Athena” dies on 40 concurrent dashboards.

2. easy · AWS

   S3 committers — why did a failed Spark write leave a partial prefix?

   The default committer can list/rename badly on S3. Use a magic committer and an idempotent overwrite of a partition.

3. medium · AWS

   Athena query billed 1.2 TB. The table is partitioned by day. First three checks?

   Did the query filter the partition column (not DATE(ts) wrapping)? Are there thousands of tiny files? Is SELECT * pulling 400 columns? Then look at partition projection vs Glue partitions that never got added.

System design (4)

1. hard · System design

   Two engines must share one lake table. What do you refuse to do?

   Dual-write CSV trees. Pick Iceberg (or UniForm) with a single writer. Two writers is how the copies lie.

2. easy · System design

   Streaming dashboard vs a 1-hour mart. How do you choose?

   If the decision can wait an hour, batch is cheaper and simpler. Streaming is for operational actions (fraud, inventory, live ops). Do not stream a CFO dashboard to look modern.

3. easy · System design

   Disaster recovery for the lake. What is the actual runbook?

   Cross-region replica or object-lock, a metastore backup, and a restore drill. “S3 is durable” is not a runbook. Know RPO/RTO in hours, not adjectives.

4. 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

   Four checks you put on every gold table first.

   Freshness, volume (not zero, not 10×), schema, uniqueness of the grain. Everything else is extra. 400 noisy tests train people to ignore red.

Behavioral (2)

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

   On-call: three pipelines red, only you awake. How do you order the work?

   User-facing gold first (revenue, auth), then the job that unblocks others, then noise. Communicate ETA in the incident channel before you debug. Heroic silence is not seniority.

SQL coding labs

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

1. 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/

2. hard · about 14 min · tables: orders

   Seven-day moving average

   Daily revenue is noisy, so the dashboard smooths it over a trailing week. The frame has to include the current day and the six before it — off-by-one here is the most common review comment on this query.

   https://www.datalane-data.blog/coding/problems/moving-average-7d/

3. hard · about 20 min · tables: events

   Sessionize events (30-minute gap)

   Group each customer’s events into sessions that break after 30 minutes of inactivity. This is the three-step gap-and-island pattern: look back, flag a break, then accumulate the flag into an id.

   https://www.datalane-data.blog/coding/problems/sessions-30m/