SNOWPRO-CORE-C03 Practice Questions: Performance Optimization, Querying, and Transformation Domain

Correct answer (A): The workload consists of highly selective filters on semi-structured JSON fields with poor pruning. Search optimization is designed for exactly this scenario: it builds data structures that support fast point lookups and very selective filters on high-cardinality or semi-structured columns, reducing the need to scan most micro-partitions.

Why the other options are wrong:
- Option B: Clustering may help pruning in some cases, but for highly selective queries on semi-structured fields, search optimization is more effective and explicitly built for this use case. Clustering also introduces ongoing reclustering overhead.
- Option C: Restructuring the table into relational columns is a major data model change, which the team wants to avoid. While it might help long term, Snowflake provides a more direct feature (search optimization) that addresses the current need.
- Option D: Scaling up the warehouse makes each scan faster but does not solve the fundamental issue that 90% of micro-partitions are scanned for very selective filters. It increases cost without fully addressing the performance problem.

Correct answer (A): Dynamic Tables let you declaratively define the transformation in SQL while Snowflake maintains the result over time and refreshes it according to defined policies. This reduces the need for complex external orchestration while still using compute resources behind the scenes for refresh operations, aligning well with the requirement to simplify orchestration of a curated CUSTOMER_METRICS table.

Why the other options are wrong:
- Option B: A stream on CUSTOMER_METRICS would track changes to the target, not simplify the upstream transformation logic. Using a task to periodically rebuild from scratch still leaves significant orchestration responsibilities with the team instead of letting Snowflake maintain the transformation declaratively.
- Option C: Materialized views are valuable for specific, repeated query patterns but are not described here as the main mechanism for replacing a multi-step transformation pipeline. They also incur maintenance and are better suited for single-query-like definitions rather than orchestrating an entire pipeline.
- Option D: Multi-cluster warehouses help with concurrency but do not simplify orchestration or maintenance of the transformation pipeline itself. The problem is not concurrency or warehouse capacity; it is the complexity of managing refresh logic over time.

Correct answer (A): Result cache entries are invalidated when DML operations modify the underlying data referenced by the cached query. Because rows in SALES were updated between runs, the cached result is no longer valid, and the query must be fully executed again, explaining the longer runtime.

Why the other options are wrong:
- Option B: Incorrect. The stem does not indicate that the warehouse suspended, and even if it did, result cache behavior depends on data changes, not strictly on warehouse suspend/resume. The key factor here is the update to SALES.
- Option C: Incorrect. Warehouse cache is local to a warehouse, not shared across warehouses, but that does not explain why the result cache was not used in this scenario.
- Option D: Incorrect. Optimizer rewrites do not typically cause the result cache to be bypassed when the query is logically identical. The primary reason here is the DML change to the underlying data.

Correct answer (C): Result cache entries are reused only when the full query context matches: same SQL text, same warehouse, same role, and relevant session parameters, and the underlying data has not changed. Because the second user runs the query under a different role (ANALYST_ADMIN), the context differs, so Snowflake does not reuse the initial result cache entry and instead re-executes the query.

Why the other options are wrong:
- Option A: Result cache is not restricted to a single session; it can be reused across sessions and users as long as the SQL text, role, warehouse, and relevant session parameters match and underlying data is unchanged.
- Option B: Result cache entries can be reused multiple times for up to 24 hours as long as the context and data validity conditions are met. They are not invalidated after a single reuse.
- Option D: Auto-suspend affects when a warehouse is billed and when it starts or stops, but it has no direct impact on result cache behavior.

Correct answer (A): Metadata cache, which supports pruning decisions, lives in the cloud services layer and does not depend on a specific warehouse. Keeping the warehouse running preserves only its local data cache. The team should weigh the benefit of avoiding a one-time warm-up of local data pages against the extra compute cost of not suspending.

Why the other options are wrong:
- Option B: Result cache is not tied to a specific warehouse and is not cleared simply because a warehouse suspends. This statement overstates the benefit of keeping the warehouse running.
- Option C: Metadata cache and result cache are not reset on warehouse resize; only the warehouse local cache (data pages) is lost. This answer incorrectly conflates different cache types.
- Option D: Warehouse cache holds data pages, not full query results, and does not replace the result cache. It helps repeated scans on the same warehouse but is not a substitute for the result cache behavior.

