Quick Overview

This question evaluates a candidate's competency in data warehousing and engineering for analytics, including event-level schema and partitioning, slowly changing dimensions, idempotent ingestion and deduplication, derived metrics modeling, and query performance and cost trade-offs using SQL/Python.

Design an analytic warehouse for event data

Company: Snowflake

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Onsite

Design a warehouse-ready analytics data model and ingestion plan to support cohort retention, ARPU, and product-case analyses at scale (50M events/day). Assume BigQuery or Snowflake. Sub-questions: 1) Schema: Propose fact and dimension tables (e.g., fact_events, fact_orders, dim_user, dim_country). Provide DDL-level details: partitioning (by event_date), clustering/sorting keys (user_id, event_type), and surrogate keys. Explain how you’d model slowly changing dimensions (Type 2) for user country and app version. 2) Idempotency & deduplication: Events can arrive late (up to 14 days) and out-of-order with occasional duplicates (same (user_id, event_ts, event_type)). Specify your dedupe key and merge/upsert strategy. How do you reprocess late data without double-counting cohorts? Include a backfill plan. 3) Metrics tables: Define a derived table grain for weekly retention by signup_week and ARPU by cohort. Show the SQL pattern (window PARTITION BY and date bucketing) you’d use and how materialization/incremental build works. Describe data quality checks (e.g., cohort_size non-increasing across week_index, retention ∈ [0,1]). 4) Performance: Estimate table sizes, choose file sizes/micro-partitions, and justify cluster keys for typical queries (top-N countries last 8 weeks, rolling DAU/WAU/MAU). Discuss cost controls (partition pruning, approximate distinct with HLL, result caching).

Overview: This question evaluates a candidate's competency in data warehousing and engineering for analytics, including event-level schema and partitioning, slowly changing dimensions, idempotent ingestion and deduplication, derived metrics modeling, and query performance and cost trade-offs using SQL/Python.

Read the full Snowflake Data Scientist interview experience this question came from

Build a Type 2 Slowly Changing User Dimension

## Build a Type 2 Slowly Changing User Dimension (SCD2) You are building a warehouse-ready analytics model for an event product. User attributes such as `country_code` and `app_version` change over time. A daily ETL job writes the latest-known attributes per user into a staging table **`user_snapshots`** (one row per user per snapshot date). Write a single PostgreSQL `SELECT` that turns these daily snapshots into a **Type 2 slowly changing dimension**: one output row per *version* of a user, where a new version starts whenever `country_code` and/or `app_version` differs from that user's previous snapshot. Consecutive snapshots with no attribute change must be collapsed into the same version. ### Input table: `user_snapshots` | column | type | notes | |---|---|---| | `snapshot_date` | DATE | the day this attribute set was observed | | `user_id` | INT | the user | | `signup_date` | DATE | constant per user | | `country_code` | VARCHAR(2) | tracked attribute | | `app_version` | VARCHAR(10) | tracked attribute | ### Required output One row per user-version, with exactly these columns: - `user_dim_id` — a surrogate key: a 1-based integer assigned by ordering all output rows by `user_id`, then `valid_from`. - `user_id` - `signup_date` - `country_code` - `app_version` - `valid_from` — the `snapshot_date` on which this version first appeared. - `valid_to` — **one day before** the `valid_from` of that user's next version (`valid_from_of_next_version - 1`). For the most recent version of each user, `valid_to` is `NULL`. - `is_current` — `TRUE` for the latest version of each user, otherwise `FALSE`. ### Sort order Return rows ordered by `user_id` ascending, then `valid_from` ascending.

Tables

user_snapshots(snapshot_date DATE, user_id INT, signup_date DATE, country_code VARCHAR(2), app_version VARCHAR(10))

