A table for IOT devices that measures water usage is created. The table quickly becomes large and contains more than 2 billion rows.
The general query patterns for the table are:
1. DeviceId, IOT_timestamp and CustomerId are frequently used in the filter predicate for the select statement
2. The columns City and DeviceManufacturer are often retrieved
3. There is often a count on UniqueId
Which field(s) should be used for the clustering key?
In the given scenario, the clustering key should be based on the columns that are frequently used in the filter predicates, as it optimizes the data retrieval process. Since DeviceId, IOT_timestamp, and CustomerId are frequently used in the filter predicates, they are the most logical choices for a clustering key. Out of these, DeviceId and CustomerId will be more effective for clustering because they will help in organizing the data in a manner that improves query performance for the most common access patterns. Hence, DeviceId and CustomerId are the appropriate choice for the clustering key.
Should be C: https://docs.snowflake.com/en/user-guide/tables-clustering-keys