DEA-C02 Data Movement Practice Test 3 — 15 Questions

Question 1

A data engineer is configuring a Snowpipe instance to ingest files from an external S3 bucket. The requirement is to ensure that files are processed only once. Which configuration step is critical to prevent duplicate data ingestion?

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

Accepted Answer

Ensure the pipe definition points to a specific stage that tracks ingested file history automatically.. Snowpipe uses event notifications to trigger ingestion, but it is not inherently idempotent against re-processed files. By utilizing the 'ON_ERROR = SKIP_FILE' setting and maintaining the internal load history metadata, Snowflake tracks the file names. If a file name exists in the metadata, it is ignored. This mechanism is vital for maintaining data integrity in cloud storage integration patterns where duplicate notifications might occur due to network or service retries.

Answer

Enable the 'FORCE' parameter in the COPY INTO statement to override existing file metadata.

Answer

Configure the S3 bucket to delete files immediately after the ingestion process completes.

Answer

Use a transformation function in the COPY INTO statement to generate a unique hash for each row.

Question 2

A data engineer needs to migrate data from an on-premises database to Snowflake using Snowball. Which TWO actions must be performed to ensure a successful data transfer?

Accepted Answer

Use the Snowball client to encrypt and transfer data onto the physical appliance.. Snowball is an appliance-based transfer method for massive datasets. The engineer must first stage data into the device using the Snowflake-provided Snowball client, then physically transport the device to the AWS facility. Once AWS imports the data into S3, the engineer uses the COPY INTO command to move data into Snowflake. This process is essential for bypassing bandwidth constraints when moving petabyte-scale data volumes into cloud storage.

Answer

Establish a dedicated Snowpipe connection between the on-premises server and the Snowball device.

Answer

Configure a storage integration object to point directly to the physical Snowball device's hardware interface.

Answer

Perform an immediate metadata refresh on the target table using the ALTER TABLE command.

Question 3

Which Snowflake command is used to move data from a local client machine directly to an internal stage?

Accepted Answer

INSERT INTO. The PUT command is the primary tool for uploading files from a local file system to Snowflake internal stages. It supports compression, encryption, and automatic chunking. Mastering this command is fundamental for manual data movement tasks, debugging ingestion pipelines, or handling small-scale data loads where cloud storage integration is not yet configured or required for the specific workflow.

Answer

COPY INTO

Answer

GET

Question 4

Which THREE file formats are natively supported by Snowflake for data loading via the COPY INTO command?

Accepted Answer

CSV. Snowflake's COPY INTO command provides native support for several common formats to facilitate seamless data integration. Recognizing these formats allows engineers to choose the most efficient data structure for their specific pipeline requirements. CSV, JSON, and Parquet are the most widely used, providing a mix of human-readability, semi-structured capability, and high-performance columnar storage for analytical workloads.

Answer

XLSX

Answer

EXE

Question 5

A data engineer is configuring a Snowpipe instance to ingest files from an external S3 bucket. The requirement is to ensure that files are processed only once. Which configuration step is critical to prevent duplicate data ingestion?

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

Accepted Answer

Configure an SQS queue notification on the S3 bucket and link it to the pipe via a storage integration.. Snowpipe with auto-ingest relies on cloud provider event notifications to trigger data loading. However, for scenarios where event configuration is restricted or complex, the Snowpipe Streaming API or manual REST API calls are alternatives. In native Snowflake environments, configuring an S3 integration with an SQS queue is the standard mechanism to ensure seamless, event-driven data ingestion without manual intervention, maintaining pipeline throughput and reducing latency for downstream analytics.

Answer

Enable the 'AUTO_REFRESH' parameter set to 'TRUE' in the pipe definition.

Answer

Use the Snowpipe REST API with a client-side polling script.

Answer

Schedule a task to run the 'ALTER PIPE ... REFRESH' command every minute.

Question 6

A data engineer is configuring a Snowpipe instance to ingest files from an external S3 bucket. The requirement is to ensure that files are processed only once. Which configuration step is critical to prevent duplicate data ingestion?

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

Accepted Answer

The pipe's load history, which is maintained by Snowflake and can be viewed with SYSTEM$PIPE_STATUS.. Snowpipe maintains a per-pipe load history that records the files it has successfully ingested. When new files arrive, Snowpipe checks this history and skips files that were already processed, preventing duplicates. This is an internal mechanism, not exposed through ACCOUNT_USAGE or stage metadata.

Answer

The COPY_HISTORY view in the ACCOUNT_USAGE schema, which is automatically updated by Snowpipe.

