COF-C03 Data Loading, Unloading, and Connectivity — Timed 40 Questions

Question 1

An administrator needs to troubleshoot a failing COPY INTO command. They want to see exactly which rows are failing and the specific error messages without actually loading any valid data into the table. Which parameter should they use?

40 minute time limit — choose an answer to begin.

Accepted Answer

VALIDATION_MODE = RETURN_ERRORS. The VALIDATION_MODE parameter in the COPY command allows users to dry-run a data load. By setting it to RETURN_ERRORS, Snowflake parses the files in the stage and identifies all data issues without committing any records. This is an essential tool for data quality checks and debugging schema mismatches before executing a production data ingestion pipeline.

Answer

VALIDATION_MODE = RETURN_ALL_ERRORS

Answer

ON_ERROR = RETURN_ERRORS

Answer

VALIDATION_MODE = RETURN_1_ROWS

Question 2

A data engineer is monitoring Snowpipe ingestion history. Which TWO methods can be used to retrieve information about the status of files processed by a specific pipe over the last 14 days?

Accepted Answer

Querying the SNOWPIPE_LOAD_HISTORY Information Schema table function.. Monitoring Snowpipe is essential for ensuring data pipelines are operating correctly. Snowflake provides both SQL-based and API-based methods for this. The SNOWPIPE_LOAD_HISTORY function in the Information Schema provides a detailed log of file-level loading status, while the PIPE_STATUS property via the SYSTEM$PIPE_STATUS function provides real-time state information about the pipe's execution and health.

Answer

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

Answer

Querying the Account Usage view LOAD_HISTORY.

Answer

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

Question 3

Which THREE operations can be performed directly within a COPY INTO statement to transform data as it is being loaded from a stage into a table?

Accepted Answer

Column reordering by selecting specific elements from the stage (e.g., $1, $2).. Snowflake's COPY INTO command supports a subset of SQL functions that allow for 'light' transformations during ingestion. This 'COPY transformation' capability is powerful because it allows users to reorder columns, cast data types, and even flatten simple semi-structured data without needing a separate staging table or a complex ETL tool. This optimizes the ingestion pipeline by combining loading and transformation steps.

Answer

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

Answer

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

Question 4

A security administrator is setting up a Storage Integration for an AWS S3 bucket. Which TWO pieces of information are required from the AWS Console to successfully complete the link between Snowflake and S3 after creating the integration object?

Accepted Answer

The STORAGE_AWS_IAM_USER_ARN provided by Snowflake after running DESCRIBE.. A Storage Integration is a Snowflake object that stores a generated identity and access management (IAM) entity for your external cloud storage. This method is more secure than using credentials because it relies on a trust relationship between Snowflake and the cloud provider. To finalize this relationship, specific values must be exchanged between the two platforms to authorize access.

Answer

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

Answer

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

Answer

The Snowflake Account Locator and the AWS VPC ID.

Question 5

An administrator needs to load comma-separated values from an Amazon S3 bucket into a Snowflake table. The source files frequently change, and the data includes occasional double quotes inside fields that are wrapped in quotes. Which COPY INTO option must be explicitly configured to ensure these embedded quotes do not cause parsing failures?

Accepted Answer

ESCAPE = '"'. Handling embedded quotes inside quoted fields requires telling the parser how to interpret the special character. Setting ESCAPE_UNENCLOSED_FIELD or the correct ESCAPE character prevents the COPY INTO command from failing when encountering irregular CSV formatting. Mastering file format options is crucial for handling messy real-world data feeds without pipeline errors.

Answer

FIELD_OPTIONALLY_ENCLOSED_BY = '"'

Answer

ESCAPE = 'NONE'

Answer

SKIP_HEADER = 1

Question 6

An organization is configuring an external stage to point to an Amazon S3 bucket. Which TWO security best practices should be implemented to ensure the most secure and manageable connectivity between Snowflake and the S3 bucket?

Accepted Answer

Creating a Storage Integration object to manage authentication via IAM roles.. Securing external stages requires a combination of identity management and network isolation. Using storage integrations is the preferred method because it avoids the need to manage long-lived secret keys within Snowflake SQL. Instead, it relies on IAM roles and trust relationships, which provide a more robust and auditable security framework for cloud cross-account communication.

Answer

Hardcoding the AWS_SECRET_KEY and AWS_ACCESS_KEY in the CREATE STAGE statement.

Answer

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

Answer

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

Question 7

Refer to the exhibit. If 'file1.csv' contains 100 rows with 5 malformed records, and 'file2.csv' contains 200 rows with all records correctly formatted, what will be the result of executing this command?

Accepted Answer

A total of 295 rows will be loaded into the target table.. The ON_ERROR = CONTINUE directive tells Snowflake to skip any rows that contain errors and continue loading the remaining valid data from the files. This is useful for bulk loading where a small percentage of bad data should not halt the entire ingestion process. Snowflake tracks the errors in the load metadata for later review.

