SOL-C01 Question #107: Real Exam Question with Answer & Explanation

Question

A data warehouse contains a table 'ORDERS' with columns 'ORDER ID', 'CUSTOMER ID', 'ORDER DATE, and `ORDER TOTAL' You need to optimize a query that frequently retrieves the total order amount for a specific customer within a given date range. Which of the following strategies would be MOST effective in improving query performance?

Options

• ACreate a materialized view that pre-calculates the total order amount for each customer.
• BCreate a view that filters the `ORDERS' table based on the date range.
• Ccreate an index on the 'ORDER DATE column.
• DCluster the `ORDERS' table using `CUSTOMER ID and `ORDER DATES.
• EIncrease the warehouse size to allow for more compute resources.

Explanation

Clustering the table on `CUSTOMER ID and 'ORDER DATE is the most effective strategy because it physically organizes the data on disk based on these columns. This allows Snowflake to efficiently retrieve the relevant data for a specific customer and date range, minimizing the amount of data that needs to be scanned. Increasing warehouse size will improve general performance, but clustering optimizes for this specific query. Indexes are not typically used in Snowflake. A materialized view would be good, however, clustering is more effective.

No community discussion yet for this question.