ADA-C01 Question 309
Single answerIdentify and create efficient queriesA retail company stores 8 TB of order history in a Snowflake table named ORDERS_FACT. The table has columns including ORDER_ID, CUSTOMER_ID, ORDER_TS, ORDER_DATE, REGION, STATUS, and AMOUNT. Analysts frequently run dashboards with queries similar to:
SELECT REGION, SUM(AMOUNT) FROM ORDERS_FACT WHERE TO_DATE(ORDER_TS) BETWEEN '2024-01-01' AND '2024-01-31' AND UPPER(STATUS) = 'COMPLETE' GROUP BY REGION;
Query Profile shows heavy scanning, poor micro-partition pruning, and high warehouse consumption. The administrator cannot change the dashboard tool, but can modify table design and SQL objects in Snowflake. Which action would most directly improve query efficiency for this workload?
- A
Create a materialized view that pre-aggregates SUM(AMOUNT) by REGION for all rows in ORDERS_FACT, without including filters on date or status.
- B
Define a clustering key on expressions such as TO_DATE(ORDER_TS) and UPPER(STATUS) for ORDERS_FACT so pruning can align with the predicates used by the dashboard.
- C
Increase the warehouse size from MEDIUM to XLARGE so more data can be scanned in parallel for each dashboard query.
- D
Create a search optimization service on the AMOUNT column because aggregate queries benefit most from point-lookup acceleration.
- E
Rewrite the dashboard query to use SELECT * so Snowflake can optimize column pruning automatically.
Show answer and explanation
Correct answer: B
Explanation
The most direct optimization is to improve pruning so Snowflake scans fewer micro-partitions. In this scenario, the repeated use of functions in predicates can make it harder for the storage layer to efficiently eliminate irrelevant data unless the table is organized to support those expressions. Defining a clustering key on expressions such as TO_DATE(ORDER_TS) and UPPER(STATUS) is a practical design choice when the application-generated SQL cannot be changed.
This aligns with Snowflake best practices for query performance: reduce scanned data, leverage micro-partition pruning, and use clustering strategically for large tables with repetitive selective filters. Simply increasing warehouse size can improve runtime but not efficiency. Search optimization is powerful for highly selective search patterns, but it is not the best fit for this aggregation-heavy scenario. Materialized views can help when they closely match query patterns, but a broad aggregation by REGION alone does not address the dashboard's frequent date and status filtering.
Relevant Snowflake documentation areas include query performance optimization, micro-partitions and data pruning, clustering keys including expression-based clustering, materialized views, and search optimization service.
- A. Incorrect.
Incorrect. A materialized view can improve performance in some cases, but this option is not the most direct improvement for the stated workload. The query filters on date and status before aggregating by region. A materialized view that only pre-aggregates by REGION across all rows does not align well with the selective predicates and may still require substantial work to satisfy the filtered query. A more targeted MV could help, but as written, this design is too broad and not the best answer.
- B. Correct.
Correct. The workload repeatedly applies functions in the WHERE clause: TO_DATE(ORDER_TS) and UPPER(STATUS). Query Profile indicates poor micro-partition pruning, so aligning clustering with the filtered expressions can improve pruning effectiveness. Snowflake supports clustering keys on expressions, and using expressions that match common filter predicates is a practical way to improve scan efficiency when the query text cannot be changed.
- C. Incorrect.
Incorrect. Increasing warehouse size may reduce elapsed time by adding compute, but it does not directly improve query efficiency. The scenario specifically highlights heavy scanning and poor pruning. The best practice is to reduce the amount of data scanned first, not simply add more compute to process inefficient queries faster.
- D. Incorrect.
Incorrect. Search optimization is intended for specific selective access patterns, such as point lookups, equality predicates, substring searches, and some join scenarios. It is not primarily designed to accelerate broad aggregation workloads on AMOUNT, and optimizing the AMOUNT column would not address the root issue of function-based predicates reducing pruning on ORDER_TS and STATUS.
- E. Incorrect.
Incorrect. SELECT * would have the opposite effect. Snowflake performs columnar pruning when only required columns are referenced. Expanding the projection to all columns increases unnecessary I/O and does not improve predicate pruning. This option reflects a common misconception about query optimization.