DEA-C02 Data Movement Practice Test 6 — 15 Questions

Question 1

A data engineer is using Snowpipe to load data from an external stage. The pipe is configured with AUTO_INGEST = TRUE. Which two statements are true regarding the behavior of Snowpipe in this configuration? (Choose two.)

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

Accepted Answer

Snowpipe relies on cloud storage event notifications to trigger the load process.. Snowpipe with AUTO_INGEST uses cloud storage event notifications to trigger loads and maintains a load history to avoid duplicates. It does not poll the stage, does not auto-create integrations, and is not compatible with internal stages.

Answer

Snowpipe periodically polls the external stage for new files based on a defined schedule.

Answer

AUTO_INGEST = TRUE requires the stage to be an internal stage.

Answer

Snowpipe automatically creates a notification integration if one does not exist.

Question 2

A data engineer runs a COPY INTO statement that loads 120 files from an external S3 stage into a target table. The LOAD_UNCERTAIN_FILES option was not specified, and 42 files were already loaded by an earlier run that completed successfully. The engineer expects all 120 files to be reprocessed because the target table was truncated before this run. What will Snowflake actually do, and why?

Accepted Answer

It will skip the 42 previously loaded files and load only the remaining 78, because load metadata is retained independently of table data.. COPY INTO maintains a per-table record of which staged files have already been loaded, and this record survives operations that change or remove table rows. With LOAD_UNCERTAIN_FILES left at its default, only files that have not been successfully loaded into the target are read, so a truncated table does not force reprocessing of files already recorded as loaded.

Answer

It will load all 120 files and produce duplicate rows because the table was emptied after the first load.

Answer

It will load all 120 files because TRUNCATE TABLE removes the table's load metadata along with the rows.

Answer

It will load all 120 files but raise an error for each previously loaded file, marking those rows as rejected.

Question 3

A data engineer is unloading a large fact table to an external stage pointing at an Amazon S3 bucket. The downstream consumer requires many small files for parallel processing, and each file must be no larger than 64 MB. Which COPY INTO location options should the engineer use?

Accepted Answer

MAX_FILE_SIZE = 67108864 and SINGLE = FALSE. MAX_FILE_SIZE sets an upper bound on each unloaded file and takes a byte value, so 64 MB must be written as 67108864. SINGLE = FALSE lets the unload split output across multiple files. Used together, they produce many files each capped at the requested size, which is what the downstream parallel consumer requires.

Answer

MAX_FILE_SIZE = 64 and SINGLE = FALSE

Answer

MAX_FILE_SIZE = 67108864 and SINGLE = TRUE

Answer

FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP) and SINGLE = FALSE

Question 4

A data engineer is configuring a Snowpipe to automatically load Parquet files from an external stage. The files are partitioned by date in the path (e.g., dt=2023-10-01/). The engineer wants to ensure that Snowpipe loads only new files and avoids reprocessing old ones, even if files are added to existing partition paths. The pipe definition includes a PATTERN option. Which approach best ensures that only new files are ingested and that previously loaded files are not reprocessed?

Accepted Answer

Ensure the pipe uses the load history to track processed files; Snowpipe automatically skips files that have already been loaded, even if they appear again.. Snowpipe's built-in load history tracks which files have been loaded for each pipe. When new files are staged, Snowpipe compares against this history and only loads files not previously processed. This ensures that previously loaded files are not reprocessed, even if they are in existing partitions. The other options either rely on non-existent features or require manual intervention that does not guarantee the requirement.

Answer

Use the PATTERN option to match only files with a specific prefix or regex that includes the current date, and update the pipe definition daily.

Answer

Rely on Snowpipe's default behavior, which automatically tracks file modification times and skips files older than the pipe creation time.

Answer

Configure the pipe with a MODIFIED_AFTER parameter set to a timestamp that is updated after each load.

Question 5

An analytics team wants to let external partners query a curated set of rows from a Snowflake table through a secure share. The partners use their own Snowflake accounts and must not be able to see any rows outside the curated set. Which approach should the data engineer use?

Accepted Answer

Create a secure view that filters the base table, grant the share access to the view, and add the view to the share.. Secure views are the supported way to expose a constrained subset of data in a share. Because the view definition is hidden and the base table is not shared, consumers can query only the curated rows. This satisfies both the access requirement and the restriction that partners must not see the full table.

Answer

Grant the share SELECT on the base table and rely on a row access policy to filter rows for the partners.

Answer

Create a materialized view of the curated rows and add it to the share for the partners to query.

Answer

Add the base table to the share and instruct partners to query only the approved rows using their own filters.

Question 6

