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

Question 1

Which TWO statements accurately describe the behavior and management of Snowflake internal stages?

Accepted Answer

User stages are automatically allocated to each user and cannot be dropped or altered by other users.. Internal stages are managed directly within Snowflake and do not require external cloud storage provider configuration like AWS S3 or Azure Blob. They are critical for local data loading via SnowSQL and offer different levels of scope, ranging from user-specific areas to table-specific storage, ensuring secure and localized data handling.

Answer

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

Answer

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

Answer

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

Question 2

When using the Snowpipe REST API to trigger data loads, what is the primary purpose of the 'insertReport' endpoint?

Accepted Answer

It retrieves a summary of the most recent load activities for a specific pipe object.. The insertReport endpoint is a critical monitoring tool for developers using the Snowpipe REST API to ensure data integrity. It provides detailed status information about files submitted for ingestion, allowing the application to confirm whether files were successfully processed, are still pending, or encountered specific errors during the load.

Answer

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

Answer

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

Answer

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

Question 3

A company needs to unload a large table to an external S3 bucket. Which TWO factors will influence the number of files generated by the COPY INTO <location> command?

Accepted Answer

The size of the virtual warehouse used for the unload operation determines parallel threads.. The number of files produced during an unload operation depends on the parallelism of the warehouse and specific command options. Snowflake attempts to maximize performance by using multiple threads, and understanding these controls is vital for downstream systems that may have limitations on file sizes or total counts.

Answer

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

Answer

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

Answer

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

Question 4

A data engineer runs COPY INTO my_table FROM @my_stage/path/ ON_ERROR = CONTINUE. The command returns a status of PARTIALLY_LOADED, and the engineer needs a detailed, per-row record of why specific rows were rejected so it can be shared with the upstream application team. Which action will produce that row-level detail?

Accepted Answer

Query the COPY command's associated query ID from the COPY_HISTORY table function and call its result_scan or use the load's rejected-record output.. When COPY INTO runs with ON_ERROR = CONTINUE, Snowflake does not fail the whole statement; instead it loads valid rows and records the rejected ones. To retrieve those rejected rows and their error text, the engineer must use the load's persisted metadata keyed by the COPY statement's query ID, with COPY_HISTORY supplying the file-level context. Aggregated views such as LOAD_HISTORY lack row-level detail, and changing ON_ERROR or using VALIDATION_MODE changes the load's behavior rather than diagnosing the completed one.

Answer

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

Answer

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

Answer

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

Question 5

A user needs to load data from a local file into a Snowflake table using the SnowSQL CLI. The file is located on their workstation. Which type of stage should they use to upload the file before executing COPY INTO?

Accepted Answer

Internal stage. To load a local file into Snowflake, the file must first be uploaded to an internal stage using the PUT command. An internal stage is a storage location within Snowflake that supports direct uploads from a local file system. External stages reference cloud storage and cannot access local files directly. Table and user stages are internal but are less suitable for general local file loading.

Answer

External stage

Answer

User stage

Answer

Table stage

Question 6

A data architect is designing a data loading pipeline using Snowpipe to ingest files from an external stage. The pipeline must handle late-arriving data and ensure that files are loaded exactly once, even if the same file is staged multiple times with different names but identical content. Which Snowpipe feature should be used to prevent duplicate data ingestion?

Accepted Answer

Configure the pipe with a metadata column that includes the file content hash and use it for deduplication.. To prevent duplicate ingestion when file names differ but content is identical, Snowpipe can leverage metadata columns that expose a file content hash. By incorporating this hash into the pipe's COPY transformation and checking against already-loaded hashes, the pipeline can skip files with duplicate content. This approach provides content-based deduplication, ensuring exactly-once loading. Other options either provide no deduplication or only automate triggering without content awareness.

Answer

Enable the COPY_HISTORY option in the pipe definition.

Answer

Use the FILE_FORMAT option with SKIP_HEADER = 1.

Answer

Set the pipe to use the AUTO_INGEST = TRUE parameter.

Question 7

