Data Loading, Unloading, and Connectivity practice questions

VALIDATION_MODE = RETURN_ALL_ERRORS

Why it fails: While 'RETURN_ERRORS' is a valid setting, 'RETURN_ALL_ERRORS' is not a recognized keyword for the VALIDATION_MODE parameter. Snowflake syntax is strict, and using the wrong keyword will result in a syntax error. The correct option identifies the errors encountered in the files being processed during the validation phase.

ON_ERROR = RETURN_ERRORS

Why it fails: The ON_ERROR parameter and VALIDATION_MODE parameter serve different purposes. ON_ERROR defines how the system handles errors during an active load, while VALIDATION_MODE is specifically for testing the load without inserting data. Combining the two incorrectly will lead to a command failure as 'RETURN_ERRORS' is not a valid value for ON_ERROR.

VALIDATION_MODE = RETURN_1_ROWS

Why it fails: The VALIDATION_MODE parameter does support returning a specific number of rows (e.g., RETURN_n_ROWS), but this is used to verify that the data mapping is correct for the first few records. It does not provide a comprehensive list of all errors in the file set, making it less effective for troubleshooting a large, failing batch.

Using the VALIDATE_PIPE() system function to check for errors in the last 24 hours.

Why it fails: There is no VALIDATE_PIPE() function in Snowflake. To validate errors for a pipe, users typically query the LOAD_HISTORY view or use the Snowpipe REST API status endpoints. Using non-existent functions is a common pitfall in exam scenarios where users must distinguish between real and fabricated system functions.

Querying the Account Usage view LOAD_HISTORY.

Why it fails: The LOAD_HISTORY view in the Account Usage schema only contains data for bulk loads performed using the COPY INTO command. It does not include data for Snowpipe loads. To see Snowpipe history, one must use the PIPE_USAGE_HISTORY view or the SNOWPIPE_LOAD_HISTORY function in the Information Schema, which are specifically designed for pipe monitoring.

Executing the SHOW PIPES command and checking the 'last_load_time' column.

Why it fails: While SHOW PIPES provides metadata about the pipe definition, it does not provide a detailed history of the files processed. The output of SHOW PIPES is relatively static and is more useful for checking the configuration (like the notification channel) rather than for auditing the success or failure of individual data ingestion events over time.

Performing an inner join with another existing table to enrich the data.

Why it fails: The transformation capabilities within a COPY INTO command are limited and do not support complex operations like JOINs or GROUP BY clauses. If data enrichment from other tables is required, the data must first be loaded into a staging table and then transformed using a standard INSERT INTO... SELECT statement with the join logic.

Using an aggregate function like SUM() to consolidate rows during the load.

Why it fails: Aggregate functions are not allowed in the SELECT statement of a COPY INTO command. The ingestion process is designed to be a row-by-row mapping operation. To perform aggregations, you must load the raw data into a table first and then use a separate query or a view to calculate the sums or averages.

The AWS IAM User Access Key and Secret Key for the bucket owner.

Why it fails: Storage Integrations are specifically designed to avoid the use of static Access Keys and Secret Keys. Instead of using these credentials, Snowflake uses IAM Roles. This 'keyless' approach is a security best practice because it eliminates the risk of sensitive credentials being leaked or stored in plain text within Snowflake's metadata.

The S3 Bucket's public URL and the owner's email address.

Why it fails: Public URLs and email addresses are not used for configuring Storage Integrations. Access is managed through the AWS Resource Name (ARN) of the bucket and the IAM Role permissions. Public access to S3 buckets should generally be disabled for security reasons, and Snowflake accesses the data via the private AWS backbone using the configured IAM trust.

The Snowflake Account Locator and the AWS VPC ID.

Why it fails: While the Account Locator identifies your Snowflake account, it is not directly used in the IAM trust relationship configuration for a Storage Integration. Similarly, the VPC ID is relevant for network-level security (like PrivateLink), but it is not a required component of the basic IAM-based Storage Integration setup for accessing S3 buckets.

FIELD_OPTIONALLY_ENCLOSED_BY = '"'

Why it fails: This parameter specifies the character used to enclose strings, but it does not dictate how internal escaped quotes within those strings should be processed by the parser during the high-speed data ingestion process.

ESCAPE = 'NONE'

Why it fails: Disabling the escape character entirely will cause the parser to fail immediately upon encountering the first embedded quote because it will misinterpret it as the premature termination of the enclosing field string.

SKIP_HEADER = 1

Why it fails: Skipping headers only ignores the first row of the input file to bypass column name records and has no functional impact on how row values containing embedded quotes or special characters are parsed.

Hardcoding the AWS_SECRET_KEY and AWS_ACCESS_KEY in the CREATE STAGE statement.

