DEA-C02 Data Movement Practice Test 2 — 15 Questions

Question 1

When loading data into Snowflake, which file format option should be configured to ensure that empty strings in a CSV file are correctly interpreted as NULL values?

Accepted Answer

NULL_IF = (''). Proper handling of NULLs is essential for maintaining data quality during ingestion. Configuring NULL_IF allows Snowflake to map specific input values, such as empty strings or custom placeholders, to native SQL NULLs. This prevents downstream analytical errors where empty strings might be treated as valid text, ensuring that data movement results in clean, query-ready tables that accurately reflect the source system's intent.

Answer

EMPTY_FIELD_AS_NULL = TRUE

Answer

SKIP_BLANK_LINES = TRUE

Answer

TRIM_SPACE = TRUE

Question 2

A data engineer is using Snowpipe to load data. The data source is an S3 bucket that frequently receives files with the same name. What happens to these files in the target table if they are uploaded to the same S3 path?

Accepted Answer

Snowpipe will ignore the new file because the file name already exists in the load history.. Snowpipe tracks file metadata in a load history table. If a file name is repeated, Snowpipe will not ingest it again unless the file is modified (if using event notifications) or the load history is cleared. This mechanism prevents duplicate data, which is crucial for maintaining data movement accuracy. Understanding this behavior helps engineers design robust pipelines that avoid unintentional data duplication or missing updates when dealing with recurring file names.

Answer

Snowpipe will automatically overwrite the existing data in the table with the new file content.

Answer

Snowpipe will append the data to the table, resulting in duplicate records.

Answer

The pipe will enter an error state until the administrator clears the load history.

Question 3

Which THREE conditions must be met to successfully use Snowpipe to ingest data from an external stage?

Accepted Answer

A storage integration must be configured to provide secure access to the cloud storage.. Snowpipe requires a specific architectural setup involving external storage, notification mechanisms, and Snowflake permissions. These three components ensure that whenever a new file arrives, Snowflake is notified, authorized, and capable of executing the load. Missing any of these pieces results in silent failures or authorization errors, making it essential for engineers to validate the entire end-to-end flow from the cloud storage to the target table.

Answer

A virtual warehouse must be running to process the pipe's ingestion tasks.

Answer

The user executing the COPY INTO command must have the ACCOUNTADMIN role.

Question 4

A data engineer wants to verify if a file was successfully loaded into a table via Snowpipe. Which function should be queried to see the load history?

Accepted Answer

SELECT * FROM TABLE(INFORMATION_SCHEMA.LOAD_HISTORY());. The COPY_HISTORY function is the standard tool for auditing data movement. It provides a detailed log of all copy operations, including files that failed or were skipped. Monitoring this is critical for detecting data ingestion gaps or errors in automated pipelines. By querying this view, engineers can proactively identify and resolve issues, ensuring that the data warehouse remains consistent and up to date with the latest source system information.

Answer

SELECT * FROM TABLE(INFORMATION_SCHEMA.PIPE_USAGE_HISTORY());

Answer

SELECT * FROM INFORMATION_SCHEMA.STAGES;

Answer

SHOW PIPE_STATUS FROM TABLE(my_table);

Question 5

Which command is used to clear the load history for a pipe, allowing files that were previously processed to be re-ingested?

Accepted Answer

ALTER PIPE my_pipe REFRESH;. Sometimes it is necessary to reload data due to errors or upstream changes. The ALTER PIPE ... REFRESH command is the standard way to trigger a re-ingestion of files. Knowing this command is critical for recovery scenarios in data movement, as it provides a controlled way to re-process files without manually recreating the pipe or the underlying stages, maintaining pipeline continuity.

Answer

ALTER PIPE my_pipe RESET;

Answer

DROP PIPE my_pipe; CREATE PIPE my_pipe ...;

Answer

ALTER PIPE my_pipe SET LOAD_HISTORY = CLEAR;

Question 6

A company is using Directory Tables on an Amazon S3 external stage to keep track of unstructured data files. They want to ensure that the directory table metadata is always synchronized with the actual files in S3 without manual intervention. What is the most efficient way to achieve this?

Accepted Answer

Set the AUTO_REFRESH parameter to TRUE when creating the stage and configure SQS.. Directory tables provide a catalog of files in an external stage, and keeping them synchronized is vital for accurate metadata queries. While manual REFRESH commands work, automating this via event notifications is the best practice for high-scale environments. This ensures that the metadata layer is updated in real-time as files are added or deleted from the underlying cloud storage provider.

Answer

Use a Snowflake Task to run 'ALTER STAGE ... REFRESH' every thirty seconds.

Answer

Define the stage with the DIRECTORY = (ENABLE = TRUE) clause only.

Answer

Create a Stream on the stage to capture file changes and update a metadata table.

Question 7

Refer to the exhibit. If the COPY INTO command encounters a single data type mismatch error in the 500th row of the third file being processed, what will be the result of the operation?

Accepted Answer

The entire load operation fails, and no rows from any of the files are committed.. The ON_ERROR parameter controls how Snowflake handles rows that violate schema constraints or file format rules during a load. Understanding the specific behavior of 'ABORT_STATEMENT' is critical for data integrity, as it ensures an 'all-or-nothing' approach. This is particularly important when loading related datasets where partial loads could lead to downstream data quality issues.

Answer

The first two files will be loaded, but the third file will be skipped entirely.

Answer

The 499 valid rows of the third file will be loaded, and the error will be logged.

Answer

Snowflake will ignore the error and insert a NULL value into the column for that row.

