DEA-C01 Data Store Management Practice Question

A company is designing a data lake on Amazon S3. Which TWO strategies improve query performance for Amazon Athena?

⚠ Common exam trap

Test-takers frequently confuse data management features (like versioning or encryption) with performance optimizations, or assume that simpler formats like CSV are sufficient for analytics, ignoring the significant performance benefits of partitioning and columnar storage.

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

✓

Partition the data by frequently queried columns such as date or region.

Option C is correct because partitioning the S3 data by frequently filtered columns such as date or region lets Athena prune partitions and scan only the relevant prefixes, drastically reducing the amount of data read and thus improving query speed and lowering cost. Option D is correct because columnar formats like Parquet or ORC allow Athena to read only the columns referenced in the query and benefit from compression and predicate pushdown, minimizing I/O compared to row-based formats. Options A and B do not improve query performance: S3 Versioning only retains object versions for recovery, and SSE-KMS only encrypts data at rest, adding no scan optimization (and KMS calls can even add latency). Option E is incorrect because CSV is a row-based, uncompressed text format that forces Athena to scan entire rows and cannot skip unneeded columns, making queries slower and more expensive than Parquet/ORC.

Answer analysis

Option-by-option breakdown

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

✗
Enable S3 Versioning on the bucket.
Why it's wrong here
S3 Versioning retains prior object versions but leaves the current object's layout unchanged, so Athena still scans the same bytes. It tempts because versioning protects against accidental overwrites and deletions, which is the correct control when durability and recovery, not query latency, are required.
✗
Use server-side encryption with AWS KMS (SSE-KMS).
Why it's wrong here
SSE-KMS encrypts objects at rest but does not change how Athena reads them; it adds KMS decrypt calls that can throttle large scans. It tempts because encryption is mandatory for sensitive data, and it is the right control when compliance, not query speed, is the requirement.
✓
Partition the data by frequently queried columns such as date or region.
Why this is correct
Partitioning by frequently filtered columns lets Athena prune entire S3 prefixes using partition metadata, so it scans only relevant data rather than the whole table. This directly cuts bytes scanned and query runtime for date- or region-filtered workloads.
✓
Use columnar file formats like Parquet or ORC.
Why this is correct
Parquet and ORC store data column-wise, so Athena reads only the columns referenced in a query instead of every field in each row. Combined with compression and predicate pushdown, this sharply reduces bytes scanned versus row-based CSV.
✗
Store data in CSV format with header rows.
Why it's wrong here
CSV forces Athena to scan and parse every column as text, with no columnar pruning or compression, so queries read far more bytes than needed. It tempts because CSV is the simplest format for exporting and hand-editing small datasets, where scan cost is irrelevant.

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