Stripe data interview pack

The Stripe loop

1. SQL screen

   Dedup to latest customer, anti-joins that survive NULL, and a revenue query that does not fan out on a payments table.

2. Pipeline design

   Exactly-once vs at-least-once into a warehouse. They want the sink story, not Kafka slogans.

3. Debugging

   A dashboard doubled overnight. Walk the grain from gold to source in five minutes.

SQL (4)

1. hard · SQL

   Deduplicate to the latest customer row. Why not filter the window in WHERE?

   Use row_number() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) in a CTE, then keep rn = 1. Window functions run after WHERE, so a filter on rn in the same SELECT is invalid (unless the dialect has QUALIFY). Interviewers want that evaluation order, not a clever one-liner.

2. easy · SQL

   EXISTS vs IN for an anti-join. Which do you write first?

   NOT EXISTS (or LEFT JOIN … IS NULL). NOT IN dies on NULLs. That is the answer they want.

3. medium · SQL

   You need “customers who ordered in January and never in February.” Write the shape, not the dialect.

   January set MINUS February set, or EXISTS January AND NOT EXISTS February. A LEFT JOIN to February WHERE feb.key IS NULL is the anti-join they want. IN (SELECT …) dies if February keys contain NULL.

   Hint: Anti-join, not a CASE inside a COUNT.

4. medium · SQL

   LAG(amount, 1) IGNORE NULLS vs the default. Give a revenue example.

   Default LAG sees NULL as a value, so a missing day poisons the previous-day comparison. IGNORE NULLS (where supported) or a date spine plus COALESCE. Say what “previous” means: previous row or previous calendar day.

Data modeling (8)

1. medium · dbt

   ref() vs source() — why does the distinction matter in an incident?

   source() is the raw relation you do not own; ref() is a model in the project DAG. Breaking a source contract is a producer problem. Breaking a ref is your DAG. Tests on sources catch upstream silence; tests on marts catch your grain. Mixing them hides who pages whom.

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

3. medium · Python & modeling

   What is the grain of a table, and why do they ask?

   What one row represents. Mismatched grains fan out joins and invent revenue. Write the grain in the model name (fct_order_item, not fct_orders if the row is a line). If you cannot say the grain, you are not ready to write the SQL.

4. easy · Python & modeling

   ThreadPoolExecutor on a CPU-heavy pandas groupby.

   The GIL will not give you 8×. Use processes, Polars, or a warehouse.

5. medium · Python & modeling

   A webhook POST retried and created two customers.

   Non-idempotent create. Use an idempotency key or an upsert on a natural key.

6. easy · Python & modeling

   A pipeline uses df.iterrows() to call an API per row. What do you change before adding threads?

   Batch the API (or SQL IN lists), then maybe a pool. iterrows is the slowest pandas pattern. Profile: is the time pandas or the network? Fix the algorithm first.

7. medium · Python & modeling

   Type 2 SCD in Python vs in SQL/dbt. When is Python acceptable?

   Tiny dimensions, one-off migrations, or when the source is an API. Warehouse SQL is the default for 50M-row facts. A pandas SCD2 that loads the whole history into RAM is a ticket waiting to happen.

8. medium · Python & modeling

   Hypothesis: “nulls in float columns become NaN and broke unique tests.” How do you talk about this in a DE interview?

   pandas NaN is not SQL NULL; uniqueness and groupby dropna defaults surprise people. Prefer nullable dtypes or stay in SQL for grain tests. Show a failing test, not a blog rant.

Orchestration (6)

1. medium · Airflow

   deferrable sensors vs poking every 30s. What did you free?

   Worker slots. A poking sensor holds a slot for the whole wait. Deferrable releases the worker and waits in the triggerer. Still need a timeout and an SLA miss callback.

2. easy · Airflow

   catchup=False forever. What history did you silently skip?

   Every interval between start_date and now when the DAG was paused or deployed late. That is fine for “from now on” jobs; it is a hole for daily finance. Say it in the design review.

3. medium · Dagster

   What is a software-defined asset, in one sentence?

   A table (or file, or report) you declare with its dependencies; the scheduler materializes it. You are not scheduling a Python function that happens to write a table — you are scheduling the table.

