Snowflake Interview Questions

Snowflake micro-partitions are immutable storage units. Each micro-partition contains rows from a single table, and any update or delete creates new micro-partitions rather than modifying existing ones in place.

Auto-suspend causes a warehouse to transition to a suspended state after the configured period of inactivity (default 60 seconds), at which point it stops consuming credits. Auto-resume means that when a query or other statement is submitted to a suspended warehouse, Snowflake automatically starts the warehouse again so the query can execute. Both behaviors are core to Snowflake's compute model and are enabled by default.

In Auto-scale mode, a multi-cluster warehouse scales out only after existing clusters can no longer keep up with incoming query load (a). Once that threshold is crossed, Snowflake provisions a new cluster (b), which then acquires compute resources and becomes available (c). Only after the new cluster is ready does Snowflake route queries to it (d). Each step strictly depends on the prior one.

Path access on a VARIANT is forgiving: v:name navigates into the JSON and returns the value if the key exists, or a JSON null / SQL NULL if it doesn't — it never raises an error for a missing key. So v:name::string yields 'Ada', and v:age::int yields NULL because the age key isn't present in the parsed object. This is why option b is wrong (no error) and option c is wrong (there is no implicit zero default — absence is NULL, not 0). Option d misstates the semantics: each path is evaluated independently, so a missing path on one column doesn't affect the other. This null-tolerant access is a deliberate feature for schema-on-read: semi-structured documents often have optional or evolving fields, and Snowflake lets you select them without guarding every path, surfacing absence as NULL. The casts (::string, ::int) convert the extracted VARIANT into typed SQL columns.

Named stages in Snowflake are referenced with the @ prefix — @my_stage, or @my_stage/path/ to scope to a subfolder — which is how COPY INTO, PUT, GET, and LIST know you mean a stage rather than a table. Stages come in two flavors: an external stage points at a location in your own cloud object storage (S3, GCS, or Azure Blob) using a storage integration or credentials, while an internal stage is storage Snowflake provisions and manages for you (named, user @~, or table @%table internal stages). The PUT command uploads local files into an internal stage; external stages are loaded by landing files in the underlying bucket. Knowing the @ reference syntax and the internal-vs-external distinction is fundamental to every data-loading workflow.

Snowflake's three-layer architecture separates persistent data storage (cloud storage), query execution (compute / virtual warehouses), and coordination (cloud services). The storage layer holds data in cloud-object stores; the compute layer runs virtual warehouses that process queries; the cloud services layer handles metadata, optimization, security, and transaction management.

Snowflake's defining design is the separation of storage and compute. Table data lives once in a shared, cloud-object-store-backed storage layer, and any number of independent virtual warehouses can read it concurrently. Because each warehouse is its own isolated compute cluster, assigning the ELT job and the BI dashboard to separate warehouses means neither competes for the other's CPU/memory — yet both still see the same single copy of the data with no duplication and no sync lag. Cloning (b) makes redundant logical copies and would require keeping them current. A clustering key (c) improves pruning within a query but does nothing about compute contention. Raising the credit quota (d) just permits more spend; it doesn't isolate the two workloads. Workload isolation via dedicated warehouses on shared storage is the textbook answer and the architectural reason Snowflake scales concurrent workloads cleanly.

Snowflake's result cache lives in the cloud services layer and stores the actual result set of a query. A later query reuses it only if the query text matches exactly (after normalization), the role has access, the underlying data hasn't changed, and the query contains no non-deterministic or time-dependent functions. Reused results cost no warehouse compute at all — the warehouse need not even be running. The cache entry is valid for 24 hours from the last time it was used, and each reuse resets that 24-hour window (it can extend up to 31 days total before being purged). The warehouse-local SSD cache (b) is a different layer — it caches raw micro-partition data to speed up scans, but a query that hits it still consumes compute. There is no one-hour full-result metadata cache (c), and reused results genuinely bypass execution rather than just running fast (d).

True. Snowflake automatically divides every table into micro-partitions — contiguous units of roughly 50–500 MB of uncompressed data (smaller compressed) — as rows are ingested, with no DDL to declare partitions and no manual maintenance. Each micro-partition is columnar and immutable, and Snowflake stores per-partition metadata (min/max value ranges, distinct counts, etc.) that the optimizer uses for pruning: it skips any micro-partition whose value range can't satisfy a query's filters, so scans touch only relevant data. This is fundamentally different from classic warehouses where you hand-pick partition keys and manage partition maintenance. The one knob you can add on top is a clustering key, which influences how rows are co-located across micro-partitions to make pruning more effective on large tables — but the partitions themselves are always created and managed automatically.

