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

Question 1

A data engineer is configuring a Snowpipe to automatically ingest files as they arrive in an external stage. They need to set up event notifications from the cloud provider to Snowflake. Which two components are required to enable this automated ingestion? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

A storage integration object in Snowflake. To enable automated Snowpipe ingestion from an external stage, two key Snowflake objects are required: a storage integration to securely access the external stage, and a notification integration to receive event notifications from the cloud provider. The storage integration provides the necessary permissions, while the notification integration allows Snowpipe to be triggered by events. Other components like file formats or tasks are not required for the event notification setup.

Answer

A named file format object

Answer

A task to periodically check for new files

Answer

An external function to process the notifications

Question 2

A data engineer has set up a Snowpipe that continuously loads JSON files from an external stage into a table. The stage references an Amazon S3 bucket with a notification integration. After several days, the engineer notices that new files are not being ingested, even though they exist in the S3 bucket. The Snowpipe is in a RUNNING state and the notification integration is active. What is the MOST likely cause of the missing data?

Accepted Answer

The S3 bucket notification is not configured to send events for the specific prefix or suffix of the new files.. Snowpipe ingests files based on event notifications from the external stage's cloud storage. When new files are not loaded, the most likely cause is that the event notification is not configured to capture those files, such as missing prefix or suffix filters. The pipe's running state and notification integration being active do not guarantee that all files trigger events. Checking the S3 bucket's event configuration is essential.

Answer

The files are compressed with gzip, which Snowpipe cannot automatically decompress.

Answer

The virtual warehouse used by Snowpipe is suspended, preventing the pipe from executing.

Answer

The Snowpipe's metadata has expired because the pipe was paused for more than 14 days.

Question 3

A data engineer has a CSV file on their local machine and wants to load it into a Snowflake table. They do not have access to an external cloud storage bucket and want the simplest path that does not require creating a named internal stage. Which command should they use?

Accepted Answer

PUT file:///tmp/data.csv @%my_table; then COPY INTO my_table FROM @%my_table. Every table has an implicit table stage accessible as @%table_name, which requires no manual creation. Uploading the local file with PUT to the table stage and then running COPY INTO from that stage is the simplest way to load a single local CSV without provisioning a named internal stage. This satisfies the no-named-stage constraint directly.

Answer

COPY INTO my_table FROM 'file:///tmp/data.csv'

Answer

PUT file:///tmp/data.csv @my_table

Answer

COPY INTO my_table FROM @my_named_stage

Question 4

A data engineer needs to load a 4.2 GB uncompressed CSV file from an internal stage into a Snowflake table. The file cannot be split because the CSV has embedded newlines within quoted fields. The engineer wants to maximize load performance. What should the engineer do?

Accepted Answer

Split the file into multiple smaller files (e.g., 100-250 MB compressed) and stage them together, then run a single COPY INTO command referencing the stage path.. Snowflake achieves load parallelism by processing multiple files concurrently, not by splitting a single file. When a file cannot be split due to embedded newlines, the engineer must manually divide it into multiple smaller files and stage them together. A single COPY INTO command can then load all files in parallel, significantly improving performance over loading one large file.

Answer

Load the file using the Snowpipe REST API with the insertFiles endpoint, which automatically splits large files into chunks for parallel ingestion.

Answer

Use a larger virtual warehouse and load the single file; Snowflake will automatically parallelize the load across all compute resources in the warehouse.

Answer

Compress the file with gzip and load it as a single file; Snowflake will automatically split the compressed file across all nodes in the warehouse.

Question 5

A data analyst needs to unload the results of a query from a Snowflake table to a local machine. The analyst wants to use the Snowflake web interface (Snowsight) to download the data as a CSV file. Which of the following is the correct approach?

Accepted Answer

Run the query in a worksheet, then use the 'Download results' button to save the output as a CSV file.. Snowsight provides a built-in feature to download query results as a CSV file. After executing a query, the results pane includes a 'Download results' button, which exports the displayed data to a CSV file on the local machine. This is the simplest and most direct method for ad-hoc downloads, especially for smaller datasets, and it does not require staging or command-line tools.

Answer

Create a named internal stage, unload the data using COPY INTO @stage, then use GET to download the file to the local machine.

Answer

Execute a COPY INTO command targeting a local file path, then download the file from the user's home directory.

Answer

Use the Snowflake REST API to execute the query and retrieve the results as a CSV stream.

Question 6

