COF-C03 Question 76
Single answerBest practicesA data engineering team loads clickstream data into a Snowflake table every 5 minutes. Analysts run frequent dashboard queries on the most recent 14 days of data, while compliance teams occasionally run audits across the full 3-year history. Query performance has become inconsistent and storage costs are increasing due to frequent table maintenance. Which action is the BEST practice to improve performance for the common dashboard queries while minimizing unnecessary maintenance overhead?
- A
Create a clustering key on the event timestamp column so Snowflake can automatically maintain data organization for the frequently filtered date range
- B
Create a materialized view containing the last 14 days of data and direct all queries, including 3-year audit queries, to the materialized view
- C
Increase the size of the virtual warehouse used by dashboard queries and disable auto-suspend so the warehouse stays warm
- D
Convert the table to a temporary table so recent data is faster to query and historical data can be reloaded when needed
Show answer and explanation
Correct answer: A
Explanation
The best answer is to create a clustering key on the event timestamp column when the table is large and a dominant access pattern repeatedly filters recent time ranges. In Snowflake, clustering can improve micro-partition pruning for selective queries, especially on very large tables where natural ordering is not sufficient. This is aligned with Snowflake best practices: use clustering selectively for large tables with clear filter patterns, rather than as a default for every table.
The other choices are less appropriate. Materialized views can accelerate repeated query patterns, but they introduce maintenance costs and are not a universal substitute for the base table, especially when workloads include both recent-range analytics and full-history queries. Simply scaling up the warehouse can help performance but does not solve inefficient pruning and can increase cost, particularly if auto-suspend is disabled. Temporary tables are inappropriate for persistent historical data.
Relevant Snowflake guidance includes best practices around selective use of clustering keys for large tables with common filter predicates, cautious use of materialized views due to maintenance cost, and cost control through auto-suspend/auto-resume for warehouses.
- A. Correct.
Correct. For a very large table with frequent filters on a timestamp/date range, defining an appropriate clustering key on the event timestamp can improve pruning and query performance for the most common access pattern. This is a recognized best practice when natural clustering is insufficient and queries repeatedly filter on the same columns. Snowflake automatically maintains clustering in the background, which is generally preferable to manual table reorganization. This approach targets the hot query pattern without forcing additional objects for all workloads.
- B. Incorrect.
Incorrect. A materialized view on the last 14 days might help some repeated queries, but using it for all queries, especially 3-year audit queries, is not appropriate. Materialized views also add maintenance cost because Snowflake must keep them updated as base table data changes. Given the data arrives every 5 minutes, that maintenance can be significant. This option also does not fit the full-history audit use case well.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce runtime through more compute, but it does not address the underlying data organization problem. Disabling auto-suspend is generally not a cost-optimization best practice because it can increase compute charges during idle periods. This option treats a storage/pruning issue as purely a compute issue.
- D. Incorrect.
Incorrect. Temporary tables are session-scoped and are not intended for durable production datasets such as 3 years of clickstream history. Converting the main table to temporary storage would break persistence requirements and create major operational risk. Temporary tables are useful for transient intermediate processing, not long-term analytical storage.