Apply Clustering Keys and the Search Optimization Service DEA-C02 Practice Questions (Page 4)

Questions 16–20

1. 16foundation · easy

   What is the primary purpose of defining a clustering key on a Snowflake table?

   • ATo physically co-locate rows with similar values in the same micro-partitions
   • BTo enforce uniqueness constraints on the specified columns
   • CTo automatically compress the table data to reduce storage costs
   • DTo create an index structure for faster point lookups
   Reveal Solution Discussion Select an answer first
2. 17foundation · easy

   What is the key difference between clustering keys and the Search Optimization Service in Snowflake?

   • AClustering keys require manual maintenance, while Search Optimization is automatic
   • BClustering keys are used for small tables, while Search Optimization is for large tables
   • CClustering keys physically organize data, while Search Optimization creates a search index for selective queries
   • DClustering keys are for point lookups, while Search Optimization is for range scans
   Reveal Solution Discussion Select an answer first
4. 18application · medium

   A data engineer is troubleshooting a performance issue on a clustered table. The table is clustered on `order_id`, and queries that filter on `order_id` are still scanning many micro-partitions. The engineer runs `SYSTEM$CLUSTERING_INFORMATION('orders')` and sees that the average clustering depth is 1.5, but the `total_partition_count` is very high. What is the most likely explanation for the poor query performance?

   • AThe clustering depth is low, but the table has so many micro-partitions that the metadata pruning is still insufficient to avoid scanning a large number of them.
   • BThe table has many micro-partitions, and even with good clustering, the query must scan all of them because the filter is not selective.
   • CThe clustering key is not selective enough because `order_id` has low cardinality.
   • DThe clustering key was defined on a different column than the one used in the query filter.
   Reveal Solution Discussion Select an answer first
5. 19application · medium

   A healthcare analytics team has a table `patient_visits` with 800 million rows. Queries commonly filter on `visit_date` (DATE, ~2,000 distinct values) and `patient_id` (NUMBER, ~40 million distinct values). The team also frequently filters on `facility_id` (NUMBER, ~500 distinct values) in reports. The table is currently unclustered. The team wants to minimize query latency for the most common filters while keeping clustering maintenance costs reasonable. Which clustering key should they choose?

   • ACluster on `visit_date` and `patient_id` as a composite key, with `visit_date` first.
   • BCluster on `facility_id` only, since it has the lowest cardinality and will create the fewest micro-partitions.
   • CCluster on `patient_id` only, since it has the highest cardinality and will provide the finest granularity.
   • DCluster on `patient_id` and `facility_id` as a composite key, with `patient_id` first.
   Reveal Solution Discussion Select an answer first
6. 20foundation · easy

   Which query type is most likely to benefit from the Search Optimization Service?

   • AA query that joins two large tables on a non-key column
   • BA query that performs a full table scan for a large aggregation
   • CA query that uses a LIKE pattern with a leading wildcard
   • DA query that filters on a high-cardinality column with an equality predicate
   Reveal Solution Discussion Select an answer first