Performance Optimization, Querying & Transformation

Result Cache

The Result Cache stores the exact output of a query in the Cloud Services Layer for 24 hours. Snowflake returns cached results instantly — no Virtual Warehouse credits consumed. Reuse conditions must ALL be true:

• Exact same SQL text
• Same user role (security context)
• No underlying data changes in the referenced objects
• No non-deterministic functions (e.g., CURRENT_TIMESTAMP(), RANDOM())

Metadata Cache

Persistent cache in the Cloud Services Layer storing micro-partition statistics — min/max values per column, row counts, and null counts. This enables Snowflake to answer certain queries with zero warehouse credits:

• SELECT COUNT(*) FROM my_table — no VW required
• SELECT MIN(col), MAX(col) FROM my_table — no VW required
• Partition pruning decisions are also driven by metadata cache

Local Disk Cache (Data Cache)

Each Virtual Warehouse node caches decompressed micro-partition data on its local SSD during query execution. Subsequent queries on the same VW can reuse this cached data — reducing remote storage reads.

• Cache lifetime tied to VW uptime — lost when the VW suspends
• Key reason to avoid frequent suspend/resume for performance-sensitive workloads
• Automatically evicted using LRU policy when SSD fills up
• Larger VW sizes = more SSD capacity per cluster node

Reading the Query Profile

Access in Snowsight under Query History. The profile is a visual DAG (directed acyclic graph) where each node is an operator:

• TableScan: shows partitions scanned vs. total — low % scanned = good pruning
• Join: reveals join type (hash, merge, nested loop) and row counts
• Aggregate: grouping and aggregation node
• Sort: sorting operator — can spill to disk if result set is large

Spilling to Disk

When an operator (Sort, Aggregate, Join) runs out of in-memory space, it spills intermediate data first to local SSD, then to remote cloud storage. This is a major performance bottleneck.

• Visible in Query Profile as "Bytes spilled to local storage" or "Bytes spilled to remote storage"
• Fix: scale UP (larger VW size = more memory per node)
• Also optimize the query: break large aggregations, avoid unnecessary sorts, use clustering keys
• Remote storage spill is far worse than local SSD spill — indicates severely under-resourced VW

Exploding Joins & Partition Pruning

Exploding join: query produces far more rows than expected. Indicates missing or incorrect join predicates — a Cartesian or near-Cartesian join. Check join node's output row count vs. input.

Partition pruning: if % of partitions scanned is high on a large table, natural clustering doesn't match your filter columns. Consider adding a clustering key.

• Use SYSTEM$CLUSTERING_INFORMATION('table', '(col1, col2)')' to assess clustering quality before committing
• Pruning efficiency is the single biggest lever for reducing query cost on large tables

Micro-Partition Fundamentals

Snowflake stores data in columnar micro-partitions of 50–500 MB (compressed). They are auto-created, immutable, and managed entirely by Snowflake. Each micro-partition stores metadata (min/max per column) enabling pruning without reading the data.

• Natural clustering = insertion order — works well if data is appended in filter-column order (e.g., event logs by date)
• Poor natural clustering = high % of partitions scanned for selective queries

Clustering Keys

Explicitly define columns to physically cluster data around. Best suited for:

• Very large tables (multi-TB)
• Tables frequently filtered on specific high-cardinality columns
• Tables with poor natural clustering relative to query patterns

-- Define clustering key
ALTER TABLE my_table CLUSTER BY (region, order_date);

-- Check clustering quality
SELECT SYSTEM$CLUSTERING_INFORMATION('my_table', '(region, order_date)');

• Automatic Clustering: Snowflake background service continuously re-clusters the table — uses serverless credits billed separately
• Clustering depth < 1 = well-clustered; high depth = poor clustering, queries scan too many partitions
• Use ALTER TABLE my_table SUSPEND RECLUSTER; to pause Automatic Clustering

Scale UP vs. Scale OUT

• Each size step doubles compute resources (and cost) — roughly linear improvement for memory-bound queries
• Queuing indicator: check QUEUED_PROVISIONING_TIME and QUEUED_OVERLOAD_TIME in QUERY_HISTORY
• High queuing = Scale OUT. High spill-to-disk / slow single queries = Scale UP

How They Work

A Materialized View (MV) stores the pre-computed result of a SELECT query on disk. When the base table changes, Snowflake's background service automatically refreshes the MV using serverless credits.

• Enterprise edition or above required
• Queries can use the MV transparently — Snowflake may rewrite the query automatically
• Faster reads than standard views (no recomputation at query time)

Limitations (exam-tested):

• No JOINs in the MV definition
• No non-deterministic functions (e.g., CURRENT_TIMESTAMP)
• No subqueries in the definition
• Only one base table allowed

