ADA-C01 Question 229
Single answerManage tablesA data engineering team loads approximately 500 million rows per day into a large FACT_SALES table. Most analyst queries filter on ORDER_DATE and REGION, and recent query history shows the table is frequently reclustered automatically after each load. The team wants to reduce ongoing maintenance cost while preserving good query performance on recent data. Which action should the Snowflake administrator take?
- A
Convert FACT_SALES to a hybrid table so ORDER_DATE and REGION are automatically indexed and clustering maintenance is eliminated.
- B
Define a clustering key on ORDER_DATE only, because Snowflake can automatically derive REGION selectivity from micro-partition metadata.
- C
Review the clustering depth and query patterns, then simplify or remove the clustering key if the pruning benefit does not justify the reclustering cost.
- D
Create a search optimization service on every column in FACT_SALES and drop the clustering key, because search optimization is the preferred replacement for clustering on large fact tables.
Show answer and explanation
Correct answer: C
Explanation
The best answer is to validate whether the current clustering strategy is worth its maintenance cost. In Snowflake, clustering keys on large frequently updated or loaded tables can improve pruning, but they also increase automatic clustering activity and cost. Administrators should use actual workload evidence, including query history and SYSTEM$CLUSTERING_INFORMATION or related clustering metrics, to determine whether the chosen key meaningfully improves pruning. If the table's natural load order already aligns well with ORDER_DATE, or if REGION does not add enough benefit relative to reclustering overhead, the administrator should simplify or remove the clustering key. Snowflake documentation emphasizes that clustering should be applied selectively to very large tables where query predicates benefit enough to justify the ongoing maintenance.
- A. Incorrect.
Incorrect. Hybrid tables are intended for transactional workloads with primary key-based access patterns and have different design considerations than standard large analytic fact tables. Converting a very large analytic fact table to a hybrid table is not a general solution for reducing reclustering cost, and hybrid tables are not simply a drop-in replacement for clustered standard tables.
- B. Incorrect.
Incorrect. Defining a clustering key on ORDER_DATE only may help some date pruning, but the statement that Snowflake automatically derives REGION selectivity from a single-column clustering key is misleading. Micro-partition metadata can help pruning regardless, but if REGION is an important filter, whether it should be part of a clustering strategy depends on actual cardinality, correlation, and maintenance cost. This option ignores the key issue in the scenario: frequent reclustering cost must be weighed against measurable query benefit.
- C. Correct.
Correct. Snowflake best practice is to evaluate whether a clustering key is actually beneficial by examining query patterns, pruning effectiveness, and clustering information such as clustering depth. For a heavily loaded table, automatic clustering can create ongoing cost. If the table still performs well because natural ordering or micro-partition metadata already provides sufficient pruning, simplifying the key or removing it can reduce maintenance cost while keeping acceptable performance.
- D. Incorrect.
Incorrect. Search Optimization Service is designed for specific selective lookup patterns, such as point lookups or highly selective predicates, and is not a universal replacement for clustering. Enabling it on every column of a large fact table would likely be expensive and unnecessary. Clustering and search optimization solve different performance problems, so dropping the clustering key in favor of broad search optimization is not the best practice described by the scenario.