Refer to the Exhibit Practice Questions

The user needs the 'OWNERSHIP' privilege on the schema.

Why it fails: Ownership is not required to view data; it is only required to manage the object or grant privileges to others. Granting ownership of a schema to a general user is a security risk and violates the principle of least privilege, as it allows them to drop the schema.

The database is not set as the default database for the user.

Why it fails: Setting a default database is a convenience for users to avoid using fully qualified names, but it is not a security requirement for accessing data. A user can query any object they have explicit privileges on by using its fully qualified name, regardless of their default settings.

The user is missing the 'MANAGE GRANTS' privilege.

Why it fails: The MANAGE GRANTS privilege allows a role to modify access control for other roles. It is an administrative privilege that is not required for a user to see or query tables in a schema. Normal data access is managed through standard object-level privileges, not administrative ones.

The load failed completely, and no rows were added to the table because of the 2 errors.

Why it fails: If the load had failed completely, the STATUS column would show 'LOAD_FAILED' or 'ABORTED', and the ROW_COUNT would be zero. Since the status is 'PARTIALLY_LOADED' and there is a positive ROW_COUNT, it confirms that the transaction was committed for the rows that did not encounter any parsing or data type errors.

The 2 errors represent duplicate files that were already loaded in a previous session.

Why it fails: Duplicate files are handled by Snowflake's load metadata and would typically result in the file being skipped entirely with a status like 'LOAD_SKIPPED' (if not forced). ROW_ERRORS specifically refers to issues within the content of the file itself, such as a string being too long for a column or a mismatch in the number of delimiters.

The entire file was skipped because the number of errors exceeded the default threshold.

Why it fails: If the file had been skipped, the ROW_COUNT would not be 500. A skipped file would result in zero rows being added to the target table for that specific file. The status 'PARTIALLY_LOADED' explicitly means that some data from that specific file was committed to the database despite the presence of some bad records.

The actual email addresses.

Why it fails: The policy explicitly checks if the user's role is 'ADMIN'. Since 'ANALYST' is not 'ADMIN', the query fails the condition and returns the masked value. Only users with the 'ADMIN' role are permitted to see the unmasked, raw data defined in the CASE statement.

An error message indicating insufficient privileges.

Why it fails: Masking policies do not prevent the query from succeeding; they simply transform the data returned. The user will receive the results of the query, but the sensitive column values will be replaced by the masked string rather than causing a system error or access denial.

NULL values.

Why it fails: The policy explicitly defines the return value as '***@***.com' for non-admin roles. Unless the policy were configured to return NULL explicitly or the data were already null, the result will be the specified mask string, not a NULL value representing the absence of data.

The warehouse automatically resumes.

Why it fails: The warehouse does not need to resume because the result is served directly from the result cache. Resuming the warehouse would incur unnecessary compute costs. Snowflake optimizes the process by checking the cache first, ensuring that compute resources are only utilized when absolutely necessary for data processing.

The query fails due to lack of compute.

Why it fails: Queries do not require an active warehouse to retrieve results if the data is available in the result cache. The Cloud Services layer manages this cache and returns the data immediately. This allows users to access historical results even if the warehouse is suspended, improving overall user accessibility.

The query waits for the warehouse to start.

Why it fails: The query does not wait because it does not require compute resources to retrieve data from the cache. Waiting for a warehouse startup is only necessary when a new query execution requires processing that cannot be fulfilled by the result cache or metadata pruning, which is not the case here.

Any Snowflake customer in the same region as the provider.

Why it fails: The 'visibility' attribute is set to 'PRIVATE', which explicitly hides the listing from the general public and other regional customers. Visibility is the primary control that determines whether a listing appears in the global Marketplace catalog or is restricted to a set of pre-approved accounts defined by the provider.

Only the users within the provider's own Snowflake account.

Why it fails: Listings are designed for external sharing. While the provider can see their own listing, the purpose of specifying 'target_accounts' is to grant access to external organizations. A listing restricted only to the provider's account would serve no purpose in the context of Snowflake's data collaboration and exchange features.

All accounts belonging to ORG_A and ORG_B, regardless of the individual account names.

Why it fails: In Snowflake, sharing is typically targeted at the account level, not the entire organization, unless specified differently. The JSON explicitly lists 'ACCOUNT_1' and 'ACCOUNT_2'. Other accounts within those same organizations will not have access to the listing unless they are also explicitly added to the target accounts list by the provider.

