Airbnb data interview pack

The Airbnb loop

1. Modeling

   One row per booking vs per stay-night. They will ask which metrics break if you pick wrong.

2. dbt

   Incremental lookback for late cancellations. unique + not_null on the booking key.

SQL (2)

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

   A date spine is missing Sundays. What breaks in a 7-day rolling metric?

   The window walks existing days, not calendar days, so Sunday is skipped and Monday’s “7-day” is six days plus a hole. Generate a complete date dimension and LEFT JOIN facts onto it before the window.

Coding (1)

1. medium · Spark

   You cached a 400 GB dataframe. The next action still recomputes. Why?

   Cache is lazy until an action; memory was evicted; or you transformed after cache without using the cached handle. persist(StorageLevel) and check the Storage tab. Caching is not a free lunch on a 20-node cluster.

Data modeling (9)

1. medium · Recent signal, 2026-07-15

   Your incremental orders model uses max(updated_at). Cancellations from three days ago never land. Why?

   A strict greater-than-max misses late rows. Add a lookback window (or a created_at plus an updates stream) and a unique test on the booking key.

2. medium · dbt

   Incremental model: merge vs delete+insert vs insert_overwrite. Pick one and defend it.

   merge when you have a unique key and late-arriving updates. delete+insert (or insert_overwrite) for partition-sized days you can safely replace. insert_overwrite needs a reliable partition column. “incremental” without a unique key or a partition is just append — and append is how you get duplicates.

3. hard · dbt

   Semantic Layer vs copy-pasted Looker measures.

   One metric definition, many consumers. Four definitions of revenue is how finance pages you. dbt metrics or your metrics tool — pick one owner.

4. medium · dbt

   You need yesterday’s incremental plus a 3-day lookback for late CDC. How do you not rebuild the month?

   Filter on a watermark minus lookback, merge on the unique key, and unique/not_null tests. max(updated_at) alone misses late deletes. Say the lookback in hours.

5. hard · dbt

   Two marts both named fct_revenue. How did that happen, and how do you kill one?

   No semantic owner. Freeze both, diff grain and filters with finance, pick a survivor, deprecate the other with a failing test if it is still queried. Renaming in place without a sunset is how Slack fills with “which revenue.”

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

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

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

9. medium · Python & modeling

   A unit test mocks datetime.now() but the DAG still uses the wrong partition. Why?

   The code under test is not using the mocked clock — it reads Airflow context, or a default argument evaluated at import. Inject logical_date. Freezegun does not fix a default arg trap.

Orchestration (13)

1. easy · Airflow

   Why is catchup=True dangerous on a new DAG?

   It backfills every interval since start_date. A two-year daily start_date creates hundreds of runs the moment you unpause. Use catchup=False until you mean it, then a targeted backfill.

2. easy · Airflow

   Sensor versus a shorter schedule. How do you avoid slot deadlock?

   A poking sensor holds a worker slot. At scale use deferrable sensors / triggers, or an external sensor with mode=reschedule. “Wait for the file every minute with a Sensor” is how a 4-worker prod queue dies at 9:00.

3. medium · Airflow

   Dynamic task mapping vs 12 copy-pasted tasks.

   Map over the list of tables/dates at runtime. Do not map 50,000 tiny tasks if the warehouse can loop cheaper. Mapping is organization plus fan-out, not a new executor.

4. easy · Airflow

   A DAG file queries Snowflake at import time. What dies?

   The scheduler parse loop. Keep DAG files cheap. I/O belongs in tasks.

5. medium · Airflow

   How do you rerun one day without editing start_date?

   A UI backfill or a DAG param. Changing start_date to now() is how you lose history.

6. hard · Airflow

   A mapped task blows the scheduler with 8,000 mapped instances. What do you do before “bigger scheduler”?

   Batch the map (chunks of files, not one task per file), raise max_map_length only with a story, and push heavy work to Spark/dbt. Dynamic mapping is not a for-loop over S3.

7. easy · Airflow

   XCom holds a 20 MB pandas JSON. Why is the metadata DB sad?

   XCom is for tiny handles (S3 URI, partition, row count), not dataframes. Put the frame in object storage. Postgres XCom tables are not a data lake.

8. medium · Airflow

   A DAG import queries Snowflake to build task lists. Why did the scheduler restart loop?

   Parse-time I/O. Top-level queries fail or hang, and every parse hits the warehouse. Build the list in a task, use a catalog, or static YAML.

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

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

11. medium · Dagster

    An asset is declared with a daily partition. A late file for 2026-01-03 arrives on the 5th. What must not happen?

    Silently skip, or overwrite 01-05. You materialize the 01-03 partition (backfill) without kicking the whole year. Partitioned assets exist so time is data, not a side effect.

12. easy · Dagster

    You still run Airflow for 40 vendor connectors and Dagster for marts. Is that a failure?

    No. Two orchestrators can be honest if ownership is split and you do not dual-schedule the same MERGE. A rewrite of every DAG is a year, not a sprint. Say the boundary.

13. medium · Dagster

    A freshness policy pages at 09:10 because a 09:00 job ran 12 minutes. Is the policy wrong?

    Maybe: the SLA was a hope. Either start earlier, slim the job, or set freshness to 09:30 and tell stakeholders. A policy that fires every day is a ignored alarm.

Streaming (1)

1. hard · Kafka

   What is a poison pill, and how do you not stall the whole partition?

   A record that always fails deserialization or sink validation. A naive retry loop blocks that partition forever. Dead-letter the record (with the offset), alert, keep consuming. Know the difference between retryable sink errors and bad payloads.