Correct answer (B): The symptom is query queueing during peak concurrent usage, not slow execution once queries run. Multi-cluster warehouses are specifically designed to address concurrency by adding more clusters that can run more queries in parallel. Keeping the same size (X-LARGE) but adding clusters improves throughput for many simultaneous dashboard queries without necessarily changing per-query latency.

Why the other options are wrong:
- Option A: Scaling up to a 2X-LARGE warehouse increases resources available to each query but does not directly increase the number of concurrent queries that can run without queueing on a single-cluster warehouse. This mainly helps individual heavy queries, not the concurrency bottleneck described.
- Option C: Auto-suspend affects cost and warm-up time, not how many queries can run concurrently once the warehouse is active. Disabling auto-suspend might reduce cold-start delays but does not address queueing under high concurrency.
- Option D: Clustering keys can help reduce bytes scanned for certain predicates, improving execution efficiency. However, the main problem here is queueing due to concurrency, not excessive scan time. Clustering would not prevent queries from being queued.

Correct answer (B): Search optimization is specifically designed to speed up highly selective lookups on large tables by creating additional data structures that allow Snowflake to locate matching rows with minimal scanning. Enabling search optimization on the email column is well-suited to stable, mission-critical point-lookups on a massive customer table.

Why the other options are wrong:
- Option A: A clustering key can improve pruning for certain range filters or ordered access patterns, but for random point-lookups on email in a very large table it may still require scanning many partitions and adds ongoing maintenance overhead.
- Option C: A materialized view of DISTINCT email and keys would still require lookups and joins to retrieve full row details and does not inherently provide the efficient index-like access that search optimization offers for these predicates.
- Option D: Scaling up the warehouse can reduce execution time slightly but does not solve the underlying issue of scanning many micro-partitions to find a single row, and it increases compute costs without the targeted benefit of search optimization.

Correct answer (B): When only a top-level field is needed and there is no requirement to explode arrays, using VARIANT path notation (e.g., data:customer_id) in the filter is more efficient than FLATTEN. It avoids generating additional rows and operators, keeping the query simpler and more performant.

Why the other options are wrong:
- Option A: FLATTEN is useful when you need to explode arrays into separate rows for joins or aggregations on array elements. Here, only the top-level customer_id field is needed, so FLATTEN introduces unnecessary overhead.
- Option C: Creating a separate table via FLATTEN and then joining back adds unnecessary complexity and compute cost for a simple filter on a top-level field.
- Option D: Converting VARIANT to VARCHAR and using string operations is generally slower, less robust, and bypasses Snowflake’s native semi-structured data optimizations.

Correct answer (B): Materialized views store precomputed results of an expensive query and are automatically maintained as the base table changes. For a complex aggregation that is run frequently by many users and can tolerate slight data latency, a materialized view is the most appropriate feature to significantly accelerate dashboard performance.

Why the other options are wrong:
- Option A: Standard views are just saved SQL definitions; they do not precompute or store results. Every query against the view still runs the full aggregation and will not materially improve performance.
- Option C: Search optimization targets highly selective lookups and certain semi-structured patterns. It does not optimize large aggregations over many rows, so it is not suitable here.
- Option D: Dynamic Tables can define incremental transformations with freshness goals, but the option specifies a 1-hour freshness target, which does not meet the 'few minutes' tolerance. For a single repeated aggregation, a materialized view is a more direct and standard pattern for accelerating BI queries.

Correct answer (A): Search optimization is designed to speed up highly selective point lookups and filters, especially on high-cardinality columns. Enabling it on the EMAIL column creates additional data structures that allow Snowflake to return a tiny subset of rows quickly without scanning large portions of the table.

Why the other options are wrong:
- Option B: A materialized view containing the same columns does not inherently make lookups selective or avoid scanning many rows; it is more beneficial for repeated aggregations or complex queries than simple point lookups.
- Option C: Clustering can help pruning, but EMAIL-based lookups are highly selective on a high-cardinality column. Search optimization is better suited for this pattern and avoids the ongoing reclustering overhead.
- Option D: Scaling up the warehouse may reduce latency temporarily but does not address the underlying inefficiency of scanning many unnecessary rows for point lookups. It also increases cost without leveraging Snowflake's selective access features.