Why it fails: Embedding credentials directly into SQL statements is considered a poor security practice because those secrets are visible in query history and metadata. It makes rotation difficult and increases the risk of credential leakage if unauthorized users gain access to the Snowflake account or the logs of the administrative tools.

Setting the stage to PUBLIC to allow easier debugging during the initial setup phase.

Why it fails: Making a stage public or opening up bucket permissions to everyone is a severe security vulnerability that exposes sensitive data to the open internet. Security should be configured correctly from the start using the principle of least privilege, and debugging should be performed using specific IAM policies and Snowflake roles.

Disabling server-side encryption on the S3 bucket to improve loading performance.

Why it fails: Disabling encryption does not provide significant performance benefits for data loading and introduces significant compliance and security risks. Snowflake seamlessly handles encrypted data from S3, and maintaining encryption at rest is a standard requirement for protecting sensitive organizational data and meeting various regulatory standards in the cloud.

The entire statement will fail, and zero rows will be loaded into the table.

Why it fails: This would only happen if ON_ERROR was set to ABORT_STATEMENT, which is the default behavior. Since the command explicitly uses CONTINUE, Snowflake is instructed to ignore the individual rows that fail parsing and proceed with the rest of the file and the rest of the fileset.

Only 'file2.csv' will be loaded, and 'file1.csv' will be completely skipped.

Why it fails: Snowflake processes files individually, but the CONTINUE setting applies to rows within those files. It will not discard an entire file just because a few rows are malformed; instead, it will extract every valid row it can find from both files to maximize the amount of data ingested.

The command will load 300 rows, but the 5 malformed rows will contain NULL values.

Why it fails: Snowflake does not automatically convert malformed records into rows with NULL values when using CONTINUE. Malformed records are those that cannot be parsed according to the FILE_FORMAT definition, such as having the wrong number of columns or invalid characters, so they are skipped entirely rather than being filled with NULLs.

SINGLE = TRUE and MAX_FILE_SIZE = 52428800

Why it fails: Setting SINGLE to TRUE forces Snowflake to generate only one file regardless of the data volume. This directly contradicts the requirement to split the output into multiple files. Even if MAX_FILE_SIZE is set, the SINGLE parameter takes precedence, potentially leading to a file much larger than the desired 50MB.

SINGLE = FALSE and MAX_FILE_SIZE = 50

Why it fails: While SINGLE = FALSE allows for multiple files, the MAX_FILE_SIZE parameter expects a value in bytes, not megabytes. Setting it to 50 would cause Snowflake to create an extremely large number of tiny files, each containing only a few bytes of data, which is inefficient and likely to cause errors.

OVERWRITE = TRUE and FILE_EXTENSION = 'csv'

Why it fails: These parameters control whether existing files in the stage are replaced and what the file extension should be, respectively. Neither of these options has any influence over the number of files generated or the maximum size of each file, making them irrelevant to the developer's specific file size requirement.

It loads data directly into a table using the INSERT command for each message.

Why it fails: The connector does not use individual INSERT statements, as this would be highly inefficient for high-volume streams. Instead, it buffers messages and uses Snowpipe to load them in batches, which is the standard Snowflake best practice for achieving high throughput and low latency in continuous data ingestion.

It requires the user to manually create a Snowpipe and a Stage for every topic.

Why it fails: One of the main benefits of the Kafka Connector is automation. It automatically creates the necessary internal stages and pipe objects required to move data from Kafka to Snowflake. Users only need to provide the configuration; the connector manages the underlying Snowflake objects on their behalf.

It can only ingest data from Kafka topics that are formatted in Parquet or Avro.

Why it fails: The Kafka Connector is quite flexible and supports various formats, including JSON and Avro. While Parquet is a common format in big data ecosystems, the connector is not limited to it. It is designed to handle the most common serialization formats used within the Kafka community seamlessly.

Unloaded data is stored in plain text to allow for faster downloading via the GET command.

Why it fails: Snowflake never stores data in plain text within its managed environment, including internal stages. Security is prioritized over the marginal performance gains that might be achieved by skipping encryption. This ensures compliance with data protection standards and protects sensitive information at all times.

Users must provide a master key to encrypt data during the COPY INTO <location> command.

Why it fails: While Snowflake supports client-side encryption for external stages where the user provides their own keys, for internal stages, Snowflake manages the encryption keys automatically. Users do not need to provide or manage keys when unloading data to internal Snowflake locations, simplifying the administrative overhead.

Internal stages do not support encryption because they are already behind a firewall.

Why it fails: The concept of a firewall is insufficient for modern cloud security. Snowflake employs a 'defense-in-depth' strategy where data is encrypted both in transit and at rest, regardless of whether it is stored in a table or a stage. Encryption is a mandatory component of the Snowflake storage layer.