Question 8

A data engineer needs to unload 100GB of data from a Snowflake table to an external S3 stage. The requirement is to ensure the output files are approximately 256MB each to optimize for downstream consumption. Which parameter should be adjusted?

Accepted Answer

Use the MAX_FILE_SIZE parameter and set it to 268435456.. When unloading data using the COPY INTO <location> command, Snowflake automatically determines the number and size of the output files based on the volume of data and the resources of the virtual warehouse. However, engineers can control this behavior using specific parameters to ensure the files are sized correctly for external tools like Spark or Athena.

Answer

Set the FILE_EXTENSION parameter to '.csv.gz' to force compression sizing.

Answer

Adjust the SINGLE parameter to TRUE to ensure all data is in one file.

Answer

Modify the warehouse size to X-Small to limit the parallel writing capabilities.

Question 9

A Data Engineer wants to check for syntax errors in a CSV file before actually loading the data into a production table. Which COPY INTO parameter should be used to accomplish this without incurring data load costs?

Accepted Answer

VALIDATION_MODE = 'RETURN_ERRORS'. Snowflake provides a validation mechanism that allows users to parse files and identify errors without committing the data to the table. This is an essential step in data movement workflows to ensure that file format definitions and schema mappings are correct. It prevents the need to delete incorrectly loaded data and saves on credits.

Answer

ON_ERROR = 'SKIP_FILE'

Answer

PURGE = FALSE

Answer

FORCE = TRUE

Answer

RETURN_FAILED_ONLY = TRUE

Question 10

Which THREE file formats are supported for both loading data into Snowflake and unloading data from Snowflake? (Choose three.)

Accepted Answer

CSV. Snowflake supports a variety of file formats for data movement, but not all formats are bidirectional. Understanding which formats can be used for both ingestion and extraction is crucial for designing interoperable data pipelines that need to move data in and out of the Snowflake Data Cloud for various business use cases.

Answer

ORC

Answer

Avro

Question 11

A data engineer is concerned that the S3 bucket used for staging data is filling up with files that have already been successfully loaded into Snowflake. Which parameter should be added to the COPY INTO statement to automatically remove these files?

Accepted Answer

PURGE = TRUE. Managing the lifecycle of files in a stage is an important part of data movement. If files are not removed after being loaded, they consume storage space and increase costs. Snowflake provides a built-in mechanism to handle this cleanup automatically as part of the ingestion process, ensuring that only new, unprocessed data remains in the stage.

Answer

ON_SUCCESS = 'DELETE'

Answer

TRUNCATE_STAGE = TRUE

Answer

AUTO_CLEAN = TRUE

Question 12

Refer to the exhibit. When a new JSON file is added to the S3 bucket, the data does not appear in the external table. What is the most likely reason for this behavior?

Accepted Answer

The S3 event notifications have not been configured to send messages to the SQS queue.. External tables allow Snowflake to query data directly from cloud storage without moving it into Snowflake's internal storage. However, they rely on metadata that must be kept in sync. Even with AUTO_REFRESH=true, specific infrastructure requirements must be met to trigger the metadata update, and understanding these requirements is essential for troubleshooting data visibility issues.

Answer

The external table must be manually refreshed using the 'ALTER TABLE ... REFRESH' command.

Answer

JSON files are not supported for AUTO_REFRESH in external tables.

Answer

The virtual warehouse assigned to the table is suspended.

Question 13

A Data Engineer is configuring a Snowflake Storage Integration for Azure Data Lake Storage Gen2. Which TWO parameters are required in the CREATE STORAGE INTEGRATION command? (Choose two.)

Accepted Answer

AZURE_TENANT_ID. Creating a Storage Integration for Azure requires specific parameters that define the tenant and the allowed locations. This object acts as the bridge between Snowflake and Azure, allowing for secure data movement without managing long-lived access keys. Correct configuration ensures that Snowflake can properly authenticate against the Microsoft Entra ID tenant.

Answer

STORAGE_AWS_ROLE_ARN

Answer

STORAGE_ACCOUNT_KEY

Answer

STORAGE_PROVIDER = 'S3'

Question 14

A data pipe has been created to load data into a table. The data engineer notices that no new data is being loaded despite files arriving in the stage. Which function should be used to troubleshoot the pipe's current status and identify potential errors?

Accepted Answer

SELECT SYSTEM$PIPE_STATUS('my_pipe');. Troubleshooting Snowpipe is a common task for data engineers. Since Snowpipe runs as a background service, standard query logs might not immediately reveal why a load is failing. Snowflake provides specific system functions to check the pipe's health and view the history of files it has attempted to process, which is vital for debugging.

Answer

SELECT * FROM TABLE(INFORMATION_SCHEMA.LOAD_HISTORY(...))

Answer

DESCRIBE PIPE my_pipe;

Answer

SHOW PIPES LIKE 'my_pipe';

Question 15

A data engineer is unloading data to an external stage. They want to ensure that if the target file already exists in the S3 bucket, it is replaced by the new file. Which parameter should be set in the COPY INTO <location> command?

Accepted Answer

OVERWRITE = TRUE. When unloading data, Snowflake's default behavior is to prevent overwriting existing files to avoid accidental data loss. However, in many automated pipelines, it is necessary to replace old exports with fresh data. Understanding the parameters that control this behavior is fundamental for managing data exports and maintaining consistent external datasets.

Answer

REPLACE = TRUE

Answer

FORCE = TRUE

Answer

ON_ERROR = 'CONTINUE'