Bulk loading in Snowflake follows a stable pipeline. First define a FILE FORMAT so Snowflake knows how to parse the files (field delimiter, header lines, compression, null handling). Next create a STAGE — a named pointer to where the files live (an internal stage Snowflake manages, or an external stage over your own S3/GCS/Azure bucket) — typically referencing that file format. Then get the files into the stage: PUT uploads local files to an internal stage, while for an external stage the files simply need to be present in the bucket. With files staged, COPY INTO <table> FROM @stage performs the actual load, parsing and inserting rows (and recording per-file load metadata so already-loaded files are skipped on re-runs). Finally, verify the load — the COPY result set and COPY_HISTORY / VALIDATE show how many rows loaded and surface any rejected files for reprocessing. Doing COPY before the stage or file format exists would simply fail.

Snowflake's Cloud Services layer is a set of globally shared, multi-tenant services that coordinate all activity. It houses the query optimizer (parsing and plan generation), the authentication and access-control subsystem, and the metadata catalog that tracks databases, tables, and micro-partition-level statistics. Option (d) describes the Database Storage layer, where data is persisted as compressed, encrypted micro-partitions in cloud object storage. Option (e) describes the Query Processing layer, where virtual warehouses — independent compute clusters — execute the plan produced by the Cloud Services optimizer. Neither data persistence nor query execution lives in the Cloud Services layer, so only (a), (b), and (c) are correct.

These are two orthogonal scaling axes. Resizing a warehouse (XS -> S -> M ...) scales up: each step roughly doubles the compute per cluster, making an individual heavy query faster, but a single cluster still has a fixed concurrency ceiling, so a flood of small queries will still queue. Multi-cluster warehouses scale out: when queries start queuing, Snowflake automatically starts additional clusters of the same size (up to a configured max) and load-balances across them, then shuts them down when demand falls. The symptom here — many small, fast queries queuing due to a concurrency spike — is the textbook multi-cluster case, so (a) is correct. Resizing (b) addresses query latency, not concurrency, and would be the wrong (and costlier-per-query) lever. Size and cluster count are not the same knob (c). Auto-suspend (d) controls idle shutdown, not queueing under load.

Zero-copy cloning creates a new, independent object that initially references the source's existing immutable micro-partitions by metadata only — so creation is near-instant and adds essentially no storage (a). The clone is a full, writable snapshot taken at clone time, not a live view: it and the source then evolve separately, so a later INSERT into sales does not appear in sales_clone (d is wrong) and edits to either are isolated (b). Because Snowflake's micro-partitions are immutable, storage only grows when either object changes data, at which point the modified partitions are written and no longer shared — this copy-on-write divergence is the 'zero-copy' economics (c). Nothing is bulk-copied up front, so there is no transient 2x storage spike (e is wrong). This is why cloning is the standard cheap, instant way to spin up dev/test or pre-deploy snapshots of production data.

LATERAL FLATTEN expands a semi-structured value into one row per element. Here t.v:tags uses the colon path operator to pull the tags array out of the VARIANT, and FLATTEN produces three rows, one per array element, exposing each element through the VALUE column of the flatten output. Casting f.value::string strips the VARIANT's JSON quoting and yields the plain strings a, b, c — so option c (which keeps them quoted) is wrong because of the explicit ::string cast. FLATTEN works on arrays and objects (and nested structures), so option d is wrong. Option b describes not flattening at all. ORDER BY then sorts them alphabetically, giving rows a, b, c. This array-to-rows pattern via LATERAL FLATTEN is the canonical way to shred JSON arrays into relational rows in Snowflake.

Time Travel lets you query historical versions of data within the retention window. The BEFORE (STATEMENT => 'Q123') clause asks for the table's state immediately prior to the execution of statement Q123 — i.e., before the DELETE took effect — so all 100 rows are still visible and COUNT(*) returns 100. Option b is the trap: even though the DELETE committed, that's precisely what Time Travel is for — reconstructing the pre-change state from retained micro-partitions. Snowflake supports three Time Travel anchors — OFFSET (seconds back), TIMESTAMP, and STATEMENT (a query id) — so option c is wrong; STATEMENT is valid. The AT vs BEFORE distinction (option d) is real but here BEFORE is exactly right: AT (STATEMENT => 'Q123') would include the effects up to and including Q123 (0 rows), whereas BEFORE excludes it (100 rows). This is the standard 'oops, undo that DELETE' recovery pattern.

The cloud services layer is the coordination tier that ties the storage and compute layers together. The interview-critical points: it owns authentication/RBAC, the optimizer, transaction and metadata management, and the result cache; it runs on Snowflake-managed compute so you never size it; and it's effectively free unless it exceeds ~10% of daily warehouse credits. A sharp follow-up is why metadata-only queries cost no warehouse compute — because this layer can answer them from metadata without starting a warehouse. Knowing the three layers (storage / compute / cloud services) and that this is the 'services'/brain layer is a foundational Snowflake architecture answer.

