Select Two (Multi-Select) Questions

XLSX

Why it fails: Snowflake does not natively support exporting data directly to the XLSX (Excel) format. To export data in this format, you would need to unload to a supported format like CSV and then use an external tool or script to convert that file into the final Excel document structure.

SQL

Why it fails: Snowflake does not support unloading data into a raw .sql file format. While you can export data, it must be in a standard data exchange format like CSV, JSON, or Parquet. SQL is a command language and not a data storage format, so it is not a valid output format.

PDF

Why it fails: PDF is a document formatting standard and is not a supported data format for unloading from Snowflake tables. Data movement is intended for structured or semi-structured data, and PDF does not fulfill the requirements for machine-readable data exchange that Snowflake's engine is designed to handle during export operations.

Disable Time Travel on critical tables to avoid storage charges and rely solely on external backups.

Why it fails: Disabling Time Travel removes the ability to query historical data or use UNDROP, directly conflicting with the requirement to reverse accidental modifications and restore dropped tables. External backups are outside Snowflake's native recovery and cannot be restored with UNDROP. Turning off Time Travel reduces cost but eliminates the primary self-service recovery mechanism the scenario depends on.

Rely on Fail-safe to restore dropped tables without involving Snowflake Support.

Why it fails: Fail-safe is a non-configurable 7-day period accessible only by Snowflake Support. Users cannot query Fail-safe data or run UNDROP against it. Depending on Fail-safe for self-service recovery contradicts its design. It is a last-resort disaster recovery mechanism, not a routine restoration path, so it cannot satisfy the requirement of restoring tables without a support ticket.

Use transient tables for all critical data to reduce storage costs while keeping recovery options.

Why it fails: Transient tables are capped at 1 day of Time Travel and have Fail-safe disabled. That severely limits the ability to reverse modifications or restore dropped tables beyond a single day. Choosing transient tables for critical data undermines the recovery objectives. Cost savings do not compensate for the sharply reduced recovery window and the absence of Fail-safe protection.

It eliminates the need for primary keys on tables.

Why it fails: Change Tracking is independent of primary keys. While primary keys are good for data integrity, they are not a prerequisite for enabling change tracking on a table. The system tracks modifications to the underlying micro-partitions, regardless of whether a unique key constraint is defined on the table.

It automatically cleans up stale data in the target table.

Why it fails: Change tracking only tracks changes; it does not perform data cleanup or record deletion in the target table. The logic to handle deletions or updates in the target table must be explicitly implemented by the developer, typically using a MERGE operation driven by the change data.

It is only available for tables in the Enterprise edition.

Why it fails: Change tracking is a standard feature of Snowflake and is available across all editions. It does not require specific licensing levels, making it accessible to all users for building robust, incremental ELT pipelines, regardless of the edition used for their Snowflake account.

Set the SINGLE parameter to TRUE to ensure all data is consolidated into one large file.

Why it fails: Setting SINGLE to TRUE disables parallel execution during the unload process. This forces the entire dataset to be processed by a single thread, which significantly increases the time required to export large tables and eliminates the performance benefits provided by the distributed architecture of the Snowflake virtual warehouse.

Disable compression using the COMPRESSION = NONE parameter to reduce the CPU load on the warehouse.

Why it fails: While disabling compression might slightly reduce CPU usage, it significantly increases the volume of data transferred over the network and the storage costs in the S3 bucket. Most modern data tools prefer compressed formats like GZIP, and the overhead of compression is usually negligible compared to the network I/O savings.

Always use the OVERWRITE = TRUE parameter to prevent the command from failing if files already exist.

Why it fails: While OVERWRITE can prevent errors, it is not a performance optimization and can be dangerous in production environments. If used incorrectly, it can lead to accidental data loss by overwriting existing exports that might still be needed by other processes, making it a management risk rather than a best practice for optimization.

Data classification can only be applied to existing tables.

Why it fails: Data classification is an automated process that scans tables and suggests tags based on content. It is not restricted to existing tables and can be run iteratively as new data is loaded. The process is designed for flexibility to handle evolving schema structures and dynamic data environments.

