DEA-C02 Data Movement Practice Test 4 — 15 Questions

Question 1

A data engineer is loading a large CSV file into a Snowflake table using the COPY INTO command. The file contains 10 million rows, and the engineer wants to capture any rows that fail validation (e.g., data type mismatches) into a separate error table without failing the entire load. The engineer sets ON_ERROR = 'CONTINUE' and VALIDATION_MODE = 'RETURN_ERRORS'. After execution, the engineer notices that no rows were loaded, but the error table contains all 10 million rows. What is the most likely cause?

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

Accepted Answer

The VALIDATION_MODE parameter causes COPY INTO to only validate data and not load it.. VALIDATION_MODE is designed for testing and debugging. When set to RETURN_ERRORS, COPY INTO parses the file, validates each row against the target table schema, and returns all rows that would fail—without loading any data. This explains why no rows were loaded and all rows appeared in the error table. To actually load data while capturing errors, the engineer should remove VALIDATION_MODE and rely on ON_ERROR = 'CONTINUE', which loads valid rows and can optionally log errors.

Answer

The error table was populated because the file format did not match the table schema, causing all rows to be rejected.

Answer

The ON_ERROR = 'CONTINUE' setting skips all rows if any error is found.

Answer

The COPY INTO command was executed with FORCE = FALSE, and the file was already loaded previously, so it was skipped.

Question 2

A data engineer unloads a large Snowflake table to an external S3 stage with COPY INTO. The output must be split into files no larger than approximately 100 MB, and the engineer wants to control the naming pattern for downstream partitioning. Which COPY INTO option combination should be used?

Accepted Answer

MAX_FILE_SIZE = 100000000 with a custom file name prefix in the stage path. MAX_FILE_SIZE sets the upper bound for each unloaded file, so a large table is written as many objects of roughly the specified size, and Snowflake adds unique suffixes. Placing a chosen prefix in the stage path yields deterministic, parseable names that downstream jobs can use for partitioning. Together they meet both constraints without forcing a single file or relying on load-time filters.

Answer

MAX_FILE_SIZE = 100000000 with a PATTERN in the path

Answer

HEADER = TRUE with a custom file name prefix in the stage path

Answer

SINGLE = TRUE with a FILE_FORMAT specifying TYPE = PARQUET

Question 3

A data engineer needs to unload data from a Snowflake table to an external stage on Amazon S3. The table contains sensitive columns that must be masked in the unloaded files. The engineer wants to ensure that the masking is applied during the unload process without creating a separate masked view. Which approach should the engineer use?

Accepted Answer

Use the COPY INTO command with a SELECT statement that applies masking functions directly.. COPY INTO allows a SELECT statement to be specified, which can include masking expressions or calls to masking policies. This enables the engineer to transform sensitive columns during the unload, ensuring that only masked data is written to the external stage. It avoids creating a separate view and keeps the masking logic within the unload command, which is efficient and secure.

Answer

Unload the data as-is and then use an external function to mask the files in S3.

Answer

Set the session parameter MASKING_POLICY = 'ENFORCED' before running the unload.

Answer

Create a secure view with masking policies and unload from that view.

Question 4

A data engineer is designing a data movement pipeline that unloads data from a Snowflake table to an external stage (Amazon S3) in CSV format. The organization requires that the unloaded files are encrypted using client-side encryption with a master key, and that the data is compressed to reduce storage costs. Which TWO options must be specified in the COPY INTO command to meet these requirements? (Choose two.)

Accepted Answer

ENCRYPTION = (TYPE = 'AWS_CSE' MASTER_KEY = 'my_key'). To unload CSV data with client-side encryption and compression, the COPY INTO command must specify FILE_FORMAT with COMPRESSION = GZIP, and ENCRYPTION with TYPE = 'AWS_CSE' and a MASTER_KEY. These two options together satisfy the encryption and compression requirements. Server-side encryption options like AWS_SSE_S3 or AWS_SSE_KMS do not provide client-side encryption, and SNAPPY is not a valid compression for CSV.

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_KMS' MASTER_KEY = 'my_key')

Answer

ENCRYPTION = (TYPE = 'AWS_SSE_S3')

Answer

FILE_FORMAT = (TYPE = CSV COMPRESSION = SNAPPY)

Question 5

A data engineer is using the COPY INTO <table> command to load JSON data from an external stage into a Snowflake table with a VARIANT column. The JSON files contain nested arrays and objects. The engineer wants to load only specific elements from the JSON and also flatten one array into separate rows. Which combination of clauses should the engineer use in the COPY INTO command?

Accepted Answer

