Refer to the Exhibit Practice Questions

The warehouse is too small; increase it to improve the scan speed.

Why it fails: Increasing the warehouse size will make a full table scan finish faster because more nodes are reading the data in parallel. However, it does not solve the underlying architectural problem of zero pruning. The goal of an architect should be to minimize the data read, which is achieved through pruning, not just making the scan faster.

The query is hitting the metadata cache; no action is required.

Why it fails: If a query were hitting the metadata cache or result cache, the profile would show that zero partitions were scanned, or it would indicate a 'Result Cache' hit. Scanning all 5,000 partitions is the opposite of a cache hit; it indicates that every single micro-partition had to be opened and read from the storage layer to satisfy the query.

The table is a Transient table; convert it to a Permanent table.

Why it fails: The table type (Transient vs. Permanent) has no impact on query pruning or the efficiency of table scans. Both table types use the same micro-partitioning architecture and metadata-driven pruning. The difference between them is strictly related to data retention (Time Travel and Fail-safe) and storage costs, not query execution performance or pruning capabilities.

Materialized views automatically created by the optimizer.

Why it fails: Materialized views are user-defined objects that store pre-computed results. They are not automatically created by the optimizer to prune partitions. Partition pruning is a native, inherent functionality of the storage engine that operates on the micro-partition metadata, not on pre-computed view results. The exhibit demonstrates native pruning behavior.

Automatic indexing of the date column by the system.

Why it fails: Snowflake does not use traditional indexes, such as B-trees, to accelerate queries. Instead, it relies on micro-partition metadata. Attributing this behavior to 'indexing' is technically incorrect, as the system does not maintain indexes that need to be updated or stored, which would otherwise complicate DML operations and system performance.

Data clustering on the 'date' column.

Why it fails: While clustering can improve the efficiency of pruning, it is not the mechanism *responsible* for the pruning itself. The mechanism is the metadata-based scanning engine. Clustering is a physical data layout optimization that aligns data to make the pruning process more effective, but the engine would still prune without explicit clustering.

Implement a Search Optimization Service on the join columns to reduce the number of partitions scanned.

Why it fails: Search Optimization Service (SOS) is designed to speed up point-lookup queries that filter on specific values. It does not help with large-scale data spilling during joins or aggregations, as shown in the exhibit, where the bottleneck is memory/disk capacity rather than the efficiency of the initial scan.

Enable the Query Acceleration Service (QAS) for the warehouse to handle the excess data volume.

Why it fails: Query Acceleration Service is primarily used to offload massive table scans to a shared compute tier. It does not typically resolve spilling issues related to complex joins or sorts that exceed the memory of the primary warehouse, as those operations still require local warehouse resources for execution.

Rewrite the query to use a LATERAL FLATTEN on the largest tables to optimize memory usage.

Why it fails: LATERAL FLATTEN is used for exploding semi-structured arrays into multiple rows and actually increases the volume of data processed in memory. Using it would likely worsen the spilling problem rather than solve it, as it creates more intermediate rows that need to be tracked and sorted.

Increase the max_cluster_count to 5 to enable multi-cluster scaling.

Why it fails: Increasing the cluster count provides horizontal scaling, which helps with query concurrency (multiple users running queries at once). It does not increase the resources available to a single query. Therefore, a massive scan and join on a single query would not benefit from more clusters; it needs a larger, more powerful single cluster.

Set the auto_resume property to TRUE to ensure the warehouse starts automatically.

Why it fails: Setting auto_resume to TRUE ensures the warehouse starts when a query is submitted, but it does not improve the performance of the query once it is running. The exhibit shows the warehouse is currently SUSPENDED, so while auto_resume is necessary for it to work, it is not a performance optimization for a massive data scan.

Alter the warehouse type from STANDARD to SNOWPARK-OPTIMIZED.

Why it fails: Snowpark-optimized warehouses are designed for memory-intensive workloads like machine learning training or complex memory-heavy transformations. While they provide more memory, a standard 'Large' warehouse is usually sufficient and more cost-effective for typical SQL-based joins and scans. Standard warehouses are the default architectural choice for most data warehousing tasks.

The stage will be created successfully and data can be loaded because GetObject is allowed.

Why it fails: Even though GetObject is granted for the entire bucket, Snowflake needs to list the files in the directory to identify which objects to process. Since the ListBucket permission is restricted to the 'logs/' prefix, the integration will fail to see any files in the 'raw/' directory, preventing data loading.

The stage creation will fail immediately because Snowflake cannot validate the bucket's existence.