It allows the user to skip the creation of a FILE_FORMAT object.

Why it fails: The exhibit explicitly shows the use of a FILE_FORMAT named 'my_json_format'. Querying staged files still requires Snowflake to understand how to parse the bits into a readable structure, so a file format (either named or inline) remains a requirement for the query to execute successfully.

It automatically creates the target table based on the JSON structure.

Why it fails: Snowflake does not automatically create tables based on a SELECT statement from a stage. The user must still define the target table with the appropriate columns and data types before they can load the results of this query into a permanent storage structure.

It is the only way to load compressed JSON files into Snowflake.

Why it fails: Snowflake can load compressed files using a standard COPY INTO command without an explicit SELECT clause. While the exhibit shows a compressed file (.gz), the transformation syntax is an optional feature for data manipulation, not a requirement for handling compressed file formats.

Multi-Factor Authentication (MFA) for the service account.

Why it fails: While MFA provides excellent security for human users, it is generally unsuitable for automated third-party applications because it requires a manual second step (like a phone notification). For programmatic access, non-interactive methods like key-pair or OAuth are required to ensure uninterrupted service connectivity.

Configuring a Network Policy to whitelist the application's IP address.

Why it fails: A network policy is a secondary security layer that restricts where a connection can originate from. It does not replace the need for authentication. Even with a whitelisted IP, the application would still need to provide a valid credential (password, key, or token) to gain access.

Using the 'ACCOUNTADMIN' role for all third-party connections.

Why it fails: Using the ACCOUNTADMIN role for application connectivity violates the principle of least privilege. It grants excessive permissions that could lead to accidental or malicious account-wide changes. Applications should always use a custom role with only the minimum permissions necessary for their specific tasks.

External tables store data in Snowflake's internal micro-partition format.

Why it fails: External tables do not store the data within Snowflake; they store only metadata and file pointers. The actual data remains in the external cloud storage (like S3) in its original format. This means they do not benefit from Snowflake's proprietary storage optimizations like internal tables do.

The COPY INTO command requires the target table to be empty before the load starts.

Why it fails: The COPY command is designed for incremental loading. It can append data to existing tables and uses load metadata to ensure that the same file is not loaded twice. There is no requirement for the table to be empty, making it suitable for continuous or batch updates.

External tables cannot be queried using standard SQL SELECT statements.

Why it fails: External tables are designed to be queried just like regular tables using standard SQL. This transparency is one of their primary features, allowing users to join external data with internal Snowflake tables seamlessly, provided the user has the necessary permissions to access the underlying stage.

Loading a single, very large file (e.g., 100GB) to reduce network overhead.

Why it fails: Loading a single massive file is a 'bottleneck' pattern. Since a single file can only be processed by a single thread in most cases, this approach wastes the parallel processing power of the Snowflake warehouse and results in much slower overall loading times compared to multiple smaller files.

Always disabling compression on source files before uploading to a stage.

Why it fails: Disabling compression increases the amount of data that must be transmitted over the network, which usually slows down the process. Snowflake is highly optimized to handle compressed files (like GZIP) and can often decompress them faster than it can wait for uncompressed data to arrive.

Using the INSERT command instead of COPY for files over 1GB.

Why it fails: The INSERT command is never recommended for bulk loading, regardless of the file size. It is much slower and less efficient than the COPY command because it is not optimized for bulk data movement and does not provide the same level of error handling and parallelism.

Define a standard table with explicit VARIANT columns, load the raw JSON files via COPY INTO, and then execute a daily merge stream to handle array expansion.

Why it fails: This approach introduces unnecessary complexity and multi-step data pipelines by staging raw variant data first. It fails to unpack arrays directly during the initial ingestion phase, requiring extra storage and compute cycles for downstream normalization.

Configure an external table over the S3 stage with an automatically generated virtual column that applies the FLATTEN transformation on read.

Why it fails: External tables allow querying data in cloud storage without loading it, but they do not support applying the FLATTEN function to materialize nested arrays into physical relational tables during a data loading operation.

Create a Snowpipe instance with an integrated file format that automatically applies lateral flattening parameters to JSON arrays prior to staging.

Why it fails: Snowpipe file formats support parsing basic JSON structures and stripping outer arrays, but they lack configuration parameters to perform dynamic lateral flattening operations on deeply nested structural arrays during auto-ingest.

Internal stages require a Storage Integration object to be created before they can be accessed by the COPY command.

Why it fails: Storage Integrations are only required for external stages that point to cloud storage like AWS, Azure, or GCP. Internal stages are natively managed by Snowflake and use the platform's internal security model, meaning they do not need any external integration objects to function or to be accessed.

