COF-C03 Data Loading, Unloading, and Connectivity Practice Test 4 — 15 Questions

Question 1

Refer to the exhibit. When the COPY INTO command is executed, which field delimiter will Snowflake use to parse the files located in the 'data/' folder of the stage?

Accepted Answer

The comma character (,) because the FILE_FORMAT in the COPY command overrides the stage default.. In Snowflake, if a FILE_FORMAT is specified in both the stage definition and the COPY INTO statement, the options provided in the COPY INTO statement take precedence. This allows developers to define a general default format for a stage while still having the flexibility to override specific parameters for individual load jobs without altering the permanent stage object itself.

Answer

The pipe character (|) because it was defined first in the CREATE STAGE command.

Answer

Snowflake will return an error because the delimiters in the stage and the COPY command conflict.

Answer

Snowflake will attempt to auto-detect the delimiter, ignoring both specified values.

Question 2

A data engineer needs to verify the structure and content of several CSV files staged in an internal Snowflake stage without actually loading the data into the production table or incurring significant compute costs. Which parameter should be used with the COPY INTO command to achieve this specific goal?

Accepted Answer

VALIDATION_MODE = RETURN_ERRORS. The VALIDATION_MODE parameter is specifically designed to parse staged files and return errors or data samples without performing an actual load. This prevents unnecessary data ingestion while allowing engineers to identify formatting issues or schema mismatches early in the development lifecycle. It is a cost-effective method to ensure data quality before executing a full production data pipeline.

Answer

ON_ERROR = ABORT_STATEMENT

Answer

PURGE = FALSE

Answer

FORCE = TRUE

Question 3

Which Snowflake command is used to upload data files from a local file system to an internal stage?

Accepted Answer

PUT. The PUT command is the primary method for moving local files into the Snowflake cloud environment's internal stages. This command is executed via the SnowSQL CLI or other drivers that support local file access. It handles the secure upload and optional encryption of data before it reaches the Snowflake managed storage.

Answer

COPY INTO

Answer

GET

Answer

INSERT

Question 4

Which property of the FILE_FORMAT object should be adjusted if a CSV file uses a semicolon (;) instead of a comma to separate values?

Accepted Answer

FIELD_DELIMITER. File formats in Snowflake are highly granular, allowing users to define the exact structure of their source data. Correctly identifying the delimiter is the most basic requirement for successful parsing. Snowflake provides specific parameters for field and record delimiters to accommodate a wide variety of data export formats.

Answer

RECORD_DELIMITER

Answer

FIELD_OPTIONALLY_ENCLOSED_BY

Answer

ESCAPE_CHARACTER

Question 5

What is the purpose of the PURGE = TRUE option in a COPY INTO command?

Accepted Answer

It removes data files from the stage after they are successfully loaded.. The PURGE parameter provides an automated way to manage the lifecycle of files in a stage. In many workflows, once a file has been successfully loaded into Snowflake, it is no longer needed in the staging area. Automating the deletion helps maintain a clean environment and can reduce storage costs in internal stages.

Answer

It deletes all records from the target table before starting the load.

Answer

It clears the Snowflake metadata cache to allow for a full reload of the data.

Answer

It removes only the files that failed to load due to formatting errors.

Question 6

Which Snowflake feature allows for secure, direct communication between a customer's virtual private cloud (VPC) and the Snowflake service without using the public internet?

Accepted Answer

Snowflake Private Link. For organizations with strict security and compliance requirements, avoiding the public internet for data transit is essential. Snowflake supports private connectivity options on major cloud providers to ensure that data remains within the cloud provider's network backbone, significantly reducing the attack surface for potential data interception.

Answer

Snowflake Data Exchange

Answer

SnowSQL Proxy Configuration

Answer

Network Policy Whitelisting

Question 7

A data engineer is designing a pipeline for a high-frequency stream of small JSON files arriving every minute in an S3 bucket. Why would Snowpipe be preferred over a scheduled COPY INTO command running on a dedicated virtual warehouse?

Accepted Answer

Virtual warehouses are billed per second with a one-minute minimum, making them expensive for frequent small loads.. Snowpipe provides a serverless compute model that automatically scales based on the volume of data being ingested, which is ideal for small, frequent files. Using a dedicated virtual warehouse for a scheduled COPY command often leads to underutilization or excessive costs because the warehouse remains active for the minimum billing period even if the load finishes in seconds.

Answer

Snowpipe requires manual intervention to trigger loads using the REST API for every single file arriving in the bucket.

Answer

The COPY INTO command cannot process JSON files directly and requires an intermediate stage to convert data to CSV.

Answer

Scheduled COPY commands are limited to executing once every hour, which would create significant latency for real-time pipelines.

Question 8

Which Snowflake feature allows a user to download data from a Snowflake table into a local folder on their computer using the SnowSQL command-line interface?

Accepted Answer

The GET command fetches files from an internal stage and saves them to the local directory.. The GET command is specifically designed to transfer files from an internal Snowflake stage to a local directory on a client machine. This is the inverse of the PUT command and is essential for retrieving data that has been unloaded from tables into internal stages for local analysis or archival purposes.

Answer

The PUT command is used to move files from the local file system into an internal stage.

Answer

The COPY INTO <location> command moves data from a table directly to a local hard drive.

Answer

The DOWNLOAD function is a built-in SQL utility that can be called from any worksheet.

Question 9