A data engineer needs to unload a large Snowflake table to an external stage on Amazon S3. To maximize parallelism and produce multiple output files, they want the unloaded data to be partitioned into separate files based on the values in a DATE column named LOAD_DATE. Which COPY INTO <location> clause should they use?

Accepted Answer

PARTITION BY LOAD_DATE. Unloading a table to an external stage can be parallelized by writing separate files per distinct value of an expression. The PARTITION BY clause in COPY INTO <location> performs exactly this, and the partition column must be excluded from the SELECT list so it does not appear in the data. This yields multiple output files, improving parallelism and enabling partition pruning for downstream readers.

Answer

SPLIT_BY LOAD_DATE

Answer

GROUP BY LOAD_DATE

Answer

CLUSTER BY LOAD_DATE

Question 8

A data engineer is loading a pipe-delimited CSV file from an external Azure stage into a Snowflake table. The file contains a header row that must be skipped, and the engineer wants to validate the load without actually inserting any rows. Which COPY INTO option should be used to perform a dry run that parses the file and reports errors but does not load data?

Accepted Answer

VALIDATION_MODE = RETURN_10_ROWS. VALIDATION_MODE = RETURN_10_ROWS is a COPY INTO option that parses and validates the file without loading data, returning up to 10 rows that would be loaded. It allows the engineer to inspect the data and catch errors before committing to a load. Other validation modes like RETURN_ERRORS only return errors, and ON_ERROR controls error handling during an actual load.

Answer

VALIDATION_MODE = RETURN_ALL_ERRORS

Answer

VALIDATION_MODE = RETURN_ERRORS

Answer

ON_ERROR = CONTINUE

Question 9

A data architect needs to unload a large table to an external stage in Parquet format. They want to ensure that the unloaded files are compressed to reduce storage costs and improve downstream performance. Which COPY INTO <location> option should they use?

Accepted Answer

FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY). For Parquet files, Snowflake's COPY INTO supports only SNAPPY compression. Specifying COMPRESSION = SNAPPY within the FILE_FORMAT options ensures that the unloaded Parquet files are compressed with Snappy, which reduces storage size and can improve query performance. Other compression algorithms like GZIP or ZSTD are not supported for Parquet in Snowflake, and NONE would leave files uncompressed.

Answer

FILE_FORMAT = (TYPE = PARQUET COMPRESSION = ZSTD)

Answer

FILE_FORMAT = (TYPE = PARQUET COMPRESSION = NONE)

Answer

FILE_FORMAT = (TYPE = PARQUET COMPRESSION = GZIP)

Question 10

A security team requires that all data loaded into a Snowflake table from an external S3 stage be encrypted at rest using a customer-managed key, even though the S3 bucket itself is encrypted with SSE-S3. The team also wants Snowflake to reject any file that is not encrypted with the expected key. What should the data engineer configure on the external stage?

Accepted Answer

Set ENCRYPTION = (TYPE = 'AWS_SSE_KMS' MASTER_KEY = '<kms_key_arn>') on the external stage.. To require a specific customer-managed key for files on an external S3 stage, the stage must declare AWS_SSE_KMS with the MASTER_KEY ARN. Snowflake then decrypts with that key and fails on objects encrypted differently, satisfying both the encryption-at-rest and rejection requirements. A storage integration only handles authorization and cannot enforce the key.

Answer

Set the stage parameter ENCRYPTION = (TYPE = 'AWS_SSE_S3') to match the bucket default.

Answer

Create a storage integration and reference it in the stage, relying on the integration to enforce KMS encryption.

Answer

Set the table parameter DATA_RETENTION_TIME_IN_DAYS and rely on Snowflake-managed encryption for the loaded data.

Question 11

A data engineer needs to unload a large fact table to an external stage on Amazon S3. The target bucket is in a different AWS region from the Snowflake account, and the company requires that data be encrypted with a customer-managed key stored in AWS KMS. The engineer wants to minimize egress and ensure the unloaded files are encrypted with the KMS key. Which approach should the engineer use?

Accepted Answer

