← All handbooks

Snowflake Handbook

Snowflake end to end — the cloud data platform whose key idea is separation of storage and compute. Understand that and warehouses, scaling, cost, and concurrency all make sense.

01 Multi-cluster shared-data architecture

Three independent layers:

Because all warehouses share one storage layer, ETL, BI, and data science teams run concurrently on isolated compute without copying data or blocking each other.

02 Virtual warehouses

• A warehouse is a compute cluster sized XS → 4XL (each step doubles compute & cost/hour). Bigger = faster for heavy queries, not free.
• Auto-suspend (idle → stop billing) and auto-resume (query → start). Set short auto-suspend to save credits.
• Multi-cluster warehouses auto-scale out (add clusters) for concurrency spikes; resizing scales up for query size.
• Scale up for a single slow/large query; scale out for many concurrent users.

CREATE WAREHOUSE etl_wh WITH
  warehouse_size='MEDIUM' auto_suspend=60 auto_resume=TRUE
  min_cluster_count=1 max_cluster_count=3;

03 Micro-partitions & clustering

• Tables are auto-split into immutable micro-partitions (~50–500MB uncompressed), stored columnar & compressed. No manual partitioning needed.
• Snowflake keeps min/max metadata per partition → pruning: queries skip partitions that can’t match the filter. This is the main performance lever.
• For very large tables with a common filter column, define a clustering key so related rows co-locate → better pruning. Costs auto-reclustering credits — use selectively.
• No indexes to manage — pruning + columnar scan replace them.

04 Loading data

-- stage -> COPY INTO (bulk batch load)
CREATE STAGE s1 URL='s3://bucket/orders/' STORAGE_INTEGRATION=int;
COPY INTO orders FROM @s1
  FILE_FORMAT=(TYPE=parquet) MATCH_BY_COLUMN_NAME=case_insensitive;

• Stages (internal/external) hold files; COPY INTO bulk-loads with parallelism scaled to warehouse size.
• Snowpipe = continuous, serverless, near-real-time micro-batch loading (event-triggered).
• Prefer compressed columnar files (Parquet) sized ~100–250MB; avoid tiny files.
• Unload with COPY INTO @stage FROM table.

05 Semi-structured data (VARIANT)

SELECT v:customer.id::int AS cust,
       v:items[0].sku::string AS first_sku
FROM raw_events;                          -- v is a VARIANT column

SELECT f.value:sku::string
FROM raw_events, LATERAL FLATTEN(input => v:items) f;  -- explode array

VARIANT stores JSON/Avro/Parquet natively; query with path notation (:, []) and cast with ::. FLATTEN explodes nested arrays into rows. Schema-on-read — load raw, parse in SQL.

06 Time travel, clones & streams

• Time Travel — query/restore past data up to retention (1 day standard, up to 90 Enterprise): SELECT ... AT(OFFSET => -3600), UNDROP TABLE.
• Zero-copy cloning — CREATE TABLE dev CLONE prod instantly, no storage cost until data diverges. Great for dev/test environments.
• Streams — track row-level changes (CDC) on a table; Tasks run scheduled SQL/procedures → build incremental ELT (stream + task + MERGE).
• Fail-safe — 7-day Snowflake-managed recovery after Time Travel ends.

07 Data sharing & the marketplace

• Secure Data Sharing — share live tables/views to other accounts with no copy (they read your storage); revoke anytime.
• Reader accounts let non-Snowflake consumers query shared data.
• Marketplace distributes/monetizes datasets. Snowpark runs Python/Java/Scala transforms in-engine; Cortex adds built-in LLM/ML functions.

08 Cost & performance

• You pay for compute credits (per-second, per warehouse, min 60s) + storage. Idle warehouses with no auto-suspend burn money.
• Cut cost: short auto-suspend, right-size warehouses, separate workloads onto separate warehouses, use the result cache (identical query <24h = free instant result).
• Speed: maximize pruning (filter on clustered/natural-ordered columns), avoid SELECT *, materialize hot aggregates, use clustering only on huge tables.
• Caches: result cache (services layer), local disk cache (warehouse), metadata cache. Monitor with QUERY_HISTORY & WAREHOUSE_METERING_HISTORY.
• Watch spilling to local/remote disk in the query profile → warehouse too small.

09 Security & governance

• RBAC: privileges granted to roles, roles granted to users/roles (hierarchy). SYSADMIN, SECURITYADMIN, ACCOUNTADMIN.
• RLS via row access policies; column masking via masking policies (dynamic, role-aware); tags for classification.
• Always-on encryption at rest & in transit; network policies; SSO/MFA. Object hierarchy: account → database → schema → table.

10 Interview drills

• Explain separation of storage & compute; scale up vs scale out.
• What are micro-partitions; how does pruning replace indexes; when to add a clustering key.
• COPY INTO vs Snowpipe; how VARIANT + FLATTEN work.
• Time Travel vs Fail-safe; zero-copy clone use cases; streams + tasks for CDC.
• How to reduce credit spend; what the result cache does.
• RBAC model; how masking/row policies enforce governance.