Select Two (Multi-Select) Questions

External tables support the same performance optimizations as native tables, including clustering keys.

Why it fails: Native Snowflake tables use proprietary micro-partitioning and can be optimized with clustering keys for performance. External tables, however, do not support clustering keys because Snowflake does not control the physical layout of the files in the external cloud storage. Consequently, queries on external tables are generally slower than on native tables.

Data in external tables can be updated directly using standard DML commands like UPDATE or DELETE.

Why it fails: External tables are strictly read-only objects in Snowflake. Since the data resides in external storage like S3 or Azure Blob, Snowflake cannot modify those files directly using SQL DML. Any changes to the data must be made by modifying the source files in the cloud storage provider's environment.

External tables store a version of the data in Snowflake's internal storage for faster access.

Why it fails: The defining characteristic of an external table is that it does NOT store the actual data within Snowflake. It only stores metadata about the files, such as their paths and formats. If you want to store the data internally for performance, you should use a standard table or a Materialized View on top of the external table.

Grant USAGE on the database to the consumer's role.

Why it fails: Granting USAGE on the database is not required for sharing and would not be effective because the consumer accesses the shared database through the share, not through direct grants. The provider should not grant privileges on the database to the consumer; instead, the share itself provides access. This action does not help achieve the goal and may be impossible across accounts.

Create a database role and grant it to the consumer.

Why it fails: Database roles are used within an account to manage privileges on database objects, but they cannot be granted to consumers in other accounts. Sharing is done through shares, not by granting roles across accounts. This action is not applicable to cross-account sharing and would not provide the desired access.

Add the table directly to the share and grant SELECT on the table to the consumer.

Why it fails: Adding a table directly to a share allows the consumer to see the table structure and all columns, which may expose sensitive metadata. Additionally, the provider cannot grant SELECT on the table to the consumer; the consumer accesses the table through the share with IMPORTED PRIVILEGES. This approach does not meet the requirement of hiding the underlying structure.

Executing the actual DML operations and data processing tasks.

Why it fails: Data processing, including DML operations like INSERT, UPDATE, and DELETE, is the responsibility of the Query Processing layer, specifically Virtual Warehouses. These warehouses provide the CPU and memory needed for heavy lifting. The Cloud Services layer only coordinates these actions and tracks the metadata changes resulting from the operations.

Storing the raw data files in micro-partitions within cloud storage.

Why it fails: The Storage layer is responsible for persisting data in highly optimized, compressed, and columnar micro-partitions. While the Cloud Services layer manages the metadata about where these partitions are located, it does not physically store the data itself. Storage is handled by the underlying cloud provider's object storage service.

Providing the physical hardware resources for the cloud environment.

Why it fails: Snowflake is a software-as-a-service platform that runs on top of public cloud providers like AWS, Azure, or GCP. It does not provide the physical hardware itself. Instead, it manages and abstracts the cloud provider's resources, such as virtual machines and object storage, to provide a seamless data cloud experience for users.

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.

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.

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.

The consumer can modify the shared data directly because they own the mounted database.

Why it fails: A database created from a share is read-only. The consumer does not own the underlying data and cannot perform DML operations on shared tables or views. Ownership of the mounted database object does not confer write access to the shared content, so this statement is incorrect.

The consumer must copy the shared data into their own tables before querying it.

Why it fails: Copying is not required and defeats the purpose of sharing. Consumers query shared data in place, and the data remains in the provider's storage. While consumers can choose to copy data if they want to modify it, that is optional and not a requirement for querying the share.

The consumer can create their own tables in the same schema as the shared objects.

Why it fails: Databases created from shares are read-only at the schema level as well. Consumers cannot create new objects such as tables or views inside the shared database or its schemas. They can create separate databases and join against shared data, but not add objects within the shared database itself.

External Tables

Why it fails: External tables allow users to query data stored in cloud storage without loading it into Snowflake. While they are useful for initial ingestion or data lake architectures, they do not inherently provide the change tracking or automated scheduling logic required for a robust CDC pipeline within Snowflake.

Stored Procedures

Why it fails: Stored procedures can contain the logic for data transformation, but they do not provide the mechanism for tracking changes or the automated trigger for execution. While often used inside a task, they are not the primary feature responsible for identifying new or modified data records.

Dynamic Tables

Why it fails: Dynamic tables are a newer feature that simplifies data engineering by declaratively defining the end state of a table. While they use streams and tasks internally, the question asks for the specific underlying features used to build a pipeline, and standard Streams/Tasks offer more granular control for complex logic.

