Snowflake SnowPro Advanced: Data Engineer (DEA-C02) · 20% practice allocation

Snowflake DEA-C02: Performance Optimization Practice Questions

5 free sample questions from the Performance Optimization domain, with answers and explanations. The full DEA-C02 bank has 262 questions across 5 domains. Questions are scenario-based and written from the official Snowflake exam guide.

1.

A Data Engineer is investigating skewed execution in a query that aggregates 10 billion rows by PRODUCT_CATEGORY. The Query Profile shows that one thread processed 6.2 billion rows while all other threads processed between 200–400 million rows each. The PRODUCT_CATEGORY column has only 4 distinct values, and one category represents 62% of all rows. Which TWO strategies are MOST effective for remediating this data skew? (Select TWO.)

  • A. Enable query acceleration service (QAS) on the warehouse, which offloads disproportionate partition workloads to a separate serverless compute tier
  • B. Convert the aggregation to use APPROXIMATE_COUNT_DISTINCT instead of COUNT(*) to reduce the memory footprint of the large group
  • C. Apply a selective filter on the skewed category value first, process it in a separate query, and UNION ALL the results with the remaining categories✓ Correct
  • D. Add a salting column by appending a random suffix (e.g., PRODUCT_CATEGORY || '_' || (RANDOM() % 10)) to distribute the dominant key across more buckets, then re-aggregate in a second pass✓ Correct
  • E. Increase the warehouse size so that each node has more RAM, allowing the dominant partition to fit entirely in memory without spilling
Explanation

When a single GROUP BY key value holds a disproportionate share of data, hash-based aggregation assigns all rows for that key to one thread, causing skew. Two effective strategies:

  1. Salting: Artificially expanding the key space (appending a random suffix) breaks the dominant bucket into smaller sub-buckets that can be processed in parallel; a second aggregation merges the partial results.
  2. Divide and conquer: Isolating the dominant category into its own query allows its volume to be processed with full parallelism across partitions, then UNION ALL combines results.

Why the distractors are wrong:

  • A larger warehouse reduces spilling but does not fix the fundamental skew; one thread still handles all 6.2B rows.
  • QAS helps accelerate specific scan/aggregation patterns but does not redistribute skewed GROUP BY keys across threads.
  • APPROXIMATE_COUNT_DISTINCT changes query semantics (approximate vs. exact) and only applies to COUNT DISTINCT, not general aggregation skew.
2.

A Data Engineer notices that a query against a 500-million-row table completes in under 100 milliseconds and returns identical results to a query run two minutes ago. No DML has occurred on the table since the first execution. Which Snowflake caching layer is MOST likely responsible for this behavior?

  • A. Remote storage cache, which prefetches frequently accessed S3/Azure/GCS objects into memory
  • B. Metadata cache, which satisfies the query entirely from partition statistics stored in the Cloud Services layer
  • C. Result cache (global services layer), which returns the cached result set without engaging any compute✓ Correct
  • D. Warehouse local SSD cache (disk cache), which stores raw micro-partition data on the virtual warehouse nodes
Explanation

The result cache (held in the Cloud Services layer) returns an identical cached result set for a re-executed query when: (1) the query text is identical, (2) no DML/DDL has changed the underlying data, and (3) the result is still within the 24-hour TTL. Because the result is returned without spinning up or billing any compute, sub-100 ms latency is characteristic.

Why the distractors are wrong:

  • Warehouse SSD cache stores columnar micro-partition data on virtual warehouse nodes and still requires compute to process it; it reduces I/O but does not eliminate execution.
  • Metadata cache can answer COUNT(*) or MIN/MAX queries without reading data files, but a general SELECT with arbitrary predicates still needs compute.
  • 'Remote storage cache' is not a distinct Snowflake caching layer; Snowflake does not expose a named prefetch cache from cloud object storage.
3.

A query's Query Profile shows the following statistics for the TableScan operator on table ORDERS: Partitions scanned: 8,400 | Partitions total: 8,400 | Bytes scanned: 2.1 TB. The table is clustered on (ORDER_DATE). The analyst's query filters on CUSTOMER_REGION only. Which remediation will MOST directly reduce partition pruning waste for this query pattern?

  • A. Re-cluster the table on (CUSTOMER_REGION) or add it as the leading clustering key, replacing ORDER_DATE✓ Correct
  • B. Enable automatic clustering on ORDER_DATE with a higher target cluster depth to improve existing key selectivity
  • C. Increase the virtual warehouse size from LARGE to X-LARGE to parallelize the full scan more efficiently
  • D. Convert the table to a transient table to eliminate Time Travel overhead and reduce bytes scanned
