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

Questions 1–5

1. 1application · medium

   A data engineer is monitoring a clustered table and notices that the clustering depth has increased from 2 to 8 over the past month. The table receives frequent inserts and updates. The engineer wants to understand why the clustering depth is increasing. What is the most likely cause?

   • AThe warehouse size used for queries was reduced, slowing down the clustering maintenance.
   • BThe clustering key was dropped and re-created on a different column.
   • CThe automatic reclustering process is not keeping up with the rate of DML operations.
   • DThe table's storage size has increased, causing the clustering depth to grow proportionally.
   Reveal Solution Discussion Select an answer first
2. 2foundation · easy

   What is a potential cost trade-off of enabling clustering on a Snowflake table?

   • AIncreased storage costs due to duplicate data in multiple micro-partitions
   • BAdditional compute costs for automatic reclustering operations
   • CIncreased licensing fees for using the clustering feature
   • DHigher egress costs for data transfer to the cloud provider
   Reveal Solution Discussion Select an answer first
4. 3expert · medium

   A large e-commerce company has a table `orders` with 10 billion rows, clustered on `order_date`. The table receives continuous inserts throughout the day. The clustering depth has degraded to 20, and queries on `order_date` are becoming slower. The company has a limited budget and wants to reduce query latency without significantly increasing compute costs. The data team has proposed three options: (1) run manual reclustering nightly, (2) rely on automatic reclustering, or (3) increase the warehouse size for queries. Which option is the most cost-effective approach?

   • ARely on automatic reclustering, which runs in the background and only reclusters the portions of the table that need it.
   • BIncrease the warehouse size for queries to compensate for the poor clustering.
   • CRun manual reclustering nightly to ensure the table is always well-clustered.
   • DDrop the clustering key and rely on Snowflake's automatic micro-partition pruning.
   Reveal Solution Discussion Select an answer first
5. 4application · medium

   A data engineer is explaining to a colleague how clustering keys work in Snowflake. The colleague asks: 'Why does clustering improve query performance on large tables?' The engineer should explain that clustering keys:

   • ACreate indexes on the clustered columns, similar to a traditional database index.
   • BPhysically order the data in micro-partitions so that queries can prune partitions based on metadata.
   • CCompress the data in the table to reduce the storage footprint and speed up scans.
   • DCopy the data into a separate, faster storage tier for query acceleration.
   Reveal Solution Discussion Select an answer first
6. 5foundation · easy

   How does a clustering key improve query performance in Snowflake?

   • ABy caching the key columns in memory for all queries
   • BBy creating a secondary index on the key columns for faster row lookups
   • CBy compressing the key columns to reduce the data scanned
   • DBy reducing the number of micro-partitions scanned for queries with predicates on the key columns
   Reveal Solution Discussion Select an answer first