Scenario practice questions

When considering the implementation of a clustering key on a large table, which TWO scenarios indicate that clustering will provide the most significant performance benefit?

Trap 1: The table is frequently updated with small DML operations.

Frequent small DML operations on a clustered table can lead to high maintenance costs. Snowflake must constantly re-cluster the data to maintain the defined order, which can consume significant serverless credits and may not provide enough performance gain to justify the ongoing overhead.

Trap 2: The table size is less than 50 GB and fits in the local cache.

Clustering is generally not recommended for smaller tables because the performance gains from pruning are negligible compared to the overhead of maintaining the clustering metadata. Smaller tables often perform well enough with the natural ingestion order and Snowflake's default metadata management.

Trap 3: The table is used exclusively for full table scans and exports.

Clustering provides no benefit for queries that must perform full table scans, such as bulk data exports. Since every row must be processed regardless of the data organization, the overhead of maintaining a clustering key would be wasted without any corresponding reduction in scanned data.

A
The table is frequently updated with small DML operations.
Why it fails: Frequent small DML operations on a clustered table can lead to high maintenance costs. Snowflake must constantly re-cluster the data to maintain the defined order, which can consume significant serverless credits and may not provide enough performance gain to justify the ongoing overhead.
B
Queries against the table typically filter on a specific dimension column.
When queries consistently use a specific column in WHERE clauses, clustering the table on that column ensures that data is physically grouped together. This maximizes the efficiency of partition pruning, as the system can quickly identify and skip micro-partitions that do not match the filter criteria.
C
The table size is less than 50 GB and fits in the local cache.
Why it fails: Clustering is generally not recommended for smaller tables because the performance gains from pruning are negligible compared to the overhead of maintaining the clustering metadata. Smaller tables often perform well enough with the natural ingestion order and Snowflake's default metadata management.
D
The Query Profile shows that a large percentage of partitions are scanned.
If the Query Profile reveals that nearly all micro-partitions are being scanned for a selective query, it indicates poor natural clustering. Implementing a clustering key will reorganize the data, allowing the engine to scan only the necessary partitions and significantly reducing I/O and execution time.
E
The table is used exclusively for full table scans and exports.
Why it fails: Clustering provides no benefit for queries that must perform full table scans, such as bulk data exports. Since every row must be processed regardless of the data organization, the overhead of maintaining a clustering key would be wasted without any corresponding reduction in scanned data.

Question 2mediummultiple choice

How does Snowflake's architecture provide the ability to run multiple concurrent workloads without resource contention?

Trap 1: By using a single large warehouse for all workloads.

Using a single large warehouse for all tasks leads to resource contention. If one query consumes all available compute resources, others will queue. This approach fails to leverage Snowflake's ability to create multiple, independent virtual warehouses for different teams, departments, or specific workload requirements.

Trap 2: By relying on the underlying cloud provider's hypervisor.

While cloud providers manage hardware virtualization, Snowflake's workload management is handled at the application layer via virtual warehouses. Relying on lower-level hypervisor management does not provide the application-level isolation and resource governance that Snowflake's architecture provides to ensure that specific business units or tasks get priority.

Trap 3: By implementing software-based resource queues in the storage layer.

The storage layer in Snowflake is passive and does not manage query queues or resource allocation. All queueing and workload management happen within the Cloud Services and Compute layers. Attempting to manage resources at the storage layer would negate the benefits of Snowflake's decoupled, scalable storage architecture.

A
By using a single large warehouse for all workloads.
Why it fails: Using a single large warehouse for all tasks leads to resource contention. If one query consumes all available compute resources, others will queue. This approach fails to leverage Snowflake's ability to create multiple, independent virtual warehouses for different teams, departments, or specific workload requirements.
B
By deploying separate virtual warehouses for each workload.
Deploying separate virtual warehouses allows each workload to operate on its own dedicated compute resources. This ensures that no single query or workload can starve others of CPU or memory. Since each warehouse acts independently, the system maintains high performance for all concurrent processes, regardless of their individual resource demands.
C
By relying on the underlying cloud provider's hypervisor.
Why it fails: While cloud providers manage hardware virtualization, Snowflake's workload management is handled at the application layer via virtual warehouses. Relying on lower-level hypervisor management does not provide the application-level isolation and resource governance that Snowflake's architecture provides to ensure that specific business units or tasks get priority.
D
By implementing software-based resource queues in the storage layer.
Why it fails: The storage layer in Snowflake is passive and does not manage query queues or resource allocation. All queueing and workload management happen within the Cloud Services and Compute layers. Attempting to manage resources at the storage layer would negate the benefits of Snowflake's decoupled, scalable storage architecture.

