COF-C03 domain - 21% of the exam

Performance Optimization, Querying, and Transformation

Performance Optimization, Querying, and Transformation is 21% of the COF-C03 exam. These are the objectives it covers, each with practice questions, with every answer explained.

The domain in numbers

58
Practice questions
6
Objectives
48
Medium
10
Hard

Where people go wrong

  • Adding clusters to speed up one query

    The wrong answers reach for multi-cluster scaling, extra clusters or a higher maximum cluster count when a single query spills or scans slowly. A query runs on one cluster only, and every cluster in a multi-cluster warehouse is the same size, so extra clusters add concurrency capacity and give that query no more memory or scan throughput. Resizing to a larger warehouse adds memory and local disk and reduces the spilling. Query acceleration borrows serverless compute for an outlier scan.

    See it in a sample question
  • Crediting the wrong cache

    The wrong answers give a behaviour to the wrong cache. The query result cache sits in the cloud services layer and returns a stored result for an identical query on unchanged data for up to 24 hours, with no running warehouse. The metadata cache holds statistics such as row counts, so COUNT(*) resolves without a warehouse. The local disk cache holds micro-partitions read by a running warehouse, helps overlapping non-identical scans, and is lost on suspend.

    See it in a sample question
  • Filtering with a clause from the wrong stage

    The wrong answers pick a clause that runs at the wrong stage. WHERE filters individual rows before grouping and before window functions are computed, so it cannot reference SUM or ROW_NUMBER. HAVING filters grouped rows on an aggregate such as SUM(sale_amount) > 10000 but cannot reference a window function. QUALIFY filters on a window function result in the same SELECT, so QUALIFY ROW_NUMBER() OVER (...) = 1 keeps one row per customer without a subquery.

    See it in a sample question
  • Treating Query Profile or RESULT_SCAN as a query history

    The wrong answers treat a single-query tool as a history log. Query Profile shows the execution plan of one chosen query at a time and is not a queryable table, so it cannot list nine months of account queries or one user's recent runs. RESULT_SCAN returns the cached result set of one prior query by its id, not a record of executions. Listing past queries needs a history source: the ACCOUNT_USAGE QUERY_HISTORY view for long account-wide reporting, or an INFORMATION_SCHEMA function such as QUERY_HISTORY_BY_USER for recent runs.

    See it in a sample question
  • Swapping clustering and search optimization

    The wrong answers swap clustering and search optimization. A clustering key physically co-locates micro-partitions on a column, which suits ranged filters such as event_timestamp and is kept current by automatic clustering. Search optimization builds separate access paths for selective equality lookups on a high-cardinality column such as account_id and does not reorganise the table. Query acceleration, a third feature, offloads broad scans rather than lookups that return a handful of rows.

    See it in a sample question

Objectives in this domain

Sample questions from this domain

Free samplePerformance Optimization, Querying, and Transformationhard

An analyst occasionally runs ad hoc queries that each scan a very large table and spill heavily because a single warehouse cluster lacks the scan throughput, yet these queries are infrequent so the team does not want to pay for a permanently larger warehouse. They want these outlier scan-heavy queries to borrow additional compute only while they run. Which feature best addresses this scenario?

  • AConvert the warehouse to a multi-cluster warehouse in auto-scale mode so additional clusters start and absorb each outlier scan-heavy query as it runs
  • BAdd the search optimization service to the large table so the infrequent ad hoc scans prune to fewer micro-partitions and finish without spilling to remote storage
  • CDefine a clustering key on the table so automatic clustering co-locates the data and each large ad hoc scan reads fewer micro-partitions and avoids spilling
  • DEnable the query acceleration service on the warehouse so eligible scan-heavy queries offload portions of their work to serverless compute only while they run Correct
Identify the query acceleration service as the way to give occasional large-scan queries extra serverless compute only during execution. The query acceleration service detects eligible portions of large scan-heavy queries and offloads them to shared serverless compute that is billed only for the work performed during the query. This lets infrequent outlier queries run faster without permanently sizing up the warehouse, whereas scaling out, search optimization, and clustering target concurrency or selective access rather than borrowing scan compute on demand.