4. medium · Dagster

   Where do warehouse credentials live?

   In a resource / I/O manager, not in the op. Same rule as Airflow connections. If the PR has a password, it fails the screen.

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

6. easy · Dagster

   Resources and IO managers: where do warehouse credentials live?

   In a Dagster resource / secret backend, not in the asset body. The IO manager writes parquet/Snowflake; the asset function should not open a raw connection with a hardcoded URL.

Streaming (4)

1. hard · Kafka

   at-least-once vs exactly-once. What do you actually promise a downstream table?

   Most pipelines are at-least-once plus an idempotent sink (MERGE on event_id, or Kafka transactions into a compacted topic). Exactly-once is a property of a specific producer–broker–consumer setup, not a checkbox on the resume. Say what happens on a retry.

2. medium · Kafka

   Schema registry compatibility in CI.

   A producer adding a required field will kill old consumers. Fail the PR on a BACKWARD/FORWARD break you did not mean.

3. medium · Kafka

   You need to fan a CDC stream to Snowflake and to a search index. Two consumers or one consumer that writes both?

   Two consumer groups (or Connect sink connectors). One consumer dual-writing is a partial-failure nightmare. Each sink owns its offsets and DLQ.

4. easy · Apache Flink

   Exactly-once into a warehouse. What do you still need?

   A transactional or idempotent sink and a business key. Checkpoint barriers without a sink contract are at-least-once in practice. Dual-write without MERGE is a duplicate table.

Warehouse (10)

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. medium · Snowflake

   Zero-copy clone of prod for a destructive test. What do you warn finance?

   Clone is cheap at t0. Storage grows as either side writes. Time Travel on the clone is not a backup of prod. Drop the clone when the test ends.

3. easy · Snowflake

   DIRECTORY tables and a stage full of JSON. Why is SELECT $1:id from the stage a bad gold pattern?

   Stage queries are ingest, not a mart. COPY or Snowpipe into VARIANT bronze, then typed silver. BI on a stage has no Time Travel you own and no tests.

4. hard · Databricks

   Auto Loader vs a COPY into a table every hour. When is streaming the simpler design?

   Auto Loader (cloudFiles) tracks what was ingested with a checkpoint — good for landing buckets that grow all day. A scheduled COPY is fine for a nightly dump. The fail is “streaming” a file drop that arrives once a day, or batching a firehose with no watermark.

5. easy · Trino

   Why did we take down prod Postgres?

   A federated connector without pushdown and a dashboard SELECT *. Point Trino at a replica, or do not point it at OLTP. This is the classic Trino incident.

6. medium · ClickHouse

   Is ClickHouse a source of truth?

   No. Keep Postgres (or similar) for transactions. ClickHouse is the analytic replica. Treating it as OLTP is the wrong interview answer.

7. easy · ClickHouse

   Can you UPDATE like Postgres? What do you tell an analyst?

   Mutations exist but are expensive background jobs, not OLTP. Model append + collapsing, or rebuild a partition. Treat CH as analytic, not a source of truth for balances.

8. medium · PostgreSQL

   How should CDC read Postgres?

   Logical decoding / a publication (Debezium or native), not polling updated_at. Monitor slot lag and disk. A slot nobody consumes is how the primary fills the disk.

9. medium · PostgreSQL

   VACUUM did not shrink the file. Why is the table still 10× live rows?

   Dead tuples need VACUUM; file shrink needs VACUUM FULL or a rewrite (and a lock). Bloat from updates without fillfactor/autovacuum tuning. Measure n_dead_tup before buying disk.

10. medium · PostgreSQL

    UNIQUE NULLS NOT DISTINCT vs the default. Why did a second NULL insert succeed?

    Default UNIQUE allows multiple NULLs because NULL ≠ NULL. If “one empty email” is the rule, use NULLS NOT DISTINCT (PG 15+) or a sentinel. This bites MERGE/CDC keys too.

Cloud (1)

1. medium · AWS

   CDC from RDS to the lake. Sketch the path.

   DMS or Debezium to Kinesis/MSK, then MERGE into Iceberg/Delta/Redshift. A nightly dump is not CDC.

System design (5)

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

   Gold is a running-balance column you UPDATE. A webhook retries. What shipped?

   Two mutations, one economic event. Model immutable ledger entries and sum them. Idempotency on (payment_id, event_id), not an overwrite.

