DEA-C02 Data Movement Practice Test 5 — 15 Questions

Question 1

A data engineer is tasked with migrating a 10TB historical dataset from an on-premise HDFS cluster to Snowflake. The data is currently stored in compressed CSV files. What is the most efficient strategy to ensure optimal performance during the initial bulk load into a Snowflake table?

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

Accepted Answer

Split the CSV files into sizes of 100-250 MB and use the COPY INTO command with a Large or X-Large warehouse.. For massive bulk loads, Snowflake recommends leveraging the COPY INTO command with a properly sized virtual warehouse to maximize parallel processing. Pre-splitting files into the 100-250MB range allows multiple threads across the warehouse nodes to ingest data concurrently. This approach avoids the overhead of serverless compute and provides more control over the ingestion window and resource utilization compared to continuous loading methods.

Answer

Upload the files to an internal stage and use Snowpipe with auto-ingest enabled to process the backlog.

Answer

Execute a single COPY INTO command targeting the entire directory using an X-Small warehouse to minimize credit consumption.

Answer

Use the Snowflake Web Interface (Classic UI) to upload the files directly into the target table in 50MB chunks.

Question 2

An organization is unloading data from a Snowflake table to an external S3 bucket for use in a machine learning pipeline. Which TWO practices will optimize the performance and manageability of the exported files? (Select TWO)

Accepted Answer

Use the PARTITION BY clause in the COPY INTO <location> statement to organize files into subfolders.. Optimizing data unloading requires balancing file size and organizational structure. Using the PARTITION BY clause ensures that the exported files are organized into a logical folder hierarchy, which facilitates faster data discovery for downstream tools. Additionally, controlling the MAX_FILE_SIZE ensures that the files are not too large for the consuming application to process efficiently in memory while maintaining parallelism.

Answer

Set the SINGLE parameter to TRUE to ensure all data is consolidated into one large file.

Answer

Disable compression using the COMPRESSION = NONE parameter to reduce the CPU load on the warehouse.

Answer

Always use the OVERWRITE = TRUE parameter to prevent the command from failing if files already exist.

Question 3

A company requires continuous ingestion of JSON logs from an S3 bucket into a Snowflake table with minimal latency. They decide to use Snowpipe with auto-ingest. How does Snowflake determine which new files need to be processed once the pipe is created?

Accepted Answer

It relies on event notifications from S3 sent to a Snowflake-managed SQS queue to trigger the pipe.. Snowpipe with auto-ingest relies on cloud-native messaging services to notify Snowflake of new data. When a file is uploaded to S3, an S3 Event Notification is triggered, which sends a message to a Snowflake-managed SQS queue. Snowpipe constantly monitors this queue and triggers the ingestion process as soon as it receives a notification, ensuring near real-time data movement without manual intervention.

Answer

Snowflake performs a metadata scan of the S3 bucket every 60 seconds to identify files with new timestamps.

Answer

The Snowpipe object uses the LIST command internally to compare the stage contents against the load history table.

Answer

The data engineer must execute the ALTER PIPE... REFRESH command every time new data is uploaded to the stage.

Question 4

A Data Engineer is using the COPY INTO <table_name> command to load Parquet files. The source files contain new columns that do not yet exist in the target Snowflake table. Which TWO features or settings should be used to handle this automatically? (Select TWO)

Accepted Answer

Set the table property ENABLE_SCHEMA_EVOLUTION = TRUE.. Snowflake provides schema evolution capabilities to simplify the ingestion of evolving datasets. By enabling the ENABLE_SCHEMA_EVOLUTION property on the table, Snowflake allows the COPY command to modify the table structure. When combined with MATCH_BY_COLUMN_NAME, Snowflake maps the Parquet fields to table columns and automatically adds any missing columns found in the source files to the target table.

Answer

Use the STRIP_OUTER_ARRAY = TRUE file format option to flatten the Parquet data.

Answer

Set ON_ERROR = 'CONTINUE' to ensure the command doesn't fail when it sees new columns.

Answer

Apply a UDF in the COPY statement to dynamically cast the Parquet schema to the table.

Question 5