Question 3hardmultiple choice

Refer to the exhibit. What is the primary advantage of using the VARIANT data type in this scenario?

Exhibit

CREATE TABLE sales_events (
  event_id INT,
  event_data VARIANT
);

-- Query:
SELECT event_data:user_id::STRING FROM sales_events;

Trap 1: It forces the data into a rigid relational schema.

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.

Trap 2: It converts all data to string format for storage.

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.

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

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.

A
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.
B
It enables seamless querying of semi-structured data using SQL.
By storing data in the VARIANT format, users can navigate nested JSON hierarchies using Snowflake's dot-notation and colon-notation syntax directly within SQL queries. This allows for powerful analytical operations on semi-structured data without requiring it to be transformed into a flat relational structure first.
C
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.
D
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.

Question 4mediummultiple choice

A provider wants to share data with a consumer using a reader account because the consumer does not have a Snowflake account. Which statement describes the correct capability of a reader account in this scenario?

Trap 1: A reader account is created by the consumer and billed to the…

Reader accounts are created and paid for by the provider, not the consumer. The consumer does not own the account and cannot create shares or databases freely; access is limited to querying the data shared to it. Billing flows to the provider that created the reader account.

Trap 2: A reader account can be converted into a standard Snowflake account…

Reader accounts cannot be upgraded into standard accounts. If a consumer later wants a full Snowflake account, it must obtain one through normal signup, and the provider would then share directly to that account. There is no in-place conversion path for a reader account.

Trap 3: A reader account can create its own shares and publish listings to…

Reader accounts are restricted query-only accounts. They cannot create shares or listings because they lack the account-level privileges and are intended solely to consume data provided to them. Publishing capabilities belong to standard provider accounts, not to reader accounts.

A
A reader account is created by the consumer and billed to the consumer, with full privileges to create databases and shares.
Why it fails: Reader accounts are created and paid for by the provider, not the consumer. The consumer does not own the account and cannot create shares or databases freely; access is limited to querying the data shared to it. Billing flows to the provider that created the reader account.
B
A reader account can be converted into a standard Snowflake account at any time by the consumer with no provider involvement.
Why it fails: Reader accounts cannot be upgraded into standard accounts. If a consumer later wants a full Snowflake account, it must obtain one through normal signup, and the provider would then share directly to that account. There is no in-place conversion path for a reader account.
C
A reader account can create its own shares and publish listings to the Snowflake Marketplace for other accounts.
Why it fails: Reader accounts are restricted query-only accounts. They cannot create shares or listings because they lack the account-level privileges and are intended solely to consume data provided to them. Publishing capabilities belong to standard provider accounts, not to reader accounts.
D
A reader account is created and fully managed by the provider, can query shared data, and cannot load or modify data or create its own objects beyond limited query usage.
Reader accounts are provider-owned, provider-billed accounts intended for consumers without Snowflake accounts. The consumer accesses a web interface with limited privileges to query shared data. Readers cannot load data, create shares, or perform most write operations, and the provider pays for the compute they consume.

Question 5hardmulti select

A data engineer is optimizing a complex query that joins five large tables and includes multiple aggregations. The Query Profile shows significant time spent in the Join and Aggregate nodes, and the engineer wants to reduce the amount of data processed. Which TWO techniques are most appropriate for improving performance in this scenario? (Choose two.)

Trap 1: Replace all joins with `UNION ALL` to combine the tables into a…

`UNION ALL` concatenates result sets and does not perform relational joins. It would produce incorrect results because it does not match rows on keys. It also increases the row count rather than reducing it, which would worsen performance. This is not a valid optimization for a query that requires joining tables on common dimensions; it fundamentally changes the semantics and is inappropriate here.

Trap 2: Increase the warehouse size to a 4X-Large to provide more memory…

Scaling up the warehouse can help with memory-intensive operations and reduce spilling, but it does not reduce the amount of data processed. The query will still read the same number of rows and perform the same joins. It may run faster due to more compute resources, but it does not address the root cause of large intermediate results. It is a costly approach that does not optimize the query itself.

Trap 3: Convert all `JOIN` clauses to `CROSS JOIN` to allow the optimizer…

`CROSS JOIN` produces a Cartesian product, multiplying row counts and dramatically increasing data volume. It does not allow the optimizer to find better join paths; instead, it removes the join predicates and forces the worst-case scenario. This would make the query far slower and return incorrect results. It is never an optimization technique for reducing data processed in a multi-table join.