The warehouse WH_XS is in a suspended state.

Why it fails: Even if the warehouse is suspended, the Cloud Services layer handles the Query Result Cache retrieval. While the warehouse being suspended is a factor, the reason zero credits were used is specifically because the result was retrieved from the cache rather than being executed by the compute nodes.

The data was already present in the warehouse local disk cache.

Why it fails: If the data were in the warehouse's local disk cache, the query would still need to be executed by the compute nodes to process the blocks. That process consumes compute credits. Disk caching accelerates execution time, but it does not reduce credit usage to zero like the Query Result Cache.

The query failed during the planning phase.

Why it fails: The exhibit explicitly states the status as 'SUCCESS'. A failure would result in no result set, and while failures generally don't consume compute, the success indicator proves the query completed successfully and returned a result, pointing definitively to cache usage rather than a failure during processing.

It forces the data into a rigid relational schema.

Why it fails: The VARIANT type does exactly the opposite; it accommodates flexible, schema-less data. It allows Snowflake to handle nested structures and varying keys within a single column, eliminating the need for complex ETL processes to flatten JSON objects before loading them into the database for analysis.

It converts all data to string format for storage.

Why it fails: Snowflake does not convert VARIANT data to plain strings. Instead, it stores the data in a highly optimized, binary internal representation that is indexed and compressed. This allows for rapid access to specific elements within the JSON object, which would be impossible if the data were stored as simple text.

It increases storage costs by requiring a fixed-width format.

Why it fails: The VARIANT type is highly space-efficient due to Snowflake's internal compression algorithms. It is not fixed-width; it dynamically adapts to the data. It helps reduce storage costs by minimizing the footprint of repetitive JSON keys and effectively utilizing Snowflake's native columnar storage architecture for semi-structured data.

It increases the number of clusters in the warehouse.

Why it fails: This parameter does not change the number of clusters in a multi-cluster warehouse. It only changes how many queries each individual cluster is allowed to execute at once. Scaling the number of clusters is controlled by the MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT parameters, which are distinct from concurrency limits.

It scales the warehouse size (e.g., X-SMALL to SMALL).

Why it fails: Warehouse size (e.g., X-SMALL, SMALL, MEDIUM) is changed using the WAREHOUSE_SIZE parameter. MAX_CONCURRENCY_LEVEL is a separate configuration that adjusts the queuing logic within the existing compute capacity, not the compute capacity itself, which is defined by the number of nodes per cluster.

It disables the auto-scaling capabilities of the warehouse.

Why it fails: Changing the concurrency level has no impact on the auto-scaling logic of a multi-cluster warehouse. The warehouse will still add or remove clusters based on the scaling policy and the demand, regardless of how many queries it is currently configured to handle within each individual cluster.

It identifies the specific S3 bucket that Snowflake is allowed to access.

Why it fails: The S3 bucket location is typically defined in the 'Resource' section of an IAM policy or within the 'STORAGE_ALLOWED_LOCATIONS' parameter of the Snowflake Storage Integration object. The External ID is not used to specify data locations but rather to secure the trust relationship between the two separate cloud accounts (Snowflake's and the customer's).

It maps Snowflake users to specific AWS IAM users for fine-grained access control.

Why it fails: Snowflake Storage Integrations do not map individual Snowflake users to IAM users. Instead, they provide a single secure channel for the Snowflake service to access cloud storage. Fine-grained access control within Snowflake is then managed through Roles and Privileges (RBAC), while the integration handles the underlying infrastructure-level authentication for the entire account.

It encrypts the data during transit between the cloud provider and Snowflake.

Why it fails: The External ID is an authentication and authorization parameter, not an encryption mechanism. Snowflake automatically encrypts all data in transit using TLS and data at rest using AES-256. The IAM policy and its conditions are focused on verifying that the request to access the storage service is coming from a legitimate and authorized source.

Search Optimization Service

Why it fails: The Search Optimization Service is designed to accelerate point-lookup queries on large tables. While it improves performance, the exhibit indicates a scan of partitions rather than a targeted lookup. Partition pruning is the standard mechanism utilized for filter-based scanning, whereas SOS is for specific key lookups.

Materialized Views

Why it fails: Materialized views are pre-computed result sets stored on disk. While they improve performance, they are typically used for complex queries involving aggregations or joins, not simple filtering on a date column. Partition pruning is the more direct and appropriate explanation for the efficiency observed in this scan.

Query Result Caching

