← Snowflake Interview Insights
I knew Type 2 SCDs conceptually but fumbled when they asked me to actually write the DDL on the spot.
Start by clarifying the business processes and grain of the fact table, then design a star schema with a central fact table and dimensions. For the fact table, propose partitioning by date and clustering by high-cardinality columns like user_id or event_type to optimize query performance. For slowly changing dimensions, use Type 2 for user country and app version to track historical changes, and explain how surrogate keys and effective dates enable point-in-time analysis.
Pro tip: Mention that in Snowflake, clustering keys are not like traditional indexes and incur costs, so choose them based on common query filters and consider using automatic clustering for large tables. Also, highlight that Type 2 SCDs can be implemented efficiently using streams and tasks for change data capture.
Ask about the specific events, dimensions, and queries to determine the fact table grain (e.g., one row per event) and identify key dimensions like user, app, and time.
Propose a fact table with surrogate keys for dimensions, degenerate dimensions (e.g., event_id), and measures. Specify partitioning by date (e.g., event_date) and clustering by columns like user_id or event_type for query performance.
Create dimension tables for user, app, and date. For user and app dimensions, include surrogate keys, natural keys, and attributes. For SCDs, add effective_start_date, effective_end_date, and is_current flags.
For user country and app version, implement Type 2 SCDs to track historical changes. Explain how surrogate keys are generated and how to join fact tables to dimensions for point-in-time analysis.
Address trade-offs between Type 1 and Type 2 SCDs, and mention Snowflake features like clustering keys, automatic clustering, and streams/tasks for SCD management.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
This is where I spent most of my mental energy.
Start by defining a deterministic deduplication key that includes user_id, event_ts, and event_type, then propose an idempotent upsert strategy using MERGE or INSERT ON CONFLICT. Explain how to handle late data by reprocessing affected partitions and using a deduplicated source of truth to avoid double-counting cohorts. Finally, outline a backfill plan that includes validation and monitoring.
Pro tip: Emphasize that deduplication should happen at the earliest possible stage (e.g., in the ingestion pipeline) to minimize downstream complexity and cost. Also, mention that using a combination of event_ts and ingestion_ts can help distinguish between late-arriving data and duplicates.
Use a composite key of (user_id, event_ts, event_type) as the primary deduplication key. Optionally, include an event_id if available, but fall back to the composite key when not.
Implement an idempotent upsert using MERGE (Snowflake) or INSERT ON CONFLICT (other databases) to ensure that duplicate events do not create multiple rows. Use a staging table to deduplicate before merging into the target.
Identify affected partitions based on event_ts and reprocess them. Use a deduplicated source (e.g., a materialized view or a deduped table) to recompute cohorts, ensuring no double-counting.
Backfill by reprocessing historical data in batches, applying the same deduplication and upsert logic. Validate results by comparing counts before and after, and monitor for anomalies.
Set up monitoring for duplicate rates, late-arriving data volumes, and cohort consistency. Use data quality checks to ensure deduplication and backfill are effective.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
SQL window functions I can do in my sleep, so PARTITION BY signup_week ORDER BY week_index for retention felt fine.
Start by clarifying the business definitions of retention and ARPU, then define the grain as one row per signup cohort per week (or per cohort per period). Walk through the SQL pattern using window functions and aggregations, explain how incremental materialization updates only new cohorts/weeks, and finish with concrete data quality checks.
Pro tip: Emphasize that the grain must support both metrics without double-counting: retention is cohort-week level, while ARPU is cohort-level, so you may need two tables or a carefully designed grain. Mention that at Snowflake, you'd leverage streams and tasks for incremental processing.
Define retention (e.g., active in week N after signup) and ARPU (revenue per user in cohort). Specify grain as one row per signup cohort per week for retention, and one row per signup cohort for ARPU, or a combined grain if needed.
Write a query that joins signups with activity and revenue, groups by cohort and week, and computes retention and ARPU using conditional aggregation and window functions.
Describe how to use Snowflake streams and tasks to capture new signups and activity, and merge only new or updated cohorts/weeks into the target table, avoiding full refreshes.
List checks such as uniqueness of cohort-week, completeness of cohorts, validity of retention rates (0-100%), and reconciliation of ARPU with source revenue.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.
Rough math: 50M events at maybe 500 bytes each uncompressed, compressed down to maybe 100 bytes, so around 5GB per day, 40GB for an 8-week window.
Start by estimating raw data volume and compressed storage for 50M daily events, then map typical query patterns to clustering keys that minimize scanned data. Walk through cost controls like partition pruning, approximate distinct counts, and result caching, quantifying savings where possible.
Pro tip: Always tie clustering choices to specific query patterns and quantify the impact on scan size and cost—this shows you understand Snowflake's architecture and cost model, not just generic data modeling.
Calculate raw event size (e.g., 1KB/event) and apply Snowflake's typical 3-5x compression to get storage per day, then extrapolate for retention periods (e.g., 8 weeks, 1 year).
Identify frequent queries: top-N countries over last 8 weeks (filter on date and country) and rolling DAU/WAU/MAU (filter on date, aggregate distinct users).
For top-N countries, cluster by (event_date, country) to prune on date and co-locate country data; for DAU/WAU/MAU, cluster by (event_date) or (event_date, user_id) to optimize date-range scans and distinct counts.
Use partition pruning via clustering, approximate distinct counts (HLL) for DAU/WAU/MAU, and result caching for repeated queries; also consider materialized views for pre-aggregated metrics.
Monitor query profiles and clustering depth, adjust keys as query patterns evolve, and measure cost savings from pruning, approximations, and caching.
AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.