Snowflake — architecture, cost, and the patterns interviews test.

The three-layer architecture — why storage and compute are separate.

Most candidates describe Snowflake as "a cloud data warehouse" and stop. The senior answer names the three decoupled layers and — more importantly — explains what each decoupling buys you. The whole pricing model, the concurrency story, and half the interview traps fall out of this one diagram.

What the decoupling actually buys you

• Storage and compute scale independently. Petabytes of cold data cost only storage; you spin compute up and down on top of it. A legacy warehouse couples the two — you over-provision compute just to hold data.
• Workload isolation without copying data. The ETL warehouse, the BI warehouse, and the data-science warehouse all read the same tables, yet a heavy backfill on one cannot slow a dashboard on another. No replicas, no data movement.
• Elastic concurrency. A single warehouse can scale out into multiple clusters when users pile up (more on this in §03).
• Compute is disposable. Suspend a warehouse and you stop paying for it instantly; the data is untouched in the storage layer.

Micro-partitions & pruning — how Snowflake reads less data.

You cannot tune what you cannot picture. Snowflake's storage unit is the micro-partition: an immutable, columnar file holding roughly 50–500 MB of uncompressed data, created automatically as data lands. You never size them, never name them, never write a PARTITION BY clause. The performance question is never "how do I partition" — it is "how well can the optimizer skip partitions."

Pruning — the only performance idea that matters

For every micro-partition, Snowflake stores per-column metadata: the min and max value, distinct counts, null counts. When a query filters on a column, the optimizer compares the predicate to that metadata and reads only the micro-partitions that could contain a match. Everything else is skipped before a single byte is scanned. That skip is called pruning.

This is why load order is itself a performance feature. If rows arrive roughly time-ordered, each micro-partition holds a tight date range and date filters prune beautifully. If you shuffle the data, every micro-partition spans the whole date range, min/max becomes useless, and the optimizer must scan everything.

Clustering keys — when natural order is not enough

Suppose the table loads time-ordered, but the heaviest queries filter on region. Date pruning works; region pruning does not, because every micro-partition contains every region. A clustering key tells Snowflake to co-locate rows with similar region values into the same micro-partitions:

ALTER TABLE orders CLUSTER BY (region, order_date);

-- inspect how well-clustered the table currently is
SELECT SYSTEM$CLUSTERING_INFORMATION('orders', '(region, order_date)');

Automatic Clustering then maintains that organization as a background service — and that service consumes credits. So clustering is a deliberate trade: you pay ongoing reorganization cost to buy faster pruning.

Search Optimization — the other access path

Clustering accelerates range and moderately selective scans. It does little for a needle-in-a-haystack point lookup — WHERE event_id = '...' returning three rows out of two billion. For that, enable the Search Optimization Service, which builds a separate per-table search-access path for highly selective equality predicates. The two are complementary and can coexist on one table.

Virtual warehouses & the credit model — the cost levers.

A virtual warehouse is a cluster of compute you rent by the second. The interview question is rarely "what is a warehouse" — it is "this Snowflake bill tripled last quarter; where do you look?" Answering that means knowing every lever.

Scale UP vs scale OUT — the distinction that separates levels

A bigger warehouse finishes a heavy query faster but, because it also costs proportionally more per second, a 2× warehouse that runs a query 2× faster is roughly cost-neutral — you are buying latency, not savings. Multi-cluster, by contrast, only spins up extra clusters while queries are actually queuing and retires them when load falls; under SCALING_POLICY = STANDARD it favours starting clusters early to kill queuing, under ECONOMY it favours keeping clusters fully loaded to save credits.

The cost levers — a checklist for "why is the bill high"

Three caching layers — free speed if you know they exist

1. Result cache — cloud-services layer. A syntactically identical query, unchanged data, < 24h old, returns the stored result with no warehouse and no compute cost. Account-wide: another user gets the hit too.
2. Warehouse (local SSD) cache — micro-partition data cached on a running warehouse's nodes. Warmed by use; lost on suspend, so a resumed warehouse starts cold.
3. Metadata cache — COUNT(*), MIN, MAX and similar are answered from micro-partition statistics with no data scan at all.

Time Travel, cloning & Fail-safe — the data-lifecycle trio.