During a data loading process using the COPY command, the data engineer notices that the same files are being processed multiple times, leading to duplicate records. Which Snowflake feature is likely being bypassed or misconfigured?

Accepted Answer

The Load History metadata maintained by the COPY command.. Snowflake's COPY command and Snowpipe both utilize 'load history' metadata to ensure that each unique file is only loaded once into a specific table. This history is maintained for 64 days. If duplicates are appearing, it is often because the files were modified (changing their checksum) or the engineer is using a method that doesn't check the history, such as using a FORCE=TRUE parameter.

Answer

The Change Data Capture (CDC) mechanism in the Snowflake Stream.

Answer

The transactional integrity of the Snowflake Virtual Warehouse.

Answer

The automatic deduplication feature of the VARIANT data type.

Question 6

A data engineer needs to move data from a Snowflake table in the 'US-WEST-2' region to another Snowflake table in the 'EU-CENTRAL-1' region. What is the most resilient and automated way to keep these tables synchronized?

Accepted Answer

Use the Snowflake Database Replication feature to sync the database containing the table to the target region.. Snowflake's Database Replication and Failover features are designed specifically for cross-region data movement. By setting up a secondary database in the target region and enabling replication, Snowflake handles the secure transfer of data and metadata automatically. This is more resilient than manual export/import processes and leverages Snowflake's global backbone for data transfer.

Answer

Unload the data to an S3 bucket in US-WEST-2 and use Snowpipe to load it into EU-CENTRAL-1.

Answer

Create an External Function that calls a Python script to copy data via the Snowflake Connector.

Answer

Set up a Secure Data Share between the two regions and use a CREATE TABLE AS SELECT statement.

Question 7

A data engineer is loading a large number of small JSON files into a Snowflake table using the COPY command. The process is taking much longer than expected. Which action is most likely to resolve the performance bottleneck?

Accepted Answer

Aggregating the small JSON files into fewer, larger files before initiating the COPY command.. Small files create significant metadata overhead for Snowflake, as every file requires a separate request and tracking entry. Consolidating small files into larger batches (100MB-250MB) before loading significantly improves throughput. This reduces the number of I/O operations and allows the virtual warehouse to spend more time processing data rather than managing file-level metadata.

Answer

Increasing the size of the virtual warehouse from Medium to 2X-Large.

Answer

Changing the target table's data retention period to 0 days during the load.

Answer

Using the 'STRIP_OUTER_ARRAY = TRUE' file format option in the COPY command.

Question 8

What is the primary purpose of the 'VALIDATION_MODE' parameter in the COPY INTO <table_name> statement?

Accepted Answer

To check the files for errors and return the results without loading the data into the table.. The VALIDATION_MODE parameter allows data engineers to test their COPY commands without actually committing any data to the table. It parses the files and identifies errors, which is a critical step in data movement pipelines to ensure that file formats and mappings are correct before performing a potentially expensive or large-scale ingestion.

Answer

To automatically correct data type mismatches during the load process.

Answer

To verify that the person executing the command has the correct IAM permissions on the S3 bucket.

Answer

To compare the data in the stage with the data already in the table to find duplicates.

Question 9

Which Snowflake feature should be used to move data from a private cloud network to Snowflake without traversing the public internet?

Accepted Answer

AWS PrivateLink or Azure Private Link.. For enterprises with strict security requirements, using AWS PrivateLink or Azure Private Link allows for a secure, private connection between the cloud network and Snowflake. This is vital for data movement in highly regulated industries. By eliminating the need for public internet exposure, organizations minimize their attack surface and comply with networking policies, ensuring that sensitive data remains within a controlled, private infrastructure environment throughout the entire transit process.

Answer

SnowSQL with VPN tunneling.

Answer

Snowflake Data Exchange.

Answer

Snowpipe with an external stage.

Question 10

When using the COPY INTO command to load data from an S3 bucket, which of the following best describes how Snowflake handles file partitioning?

Accepted Answer