Why A is wrong: Multi-cluster warehouses add clusters to handle concurrent queries, but a single large scan still runs on one cluster, so extra clusters do not give that one query more scan throughput.

Why B is wrong: Search optimization accelerates selective lookups, not broad full-table scans, so it does not help ad hoc queries that legitimately read most of a very large table.

Why C is wrong: Clustering only helps when queries filter on the clustering key, so it does little for unpredictable ad hoc scans that read large fractions of the table regardless of order.

Why D is correct: Query acceleration lets eligible large-scan queries borrow serverless compute for parts of their work during execution, speeding outliers without paying for a permanently bigger warehouse.

Free samplePerformance Optimization, Querying, and Transformationmedium

Analysts run many different but overlapping ad hoc queries that scan the same large fact table all day, each filtering on slightly different conditions so no two queries are textually identical. They want repeated scans of that data to read faster without paying to fetch the same micro-partitions from remote storage every time. Which cache best serves this pattern?

  • AThe query result cache, because each new ad hoc query reuses the persisted result of the earlier non-identical queries against the same table
  • BThe metadata cache, because the cloud services layer answers each ad hoc scan from stored object statistics rather than reading the micro-partitions
  • CThe query result cache, because keeping the warehouse running lets it persist each varied result for up to 24 hours in the cloud services layer
  • DThe virtual warehouse local disk cache, because a warm warehouse keeps recently read micro-partitions on SSD so later overlapping scans avoid refetching them Correct
Match the virtual warehouse local disk cache to repeated non-identical scans of the same data that benefit from a warm warehouse. When queries are not textually identical the result cache cannot help, but a continuously running warehouse retains recently read micro-partitions in its local SSD cache, so overlapping scans of the same fact table read those partitions from SSD instead of refetching them from remote storage.

Why A is wrong: The result cache only helps byte-for-byte identical queries, so non-identical ad hoc queries cannot reuse one another's results and this does not fit.

Why B is wrong: The metadata cache only answers simple statistic queries; full ad hoc scans with varied filters must read partitions, so it cannot serve this workload.

Why C is wrong: A running warehouse does not make the result cache match non-identical queries, and result reuse still needs identical text, so this reasoning is wrong.

Why D is correct: The local disk cache holds recently read micro-partitions on warehouse SSD, so overlapping non-identical scans of the same data read from SSD rather than remote storage, matching the need.

Free samplePerformance Optimization, Querying, and Transformationmedium

A query groups sales by product category and must return only those categories whose total sales exceed 10000. The threshold compares against the aggregated total for each category, not against any individual sale. Which clause correctly applies this filter on the grouped result?

SELECT category, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY category
  • AAdd WHERE SUM(sale_amount) > 10000 before the GROUP BY, because the WHERE clause is where aggregate totals for each category are compared against the threshold
  • BAdd WHERE sale_amount > 10000 before the GROUP BY, because filtering each sale above the threshold first leaves only the categories whose totals exceed 10000
  • CAdd HAVING SUM(sale_amount) > 10000 after the GROUP BY, because HAVING filters the grouped rows using the aggregated total computed for each category Correct
  • DAdd QUALIFY SUM(sale_amount) > 10000 after the GROUP BY, because QUALIFY is the clause designed to filter the totals produced by a GROUP BY aggregation
Use HAVING, not WHERE, to filter grouped results on an aggregate value computed per group. WHERE filters individual rows before aggregation and cannot reference aggregate functions, whereas HAVING is evaluated after GROUP BY and can compare against aggregates like SUM. Because the threshold tests each category's total rather than any single sale, HAVING SUM(sale_amount) > 10000 is the correct clause, while QUALIFY targets window functions and so does not apply here.

Why A is wrong: WHERE filters individual rows before grouping and cannot reference an aggregate such as SUM, so this clause raises an error rather than filtering category totals.

