DEA-C01 Data Store Management Practice Question

A data engineer needs to store semi-structured JSON event logs in a data lake on Amazon S3 and query them with Amazon Athena using SQL, including filtering on individual JSON attributes. The team wants to avoid transforming the files before querying. Which approach should the engineer use?

⚠ Common exam trap

The trap here is assuming JSON must be flattened or loaded into a warehouse before SQL can filter individual attributes, when Athena supports complex types that expose nested fields directly.

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

✓

Define an Athena table with a struct or map column over the JSON and query nested fields

Athena queries data in place on S3 using table definitions in the AWS Glue Data Catalog. For semi-structured JSON, defining columns as struct, array, or map with the appropriate JSON SerDe lets SQL access nested attributes directly without preprocessing. Storing as CSV would require flattening, loading into Redshift adds a transformation step, and CloudWatch Logs Insights is scoped to log groups rather than the S3 data lake.

Answer analysis

Option-by-option breakdown

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

✗
Store the logs as CSV and use Athena to parse the columns
Why it's wrong here
CSV is a flat row-based format that cannot represent nested JSON structures without flattening, and the team explicitly wants to avoid transformation. Storing JSON as CSV would require converting the data first, defeating the requirement. Athena can query CSV, but nested attributes and arrays would be lost or require brittle string parsing, so this does not meet the goal of querying raw JSON directly.
✗
Use Amazon CloudWatch Logs Insights to query the JSON logs in place
Why it's wrong here
CloudWatch Logs Insights queries log groups, not arbitrary objects in an S3 data lake, and it does not provide a persistent SQL table over S3 files. While it can parse JSON in log events, it is scoped to CloudWatch Logs storage and lacks the catalog-based, SQL-over-S3 model the team needs. It therefore does not meet the requirement to query the S3 data lake with SQL.
✗
Load the JSON into Amazon Redshift using COPY JSON and query it there
Why it's wrong here
Redshift COPY can ingest JSON into SUPER columns, but this first moves and loads the data into a warehouse, which is a transformation and load step the team wants to avoid. It also introduces cluster cost and management. The requirement is to query the files in the S3 data lake without transforming them, so a separate load into Redshift does not satisfy the stated constraint.
✓
Define an Athena table with a struct or map column over the JSON and query nested fields
Why this is correct
Athena, built on the AWS Glue Data Catalog, supports JSON SerDe and complex types such as struct, array, and map. Defining columns with these types lets SQL reference nested attributes directly, for example using dot notation on a struct field. No preprocessing is needed, which satisfies the requirement to query raw JSON in place while still allowing filters on individual attributes.

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 Store Management Practice Question

Why each option matters

Option-by-option breakdown

AWS S3 Storage Class Comparison

Related to this question