Try 10 focused SnowPro Core COF-C02 questions on Loading and Transformation, with explanations, then continue with IT Mastery.
Open the matching IT Mastery practice page for timed mocks, topic drills, progress tracking, explanations, and full practice.
Try SnowPro Core COF-C02 on Web View full SnowPro Core COF-C02 practice page
| Field | Detail |
|---|---|
| Exam route | SnowPro Core COF-C02 |
| Topic area | Data Loading, Unloading, and Transformation |
| Blueprint weight | 20% |
| Page purpose | Focused sample questions before returning to mixed practice |
Use this page to isolate Data Loading, Unloading, and Transformation for SnowPro Core COF-C02. Work through the 10 questions first, then review the explanations and return to mixed practice in IT Mastery.
| Pass | What to do | What to record |
|---|---|---|
| First attempt | Answer without checking the explanation first. | The fact, rule, calculation, or judgment point that controlled your answer. |
| Review | Read the explanation even when you were correct. | Why the best answer is stronger than the closest distractor. |
| Repair | Repeat only missed or uncertain items after a short break. | The pattern behind misses, not the answer letter. |
| Transfer | Return to mixed practice once the topic feels stable. | Whether the same skill holds up when the topic is no longer obvious. |
Blueprint context: 20% of the practice outline. A focused topic score can overstate readiness if you recognize the pattern too quickly, so use it as repair work before timed mixed sets.
These questions are original IT Mastery practice items aligned to this topic area. They are designed for self-assessment and are not official exam questions.
Topic: Data Loading, Unloading, and Transformation
A data platform team is setting up controls to prevent unexpected credit overages in their Snowflake account. They want to use resource monitors and Snowflake Budgets effectively. Which of the following practices should they AVOID? (Select TWO.)
Options:
A. Configure warehouse-level resource monitors that notify at 80% of monthly credits and suspend non-production warehouses at 100% usage.
B. Set up Snowflake Budgets by cost-center tag and send alerts to a central channel when 75%, 90%, and 100% of each budget is consumed.
C. Rely only on manual monthly review of usage in Snowsight without configuring any automated alerts, resource monitors, or Budgets.
D. Assume Snowflake Budgets will automatically block additional credit consumption once a budget is reached and omit resource monitors or suspend actions.
E. Use separate resource monitors for production and development warehouses so that only development workloads are suspended when their daily credit allocation is exhausted.
Correct answers: C and D
Explanation: Snowflake provides two complementary mechanisms for managing credit consumption: resource monitors and the Snowflake Budgets service.
Resource monitors track credit usage for one or more virtual warehouses (or the whole account) and can take actions at configured thresholds, such as sending notifications or suspending warehouses. This allows you to both observe and actively enforce limits, especially on non-production workloads.
Snowflake Budgets, by contrast, are designed for tracking and alerting on spend at the organization or account level, often grouped by tags or accounts. Budgets generate alerts when forecasted or actual consumption crosses thresholds, but they do not stop compute or block additional credit usage.
Good cost governance uses these together: Budgets for high-level visibility and early warning, and resource monitors for concrete enforcement where appropriate. Practices that rely on delayed, manual reviews or misunderstand Budgets as hard limits should be avoided because they leave the account exposed to unexpected credit overruns.
Topic: Data Loading, Unloading, and Transformation
Which of the following statements about using Query History in the Snowflake AI Data Cloud to identify long-running or expensive queries is NOT correct?
Options:
A. Query History can be filtered by virtual warehouse to focus on queries that may be overloading a particular warehouse.
B. Query History cannot be filtered by execution time or duration; you must use Query Profile to find long-running queries.
C. You can limit Query History results to a specific user to investigate which of that user’s queries are taking the longest or consuming the most resources.
D. Sorting Query History by duration or credits consumed can quickly highlight candidates for further analysis in Query Profile.
Best answer: B
Explanation: Query History is the primary place to discover long‑running or expensive queries before diving deeper into individual Query Profiles. In Snowsight, you can view historical queries and filter or sort them using practical attributes such as time range, user, role, virtual warehouse, status, and duration. This helps you quickly narrow down which queries are causing performance or cost issues.
Once you identify a problematic query from Query History, you can open its Query Profile to examine execution details such as stages, partitions scanned, and data skew. Query Profile is not a discovery tool across many queries; it is a diagnostic view for a single query. Because of this, any statement claiming that you cannot use Query History filters like duration, or that you must rely only on Query Profile to find long‑running queries, is incorrect.
Topic: Data Loading, Unloading, and Transformation
Which TWO statements about scaling up and scaling out Snowflake virtual warehouses are TRUE? (Select TWO.)
Options:
A. Scaling out means enabling a multi-cluster warehouse so Snowflake can add clusters to handle more concurrent queries when workloads queue.
B. Scaling out allows a single long-running query to split across multiple clusters at once, reducing its run time more than scaling up.
C. Scaling up automatically converts a single-cluster warehouse into a multi-cluster warehouse when query queues are detected.
D. Scaling up means increasing the warehouse size so each cluster has more compute resources, which is typically better for large or complex individual queries.
E. Both scaling up and scaling out directly increase the storage capacity available for tables in the account.
Correct answers: A and D
Explanation: Snowflake virtual warehouses provide compute for queries and other DML operations. You can improve performance by either scaling up (changing the warehouse size) or scaling out (using multi-cluster warehouses). Scaling up increases the compute resources per cluster, which tends to benefit large, complex, or resource-intensive queries by giving them more CPU and memory. Scaling out adds more clusters of the same size to serve additional concurrent queries, which is most effective when many users or jobs run at the same time and queries begin to queue.
Because storage is fully separated from compute in the Snowflake AI Data Cloud, neither scaling up nor scaling out changes how much data you can store. They affect only compute capacity and cost, not storage capacity. Similarly, multi-cluster behavior must be configured explicitly; simply increasing warehouse size does not create extra clusters automatically, and a single query cannot span multiple clusters concurrently.
Topic: Data Loading, Unloading, and Transformation
A data engineering team runs occasional ad-hoc queries that filter on a highly selective predicate over a 15 TB fact table. Concurrency is low, but these queries still take several minutes to complete even on a large virtual warehouse. They want to speed up these large, selective scans without permanently increasing warehouse size or redesigning the schema. Which action is the most appropriate in Snowflake?
Options:
A. Create a materialized view on the entire 15 TB fact table to accelerate all queries
B. Resize the virtual warehouse to the next larger size and keep it running longer
C. Increase the maximum number of clusters on the virtual warehouse to improve concurrency
D. Enable the Query Acceleration Service on the virtual warehouse used for these ad-hoc queries
Best answer: D
Explanation: The scenario describes low-concurrency, ad-hoc queries that perform large but highly selective scans on a very large table. The existing virtual warehouse is already large, so simply adding more permanent compute is undesirable. The team wants to improve performance for these specific scan-heavy queries without a schema redesign.
Snowflake’s Query Acceleration Service (QAS) is designed for exactly this situation: it can offload parts of large, selective scans from the main virtual warehouse to additional serverless compute resources. This helps reduce query latency for such workloads while allowing you to keep the base warehouse size stable. You pay only for the extra serverless compute consumed when acceleration is used.
Other options like multi-cluster warehouses or large materialized views either focus on concurrency or change the data model and incur ongoing maintenance costs. They do not directly match the requirement: speeding up large, selective scans without permanently increasing warehouse size or restructuring the schema.
Topic: Data Loading, Unloading, and Transformation
Which TWO statements about data spilling and query performance in Snowflake are INCORRECT? (Select TWO.)
Options:
A. Data spilling occurs when intermediate results exceed available memory and must be temporarily written to remote storage, adding extra I/O and latency.
B. Data spilling usually improves performance by using a faster storage tier than memory, so it is not a problem.
C. Data spilling can slow queries because reading and writing spilled data is slower than processing the same data entirely in memory.
D. Seeing data spilling in Query Profile is a clue that you might need to resize the warehouse or optimize the query to reduce memory pressure.
E. Data spilling shown in Query Profile is typically a harmless visualization issue and can be ignored when troubleshooting slow queries.
Correct answers: B and E
Explanation: In Snowflake, data spilling happens when an operation (such as a large join, aggregation, or sort) cannot keep all its working data in memory. Snowflake then writes some of that data out to remote storage and later reads it back.
Remote storage access is much slower than processing data that remains fully in memory. This extra read/write activity increases I/O and latency, so spilling is commonly associated with slower queries. In the Query Profile, spilling indicators help you identify which steps are affected so you can consider increasing warehouse size or improving query logic to reduce memory pressure.
Because of this, any statement that describes spilling as “faster than memory” or “safe to ignore for performance tuning” misrepresents its impact and is incorrect.
Topic: Data Loading, Unloading, and Transformation
A Snowflake table SALES contains billions of rows and is frequently queried with predicates such as WHERE ORDER_DATE BETWEEN .... Which choice is the most appropriate clustering key to improve micro-partition pruning and query performance?
Options:
A. Use a random UUID transaction identifier as the clustering key.
B. Use every column in the SALES table as a composite clustering key.
C. Use ORDER_DATE as the clustering key for the SALES table.
D. Avoid defining any clustering key and rely only on automatic micro-partitioning.
Best answer: C
Explanation: In Snowflake, clustering keys help organize micro-partitions so that rows with similar key values are stored close together. When queries filter on the clustering key, Snowflake can skip many micro-partitions entirely, reducing the amount of data scanned and improving performance.
For a very large fact table that is commonly filtered by a specific column, such as ORDER_DATE, defining that column as the clustering key is an effective way to improve micro-partition pruning. This is especially helpful when queries frequently use date ranges, because Snowflake can avoid scanning partitions that fall completely outside the requested date range.
Choosing clustering keys that do not align with typical filter predicates, or using too many columns, reduces the effectiveness of pruning while increasing maintenance overhead. In some cases, it is better to rely on Snowflake’s automatic micro-partitioning, but when there is a clear, dominant filter column on a very large table, an explicit, simple clustering key is recommended.
Topic: Data Loading, Unloading, and Transformation
A data analyst reruns an identical SELECT query five minutes after the first execution but notices the query still scans table data instead of using the result cache. Which situation most likely explains why the result cache was not used?
Options:
A. The analyst resized the virtual warehouse from Medium to Large between runs.
B. A new row was inserted into one of the tables referenced by the query between executions.
C. The virtual warehouse auto-suspended after the first run and resumed before the second run.
D. The second query was run from Snowsight instead of being run from SnowSQL.
Best answer: B
Explanation: Snowflake’s query result cache stores the results of a query for a period of time so that identical subsequent queries can be served directly from the cache without re-scanning data. This improves performance and reduces compute cost.
However, the result cache can only be reused if several conditions hold: the query text is identical, relevant session parameters (that affect results) are unchanged, the underlying data referenced by the query has not changed, and the cached result has not expired.
In this scenario, the query is executed again only a few minutes later, so time-based expiration is unlikely. The most common reason for bypassing the result cache in this situation is that one of the underlying tables has changed. Snowflake must then recompute the result to reflect the current data, invalidating any earlier cached result for that query.
Understanding that data changes invalidate the result cache helps explain why some queries continue to consume full warehouse compute even when they look identical: if the data is changing frequently (for example, in transactional or ingestion-heavy tables), result cache reuse will naturally be limited.
Topic: Data Loading, Unloading, and Transformation
A BI team is tuning dashboards on Snowflake and wants to benefit from caching while ensuring query results are always correct, even immediately after data loads. Which guideline is the most appropriate for this requirement?
Options:
A. Write queries as if they always read from storage and allow Snowflake to manage all caches transparently as a performance optimization.
B. Pin BI workloads to a single always-on warehouse so local disk cache is preserved and can be relied on for correct results.
C. Disable the result cache for BI users so every query re-reads data from storage, guaranteeing correctness at the cost of higher latency.
D. Schedule manual cache flushes on the virtual warehouse after each data load so that all subsequent queries are forced to be correct.
Best answer: A
Explanation: In Snowflake, all persistent data lives in central, encrypted storage, and correctness always comes from that storage, not from any cache. Caches (result cache, metadata cache, and local disk cache in virtual warehouses) exist purely to improve performance by avoiding unnecessary re-reading and recomputation.
Because Snowflake’s cloud services layer tracks table versions and dependencies, it knows when cached data is no longer valid. When underlying data changes in a way that affects a query, Snowflake automatically invalidates or bypasses the relevant cache and reads the correct data from storage.
Therefore, the best guideline is to design queries and workloads as if caches did not exist, trusting Snowflake to manage them transparently. This approach both guarantees correctness and still allows Snowflake to use caches whenever it is safe to do so.
Manually flushing caches or disabling them is usually counterproductive: it increases latency and credit consumption without improving correctness, because correctness was already guaranteed by the platform. Likewise, you must never design around the assumption that any particular cache will remain warm; warehouses can suspend and resume, and caches can be evicted at any time.
Topic: Data Loading, Unloading, and Transformation
A financial analytics team runs its production Snowflake account in a single region. Compliance now requires a disaster-recovery copy in another region with an RPO of 30 minutes. They will run a 2-hour failover test once per quarter by executing a subset of reporting queries in the DR region. They want to minimize both ongoing storage and compute costs in the DR account. Which approach is BEST?
Options:
A. Create a failover group that includes only critical production databases and required roles; schedule replication every 30 minutes; keep all DR warehouses suspended except during the quarterly test, when a small warehouse is resumed to run the test queries against the replicated data.
B. Create a failover group for the entire account, including all databases and stages; replicate every 5 minutes; keep a medium warehouse running in the DR region to continuously validate replication.
C. Use Time Travel with 30-day retention in the primary region instead of cross-region replication; during quarterly tests, clone the production database and run all queries in the same region.
D. In the DR region, rebuild the production environment by re-running all ETL pipelines daily into new tables without using Snowflake replication; during tests, compare row counts using large warehouses in both regions.
Best answer: A
Explanation: Cross-region disaster recovery in Snowflake is typically implemented using database or failover groups with replication. Replication creates an additional full copy of the selected objects in the target region, which incurs extra storage cost for that replicated data. However, compute in the DR region is only charged when warehouses or serverless features actually run, so keeping DR warehouses suspended most of the time minimizes ongoing compute cost.
A cost-optimized design for the given requirements will replicate only the critical data and objects needed to meet the 30-minute RPO and to run quarterly tests, and will schedule replication at an interval aligned with that RPO. Warehouses in the DR region should remain suspended except when performing failover tests or a real failover, and those tests should use appropriately small warehouses and a limited workload to keep compute cost low while still validating the DR plan.
Topic: Data Loading, Unloading, and Transformation
A Snowflake account uses a single X-SMALL virtual warehouse for BI dashboards. It runs 24/7 with auto-suspend disabled, causing long query queues between 9–11 a.m. The team wants to remove queuing during peaks without increasing daily credit usage. Which change is MOST appropriate?
Options:
A. Keep the warehouse as a single-cluster X-SMALL, enable auto-resume, and set auto-suspend to 5 minutes.
B. Keep the warehouse configuration unchanged, but rely on the result cache to improve performance during peak hours.
C. Convert the warehouse to a multi-cluster X-SMALL with min=1, max=3, enable auto-resume, and set auto-suspend to 5 minutes.
D. Resize the warehouse to MEDIUM, keep it single-cluster, and continue running it 24/7 without auto-suspend.
Best answer: C
Explanation: The current setup uses a single X-SMALL warehouse that never suspends. This wastes credits when the warehouse is idle and cannot scale to handle high concurrency, causing query queues during peak hours. The goal is to eliminate queuing between 9–11 a.m. and avoid increasing daily credit use.
A multi-cluster warehouse can automatically add clusters to handle more concurrent queries. When demand is low, it shrinks back down. Combined with auto-suspend and auto-resume, Snowflake only runs clusters when work actually arrives, which reduces idle compute time.
Converting the existing X-SMALL to a multi-cluster configuration with min=1 and max=3, plus auto-resume and a short auto-suspend (for example, 5 minutes), means:
This balances the two goals: improved responsiveness under load (via multi-cluster scaling) and controlled or reduced credit usage (via auto-suspend/resume and right-sized clusters).
Use the SnowPro Core COF-C02 Practice Test page for the full IT Mastery route, mixed-topic practice, timed mock exams, explanations, and web/mobile app access.
Try SnowPro Core COF-C02 on Web View SnowPro Core COF-C02 Practice Test
Read the SnowPro Core COF-C02 Cheat Sheet on Tech Exam Lexicon, then return to IT Mastery for timed practice.