DEA-C02 Data Movement Practice Question
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?
⚠ Common exam trap
Candidates often suggest creating multiple pipes for different file structures. This is an anti-pattern that increases management overhead and complexity unnecessarily.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
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 analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Define multiple pipes pointing to the same stage with different pattern matching.
Why it's wrong here
Multiple pipes increase management overhead and resource usage for the same ingestion target. It contradicts the goal of simplifying the ingestion architecture when a single variant column can store all varied structures efficiently, preventing unnecessary administrative complexity in managing dozens of individual pipe objects.
- ✗
Use a schema-on-read approach by configuring the pipe to cast columns explicitly.
Why it's wrong here
Explicit casting inside a pipe definition will fail if the incoming file schema does not match the hardcoded transformation logic. This creates brittle pipelines that break whenever upstream source data modifies its structure, leading to failed loads and requiring constant maintenance of the pipe definitions.
- ✓
Load the files into a table with a VARIANT column to store the semi-structured data.
Why this is correct
Loading into a VARIANT column provides the most flexibility for varied schemas. Snowflake automatically parses JSON, Avro, or Parquet structures into the variant type. This allows the ingestion process to proceed regardless of column additions or removals, deferring the structural parsing to the transformation layer.
- ✗
Configure the pipe to use an external table instead of loading data into an internal table.
Why it's wrong here
External tables are optimized for querying data in place rather than high-performance ingestion. While they handle schema variance well, they do not provide the performance benefits of native Snowflake storage, such as clustering, caching, and time travel, which are required for standard data engineering workloads.
Quick reference
AWS S3 Storage Class Comparison
| Storage Class | Min Duration | Retrieval | Use Case |
|---|---|---|---|
| S3 Standard | None | Immediate | Frequently accessed data |
| S3 Standard-IA | 30 days | Immediate | Infrequent access, rapid retrieval |
| S3 One Zone-IA | 30 days | Immediate | Non-critical infrequent data |
| S3 Intelligent-Tiering | None | Immediate–hours | Unknown or changing access patterns |
| S3 Glacier Instant | 90 days | Milliseconds | Archive with instant retrieval |
| S3 Glacier Flexible | 90 days | Minutes–hours | Archive, flexible retrieval |
| S3 Glacier Deep Archive | 180 days | Hours | Long-term compliance archive |
About these practice questions
Courseiva writes every DEA-C02 question from scratch — 229 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Snowflake exam blueprint
This DEA-C02 practice question is part of Courseiva's free Snowflake certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DEA-C02 exam.