A data engineer is loading data from a local file system into a Snowflake table using the PUT command to an internal stage, followed by COPY INTO. The engineer notices that some rows are rejected due to data type mismatches. The engineer wants to capture the rejected records and continue loading valid rows. Which COPY INTO option should be used to achieve this?

Accepted Answer

ON_ERROR = 'CONTINUE'. To load valid rows while capturing rejected records, ON_ERROR = 'CONTINUE' is the appropriate option. It allows the load to proceed, skipping erroneous rows, and the rejected records can be retrieved from the COPY_HISTORY or by using VALIDATION_MODE. Other options either skip the entire file, only validate, or abort the statement, none of which meet the requirement to continue loading valid rows.

Answer

ON_ERROR = 'SKIP_FILE'

Answer

ON_ERROR = 'ABORT_STATEMENT'

Answer

VALIDATION_MODE = 'RETURN_ERRORS'

Question 7

A data engineer loads JSON files from an external Azure stage into a table with a single VARIANT column. The JSON documents are newline-delimited, and each line is a separate object. Which file format type and option should be specified to correctly parse one JSON object per line?

Accepted Answer

FILE_FORMAT = (TYPE = 'JSON') with the default settings. Snowflake's JSON file format parses newline-delimited JSON by default, treating each line as a distinct document and loading it as a row. No additional option is required for this common layout. STRIP_OUTER_ARRAY is only relevant when the file is a single JSON array, and other JSON options control field handling rather than record splitting.

Answer

FILE_FORMAT = (TYPE = 'JSON') with STRIP_NULL_VALUES = TRUE

Answer

FILE_FORMAT = (TYPE = 'JSON') with STRIP_OUTER_ARRAY = TRUE

Answer

FILE_FORMAT = (TYPE = 'CSV') with FIELD_DELIMITER = '
'

Question 8

A user needs to load data from a CSV file stored in an external stage into a Snowflake table. The CSV file has a header row and uses a pipe (|) as the field delimiter. The user wants to ensure the header row is skipped and the pipe delimiter is recognized. Which FILE_FORMAT options should be specified in the COPY INTO command?

Accepted Answer

TYPE = 'CSV', FIELD_DELIMITER = '|', SKIP_HEADER = 1. For a CSV file with a pipe delimiter and a header row, the FILE_FORMAT should specify TYPE = 'CSV', FIELD_DELIMITER = '|', and SKIP_HEADER = 1. This ensures the header is ignored and fields are correctly separated. Using the wrong delimiter or not skipping the header would result in parsing errors or unwanted data.

Answer

TYPE = 'CSV', FIELD_DELIMITER = '|', SKIP_HEADER = 0

Answer

TYPE = 'CSV', RECORD_DELIMITER = '|', SKIP_HEADER = 1

Answer

TYPE = 'CSV', FIELD_DELIMITER = ',', SKIP_HEADER = 1

Question 9

A data analyst wants to query data stored in an external stage (Amazon S3) without loading it into a Snowflake table. The analyst creates an external table pointing to the stage. Which statement accurately describes how the data is accessed?

Accepted Answer

The external table reads data directly from the external stage at query time, and the data remains in the external storage location.. External tables provide a way to query data stored in external stages without loading it into Snowflake. They act as metadata pointers to files in the external stage, and queries read the data directly from those files. This allows querying data in place, which is useful for infrequent access or when data must remain in external storage.

Answer

The external table automatically loads all data into Snowflake's storage upon creation, so queries run against local micro-partitions.

Answer

The external table caches the data in the virtual warehouse's local SSD, and subsequent queries do not access the external stage again.

Answer

The external table requires a materialized view to be created on top of it before any queries can return results.

Question 10

A data engineer is configuring Snowpipe to automatically ingest files as they arrive in an external S3 stage. They must ensure the pipe loads only files matching a specific path prefix and that duplicate notifications for the same file do not cause duplicate rows. Which two configurations should they apply? (Choose two.)

Accepted Answer

Define the pipe with a COPY INTO statement that includes a PATTERN option matching the desired path prefix.. Filtering by path is achieved with the PATTERN option in the pipe's COPY statement, which restricts ingestion to matching files. Duplicate prevention comes from Snowpipe's load metadata, which records each loaded file by name and checksum and skips repeats even if notifications are delivered multiple times. Together these two settings satisfy both the prefix restriction and the deduplication requirement.

Answer

Rely on the S3 event notification configuration to prevent duplicate notifications from being sent.

Answer

Set the pipe parameter FORCE = TRUE so that every notification reloads the file.

Answer

