DEA-C02 Data Movement — 50 Questions

Question 1

An organization is migrating large datasets from on-premises storage to Snowflake using Snowball. Which TWO steps are mandatory to ensure the data is encrypted and accessible within the Snowflake account?

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

Accepted Answer

Encrypt the data using a customer-managed key before loading onto the Snowball device.. Data movement via Snowball requires specific cryptographic handling and staging configuration. Encrypting data before transit ensures security, while the subsequent COPY INTO command must be configured with the appropriate encryption keys generated during the Snowball process. Understanding these security protocols is critical for compliance and ensuring that high-volume data transfers remain secure from origin to destination without exposing sensitive information during the physical migration phase.

Answer

Use a public network connection to stream data directly into Snowflake stages.

Answer

Provide the encryption key to Snowflake support for decryption upon device arrival.

Answer

Create a temporary internal stage to serve as an intermediary for Snowball data.

Question 2

A company needs to unload data from a Snowflake table to an S3 bucket in Parquet format. The output files must be partitioned by year and month. Which approach is most efficient?

Accepted Answer

Use the COPY INTO command with a path expression containing date-based columns.. Using the COPY INTO <location> command with a partition path expression is the most efficient way to structure unloaded data. By including the date components in the file path, downstream systems can use partition pruning to improve read performance. This task is fundamental for data lake maintenance and ensuring that exported Snowflake data adheres to modern industry standards for storage organization, supporting both performance and logical data partitioning.

Answer

Unload all data into one file and use an external script to rename and move files.

Answer

Execute a series of COPY INTO commands for each month using a WHERE clause.

Answer

Unload the data as JSON and convert to Parquet using a separate compute cluster.

Question 3

Which THREE file formats are natively supported for unloading data directly from Snowflake using the COPY INTO <location> command?

Accepted Answer

CSV. Understanding supported unload formats is critical for ensuring interoperability between Snowflake and external systems like data lakes or BI tools. Snowflake supports CSV, JSON, and Parquet as first-class formats for exporting data. These formats cover the vast majority of data exchange requirements, allowing engineers to choose the best format based on downstream consumption needs and storage optimization strategies within their data architecture.

Answer

XML

Answer

SQL

Question 4

A data engineer is loading files into Snowflake using Snowpipe. The files have a header row that needs to be skipped. Which parameter in the file format definition will achieve this?

Accepted Answer

SET SKIP_HEADER = 1;. The 'SKIP_HEADER' parameter is specifically designed to handle files that contain non-data metadata in the first row. Correctly configuring file formats is essential for data quality; failing to skip the header would result in the header string being treated as actual data, corrupting the table contents and potentially causing type-conversion errors during the ingestion process, which undermines the integrity of the downstream analytics.

Answer

SET SKIP_ROW = 1;

Answer

SET HEADER_ROW = TRUE;

Answer

SET IGNORE_FIRST_LINE = TRUE;

Question 5

When using Snowpipe, how should a data engineer verify that the files are being processed correctly and identify any files that failed to ingest?

Accepted Answer

Query the 'COPY_HISTORY' view in the 'INFORMATION_SCHEMA'.. Monitoring ingestion is a key responsibility for a data engineer. Using the 'COPY_HISTORY' view or the 'PIPE_USAGE_HISTORY' function provides deep insights into the ingestion status of individual files. This is crucial for maintaining pipeline health; without proper monitoring, failed loads can go unnoticed for long periods, leading to data staleness and business impact, which is unacceptable in production environments.

Answer

Check the 'STAGES' table to see a list of ingested files.

Answer

Use the 'SHOW PIPES' command for detailed file-level load results.

Answer

Examine the S3 bucket logs to verify the pipe successfully read the files.

Question 6

Which TWO of the following are valid ways to load data from an internal stage into a Snowflake table?

Accepted Answer

Use the COPY INTO command to load files from an internal stage.. Snowflake offers flexible methods for internal staging. Using the COPY INTO command is the standard way for bulk loads from files, while the SnowSQL PUT command is necessary for uploading files from a local machine to the internal stage before they can be loaded. Understanding this two-step process—moving files to the stage, then loading them into the table—is fundamental to mastering data ingestion workflows in Snowflake.

Answer

Directly insert data into the table using a standard SQL INSERT statement.

Answer

Create an external table that reads directly from the internal stage.

Answer

Use the ALTER TABLE command to import data from an internal stage.

Question 7

Refer to the exhibit. A data engineer executes the provided SQL command to load data from an external stage. What will be the outcome if one file out of 100 contains a single row with a data type mismatch, while all other files and rows are valid?

Accepted Answer

The specific file with the error will be skipped, and all other 99 files will be loaded and deleted from the stage.. The ON_ERROR = 'SKIP_FILE_10%' setting tells Snowflake to skip any file where the percentage of error rows exceeds 10%. In this scenario, a single row error in a file with multiple rows will likely represent less than 10% of that file's total rows. Therefore, the specific file containing the error will be skipped entirely, and the PURGE = TRUE clause will only delete the successfully loaded files from the stage.

Answer

The command will fail immediately because any data type mismatch triggers an abort by default.

Answer

The entire batch of 100 files will be loaded, but the row with the mismatch will be replaced with NULL values.

Answer

All 100 files will be deleted from the stage regardless of whether they were successfully loaded into the table.

Question 8

Which Snowflake object is used to store the connection information and access credentials for an external cloud storage location like Azure Blob Storage, ensuring that credentials are not exposed in SQL code?

Accepted Answer