Explanation

The profile shows 100% of partitions are scanned (8,400/8,400), meaning zero pruning is occurring. The existing cluster key (ORDER_DATE) does not help a filter on CUSTOMER_REGION because the physical ordering does not correlate with region values. Re-clustering on CUSTOMER_REGION (or making it the leading key) will co-locate rows by region, enabling Snowflake's micro-partition metadata to prune irrelevant partitions when CUSTOMER_REGION is filtered.

Why the distractors are wrong:

  • Increasing warehouse size speeds up a full scan but does not reduce the amount of data scanned; it is a cost-scaling fix, not a pruning fix.
  • Increasing cluster depth on ORDER_DATE further optimizes time-based pruning but does nothing for CUSTOMER_REGION filters.
  • Transient tables remove Fail-safe and reduce storage cost, but they do not affect micro-partition layout or pruning.
4.

While reviewing ACCOUNT_USAGE.QUERY_HISTORY, a Data Engineer sees that a single recurring ETL query has BYTES_SPILLED_TO_LOCAL_STORAGE > 0 and BYTES_SPILLED_TO_REMOTE_STORAGE > 0 on every execution. The query performs a large hash join between two unfiltered, 200 GB tables. Which TWO actions are most likely to eliminate or significantly reduce remote disk spilling for this query? (Select TWO.)

  • A. Enable query acceleration service (QAS) on the warehouse to offload the spilling portions to the serverless QAS layer
  • B. Enable the result cache by ensuring the query is executed by the same role each time
  • C. Pre-filter or aggregate the larger table in a CTE or subquery before the join to reduce the data volume entering the hash build phase✓ Correct
  • D. Set STATEMENT_TIMEOUT_IN_SECONDS to a lower value to force the query to fail fast and retry on a warmer cache
  • E. Upgrade the virtual warehouse to a larger size (e.g., from MEDIUM to LARGE or X-LARGE) to increase available memory per node✓ Correct
Explanation

Spilling to remote storage occurs when the in-memory and local SSD buffers on virtual warehouse nodes are exhausted. Two proven remedies are:

  1. Larger warehouse size — more nodes means more aggregate RAM, so the hash join build phase fits in memory.
  2. Reducing data volume before the join — filtering or aggregating in a CTE shrinks the rows participating in the hash join, reducing memory pressure at the join operator itself.

Why the distractors are wrong:

  • Result cache applies only to identical, repeated queries with no intervening DML; it does not help with the internal spilling of a running query, and the role has no impact on spilling.
  • Lowering STATEMENT_TIMEOUT just kills the query sooner; it does not address the root cause.
  • QAS accelerates specific operators but does not directly eliminate hash-join spilling; it helps with partial scans and lightweight aggregations, not memory-bound hash joins.
5.

A Data Engineer wants to monitor warehouse credit consumption with 5-minute granularity to identify peak usage windows and correlate them with specific query workloads. Which view provides per-warehouse credit usage at the finest available time granularity in Snowflake?

  • A. INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY, which provides per-minute credit granularity for the last 14 days.
  • B. ACCOUNT_USAGE.QUERY_HISTORY joined with ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY to compute rolling 5-minute averages.
  • C. ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY, which provides per-second credit granularity.
  • D. ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY, which provides per-hour credit granularity.✓ Correct
Explanation

ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY records credit consumption at one-hour granularity per warehouse. This is the finest granularity available in the metering history views for credit accounting. Option C is wrong because neither the ACCOUNT_USAGE nor INFORMATION_SCHEMA metering views provide per-second granularity; Snowflake bills and reports at the hour level in metering history. Option B is wrong because while QUERY_HISTORY contains per-query CREDITS_USED_COMPUTE and timestamps that can be aggregated into custom windows, WAREHOUSE_LOAD_HISTORY measures concurrency (query count), not credits, and joining them does not yield native 5-minute credit windows. Option A is wrong because INFORMATION_SCHEMA.WAREHOUSE_METERING_HISTORY also provides hourly granularity (not per-minute), and its 14-day retention is a correct fact but the granularity claim is false.

Test yourself across every DEA-C02 domain

The free DEA-C02 practice test mixes about 20 questions weighted like the exam. The full 262-question bank adds instant grading, flashcards and a timed mock exam.

Take the free DEA-C02 practice testPractice the full bank

Study this domain

Performance Optimization study guide (DEA-C02) →

Other DEA-C02 domains

← All Snowflake SnowPro Advanced: Data Engineer study resources