Answer

The pipe's notification integration, which filters out files that have already been processed.

Answer

The stage's file metadata, which includes a checksum that Snowflake compares before loading.

Question 7

A data engineer needs to unload data from a Snowflake table to an external stage in Parquet format. The unloaded files must be encrypted with a customer-managed key (CMK) in the cloud storage provider. Which Snowflake feature should be used to meet this requirement?

Accepted Answer

Configure the external stage with a storage integration that references the CMK.. To unload data with a customer-managed key, you must configure a storage integration that references the CMK. This integration is then used by the external stage. The COPY INTO command can then unload data, and the cloud storage will encrypt the files using the specified key.

Answer

Create a named file format with the ENCRYPTION option set to 'CMK'.

Answer

Use the ENCRYPTION = (TYPE = 'AWS_SSE_KMS', KMS_KEY_ID = '...') option in the COPY INTO command.

Answer

Enable client-side encryption by providing a 128-bit or 256-bit key in the COPY INTO command.

Question 8

A data engineer needs to load data from a local file system into a Snowflake table. The file is 100 MB in size. Which Snowflake stage type should be used for this one-time load?

Accepted Answer

User stage. For a one-time load from a local file, the user stage is the simplest choice. It is automatically available, requires no setup, and can be used with PUT and COPY INTO. Other stage types are either for cloud storage or add unnecessary complexity for a single load.

Answer

Named internal stage

Answer

External stage

Answer

Table stage

Question 9

A data engineer needs to copy data from an internal named stage into a target table. The stage contains files in Parquet format, and the engineer wants the load to fail immediately if any file cannot be parsed rather than skipping bad rows. Which COPY INTO option set accomplishes this while still allowing the engineer to review which files were problematic?

Accepted Answer

ON_ERROR = ABORT_STATEMENT on the loading COPY INTO, and VALIDATION_MODE = RETURN_ERRORS in a separate validation pass to enumerate problematic files.. ABORT_STATEMENT makes a loading COPY INTO stop when it encounters a parsing error instead of continuing past it. Because a loading run only reports the error that halted it, a separate validation pass using VALIDATION_MODE = RETURN_ERRORS is the supported way to enumerate every problematic file. Together they deliver strict failure behavior plus an error inventory.

Answer

ON_ERROR = CONTINUE with VALIDATION_MODE = RETURN_ALL_ERRORS

Answer

ON_ERROR = ABORT_STATEMENT with VALIDATION_MODE = RETURN_ERRORS, then inspect the returned errors before the real load.

Answer

ON_ERROR = ABORT_STATEMENT with VALIDATION_MODE = RETURN_ERRORS

Question 10

A data engineer has a Snowpipe configured to load JSON events from an external S3 stage. The pipe was created without specifying ON_ERROR or VALIDATION_MODE. During a recent load, one file contained a malformed record that caused the entire file to be skipped, and no data from that file appears in the target table. The engineer wants to ensure that valid records in a file are loaded even if some records are malformed, while still capturing errors for review. Which COPY statement option should be set in the pipe definition to achieve this?

Accepted Answer

ON_ERROR = 'CONTINUE'. The scenario requires loading valid records even when some records are malformed, while still capturing errors. ON_ERROR = 'CONTINUE' is the only option that skips only the bad records and loads the rest. The other options either skip the entire file or perform validation without loading. Therefore, setting ON_ERROR to CONTINUE in the pipe definition solves the problem.

Answer

ON_ERROR = 'SKIP_FILE'

Answer

ON_ERROR = 'ABORT_STATEMENT'

Answer

VALIDATION_MODE = 'RETURN_ERRORS'

Question 11

A data engineer manages an ingestion pipeline that uses Snowpipe to load JSON events from an external GCS stage into a landing table. The operations team reports that some events are missing from the landing table even though the corresponding objects exist in the bucket. The engineer must diagnose why specific files were not ingested. Which TWO actions should the engineer take to investigate the missing files? (Choose two.)

Accepted Answer

Query the COPY_HISTORY table function for the pipe's target table and filter by the file names to see whether each file was loaded, failed, or partially loaded.. Diagnosing missing Snowpipe events requires per-file status and pipe-level health. COPY_HISTORY shows whether each specific file was loaded, failed, or partially loaded along with error messages, while SYSTEM$PIPE_STATUS reveals whether the pipe is running and how many files are pending. Together they separate a broken notification path from a pipe that is running but rejecting files.

Answer

