DEA-C01 Data Operations and Support Practice Question
A company uses AWS Glue to run ETL jobs that process data from Amazon S3 and write results to Amazon Redshift. The Glue job uses the JDBC connection to Redshift. Recently, the job has been failing intermittently with the error: 'java.sql.SQLException: [Amazon](500310) Invalid operation: INSERT has more expressions than target columns;' The Glue job writes to a staging table in Redshift before performing a merge into the final table. The staging table schema matches the source data. The error occurs only on some days and affects different columns each time. The data engineer suspects that the source data occasionally contains extra columns due to a schema drift in the upstream data producer. Which approach should the data engineer take to handle this issue robustly?
⚠ Common exam trap
A common mix-up: candidates confuse `dropFields` (which removes specific columns statically) with `resolveChoice` (which handles dynamic schema drift), leading them to choose Option D even though it cannot adapt to varying extra columns across different days.
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
✓
Use a Glue DynamicFrame and apply the resolveChoice method to make the schema consistent.
Glue DynamicFrames can automatically handle schema drift using the `resolveChoice` method, which allows you to specify how to handle columns that appear inconsistently across records (e.g., making them null, casting to a common type, or dropping them). This directly addresses the intermittent error caused by extra columns in the source data without requiring manual schema updates or fragile conditional logic.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Skip any records that have extra columns by adding a conditional check in the Glue script.
Why it's wrong here
Skipping records leads to data loss; the job should handle extra columns gracefully.
- ✓
Use a Glue DynamicFrame and apply the resolveChoice method to make the schema consistent.
Why this is correct
resolveChoice can handle schema drift by casting or dropping columns, making the job resilient.
- ✗
Manually update the Redshift staging table schema whenever the source data changes.
Why it's wrong here
Manual intervention is not robust and does not scale; the job should handle schema drift automatically.
- ✗
Use a Glue DynamicFrame and apply the dropFields method to remove extra columns before writing.
Why it's wrong here
dropFields requires knowing which columns to drop; it does not handle dynamic drift automatically.
Visual reference
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
Courseiva writes every DEA-C01 question from scratch — 1,711 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 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.