ADA-C01 Question 231
Single answerAnalyze table design considerationsA retail company stores 8 TB of order history in a Snowflake table named ORDERS_FACT. The table receives hourly inserts and occasional status updates for recent orders. Analysts run two main query patterns: (1) dashboards that filter on ORDER_DATE ranges and REGION, and (2) customer service lookups by ORDER_ID. Query performance has become inconsistent as the table has grown. The administrator wants to improve pruning efficiency and overall table design without introducing unnecessary maintenance overhead. Which action is the BEST recommendation?
- A
Define a clustering key on (ORDER_DATE, REGION) and monitor clustering depth over time
- B
Define a clustering key on ORDER_ID because point lookups benefit most from clustering
- C
Convert ORDERS_FACT to a temporary table so that Snowflake can optimize storage layout for recent data
- D
Split ORDERS_FACT into one table per REGION to guarantee partition pruning for dashboard queries
Show answer and explanation
Correct answer: A
Explanation
The best answer is to align table design with actual filter patterns used by the largest workload. In Snowflake, data is stored in immutable micro-partitions, and query performance often depends on effective partition pruning. For very large tables, explicit clustering keys can improve pruning when queries frequently filter on the same columns, especially date ranges and low- to moderate-cardinality dimensions such as REGION. By contrast, clustering on a unique or near-unique identifier like ORDER_ID often provides less benefit, particularly when point lookups are only one of several access patterns. Snowflake best practices emphasize evaluating clustering only for large tables where filtering patterns justify the cost, and using system functions/views to assess clustering quality rather than assuming it is always needed. Likewise, converting to temporary tables or manually splitting tables into region-specific shards does not address the root design consideration and can introduce operational drawbacks. Relevant Snowflake documentation includes guidance on micro-partitions, data clustering, and choosing clustering keys based on selective query predicates and table size.
- A. Correct.
Correct. Snowflake automatically organizes table data into micro-partitions, and explicit clustering is most useful for very large tables with selective filter predicates that align to common query patterns. Since the main analytic workload filters on ORDER_DATE ranges and REGION, clustering on those columns can improve micro-partition pruning and make performance more consistent. This recommendation also fits the stated goal of avoiding unnecessary overhead because clustering should be applied only where query benefit justifies the maintenance cost; monitoring clustering depth or related clustering information helps validate that benefit over time.
- B. Incorrect.
Incorrect. Although ORDER_ID lookups are part of the workload, clustering is generally most effective for large-scale pruning across ranges or correlated filter dimensions rather than for isolated, highly selective point lookups on a single identifier. In many cases, clustering on a high-cardinality key such as ORDER_ID provides limited overall benefit relative to maintenance cost, especially when the dominant workload is date- and region-based analytics.
- C. Incorrect.
Incorrect. Temporary tables are intended for session-scoped or short-lived data, not for improving storage layout or pruning behavior of a long-lived fact table. Converting a production fact table to temporary would be operationally inappropriate and would not solve the underlying design issue. Snowflake's micro-partitioning behavior is not improved simply by making a table temporary.
- D. Incorrect.
Incorrect. Manually splitting the table into one table per REGION increases schema and ETL complexity, makes queries and governance harder to manage, and is usually unnecessary in Snowflake because micro-partition pruning and clustering can address many performance issues without physically sharding data this way. This option reflects a legacy data warehouse design pattern that is often not the best fit for Snowflake.