Inspect the stage's directory table with ALTER STAGE ... REFRESH to confirm the new objects are visible to Snowflake.

Answer

Run ALTER PIPE ... REFRESH on the pipe to re-scan the stage prefix and pick up any objects that were added while the pipe was paused.

Answer

Query the PIPE_USAGE_HISTORY view filtered by pipe name to see the bytes and credits consumed by recent ingestion runs.

Question 12

A data engineer runs COPY INTO my_table FROM @my_ext_stage/path1/ which returns the status 'LOAD_FAILED' for several files with the error 'Numeric value is not recognized'. The files are still present in the external stage and have not been modified. The engineer corrects the data type mismatch in the source files and re-uploads them to the same stage path. Which action will allow the corrected files to be loaded?

Accepted Answer

Re-run the COPY INTO command with the FORCE = TRUE option.. The FORCE = TRUE option overrides the default behavior of skipping files that have already been processed by COPY INTO, regardless of whether they succeeded or failed. Because the files remain in the stage and were previously attempted, only FORCE = TRUE will cause them to be re-read. Other commands like ALTER PIPE REFRESH or ALTER STAGE REFRESH do not affect manual COPY INTO load history.

Answer

Run ALTER STAGE my_ext_stage REFRESH to update the stage metadata.

Answer

Run ALTER PIPE my_pipe REFRESH to force re-evaluation of the stage files.

Answer

Run REMOVE @my_ext_stage/path1/ to delete the files, then re-upload them.

Question 13

A data engineer is using the COPY INTO command to unload data from a Snowflake table to an external stage. The engineer wants to ensure that the unloaded files are compressed to reduce storage costs and speed up transfer. Which file format option should be specified in the COPY INTO statement to achieve compression?

Accepted Answer

FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP). To compress unloaded files, the file format must specify a compression algorithm, such as COMPRESSION = GZIP within the FILE_FORMAT parameter. This reduces file size and transfer time. The other options either specify encryption, file size limits, or incorrectly place the compression parameter outside the file format definition. The correct syntax is to include COMPRESSION in the file format options.

Answer

COMPRESSION = GZIP

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_S3')

Answer

MAX_FILE_SIZE = 100000000

Question 14

A data engineer runs a COPY INTO command to load a CSV file from an internal stage into a target table. The command completes successfully, but the target table contains zero rows. The engineer notices that the file was previously loaded into a different table using the same stage and a similar COPY INTO statement. What is the most likely cause of the empty load?

Accepted Answer

The file was skipped because it had already been loaded into the target table, as indicated by the load metadata.. Snowflake tracks which files have been loaded into each table using load metadata. When a COPY INTO command is executed, it checks this metadata and skips files that have already been loaded into that specific table. If the file was previously loaded into the same target table, it will be skipped, resulting in zero rows loaded. The engineer should verify the load history or use the FORCE = TRUE option to override the metadata check.

Answer

The internal stage was empty because the file was automatically purged after the previous load.

Answer

The COPY INTO command failed silently because the file format did not match the target table schema.

Answer

The file was already loaded into the target table and the load metadata prevented re-loading.

Question 15

A data engineer needs to unload a large dataset from a Snowflake table to an external stage (Amazon S3) in Parquet format. The table contains a column with sensitive data that must be masked in the output. The engineer wants to ensure the unloaded files are compressed and encrypted. Which approach should be used?

Accepted Answer

Create an external stage that references the S3 bucket with server-side encryption enabled, then run COPY INTO @stage FROM (SELECT column1, column2, SHA2(sensitive_column) AS masked_column FROM table) FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY).. To unload masked data, a subquery in the COPY INTO statement can transform the sensitive column. Encryption is handled by configuring the external stage with server-side encryption or using client-side encryption with a master key. The combination of a stage with encryption enabled and a subquery that applies masking ensures the unloaded data is both masked and encrypted. Parquet with SNAPPY compression is specified in the file format.

Answer

Run COPY INTO @stage FROM table FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ENCRYPTION = (TYPE = 'AWS_SSE_S3') and rely on Snowflake's automatic data masking for the sensitive column.

Answer

Create a secure view that masks the sensitive column, then run COPY INTO @stage FROM secure_view FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ENCRYPTION = (TYPE = 'AWS_SSE_S3').

Answer

Create a regular view that masks the sensitive column using a masking policy, then run COPY INTO @stage FROM view FILE_FORMAT = (TYPE = PARQUET COMPRESSION = SNAPPY) ENCRYPTION = (TYPE = 'AWS_SSE_KMS' MASTER_KEY = 'key_id').