Classification results are stored in the user's local file system.

Why it fails: Classification results are integrated directly into the Snowflake metadata system, allowing for seamless usage within SQL queries and governance policies. Storing metadata in a local file system would contradict the cloud-native, centralized management architecture that Snowflake promotes for its data governance features and security management.

Classification requires the data to be in a flat file format.

Why it fails: Data classification operates on the actual data stored within Snowflake tables, regardless of the original file format used to import the data. The service reads the contents of columns directly, meaning it can classify data regardless of whether it originated from Parquet, CSV, or JSON sources.

Convert the permanent tables to transient tables to increase the maximum allowed retention window.

Why it fails: Transient tables have a maximum Time Travel retention of one day and do not receive Fail-safe protection. Converting permanent tables to transient would reduce, not increase, the available retention window and would remove Fail-safe coverage. This action directly contradicts the requirement for 30 days of queryable history.

Rely on Fail-safe to provide the 30-day queryable history window for dropped tables.

Why it fails: Fail-safe provides a seven-day, non-queryable recovery window that begins after the Time Travel period ends, and it requires Snowflake Support to restore data. It cannot be queried directly, and its duration is fixed at seven days, so it cannot satisfy a 30-day queryable history requirement.

Enable Fail-safe on the tables by setting FAILSAFE_DAYS to 30 using ALTER TABLE.

Why it fails: There is no FAILSAFE_DAYS parameter that can be set via ALTER TABLE. Fail-safe duration is fixed at seven days and is managed by Snowflake, not configurable per table. Attempting this action would fail because the parameter does not exist, and it would not provide the required queryable 30-day window.