Configure the pipe with AUTO_INGEST = TRUE and a notification channel, and set ON_ERROR = 'CONTINUE'.

Question 11

A data engineer is configuring a Snowflake storage integration to allow Snowflake to access an external S3 bucket. The engineer needs to ensure that the integration has the necessary permissions to read and write data. Which two actions must the engineer perform? (Choose two.)

Accepted Answer

Grant the IAM role permissions to access the S3 bucket, such as s3:GetObject, s3:PutObject, s3:ListBucket, and s3:GetBucketLocation.. To configure a storage integration for S3, you must create an IAM role in AWS that trusts Snowflake's AWS account and has the necessary S3 permissions. These two actions are essential for Snowflake to assume the role and access the bucket. Other options are either insecure or optional. The integration uses temporary credentials, so storing access keys is not needed.

Answer

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

Answer

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

Answer

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

Question 12

A data engineer is loading JSON data from an external stage into a Snowflake table using COPY INTO with a JSON file format. The JSON records contain nested arrays and objects. The engineer wants to load specific elements into separate columns. Which approach should the engineer use?

Accepted Answer

Load the entire JSON record into a single VARIANT column, then use a view with dot notation and array indexing to extract the desired elements.. Snowflake provides robust support for semi-structured data through the VARIANT data type. Loading JSON into a VARIANT column and then using dot notation and array indexing in a view or query allows flexible extraction of nested elements. This approach leverages Snowflake's built-in functions and avoids the need for complex transformations or external processing.

Answer

Use the COPY INTO command with a transformation that uses the SPLIT_TO_TABLE function to flatten the arrays into rows before loading.

Answer

Define the target table with columns matching the JSON keys and use the MATCH_BY_COLUMN_NAME option in the file format to automatically map nested elements to columns.

Answer

Convert the JSON to CSV using an external tool before loading, then use a standard CSV file format with column mapping.

Question 13

A data engineer is configuring a Snowpipe to automatically ingest files as they arrive in an external stage. They need to set up event notifications from the cloud provider to Snowflake. Which two components are required to enable this automated ingestion? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Set up a cloud messaging service (e.g., Google Cloud Pub/Sub) to send event notifications to Snowflake, and create a pipe with AUTO_INGEST = TRUE.. To minimize latency, Snowpipe should be configured with AUTO_INGEST = TRUE, which leverages cloud messaging services to receive event notifications when new files arrive. This event-driven approach triggers immediate ingestion, ensuring data is loaded as soon as it lands in the external stage.

Answer

Configure the external stage to use a storage integration and set the pipe's file format to automatically detect new files via directory listing.

Answer

Create a pipe with AUTO_INGEST = FALSE and schedule a task to call the pipe every minute using SYSTEM$PIPE_FORCE_REFRESH.

Answer

Use the Snowpipe REST API to call insertFiles every 30 seconds from an external scheduler to check for new files.

Question 14

A data engineer is loading data from a set of CSV files stored in an external stage into a Snowflake table. The files have a header row, and the engineer wants to skip the header during loading. The engineer also wants to ensure that any rows with missing values in a NOT NULL column are skipped and logged. Which FILE_FORMAT option should be used to skip the header row?

Accepted Answer

SKIP_HEADER = 1. To skip the header row in a CSV file during loading, the FILE_FORMAT option SKIP_HEADER should be set to the number of header rows to skip, typically 1. This ensures that the first row is ignored and not loaded into the table. Other options like SKIP_BLANK_LINES or FIELD_DELIMITER do not serve this purpose. PARSE_HEADER is not a valid Snowflake option.

Answer

PARSE_HEADER = TRUE

Answer

FIELD_DELIMITER = ','

Answer

SKIP_BLANK_LINES = TRUE

Question 15

A data engineer is using the COPY INTO command to unload data from a Snowflake table to an external stage (Amazon S3). The engineer wants to ensure the unloaded files are encrypted and can be decrypted by the target system. Which TWO statements are true regarding the encryption of unloaded files? (Choose two.)

Accepted Answer

Client-side encryption with a master key requires the target system to use the same master key to decrypt the files.. For unloading to an external stage, Snowflake supports both server-side encryption (e.g., SSE-S3, SSE-KMS) and client-side encryption with a master key. Server-side encryption relies on the cloud provider's encryption, while client-side encryption encrypts data before upload, requiring the target system to have the master key. Both methods ensure data is encrypted at rest and can be decrypted by authorized parties.

Answer

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

Answer

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

Answer

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