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

Question 1

A data engineer is using the Snowflake COPY INTO command to load data from an external stage into a table. The engineer needs to capture information about any errors that occur during the load, such as rejected rows and the reasons for rejection. Which TWO actions should the engineer take to ensure error details are available after the load? (Choose two.)

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

Accepted Answer

Query the COPY_HISTORY table function for the load operation.. To capture detailed error information during a COPY INTO load, the engineer can use VALIDATION_MODE = RETURN_ERRORS to validate and return all errors without loading data, or query the COPY_HISTORY table function after the load to retrieve error details and row counts. These two methods provide the necessary error insights. Other options either skip errors without detail, force reloads, or enforce column count matching, none of which provide comprehensive error reporting.

Answer

Use the FORCE = TRUE parameter to reload all files and capture errors.

Answer

Enable the ERROR_ON_COLUMN_COUNT_MISMATCH = TRUE parameter.

Answer

Set the ON_ERROR = CONTINUE parameter to skip errors and continue loading.

Question 2

A data engineer is configuring a Snowpipe to load data from an external stage that points to a Microsoft Azure Blob Storage container. The engineer needs to ensure that the pipe can access the container securely. Which TWO authentication methods are supported for an external stage referencing Azure Blob Storage in Snowflake? (Choose two.)

Accepted Answer

Azure Active Directory (AD) service principal with a storage integration. Snowflake supports two primary authentication methods for Azure Blob Storage external stages: SAS tokens and Microsoft Entra ID service principals via storage integrations. SAS tokens provide limited, temporary access, while service principals offer a more secure, credential-less approach. Other cloud-specific methods like AWS IAM roles or GCP service accounts are not applicable to Azure.

Answer

Storage account access key embedded in the stage URL

Answer

Google Cloud Platform (GCP) service account

Answer

AWS Identity and Access Management (IAM) role

Question 3

A data engineer is using the Snowflake COPY INTO command to load data from an external stage into a table. The engineer needs to capture information about any errors that occur during the load, such as rejected rows and the reasons for rejection. Which TWO actions should the engineer take to ensure error details are available after the load? (Choose two.)

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

Accepted Answer

ON_ERROR = 'CONTINUE'. ON_ERROR = 'CONTINUE' is the key option that allows a load to proceed despite row errors, and it can be paired with querying COPY_HISTORY or using the VALIDATE() function to retrieve error details. The other options either skip entire files or are not COPY INTO parameters. Thus, the two correct choices are ON_ERROR = 'CONTINUE' and the combination of ON_ERROR = 'CONTINUE' with COPY_HISTORY.

Answer

VALIDATION_MODE = 'RETURN_ERRORS'

Answer

ON_ERROR = 'CONTINUE' with the VALIDATE() function

Answer

ON_ERROR = 'SKIP_FILE'

Question 4

A user is unloading data from a Snowflake table to an internal stage using the COPY INTO command. The user wants to ensure that the unloaded files are encrypted with a customer-managed key rather than the default Snowflake-managed key. Which parameter should be used in the COPY INTO command?

Accepted Answer

ENCRYPTION = (TYPE = 'CUSTOMER_MANAGED_KEY', KEY_ID = 'key_id'). For internal stages, Snowflake supports customer-managed keys through the ENCRYPTION parameter with TYPE = 'CUSTOMER_MANAGED_KEY' and a specified KEY_ID. This allows the user to use their own key from a supported key management service, rather than the default Snowflake-managed key. The other options are either for external stages or do not support customer-managed keys.

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_KMS', KMS_KEY_ID = 'key_id')

Answer

ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE')

Answer

ENCRYPTION = (TYPE = 'AZURE_SSE')

Question 5

A data engineer is using the COPY INTO command to load data from an external stage into a Snowflake table. The engineer wants to capture information about any errors that occur during the load, including the file name, line number, and error message, without failing the entire load. Which TWO options should be used? (Choose two.)

Accepted Answer

