Spotify data interview pack

The Spotify loop

1. SQL

   30-day rolling unique listeners without scanning 30 days of raw events every night.

2. Warehouse

   Partition filters, clustering, and a dashboard that ticks too often.

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

   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.

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

   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.

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

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

1. medium · BigQuery

   A query scanned 4 TB. Name three fixes before you buy more slots.

   Partition on the filter column (usually date). Cluster on high-frequency filters / join keys. Select only needed columns (columnar). Avoid SELECT *. A clustered table still scans the partition; it just reads fewer blocks inside it.

2. easy · BigQuery

   Slots vs a materialized table for a 5-minute dashboard.

   Materialize the aggregate (or an MV) and/or put repeat BI on capacity. Do not re-scan 4 TB on-demand every refresh.

3. medium · BigQuery

   You clustered on customer_id and filtered WHERE country = 'US'. Why did bytes scanned barely drop?

   Clustering helps when the filter matches the cluster columns (and the table is large enough). Country is not customer_id. Partition by date, cluster by the selective columns you actually filter.

4. medium · BigQuery

   Slots are reserved; a student query still billed on-demand. How?

   The job ran outside the reservation assignment (wrong project, admin project, or on-demand fallback). Draw the assignment: project → reservation → folder. “We bought slots” is not a policy.

System design (3)

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

3. hard · System design

   Multi-region: EU data cannot leave EU, US analysts want a global dashboard. Draw the legal design.

   Region-local bronze/silver, aggregates that are non-PII replicated to a global mart, or a viewer in-region. Do not COPY PII to us-east-1 “temporarily.” Privacy engineering is part of the sketch.

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/