Storage Integration. Storage Integrations are first-class Snowflake objects that decouple the security credentials from the stage definition. By using a Storage Integration, administrators can grant Snowflake access to specific cloud storage paths using IAM roles or service principals. This enhances security by centralizing credential management and preventing users from seeing sensitive keys in the stage's DDL or metadata.

Answer

External Stage

Answer

File Format

Answer

Resource Monitor

Question 9

Refer to the exhibit. What is the primary advantage of using this syntax during a data movement task instead of loading the raw JSON into a VARIANT column first?

Accepted Answer

It enables the data to be loaded directly into a permanent table with a defined schema, improving downstream query performance.. Snowflake allows for 'Transformation during Load' by using a SELECT statement within the COPY command (or as a standalone query against a stage). This approach allows data engineers to perform column projection, type casting, and flattening of nested values before the data is written to the table. This reduces storage requirements by excluding unneeded fields and improves query performance on the target table.

Answer

It bypasses the need for a virtual warehouse, making the data movement completely free.

Answer

This syntax is required because Snowflake cannot store raw JSON data in a table without pre-processing.

Answer

It automatically creates a new table with the correct schema if the table does not already exist.

Question 10

A data engineer needs to track the metadata of files located in an external S3 bucket without actually loading the data into a Snowflake table. Which TWO Snowflake features should be combined to achieve this? (Select TWO)

Accepted Answer

Directory Tables. To manage and query file-level metadata without ingestion, Snowflake uses Directory Tables attached to stages. A Directory Table stores information like file names, sizes, and timestamps. When combined with an External Table, the engineer can query the file content while the Directory Table provides an easy way to see what files are available and when they were last updated.

Answer

Snowpipe

Answer

Streams on Stages

Answer

Materialized Views

Question 11

When designing a data movement pipeline that unloads sensitive data from Snowflake to a cloud storage stage, which TWO methods provide the highest level of data security? (Select TWO)

Accepted Answer

Using Client-Side Encryption with a user-provided Master Key during the COPY INTO <location> command.. Security in data movement involves both access control and data protection. Using a Storage Integration ensures that Snowflake uses a secure, non-credential-based method to access the storage. Client-side encryption ensures that the data is encrypted using a master key before it even leaves the Snowflake environment, protecting it from unauthorized access within the cloud storage provider.

Answer

Encrypting the data using Snowflake-managed keys with the ENCRYPTION = (TYPE = 'SNOWFLAKE_SSE') parameter.

Answer

Setting the stage's directory table to 'REFRESH_ON_CREATE = FALSE' to hide the file list.

Answer

Using a Public S3 bucket to simplify the connection and ensure no authentication errors occur.

Question 12

An engineer is configuring a Snowpipe for a high-volume data stream. Which THREE factors directly influence the cost of using Snowpipe? (Select THREE)

Accepted Answer

The number of files processed by the pipe.. Snowpipe costs are determined by its serverless nature. There is a fixed overhead for every file notified and processed, making many small files more expensive than fewer large files. Additionally, Snowflake charges for the actual compute time used to ingest the data and for the management of the internal notification queue that triggers the pipe's execution.

Answer

The size and uptime of the virtual warehouse assigned to the pipe.

Answer

The distance between the S3 bucket region and the Snowflake region.

Question 13

A data engineer is configuring Snowpipe to ingest files from an S3 bucket. The bucket contains subfolders, and the engineer needs to ingest files from all subfolders recursively. How should the pipe be configured to achieve this?

Accepted Answer

Define the stage pointing to the root bucket and use a pattern that matches the subfolders.. Snowpipe naturally handles recursive ingestion if the stage is defined at the bucket level or a common prefix. When creating the pipe, the file pattern must be set to match the directory structure. This approach is essential for scalable data movement architectures where source systems drop files into dynamic subdirectories based on timestamps or source identifiers, ensuring that automated ingestion captures all incoming data without requiring manual updates to the pipe configuration.

Answer

Set the pipe's AUTO_INGEST parameter to recursive.

Answer

Create a separate pipe for every subfolder in the S3 bucket.

Answer

Configure an S3 event notification to trigger the pipe for each subfolder specifically.

Question 14

A company is migrating on-premises data to Snowflake using Snowball Edge. Which step is mandatory to ensure the data is loaded securely into the target Snowflake table after the device arrives at the Snowflake facility?

Accepted Answer

Use the PUT command to upload the data from the Snowball device to the internal stage.. After the Snowball device is returned and the data is loaded into an S3 bucket by the provider, you must create an external stage referencing that bucket. Then, you execute the COPY INTO command to move data into target tables. This process is critical for large-scale migrations where network bandwidth is insufficient, ensuring data integrity and security while bridging the gap between physical hardware and cloud-native storage.

Answer

Run the SnowSQL command to directly mount the Snowball device to the Snowflake warehouse.

Answer

Configure a storage integration object to grant Snowflake access to the temporary S3 bucket.

Answer

Import the Snowball data directly into a table using the IMPORT INTO command.

Question 15

Which TWO of the following statements are true regarding the use of the PUT command in Snowflake?

Accepted Answer

The PUT command can compress files automatically before uploading them to the internal stage.. The PUT command is primarily used for uploading files from a local client machine to an internal stage. Understanding its limitations, such as file size constraints and its lack of support for external stages, is critical for architects planning data movement. Knowing when to use PUT versus Snowpipe or bulk loading via COPY INTO ensures that the ingestion architecture is optimized for performance, security, and scalability in various deployment scenarios.

Answer

The PUT command supports uploading files directly to an external stage (e.g., S3).

Answer

The PUT command is the recommended tool for high-volume, automated production data ingestion.

Answer

The PUT command can be executed within a Snowflake Worksheet.

Question 16

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 17

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 18

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 19

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 20

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;