VALIDATION_MODE = 'RETURN_ERRORS'. To capture error details without failing the load, the engineer can first use VALIDATION_MODE = 'RETURN_ERRORS' to validate the files and retrieve all errors, then use ON_ERROR = 'CONTINUE' during the actual load to skip erroneous rows and load the valid ones. This combination allows error capture and successful loading of valid data.

Answer

ON_ERROR = 'ABORT_STATEMENT'

Answer

VALIDATION_MODE = 'RETURN_ALL_ERRORS'

Answer

ON_ERROR = 'SKIP_FILE'

Question 6

A user needs to unload a large table from Snowflake to an external S3 stage as compressed Parquet files, partitioned by date, for downstream consumption. Which COPY INTO <location> syntax element achieves the partitioning of the unloaded output?

Accepted Answer

PARTITION BY = ('date_column'). PARTITION BY in COPY INTO <location> generates one subdirectory per distinct value of the specified expression, yielding a hive-style layout where each date becomes a folder. File format, compression, single-file, and max-file-size options affect encoding and file count but not the directory structure. Combining PARTITION BY with Parquet and compression produces the partitioned, compressed unload the scenario requests.

Answer

FILE_FORMAT = (TYPE = PARQUET) with COMPRESSION = SNAPPY

Answer

MAX_FILE_SIZE = 16777216

Answer

SINGLE = TRUE

Question 7

A Snowflake user is loading data from a Parquet file stored in an external stage. The Parquet file contains a column of type INT96, which is commonly used for timestamps. The user wants to ensure that this column is loaded as a TIMESTAMP_NTZ in Snowflake. Which file format option should be configured to achieve this?

Accepted Answer

No additional option is needed; INT96 is automatically loaded as TIMESTAMP_NTZ.. Snowflake's Parquet reader automatically maps INT96 columns to TIMESTAMP_NTZ by default. No additional file format options are required. Options like USE_LOGICAL_TYPE or TIMESTAMP_TYPE_MAPPING do not apply to INT96, and BINARY_AS_TEXT is for delimited files. Therefore, the user can simply load the data and the INT96 column will be stored as TIMESTAMP_NTZ.

Answer

Set TIMESTAMP_TYPE_MAPPING = 'TIMESTAMP_NTZ' in the file format.

Answer

Set USE_LOGICAL_TYPE = TRUE in the file format.

Answer

Set the file format option BINARY_AS_TEXT = FALSE.

Question 8

Which type of Snowflake stage is automatically created for every user and cannot be dropped or altered?

Accepted Answer

User Stage. Snowflake provides several types of internal stages to simplify file management. The User Stage is a unique, dedicated area for each user to store files before loading them into tables. It is automatically provisioned and managed by Snowflake, ensuring that users always have a private location for staging data without requiring administrative configuration or manual stage creation.

Answer

Named Internal Stage

Answer

Table Stage

Answer

External Stage

Question 9

A developer is running a COPY INTO command to load 1,000 CSV files. The requirement is that if even one row in any file fails due to a data type mismatch, the entire load operation must stop and no data should be committed to the table. Which ON_ERROR setting is required?

Accepted Answer

ON_ERROR = ABORT_STATEMENT. The ON_ERROR parameter determines how the COPY command handles malformed data or schema mismatches. The ABORT_STATEMENT setting is the strictest option available, ensuring transactional integrity by rolling back the entire load if a single error occurs. This is critical for datasets where partial loads could lead to data inconsistency or require complex manual cleanup.

Answer

ON_ERROR = CONTINUE

Answer

ON_ERROR = SKIP_FILE

Answer

ON_ERROR = SKIP_FILE_1%

Question 10

A Python developer wants to upload a Pandas DataFrame to a Snowflake table as efficiently as possible without manually writing files to a local stage. Which function from the Snowflake Connector for Python should be used?

Accepted Answer