Answer

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

Answer

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

Answer

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

Question 8

When unloading data from Snowflake to an external stage, a developer wants to ensure that the output is split into multiple files, each no larger than 50MB. Which combination of COPY INTO <location> options should be used?

Accepted Answer

SINGLE = FALSE and MAX_FILE_SIZE = 52428800. The unloading process in Snowflake is highly configurable to meet the requirements of downstream systems. By controlling file size and naming, developers can ensure that the exported data is easily consumable by other applications or cloud services. The MAX_FILE_SIZE parameter is the primary control for limiting the footprint of individual exported files.

Answer

SINGLE = TRUE and MAX_FILE_SIZE = 52428800

Answer

SINGLE = FALSE and MAX_FILE_SIZE = 50

Answer

OVERWRITE = TRUE and FILE_EXTENSION = 'csv'

Question 9

Which of the following describes the behavior of the Snowflake Kafka Connector during data ingestion?

Accepted Answer

It ingests data into a two-column table consisting of a VARIANT record and metadata.. The Kafka Connector is a powerful tool for streaming data into Snowflake. It abstracts the complexity of managing pipes and stages by automatically handling the ingestion of Kafka topics into Snowflake tables. Understanding its internal mechanics, such as how it handles JSON and metadata, is essential for developers working with real-time streams.

Answer

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

Answer

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

Answer

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

Question 10

A user wants to unload data from a Snowflake table to an internal stage. Which TWO statements are true regarding the encryption of the unloaded data?

Accepted Answer

Snowflake automatically encrypts data unloaded to an internal stage.. Security is a core tenet of the Snowflake architecture. When data leaves a table and is stored in a stage, it must remain protected. Snowflake provides automatic encryption for internal stages, but users must understand how this encryption interacts with the tools they use to retrieve the data from the cloud.

Answer

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

Answer

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

Answer

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

Question 11

Refer to the exhibit. What is the primary advantage of using this syntax to query files in a stage before loading them into a table?

Accepted Answer

It enables data transformation and column selection during the ingestion process.. Querying staged files directly allows for data exploration and transformation without the need for a persistent table. This capability is vital for schema-on-read workflows and for validating the structure of semi-structured data like JSON or Parquet before committing to a specific table schema in the database.

Answer

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

Answer

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

Answer

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

Question 12

A company wants to allow a third-party application to connect to Snowflake using a secure connection that does not rely on a username and password. Which connectivity option should they implement?

Accepted Answer

Key-pair authentication using a public and private key.. Modern security standards emphasize moving away from static passwords. Snowflake supports several advanced authentication methods to facilitate secure, programmatic access. Key-pair authentication and OAuth are the two primary methods used for service-to-service connectivity, providing a more secure and manageable alternative to traditional credentials.

Answer

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

Answer

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

Answer

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

Question 13

A data architect is designing a solution to load data from an S3 bucket into Snowflake. They are considering using External Tables versus the COPY INTO command. Which TWO statements accurately describe the differences?

Accepted Answer

The COPY INTO command generally provides better query performance than External Tables.. External tables and the COPY command serve different purposes in the Snowflake ecosystem. External tables allow for 'query-in-place' capabilities, making them ideal for data lakes, while the COPY command is the preferred method for high-performance analytics on data stored within Snowflake's optimized micro-partitioned storage.

Answer

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

Answer

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

Answer

External tables cannot be queried using standard SQL SELECT statements.

Question 14

To ensure optimal performance when loading large volumes of data into Snowflake using the COPY INTO command, which TWO practices are recommended?

Accepted Answer

Aiming for a file size between 100MB and 250MB (compressed).. Performance tuning for data loading focuses on parallelism and resource utilization. Snowflake is designed to handle massive scale, but it performs best when the work can be distributed across multiple threads. Managing file sizes and warehouse scaling are the two most impactful levers for optimizing ingestion speed.

Answer

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

Answer

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

Answer

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

Question 15

An enterprise data team is loading high-frequency clickstream JSON data into Snowflake using the COPY INTO command from an external S3 stage. The files contain nested arrays and objects that need to be flattened directly during the ingestion phase into separate relational columns without a post-processing transformation step. Which approach satisfies this requirement efficiently?

Accepted Answer

Execute the COPY INTO command directly specifying the target table and include a subquery utilizing the FLATTEN function in the SELECT clause to unpack the variant data.. Querying semi-structured data using FLATTEN within a SELECT clause of the COPY INTO command allows direct relational transformation during loading. This eliminates costly post-processing steps and materializes the flattened hierarchy into destination columns, making it critical for handling complex nested JSON efficiently at scale.

Answer

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.

Answer

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

Answer

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

Question 16

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 17

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 18

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 19

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 20

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