Configure the warehouse to use a larger number of clusters with scaling policy set to ECONOMY.

Why it fails: The ECONOMY scaling policy is designed to minimize credit consumption by favoring fewer clusters, which can reduce performance during peak loads. For a data science team needing fast performance, a STANDARD scaling policy or a larger warehouse size is more appropriate. Multi-cluster settings do not improve single-query performance.

Enable multi-cluster warehouse with a minimum cluster count of 2.

Why it fails: Multi-cluster warehouses are designed for concurrency scaling, not for improving the performance of individual complex queries. They add clusters to handle many concurrent users, but a single complex query will not benefit from multiple clusters. For occasional complex queries, a larger single-cluster warehouse is more effective.

Set AUTO_SUSPEND to a low value (e.g., 60 seconds) to minimize idle costs.

Why it fails: While setting a low AUTO_SUSPEND reduces costs during idle periods, it does not directly optimize query performance. In fact, if the warehouse suspends too quickly, it may need to resume frequently, adding latency. For performance, the focus should be on compute resources, not suspension settings.

Encrypt historical data with a separate customer-managed key that is distinct from the key used for current data.

Why it fails: Snowflake encrypts data, including historical versions maintained by Time Travel, using its hierarchical key model, and customers do not assign a distinct key specifically for historical data. Time Travel does not introduce a separate encryption key for past versions, so this option describes a key management behavior that does not exist in this context.

Automatically fail over read and write operations to a secondary account in a different region without data loss.

Why it fails: Automatic failover to a secondary account in another region is provided by Snowflake's replication and failover features, not by Time Travel. Time Travel operates within a single account and retains historical versions of objects for a limited period; it does not manage cross-region availability or failover, so this option misattributes a different capability.

Permanently retain all historical versions of every table indefinitely without any retention period configuration.

Why it fails: Time Travel retention is bounded by a configurable retention period, with a default of one day and a maximum of 90 days for permanent tables. Historical data is not kept indefinitely, and extended retention requires explicit configuration and may incur additional storage costs. This option overstates the duration and permanence of Time Travel.

Set the STORAGE_ALLOWED_LOCATIONS parameter in the storage integration to restrict access to specific S3 paths.

Why it fails: While STORAGE_ALLOWED_LOCATIONS is a good security practice to limit access, it is not strictly required for the integration to function. The integration will work without it, though it may allow access to any location the IAM role can access. The required actions are creating the IAM role and granting permissions. This parameter is optional but recommended for least privilege.

Store AWS access keys and secret keys in the storage integration definition for authentication.

Why it fails: This is incorrect because storage integrations are designed to avoid storing long-term credentials. Instead, they use IAM role assumption with temporary credentials. Storing access keys is less secure and not the recommended method. Snowflake generates an external ID and uses it with the trust policy. Therefore, this is not a required action and should be avoided.

Configure the S3 bucket policy to allow public access so Snowflake can read the data.

Why it fails: This is incorrect and insecure. Public access is not required and is a security risk. Snowflake accesses the bucket using the IAM role, not public access. The bucket policy should instead allow the IAM role to perform actions. Making the bucket public could expose data to unauthorized users. Therefore, this is not a required action and is strongly discouraged.

A named file format object

Why it fails: A named file format defines how data in files should be parsed, such as delimiters and data types. While it is often used with COPY INTO and Snowpipe, it is not strictly required for setting up event notifications. The pipe can use an inline file format or a named one. Therefore, it is not a required component for enabling automated ingestion via event notifications.

A task to periodically check for new files

Why it fails: A task is a scheduled job within Snowflake, but Snowpipe is designed for event-driven ingestion, not polling. Using a task to periodically check for new files would be a manual polling approach and is not the automated event notification method. Snowpipe relies on cloud events, not scheduled tasks, so this option is not required and is incorrect for the scenario.

An external function to process the notifications

Why it fails: External functions are used to call code outside Snowflake, such as AWS Lambda. They are not used for receiving event notifications for Snowpipe. Instead, Snowpipe uses notification integrations to receive events directly from the cloud provider's event messaging service. An external function is not part of the standard Snowpipe automation setup and is therefore incorrect.

Increase the size of the virtual warehouse to reduce the number of partitions scanned.

Why it fails: Increasing the warehouse size adds more compute resources, which can improve performance for some workloads, but it does not reduce the number of partitions scanned. The number of partitions scanned is determined by pruning, which depends on the query filters and data organization. A larger warehouse may process the same number of partitions faster, but it does not change the volume of data read.