Three features fall straight out of one fact: micro-partitions are immutable. An "update" never edits a file — it writes new micro-partitions and leaves the old ones in place for a while. Once you internalise that, Time Travel, zero-copy cloning and Fail-safe stop being magic.

Time Travel — query the past

Because the previous micro-partitions still exist, you can query a table as of an earlier moment, within its retention window (default 1 day; up to 90 days on Enterprise edition and above):

-- the three ways to address the past
SELECT * FROM orders AT(OFFSET => -3600);                    -- 1 hour ago
SELECT * FROM orders AT(TIMESTAMP => '2026-03-14 09:00'::timestamp);
SELECT * FROM orders BEFORE(STATEMENT => '01b2...-query-id'); -- just before a bad query

-- recover from an accidental unqualified DELETE
INSERT INTO orders
SELECT * FROM orders BEFORE(STATEMENT => '<the_delete_query_id>');

-- recover a dropped object
UNDROP TABLE orders;

Zero-copy cloning — branch the data, not the bytes

CREATE TABLE orders_dev CLONE orders; on a 5 TB table returns in seconds. The clone is a new, fully writable object that initially just references the source's existing micro-partitions — metadata only, zero added storage. Because those micro-partitions are immutable, the moment either copy is modified it writes new micro-partitions, and you pay storage only for that divergence. You can clone a table, a schema, or an entire database.

Fail-safe — the last resort, not a feature you use

After Time Travel retention expires, permanent tables enter a non-configurable 7-day Fail-safe period. You cannot query it; only Snowflake support can recover from it, on a best-effort basis. It is disaster insurance — and it is billed storage.

Streams, Tasks & Dynamic Tables — native change-data-capture.

The defining pipeline question: "new data keeps arriving — how do you process only what changed, without re-scanning the world?" Snowflake answers it natively, and there are now two idioms — the imperative one (Streams + Tasks) and the declarative one (Dynamic Tables).

Streams — a bookmark over a table's changes

A stream is a change-tracking object. It does not store a copy of the data — it stores an offset, and exposes the rows that changed between that offset and the table's current version, with three metadata columns:

• METADATA$ACTION — INSERT or DELETE
• METADATA$ISUPDATE — TRUE when the row is part of an update (represented as a paired DELETE + INSERT)
• METADATA$ROW_ID — a stable row identifier

Reading a stream is repeatable — it returns the same change set every time until a DML statement consumes it inside a transaction, which advances the offset on commit. A stream goes stale if its offset falls outside the source table's data-retention window, because the change history is no longer available — a real production failure mode to design around.

Tasks — scheduled or triggered SQL

A task runs a SQL statement (or a call to a stored procedure) on a schedule or in a dependency graph. The idiom that makes the loop cheap is the stream guard:

CREATE TASK refresh_dim_customer
  WAREHOUSE = etl_wh
  SCHEDULE  = '2 MINUTE'
  WHEN SYSTEM$STREAM_HAS_DATA('customer_stream')   -- skip empty cycles
AS
  CALL apply_scd2_customer();   -- the MERGE logic lives in a procedure

When the stream is empty the task body does not run; a serverless task bills nothing for that skipped cycle. Tasks can be wired into a task graph (a DAG) so a root task fans out to dependents.

Worked example — an SCD2 dimension

A Type-2 slowly-changing dimension keeps history: when a customer's tier changes, you close the current row and open a new one. A single MERGE cannot both update the old version and insert its replacement on the same key, so the procedure runs two statements in one transaction:

-- 1. expire the active version where a tracked attribute changed
UPDATE dim_customer d
SET    valid_to = CURRENT_TIMESTAMP(), is_current = FALSE
FROM   customer_stream s
WHERE  d.customer_id = s.customer_id
  AND  d.is_current = TRUE
  AND  s.metadata$action = 'INSERT'
  AND  (d.name <> s.name OR d.tier <> s.tier);

-- 2. open a new current version for changed + brand-new customers
INSERT INTO dim_customer (customer_id, name, tier, valid_from, valid_to, is_current)
SELECT s.customer_id, s.name, s.tier, CURRENT_TIMESTAMP(), NULL, TRUE
FROM   customer_stream s
WHERE  s.metadata$action = 'INSERT'
  AND  NOT EXISTS (SELECT 1 FROM dim_customer d
                   WHERE d.customer_id = s.customer_id AND d.is_current = TRUE
                     AND d.name = s.name AND d.tier = s.tier);

