Use Clustering and the Search Optimization Service COF-C03 Practice Questions (Page 4)

Questions 16–20

1. 16expert · hard

   A data engineer is monitoring a large `fact_sales` table with a clustering key on `sale_date`. The clustering depth has been steadily increasing over the past month, and query performance has degraded. The table receives frequent inserts and updates. What is the most effective strategy to address this issue?

   • AInvestigate the DML patterns and consider adjusting the clustering key or scheduling manual reclustering during low-activity periods.
   • BChange the clustering key to a different column that is more frequently filtered.
   • CManually recluster the table once and rely on automatic clustering thereafter.
   • DDisable automatic clustering and perform manual reclustering on a nightly schedule.
   Reveal Solution Discussion Select an answer first
2. 17foundation · easy

   Which type of query benefits most from the Search Optimization Service?

   • AA query that aggregates millions of rows, such as SELECT SUM(sales) FROM orders
   • BA query that returns all rows from a table
   • CA query that filters on a column with a highly selective predicate, such as WHERE email = 'user@example.com'
   • DA query that joins two large tables on a non-indexed column
   Reveal Solution Discussion Select an answer first
4. 18foundation · easy

   How does a high clustering depth affect query performance on a clustered table?

   • AIt only affects write performance, not query performance
   • BIt improves performance by making data more compact
   • CIt degrades performance because more micro-partitions must be scanned for a given key value
   • DIt has no impact on query performance
   Reveal Solution Discussion Select an answer first
5. 19application · medium

   A data engineer notices that queries on a large fact table have become slower over the past week. The table has a clustering key on `customer_id`. The engineer checks the clustering depth and finds it has increased significantly. What is the most likely cause and the appropriate next step?

   • AAutomatic clustering has not kept up with DML operations; manually recluster the table.
   • BThe clustering key was poorly chosen; change it to a higher-cardinality column.
   • CThe clustering key is on the wrong column; use a column that is frequently filtered.
   • DThe table is too large; consider splitting it into smaller tables.
   Reveal Solution Discussion Select an answer first
6. 20application · medium

   A financial services firm has a large `transactions` table that is queried with exact-match lookups on `transaction_id`. The table is updated frequently with new transactions. The team wants to improve point lookup performance. Which command should they run?

   • A`ALTER TABLE transactions CLUSTER BY (transaction_id);`
   • B`CREATE INDEX ON transactions (transaction_id);`
   • C`ALTER TABLE transactions ADD SEARCH OPTIMIZATION;`
   • D`ALTER TABLE transactions ENABLE CLUSTERING;`
   Reveal Solution Discussion Select an answer first