Performance Optimization · Topic 1 of 9

Performance Tools, Query Profile and Metadata Functions

Pick the right Snowflake performance tool, read a Query Profile for spilling, pruning and exploding joins, and use history and metadata functions.

  • 5 min
In a nutshell
  • Query Profile shows operator tree, spilling, pruning and IO statistics; most expensive nodes are highlighted.
  • Partitions scanned near total means poor pruning; spilling means memory shortage; output far above input means an exploding join.
  • GET_QUERY_OPERATOR_STATS gives profile data as rows; EXPLAIN shows the plan without running.

What it is

Snowflake gives an Architect a set of tools, and the skill the exam tests is matching a symptom to the right one. The Query Profile shows what a single query did. Query history shows patterns across queries. Metadata functions report on tables, clustering and plans without running the workload again.

Each tool answers a different question, so start from the symptom: slow, queued, expensive or scanning too much.

Symptomslow, queued, costlylook atQuery Profileoperators and statisticsfindBottleneckspill, scan, join, queueapplyFixsize, cluster, rewrite, accelerate

How it works

Choosing the performance tool

SymptomFirst lookTypical fix
One query is slowQuery ProfileRewrite, prune better, larger warehouse
Many queries waitWarehouse load and queue columnsMulti-cluster or more warehouses
Big scans for selective filtersPartitions scanned versus totalClustering key or search optimization
Memory spillingBytes spilled in the profileLarger or Snowpark-optimized warehouse
Repeated identical queriesCache useResult cache, materialized view
Sporadic large scans on a busy warehouseQuery acceleration eligibilityQuery acceleration service

The Query Profile

Open a query in Snowsight and choose Query Profile. It shows an operator tree and statistics. Click an operator for its detail.

Operator or sectionWhat to read
TableScanPartitions scanned versus total, bytes scanned, percent scanned from cache
Filter, Join, Aggregate, SortRows in and out, and the share of time spent
Most expensive nodesHighlighted by percentage of total time
Statistics: SpillingBytes spilled to local storage and to remote storage
Statistics: IOBytes read, scanned from cache, network
Statistics: PruningPartitions scanned and total

Time is split into processing, local disk IO, remote disk IO, network communication, synchronization and initialization. A high remote disk IO share points at scanning and spilling; high processing points at compute heavy operators.

Reading the profile for bottlenecks

Partitions scanned near totalpoor pruningfixCluster, filter on the key, avoid functions on columnsBytes spilledmemory shortagefixBigger warehouse or less data per stepJoin output rows far above inputexploding joinfixAdd the missing join condition
FindingCauseRecommendation
Scanned partitions equal total partitionsFilter on a column that is not clustered, or the filter wraps the column in a functionCluster on the filtered column, rewrite the predicate
Bytes spilled to local or remote storageOperator needs more memory than the warehouse hasUse a larger warehouse, process in smaller pieces, or a Snowpark-optimized warehouse
Join returns far more rows than either inputMissing or wrong join condition produces a Cartesian productFix the join condition
UNION that is slowUNION removes duplicates through an extra stepUse UNION ALL when duplicates are fine
Large Sort at the topORDER BY on a big resultRemove it or add LIMIT
Queued time very highWarehouse concurrency limit reachedAdd clusters or another warehouse
Exam tip

A profile that shows spilling to remote storage is the stronger signal. Local spilling is slow; remote spilling is much slower and nearly always justifies a larger warehouse or a rewrite.

Query history and metadata functions

SourceUse
INFORMATION_SCHEMA.QUERY_HISTORY table functionRecent queries, current and near real time
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY viewLong term, whole account, with some latency
GET_QUERY_OPERATOR_STATS(<query_id>)The profile's operator statistics as rows, for scripting
EXPLAIN and SYSTEM$EXPLAIN_PLAN_JSONThe plan and the partitions it expects to scan, without running the query
SYSTEM$CLUSTERING_INFORMATION and SYSTEM$CLUSTERING_DEPTHClustering quality for a table and key
SYSTEM$ESTIMATE_QUERY_ACCELERATIONWhether a query would benefit from query acceleration
SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTSCost estimate before enabling search optimization

Useful query history columns are TOTAL_ELAPSED_TIME, QUEUED_OVERLOAD_TIME, QUEUED_PROVISIONING_TIME, PARTITIONS_SCANNED, PARTITIONS_TOTAL, BYTES_SPILLED_TO_LOCAL_STORAGE, BYTES_SPILLED_TO_REMOTE_STORAGE and BYTES_SCANNED.

SELECT query_id, total_elapsed_time, partitions_scanned, partitions_total,
       bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE start_time > DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND bytes_spilled_to_remote_storage > 0
ORDER BY total_elapsed_time DESC
LIMIT 20;

This finds the day's worst spilling queries. A ratio of PARTITIONS_SCANNED to PARTITIONS_TOTAL near 1 on a selective query is the pruning check.

Best practices that apply broadly

  • Select only the columns you need, instead of SELECT *.
  • Filter early and on columns that prune; avoid wrapping filtered columns in functions.
  • Use native types, not strings, for dates and numbers.
  • Size by workload: small and steady for ETL, large for heavy analytics, separate warehouses per workload type.
  • Let caching help before buying compute.
  • Test a bigger warehouse on one slow query: if runtime halves with double credits per hour, total credits stay about the same.

Common mistakes

Adding compute first. A bad pruning ratio or an exploding join does not get fixed by a bigger warehouse.

Reading only elapsed time. Compare queued, compile, spill and scan numbers to find the real cause.

Treating ACCOUNT_USAGE as real time. It has latency; use the INFORMATION_SCHEMA table function for fresh data.

Running the query again to investigate it. EXPLAIN and query history avoid rerunning an expensive statement.

What to remember for the exam

  • Query Profile shows operator tree, spilling, pruning and IO statistics; most expensive nodes are highlighted.
  • Partitions scanned near total means poor pruning; spilling means memory shortage; output far above input means an exploding join.
  • GET_QUERY_OPERATOR_STATS gives profile data as rows; EXPLAIN shows the plan without running.
  • SYSTEM$CLUSTERING_INFORMATION, SYSTEM$ESTIMATE_QUERY_ACCELERATION and SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS help decide before enabling a feature.
  • Match symptom to tool before changing warehouse size.

Check yourself

  • A selective query scans 98 percent of the partitions in a large table. What does the profile tell you?

    Pruning is poor. Consider a clustering key on the filtered column or rewriting a predicate that wraps the column in a function.

  • Which profile statistic suggests that a larger warehouse may help?

    Bytes spilled to local or remote storage, which means operators ran out of memory.

  • How can you check a query plan without executing the query?

    Run EXPLAIN or use SYSTEM$EXPLAIN_PLAN_JSON.

Topic 1 of 9 in Performance Optimization

Test yourself on Performance Optimization

The full Snowflake ARA-C01 question bank has 687 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.