COF-C03 - Performance Optimization, Querying, and Transformation (21% of the exam) - Section 4.3

Optimise performance with clustering keys, materialized views, the search optimization service, and the query acceleration service.

Apply the advanced optimisation features and know when each pays off: a clustering key with automatic clustering on a very large table with selective filters, materialized views for repeated expensive aggregations, the search optimization service for selective point lookups, and the query acceleration service for queries with large scans or unpredictable bursts. Recognise the cost trade-off of each.

clustering keysautomatic clusteringmaterialized viewssearch optimization servicequery acceleration service

Practice question for this objective

Free samplePerformance Optimization, Querying, and Transformationhard

A customer lookup table holds hundreds of millions of rows, and a support application issues many highly selective queries that filter on an indexed-style equality predicate against a high-cardinality account_id, each returning only a handful of rows. The clustering key is already set on a different reporting column that the team will not change. Which feature best accelerates these selective point lookups without altering the existing physical clustering?

ARe-cluster the table by replacing the clustering key with account_id so automatic clustering co-locates each account's rows and the lookups prune effectively
BEnable the search optimization service on the table so its persistent search access paths prune to the few micro-partitions holding each selective account_id lookup Correct
CBuild a materialized view keyed on account_id so the support queries read precomputed lookup results instead of scanning the large base customer table directly
DTurn on the query acceleration service for the warehouse so extra serverless compute scans the large table faster for each selective account_id lookup

Identify the search optimization service as the way to speed selective point lookups without changing a table's clustering key. The search optimization service maintains a separate persistent data structure of search access paths that lets highly selective equality and point-lookup queries skip micro-partitions that cannot match. Because it operates alongside the table rather than reorganising it, it accelerates account_id lookups while leaving the existing clustering on the reporting column unchanged.

Why A is wrong: Changing the clustering key to account_id could help, but the stem fixes the existing clustering column, and re-clustering reorganises the whole table rather than leaving the physical layout untouched.

Why B is correct: Search optimization builds and maintains search access paths that make selective equality and point-lookup queries scan only the relevant micro-partitions, accelerating them without touching the clustering key.

Why C is wrong: A materialized view precomputes a query's results and adds storage and refresh cost, but it is not designed to serve arbitrary selective point lookups across many distinct account_id values.

Why D is wrong: Query acceleration offloads large scan-heavy work to serverless compute, which suits big scans, not tiny selective lookups that already return only a handful of rows.

See more COF-C03 practice questions, answers explained.

Exam traps in Performance Optimization, Querying, and Transformation

Answers that look right on this material and are not. Each one is a distractor from a different question in the COF-C03 bank for this domain.

Broad full-table scans that read most micro-partitions for a monthly aggregation, because the service rewrites the scan to read the whole table from serverless compute faster.
Why it is wrong: Search optimization helps queries that touch a small fraction of rows, not broad scans that read most of the table, and it does not offload scans to serverless compute, so this confuses it with the query acceleration service.
Replace automatic clustering with the search optimization service on the table, because its access paths remove all reclustering credits while preserving range-scan pruning
Why it is wrong: Search optimization is a separate maintained structure with its own cost and targets selective lookups, so it neither eliminates the need for clustering nor preserves range-scan pruning the same way.
Periodically run a manual INSERT OVERWRITE that re-sorts the whole table by event_timestamp on a schedule, so each rewrite restores ordering after every load
Why it is wrong: A scheduled full rewrite does sort the data, but it is heavy manual maintenance that reprocesses the entire table and is exactly the ongoing effort the team wants to avoid.

Examworthy is not affiliated with or endorsed by Snowflake. Original, blueprint-aligned practice material only.