Snowflake
Interview Prep

Snowflake's multi-cluster shared data architecture stores data in cloud object storage (S3/GCS/Azure Blob) independently from compute. Virtual warehouses (compute clusters) can scale independently and can be spun up/down in seconds without moving data. This enables multiple workloads (reporting, ETL, ML) to run concurrently without resource contention, and customers pay only for compute they use.

Walk through the business requirements, the modeling approach you chose (dimensional modeling/star schema, data vault, OBT), the trade-offs you made (normalization vs. query performance, incremental vs. full refresh), the grain of each table, and how the model performed in production. Discuss how the model evolved as requirements changed.

Walk through the original query or pipeline, how you diagnosed the bottleneck (query profile, explain plan, stage durations, spill to disk), the optimization you applied (query restructuring, clustering key adjustment, materialized view, warehouse sizing, result caching), and the before/after performance improvement. Quantify the improvement.

Snowflake hires heavily for customer-facing roles (solutions engineering, customer success, professional services). Describe the customer's data challenge, how you diagnosed it through discovery, what solution you designed, how you communicated it in terms the customer understood, and how you measured success. Show technical depth and communication clarity simultaneously.

Structure: 1) Assessment (inventory all schemas, objects, data volumes, job schedules, user communities), 2) SQL compatibility analysis (Snowflake SQL vs. Teradata SQL differences — DATE functions, string handling, analytical functions), 3) Data migration approach (full historical load via external stage, then incremental sync), 4) ETL/ELT refactoring (push compute into Snowflake rather than pre-transforming), 5) User acceptance testing, 6) Cutover strategy. Estimate timeline by scale.

Walk through your governance model: role-based access control (RBAC hierarchy in Snowflake: system roles → custom roles → functional roles), column-level security (masking policies for PII), row-level security (row access policies), dynamic data masking for different user groups, and data sharing (secure views vs. Snowflake Data Sharing for cross-account access). Address the governance process: who can grant what, how access requests are managed.

Walk through: data source and ingestion method (Kafka + Snowpipe, direct Snowpipe, connector), latency requirements and how close you got, the transformation approach (stream + task in Snowflake, or external orchestration), monitoring and alerting for pipeline health, and failure recovery strategy. Address the trade-off between micro-batch and true streaming.

Walk through the cost levers: warehouse sizing (match warehouse to workload — large warehouses for complex queries, XS for simple dashboards), auto-suspend and auto-resume settings, query result cache utilization (avoid repeated query costs), clustering keys (reduce partition scanning for common filter patterns), storage (compression, lifecycle policies for older data), and credits vs. savings plans. Identify the top 20% of expensive queries/warehouses driving 80% of costs.