Both statements read customer_stream inside one transaction, so the change set is consistent across them and the offset advances exactly once.

Dynamic Tables — the declarative alternative

Streams + Tasks is powerful but imperative — you hand-wire offsets, guards and schedules. A Dynamic Table flips it around: you declare the query and a target freshness, and Snowflake works out the incremental refresh itself.

CREATE DYNAMIC TABLE customer_orders_daily
  TARGET_LAG = '5 minutes'
  WAREHOUSE  = etl_wh
AS
  SELECT c.customer_id, c.tier, COUNT(*) AS orders, SUM(o.amount) AS revenue
  FROM   orders o JOIN dim_customer c ON o.customer_id = c.customer_id
  WHERE  c.is_current = TRUE
  GROUP BY c.customer_id, c.tier;

Project brief — a near-real-time analytics pipeline.

Reading about Snowflake is not the same as building on it. This is a self-contained project — buildable on a Snowflake trial account — that exercises every concept in §§01–05. Treat it as a take-home: build it, then be ready to defend each decision.

The brief

A retailer drops order events as JSON files into cloud storage every few minutes, all day. Customer records (name, loyalty tier) drift over time. Build a Snowflake pipeline that ingests the events continuously, models them into a star schema with a history-preserving customer dimension, and serves an always-fresh revenue mart — incrementally, with no full reloads.

The architecture

Build steps

1. Stage & file format. Create an external stage over the cloud-storage bucket and a JSON FILE FORMAT. Land raw events in a table with a single VARIANT column — schema-on-read keeps ingestion resilient to upstream JSON changes.
2. Continuous ingest. Create a Snowpipe with auto-ingest so new files load within a minute or two on serverless compute — no warehouse schedule to manage.
3. Streams. Put a stream on raw_orders and one on raw_customer so every downstream step processes only new rows.
4. SCD2 customer dimension. Build dim_customer with valid_from / valid_to / is_current. Drive it with a stored procedure (the two-statement MERGE from §05) called by a Task guarded by SYSTEM$STREAM_HAS_DATA.
5. Fact table. Flatten the order JSON with LATERAL FLATTEN, casting VARIANT paths to typed columns. Join each order to the customer version that was current at order time — that is the whole point of keeping SCD2 history.
6. Reporting mart. Define mart_revenue_daily as a Dynamic Table with TARGET_LAG = '5 minutes' — it satisfies the freshness requirement declaratively, no extra task wiring.
7. Recoverability. Confirm Time Travel retention covers your worst-case "notice the bug" window; rehearse a BEFORE(STATEMENT => ...) recovery so it is muscle memory.

Why each requirement is met

• Freshness — Snowpipe ingests within ~1–2 min; the Dynamic Table holds the mart within its 5-minute TARGET_LAG.
• Idempotency — streams advance their offset only on commit, so a failed task simply re-sees the same unconsumed changes next run; the SCD2 MERGE checks for an existing identical current row before inserting.
• Cost-to-zero — Snowpipe and serverless tasks bill per work done; the ETL warehouse auto-suspends; empty cycles are skipped by the stream guard.
• Recoverability — Time Travel on the permanent fact and dimension tables; transient tables for any throwaway intermediate so you do not pay Fail-safe on scratch data.

Stretch goals — turn it into a portfolio piece

• Data quality. Add a task that rejects malformed events into a quarantine table and alerts when the reject rate crosses a threshold.
• Governance. Apply a masking policy on customer PII and a row access policy so each region's analysts see only their rows.
• A CI environment. Zero-copy CLONE the whole database into a CI database, run the pipeline end-to-end against it, assert row counts, then drop it.
• Observability. Build a small monitoring view over SNOWFLAKE.ACCOUNT_USAGE — credits per warehouse per day, Snowpipe load latency, task failure history.

Rapid-fire — the tricky questions, with crisp answers.

The follow-ups that separate "used Snowflake" from "understands Snowflake." One or two sentences each — the way you would answer them in the room.