DEA-C01 Data Ingestion and Transformation Practice Question

A data engineer is using AWS Glue DataBrew to profile a dataset stored in Amazon S3. The profile shows that a column named country contains values such as 'US', 'usa', 'United States', and 'U.S.A.' The engineer needs to standardize these values to a single canonical form before loading the data into Amazon Redshift. Which DataBrew transformation should the engineer apply?

⚠ Common exam trap

The trap here is treating case normalization or punctuation cleanup as sufficient standardization when the column contains entirely different spellings of the same entity.

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 the 'Replace value or pattern' transform to replace each variant with 'United States'.

Standardizing a column with several representations of the same entity requires explicit value mapping, which the 'Replace value or pattern' transform provides. Case conversion, punctuation removal, and duplicate flagging each address only part of the problem or change nothing in the stored values. The replace transform is the only option that yields one canonical value for all variants.

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

✗
Use the 'Remove special characters' transform to strip punctuation.
Why it's wrong here
Stripping punctuation would turn 'U.S.A.' into 'USA', but 'US', 'usa', and 'United States' would remain different values. The column would still fail standardization. Removing characters addresses only one class of variation and does not map distinct representations to a shared canonical value, so it cannot meet the requirement alone.
✓
Use the 'Replace value or pattern' transform to replace each variant with 'United States'.
Why this is correct
The 'Replace value or pattern' transform in DataBrew lets the engineer map each observed variant to a canonical value, handling 'US', 'usa', 'United States', and 'U.S.A.' in one recipe step. It matches the profiling output directly and produces a deterministic result, which is exactly what standardization requires before loading into Redshift.
✗
Use the 'Change case' transform to convert all values to uppercase.
Why it's wrong here
Changing case would fix 'usa' versus 'US' but leaves 'United States' and 'U.S.A.' distinct from 'US'. The column would still contain multiple representations of the same country, so the data would not be standardized. Case normalization is a partial fix that does not satisfy the requirement of a single canonical form.
✗
Use the 'Flag duplicate values' transform to identify repeated entries.
Why it's wrong here
Flagging duplicates marks rows but does not modify the underlying values. The column would still contain 'US', 'usa', 'United States', and 'U.S.A.' as separate strings. This transform is useful for data quality review, but it does not standardize values for loading, so it does not satisfy the stated goal.

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

DEA-C01 Data Ingestion and Transformation Practice Question

Why each option matters

Option-by-option breakdown

AWS S3 Storage Class Comparison

Related to this question