Coding questions · Snowflake
Design an analytic warehouse for event data
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 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:
- 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.
- 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.
- 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]).
- 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).
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 byuser_id, thenvalid_from.user_idsignup_datecountry_codeapp_versionvalid_from— thesnapshot_dateon which this version first appeared.valid_to— one day before thevalid_fromof that user's next version (valid_from_of_next_version - 1). For the most recent version of each user,valid_toisNULL.is_current—TRUEfor the latest version of each user, otherwiseFALSE.
Sort order
Return rows ordered by user_id ascending, then valid_from ascending.
Table user_snapshots
| column | type | constraints |
|---|---|---|
| snapshot_date | DATE | NOT NULL |
| user_id | INT | NOT NULL |
| signup_date | DATE | NOT NULL |
| country_code | VARCHAR(2) | NOT NULL |
| app_version | VARCHAR(10) | NOT NULL |
Sample data:
| app_version | country_code | signup_date | snapshot_date | user_id |
|---|---|---|---|---|
| 1.0 | US | 2025-04-01 | 2025-04-01 | 1 |
| 1.0 | CA | 2025-04-01 | 2025-05-10 | 1 |
| 1.1 | CA | 2025-04-01 | 2025-05-20 | 1 |
| 1.0 | GB | 2025-04-15 | 2025-04-15 | 2 |
| 1.0 | US | 2025-05-05 | 2025-05-05 | 3 |
| 1.2 | US | 2025-05-05 | 2025-05-25 | 3 |
Expected output
One row per user-version: attributes constant over [valid_from, valid_to]; valid_to = day before the next version's valid_from (NULL for the current version); is_current TRUE only for the latest version; user_dim_id is a surrogate key ordered by (user_id, valid_from).
| app_version | country_code | is_current | signup_date | user_dim_id | user_id | valid_from | valid_to |
|---|---|---|---|---|---|---|---|
| 1.0 | US | false | 2025-04-01 | 1 | 1 | 2025-04-01 | 2025-05-09 |
| 1.0 | CA | false | 2025-04-01 | 2 | 1 | 2025-05-10 | 2025-05-19 |
| 1.1 | CA | true | 2025-04-01 | 3 | 1 | 2025-05-20 | NULL |
| 1.0 | GB | true | 2025-04-15 | 4 | 2 | 2025-04-15 | NULL |
| 1.0 | US | false | 2025-05-05 | 5 | 3 | 2025-05-05 | 2025-05-24 |
| 1.2 | US | true | 2025-05-05 | 6 | 3 | 2025-05-25 | NULL |
Hints
- 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).
- 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).
- is_current is TRUE exactly when LEAD returns NULL; assign user_dim_id with ROW_NUMBER() OVER (ORDER BY user_id, valid_from).
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.
Table staging_events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_ts | TIMESTAMP | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
| event_value | INT | |
| ingestion_ts | TIMESTAMP | NOT NULL |
Sample data:
| event_ts | event_type | event_value | ingestion_ts | user_id |
|---|---|---|---|---|
| 2025-05-20 10:00:00 | app_open | 1 | 2025-05-20 10:01:00 | 1 |
| 2025-05-20 10:00:00 | app_open | 1 | 2025-05-20 10:02:00 | 1 |
| 2025-05-21 09:00:00 | purchase | 50 | 2025-05-21 09:05:00 | 1 |
| 2025-05-18 12:00:00 | app_open | 1 | 2025-05-20 11:00:00 | 2 |
| 2025-05-18 12:00:00 | app_open | 1 | 2025-05-19 10:00:00 | 2 |
| 2025-05-30 15:30:00 | app_open | 1 | 2025-05-30 15:35:00 | 3 |
Expected output
Deduplicated events for the 2025-05-17 to 2025-05-31 window, with one record per (user_id, event_ts, event_type), keeping the earliest ingestion_ts and adding an event_date column.
| event_date | event_ts | event_type | event_value | ingestion_ts | user_id |
|---|---|---|---|---|---|
| 2025-05-20 | 2025-05-20 10:00:00 | app_open | 1 | 2025-05-20 10:01:00 | 1 |
| 2025-05-21 | 2025-05-21 09:00:00 | purchase | 50 | 2025-05-21 09:05:00 | 1 |
| 2025-05-18 | 2025-05-18 12:00:00 | app_open | 1 | 2025-05-19 10:00:00 | 2 |
| 2025-05-30 | 2025-05-30 15:30:00 | app_open | 1 | 2025-05-30 15:35:00 | 3 |
Hints
- Apply the processing-window filter before deduplication.
- Use
ROW_NUMBERover(user_id, event_ts, event_type)ordered byingestion_tsto keep the earliest ingested duplicate. - Use
TO_CHARto match the expected timestamp and date display formats in PostgreSQL.
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).
Table dim_user
| column | type | constraints |
|---|---|---|
| user_id | INT | PRIMARY KEY, NOT NULL |
| signup_date | DATE | NOT NULL |
| country_code | VARCHAR(2) | NOT NULL |
Sample data:
| country_code | signup_date | user_id |
|---|---|---|
| US | 2025-03-31 | 1 |
| US | 2025-04-02 | 2 |
| CA | 2025-04-08 | 3 |
| CA | 2025-04-09 | 4 |
Table fact_events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
Sample data:
| event_date | event_type | user_id |
|---|---|---|
| 2025-03-31 | app_open | 1 |
| 2025-04-08 | app_open | 1 |
| 2025-04-15 | app_open | 1 |
| 2025-03-31 | app_open | 2 |
| 2025-04-10 | app_open | 2 |
| 2025-04-08 | app_open | 3 |
| 2025-04-15 | app_open | 3 |
| 2025-04-09 | app_open | 4 |
| 2025-04-16 | app_open | 4 |
Table fact_orders
| column | type | constraints |
|---|---|---|
| order_id | INT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| order_date | DATE | NOT NULL |
| revenue | DECIMAL(10,2) | NOT NULL |
Sample data:
| order_date | order_id | revenue | user_id |
|---|---|---|---|
| 2025-03-31 | 101 | 10 | 1 |
| 2025-04-15 | 102 | 5 | 1 |
| 2025-04-01 | 103 | 20 | 2 |
| 2025-04-08 | 104 | 7 | 3 |
| 2025-04-15 | 105 | 3 | 3 |
Expected output
Weekly cohort metrics by signup_week and week_index, including cohort_size, active_users, retention_rate, and ARPU. Only weeks where at least one user from the cohort is active are included.
| active_users | arpu | cohort_size | retention_rate | signup_week | week_index |
|---|---|---|---|---|---|
| 2 | 15 | 2 | 1 | 2025-03-31 | 0 |
| 2 | 0 | 2 | 1 | 2025-03-31 | 1 |
| 1 | 2.5 | 2 | 0.5 | 2025-03-31 | 2 |
| 2 | 3.5 | 2 | 1 | 2025-04-07 | 0 |
| 2 | 1.5 | 2 | 1 | 2025-04-07 | 1 |
Hints
- First derive cohorts and cohort_size from dim_user using DATE_TRUNC('WEEK', signup_date).
- 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.
Table fact_events
| column | type | constraints |
|---|---|---|
| user_id | INT | NOT NULL |
| event_date | DATE | NOT NULL |
| event_type | VARCHAR(50) | NOT NULL |
Sample data:
| event_date | event_type | user_id |
|---|---|---|
| 2025-03-31 | app_open | 1 |
| 2025-03-31 | app_open | 2 |
| 2025-04-08 | app_open | 1 |
| 2025-04-08 | app_open | 3 |
| 2025-04-09 | app_open | 4 |
| 2025-04-10 | app_open | 2 |
| 2025-04-15 | app_open | 1 |
| 2025-04-15 | app_open | 3 |
| 2025-04-16 | app_open | 4 |
Expected output
For each event_date in the range with data (here 2025-04-08, 2025-04-09, 2025-04-10, 2025-04-15, 2025-04-16), the query returns DAU and rolling 7-day and 30-day sums of DAU, ordered by date.
| dau | event_date | mau_30d | wau_7d |
|---|---|---|---|
| 2 | 2025-04-08 | 2 | 2 |
| 1 | 2025-04-09 | 3 | 3 |
| 1 | 2025-04-10 | 4 | 4 |
| 2 | 2025-04-15 | 6 | 6 |
| 1 | 2025-04-16 | 7 | 7 |
Hints
- First aggregate fact_events to daily DAU with COUNT(DISTINCT user_id) grouped by event_date in the given range.
- 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.