ADA-C01 Question 302
Single answerOperator nodesA Snowflake administrator is troubleshooting a recurring performance issue on a reporting query that joins a 12 TB SALES_FACT table to a 50 MB DIM_REGION table and then performs an aggregation. Query Profile shows most elapsed time is spent in a Join operator, and the profile indicates significant data movement between processing nodes before the aggregation begins. The warehouse is appropriately sized and is not queued. Which action is most likely to reduce the time spent in the operator nodes for this workload?
- A
Define a clustering key on SALES_FACT using the join column used to match DIM_REGION
- B
Convert DIM_REGION into a temporary table so Snowflake can keep it local to each operator node
- C
Increase the STATEMENT_TIMEOUT_IN_SECONDS parameter so the Join operator has more time to finish
- D
Replace the join with a view on SALES_FACT so the optimizer can eliminate operator-node data exchange
Show answer and explanation
Correct answer: A
Explanation
Operator nodes in Snowflake Query Profile represent execution steps such as scans, joins, aggregations, sorts, and exchanges performed across processing nodes in the virtual warehouse. In this scenario, the key clue is significant data movement before aggregation, centered on the Join operator. That usually points to expensive repartitioning/shuffling across nodes. For a very large fact table joined to a small dimension, administrators should examine whether the large table's physical organization supports efficient pruning and join processing. A clustering key on the large table's join column can improve micro-partition organization and reduce the amount of data that must be scanned and redistributed, which can lower operator-node work. The other options reflect common misconceptions: temporary tables do not control node locality, timeouts do not improve performance, and views do not eliminate physical join costs. This aligns with Snowflake best practices around using Query Profile to identify costly operators and considering clustering for large, repeatedly accessed tables with predictable filter or join patterns.
- A. Correct.
Correct. When Query Profile shows a Join operator with heavy data movement, one practical tuning approach is to improve pruning and data locality on the large table involved in the join. Clustering SALES_FACT on the join key can reduce the amount of data scanned and shuffled for the join, especially for large fact-to-small-dimension join patterns. While Snowflake automatically handles many physical optimizations, clustering can help specific large-table workloads where join/filter access patterns are stable and Query Profile indicates excessive work in join-related operator nodes.
- B. Incorrect.
Incorrect. Temporary tables change object lifespan and session scope, but they do not cause Snowflake to pin data to specific operator nodes for joins. Snowflake's execution engine distributes work across processing nodes dynamically, and table type does not provide node-local placement control in the way implied here.
- C. Incorrect.
Incorrect. Increasing STATEMENT_TIMEOUT_IN_SECONDS does not improve execution efficiency. It only allows a query to run longer before timing out. Because the issue is excessive work and data movement in the Join operator, extending the timeout would not reduce elapsed time or operator-node overhead.
- D. Incorrect.
Incorrect. Creating or using a view does not inherently remove the need for a join or the associated data exchange between operator nodes. A standard view is a logical abstraction over the underlying query. Unless the query itself changes in a way that reduces join cost, the optimizer still must execute the join against the base data.