Shopify data interview pack

The Shopify loop

1. dbt + SQL

   Refunds that arrive after the order incremental window. Lookback vs a payments fact.

SQL (2)

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

Data modeling (8)

1. easy · dbt

   What belongs in staging versus a mart?

   Staging is 1:1 with a source: rename, type, light clean. Marts are the business grain a dashboard or feature job can trust. Logic piled into staging is how projects rot. If two marts need the same join, that is an intermediate — not a copy-paste.

2. medium · dbt

   A model is ephemeral and 20 joins. Why is CI sad?

   The CTE is inlined into every downstream compile. Materialize it as a view/table or split it. Ephemeral is for tiny reusable logic.

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

4. medium · dbt

   state:modified+ in CI still rebuilt 80 models. First three checks?

   A changed macro/project YAML, a source that always looks new, and defer pointing at the wrong prod manifest. Then check whether you selected the whole tag by accident.

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

6. easy · Python & modeling

   ThreadPoolExecutor on a CPU-heavy pandas groupby.

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

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

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

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

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

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

1. easy · Kafka

   Compaction vs deletion retention. When do you use a compacted topic?

   Compacted topics keep the latest value per key (CDC changelog, config, ktables). Time retention drops old messages regardless of key (raw events). Using compaction on a clickstream deletes history you still need for sessionization.

2. medium · Kafka

   Tombstones — what do you produce for a CDC delete?

   A null value for that key on a compacted topic. Downstream must apply deletes. A random new key is not a delete.

3. medium · Kafka

   A compacted topic still has two values for one key. Why?

   Compaction is not instantaneous; duplicates exist until the cleaner runs. Compacted topics are a cache of latest, not a SQL unique constraint. Downstream still upserts.

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

5. easy · Kafka

   Kafka Connect vs a Flink job for the same JDBC sink. When is Connect enough?

   When the transform is light (rename, flatten) and you want offset management for free. Flink when you need event-time windows, exactly-once into Iceberg, or serious state. Do not write a Connect SMT that is actually a stream join.

Warehouse (11)

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

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

3. easy · BigQuery

   Streaming inserts vs load jobs. When is streaming the expensive habit?

   Streaming is for true low-latency rows and costs more. Nightly dumps should be load jobs (or Storage Write API in batch). Querying the streaming buffer has caveats. Do not stream a daily Salesforce extract.

4. medium · BigQuery

   Streaming a nightly dump. Why is that the expensive habit?

   Streaming is for low-latency rows. Nightly files are load jobs. You pay more and gain nothing.

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. hard · PostgreSQL

   CDC with Debezium: the slot is 400 GB behind. What happens if you drop it?

   You lose the WAL you have not consumed; a new slot starts from now (or a snapshot). Disk may recover. Never drop a slot to “free disk” without a re-snapshot plan. Also check a forgotten unused slot.

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.

11. easy · PostgreSQL

    Logical replication vs a nightly dump to the lake. When is dump still right?

    Small DBs, no delete capture needed, or you cannot get a slot. CDC is for ongoing deltas and deletes. A dump is a snapshot grain — say that in the mart.

System design (3)

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

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

3. hard · System design

   CDC from Postgres: you need deletes in the lake. What must the source have?

   Replica identity FULL (or unique key) so WAL carries the old row, a slot that does not fall behind, and a sink that applies deletes. Replica identity DEFAULT on a table without PK drops delete details.

Data quality (2)

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

1. medium · Behavioral

   A stakeholder wanted “just one more source” in the gold mart this sprint. How did you say no?

   Restate the grain and the SLA you already promised. One more source at a different grain is a fan-out, not a favor. Offer a dated spike or a separate mart. “We will add it and DISTINCT later” is the story they are waiting to fail. Good answers name the person you told and what shipped instead.

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.

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 12 min · tables: orders

   Latest order per customer

   Build a one-row-per-customer table holding their most recent order. Two orders can share a timestamp, so the tie-break has to be deterministic or your pipeline produces different rows on every run.

   https://www.datalane-data.blog/coding/problems/latest-order-per-customer/

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. easy · about 8 min · tables: order_items

   Orders with more than one line

   Multi-line orders are the ones that break naive revenue joins, so find them first. Return every order carrying more than one line item, with its line count and total quantity.

   https://www.datalane-data.blog/coding/problems/orders-with-multiple-items/

5. hard · about 18 min · tables: orders

   Second order within seven days

   Retention wants customers whose second completed order landed within 7 days of their first completed order. Return customer_id and the days between those two orders, smallest customer_id first.

   https://www.datalane-data.blog/coding/problems/repeat-within-7-days/

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/