write_pandas(). The Snowflake Connector for Python provides a high-level function called write_pandas specifically for this purpose. This function automates the process of converting the DataFrame to Parquet format, staging the files in a temporary internal stage, and executing the COPY INTO command. This abstraction simplifies the developer's workflow while maintaining high performance for large data transfers.

Answer

cursor.execute("INSERT INTO...")

Answer

snowflake.load_df()

Answer

pd.to_sql() with the default engine

Question 11

For optimal parallel loading performance using a Snowflake virtual warehouse, what is the generally recommended compressed file size range for data files in a stage?

Accepted Answer

10MB to 100MB. Snowflake's architecture is optimized for parallel processing, where each execution thread in a warehouse can process a separate file. To maximize this parallelism and avoid overhead, it is recommended to aim for file sizes between 10MB and 100MB when compressed. This ensures that the workload is distributed evenly across all available compute nodes without overwhelming the system with metadata management.

Answer

1KB to 100KB

Answer

1GB to 5GB

Answer

Exactly 256MB to match HDFS blocks

Question 12

A company is using an external S3 stage to load data daily. They want Snowflake to automatically delete the source files from the S3 bucket only after they have been successfully loaded into the table. Which COPY INTO option should they enable?

Accepted Answer

PURGE = TRUE. The PURGE parameter in the COPY INTO command is designed for automated cleanup of staged files. When set to TRUE, Snowflake will issue a delete command to the stage (internal or external) for every file that was successfully ingested. This helps manage storage costs and ensures that the same files are not accidentally processed again in future load cycles.

Answer

DELETE_AFTER_LOAD = TRUE

Answer

REMOVE_FILES = TRUE

Answer

AUTO_CLEAN = TRUE

Question 13

When loading semi-structured data like Parquet into a Snowflake table, what is a primary advantage of using Parquet over CSV for the ingestion process?

Accepted Answer

Parquet files contain schema metadata, allowing for easier mapping of complex data.. Parquet is a columnar storage format that includes embedded schema information and metadata about the data it contains. This allows Snowflake to automatically detect column names and types during the load process. Unlike CSV, which is a flat text format, Parquet's structure enables more efficient data mapping and better performance during ingestion of complex, nested datasets.

Answer

Parquet files are always smaller than CSV files regardless of the data content.

Answer

Parquet files can be loaded using the PUT command directly into a table.

Answer

Parquet is the only format that supports the ON_ERROR = CONTINUE parameter.

Question 14

Which TWO statements are true regarding the behavior and management of External Tables in Snowflake?

Accepted Answer

External tables can be manually refreshed or configured to refresh automatically using cloud notifications.. External tables allow Snowflake to query data stored in external cloud storage without first importing it into Snowflake's proprietary storage format. They are read-only and rely on metadata that points to the files. To maintain performance and accuracy, they can be partitioned based on the folder structure and require metadata refreshes to detect new or removed files in the cloud bucket.

Answer

External tables support the same performance optimizations as native tables, including clustering keys.

Answer

Data in external tables can be updated directly using standard DML commands like UPDATE or DELETE.

Answer

External tables store a version of the data in Snowflake's internal storage for faster access.

Question 15

Refer to the exhibit. A user executes a COPY INTO command and then queries the COPY_HISTORY. Based on the output shown, what most likely happened during the load and what is the current state of the data in the SALES_DATA table?

Accepted Answer

Exactly 500 rows were successfully loaded into the table, and the 2 errors were ignored.. The COPY_HISTORY result showing 'PARTIALLY_LOADED' with specific row errors indicates that the ON_ERROR parameter was likely set to 'CONTINUE'. In this mode, Snowflake skips rows that contain errors but proceeds to load all valid records into the table. This results in a successful transaction for the 500 valid rows, while the 2 erroneous rows are logged but not ingested.

Answer

The load failed completely, and no rows were added to the table because of the 2 errors.

Answer

The 2 errors represent duplicate files that were already loaded in a previous session.

Answer

The entire file was skipped because the number of errors exceeded the default threshold.