Best use case: expensive aggregations (SUM, COUNT, AVG) over large tables, queried frequently with the same filters.

Point Lookup Acceleration

The Search Optimization Service (SOS) is a serverless feature that builds a persistent search access path alongside the table data. It dramatically speeds up equality and IN predicate queries on large tables.

-- Enable Search Optimization
ALTER TABLE my_table ADD SEARCH OPTIMIZATION;

-- Disable
ALTER TABLE my_table DROP SEARCH OPTIMIZATION;

• Best for: tables with billions of rows, ad-hoc point lookups, highly selective WHERE clauses with equality or IN predicates
• NOT for: range scans, full table scans, analytical aggregation workloads — use clustering keys instead
• Incurs additional storage cost for the search access path
• Serverless credits consumed to build and maintain the access path

How Streams Work

A Stream tracks DML changes (INSERT, UPDATE, DELETE) on a source table since the last time the stream was consumed. It uses the table's Time Travel metadata to track an offset.

-- Create a standard stream
CREATE STREAM my_stream ON TABLE source_table;

-- Query changes
SELECT * FROM my_stream;

Metadata columns added by streams:

• METADATA$ACTION: INSERT or DELETE (updates = one DELETE row + one INSERT row)
• METADATA$ISUPDATE: TRUE if the row is part of an UPDATE operation
• METADATA$ROW_ID: unique identifier for the row

Stream Types & Staleness

• Standard stream: captures inserts, updates (as delete+insert pair), and deletes
• Append-only stream: captures inserts only — more efficient for append-only source tables (event logs, Kafka landing tables)
• Insert-only stream: for external tables; inserts only

Staleness: a stream becomes STALE if not consumed within the source table's Time Travel retention window (default 1 day, max 90 days on Enterprise). Once stale, the stream must be recreated.

• Consuming a stream requires a DML statement inside a transaction — this advances the stream offset
• Check: SYSTEM$STREAM_HAS_DATA('my_stream') — returns TRUE if unconsumed changes exist

Scheduling SQL with Tasks

Tasks schedule a SQL statement or a stored procedure call on a fixed interval or cron schedule.

-- VW-based task (every 5 minutes)
CREATE TASK my_task
  WAREHOUSE = my_wh
  SCHEDULE = '5 MINUTE'
AS
  INSERT INTO target_table
  SELECT * FROM my_stream WHERE METADATA$ACTION = 'INSERT';

-- Tasks start SUSPENDED — must resume
ALTER TASK my_task RESUME;

• Default state: SUSPENDED — you must explicitly ALTER TASK ... RESUME after creation
• Serverless tasks: omit the WAREHOUSE clause; Snowflake manages compute via USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE
• Task trees: child tasks triggered on parent completion using AFTER parent_task clause
• Stream + Task pattern: task checks SYSTEM$STREAM_HAS_DATA() before processing to avoid empty runs

Declarative Pipelines

Dynamic Tables take a declarative approach: you define the transformation query and a TARGET_LAG (maximum staleness). Snowflake automatically determines when and how to refresh — no manual stream/task orchestration needed.

CREATE DYNAMIC TABLE customer_summary
  TARGET_LAG = '1 minute'
  WAREHOUSE = transform_wh
AS
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id;

• TARGET_LAG: max acceptable staleness — e.g., '1 minute', '1 hour', or DOWNSTREAM (inherit from downstream dynamic tables)
• Snowflake chooses full or incremental refresh automatically
• Dynamic Tables can chain: output of one feeds the definition of another
• Simpler to manage than Streams + Tasks for multi-hop transformation pipelines

Stored Procedures

Stored procedures execute procedural logic: loops, conditionals, multiple SQL statements, and DDL. Called with CALL my_proc(args);.

• Snowflake Scripting (SQL-based): recommended approach — native SQL with procedural constructs
• JavaScript: legacy option, still tested on exam
• Python, Java, Scala: available for complex logic and library access
• Can return a scalar value or a table (TABLE return type)
• Can execute DDL (CREATE TABLE, DROP TABLE) — unlike UDFs
• Run with caller's rights or owner's rights — controls which role's privileges apply

UDFs (User-Defined Functions)

Scalar UDFs: return one value per input row. Used inline in SQL:

SELECT my_udf(column_name) FROM my_table;

UDTFs (Table Functions): return a table (multiple rows) per input. Called with TABLE():

SELECT * FROM TABLE(my_udtf(arg1, arg2));

• Languages: SQL, JavaScript, Python, Java
• Secure UDFs: hide implementation from users who lack OWNERSHIP privilege — required for data sharing scenarios where the UDF logic is proprietary
• UDFs cannot execute DML or DDL — use stored procedures for that