This flashcard tests senior-level understanding of the fundamental storage unit in Snowflake, including its immutability, columnar nature, self-describing metadata, and role in data pruning.

Statements a, b, and e are true.

(a) TRUE: In Snowflake's multi-cluster architecture, each cluster is an independent set of compute resources with its own local SSD cache. A query running on cluster 2 cannot benefit from data cached on cluster 1's local SSD.

(b) TRUE: Snowflake's result cache is maintained at the service layer (not per-warehouse) and persists for 24 hours. The same query re-issued from any session on any warehouse in the account returns the cached result without re-execution, as long as the underlying data hasn't changed and the result is still in cache.

(c) FALSE: When a warehouse auto-suspends, its compute resources are released. On resume, Snowflake makes a best-effort attempt to reassign the same compute resources (which would preserve the local SSD cache), but this is never guaranteed regardless of how quickly the warehouse resumes. A different set of compute resources means a cold cache.

(d) FALSE: Snowflake allows resizing a running warehouse without suspending it. The resize takes effect for new queries; in-progress queries continue executing on the original compute resources and are NOT migrated to the new size.

(e) TRUE: A multi-cluster warehouse with MIN_CLUSTER_COUNT = 0 starts with no running clusters. Clusters are provisioned on demand when queries arrive, up to MAX_CLUSTER_COUNT, and are automatically shut down when idle (subject to AUTO_SUSPEND).

AUTO_SUSPEND is the number of seconds of inactivity after which Snowflake automatically suspends (stops billing) the warehouse. Setting it to 0 disables auto-suspend entirely: once any query auto-resumes the warehouse, it stays running indefinitely and accrues credits every second until someone manually suspends it — so a warehouse used for a few short bursts per hour effectively bills 24/7. That's the bug (a). The fix is a small positive value (e.g. AUTO_SUSPEND = 60) so it idles down quickly after each burst, especially painful at XLARGE where the per-second credit rate is high. Option b is wrong: INITIALLY_SUSPENDED = TRUE is correct and harmless — it just means the warehouse starts suspended and resumes on first use. Option c misreads AUTO_RESUME, which only governs starting on demand, not staying on. Option d is wrong — 'XLARGE' is valid. Pairing AUTO_RESUME with a sensible AUTO_SUSPEND is the standard cost-control idiom.

Clustering keys are a pruning optimization, not a magic speedup. By physically co-locating rows with similar key values, they narrow each micro-partition's value range so the optimizer can skip more partitions when a query filters on that key. The strong-answer nuance is the cost side: Snowflake's automatic reclustering runs as a background serverless service that burns credits to keep the table organized, and churny DML keeps it working — so the feature only earns its keep on big tables with selective, frequent predicates on a well-chosen column that isn't already ordered by load. A senior candidate will mention measuring with SYSTEM$CLUSTERING_INFORMATION and confirming real pruning gains, and will avoid clustering small tables or picking a near-unique or extremely low-cardinality key.

A Stream is Snowflake's CDC primitive: it records what changed on a source table since an offset, not a data copy, and surfaces inserts/updates/deletes plus metadata columns describing each change. The offset mechanics are the crux of the answer — when a stream is consumed by a committing DML statement, its offset moves forward to the transaction's start version, so each change is processed exactly once and subsequent reads see only newer deltas; a bare SELECT is a non-consuming peek that leaves the offset untouched. The canonical pattern is Stream + Task: a scheduled Task wakes up, checks SYSTEM$STREAM_HAS_DATA, and MERGEs the stream's changes into a target, giving incremental, idempotent ELT. Mentioning the consume-on-commit semantics and the Stream+Task pairing signals real operational understanding.

The ECONOMY scaling policy is designed to conserve credits by queuing queries rather than aggressively scaling out to additional clusters. This directly contradicts the requirement that queries start executing immediately without queuing. The STANDARD policy scales out more readily to minimize query queuing. Option a is wrong because MIN_CLUSTER_COUNT = 1 does not prevent full warehouse suspension (AUTO_SUSPEND still applies). Option c is wrong because AUTO_SUSPEND only triggers after inactivity, not during load spikes, and AUTO_RESUME = TRUE handles restart. Option d is wrong because each cluster in a multi-cluster warehouse runs at the full WAREHOUSE_SIZE — the size is not divided across clusters.

Line 5 uses MAX(AVG_RUNNING) and aliases it as peak_clusters, but the AVG_RUNNING column in WAREHOUSE_LOAD_HISTORY represents the average number of running queries per interval — not the number of running clusters. The query will execute successfully but returns peak concurrent queries, not peak clusters. To compute the number of running clusters, one must query WAREHOUSE_EVENTS_HISTORY for RESUMED and SUSPENDED events, tracking CLUSTER_NUMBER over time to determine how many clusters were active at each point.