DEA-C02 Question #97: Real Exam Question with Answer & Explanation

Question

This query was executed: SELECT SYSTEM$EXPLAIN_PLAN_JSON( 'SELECT Z1.ID, Z2.ID FROM Z1, Z2 WHERE Z2.ID = Z1.ID' ) AS explain_plan; What information will be provided in the results?

Options

• AAverage partition overlap
• BTotal partitions scanned
• CAverage clustering depth
• DClustering errors

Explanation

SYSTEM$EXPLAIN_PLAN_JSON returns the query execution plan as a JSON object, which includes scan-level statistics such as total partitions scanned - the number of micro-partitions Snowflake will read to execute the query. This makes B correct because explain plans are fundamentally about how the query engine will execute the statement, and partition pruning efficiency is a key part of that.

Why the distractors are wrong:

• A (Average partition overlap) and C (Average clustering depth) are metrics returned by SYSTEM$CLUSTERING_INFORMATION, a separate function specifically for analyzing clustering quality on a table - not execution plans.
• D (Clustering errors) is not a real output of any standard Snowflake system function; it's a fabricated distractor.

Memory tip: Think EXPLAIN = Execution, so EXPLAIN_PLAN tells you what the engine does at runtime (partitions scanned). Clustering functions tell you about the state of your data at rest - those are the ones that return depth, overlap, and similar structural metrics.

No community discussion yet for this question.