Snowflake System Design Interview Questions

Capture changes (e.g., Debezium) → land to stage → ingest into a raw CDC table with Streams → merge into modeled tables via Tasks. Use variant columns or staged ALTERs to absorb new columns, then promote to typed fields.

Bronze stores raw, immutable data; Silver applies cleaning and conformance; Gold serves analytics-ready marts. Implement transitions with Streams/Tasks or Dynamic Tables for managed incremental refresh.

Use Snowpipe Streaming into Bronze, maintain Dynamic Tables or incremental MERGE into Silver/Gold, and power dashboards from isolated reader warehouses. Track end-to-end freshness with task lag and query tags.

Snowflake stores data in micro-partitions with rich metadata (ranges, stats). Queries prune partitions using that metadata and clustering, so good filters and clustering keys reduce scanned data and cost.

Define clustering keys for very large, frequently filtered tables (e.g., by date, customer_id). Enable Automatic Clustering when DML is steady and you want Snowflake to maintain clustering in the background without manual maintenance.

Time Travel lets you query/restore historical data for a configurable window; it incurs storage for kept versions. Fail-safe is a last-resort recovery after Time Travel expires and is not meant for routine restores—plan retention to balance recovery needs and storage spend.

Zero-copy cloning instantly creates a clone of tables/schemas/databases using copy-on-write. Use it for testing, backfills, or point-in-time snapshots without duplicating data up front; storage grows only for new/changed data.

Right-size for query profile and concurrency: start small, scale up if CPU-bound scans dominate, or scale out via more clusters if queueing dominates. Validate with Query Profile and credit/latency trade-offs.

Enable multi-cluster for bursty BI or many short queries. Snowflake adds clusters to reduce queuing and removes them when load falls; cap min/max clusters to control cost.

Use Resource Monitors to set credit thresholds and alerts; tune statement queues (warehouse size, max concurrency) to avoid thrashing. Combine with query acceleration features only where they pay off.

Provision separate warehouses per workload/team to protect SLAs and budgets. Pin ELT jobs to one warehouse, BI to another, and give ad hoc users a small, auto-suspend warehouse.

Choose Dynamic Tables to materialize transformations on a schedule with automatic incremental maintenance. Choose Materialized Views to accelerate a specific query’s result. Use DTs upstream of MVs when you need both.

Create Streams on source tables and Tasks that run incremental merges/updates in order (with dependencies). Add a “catch-up” task for backfills and use task history to monitor freshness and failures.

Declare end-to-end freshness (e.g., “<10 min lag”), monitor task lag and last-modified timestamps, and backfill with Time Travel + idempotent MERGEs. Document acceptable catch-up windows and cost thresholds.

Model by event_time, store a load_time, and run periodic reconciliation MERGEs using Streams or Time Travel. Use dedup keys (source_id + event_time) to avoid double counting.

Pick Iceberg tables when you need open-format interoperability with external engines or lakehouse lakes. Use native Snowflake tables when you want pure Snowflake features and the simplest ops model.

External/iceberg tables lower storage cost and improve openness, but may trade some performance/features. Native tables maximize pruning, services, and simplicity at the cost of full Snowflake storage pricing.

Snowpark lets you write data pipelines and ML prep in Python/Java/Scala that execute inside Snowflake, minimizing data egress. Use it for feature engineering, UDF pipelines, and secure in-place transforms.

Stage raw to Bronze, compute features with Snowpark into a governed feature table, enforce point-in-time correctness, and refresh incrementally via Streams/Tasks or Dynamic Tables. Version schemas and log lineage.

Ask for data volumes, arrival cadence, freshness targets, BI concurrency, acceptable p95 latency, retention policies, and monthly credit budget. Restate numbers, tie them to warehouse sizing, clustering, and modeling choices, and propose degradation/backfill plans within those limits.