A
Use `EXPLAIN` to inspect the query plan and identify steps that process disproportionately large row counts.
`EXPLAIN` provides the logical execution plan without running the query, showing operators and estimated costs. By inspecting the plan, the engineer can spot steps like Cartesian joins, unnecessary aggregations, or missing filters that cause large intermediate results. This diagnostic step guides targeted optimizations such as adding predicates or rewriting joins. It is a best practice for understanding where the optimizer is spending effort and where data volume explodes.
B
Replace all joins with `UNION ALL` to combine the tables into a single result set.
Why it fails: `UNION ALL` concatenates result sets and does not perform relational joins. It would produce incorrect results because it does not match rows on keys. It also increases the row count rather than reducing it, which would worsen performance. This is not a valid optimization for a query that requires joining tables on common dimensions; it fundamentally changes the semantics and is inappropriate here.
C
Apply filters as early as possible in the query, ideally in a subquery or CTE, to reduce the row count before joins.
Pushing filters down to the earliest possible point reduces the number of rows that participate in joins and aggregations. Snowflake's optimizer can sometimes do this automatically, but complex queries with CTEs or subqueries may benefit from explicit early filtering. This directly decreases the data volume flowing through the Join and Aggregate nodes, lowering both time and resource consumption. It is a fundamental optimization technique for multi-join queries.
D
Increase the warehouse size to a 4X-Large to provide more memory for the joins.
Why it fails: Scaling up the warehouse can help with memory-intensive operations and reduce spilling, but it does not reduce the amount of data processed. The query will still read the same number of rows and perform the same joins. It may run faster due to more compute resources, but it does not address the root cause of large intermediate results. It is a costly approach that does not optimize the query itself.
E
Convert all `JOIN` clauses to `CROSS JOIN` to allow the optimizer more flexibility.
Why it fails: `CROSS JOIN` produces a Cartesian product, multiplying row counts and dramatically increasing data volume. It does not allow the optimizer to find better join paths; instead, it removes the join predicates and forces the worst-case scenario. This would make the query far slower and return incorrect results. It is never an optimization technique for reducing data processed in a multi-table join.

Question 6hardmultiple choice

A provider has shared a database with a consumer. The consumer reports that they can see the shared database but cannot query any tables because they get an error about insufficient privileges. The provider used the following command to create the share: CREATE SHARE MY_SHARE; then added a database and a table to the share. The provider also granted USAGE on the database and SELECT on the table to a role named SHARE_ROLE, and then granted SHARE_ROLE to MY_SHARE. What is the most likely cause of the consumer's issue?

Trap 1: The consumer must create a database from the share before they can…

The consumer must create a database from the share to access the data, but the scenario implies they already see the shared database, which means they have created it. The error occurs when querying tables, so the issue is likely within the share's privileges, not the consumer's database creation step.

Trap 2: The share was not granted to the consumer account using ALTER SHARE…

If the share was not granted to the consumer account, the consumer would not be able to see the shared database at all. The scenario states that the consumer can see the database, so the share must have been granted to the consumer account. Therefore, this is not the cause of the privilege error.

Trap 3: The provider must use a secure view instead of a table to share…

While secure views are often used to share data with fine-grained control, they are not mandatory. Tables can be shared directly. The error is about insufficient privileges, not about the type of object. The missing schema usage grant is the likely cause, not the absence of a secure view.

A
The provider must also grant USAGE on the schema containing the table to SHARE_ROLE.
In Snowflake, to grant access to a table in a share, the provider must grant USAGE on the database, USAGE on the schema, and SELECT on the table to the share. The scenario mentions granting USAGE on the database and SELECT on the table, but not USAGE on the schema. Without schema usage, the consumer cannot access the table, resulting in an insufficient privileges error.
B
The consumer must create a database from the share before they can query the tables.
Why it fails: The consumer must create a database from the share to access the data, but the scenario implies they already see the shared database, which means they have created it. The error occurs when querying tables, so the issue is likely within the share's privileges, not the consumer's database creation step.
C
The share was not granted to the consumer account using ALTER SHARE ... ADD ACCOUNT.
Why it fails: If the share was not granted to the consumer account, the consumer would not be able to see the shared database at all. The scenario states that the consumer can see the database, so the share must have been granted to the consumer account. Therefore, this is not the cause of the privilege error.
D
The provider must use a secure view instead of a table to share data.
Why it fails: While secure views are often used to share data with fine-grained control, they are not mandatory. Tables can be shared directly. The error is about insufficient privileges, not about the type of object. The missing schema usage grant is the likely cause, not the absence of a secure view.

Question 7mediummultiple choice

