Performance Optimization Study Guide — DEA-C02

Performance and Troubleshooting

This domain is one of the larger topic areas on the SnowPro Advanced: Data Engineer (DEA-C02) exam under the current (carried-over, unverified) blueprint weighting — second to Data Movement. It tests your ability to diagnose slow queries, interpret execution metadata, tune warehouses and clustering, troubleshoot data-loading pipelines, and monitor tasks and streams. Expect scenario-based questions where you must choose the correct tool, view, or remediation strategy from a set of plausible-sounding alternatives.

Query Profile: Operator Nodes, Partition Pruning Stats, Spilling to Disk

The Query Profile is accessed via Snowsight (or Classic UI) and shows a DAG of operator nodes for a completed or running query.

Key Operator Node Types

Partition Pruning Statistics

In the TableScan node, inspect:

• Partitions total — all micro-partitions in the table
• Partitions scanned — partitions not eliminated by pruning
• Bytes scanned — actual data read after pruning

A well-clustered table on the filter column will show partitions scanned ≪ partitions total. If the ratio approaches 1.0, clustering is ineffective for that predicate.

Spilling to Disk

Spilling occurs when an operator's memory requirement exceeds the warehouse memory allocation for that node.

Remediation options (in order of preference):

1. Rewrite query to reduce intermediate result set (push filters earlier)
2. Increase warehouse size (more memory per node)
3. Use CLUSTER BY to reduce data scanned upstream of the spilling operator
4. Split large queries into incremental steps with intermediate tables

Common traps:

• Exam may show a query that spills but also has poor pruning — the root cause is almost always pruning; fix pruning first.
• Remote spill is always worse than local spill; choosing "increase warehouse size" to fix remote spill is correct only if query rewriting is not an option listed.
• ExternalScan nodes never benefit from micro-partition pruning statistics.

EXPLAIN Plan: Interpreting Execution Plans, Identifying Bottlenecks

EXPLAIN generates a logical execution plan without running the query. EXPLAIN USING TABULAR / USING JSON / USING TEXT are the output format options. Default is TEXT.

EXPLAIN SELECT o.order_id, SUM(l.amount)
FROM orders o JOIN lineitems l ON o.order_id = l.order_id
WHERE o.region = 'EMEA'
GROUP BY o.order_id;

Key columns in the TABULAR output:

Identifying Bottlenecks via EXPLAIN

1. Look for TableScan with no filter expression — full table scan.
2. Verify Join order: the smaller (build) table should feed the inner side.
3. Check for Aggregate or Sort steps without preceding Filter — optimizer missed pushdown.
4. EXPLAIN does not show actual row counts, actual spill, or runtime statistics — use Query Profile for those.

Common traps:

• EXPLAIN does not consume credits and does not execute the query — exam frequently tests this boundary.
• EXPLAIN reflects the optimizer's plan; the actual plan at runtime can differ (e.g., adaptive join decisions).
• EXPLAIN cannot show partition pruning effectiveness because it doesn't access metadata at plan time.