Use the SEARCH function in the WHERE clause for all string filters.

Why it fails: The SEARCH function is part of the search optimization service and is used for substring searches. It can accelerate certain queries, but it is not a general replacement for pruning. Using it for all string filters may not reduce partitions scanned and could add overhead. Pruning is primarily achieved through filters on clustering keys or natural ordering of data, not by using SEARCH indiscriminately.

Convert the table to a transient table to enable automatic pruning.

Why it fails: Transient tables are a table type that affects data retention and fail-safe, not pruning. Converting a table to transient does not change how micro-partitions are pruned. Pruning is based on the filters and clustering metadata, regardless of table type. This action would not reduce the number of partitions scanned and could have unintended data retention consequences.

Unloaded files are always encrypted with AES-256 regardless of the encryption settings on the external stage.

Why it fails: The encryption method and key size depend on the stage's encryption settings. While Snowflake may use strong encryption, it is not always AES-256. For example, client-side encryption uses a 128-bit key by default. Server-side encryption options vary. The statement that files are always encrypted with AES-256 is incorrect.

By default, Snowflake uses client-side encryption with a 128-bit key to encrypt unloaded files.

Why it fails: Snowflake does not use client-side encryption by default for unloaded files. By default, files are encrypted using server-side encryption with Snowflake-managed keys if the external stage does not specify encryption. Client-side encryption requires explicit configuration with a master key, and the default key size is 128-bit, but it is not the default behavior.

If no encryption is specified for the external stage, unloaded files are not encrypted and are stored in plaintext.

Why it fails: Snowflake automatically encrypts unloaded files even if no encryption is specified on the external stage. By default, it uses server-side encryption with Snowflake-managed keys. Files are never stored in plaintext. However, to use your own keys or client-side encryption, you must explicitly configure the stage.

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.

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.

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.

Use OBJECT_CONSTRUCT to iterate over the array and emit one row per element.

Why it fails: OBJECT_CONSTRUCT builds a single object from key-value pairs; it does not iterate over an array or produce multiple rows. It is useful for assembling output objects, but it cannot serve as the row-generating mechanism. Using it here would leave the array nested in a single row rather than exploding it.

Use PARSE_JSON to convert the array into a relational table with one column per object attribute.

Why it fails: PARSE_JSON interprets a string as a VARIANT value; it does not flatten arrays or infer a relational schema. It is typically used when loading JSON text into a VARIANT column. It cannot produce one row per element, so it does not satisfy the requirement.

Use GET_PATH to extract the array and then join it to a sequence generator to produce rows.

Why it fails: GET_PATH extracts a value at a given path, returning a single VARIANT value rather than multiple rows. Joining to a sequence generator would require knowing the array length in advance and manually indexing each element, which is fragile. FLATTEN already handles this natively and is the intended tool.

Grant SELECT on the base table to the share.

Why it fails: Granting SELECT on the base table would allow the consumer to query the base table directly, which the provider wants to prevent. The goal is to share only the secure view, not the underlying table. Therefore, this action is incorrect and would violate the security requirement.

Create a new role that has access to the base table and grant that role to the share.

Why it fails: Roles cannot be granted to a share; only privileges on objects can be granted. A share is a container for grants, not a role recipient. Creating a role and attempting to grant it to a share is not supported. The provider must grant privileges directly to the share, not through a role.

Grant USAGE on the database and schema containing the base table to the share.

Why it fails: Granting USAGE on the base table's database and schema would expose the existence of the base table and its schema to the consumer, but it would not grant SELECT on the table. However, it is unnecessary and could lead to confusion. The secure view runs with the owner's rights, so the consumer does not need access to the base table's database. This action is not required and may weaken security.

Enable STRIP_OUTER_ARRAY = TRUE on the file format to flatten the JSON into columns.

Why it fails: STRIP_OUTER_ARRAY removes the enclosing array so each element becomes a separate row; it does not flatten objects into columns nor explain why individual paths are NULL. It is a legitimate JSON loading option but unrelated to diagnosing unexpected NULLs. Setting it without understanding the source structure could change row counts rather than reveal the parsing fault.

Set ON_ERROR = 'ABORT_STATEMENT' and rerun the load to force Snowflake to reveal the malformed records.