A data engineer needs to unload a large table to an external stage in Parquet format. The table contains a column with sensitive data that must be masked in the unloaded files. The engineer wants to use a secure view that applies a masking policy, and then unload from that view. Which statement accurately describes the behavior when unloading from a secure view?

Accepted Answer

Unloading from a secure view is allowed, and the masking policies defined in the view are applied to the unloaded data.. COPY INTO <location> supports unloading from secure views, and the secure view's definition, including masking and row access policies, is enforced during the unload. This means sensitive columns can be masked in the output files. The other options either deny support, claim masking is bypassed, or impose unnecessary role requirements. The correct behavior is that policies are applied, ensuring data security.

Answer

Unloading from a secure view is allowed, but the masking policies are not applied; the underlying data is unloaded unmasked.

Answer

Unloading from a secure view is not supported; COPY INTO <location> requires a base table or a regular view.

Answer

Unloading from a secure view requires the ACCOUNTADMIN role and the masking policies are ignored.

Question 7

A data engineer is configuring a Snowpipe to continuously load new files from an external stage backed by Google Cloud Storage. The pipeline must ingest files within seconds of their arrival. The engineer notices that files are not being loaded and that no errors appear in the pipe's copy history. The pipe was created with AUTO_INGEST = TRUE, and the notification channel is configured. Which Snowflake feature should the engineer verify to ensure that Snowpipe receives event notifications from GCS?

Accepted Answer

The GCS bucket has a Pub/Sub subscription that forwards messages to Snowflake's notification endpoint.. For Snowpipe auto-ingest from Google Cloud Storage, Snowflake does not poll the bucket. Instead, it relies on Google Cloud Pub/Sub to publish event notifications when new objects arrive. A subscription must be created that pushes these messages to the Snowflake-provided notification endpoint. If that subscription is missing, misconfigured, or lacks permissions, Snowflake never receives the event, and the pipe remains idle without errors. Verifying the Pub/Sub subscription is therefore the correct diagnostic step.

Answer

The pipe's FILE_FORMAT is set to skip header rows and handle compressed files.

Answer

The external stage URL uses the gcs:// scheme and specifies the correct bucket and folder path.

Answer

The storage integration's STORAGE_ALLOWED_LOCATIONS parameter includes the GCS bucket path.

Question 8

A data engineer is configuring continuous ingestion of new event files from an external Amazon S3 stage into a Snowflake table. The files arrive frequently and the engineer wants Snowflake to load them automatically without building an external orchestrator. The stage already has a storage integration attached. Which Snowflake object should the engineer create to accomplish this?

Accepted Answer

A pipe with AUTO_INGEST = TRUE. Continuous, event-driven loading from an external stage is implemented with a pipe configured for auto-ingest. The pipe wraps the COPY INTO statement, and the cloud provider's event notification tells Snowflake when new files appear, so no polling or external scheduler is needed. Streams, tasks, and materialized views solve different problems and cannot react to S3 object creation events.

Answer

A stream on the external stage that captures new file names

Answer

A materialized view defined over the external stage

Answer

A task scheduled with a CRON expression that runs COPY INTO every minute

Question 9

A data engineer needs to unload a large table from Snowflake to an external stage. The unloading process must produce a single compressed file. Which COPY INTO <location> option should be used to ensure the output is a single file?

Accepted Answer

SINGLE = TRUE. To unload data into a single file, the COPY INTO <location> command must include the SINGLE = TRUE option. This forces Snowflake to write all data to one file, which can be necessary when the downstream system cannot handle multiple files. However, using SINGLE = TRUE may reduce performance for large datasets because the unload operation is not parallelized across multiple files.

Answer

PARTITION_BY = <column>

Answer

DETAILED_OUTPUT = TRUE

Answer

MAX_FILE_SIZE = <size>

Question 10

A data engineer needs to continuously ingest JSON event files landing in an Amazon S3 bucket. The ingestion must be automatic, near real-time, and must reuse the same transformation logic already validated in a COPY INTO statement. The team wants the least administrative overhead. Which Snowflake feature should they configure?

Accepted Answer

A Snowpipe configured with COPY INTO from the S3 stage. Snowpipe is the Snowflake service built for continuous, event-driven loading. It runs COPY INTO statements automatically when new files arrive and can embed transformation logic, so the validated COPY logic is reused. Because it uses serverless compute triggered by cloud event notifications, it provides near real-time ingestion with very little operational management, fitting the stated constraints.

Answer

A scheduled task that runs COPY INTO every five minutes against the S3 stage

Answer

An external table defined over the S3 stage with automatic refresh enabled

Answer

A materialized view created over an external table pointing at the S3 stage

Question 11