2. medium · System design

   Design an orders platform for 40 analysts at 9:00 and a 15-minute CDC lag. Sketch the layers.

   OLTP CDC → raw/bronze files → typed silver (MERGE on order_id) → gold at one grain (order or line — pick one) → a serving warehouse or marts for BI. Orchestrate freshness; do not transform 2 TB in the orchestrator. Page on freshness and volume, not only “job succeeded.”

3. easy · System design

   Sketch CDC from Postgres to Iceberg with a 5-minute SLA.

   Debezium/DMS → Kafka/MSK → sink that MERGEs into Iceberg on the primary key, watermark on LSN/time, dead-letter for poison rows. A nightly dump is not this design.

4. hard · System design

   Payments ledger: webhook retries, partial refunds, and a warehouse. What is the grain of gold?

   One row per ledger event (or immutable entry), not a mutable balance column. Balance is a sum. Idempotency on (payment_id, event_id). Refunds are new events, not UPDATEs of the capture.

5. 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 (5)

1. medium · Data quality

   Fail the pipeline or open a ticket?

   Fail on grain, uniqueness, and PII contract breaks. Ticket on a soft anomaly you cannot yet explain. Write the policy down. “It depends” without a rule is a fail.

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. medium · Data quality

   Where should the contract live — producer app or warehouse?

   Producer first (schema registry, API contract, protobuf). Warehouse tests are defense in depth. If only the warehouse tests, you find out after the app shipped. Both, with an owner on the producer.

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

5. easy · Data quality

   Unit test fixtures are all happy paths. What production bug will you miss?

   Duplicate keys, late CDC, NULL in the join key, and timezone edges. Fixtures should include the incident you had last quarter. A unique test on real data still has to exist.

AI / ML (1)

1. easy · AI / RAG / MLOps

   LLM in a batch pipeline: four patterns that survive production.

   Classify, extract structured fields, summarize into a stored column, route (ticket → queue). Always: schema, retries, cost cap, and a deterministic fallback. Never call a chat API from a BI dashboard on every refresh.

Behavioral (5)

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. easy · Behavioral

   How do you raise the bar for a junior who ships untested marts?

   Pair on the first unique/not_null tests, require them on every gold PR, and show a past fan-out. Do not take the keyboard forever. Interviewers want coaching plus a gate, not “I rewrote it Friday night.”

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

5. 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 10 min · tables: orders

   Completed revenue by country

   Finance wants revenue by country, but only for orders that actually completed. Cancelled orders must not contribute a cent. Return one row per country with revenue rounded to 2 decimals, biggest country first.

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

2. medium · about 10 min · tables: orders, payments

   Orders with no payment

   Every order should have a payment row. Find the ones that do not. This is the anti-join interviewers use to see whether you reach for NOT IN, which silently returns nothing when the subquery contains a NULL.

   https://www.datalane-data.blog/coding/problems/orders-without-payment/

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

4. medium · about 12 min · tables: payments

   Payment method mix by day

   Turn payment methods from rows into columns so each day is a single row with card, ach, and wallet totals. Warehouses have a PIVOT clause; plain SQL does it with conditional aggregation.

   https://www.datalane-data.blog/coding/problems/payment-method-mix/

5. hard · about 14 min · tables: orders, payments

   Reconcile orders against payments

   A revenue report and a settlement report disagree. Find the orders where the paid amount does not match the order amount — this is the reconciliation check that belongs in a data quality test, not a dashboard.

   https://www.datalane-data.blog/coding/problems/orders-vs-payments-mismatch/

6. hard · about 16 min · tables: orders

   Customers who cancelled and completed

   Risk wants customers who both cancelled at least one order and completed at least one order. Return customer_id, cancelled_orders, and completed_orders, smallest customer_id first.

   https://www.datalane-data.blog/coding/problems/cancelled-and-completed/

7. hard · about 18 min · tables: orders, payments

   Hours from order to settlement

   Treasury wants average hours between ordered_at and paid_at, by payment method. One payment row per paid order in this warehouse. Return method and avg_hours rounded to 2 decimals, slowest method first.

   https://www.datalane-data.blog/coding/problems/payment-settlement-lag/