COF-C03 Question 255
Single answerQuery Performance TuningA retail company stores 4 years of sales data in a large SALES_FACT table. Analysts frequently run queries such as: SELECT SUM(net_amount) FROM sales_fact WHERE sale_date BETWEEN '2024-11-01' AND '2024-11-30' AND region = 'WEST'; The team notices these queries are scanning a large number of micro-partitions and running slowly, even though the virtual warehouse size has already been increased. Which action would most directly improve query performance for this workload?
- A
Define a clustering key on columns such as sale_date and region for the SALES_FACT table
- B
Convert the SALES_FACT table to a temporary table so Snowflake can scan it faster
- C
Create more schemas and move the table into a dedicated schema for reporting queries
- D
Increase the data retention period so more historical versions are available for pruning
Show answer and explanation
Correct answer: A
Explanation
The key issue is that the query is filtering on specific columns, but Snowflake is still scanning many micro-partitions. In Snowflake, query performance for large tables often depends on effective micro-partition pruning. When common, selective filter columns are not well clustered, Snowflake may need to scan more micro-partitions than necessary. For large tables with repeated filtering on columns like dates and regions, defining a clustering key can improve locality of related values and reduce scanned data. Increasing warehouse size may help with compute capacity, but it does not directly solve poor pruning. Best practices in Snowflake documentation for performance tuning emphasize examining query profile information, looking at partition pruning behavior, and considering clustering for large tables with selective and frequent filter predicates.
- A. Correct.
Correct. This workload repeatedly filters on sale_date and region, and the problem statement specifically says many micro-partitions are being scanned. Defining an appropriate clustering key can improve micro-partition pruning by organizing data so rows with similar values for the filter columns are stored closer together. For large fact tables with selective predicates that are used frequently, clustering can reduce the number of micro-partitions scanned and improve performance.
- B. Incorrect.
Incorrect. Temporary tables do not inherently improve scan performance for the same data and query pattern. Table type affects object lifespan and visibility, not micro-partition pruning behavior in a way that would make this large fact-table query faster by itself. Someone might choose this if they confuse session-scoped object management with storage optimization.
- C. Incorrect.
Incorrect. Schemas are logical containers for database objects and do not affect how table data is physically organized or pruned during query execution. Moving a table to another schema would not reduce the number of micro-partitions scanned. This distractor targets the misconception that administrative organization improves runtime performance.
- D. Incorrect.
Incorrect. Increasing data retention affects Time Travel availability and storage usage, not pruning efficiency for active query workloads. In fact, it does not reorganize table data to help these date-and-region filters. A candidate might pick this if they associate historical metadata with query optimization, but retention is not a tuning mechanism for this scenario.