Files in an internal stage are automatically purged after 24 hours regardless of whether they have been loaded.

Why it fails: Snowflake does not automatically purge files from internal stages based on a timer. Files remain in the stage until they are manually removed by a user or until the PURGE = TRUE option is used during a successful COPY INTO command, which clears files immediately after they are ingested.

Internal stages only support the loading of uncompressed CSV files to ensure maximum compatibility across regions.

Why it fails: Internal stages support a wide variety of file formats, including compressed CSV, JSON, Parquet, and XML. There is no limitation restricting them to uncompressed files. In fact, Snowflake recommends uploading compressed files to internal stages to reduce network transfer time and minimize the storage costs associated with staging data.

This endpoint is used to submit a list of new files from the external stage to the Snowpipe queue.

Why it fails: The endpoint used to submit files for ingestion is actually the insertFiles endpoint. The insertReport endpoint is used after submission to check the results of those files, making it a diagnostic and reporting tool rather than an ingestion trigger within the Snowpipe REST API architecture.

The endpoint is used to update the metadata of an existing pipe to point to a different target table.

Why it fails: Updating a pipe's metadata or changing its target table requires a DDL command like ALTER PIPE or recreating the pipe object entirely. The REST API endpoints are designed for operational tasks like triggering loads and monitoring status, not for administrative changes to the structure of Snowflake objects.

This function provides a way to purge files from the internal stage once they have been loaded.

Why it fails: Purging files from a stage is handled by the PURGE option in the COPY statement defined within the pipe or by manual removal. The Snowpipe REST API does not include an endpoint specifically for deleting files from storage; it focuses entirely on the ingestion workflow and reporting status.

The number of columns in the source table directly dictates the file count.

Why it fails: Snowflake unloads data in a row-based format (like CSV or JSON) even though it stores it internally as columns. The number of columns affects the size of each row and the overall file size, but it does not cause Snowflake to create one file per column during the unload process.

The presence of a CLUSTER BY clause on the target table forces exactly one file per micro-partition.

Why it fails: Clustering affects how data is organized within Snowflake's internal storage, but it does not dictate the file structure of an unload operation. The COPY INTO <location> command uses the warehouse's current state and parallel threads to write data, regardless of how the source table is clustered.

The REGION of the S3 bucket determines the file count due to 5GB chunk limits.

Why it fails: While cloud providers have individual file size limits, Snowflake's unloading logic is not automatically determined by the region. Snowflake manages the multi-part upload process to S3, and the file count is primarily driven by internal parallelism and the MAX_FILE_SIZE setting rather than regional S3 protocols.

Set the session parameter ON_ERROR to SKIP_FILE and inspect the query profile operator statistics.

Why it fails: Changing ON_ERROR to SKIP_FILE alters load behavior so entire files are skipped on any error, which is the opposite of what is wanted and would discard otherwise-valid rows. The query profile shows operator-level statistics such as bytes scanned and rows processed, but not the text of rejected rows or their specific parse errors. Neither mechanism yields the requested row-level detail.

Query the LOAD_HISTORY Information Schema view filtered by the COPY command's query ID.

Why it fails: The Information Schema LOAD_HISTORY view aggregates load activity per table and time window, reporting file names, row counts, and status. It does not expose individual rejected rows or their column-level parse errors, so it cannot give the upstream team the per-row reasons they need. It is useful for auditing volume and success rates, not for diagnosing which specific rows failed and why.

Re-run the COPY INTO with VALIDATION_MODE = RETURN_ERRORS to capture the rejected rows.

Why it fails: VALIDATION_MODE = RETURN_ERRORS validates files without loading them and returns errors, but it must be used in a separate validation pass rather than to inspect an already-executed load. Re-running it after the fact does not produce a persisted per-row artifact tied to the completed load, and using it on the same statement would change the load semantics entirely. It is a pre-load validation tool, not a post-load diagnostic.

External stage

Why it fails: An external stage references a cloud storage location such as Amazon S3, Google Cloud Storage, or Microsoft Azure Blob Storage. It cannot directly access files on a local workstation. The user would need to upload the file to cloud storage first, which is unnecessary for a local file. Therefore, an external stage is not the appropriate choice here.

User stage

Why it fails: A user stage is an internal stage automatically created for each user. It can hold files uploaded from a local system, but it is intended for personal, temporary files and is not typically used for loading data into tables. While it is possible, it is not the recommended or standard approach for loading local files into a Snowflake table, making it incorrect for this scenario.

Table stage

Why it fails: A table stage is an internal stage implicitly associated with a specific table. While it can hold files uploaded from a local system, it is tied to that table and cannot be referenced independently. The question does not specify that the file must be loaded into a particular table's stage, and using a table stage is less flexible. Thus, it is not the best choice for a general local file load.