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

Questions 11–15

1. 11application · medium

   A logistics company has a table `shipments` with 2 billion rows. The most common queries are: (1) range scans on `shipment_date` to get shipments for a week, and (2) exact-match lookups on `tracking_number` (a high-cardinality string column) to retrieve a single shipment. The table is currently not clustered and does not have Search Optimization enabled. The team wants to improve performance for both query patterns. What is the most effective combination of actions?

   • AEnable Search Optimization on `shipment_date` and add a clustering key on `tracking_number`.
   • BAdd a clustering key on `shipment_date` and enable Search Optimization on `tracking_number`.
   • CAdd a composite clustering key on `shipment_date` and `tracking_number`.
   • DEnable Search Optimization on both `shipment_date` and `tracking_number`.
   Reveal Solution Discussion Select an answer first
2. 12foundation · easy

   Which column is the most appropriate choice for a clustering key on a large fact table that is frequently queried with filters on `order_date` and `customer_id`?

   • Aorder_status, because it has very low cardinality and will group many rows together
   • Border_id, because it is unique and will ensure each micro-partition contains one row
   • Ccustomer_id, because it has high cardinality and will distribute data evenly
   • Dorder_date, because it is a low-cardinality column that will group data well
   Reveal Solution Discussion Select an answer first
4. 13application · medium

   A data engineer notices that a clustered table's `SYSTEM$CLUSTERING_INFORMATION` output shows an average clustering depth of 12, and query performance on the clustered column has degraded noticeably over the past week. The table receives frequent DML operations (INSERT, UPDATE, DELETE) throughout the day. The engineer wants to restore clustering efficiency without disrupting ongoing workloads. What should the engineer do first?

   • ASuspend all DML operations on the table until the clustering depth returns to a value below 4.
   • BCheck whether automatic clustering is enabled and running; if it is, monitor its progress and consider increasing the warehouse size for the automatic clustering task.
   • CDrop the clustering key and re-create it on a different column to reset the clustering state.
   • DManually run `ALTER TABLE ... CLUSTER BY` to force an immediate full recluster.
   Reveal Solution Discussion Select an answer first
5. 14application · medium

   A data engineer enabled the Search Optimization Service on a table `inventory` for the `sku` column. After a few days, the engineer wants to verify that the Search Optimization is actually improving query performance. Which action should the engineer take to measure the benefit?

   • ARun a full table scan query to see if it is faster.
   • BReview the table's storage size to see if it has decreased.
   • CCompare the query execution time for point lookups on `sku` before and after enabling Search Optimization.
   • DCheck the clustering depth of the table to see if it has improved.
   Reveal Solution Discussion Select an answer first
6. 15foundation · easy

   What is the primary purpose of the Search Optimization Service in Snowflake?

   • ATo automatically tune warehouse sizes for optimal performance
   • BTo improve performance of large aggregations and joins
   • CTo compress table data to reduce storage costs
   • DTo accelerate point lookups and small range queries on tables
   Reveal Solution Discussion Select an answer first