ADA-C01 Question 311
Single answerArticulate the execution pathA Snowflake administrator is troubleshooting a dashboard query that is consistently slower than expected. The query joins a 4 TB FACT_SALES table to a 20 MB DIM_REGION table and filters on FACT_SALES.ORDER_DATE for the last 7 days. The administrator opens Query Profile to articulate the execution path and determine where the time is being spent. Which observation from the execution path most strongly indicates that partition pruning on FACT_SALES is ineffective and is likely the primary cause of the slowdown?
- A
The TableScan node for FACT_SALES shows a very high percentage of micro-partitions scanned compared to total micro-partitions, followed by a large volume of rows flowing into downstream operators.
- B
The Join node shows that the DIM_REGION table was broadcast to all processing nodes before the join was executed.
- C
The Result node shows a small final output set because the dashboard only returns aggregated values for a few regions.
- D
The Aggregate node consumes measurable time because the query groups by region after the join.
Show answer and explanation
Correct answer: A
Explanation
To articulate the execution path in Snowflake, an administrator should read Query Profile from the data access layer upward: scan operators, then joins, then aggregations, then final result processing. For large-table performance issues, the most important early question is whether Snowflake pruned micro-partitions effectively. If a query filtering on a recent 7-day range still scans a large percentage of FACT_SALES micro-partitions, the profile indicates poor pruning and unnecessary I/O, which often dominates runtime. By contrast, broadcasting a small dimension table is commonly a valid execution choice, and small final outputs do not imply efficient processing. Snowflake documentation and best practices emphasize using Query Profile to inspect scan volume, rows produced at each step, and whether pruning and clustering are supporting selective predicates.
- A. Correct.
Correct. In Query Profile, ineffective partition pruning is reflected in the scan stage reading many micro-partitions from the large table even though the query filters on a narrow date range. When the TableScan on FACT_SALES scans a high proportion of available micro-partitions and pushes a large number of rows downstream, Snowflake is unable to eliminate enough storage partitions early. For a large fact table, this is a strong signal that clustering/micro-partition organization on ORDER_DATE is not helping the predicate, or that the predicate is not selective in practice.
- B. Incorrect.
Incorrect. Broadcasting a very small dimension table such as a 20 MB DIM_REGION table is often an efficient join strategy in Snowflake and does not, by itself, indicate a performance problem. Candidates may choose this because 'broadcast' sounds expensive, but for a small dimension joined to a very large fact table, it is typically expected and can reduce data movement.
- C. Incorrect.
Incorrect. A small final result set does not reveal whether the expensive part of the query occurred during scanning, joining, or aggregating. Many slow analytical queries return only a few rows after processing large input volumes. The execution path must be interpreted from scan and intermediate operators, not just the final result size.
- D. Incorrect.
Incorrect. Aggregation can consume time, but in this scenario it is not the strongest indicator of the root cause. If the large fact table scan is already excessive because many micro-partitions were read unnecessarily, downstream aggregation time is often a consequence rather than the primary issue. This option reflects a common mistake of focusing on later operators instead of identifying where excess data first enters the plan.