When using the Snowflake Connector for Python, which method is most efficient for uploading and loading large local CSV files into a Snowflake table?

Accepted Answer

Using the 'write_pandas' function which automates the PUT and COPY INTO commands internally.. The Python Connector offers specialized methods to optimize data movement. For large local files, using the write_pandas method or executing a PUT followed by a COPY command is much more efficient than executing individual INSERT statements, as it leverages Snowflake's bulk loading capabilities and reduces network overhead.

Answer

Iterating through the CSV in Python and executing an INSERT statement for every row found.

Answer

Converting the CSV into a single large SQL string and executing it as one massive INSERT.

Answer

Calling the 'snowflake.load_file' method which bypasses the need for any virtual warehouse.

Question 10

A user wants to check for potential errors in a set of staged files without actually loading the data or consuming significant warehouse credits. Which approach should they use?

Accepted Answer

Execute the COPY command with the VALIDATION_MODE = 'RETURN_ERRORS' parameter specified.. The VALIDATION_MODE parameter in the COPY command is a powerful tool for data quality assurance. It allows Snowflake to parse files and identify errors without writing data to the table, which helps prevent failed loads and saves time by catching formatting issues before the actual ingestion occurs.

Answer

Run the COPY command with the ON_ERROR = 'ABORT_STATEMENT' and PURGE = 'FALSE' options.

Answer

Query the files directly using the SELECT * FROM @stage syntax with a LIMIT 100 clause.

Answer

Use the ANALYZE_STAGE function to generate a report on the health and formatting of the staged files.

Question 11

A data engineer is loading a batch of semi-structured JSON files from an external stage into a VARIANT column. They want the COPY INTO command to skip any file that contains malformed JSON without failing the entire load. Which COPY INTO option should they configure?

Accepted Answer

ON_ERROR = 'SKIP_FILE'. The ON_ERROR = 'SKIP_FILE' option tells Snowflake to skip a file entirely when any error occurs during loading, which includes malformed JSON. This allows the COPY INTO operation to continue processing other valid files without failing the entire statement. The other options either abort the load, skip only rows, or are not valid Snowflake syntax.

Answer

ON_ERROR = 'REPLACE'

Answer

ON_ERROR = 'CONTINUE'

Answer

ON_ERROR = 'ABORT_STATEMENT'

Question 12

A data engineer is configuring a Snowflake external stage that points to an Amazon S3 bucket. The bucket is in the same region as the Snowflake account. The engineer wants to avoid embedding long-lived AWS credentials in the stage definition and instead use a secure, temporary credential mechanism. Which authentication method should be used for the external stage?

Accepted Answer

Use a storage integration with an IAM role and external ID.. The secure and recommended way to grant Snowflake access to an external S3 bucket without embedding long-lived credentials is to create a storage integration. This integration establishes a trust relationship between Snowflake and AWS, allowing Snowflake to assume an IAM role and obtain temporary credentials. The other options either embed static keys or confuse AWS IAM features with Snowflake capabilities, failing to meet the security requirement.

Answer

Create a Snowflake user with an AWS IAM policy attached directly.

Answer

Enable AWS IAM database authentication on the Snowflake account.

Answer

Configure the stage with AWS_KEY_ID and AWS_SECRET_KEY parameters.

Question 13

A data engineer is using the Snowpipe REST API to ingest data from an external stage. They need to ensure that the pipe does not reprocess files that have already been loaded. Which mechanism does Snowpipe use to track which files have been processed?

Accepted Answer

A load history table maintained by Snowpipe. Snowpipe uses an internal load history that records the filename and checksum of each ingested file. When a new file notification arrives, Snowpipe consults this history to determine if the file has already been processed. This prevents duplicate ingestion even if the same file is staged multiple times. The other options do not provide this tracking capability.

Answer

A unique file naming convention enforced by the user

Answer

A metadata table in the INFORMATION_SCHEMA

Answer

A hash of the file stored in the stage metadata

Question 14

A data engineer is loading a 4 GB CSV file from an external stage into a Snowflake table using COPY INTO. The file is compressed with gzip and has a header row. The engineer notices the load is taking longer than expected. Which action is MOST likely to improve performance?

Accepted Answer

Split the file into multiple smaller files and load them in parallel.. Splitting large files into multiple smaller files enables parallel loading, which is a key performance optimization for COPY INTO. Snowflake can distribute the load across multiple threads when multiple files are present, reducing overall load time.

Answer

Enable the PURGE option to remove the file after loading.

Answer

Increase the warehouse size to a larger size.

Answer

Use a larger file format option to skip the header.

Question 15

A data engineer needs to unload data from a Snowflake table to an external stage that references an Amazon S3 bucket. The engineer wants to ensure that the unloaded files are encrypted using a customer-managed key in AWS KMS. Which COPY INTO <location> parameter should be used to specify the KMS key?

Accepted Answer

ENCRYPTION = (TYPE = 'AWS_SSE_KMS' MASTER_KEY = 'arn:aws:kms:...'). To unload data to an external S3 stage with a customer-managed KMS key, the ENCRYPTION parameter must include TYPE = 'AWS_SSE_KMS' and MASTER_KEY set to the KMS key ARN. This ensures server-side encryption with the specified key. Other options either use S3-managed keys, Snowflake-managed encryption, or omit the necessary key parameter, failing to meet the requirement.

Answer

ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_KMS')

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_S3')