A data engineer needs to move a large table from an on-premises Oracle database into Snowflake on a recurring nightly basis. The source system allows outbound connections to cloud endpoints but does not permit installing third-party agents on the database host. Which Snowflake-native approach best fits these constraints?

Accepted Answer

Stage the extracted files in cloud storage and use COPY INTO with a storage integration. When agents cannot be installed on a source database, the practical Snowflake-native pattern is to extract to cloud storage and load with COPY INTO. A storage integration keeps credentials out of SQL, and COPY INTO handles parallelism, error handling, and load history. Streaming connectors and external tables assume different source technologies and would not work against a plain Oracle host under these constraints.

Answer

Create an external table directly over the Oracle database

Answer

Mount the Oracle data files as a network share and query them with a directory table

Answer

Use Snowpipe Streaming with the Snowflake Kafka connector pointed at Oracle

Question 12

A data engineer is setting up a Snowpipe to automatically ingest data from an external stage (Google Cloud Storage) into a Snowflake table. The engineer wants to minimize latency and ensure that files are loaded as soon as they are available. Which Snowpipe configuration should be used?

Accepted Answer

Create a pipe with AUTO_INGEST = TRUE and configure a cloud notification integration for Google Cloud Pub/Sub.. For automatic, low-latency ingestion from Google Cloud Storage, Snowpipe must be configured with AUTO_INGEST = TRUE and a notification integration that uses Google Cloud Pub/Sub. This allows Snowflake to receive event notifications when new files arrive, triggering immediate ingestion. Storage integrations alone do not provide event notifications, and polling introduces delay and unnecessary compute.

Answer

Create a pipe with AUTO_INGEST = TRUE and rely on Snowflake's internal polling mechanism to detect new files.

Answer

Create a pipe with AUTO_INGEST = TRUE and configure the stage to use a storage integration with Google Cloud Storage.

Answer

Create a pipe with AUTO_INGEST = FALSE and schedule it to run every minute using a task.

Question 13

An analyst needs to unload a 4 TB table from Snowflake to an external Amazon S3 stage for archival. The team wants maximum write throughput and wants the output organized so downstream tools can read subsets without scanning everything. Which combination of COPY INTO options best achieves this?

Accepted Answer

Set MAX_FILE_SIZE to a moderate value and use a partition-like folder path in the stage prefix. Unload throughput scales with the number of files written in parallel, which is governed by MAX_FILE_SIZE and the warehouse size. Organizing output under a meaningful prefix lets downstream consumers prune by path instead of reading the entire export. Single-file output, overwrite semantics, and uncompressed CSV do not advance either goal and in some cases actively harm performance or safety.

Answer

Add a FILE_FORMAT option of TYPE = CSV with COMPRESSION = NONE

Answer

Use OVERWRITE = TRUE and leave MAX_FILE_SIZE at its default

Answer

Set SINGLE = TRUE so the output is one large file

Question 14

A data engineer is using a Snowpipe to load data from an external stage. The pipe has been running successfully, but the engineer notices that some files are being loaded multiple times, resulting in duplicate records. Which action should be taken to prevent future duplicate loads?

Accepted Answer

Ensure that the pipe's load history is properly maintained and that the cloud notification integration is configured to avoid duplicate event messages.. Duplicate loads in Snowpipe are typically caused by duplicate event notifications or by clearing the load history. Snowflake maintains a 14-day load history for each pipe, which prevents re-loading the same file if it hasn't changed. Ensuring the notification integration is configured correctly to avoid duplicate events, and not manually clearing the load history, are key to preventing duplicates. Monitoring with COPY_HISTORY can help detect issues.

Answer

Set the pipe to use a file format that includes a unique key column and rely on Snowflake's automatic deduplication.

Answer

Recreate the pipe with a new name to reset the load history and start fresh.

Answer

Enable the PURGE = TRUE option in the pipe definition.

Question 15

A data engineer must validate a COPY INTO load from an external stage before promoting it to production. The team wants to confirm which files were loaded, how many rows each contained, and which rows were rejected, without leaving partial or duplicated data in the target table. (Choose two.)

Accepted Answer

Run VALIDATION_MODE = RETURN_ERRORS in a COPY INTO statement. Pre-production validation combines a dry run that surfaces rejected rows without committing data and a post-attempt audit that reports per-file outcomes. VALIDATION_MODE = RETURN_ERRORS provides the dry run, and the COPY_HISTORY table function provides the file-level audit. Options that load partial data, force reloads, or observe changes after insertion either pollute the target or fail to expose the needed diagnostics.

Answer

Set ON_ERROR = CONTINUE and inspect the target table row counts

Answer

Use FORCE = TRUE to reload all files and compare checksums

Answer

Create a stream on the target table and read the change records