Use `DataFrame.union()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Why it fails: union() concatenates rows from two DataFrames with the same schema, rather than joining them on a key. This produces a vertical stack, not a relational join. Applying a filter afterward would not achieve the intended join semantics and would likely yield incorrect results.

Use `DataFrame.join()` to combine the DataFrames, then `DataFrame.select()` to apply the condition.

Why it fails: select() is used to project columns or create expressions, not to filter rows. While you can include a conditional expression in select(), it does not remove rows; it returns a column with boolean or conditional values. To filter rows, you need filter() or where().

Use `DataFrame.merge()` to combine the DataFrames, then `DataFrame.filter()` to apply the condition.

Why it fails: Snowpark's DataFrame API does not have a merge() method. Merge is a SQL DML statement for updating or inserting rows, not a DataFrame join operation. Using merge() would cause an error, and it is not a valid way to combine two DataFrames for this transformation.

Tag-based masking policies to enforce access controls based on tags.

Why it fails: Tag-based masking policies enforce column-level security by masking data, but they do not provide auditing or usage attribution. They control what data is shown, not who accessed it. While they are part of governance, they do not meet the requirement for auditable access or attribution to users and roles.

Data Classification to automatically tag sensitive columns and track their usage.

Why it fails: Data Classification identifies and tags sensitive columns but does not track access or usage. It is a discovery tool that helps categorize data, not an auditing mechanism. It cannot attribute data access to users or roles. Therefore, it does not fulfill the requirement for auditable access or usage attribution.

Object Dependencies in the ACCOUNT_USAGE schema to map relationships between tables and views.

Why it fails: Object Dependencies show how objects reference each other, such as views depending on tables. This helps understand data lineage but does not provide access auditing or user attribution. It cannot track who accessed data or when. Therefore, it does not meet the requirement for auditable access or usage attribution.

Snowpark automatically caches all intermediate transformation results in memory on the client side to speed up iterative development.

Why it fails: Snowpark does not automatically cache intermediate results on the client side. Data remains in Snowflake, and caching is managed by the Snowflake service, not the client. Client-side caching would be impractical for large datasets and is not a feature of Snowpark. This statement misrepresents how Snowpark handles data.

Snowpark requires data to be extracted to a local Python environment for any transformation that uses third-party libraries.

Why it fails: Snowpark is designed to push computations to Snowflake, and it supports third-party libraries within UDFs without extracting data. You can package dependencies and run them in Snowflake. Extracting data locally is not required and would be inefficient. This statement is incorrect because Snowpark avoids data movement.

Snowpark provides a built-in visual interface for designing transformation pipelines without writing code.

Why it fails: Snowpark is a code-first API available in Python, Java, and Scala. It does not provide a built-in visual interface for designing pipelines. While Snowflake offers other tools like Snowsight for SQL, Snowpark itself is programmatic. This statement is false and misrepresents Snowpark's capabilities.

Fail-safe can be configured to extend beyond 7 days for permanent tables in Enterprise Edition.

Why it fails: Fail-safe is a fixed 7-day period for permanent tables and cannot be configured or extended. It is not a user-accessible feature; only Snowflake Support can recover data from Fail-safe. This option misrepresents Fail-safe as a configurable setting, which it is not.

Transient tables can have a Time Travel retention period of up to 90 days if the account is Enterprise Edition.

Why it fails: Transient tables have a maximum Time Travel retention of 1 day, regardless of account edition. They do not support extended retention periods like permanent tables. This option incorrectly applies the permanent table limit to transient tables, which are designed for lower-cost, short-term data.

Time Travel retention for permanent tables can be set to 0 days to disable it, but Fail-safe still applies.

Why it fails: Setting DATA_RETENTION_TIME_IN_DAYS to 0 disables Time Travel for a permanent table, but Fail-safe still provides 7 days of recovery for permanent tables. However, this statement is not entirely accurate because Fail-safe applies only after Time Travel expires, and with 0 days, Fail-safe begins immediately. But the question asks for accurate descriptions; this option is partially true but not one of the two best answers. The key inaccuracies: Fail-safe is not user-configurable, and the statement implies it applies, which is true, but the focus is on the two correct statements about maximum retention and Fail-safe duration.

Increase the virtual warehouse size so more compute nodes are available to evaluate the filter predicate in parallel.

Why it fails: Warehouse size changes how many threads evaluate the predicate, not how many micro-partitions must be read. If pruning is ineffective, every node still receives partitions that cannot match, so a larger warehouse only scans the same unpruned data faster at higher credit cost, leaving the high scanned-partition ratio unchanged.

Convert the table to a view over the same data so the optimizer can push the filter predicate down into the view definition.

Why it fails: A view is a stored query, not a physical reorganization, so it cannot change which micro-partitions exist or their value ranges. Predicate pushdown may occur, but with no clustering or constant-bounded predicate the scan still touches the same partitions. This adds a layer of indirection without improving pruning.

Apply a filter transformation inside the query that wraps the filtered column in a function such as UPPER or CAST before comparing it to a literal.

Why it fails: Wrapping the filtered column in a function prevents the optimizer from using micro-partition pruning metadata, because the stored min and max values no longer match the transformed expression. This typically forces a full scan and makes the pruning problem worse, so it is the opposite of what the scenario requires.

Set ON_ERROR = CONTINUE and inspect the target table row counts

Why it fails: ON_ERROR = CONTINUE skips bad rows and loads the rest, which introduces exactly the partial data the team wants to avoid before promotion. Row counts alone would not reveal which rows were rejected or why, and the load would already have modified the target. This option conflates error tolerance with validation and would leave the table in an unclean state during a pre-production check.

Use FORCE = TRUE to reload all files and compare checksums

Why it fails: FORCE = TRUE bypasses load metadata and reloads files that may already be loaded, creating duplicates in the target table. Comparing checksums is a manual and indirect way to detect problems, and it does not report row-level rejection reasons. This option increases risk and effort while failing to provide the file-level and row-level diagnostics the team actually needs.

Create a stream on the target table and read the change records

Why it fails: A stream captures change data after rows are inserted, so it only helps once data has already landed in the table. It cannot show which staged files were parsed or why rows were rejected, and it does not provide pre-load validation. Using a stream here is a post-hoc observation technique that does not satisfy the requirement to validate before promoting the load.

Enable the ACCOUNTADMIN role to run the classification process.

Why it fails: Although ACCOUNTADMIN has all privileges and can run classification, it is not a requirement. Any role with the necessary privileges (USAGE on database and schema, SELECT on table) can run classification. Using ACCOUNTADMIN is not mandatory and goes against the principle of least privilege. Thus, this is not a required action.

Set the CLASSIFICATION_PROFILE parameter on the table to enable automatic classification.

Why it fails: There is no table-level parameter called CLASSIFICATION_PROFILE that enables automatic classification. Classification is performed by calling the SYSTEM$CLASSIFY function or using the UI, and it can be scheduled. The parameter does not exist. Therefore, this option is incorrect.

Create a custom classifier that defines the patterns for sensitive data before running classification.

Why it fails: While custom classifiers can be used to enhance classification, they are not required to enable Data Classification. Snowflake provides native classifiers that can identify common sensitive data types out of the box. The question asks for required actions to enable classification, and creating a custom classifier is optional. Therefore, this is not a required action.

Snowpipe periodically polls the external stage for new files based on a defined schedule.

Why it fails: Snowpipe does not poll the stage; it relies on event notifications. Polling is a different mechanism, often implemented with tasks or external schedulers. With AUTO_INGEST, Snowpipe is event-driven, not schedule-driven. Polling would introduce latency and is not how Snowpipe operates.

AUTO_INGEST = TRUE requires the stage to be an internal stage.

Why it fails: AUTO_INGEST is specifically designed for external stages, as it relies on cloud storage event notifications. Internal stages do not support event notifications from cloud providers. Therefore, AUTO_INGEST cannot be used with internal stages; it requires an external stage with a notification integration.

Snowpipe automatically creates a notification integration if one does not exist.

Why it fails: Snowpipe does not automatically create notification integrations. The data engineer must manually create a notification integration and configure the cloud storage to send events to Snowflake. Without this setup, AUTO_INGEST will not function. The integration is a separate object that must be created and granted to the pipe.

Increase the warehouse size from Medium to Large.

Why it fails: Scaling up a warehouse increases compute power for individual queries, but it does not increase concurrency. A larger warehouse still has a fixed number of concurrent query slots, and adding more resources to a single cluster can actually increase queueing if many small queries are competing. The bottleneck is concurrency, not per-query performance, so scaling up is not the right solution and may increase cost without reducing queueing.

Set the STATEMENT_QUEUED_TIMEOUT_IN_SECONDS parameter to a low value.

Why it fails: This parameter controls how long a statement waits in the queue before timing out. Setting it low would cause queries to fail rather than reduce queueing. It does not increase concurrency or throughput; it simply aborts waiting queries. While it can free up queue slots, it does so by failing queries, which is not a performance optimization and could lead to application errors. It does not address the root cause of high concurrency.

Enable the 'USE_CACHED_RESULT' parameter and increase the result cache size.

Why it fails: Result caching can speed up repeated identical queries, but it does not reduce queueing for new or unique queries. The scenario describes many small, frequent queries that are likely different, so caching would have limited impact. Also, there is no parameter to increase result cache size; it is managed by Snowflake. This option does not address the concurrency bottleneck and is not a recommended practice for reducing queueing.

Set the file format TYPE to CSV with a delimiter that matches the JSON structure.

Why it fails: CSV cannot represent nested arrays and objects natively, so using it for JSON content would flatten or corrupt the structure. Choosing a delimiter does not help parse nested JSON, and the varying keys would not be preserved as they are with a VARIANT column.

Define a fixed relational schema for every possible key before loading.

Why it fails: A fixed schema requires knowing all keys in advance, which contradicts the requirement that keys vary between records. New or missing keys would cause load errors or nulls, and nested arrays would need to be flattened, losing the original structure that VARIANT preserves.

Use MATCH_BY_COLUMN_NAME = CASE_SENSITIVE to map JSON keys to columns.

Why it fails: MATCH_BY_COLUMN_NAME is intended for loading semi-structured data into separate relational columns by key name, which requires pre-defined columns and does not suit varying keys. It also does not preserve nested arrays as a single structure, so it does not meet the design goals.

The ability to automatically materialize transformation results into a new table without explicit DDL.

Why it fails: Snowpark does not automatically materialize results into tables; you must explicitly write the DataFrame to a table using the write method. While it simplifies data persistence, it does not eliminate the need for DDL or explicit write operations. This is not a specific capability for transformations.

The ability to execute transformations on data without moving it out of Snowflake's storage layer.

Why it fails: While Snowpark processes data within Snowflake, this is a general benefit, not a specific capability for transformations. The question asks for capabilities that support complex data cleaning and feature engineering, and this is more of an architectural advantage. It is not a distinct feature like the DataFrame API or UDFs.

The ability to use the Snowpark ML library for building and deploying machine learning models directly in Snowflake.

Why it fails: Snowpark ML is a library for machine learning, not specifically for data transformation. While it can be used in feature engineering, it is not a core transformation capability. The question asks for capabilities designed to support transformations, and Snowpark ML is more focused on ML workflows.

Users can query data in Fail-safe using the AT or BEFORE clauses, similar to Time Travel.

Why it fails: Fail-safe data is not accessible to users via SQL. It can only be recovered by Snowflake Support. The AT and BEFORE clauses are used for Time Travel, which is user-accessible. Fail-safe is a last-resort recovery mechanism and does not allow direct queries. This statement incorrectly suggests that Fail-safe data can be queried like Time Travel data, which is not the case.

Fail-safe applies to transient tables, providing an additional layer of protection after Time Travel.

Why it fails: Transient tables do not have Fail-safe. They only support Time Travel with a maximum retention of 1 day. Once the Time Travel period ends, transient table data is purged and cannot be recovered. Fail-safe is only for permanent tables. This statement is incorrect because it assigns Fail-safe to transient tables, which is not supported.

Fail-safe can be disabled at the account level to reduce storage costs.

Why it fails: Fail-safe is a mandatory feature for permanent tables in Snowflake and cannot be disabled. It provides a safety net for disaster recovery. Storage costs for Fail-safe are incurred after the Time Travel period and are not separately configurable. Users cannot turn off Fail-safe. This statement is incorrect because Fail-safe is always enabled for permanent tables and cannot be disabled by users.

Permanent tables with DATA_RETENTION_TIME_IN_DAYS set to 0

Why it fails: Setting retention to 0 disables Time Travel on a permanent table, but Fail-safe still applies after any data changes. Permanent tables always carry the 7-day Fail-safe window regardless of the Time Travel setting. This option incorrectly assumes that disabling Time Travel also removes Fail-safe, which is not the case.

External tables referencing cloud storage files

Why it fails: External tables reference data outside Snowflake, and Fail-safe does not apply to the external files or provide the same recovery model. However, this question asks specifically about table types exempt from Fail-safe storage charges within Snowflake's protection model, and external tables are not typically counted among the transient and temporary categories used for this purpose.

Permanent tables in a database with a short retention default

Why it fails: Database-level retention defaults affect Time Travel duration, not Fail-safe eligibility. A permanent table remains Fail-safe covered even if its Time Travel window is short. This option confuses retention configuration with table type, leading to an incorrect assumption that database defaults can exempt a permanent table from Fail-safe.

Tag-based masking policies cannot be applied to system tags; only user-defined tags support masking.

Why it fails: This is incorrect. Snowflake supports attaching masking policies to both system tags and user-defined tags. System tags, such as those from Data Classification, can have masking policies associated with them. This allows for automated protection of classified data without manual intervention.

Data Classification can only tag columns with user-defined tags, not system tags.

Why it fails: Data Classification primarily uses system tags to store classification results. While it can also apply user-defined tags if configured, its core functionality relies on system tags like SNOWFLAKE.CORE.SEMANTIC_CATEGORY. Therefore, it does tag columns with system tags, not only user-defined tags.

Data Classification requires that all tags be manually created before classification can run.

Why it fails: Data Classification does not require manual tag creation. It uses built-in system tags and can also apply user-defined tags if configured. The classification process itself identifies and tags columns automatically. Manual tag creation is optional and not a prerequisite.