Why it fails: Snowflake might allow the creation of the stage object in its metadata, but the validation of the path usually happens during the first access or if 'VALIDATION = TRUE' is used. The core issue is not the existence of the bucket itself, but the lack of permissions to list the specific prefix.

Data loading will work, but only if the user specifies the exact filenames in the COPY command.

Why it fails: Snowflake's internal mechanisms for accessing external stages still rely on the ability to list objects within a prefix to manage the ingestion metadata. Even with exact filenames, the storage integration requires proper IAM permissions to interact with the S3 API for listing operations to ensure the path is valid.

A new cluster will start immediately as soon as a single query is queued.

Why it fails: This behavior describes the 'Standard' scaling policy. With the 'Economy' policy, Snowflake is more conservative and will wait until it determines that the system has enough sustained load to justify the cost of an additional cluster, prioritising credit savings over the immediate reduction of query queuing time.

The warehouse will scale up to an X-Large size to handle the increased load.

Why it fails: The 'WAREHOUSE_SIZE' parameter is static in this command and does not change automatically based on load. To change the size (vertical scaling), a manual ALTER WAREHOUSE command or a third-party tool is required. Multi-cluster settings only control horizontal scaling, which adds or removes clusters of the same size.

All five clusters will start simultaneously when the first query is submitted to the warehouse.

Why it fails: Snowflake's multi-cluster warehouses scale dynamically between the MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT. They do not start all clusters at once unless the MIN and MAX are set to the same value. In this configuration, it starts with two clusters and adds more only as the workload demands according to the policy.

The connection is successful.

Why it fails: The connection cannot be successful because the source IP address 10.0.0.5 is explicitly included in the blocked_ip_list. Snowflake applies blocked list rules with higher priority than allowed list rules, ensuring that any traffic matching a blocked entry is denied access before checking if it is allowed elsewhere.

The connection depends on the user's role.

Why it fails: Network policies in Snowflake are applied at the account or user level and are evaluated based on the source IP of the connection, not the privileges of the user role. Even an account administrator would be blocked if their source IP matches an entry in the blocked_ip_list of the policy.

The connection is deferred to the secondary policy.

Why it fails: Snowflake does not use a system of primary and secondary network policies. Only one network policy can be active at the account level at any given time. The evaluation logic is deterministic based solely on the active policy's configuration, and no fallback mechanism exists for rejected IP addresses.

The warehouse is too small for the amount of data.

Why it fails: While a larger warehouse might make the scan faster, the fundamental problem is that the scan is happening at all. Efficient queries should prune partitions. A small warehouse is not the cause of the scan efficiency failure; the lack of appropriate clustering is the true root of this performance issue.

The result cache is disabled.

Why it fails: The result cache is enabled by default in Snowflake and cannot be disabled. If a query is slow, it is because it is performing actual compute work, not because the cache is missing. The profile clearly shows an expensive scan, which signifies that the engine is doing unnecessary data processing.

The query is missing a search optimization index.

Why it fails: Search optimization is for point lookups, not range queries. Even if an index were added, it would not assist in pruning partitions for a range filter. The correct solution for range filtering is clustering the data to allow the engine to ignore non-relevant partitions during the scan process.

Scale up the virtual warehouse assigned to the pipe to a larger size.

Why it fails: Snowpipe does not use user-managed virtual warehouses; it uses Snowflake-managed serverless compute. Consequently, scaling up a virtual warehouse in your account will have no effect on the ingestion performance or the pending file count of a Snowpipe, as the resources are managed externally.

Increase the MAX_CONCURRENCY_LEVEL parameter in the Pipe definition to allow more parallel loads.

Why it fails: There is no MAX_CONCURRENCY_LEVEL parameter for a Snowflake Pipe. Snowpipe automatically scales its concurrency based on the volume of data and the internal availability of resources. Architects cannot manually tune the internal parallelism of the Snowpipe service through DDL parameters like they can with warehouses.

Use the ALTER PIPE ... REFRESH command to force the pipe to process the pending files faster.

Why it fails: The REFRESH command is used to synchronize the pipe's metadata with the files currently in the stage that might have been missed by event notifications. It does not increase the processing speed or throughput; it simply ensures that older files are added to the ingestion queue.

T2 will fail with an error indicating that the stream contains no records for processing.

Why it fails: A task does not fail simply because a stream is empty; the WHEN clause is specifically designed to handle this scenario gracefully. Failure only occurs if the SQL command within the task encounters an error during execution, whereas the WHEN clause prevents the execution from even starting.

