DEA-C01 Data Ingestion and Transformation Practice Question
A data pipeline ingests CSV files from an S3 bucket into a Redshift table using the COPY command. Recently, files with inconsistent column delimiters (some use pipes, others use commas) have been arriving. The pipeline must handle both delimiters without manual intervention. What is the MOST efficient solution?
⚠ Common exam trap
Watch out — candidates often assume AWS Glue or Athena are suitable for dynamic delimiter detection, but they lack the ability to directly and efficiently execute Redshift COPY commands with per-file delimiter customization without significant additional orchestration.
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
✓
Create an AWS Lambda function triggered by S3 events that reads the first line of each file, detects the delimiter, and runs the COPY command with the appropriate DELIMITER option.
It provides an automated, event-driven solution that dynamically detects the delimiter of each incoming CSV file and executes the COPY command with the appropriate DELIMITER option. This approach eliminates manual intervention and leverages AWS Lambda's ability to process S3 events in near real-time, making it the most efficient for handling inconsistent delimiters.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Configure the COPY command with a fixed delimiter (e.g., comma) and manually convert files with pipes before ingestion.
Why it's wrong here
Manual conversion is not efficient and does not scale.
- ✓
Create an AWS Lambda function triggered by S3 events that reads the first line of each file, detects the delimiter, and runs the COPY command with the appropriate DELIMITER option.
Why this is correct
Lambda provides a lightweight, event-driven solution to dynamically detect and handle delimiters.
- ✗
Use AWS Glue to crawl the S3 bucket and automatically detect the schema and delimiter before writing to Redshift.
Why it's wrong here
Running an AWS Glue crawler introduces significant latency and computational overhead compared to a direct Redshift COPY command. This approach fails because the crawler does not dynamically reconfigure the ingestion logic for individual files within a single batch, whereas the correct solution uses the 'auto' delimiter feature in the COPY command. Using Glue is appropriate when you need to build a Data Catalog or discover schema changes across massive datasets where manual metadata management is unfeasible.
- ✗
Use Amazon Athena to query the files with the OpenCSVSerDe, which automatically detects delimiters, and then write the results to Redshift.
Why it's wrong here
Athena can handle delimiters but adds latency and cost; the COPY command is more direct.
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 |
Go deeper
Related to this question
About these practice questions
One of 1,711 original DEA-C01 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DEA-C01 practice question is part of Courseiva's free Amazon Web Services 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-C01 exam.