Snowflake automatically parallelizes the load by distributing files across the nodes of the virtual warehouse.. Snowflake can load files in parallel if they are partitioned or if multiple files are present. By default, the COPY INTO command leverages the compute power of the warehouse to process multiple files concurrently. This parallelization is a key driver of Snowflake's performance in data movement, allowing massive datasets to be ingested in a fraction of the time required by traditional serial loading methods.

Answer

Snowflake only processes one file at a time, regardless of the warehouse size.

Answer

The files must be manually split by the engineer before the COPY INTO command is executed.

Answer

Partitioning is only supported when loading data from internal stages.

Question 11

When unloading data from Snowflake to an external stage, which TWO of the following are supported file formats?

Accepted Answer

CSV. Unloading data is just as important as loading. Snowflake supports CSV, JSON, and Parquet for unloading, providing flexibility for downstream systems. Understanding which formats are natively supported allows engineers to choose the right format for compatibility with external tools (like BI platforms or data lakes) without needing additional transformation steps, making the data movement process more efficient and standardized.

Answer

XLSX

Answer

SQL

Answer

PDF

Question 12

A company requires that all data moved into Snowflake be encrypted at rest within the target table. How does Snowflake handle this requirement?

Accepted Answer

Snowflake automatically encrypts all data at rest using AES-256.. Snowflake provides transparent, end-to-end encryption for all data at rest by default. This feature is fundamental to the platform's security architecture. Because it is managed automatically by Snowflake, data engineers do not need to configure encryption keys manually or change their data movement workflows. This provides peace of mind for security-conscious organizations and ensures compliance with industry standards without adding complexity to the ingestion pipeline.

Answer

The data must be encrypted by the user before being uploaded to the stage.

Answer

The engineer must use the ENCRYPT_DATA parameter in the COPY INTO command.

Answer

Encryption at rest is only available for Snowflake Enterprise Edition and higher.

Question 13

A data engineer needs to ingest files from an S3 bucket into Snowflake as soon as they are uploaded. The files arrive every few minutes and are generally smaller than 50MB. Which approach provides the most cost-effective and low-latency solution for this requirement?

Accepted Answer

Configuring Snowpipe with auto-ingest enabled using SQS notifications.. Snowflake supports several methods for loading data, and choosing the right one depends on file size, frequency, and latency requirements. Snowpipe is specifically designed for continuous, automated loading of small files. This approach reduces manual overhead and ensures that data is available for analysis shortly after it is generated at the source, which is a core requirement for modern data engineering pipelines.

Answer

Using a Task to run a COPY INTO statement every 5 minutes on a Small warehouse.

Answer

Executing a COPY INTO command via a Python script triggered by a cron job.

Answer

Creating an External Table and using the REFRESH command every minute.

Question 14

During a bulk load from an S3 stage, a data engineer notices that the data is not being loaded despite the COPY INTO command executing successfully. What is the most likely cause?

Accepted Answer

The files in the stage do not match the 'PATTERN' specified in the COPY command.. Snowflake's COPY INTO command includes a 'VALIDATION_MODE' option, but if the command is executed normally and the files are empty or mis-filtered by the 'FILE_FORMAT' settings, no rows will be ingested. The 'LOAD_HISTORY' information can confirm if files were processed. Often, the issue stems from an incorrect 'PATTERN' argument or a misalignment between the file structure and the defined format, resulting in zero rows being accepted into the target table.

Answer

The warehouse is too small to process the files.

Answer

The user lacks the 'INSERT' privilege on the target table.

Answer

The source S3 bucket was deleted before the load started.

Question 15

A data engineer is loading data from an external stage into a Snowflake table using a COPY INTO command. The source files are compressed with gzip and contain a header row. The engineer wants to skip the first row of each file and load the remaining data. Which COPY INTO option should be used?

Accepted Answer

SKIP_HEADER = 1. SKIP_HEADER = 1 is the correct COPY INTO option to skip the first row of each file during loading. It tells Snowflake to ignore the specified number of lines at the start of the file, which is typically the header. Other options control different aspects of file parsing and error handling.

Answer

FIELD_OPTIONALLY_ENCLOSED_BY = '"'

Answer

SKIP_FILE = '1'

Answer

HEADER = TRUE