Snowflake Interview Questions and Answers for Data Engineers (2026)

Snowflake separates cloud services, query processing, and database storage. The services layer handles parsing, optimisation, metadata, transactions, and security; query processing runs on virtual warehouses — independent MPP compute clusters; storage is compressed columnar micro-partitions in cloud object storage (S3, Azure Blob, or GCS). Because the layers are decoupled, compute and storage scale and bill independently, and multiple warehouses can read the same data without contention.

A virtual warehouse is a named cluster of compute nodes that executes queries and DML; sizes run XS to 6XL, with each step doubling the nodes and the credits per hour (XS burns 1 credit/hour). Resizing is near-instant because no data lives on the warehouse — new nodes simply start reading micro-partitions from the storage layer. Auto-suspend and auto-resume stop billing the moment a warehouse goes idle.

Micro-partitions are immutable columnar storage units holding roughly 50–500 MB of uncompressed data, created automatically on load — you never manage them directly. For every column in every micro-partition, Snowflake stores metadata such as min/max values, distinct counts, and null counts. The optimiser uses this metadata for partition pruning, scanning only micro-partitions that could contain matching rows.

Time Travel lets you query, clone, or restore past data using AT/BEFORE clauses and recover dropped objects with UNDROP. Retention is controlled by DATA_RETENTION_TIME_IN_DAYS: 1 day by default, up to 90 days for permanent tables on Enterprise edition and above. After Time Travel expires, data enters Fail-safe — 7 non-configurable days recoverable only via Snowflake support.

CREATE TABLE dev_orders CLONE prod.orders creates a metadata-only copy pointing at the same micro-partitions, so it completes in seconds with no extra storage cost at creation. Because micro-partitions are immutable, any later change writes new micro-partitions owned by the clone (copy-on-write), and only those deltas are billed. Teams use it to spin up full-size dev/test environments and instant pre-release backups.

Add a clustering key only on large multi-terabyte tables where queries filter or join on a column that does not match the natural load order — for example, event data loaded by time but queried by customer_id. Verify with SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTH, and confirm in the Query Profile that partitions scanned drops. Automatic clustering is a background serverless service that burns credits, so the pruning benefit must outweigh the recluster cost.

The result cache in the cloud services layer returns a previous result for 24 hours if the query text is identical and the underlying data unchanged — no warehouse needed. The warehouse (local disk) cache keeps recently read micro-partitions on the compute nodes' SSDs and is lost on suspend. The metadata cache answers queries like COUNT(*), MIN, and MAX directly from micro-partition statistics without resuming any compute.

Snowpipe is serverless continuous ingestion: a cloud event notification (for example S3 → SQS) or a REST call tells the pipe a new file landed, and Snowflake loads it on its own compute, billed per second plus a per-file overhead, with roughly minute-level latency. COPY INTO is a batch command running on your own warehouse and is cheaper for large scheduled loads. For seconds-level, row-level ingestion, Snowpipe Streaming writes rows over a channel API without staging files at all.

A stream is a CDC offset on a table exposing rows changed since the last consumption, with METADATA$ACTION and METADATA$ISUPDATE columns; consuming it inside a committed DML advances the offset. A task is scheduled SQL or a stored procedure (CRON or interval) chained with AFTER into a DAG. The standard pattern is a task guarded by WHEN SYSTEM$STREAM_HAS_DATA('my_stream') running a MERGE from the stream into the target — the warehouse never resumes when there is nothing to process.

Scale up to a bigger size when individual queries are slow or spilling — each size doubles memory and parallelism per query. Scale out with a multi-cluster warehouse (min/max cluster counts) when queries are fast individually but queue under concurrency, like a BI dashboard at 9 am. Auto-scale adds clusters on queueing and retires them when idle; Economy mode conserves credits while Standard mode starts clusters more eagerly.

Spilling means an operation — usually a large sort, join, or aggregation — exceeded warehouse memory, writing first to local SSD and then to remote object storage; remote spilling can slow a query by an order of magnitude. Fix it by running on a larger warehouse, filtering earlier with pruning-friendly predicates, eliminating exploding joins caused by duplicate keys, and splitting monster CTAS statements into stages.

Set resource monitors with credit quotas that notify and then suspend warehouses; keep auto-suspend aggressive (60 seconds) with auto-resume on, and right-size separate warehouses per workload instead of one shared giant. Track spend through ACCOUNT_USAGE views like WAREHOUSE_METERING_HISTORY and QUERY_HISTORY to find expensive queries. On the storage side, watch Time Travel and Fail-safe bloat on high-churn tables — staging tables should be transient so they skip Fail-safe entirely.

Every DML writes new micro-partitions instead of modifying files; a commit is an atomic metadata operation in the cloud services layer that swaps the table's file list to the new version. Readers work from a consistent snapshot of that metadata, so they never block and never see partial writes — this same versioning powers Time Travel and cloning. Concurrent writes to the same table coordinate through partition-level locking, so conflicting DML queues rather than corrupting state, with READ COMMITTED isolation.

Because micro-partitions are immutable, updating one row rewrites the entire micro-partition containing it — a single-row update can rewrite hundreds of megabytes, and the old version lingers in Time Travel storage. OLTP-style trickle updates therefore inflate both compute and storage. The better pattern is batching changes and applying one MERGE per interval, capturing deltas with streams, and keeping churn-heavy staging data in transient tables with minimal retention.

Use the Snowflake Kafka connector with Snowpipe Streaming to land rows in a raw table with seconds-level latency and no file staging. Put a stream on the raw table and either a task running MERGE into the curated layer, or declare curated tables as dynamic tables with TARGET_LAG = '1 minute' so Snowflake manages incremental refresh. Keep the raw table transient, monitor lag via SYSTEM$STREAM_HAS_DATA and connector offsets, and dedicate a warehouse to transformation so BI concurrency never starves ingestion.

Permanent tables get up to 90 days of Time Travel on Enterprise plus 7 days of Fail-safe; transient tables cap Time Travel at 1 day and skip Fail-safe; temporary tables live only for the session and also skip Fail-safe. On high-churn tables the difference is dramatic: every rewritten micro-partition is retained for the full Time Travel plus Fail-safe window, so a churny permanent staging table can bill several times its live size. Standard design: transient for staging and intermediate layers, permanent only where point-in-time recovery genuinely matters.