Hints

  1. Use LAG over (PARTITION BY user_id ORDER BY snapshot_date) to compare each snapshot to the previous one, and keep only rows where country_code or app_version changed (plus each user's first snapshot).
  2. Use LEAD over the surviving change rows to find when the next version starts; valid_to is the day before that (in Postgres, subtract an interval or an integer — DATEADD is not valid here).

Idempotent Deduplication of Late and Duplicate Events

You ingest raw events into a staging table staging_events before merging into a large fact_events table. Events can: - Arrive late, up to 14 days after event_ts. - Arrive out-of-order by ingestion_ts. - Contain exact duplicates defined by the key (user_id, event_ts, event_type). For a daily batch with processing date '2025-05-31', you want to process events whose event_ts is in the range from '2025-05-17' inclusive (14 days before) up to but not including '2025-06-01'. For this batch, you need a deduplicated result set that: - Contains at most one row per (user_id, event_ts, event_type). - Keeps the row with the earliest ingestion_ts when duplicates exist. Using the staging_events table below, write a SQL query that returns the deduplicated events for this window, including a derived event_date column (DATE(event_ts)). The result will later be used as the source for an idempotent MERGE into fact_events. Render `event_ts` and `ingestion_ts` as `YYYY-MM-DD HH24:MI:SS`, and render `event_date` as `YYYY-MM-DD`.

Tables

staging_events(user_id INT, event_ts TIMESTAMP, event_type VARCHAR(50), event_value INT, ingestion_ts TIMESTAMP)

Hints

  1. Apply the processing-window filter before deduplication.
  2. Use `ROW_NUMBER` over `(user_id, event_ts, event_type)` ordered by `ingestion_ts` to keep the earliest ingested duplicate.

Weekly Cohort Retention and ARPU by Signup Week

You need to compute weekly cohort metrics to support retention and ARPU analysis at scale. A cohort is defined by signup_week = DATE_TRUNC('WEEK', signup_date). For each cohort and each week_index (0 = signup week, 1 = week after signup, etc.), you want: - cohort_size: number of distinct users in the cohort. - active_users: distinct users with at least one 'app_open' event in that week. - retention_rate = active_users / cohort_size. - arpu = total revenue from orders placed by cohort users in that week divided by cohort_size. Using the tables dim_user, fact_events, and fact_orders defined below, write a SQL query that produces a weekly cohort metrics table with columns: (signup_week, week_index, cohort_size, active_users, retention_rate, arpu). Assume: - Weeks are computed as DATE_TRUNC('WEEK', <date>) (PostgreSQL DATE_TRUNC('week', ...) is Monday-based). - week_index is computed as the number of whole 7-day week boundaries between signup_week and the event/order week, e.g. (week_start_date - signup_week) / 7 in PostgreSQL. Return only rows where at least one user in the cohort is active (active_users > 0).

Tables

dim_user(user_id INT, signup_date DATE, country_code VARCHAR(2))

fact_events(user_id INT, event_date DATE, event_type VARCHAR(50))

fact_orders(order_id INT, user_id INT, order_date DATE, revenue DECIMAL(10,2))

Hints

  1. First derive cohorts and cohort_size from dim_user using DATE_TRUNC('WEEK', signup_date).
  2. Join cohorts to events and orders, bucket both to weeks with DATE_TRUNC, compute week_index with DATEDIFF('WEEK', ...), then aggregate active_users and revenue and divide by cohort_size.

Rolling DAU, WAU, and MAU from Event Fact Table

Your fact_events table is partitioned by event_date and clustered by user_id and event_type to support analytics at scale (around 50M events/day). You want to compute daily active users and rolling usage metrics over a recent window. Define the metrics as: - DAU (daily active users): COUNT(DISTINCT user_id) per event_date. - WAU_7d: rolling 7-day sum of DAU (including the current date). - MAU_30d: rolling 30-day sum of DAU (including the current date). Using the fact_events table below, write a SQL query that returns, for each event_date between '2025-04-07' and '2025-06-01' inclusive where you have data, the columns: (event_date, dau, wau_7d, mau_30d). Assume this pattern would scale to the full partitioned fact_events table; focus on using window functions over a daily aggregate.

Tables

fact_events(user_id INT, event_date DATE, event_type VARCHAR(50))

Hints

  1. First aggregate fact_events to daily DAU with COUNT(DISTINCT user_id) grouped by event_date in the given range.
  2. Then use window SUM() over the ordered dates with ROWS BETWEEN N PRECEDING AND CURRENT ROW to compute rolling 7-day and 30-day sums of DAU.

Loading coding console...