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

Transform data with SQL using aggregate functions, window functions, joins, and cardinality-estimation functions.

Transform and analyse data with SQL: aggregate functions with GROUP BY, window functions for running totals and rankings over partitions, the join types Snowflake supports, and estimation functions such as APPROX_COUNT_DISTINCT and APPROX_PERCENTILE for fast approximate answers on very large data. Match a transformation need to the right construct.

aggregate functionswindow functionsjoinsGROUP BYAPPROX_COUNT_DISTINCT

Practice question for this objective

Free samplePerformance Optimization, Querying, and Transformationmedium

An analyst must report the number of distinct visitor identifiers in a clickstream table that holds many billions of rows. An exact figure is not required, and the team wants the count to return as quickly and cheaply as possible while accepting a small margin of error. Which function should the analyst use to satisfy this requirement?

ACOUNT(DISTINCT visitor_id), because computing the exact count of unique identifiers is the only supported way to measure cardinality across billions of rows in Snowflake
BCOUNT(visitor_id) with a DISTINCT clause in the surrounding GROUP BY, because grouping first reduces the rows so the final count is both exact and fast
CAPPROX_COUNT_DISTINCT(visitor_id), because it estimates the number of distinct values using a sketch and runs far faster and cheaper than an exact distinct count Correct
DAPPROX_PERCENTILE(visitor_id, 0.5), because the median identifier value is an accepted proxy for how many unique visitors appear in the table

Choose APPROX_COUNT_DISTINCT when an approximate distinct count is acceptable and speed and cost matter more than an exact figure. APPROX_COUNT_DISTINCT implements the HyperLogLog algorithm, maintaining a compact probabilistic sketch instead of every unique value, so it estimates cardinality in one pass with far less memory and compute than COUNT(DISTINCT). That trade of a small error for large speed and cost savings is precisely what the scenario calls for on a billions-of-rows table.

Why A is wrong: An exact COUNT(DISTINCT) must track every unique value, which is memory heavy and slow on billions of rows, so it conflicts with the stated goal of a fast, cheap approximate count.

Why B is wrong: Adding a GROUP BY still forces Snowflake to materialise every distinct group, so it does not avoid the cost of an exact distinct count and remains slow at this scale.

Why C is correct: APPROX_COUNT_DISTINCT uses a HyperLogLog sketch to estimate cardinality with low memory in a single pass, returning a close approximate count quickly, which matches the requirement exactly.

Why D is wrong: APPROX_PERCENTILE estimates a percentile of a numeric distribution, not the number of distinct values, so its result has nothing to do with visitor cardinality.

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.

It samples a fixed one percent of the rows and scales the distinct count it sees up to the full table, so its accuracy depends entirely on how representative that sample happens to be.
Why it is wrong: APPROX_COUNT_DISTINCT does not take a fixed-rate row sample and scale up; it processes all rows through a sketch, so attributing its accuracy to sample representativeness misdescribes the algorithm.
A materialized view must be defined as a secure view, because Snowflake refuses to maintain the precomputed results of any materialized view whose definition is visible to consumers.
Why it is wrong: Materialized views can optionally be made secure, but security is not a precondition for maintenance, so claiming Snowflake refuses to maintain a non-secure materialized view misstates the rules.
The query result cache, because the cloud services layer had already stored this exact count from a prior identical run and replayed it to the analyst
Why it is wrong: The result cache only helps if an identical query ran before, but the instant answer here comes from metadata even on a first run, so this is not the direct cause.

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