A security administrator needs to ensure that a set of sensitive columns in an existing table are automatically masked for all users except those with the role 'HR_ADMIN'. The masking must be applied without modifying the underlying data. Which Snowflake feature should be used?

Trap 1: Secure Views

Secure Views hide the view definition and can restrict access, but they do not automatically mask column values. You would need to manually build logic into the view to mask data, which is less flexible and does not meet the requirement of automatic masking without modifying data.

Trap 2: Object Tagging

Object tagging allows you to assign metadata tags to objects for classification and tracking, but it does not enforce data masking. Tags alone do not change query results or restrict access to column values. Therefore, it cannot automatically mask sensitive columns for users without HR_ADMIN.

Trap 3: Row Access Policies

Row Access Policies filter rows based on conditions, not columns. They are used to restrict which rows a user can see, not to mask column values. Applying a row access policy would not obfuscate the sensitive column data for unauthorized users.

A
Secure Views
Why it fails: Secure Views hide the view definition and can restrict access, but they do not automatically mask column values. You would need to manually build logic into the view to mask data, which is less flexible and does not meet the requirement of automatic masking without modifying data.
B
Object Tagging
Why it fails: Object tagging allows you to assign metadata tags to objects for classification and tracking, but it does not enforce data masking. Tags alone do not change query results or restrict access to column values. Therefore, it cannot automatically mask sensitive columns for users without HR_ADMIN.
C
Row Access Policies
Why it fails: Row Access Policies filter rows based on conditions, not columns. They are used to restrict which rows a user can see, not to mask column values. Applying a row access policy would not obfuscate the sensitive column data for unauthorized users.
D
Dynamic Data Masking
Dynamic Data Masking uses masking policies to obfuscate column values at query time based on the user's role. It does not alter the stored data. Applying a masking policy to the sensitive columns and granting the policy's exemption to HR_ADMIN achieves the requirement without data changes.

Question 8mediummultiple choice

A data engineer runs a query that joins a large fact table with a small dimension table. The query takes 12 minutes. The engineer notices that the small dimension table is broadcast to all compute nodes, and the fact table is redistributed across nodes. Which Snowflake feature is primarily responsible for this behavior, and what is its main benefit in this scenario?

Trap 1: Virtual warehouse scaling, which adds compute resources to handle…

Virtual warehouse scaling increases compute power (either by resizing or adding clusters), but it does not change the join algorithm or data movement strategy. The broadcast behavior is a result of the optimizer's decision, not of scaling. Scaling might improve overall performance but does not explain the specific redistribution pattern observed.

Trap 2: Result caching, which stores the query results for subsequent…

Result caching stores the output of a query for reuse, but it does not influence how a join is executed. The scenario describes the internal join method (broadcast vs. redistribution), which is determined by the optimizer, not by result caching. Result caching would only help if the exact same query were run again.

Trap 3: Micro-partitioning, which automatically divides tables into small…

Micro-partitioning is a storage optimization that allows efficient pruning of irrelevant data during table scans, but it does not dictate join strategies such as broadcast. The observed behavior is about data movement during join execution, which is handled by the optimizer's join strategy selection, not by micro-partitioning itself.

A
Virtual warehouse scaling, which adds compute resources to handle the join operation more quickly.
Why it fails: Virtual warehouse scaling increases compute power (either by resizing or adding clusters), but it does not change the join algorithm or data movement strategy. The broadcast behavior is a result of the optimizer's decision, not of scaling. Scaling might improve overall performance but does not explain the specific redistribution pattern observed.
B
Result caching, which stores the query results for subsequent identical queries, reducing execution time.
Why it fails: Result caching stores the output of a query for reuse, but it does not influence how a join is executed. The scenario describes the internal join method (broadcast vs. redistribution), which is determined by the optimizer, not by result caching. Result caching would only help if the exact same query were run again.
C
Query optimizer's join strategy selection, which chooses broadcast for small tables to minimize data movement and improve performance.
The Snowflake query optimizer analyzes table statistics and determines the most efficient join strategy. For a large fact table joined with a small dimension table, broadcasting the small table to all nodes avoids redistributing the large table, reducing network overhead and speeding up the join. This is a core optimization technique in Snowflake's architecture.
D
Micro-partitioning, which automatically divides tables into small chunks and enables pruning during scans.
Why it fails: Micro-partitioning is a storage optimization that allows efficient pruning of irrelevant data during table scans, but it does not dictate join strategies such as broadcast. The observed behavior is about data movement during join execution, which is handled by the optimizer's join strategy selection, not by micro-partitioning itself.