Why it fails: Query Result Caching stores the output of a query for 24 hours. If the query hit the cache, the 'Scanned' data volume would be zero. Since the query scanned 10GB of data, it implies the execution engine actually processed the underlying data rather than returning a cached result.

jdoe has the privileges of the manager role only.

Why it fails: Role inheritance allows jdoe to automatically inherit the privileges of any role assigned to the manager role. Since the analyst role was granted to the manager, jdoe effectively gains all permissions associated with the analyst role in addition to the manager role's direct permissions.

jdoe must explicitly switch to the analyst role to see its data.

Why it fails: Snowflake role inheritance is transitive and automatic. Once the roles are granted, the user does not need to manually switch roles to exercise the privileges granted to the child role. The effective set of permissions is calculated automatically at runtime whenever the user performs an action.

jdoe only has access to objects created by the analyst role.

Why it fails: Role inheritance provides access to all privileges granted to the roles, not just those related to object ownership. If the analyst role has SELECT privileges on a table, the manager role (and jdoe) will inherit that SELECT privilege immediately upon the execution of the GRANT statement.

Apply a clustering key to the primary keys of both tables.

Why it fails: Clustering keys assist with partition pruning during the scanning phase but cannot mitigate the performance impact of a Cartesian product. Once the data is scanned, the join operator will still produce an excessive number of rows because the logical relationship between the tables remains incorrectly defined in the SQL.

Enable the Query Acceleration Service for the warehouse.

Why it fails: The Query Acceleration Service offloads heavy scanning and filtering tasks to shared compute resources, but it is not intended to fix inefficient SQL. A Cartesian product creates a processing bottleneck at the join level that additional compute power cannot efficiently resolve without first fixing the underlying query logic.

Increase the warehouse size to 4X-Large to handle the volume.

Why it fails: Scaling the warehouse provides more resources, but it is a costly and temporary workaround for a logical error in the code. Even a large warehouse will struggle with the exponential row growth of a Cartesian product, making code correction a far more effective and sustainable optimization strategy.

Row-level Security

Why it fails: Row-level security involves filtering the rows returned in a query result based on user attributes. The exhibit shows a masking expression applied to a column's value (a string), not a predicate that filters rows from the table, making this an example of column-level masking rather than row-level security.

Access Control List

Why it fails: An Access Control List (ACL) defines who has what level of access (read, write, etc.) to an object. The exhibit is a functional policy that modifies the data content based on a conditional expression, which goes beyond simple access control and into the realm of data transformation and masking.

Data Projections

Why it fails: Data projection is a concept related to selecting specific columns in a query. It is not a security feature. The JSON provided is a security policy object, not a query projection, and it performs a transformation on the data value rather than just selecting or filtering it from the set.

The query fails with an 'Access Denied' error.

Why it fails: Masking policies do not block access to the entire query; they simply return a masked version of the data. If the user has SELECT privileges on the underlying table, they will still be able to execute the query, but the sensitive column will display the masked result.

The query returns NULL for all rows.

Why it fails: A masking policy does not automatically return NULL unless the policy definition specifically uses the NULL value as the masking outcome. Without a defined function returning NULL, the system will return the result of the masking function specified within the policy, which is typically a hashed string.

The user sees the unmasked ssn data.

Why it fails: The user has the 'ANALYST' role, which is not included in the 'HR_ADMIN' permission set. Since the policy condition is not met, the unmasked data is not returned. The masking policy is working as intended to restrict access to the sensitive information for non-authorized roles.

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

Why it fails: This behavior describes the Standard scaling policy, which focuses on minimizing latency for users. Under the Economy policy, Snowflake monitors the estimated load and only spins up a new cluster if it determines the cluster will be fully utilized for several minutes. This prevents frequent, short-lived cluster activations that increase costs.

The warehouse will immediately scale up to 4 clusters to handle the incoming load.

Why it fails: Snowflake scales out incrementally, adding one cluster at a time based on the scaling policy and the actual demand. It would never jump directly to the maximum cluster count unless the queued workload was massive and the scaling policy allowed for it. The Economy policy further restricts this rapid expansion to save money.

The warehouse will automatically increase its size from X-Small to Large.

Why it fails: Multi-cluster warehouses scale horizontally by adding clusters of the same size, not vertically by changing the size of the existing clusters. Changing the size of a warehouse (e.g., from Small to Medium) requires a separate command and is not part of the automatic scaling process managed by the Scaling Policy.