T2 will run and perform a full table scan of the stream, resulting in zero rows inserted.

Why it fails: The purpose of the WHEN clause is to prevent the task from running entirely. If the clause evaluates to false, the task logic is not executed. This avoids the overhead of starting a warehouse and executing a query that would return no results, optimizing resource usage.

T2 will wait until S1 has data before executing, potentially delaying the rest of the DAG.

Why it fails: Tasks do not 'wait' for data; they evaluate the condition at the scheduled time or upon completion of a predecessor. If the condition is false at that specific moment, the task is skipped immediately, allowing the DAG to move forward to any subsequent child tasks if applicable.

It specifies the Snowflake role that the user will be assigned when they connect via the OAuth provider.

Why it fails: The role assignment in External OAuth is typically handled by the `scope_mapping_attribute` or the `EXTERNAL_OAUTH_ANY_ROLE_MODE` parameter, not the `token_user_field`. The `token_user_field` is strictly used for user identification and mapping, rather than determining the privileges or roles the user assumes during their session.

It identifies the public key used to verify the digital signature of the incoming OAuth access token.

Why it fails: Verification of the digital signature is performed using the public keys retrieved from the URL specified in the `jks_url` parameter. The `token_user_field` is only evaluated after the token signature has been successfully validated, serving as a secondary step to link the validated token to a specific Snowflake user.

It determines the audience for which the token was issued to prevent token substitution attacks.

Why it fails: The audience validation is handled by the `audience_list` parameter, which contains the expected values for the 'aud' claim in the JWT. This ensures that the token was intended for the Snowflake service, whereas the `token_user_field` specifically identifies the subject or user who is attempting to authenticate.

The Virtual Warehouse local SSD cache.

Why it fails: The Virtual Warehouse local SSD cache stores data retrieved from the centralized storage layer for faster subsequent access. However, result set caching happens at a higher level in the Cloud Services layer and does not involve the Virtual Warehouse compute resources at all for matching queries.

The centralized cloud storage bucket.

Why it fails: The centralized cloud storage bucket is the persistence layer where raw micro-partitions reside. Retrieving data from here would require a running Virtual Warehouse and would involve scanning micro-partitions, which takes significantly longer than the 0.2 seconds indicated in the exhibit's result cache scenario.

The local disk of the user's client.

Why it fails: Snowflake does not cache result sets on the client machine. The caching mechanism is entirely server-side, managed by the Cloud Services layer within the Snowflake architecture. Client-side caching is typically handled by third-party drivers or application-level logic, not by the internal Snowflake result cache architecture.

The ANALYST_ROLE is a managed access role and cannot modify any privileges on the SALES_DATA table.

Why it fails: Managed access is a property of a schema, not a role. Even in a managed access schema, the 'grant_option' column in this output specifically refers to the delegation of the SELECT privilege itself, not the broader management settings of the schema or the role's inability to modify privileges.

The SELECT privilege is automatically granted to any role that is a child of the ANALYST_ROLE.

Why it fails: Privileges flow upwards in the role hierarchy (child roles pass privileges to parent roles), not downwards. The 'grant_option' does not affect how privileges are inherited by other roles; it only dictates whether the current role has the right to manually grant that privilege to another distinct role.

The ANALYST_ROLE can only grant the SELECT privilege if they also have the OWNERSHIP privilege on the table.

Why it fails: Normally, only the owner of an object can grant privileges. However, the 'WITH GRANT OPTION' allows a non-owner to grant specific privileges to others. In this case, the `ANALYST_ROLE` does not need ownership because the 'grant_option' explicitly provides the necessary authority for that specific SELECT privilege.

To ensure the Task only runs if the sales_summary table is empty.

Why it fails: The WHEN clause evaluates the condition specified, which in this case is the state of a stream, not the row count of the target table. There is no native function in the WHEN clause specifically designed to check if a table is empty before execution.

To check for the existence of the 'sales_stream' object before starting the warehouse.

Why it fails: The SYSTEM$STREAM_HAS_DATA function does not just check for the existence of the object; it checks if the stream contains any transactional offsets (data changes). If the stream exists but is empty, the function returns false, and the task will skip its current execution.

To validate that the data in the stream matches the schema of the sales_summary table.

Why it fails: The WHEN clause is used for execution flow control, not for schema validation. Schema mismatches will result in an error at the time the INSERT statement is executed, but the WHEN clause cannot perform complex metadata comparisons or data quality checks on its own.