Why it fails: ABORT_STATEMENT simply halts the load when an error occurs; it does not report which JSON elements failed to parse or why VARIANT columns became NULL. The statement fails without the detailed diagnostics needed to understand parsing behavior. It is a load-control setting rather than a diagnostic facility, so it does not help identify the cause of the NULLs.

Change the file format TYPE to CSV so Snowflake parses each line as a flat record and exposes the raw text.

Why it fails: Switching the file format to CSV misinterprets JSON content and would produce different, unrelated parsing errors instead of clarifying the VARIANT issue. The scenario concerns JSON parsing, so altering the format type changes the semantics entirely. It does not surface the underlying problem and risks corrupting how the data is interpreted.

Grant REFERENCES on the table to the share to allow future columns to be visible.

Why it fails: REFERENCES is used for foreign key constraints, not for granting query access or making columns visible. It does not enable SELECT access. Granting REFERENCES would not allow the consumer to query the table or see new columns. This privilege is irrelevant to data sharing for query purposes.

Grant SELECT on each future column to the share as they are added.

Why it fails: Snowflake does not support column-level grants for shares. Privileges are granted at the table level. When SELECT is granted on a table, all current and future columns are included. There is no need to grant privileges on individual columns. This option describes an unnecessary and unsupported action.

Create a secure view that selects all columns from the table and grant SELECT on the view to the share.

Why it fails: While a secure view can be used to share data, it is not required to automatically include future columns. A secure view would need to be altered to include new columns if it explicitly lists them. If the view uses SELECT *, it might include new columns, but that is not guaranteed and depends on the view definition. This adds complexity and is not a mandatory step.

Disable the query result cache to ensure queries always use the latest data.

Why it fails: Disabling the query result cache would force every query to execute, even if the same query was run recently and the data hasn't changed. This increases compute usage and cost, and does not improve performance for ad-hoc queries. The result cache is beneficial for repetitive queries and does not affect data freshness if the underlying data changes.

Set the warehouse to run 24/7 without auto-suspend to avoid startup latency.

Why it fails: Running a warehouse 24/7 without auto-suspend incurs continuous credit consumption, even when no queries are running. This is not cost-efficient for ad-hoc workloads that may have idle periods. Startup latency is minimal and does not justify the high cost. Auto-suspend is recommended for intermittent usage.

Increase the warehouse size to 4X-Large to ensure queries run as fast as possible.

Why it fails: Increasing warehouse size provides more compute resources per query, but for ad-hoc queries with varying concurrency, it may not be cost-effective. Larger warehouses consume more credits per hour. The team may not need such a large size; multi-cluster scaling is more appropriate for concurrency. Oversizing can lead to unnecessary costs without proportional benefits.

Grant the SECURITYADMIN role to all analysts so they can manage their own masking policies.

Why it fails: Granting SECURITYADMIN broadly violates least privilege and gives analysts the ability to alter users, roles, and grants account-wide. It does not provide a scalable protection mechanism and actively increases risk. Managing masking policies should remain with a small governance group, and analysts should receive only the data access they require through roles.

Apply a separate masking policy to every sensitive column using ALTER TABLE for each column.

Why it fails: Attaching a distinct masking policy to each column manually does not scale and is exactly the manual editing the team wants to avoid. It also creates maintenance overhead because policy changes must be repeated across many objects. Tag-based masking is the intended alternative for consistent, centralized protection across many tables.

Enable Tri-Secret Secure to automatically classify and mask sensitive columns.

Why it fails: Tri-Secret Secure is a security feature that combines a Snowflake-managed key with a customer-managed key for encryption at rest. It protects data cryptographically but does not classify columns or apply masking policies, so it does not fulfill the governance team's requirement to classify and mask sensitive data consistently across many tables.

Network policy

Why it fails: A network policy restricts access based on IP addresses, not user roles or data content. It does not provide column-level masking or tagging. It is used for network-level security, such as allowing or blocking connections from certain IP ranges. It is unrelated to the requirements of data classification and column masking.

Row access policy

Why it fails: A row access policy filters rows based on conditions, not columns. It cannot mask or restrict access to specific column values. While it can restrict which rows a role sees, it does not provide column-level masking. Therefore, it does not meet the requirement to show unmasked data only to a specific role for certain columns.

Secure view

Why it fails: A secure view hides the view definition and prevents certain optimizations, but it does not inherently provide column-level masking based on role. While a secure view could include a CASE statement to mask data, it would not be as flexible or centralized as a masking policy. It also does not address the tagging requirement. Thus, it is not the best fit.