I85PREP
⌘ K
SnowPro Core study materials
SnowPro Core study resources

SnowPro Core: Performance Optimization, Querying, and Data Transformation

SnowPro Core · Work through your exam, one topic at a time.

All guide sections

Performance Optimization, Querying, and Data Transformation

Performance Optimization

What makes a query fast

Almost all Snowflake tuning reduces bytes scanned. Because every micro-partition stores min/max metadata per column, the optimizer prunes partitions that cannot match a filter. Clustering - natural, from load order, or an explicit clustering key - keeps related rows in the same partitions so pruning stays effective as the table grows; clustering depth/overlap measures how well sorted the table is. Good clustering keys are high-cardinality columns used in WHERE clauses, not low-cardinality flags.

Use the Query Profile to find real cost: the most expensive operator, partitions scanned versus total, and spilling. Spilling goes to local disk first, then remote storage; remote spilling means the warehouse is undersized.

Tooling: search optimization service for selective point lookups and substring searches, materialized views for repeated aggregations, result and local disk caches for repeats, and EXPLAIN plans to inspect pruning.

Traps

  • Resizing a warehouse does not fix poor pruning; clustering does.
  • Multi-cluster warehouses solve queueing, not slow queries.
  • Automatic clustering runs in the background and consumes credits.
  • Adding a clustering key to a table that is already naturally sorted wastes credits.
  • An undersized warehouse on a huge scan can spill and run far longer than a bigger one, even at similar total credits.

Rule of thumb

Read the profile top-down: is the scan pruning badly (partitions scanned ÷ total), or is the join spilling? Fix pruning with clustering and selective filters; fix spilling with a bigger warehouse.

Querying and Data Transformation

SQL surface you must know

Snowflake supports ANSI SQL with strong analytical extensions: window functions, QUALIFY (filters window results without a subquery), CTEs, PIVOT/UNPIVOT, GROUPING SETS/ROLLUP/CUBE, ASOF joins, and time-travel clauses (AT, BEFORE) in the FROM clause.

Semi-structured data lives in VARIANT columns. Navigate with the colon path (src:customer.id::string) or bracket notation (src['customer']). LATERAL FLATTEN(input => src:items) explodes arrays into rows and must be lateral-joined to the source row. TRY_CAST/TRY_TO_NUMBER return NULL instead of failing on bad input.

Transformation patterns

  • Streams + tasks for change data capture and scheduled pipelines: a stream records DML on a table, a task runs SQL on a schedule or when the stream has data.
  • Dynamic tables for declarative, refresh-on-schedule transformations.
  • UDFs/UDTFs (SQL, JavaScript, Python) and Snowpark for procedural logic.
  • Zero-copy CLONE for dev/test copies of production data.

Traps

  • QUALIFY is evaluated after window functions but before ORDER BY; it is not a substitute for HAVING on aggregates.
  • FLATTEN without LATERAL can behave like a cross join against every row in the table.
  • VARIANT keys are case-sensitive: src:City and src:city are different paths.
  • A stream becomes stale if it is not consumed within the table's data retention period.

Rule of thumb

Filtering on ROW_NUMBER() or SUM() OVER ()? Write QUALIFY. Filtering on a grouped total? Write HAVING.