SnowPro Core · 15% of the exam

Performance and cost optimization: free practice questions

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

1. A Snowflake table has DATA_RETENTION_TIME_IN_DAYS = 7. An analyst runs a DELETE statement removing 2 million rows, then immediately realizes the deletion was a mistake. The analyst attempts to use Time Travel to restore the rows. Which statement is TRUE about the storage cost implications of this Time Travel usage scenario?

  • A. Using Time Travel to clone or restore data creates a new full copy of the table, doubling the storage cost permanently.
  • B. The deleted rows are retained in Time Travel storage for 7 days and incur storage charges during that period; cloning or querying via Time Travel does not immediately create additional full copies because Snowflake uses metadata pointers to the existing micro-partitions.✓ Correct
  • C. Time Travel storage is free and does not count toward the account's billable storage.
  • D. Once Time Travel is used to restore data, the retention period resets to 7 days from the restore date, causing the original data to be stored for up to 14 days total.
Explanation

When rows are deleted, Snowflake retains the original micro-partitions for the duration of the Time Travel retention period (7 days here), and these partitions do incur storage charges. However, Time Travel queries and clones work via metadata references to the existing micro-partitions — they do not create redundant full data copies at the moment of access. This is an important cost nuance. Option A is incorrect — cloning via Time Travel uses zero-copy cloning with metadata pointers; it does not immediately duplicate all data. Option C is incorrect — Time Travel storage is absolutely billable; it is a significant storage cost factor, especially for high-churn tables with long retention periods. Option D is incorrect — restoring data does not reset or extend the original Time Travel window; the original deleted micro-partitions' retention clock runs from the time of deletion.

2. A Snowflake administrator needs to enforce strict cost controls so that if a specific warehouse exceeds 200 credits in a single day, all running and queued queries are immediately killed. Which Resource Monitor configuration achieves this?

  • A. Set CREDIT_QUOTA = 200, interval = DAILY, and add an action: NOTIFY at 100%.
  • B. Set CREDIT_QUOTA = 200, interval = DAILY, and add an action: SUSPEND_IMMEDIATE at 100%.✓ Correct
  • C. Set CREDIT_QUOTA = 200, interval = DAILY, and add an action: SUSPEND at 100%.
  • D. Set CREDIT_QUOTA = 200, interval = MONTHLY, and add an action: SUSPEND_IMMEDIATE at 100%.
Explanation

Correct (B): SUSPEND_IMMEDIATE kills all currently running and queued queries immediately when the threshold is reached, then suspends the warehouse. Combined with a DAILY interval and a quota of 200, this matches the requirement exactly. Incorrect (A): NOTIFY only sends an alert; it does not stop any queries or suspend the warehouse. Running and queued queries continue uninterrupted. Incorrect (C): SUSPEND waits for currently running queries to complete before suspending the warehouse. This does not 'immediately kill' running queries, which is the stated requirement. Incorrect (D): A MONTHLY interval means the 200-credit quota is measured over the entire month, not per day. The requirement is a daily limit, so the interval must be DAILY.

3. A data platform team is evaluating whether to use a Materialized View or the Search Optimization Service for a 2 TB table that is queried by a BI tool. The queries always aggregate sales data by REGION and PRODUCT_CATEGORY with no row-level filtering. Which recommendation is MOST appropriate?

  • A. Use the Search Optimization Service, because it handles aggregation queries more efficiently than Materialized Views
  • B. Use a Materialized View that pre-aggregates by REGION and PRODUCT_CATEGORY, because it stores the pre-computed aggregates and returns results faster for this fixed query pattern✓ Correct
  • C. Use Clustering Keys on REGION and PRODUCT_CATEGORY, because they eliminate the need for both Materialized Views and Search Optimization
  • D. Use the Query Acceleration Service, because aggregation queries always benefit from offloading to serverless compute
Explanation

Materialized Views are ideal for repeated aggregation queries with a known, fixed GROUP BY pattern. The MV pre-computes and stores the aggregated result; the BI tool's queries are served from the small aggregated dataset rather than scanning 2 TB each time. — Option A is wrong: the Search Optimization Service is designed for selective equality/range lookups on individual rows, not aggregation — it would not meaningfully help aggregation queries. — Option C is wrong: clustering keys improve micro-partition pruning for range/equality filters but do not eliminate the need to scan and aggregate all rows matching a GROUP BY; they would help less than an MV for a full-table aggregation pattern. — Option D is wrong: the Query Acceleration Service (QAS) helps queries with large, selective scans by offloading outlier partitions to serverless compute; it does not pre-compute results and does not specifically target repetitive fixed-pattern aggregations.

4. A query that previously ran in 10 seconds now consistently takes 4 minutes. A developer checks the Query Profile and notices the first run of the day is slow, but subsequent identical runs complete in under 1 second. What is the MOST likely explanation for the fast subsequent runs?

  • A. The Query Acceleration Service caches query plans from the first execution.
  • B. Snowflake's result cache returns the exact query result from the first execution without re-scanning any data, as long as the underlying data has not changed and the result has not expired.✓ Correct
  • C. The virtual warehouse's local disk cache (SSD cache) is populated after the first run, and subsequent runs read from the warehouse's local SSD rather than remote storage.
  • D. Automatic clustering runs between executions and reorganizes micro-partitions, improving pruning for later runs.
Explanation

Correct (B): Snowflake's result cache (also called the query result cache) stores the output of a query for up to 24 hours. When an identical query is re-executed and the underlying data has not changed, Snowflake returns the cached result instantly without engaging any warehouse compute. This produces sub-second response times and explains the dramatic difference between the first and subsequent runs. Incorrect (A): QAS does not cache query plans or results. It offloads compute-intensive query portions to shared resources during execution; it does not persist results between runs. Incorrect (C): While the warehouse's local SSD (data cache) does cache micro-partition data and can improve subsequent scan speeds, it would not reduce a 4-minute query to under 1 second on its own. The near-instant response is characteristic of the result cache, not the data cache. Incorrect (D): Automatic clustering operates asynchronously in the background and typically takes minutes to hours, not the seconds between consecutive query executions in the same session.

5. A Snowflake Query Profile shows that a query scanning a large table has very few partitions pruned — nearly all micro-partitions are being scanned. The table is clustered by CREATE_DATE, but the query filters exclusively on CUSTOMER_REGION. Which action will MOST directly improve partition pruning for this query?

  • A. Increase the virtual warehouse size to reduce scan time.
  • B. Define a clustering key on CUSTOMER_REGION to allow micro-partition pruning on that column.✓ Correct
  • C. Enable the Query Acceleration Service (QAS) to offload the full table scan.
  • D. Add a materialized view that pre-aggregates data by CUSTOMER_REGION.
Explanation

Correct (B): Partition pruning is driven by clustering keys. When a table is clustered by CREATE_DATE but queried on CUSTOMER_REGION, Snowflake cannot skip micro-partitions based on CUSTOMER_REGION values. Redefining or adding a clustering key on CUSTOMER_REGION allows the metadata to reflect the value ranges for that column per micro-partition, enabling effective pruning. Incorrect (A): Increasing warehouse size speeds up scanning but does not reduce the number of partitions scanned — it just parallelizes reading the same data. Incorrect (C): QAS accelerates large, outlier queries by offloading work to shared resources, but it does not improve pruning; all micro-partitions still need to be read. Incorrect (D): A materialized view can reduce repeated aggregation work but does not fix the root cause of poor pruning on the base table for ad-hoc filter queries.

33 more questions in this domain

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

Start practicing free