Warehouse (4)

1. medium · Snowflake

   Streams and Tasks vs an external orchestrator. How do you choose?

   Streams + Tasks are fine for a few in-Snowflake incremental loads. Once you have 30 jobs, cross-system dependencies, or a human on-call rotation, put Airflow / Dagster on top and keep Tasks as the SQL worker. Do not invent a second orchestrator inside Snowflake “just because it is native.”

2. hard · Snowflake

   Dynamic Tables vs Streams + Tasks. Pick one and defend it.

   Dynamic Tables when the graph is SQL and you want a lag target. Streams+Tasks when you need custom Task logic or an external orchestrator already owns the clock. Do not run both on the same table without a reason.

3. medium · Snowflake

   A task graph and Airflow both schedule the same MERGE. Failure mode?

   Two clocks, duplicate work, lock contention, and “who pages.” Pick one orchestrator. Snowflake Tasks are fine for SQL-only graphs; Airflow owns when humans and sensors exist.

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

Cloud (2)

1. hard · AWS

   How do you partition an S3 data lake so Athena does not scan the year?

   Hive-style dt=YYYY-MM-DD (and maybe hour) on the columns you filter. Do not partition on high-cardinality ids (customer_id). Compact tiny files (target ~128–512 MB). Clustering / sort inside the file helps, but partition pruning is the first win.

2. easy · AWS

   S3 Inventory vs listing 40 million keys in a Lambda. Which is the pipeline?

   Inventory (or S3 Batch) for discovery. LIST in a loop throttles and times out. Object creation events (SQS) for incremental ingest, not a nightly LIST.

System design (2)

1. hard · System design

   Backfill two years without starving the 8:00 SLA. How?

   Bounded date chunks, a smaller pool, catchup off, and idempotent partition replace. Unpausing catchup from 2019 is the stampede.

2. hard · System design

   Backfill 3 years after a grain change. How do you cut over without two revenues?

   Dual-run old and new marts, reconciling on a date range, then swap the BI pointer on a freeze window. Keep the old table read-only for a month. A silent rename on Friday is how finance pages you.

Data quality (4)

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.

2. medium · Data quality

   Fail the pipeline or open a ticket? Give a rule of thumb.

   Fail on grain/uniqueness/freshness that would ship a wrong number. Ticket (warn) on soft volume drift and new enum values you have not classified. If everything fails, nothing does — people --full-refresh and go home.

3. easy · Data quality

   A check is red every day for 40 days. What did you do wrong?

   You trained the org to ignore it. Fix the data, fix the threshold, or delete the check. On-call rotation should include “burn down forever-red tests.”

4. hard · Data quality

   Reconciliation: orders 1.02× payments. Is that a fail?

   Depends on the documented lag and unpaid orders. A 2% gap with a known pending state can be a ticket; a 2% gap on “completed_at yesterday” is a fail. Write the invariant in numbers, not “looks close.”

AI / ML (2)

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

   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 (4)

1. medium · Behavioral

   Tell me about a pipeline that failed in production. What did you do in the first hour?

   Name the SLO you missed (freshness, volume, or grain), how you found it (Datadog, row-count check, a Slack from BI), and the first safe move — stop writers, clone or time-travel, do not reload on a live table. Then the permanent fix: a test at that grain, an owner, and a page that is not “job succeeded.” Interviewers want the clock and the control, not a hero story.

   Hint: Lead with the SLO you missed, then the first safe move.

2. easy · Behavioral

   You and an analyst disagreed on the grain of revenue. How did it end?

   Write both grains on the board: order vs line vs payment. Pick one for the gold table and give the other a named mart. Do not average the disagreement into a DISTINCT. Close with who owns the metric definition now — dbt docs, a semantic layer, or finance. The interview is about conflict plus a contract, not who won.

3. medium · Behavioral

   Tell me about a time you shipped a dashboard that was wrong. What did you do in public?

   STAR: situation (wrong grain), task (stop the blast), action (revert pointer, post the invariant, add a unique test), result (hours of bad numbers, then a written owner). Do not skip who you told. Hiding it is the fail.

   Hint: Name the grain bug and the test you added.

4. easy · Behavioral

   A junior duplicated a mart instead of extending yours. What did you do besides being annoyed?

   Pair on the grain, add the exposure/test so the next person sees the survivor, and blamelessly document why two revenues existed. Mentorship is a process change, not a Slack dunk.

SQL coding labs

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

1. medium · about 12 min · tables: orders

   Running total of daily revenue

   Finance wants a cumulative revenue column beside the daily number so they can see the month build up. Aggregate to a day first, then run the window over that result — not over raw orders.

   https://www.datalane-data.blog/coding/problems/running-total-revenue/

2. medium · about 15 min · tables: customers, orders

   Customer lifetime summary

   Build the customer dimension the CRM team keeps asking for: order count, completed revenue, and first and last order timestamps. Customers with no orders must still appear, with zero revenue rather than NULL.

   https://www.datalane-data.blog/coding/problems/customer-lifetime-summary/

3. medium · about 12 min · tables: events

   Traffic source from JSON payload

   events.payload is raw JSON with source and campaign keys. Break activity down by source without unloading the column into a staging table first — this is the semi-structured question every warehouse screen has now.

   https://www.datalane-data.blog/coding/problems/event-source-breakdown/

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

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