Load the entire JSON into a VARIANT column using COPY INTO, then use a separate INSERT ... SELECT with LATERAL FLATTEN to extract and flatten the data.. COPY INTO is designed to load data in bulk and cannot perform row expansion such as flattening an array into multiple rows. The correct approach is to first load the raw JSON into a VARIANT column using COPY INTO, then use a separate INSERT ... SELECT statement with LATERAL FLATTEN to extract and flatten the desired elements. This two-step method is efficient and leverages Snowflake's semi-structured data capabilities.

Answer

Use the TRANSFORM clause with a SELECT statement that includes LATERAL FLATTEN.

Answer

Use the MATCH_BY_COLUMN_NAME clause and a SELECT statement with LATERAL FLATTEN.

Answer

Use the FILE_FORMAT option with STRIP_OUTER_ARRAY = TRUE and REPLACE_INVALID_CHARACTERS = TRUE to automatically flatten the JSON.

Question 6

A data engineer is troubleshooting a Snowpipe that ingests files from an external S3 stage. New files are arriving in the bucket, but they are not being loaded. The engineer confirms the pipe exists and the COPY statement works when run manually. Which TWO conditions should the engineer verify to resolve the automatic ingestion failure? (Choose two.)

Accepted Answer

The storage integration and notification integration are correctly associated with the pipe. Auto-ingest fails when the event path between S3 and Snowflake is broken. The notification integration must be properly associated with the pipe so Snowflake accepts events, and the S3 bucket must publish ObjectCreated notifications to the exact SQS queue ARN that the integration exposes. When both are correct, file arrival triggers the pipe even though no user warehouse is involved.

Answer

The stage URL uses a trailing slash and the correct S3 region endpoint

Answer

The pipe's COPY statement includes PATTERN with a regular expression that matches the new files

Answer

The warehouse assigned to the pipe is resumed and sized appropriately for the file volume

Question 7

A data engineer is designing a data movement pipeline that unloads data from a Snowflake table to an external stage on Microsoft Azure. The organization requires that the unloaded files be encrypted at rest and that the encryption keys be managed by the organization. Which TWO options should the engineer use to meet these requirements? (Choose two.)

Accepted Answer

Enable Azure Storage Service Encryption (SSE) with customer-managed keys in the Azure portal.. To unload data to Azure with organization-managed encryption, the engineer can use either client-side encryption with AZURE_CSE, providing a master key in the COPY INTO command, or enable Azure Storage Service Encryption with customer-managed keys in Azure. Both approaches ensure that the organization controls the encryption keys. The other options either refer to AWS-specific encryption types or do not provide customer-managed keys.

Answer

Use the COPY INTO <location> command with the ENCRYPTION = (TYPE = 'AWS_SSE_S3') parameter.

Answer

Configure the external stage with a client-side encryption key using the ENCRYPTION = (TYPE = 'AWS_SSE_KMS') parameter.

Answer

Create a storage integration with the AZURE_TENANT_ID and AZURE_STORAGE_ACCOUNT parameters, and rely on Snowflake-managed encryption.

Question 8

A data engineer is designing a pipeline to unload a large fact table from Snowflake to an external stage on Amazon S3 for downstream analytics. The team wants to minimize cost and maximize throughput. (Choose two.)

Accepted Answer

Compress the unloaded files using GZIP to reduce storage and transfer costs.. To balance cost and throughput when unloading a large table, the engineer should control output file sizes with MAX_FILE_SIZE and compress the output with GZIP. These options reduce storage and transfer costs while allowing parallel consumption. Forcing a single file or relying on non-existent file format options would limit throughput or fail entirely.

Answer

Enable the PARALLEL option in the file format to allow concurrent writes to the same file.

Answer

Set the SINGLE = TRUE option to ensure all data is written to one file for easier downstream processing.

Answer

Use a single large warehouse with the maximum cluster size to force parallel writes to one file.

Question 9

A data engineer is configuring Snowpipe to ingest files from an S3 bucket. The bucket contains files with different schemas. Which approach allows the engineer to handle these variations without creating separate pipes?

Accepted Answer

Load the files into a table with a VARIANT column to store the semi-structured data.. Using a single Snowpipe with a variant column is the most scalable pattern for schema drift or varied structures. By loading raw data into a VARIANT column, the engineer avoids the overhead of managing multiple pipe definitions. This allows downstream transformations using SQL views or dynamic tables to parse the specific keys as needed, ensuring the ingestion process remains decoupled from schema changes while maintaining a low-latency data pipeline architecture.

Answer

Define multiple pipes pointing to the same stage with different pattern matching.

Answer

