SnowPro Advanced: Administrator free question 7 / 10 · Domain 4: Performance Monitoring and Tuning
A query joins table FACT_SALES (about 120,000 micro-partitions) with small table DIM_STORE. Before running it, EXPLAIN showed partitionsAssigned of about 40,000 for the FACT_SALES TableScan. After running it, the Query Profile showed about 6,000 partitions read from FACT_SALES. How should this difference be read, according to the documentation?
- partitionsAssigned comes from statistics of the previous run, and later table changes altered the count at run time
- partitionsAssigned is the value without the result cache; partitions still in the cache did not have to be read
- partitionsAssigned is an upper-bound estimate after compile-time pruning, which runtime optimizations reduced further
- partitionsAssigned counts partitions not in the warehouse cache; fewer were read because some had already been cached
Show the answer and explanation
Answer: C
Why this is correct
EXPLAIN compiles a statement but does not execute it. In its output, partitionsAssigned is the number of partitions of a referenced table that remain after compile-time pruning, that is, the partitions the query might read. The documentation describes partitionsAssigned and bytesAssigned as upper-bound estimates for execution, and says runtime optimizations such as join pruning can further reduce the partitions and bytes actually read. In a join with a small table, the large table can be pruned at run time once the join values are known, so reading about 6,000 instead of the planned 40,000 is exactly that mechanism.
Why the other options are wrong
"partitionsAssigned counts partitions not in the warehouse cache; fewer were read because some had already been cached" … partitionsAssigned is the count left after compile-time pruning, not a count of partitions missing from a cache. Caching speeds up reading but does not prune what is read; the documentation names runtime optimizations such as join pruning as what reduces the count.
"partitionsAssigned comes from statistics of the previous run, and later table changes altered the count at run time" … EXPLAIN compiles the statement at the moment it is run and returns a logical plan; it does not build values from past executions. The difference comes from runtime optimization narrowing an upper-bound estimate, not from table changes.
"partitionsAssigned is the value without the result cache; partitions still in the cache did not have to be read" … When a stored result can be reused, the statement does not execute at all; there is no partial skipping of partitions. Here the Query Profile shows partitions being read, so this is runtime pruning, not result reuse.
Reading the plan
EXPLAIN output lists partitionsTotal (all partitions of the table), partitionsAssigned (left after pruning), and bytesAssigned. The plan is logical, and the actual order of execution of operations can differ from the logical order shown. The output can also be produced as JSON, stored in a table, and turned back into readable text later with SYSTEM$EXPLAIN_JSON_TO_TEXT.