Why B is wrong: Filtering single sales above 10000 changes which rows are aggregated and does not test the category total, so the surviving categories can have totals far above or below 10000.

Why C is correct: HAVING runs after grouping and can reference aggregates, so HAVING SUM(sale_amount) > 10000 keeps only the categories whose computed total exceeds the threshold, exactly as required.

Why D is wrong: QUALIFY filters rows based on window function results, not on GROUP BY aggregates, so it is the wrong clause for testing a grouped category total.

Free samplePerformance Optimization, Querying, and Transformationmedium

An auditor needs to review every query run across the whole account during the past nine months, including queries from users and sessions that have long since ended. They will run reporting on this history and can accept some delay before recent queries appear. Which source should they query to meet this need?

  • AThe INFORMATION_SCHEMA.QUERY_HISTORY table function, because it returns the full account query history for the past several months with no latency on recent rows
  • BThe SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view, because it retains account-wide query history for a year and is queryable with some latency before recent rows appear Correct
  • CThe Query Profile graph in Snowsight for each query, because it stores the operator tree for every account query for up to a year for later auditing
  • DThe RESULT_SCAN table function over the last query id, because it replays the stored result set of every query the account ran over the previous nine months
Choose the ACCOUNT_USAGE.QUERY_HISTORY view for long-retention, account-wide query reporting where some data latency is acceptable. The SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view retains query history across the entire account for one year, which suits long-range auditing. It carries a documented latency before the most recent queries appear, whereas INFORMATION_SCHEMA functions are low latency but limited to a short window and a narrower scope, so they cannot cover a nine-month audit.

Why A is wrong: The INFORMATION_SCHEMA function is scoped and time-limited to the last seven days or fewer, so it cannot return a nine-month account-wide history for the audit.

Why B is correct: The ACCOUNT_USAGE view keeps one year of account-wide query history and is the correct reporting source, with a known latency before the newest queries are reflected.

Why C is wrong: Query Profile shows the execution plan for one query at a time and is not a queryable historical table, so it cannot report on nine months of account-wide queries.

Why D is wrong: RESULT_SCAN returns the cached result of one recent prior query by its id, not a long historical log, so it cannot supply a nine-month account audit.

Free samplePerformance Optimization, Querying, and Transformationhard

A multi-terabyte events table is queried almost exclusively with predicates that filter on a single high-cardinality event_timestamp column, but the table is loaded in random order so scans read most micro-partitions. The team wants Snowflake to keep the data physically co-located on event_timestamp over time as new rows arrive, with the least ongoing operational effort. Which approach best meets this requirement?

  • ADefine a clustering key on event_timestamp so automatic clustering reorganises micro-partitions in the background as data changes, keeping the table well clustered Correct
  • BPeriodically 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
  • CAdd the search optimization service to the table so its per-column search access paths keep results filtered by event_timestamp without reorganising any micro-partitions
  • DCreate a materialized view that selects from the table ordered by event_timestamp, so queries read the pre-sorted view instead of the unordered base table
Recognise that a clustering key with automatic clustering maintains physical co-location of a large table as data changes with minimal manual effort. Defining a clustering key lets Snowflake's automatic clustering service reorganise micro-partitions in the background so rows that share the clustering key value stay co-located as the table is loaded and updated. This improves partition pruning for predicates on that key without the team running manual rewrites, which is why it beats scheduled re-sorts or features that do not change physical layout.

Why A is correct: A clustering key with automatic clustering continuously reorganises micro-partitions on event_timestamp as new rows land, improving pruning for the timestamp filters with no manual maintenance.

Why B 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.

Why C is wrong: Search optimization builds access paths for selective point lookups and equality filters, but it does not physically co-locate data, and it suits high-cardinality equality rather than ranged timestamp scans.

Why D is wrong: A materialized view cannot guarantee a persisted physical sort order and is meant to precompute results, so it does not maintain physical clustering of the underlying base table.

Other domains in this exam

See also the COF-C03 cert hub, the study guide, and the cheat sheet.

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