Use COPY INTO with a storage integration that references the KMS key, and set the ENCRYPTION=(TYPE='AWS_SSE_KMS', KMS_KEY_ID='...') parameter.. Unloading to an external stage with a customer-managed KMS key requires specifying the ENCRYPTION parameter with TYPE='AWS_SSE_KMS' and the KMS_KEY_ID. This ensures Snowflake encrypts the data using the specified KMS key. A storage integration should be used for authentication, not embedded credentials. Other options either use the wrong encryption type or introduce unnecessary manual steps.

Answer

Create an external stage that references the S3 bucket and include AWS credentials in the stage definition, then rely on S3 default encryption (SSE-S3).

Answer

Use COPY INTO with the INTERNAL stage and then use AWS CLI to copy files to S3, applying KMS encryption during the copy.

Answer

Set the ENCRYPTION=(TYPE='AWS_SSE_S3') parameter in the COPY INTO command and configure the S3 bucket to use a KMS key by default.

Question 12

A Snowflake user needs to unload data from a table to a named internal stage. The user wants to ensure that the unloaded files are encrypted with a client-side encryption key that they manage, rather than Snowflake-managed encryption. Which COPY INTO <location> option must be specified?

Accepted Answer

ENCRYPTION = (TYPE = 'AWS_CSE', MASTER_KEY = 'key'). To unload data to an internal stage with client-side encryption using a user-managed key, the COPY INTO <location> command must include ENCRYPTION = (TYPE = 'AWS_CSE', MASTER_KEY = 'key'). This ensures that the data is encrypted with the user's key before being stored on the internal stage. Other encryption types are for external stages or do not allow user-managed keys.

Answer

ENCRYPTION = (TYPE = 'NONE')

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_S3')

Answer

ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')

Question 13

A data engineer is loading data from a local file system into a Snowflake table using the PUT command followed by COPY INTO. The engineer wants to minimize the number of files staged to improve load performance. Which PUT command option should be used to automatically compress files during staging?

Accepted Answer

AUTO_COMPRESS = TRUE. The PUT command's AUTO_COMPRESS = TRUE option automatically compresses files during staging using gzip, reducing their size and potentially improving load performance. Other options listed are not valid PUT command parameters for compression or serve different purposes, such as encryption. Therefore, AUTO_COMPRESS is the correct choice to minimize staged file size.

Answer

COMPRESSION = GZIP

Answer

SOURCE_COMPRESSION = GZIP

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_S3')

Question 14

A Snowflake administrator wants to grant a service account the minimum privilege needed to stage files into an internal named stage called sales_stage and then load them with COPY INTO. Which single privilege on the stage object is required for writing files to that stage?

Accepted Answer

WRITE on the stage. Uploading files into an internal named stage requires the WRITE privilege on that stage object, which authorizes PUT operations. USAGE covers reading and listing but not writing, while OWNERSHIP grants excessive control and CREATE STAGE concerns object creation rather than file upload. Pairing WRITE with USAGE gives the service account exactly the access needed to stage and then load data.

Answer

USAGE on the stage

Answer

OWNERSHIP on the stage

Answer

CREATE STAGE on the schema

Question 15

A data engineer is loading data from an external S3 stage into a Snowflake table using COPY INTO. The S3 bucket contains files in a nested folder structure, and the engineer wants to load only files that match a specific pattern and are located in a specific subfolder. The engineer also wants to ensure that files already loaded are not loaded again. Which combination of COPY INTO parameters should be used to achieve this?

Accepted Answer

PATTERN = '.*subfolder/.*\.csv' and use the default load metadata behavior. The PATTERN parameter allows filtering files by a regular expression, and the default load metadata behavior automatically prevents reloading files that have already been loaded. Using PATTERN with the default behavior meets both the filtering and duplicate-avoidance requirements. FORCE = TRUE would reload files, and FILES is for explicit file lists, not patterns.

Answer

PATTERN = '.*subfolder/.*\.csv' and FILES = ('file1.csv')

Answer

PATTERN = '.*subfolder/.*\.csv' and FORCE = TRUE

Answer

FILES = ('subfolder/file1.csv', 'subfolder/file2.csv') and FORCE = FALSE