SnowPro Advanced: Data Engineer · 25% of the exam

Performance and Troubleshooting: free practice questions

5 sample questions from our 63-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Snowflake exam.

1. An engineer wants to attribute warehouse credit consumption to specific business units by tagging queries at runtime. After setting the session parameter QUERY_TAG = 'BU:Finance' before a batch of queries, where can this tag be found for post-hoc analysis, and what is a key limitation of this approach?

A. The tag appears in ACCOUNT_USAGE.QUERY_HISTORY.QUERY_TAG; the key limitation is that QUERY_TAG values are limited to 50 characters and cannot contain special characters
B. The tag appears in ACCOUNT_USAGE.QUERY_HISTORY.QUERY_TAG; the key limitation is that this column is only populated for queries run by ACCOUNTADMIN or SYSADMIN roles
C. The tag appears in ACCOUNT_USAGE.QUERY_HISTORY.QUERY_TAG; a key limitation is that the tag must be set per session and does not persist across sessions or connections, requiring every client connection to explicitly set it before execution✓ Correct
D. The tag appears in ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY as a labeled dimension; the limitation is a 24-hour delay before tagged credits appear in that view

Explanation

QUERY_TAG is a session-level parameter stored in ACCOUNT_USAGE.QUERY_HISTORY.QUERY_TAG. It is an effective attribution tool for BI tools or ETL frameworks that can inject the tag at connection time. Its primary operational limitation is that it is session-scoped: every new connection or session must explicitly set the parameter, making it fragile in connection-pooling environments where sessions are reused across business units without being reset. Why the distractors are wrong: - QUERY_TAG supports up to 2,000 characters and can contain most printable characters including colons, slashes, and JSON; the 50-character / special character restriction is incorrect. - QUERY_TAG is available regardless of role; it is not restricted to ACCOUNTADMIN or SYSADMIN. - WAREHOUSE_METERING_HISTORY records credit consumption aggregated by warehouse and does not store per-query tags; there is no QUERY_TAG dimension in that view.

2. An engineer reviews the Query Profile of a nightly aggregation job and sees that one of eight warehouse nodes shows 'Rows processed: 4,200,000,000' while the other seven nodes each show approximately 150,000,000 rows. The query groups by a STATUS column that contains only three distinct values: 'ACTIVE', 'INACTIVE', and 'PENDING', with 'ACTIVE' representing 95% of all rows. Which approach BEST addresses the root cause of this skew?

A. Increase the warehouse size to XXXL to provide more total memory and distribute the skewed partition across more nodes.
B. Pre-aggregate the STATUS column into a separate summary table and join it at query time to avoid full scans.
C. Rewrite the query to process the dominant STATUS value ('ACTIVE') in a separate branch, then UNION ALL the results of the other two values processed in a second pass.✓ Correct
D. Cluster the table on the STATUS column so that Snowflake can prune partitions more effectively during the GROUP BY.

Explanation

The skew is caused by data distribution imbalance: one worker receives the vast majority of rows because hash-based partitioning of a low-cardinality column concentrates 95% of rows on a single node. Splitting the dominant value into a separate branch (option C) processes 'ACTIVE' rows independently and then combines results with UNION ALL, distributing work evenly. Simply increasing warehouse size (option A) does not fix the distribution imbalance—the same node still receives the same proportion of rows. Pre-aggregating into a summary table (option B) changes the pipeline architecture significantly and may not be applicable for dynamic aggregation needs. Clustering on STATUS (option D) helps partition pruning for filter predicates but does not change how the hash-based GROUP BY distributes rows across nodes.

3. A Data Engineer is troubleshooting a Stream-based pipeline. They query INFORMATION_SCHEMA.STREAMS and find STALE = TRUE for stream STR_ORDERS. Which THREE statements correctly describe the implications and remediation steps?

A. The stream's offset has fallen outside the data retention period of the source table, and change data captured before the stale point is permanently lost.✓ Correct
B. The stream can be refreshed by calling SYSTEM$STREAM_BACKFILL() to reconstruct lost change records from Time Travel.
C. To recover the pipeline, the engineer must recreate the stream, which resets the offset to the current table version.✓ Correct
D. Any task that consumes the stale stream will fail with an error rather than silently skip the missing change records.
E. Increasing the DATA_RETENTION_TIME_IN_DAYS on the source table retroactively prevents the stream from having become stale.
F. After recreating the stream, the engineer should perform a full reload of the target table to ensure consistency, since the gap in change data cannot be replayed.✓ Correct

Explanation

Option A is correct: a stream becomes STALE when the source table's data retention period has been exceeded; the change records for the period before the stale point are gone and cannot be recovered. Option C is correct: the only way to resume a stale stream is to DROP and recreate it; recreation resets the offset to NOW (current table version), meaning historical changes are lost. Option F is correct: because the gap in CDC records cannot be replayed, the target table may be out of sync, so a full reload (or reconciliation) from the source is necessary to ensure consistency. — Option B is wrong: there is no SYSTEM$STREAM_BACKFILL() function in Snowflake; stale stream data cannot be reconstructed. — Option D is wrong: tasks consuming a stale stream raise an error (stream is stale), but the phrasing 'silently skip' is what they do NOT do — they do raise errors, which is partially true, but Option D's full implication that tasks always fail cleanly rather than having partial issues is misleading; more importantly Option D is not a remediation step and the set of three correct answers is A, C, F. — Option E is wrong: increasing DATA_RETENTION_TIME_IN_DAYS only affects future retention; it cannot retroactively extend the retention window that already elapsed and caused the stream to become stale.

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

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.

5. A Data Engineer runs SYSTEM$CLUSTERING_DEPTH('SALES', '(SALE_DATE, REGION)') and receives the result: {"average_depth": 8.4, "average_depth_without_overlaps": 1.0}. The table is 800 GB and is queried predominantly with WHERE SALE_DATE = :date AND REGION = :region filters. What does this output indicate, and what action should the engineer take?

A. The table is well-clustered; average_depth of 8.4 means 8.4 micro-partitions must be scanned per query on average, which is acceptable for 800 GB.
B. The table is poorly clustered; the high average_depth with overlapping values (8.4) indicates significant micro-partition overlap on the clustering keys, and the engineer should run ALTER TABLE SALES CLUSTER BY (SALE_DATE, REGION) to enable Automatic Clustering.✓ Correct
C. The result indicates that 8.4% of micro-partitions overlap, which is within acceptable bounds; no action is needed.
D. The average_depth_without_overlaps of 1.0 means the data is perfectly sorted when overlaps are excluded; the engineer should recluster using REGION as the leading key to reduce depth.

Explanation

SYSTEM$CLUSTERING_DEPTH returns the average depth of micro-partitions for the given clustering keys. An average_depth of 8.4 means that, on average, a single value combination spans 8.4 micro-partitions, indicating significant overlap and poor clustering effectiveness—queries must scan many more micro-partitions than necessary. The average_depth_without_overlaps of 1.0 confirms that if overlaps were eliminated, each value would map to exactly one partition, representing ideal clustering. The recommended action is to enable Automatic Clustering (ALTER TABLE … CLUSTER BY …) so Snowflake continuously reclusters the table. Option A misinterprets depth as partitions scanned per query—depth reflects overlap, not scan count. Option C incorrectly interprets depth as a percentage. Option D misreads the without_overlaps figure as a reason to change the key order rather than as a target state.

58 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free