Performance Monitoring and Tuning · Topic 2 of 16

Query Profile Steps, Operator Tree, Nodes and Operator Types

Know the Query Profile building blocks for ADA-C02: steps, the operator tree, operator nodes and the operator types to recognize and what each signals.

  • 5 min
  • Includes a diagram
In a nutshell
  • Steps appear only when a query runs in stages; the operator tree runs from scans at the bottom to Result at the top.
  • Each node shows type, ID and percentage of total time.
  • Know the groups: data access, data processing, DML, metadata and miscellaneous.

What it is

A Query Profile is built from four ideas. A step is one stage of a query that ran as several. The operator tree is the whole plan drawn as a graph. An operator node is one box in it, and each node has an operator type that says what kind of work it does.

The exam likes to show a symptom, such as a join that returns billions of rows, and ask which operator is the cause. Knowing the operator names and what a bad one looks like is the skill.

Stepsnavigation when query ran in stagescontainOperator treewhole planmade ofOperator nodestype, ID, percent of timelabelled byOperator typesTableScan, Join, Aggregate

How it works

Steps

Some queries run as one step. Others run as several, for example when a statement must first create an intermediate result. The navigation controls let you move between steps, and they appear only when the query ran in steps. If a profile shows no step controls, the whole query is one plan.

Operator tree

An operator tree: two TableScan leaf nodes feed a Join that combines two inputs, then an Aggregate, and the Result root returns rows. A node that receives a few thousand rows and sends out millions, such as a Join with a missing condition, is where rows multiply. Data flows from the bottom of the tree to the top. The leaves are data access operators such as TableScan, and the root is the Result operator that returns rows to the client. The arrows between nodes carry the rowsets produced by one operator for the next.

Arrows are the place to spot trouble. If a node receives a few thousand rows and sends out millions, something in it multiplies rows.

Operator nodes

Each node shows its operator type and ID, its share of the total query time as a percentage, and a short preview of its details such as a table name or an expression list. Click a node to see its Attributes, while the Statistics pane for the whole query stays visible beside it.

  • A high percentage marks a candidate for tuning, but check why the node is slow.
  • The node ID lets you tie a node to the text of an EXPLAIN plan.

Operator types

GroupOperatorsWhat they do
Data access and generationTableScan, IndexScan, ValuesClause, Generator, ExternalScan, InternalObjectRead a table, a hybrid table index, literal values, generated rows, staged files, or internal objects such as Information Schema tables
Data processingFilter, Join, Aggregate, GroupingSets, WindowFunction, Sort, SortWithLimit, Flatten, JoinFilter, UnionAll, ExternalFunctionTransform rows
DMLInsert, Delete, Update, Merge, UnloadChange data or export it
MetadataDDL and transaction commands, table creation command, query result reuse, metadata based resultWork answered without scanning data
MiscellaneousResultReturn the final output

What the commonly tested ones signal:

  • TableScan shows the table, columns read and partitions scanned against total. A high share of time here points at too much data read or poor pruning.
  • Join combines two inputs on a condition. A very high output row count means a join explosion, usually a missing or wrong join condition.
  • Aggregate does GROUP BY and SELECT DISTINCT. High memory use here can cause spilling.
  • Sort orders everything; SortWithLimit returns only a limited slice, which is cheaper.
  • JoinFilter drops rows early that cannot match a later join. Seeing it means the optimizer found a way to cut data before the join.
  • Flatten processes semi structured data and can multiply rows.
  • ExternalScan reads files from a stage, as in a COPY load.
  • Query result reuse means the result cache answered the query. Metadata based result means the answer came from metadata, such as SELECT COUNT(*) FROM t, without a warehouse.
Exam tip

When a profile shows a single node labelled metadata based result, no warehouse was needed and no compute credits were spent.

TableScanreads partitionsrowsFilterWHERErowsJoinon conditionrowsAggregateGROUP BYrowsSortORDER BYrowsResult

Common mistakes

Thinking every query has steps. Step navigation exists only when the query ran in several steps.

Confusing Sort with SortWithLimit. The second is used when a limit is combined with ordering, and it avoids sorting the full set.

Blaming Join for time that belongs to its inputs. If the scan beneath it is slow, fix the scan.

Ignoring row counts on arrows. A jump in rows between two nodes is the clue to a join explosion or a Flatten that multiplies rows.

What to remember for the exam

  • Steps appear only when a query runs in stages; the operator tree runs from scans at the bottom to Result at the top.
  • Each node shows type, ID and percentage of total time.
  • Know the groups: data access, data processing, DML, metadata and miscellaneous.
  • A join with far more output rows than input is a join explosion, often from a missing join condition.
  • Query result reuse and metadata based result nodes mean no real scan happened.

Check yourself

  • What does an operator node display?

    Its operator type and ID, its percentage of total query time and a preview of its details.

  • A Join node outputs billions of rows from small inputs. What is likely wrong?

    A join explosion from a missing or too loose join condition, such as an accidental Cartesian product.

  • Which operator signals that the optimizer dropped non matching rows before a later join?

    JoinFilter.

Topic 2 of 16 in Performance Monitoring and Tuning

Test yourself on Performance Monitoring and Tuning

The full Snowflake ADA-C02 question bank has 814 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.