Use a schema-on-read approach by configuring the pipe to cast columns explicitly.

Answer

Configure the pipe to use an external table instead of loading data into an internal table.

Question 10

Which feature should be used to automate the loading of files from an S3 bucket into Snowflake as soon as they are uploaded, without manual intervention?

Accepted Answer

Snowpipe using event-based notifications.. Snowpipe is the native Snowflake service for continuous data ingestion. It leverages event notifications (such as S3 Event Notifications) to trigger the ingestion process immediately when new files arrive in cloud storage. This automation is vital for real-time reporting and analytics, ensuring that downstream data consumers have access to the freshest data without needing a developer to manually trigger batch processes or schedule cron jobs.

Answer

A scheduled task using the EXECUTE TASK command.

Answer

An external function that writes directly to the table.

Answer

A recurring COPY INTO command inside a stored procedure.

Question 11

A data engineer needs to load data from an Azure Blob storage container into Snowflake. The organization requires a secure connection that does not use public endpoints. What should the engineer configure?

Accepted Answer

Create a storage integration using an Azure AD service principal and Private Link.. Using Azure Private Link with a Snowflake storage integration is the recommended method for secure, private data movement. This architecture ensures that traffic between Azure and Snowflake stays on the private network, bypassing the public internet and meeting strict compliance requirements for enterprise security. This question highlights the importance of cloud networking fundamentals in the context of data engineering and secure cloud-to-cloud integrations.

Answer

Configure an Azure Service Bus to relay the data to Snowflake.

Answer

Use a public URL for the stage but enable IP whitelisting.

Answer

Use an external stage with a SAS token that has a long expiration time.

Question 12

A data engineer wants to load data from an S3 bucket and perform a transformation during the load. Which method is the most appropriate for this task?

Accepted Answer

Use a COPY INTO command with a subquery that includes transformations.. Performing transformations during the load process using a COPY INTO command is highly efficient. By selecting, casting, or filtering data as it moves from the stage into the target table, the engineer avoids the need for a separate staging table and subsequent transformation job. This reduces compute costs and latency, demonstrating a deep understanding of Snowflake's ability to combine ingestion and transformation (ELT) into a single, high-performance operation.

Answer

Load the raw data into a temporary table, then run a task to transform it.

Answer

Create a stream on the S3 bucket to trigger a transformation procedure.

Answer

Use an external function to transform the data before it reaches the stage.

Question 13

Which of the following describes the purpose of a Snowflake storage integration object?

Accepted Answer

To provide a secure way to access cloud storage without hardcoding credentials.. A storage integration is a secure object that stores the authentication credentials (like IAM roles) required for Snowflake to access cloud storage. It eliminates the need to include secret keys or passwords in the stage definition, which follows security best practices. By centralizing credential management, it simplifies administration and enhances security, ensuring that sensitive access keys are never exposed in SQL code, which is critical for enterprise data governance.

Answer

To cache frequently accessed data to improve query performance.

Answer

To create a physical partition of data within the cloud storage bucket.

Answer

To convert data into a proprietary Snowflake format for faster loading.

Question 14

A data engineer is tasked with migrating small, frequent batches of data into Snowflake. Which feature is most appropriate to keep costs low while ensuring the data is processed continuously?

Accepted Answer

Using Snowpipe for serverless continuous ingestion.. Snowpipe is highly efficient for continuous ingestion because it only charges for the actual compute used during the load. For small, frequent batches, it avoids the cost of keeping a warehouse running 24/7. This cost-effectiveness makes it the ideal choice for real-time ingestion patterns, ensuring that the organization pays only for the resources consumed during data movement, rather than paying for idle compute cycles in a traditional, scheduled warehouse setup.

Answer

A virtual warehouse running 24/7 with a scheduled task.

Answer

Triggering a stored procedure every minute to check the storage stage.

Answer

Using a large multi-cluster warehouse to process the batches in parallel.

Question 15

What is the primary benefit of using a file format object in Snowflake when dealing with multiple stages and load jobs?

Accepted Answer

It centralizes file configuration to ensure consistency across multiple load jobs.. File format objects provide a centralized, reusable definition of file settings (like delimiter, compression, or error handling). By referencing a single object in multiple COPY commands, an engineer ensures consistency across the entire data pipeline. This eliminates drift and makes it easy to update the configuration for all load jobs at once, reducing maintenance and preventing errors associated with manually defining parameters repeatedly across many different SQL statements.

Answer

It automatically compresses data before it is uploaded to the cloud.

Answer

It encrypts the data at rest in the stage before loading.

Answer

It stores the credentials for the external stages for easier access.