ADA-C01 Question 308
Single answerIdentify and create efficient queriesA retail company runs a nightly report that joins a 4 TB SALES_FACT table to a 20 MB PRODUCT_DIM table and filters on SALES_DATE for the last 7 days and REGION = 'EMEA'. The query has become slower as data volume has grown. Query Profile shows large table scan time on SALES_FACT, while the join itself is inexpensive. The administrator wants to improve query efficiency without changing the business result. Which action is the BEST choice?
- A
Define a clustering key on SALES_FACT using (SALES_DATE, REGION) to improve micro-partition pruning for the report filter
- B
Increase the warehouse size from MEDIUM to 2XL so the query scans fewer micro-partitions
- C
Create a materialized view only on PRODUCT_DIM because the smaller table is joined frequently
- D
Rewrite the query to replace the join with a CROSS JOIN and filter the rows after the join
Show answer and explanation
Correct answer: A
Explanation
The best answer is to improve pruning on the large fact table because the scenario explicitly states that query time is dominated by scanning SALES_FACT. In Snowflake, efficient queries often depend on reducing scanned data through effective micro-partition pruning. When a large table is commonly filtered by specific columns, defining an appropriate clustering key can improve clustering depth over time and help Snowflake skip irrelevant micro-partitions. By contrast, simply increasing warehouse size adds compute but does not change the query access pattern or reduce bytes scanned. Likewise, optimizing the already-small dimension table provides little benefit when the join is not the bottleneck. Snowflake documentation and best practices emphasize examining Query Profile, identifying scan-heavy operations, and using clustering selectively for very large tables with common selective filter predicates.
- A. Correct.
Correct. The profile indicates the main bottleneck is scanning the large SALES_FACT table, not the join. Since the query repeatedly filters on SALES_DATE and REGION, clustering SALES_FACT on those columns can improve micro-partition pruning and reduce the amount of data scanned. This is a practical optimization for large fact tables when filter predicates align with clustering keys and pruning is not already effective.
- B. Incorrect.
Incorrect. Increasing warehouse size can reduce elapsed time by adding compute, but it does not make the query itself more efficient and does not reduce the number of micro-partitions scanned. The scenario specifically points to poor scan efficiency on the large table. Scaling up may mask the issue temporarily but does not address pruning.
- C. Incorrect.
Incorrect. Creating a materialized view on the small dimension table is unlikely to help because PRODUCT_DIM is already only 20 MB and the profile shows the join cost is low. Materialized views are more useful when they precompute expensive aggregations or selective subsets of large base tables that are repeatedly queried.
- D. Incorrect.
Incorrect. A CROSS JOIN would create a Cartesian product before filtering, which would dramatically increase intermediate row volume and make the query less efficient. This option reflects a misunderstanding of join strategy and would worsen performance in most real-world cases.