Cost-Effective ACID Querying on S3 Data

DEA-C01 By islever Editorial Last updated

Automate data processing by using AWS services.

Short answer

C Amazon Athena provides a serverless, cost-effective way to query S3 data with partitioning and ACID transaction support.

Question

A marketing company uses Amazon S3 to store clickstream data. The company queries the data at the end of each day by using a SQL JOIN clause on S3 objects that are stored in separate buckets. The company creates key performance indicators (KPIs) based on the objects. The company needs a serverless solution that will give users the ability to query data by partitioning the data. The solution must maintain the atomicity, consistency, isolation, and durability (ACID) properties of the data. Which solution will meet these requirements MOST cost-effectively?
  1. Amazon S3 Select
  2. Amazon Redshift Spectrum
  3. Amazon Athena Correct Answer
  4. Amazon EMR

Key Takeaways

The question tests knowledge of AWS query services supporting ACID properties on S3; the common trap is confusing serverless status between Athena and Redshift Spectrum.

Amazon Athena is the serverless solution that enables SQL partitioning and ACID transactions on Amazon S3 data. This guide explains why Athena is the most cost-effective choice compared to Redshift Spectrum or EMR.

Common mistake: Users often choose Amazon Redshift Spectrum because it also queries S3, but they overlook that Athena natively supports ACID transactions for data lake scenarios while being more cost-effective for ad-hoc querying.

Expert Analysis

Why the Answer Is Correct

Amazon Athena is a serverless interactive query service that makes it easy to analyze data in Amazon S3 using standard SQL. It supports partitioning to improve performance and has introduced support for ACID transactions (via Hive transactional tables) which satisfies the requirement for atomicity, consistency, isolation, and durability. Since the company wants a serverless solution that is cost-effective, Athena charges only for the data scanned per query, making it ideal for end-of-day KPI generation without maintaining infrastructure.

Why the Other Options Are Wrong

Amazon S3 Select allows retrieving a subset of data from an object but does not support complex SQL JOINs across multiple buckets or full ACID transactional guarantees. Amazon Redshift Spectrum is serverless for querying S3, but it requires a provisioned Redshift cluster for management and typically incurs higher costs if used primarily for simple ad-hoc queries compared to Athena's pay-per-query model, though both are serverless; however, Athena is generally preferred for pure data lake analytics with ACID support. Amazon EMR is not serverless (unless using EMR Serverless, which is less mature for this specific use case than Athena) and involves managing clusters, violating the serverless requirement.

Community Comment Notes

Community consensus strongly favors Amazon Athena. Users highlight that Athena is serverless and cost-effective because it only charges for queries run. Some users incorrectly claim Redshift Spectrum is not serverless, but technically it is a serverless feature of Redshift; however, Athena is still the better fit for standalone S3 querying with ACID needs. The key takeaway is that Athena provides the necessary ACID capabilities directly on S3 without infrastructure management.

Exam Strategy

Always verify if 'serverless' and 'ACID' are required when analyzing S3 query options. Athena is the go-to for serverless SQL on S3 with modern ACID support.

Frequently Asked Questions

Does Amazon Athena support ACID transactions?

Yes, Athena supports ACID transactions for Hive transactional tables stored in S3, ensuring data integrity.

Why is Redshift Spectrum not the best choice here?

While serverless, Athena is more cost-effective for ad-hoc S3 queries and offers native ACID support without needing a Redshift cluster context.

More DEA-C01 FAQ →

Community Votes

C
82%
B
18%

82% of anonymous learners picked answer C. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 6 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 6

  1. Ja13 👍 5 Answered C
    C. Amazon Athena Here's why Amazon Athena is suitable: Serverless: Amazon Athena is a serverless query service that allows you to run SQL queries directly on data stored in Amazon S3 without the need to manage infrastructure. Partitioning: Athena supports querying data by partitioning, which can significantly improve query performance by limiting the amount of data scanned. ACID Properties: Although Amazon S3 itself does not provide ACID properties, Amazon Athena ensures consistency in query results and durability of the data stored in S3 through its managed query execution. Cost-effective: With Amazon Athena, you only pay for the queries you run and the amount of data scanned, making it a cost-effective choice compared to managing infrastructure or using dedicated services like Amazon Redshift Spectrum or Amazon EMR.
  2. andrologin 👍 2 Answered B
    Athena is cost effective as it only charges for queries run
  3. EJGisME 👍 2 Answered C
    Amazon Redshift Spectrum is not serverless.
  4. HunkyBunky 👍 1
    C - cheapest solution in this case
  5. EJGisME 👍 2 Answered C
    C. Amazon Athena
Show 1 more comment
  1. Bmaster 👍 1
    C is good.

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

Submitted for moderation before publishing. Keep it helpful and respectful.

Amazon Redshift Compound Sort Key Query Optimization

S-grade

Secure Redshift Spectrum Access with VPC Endpoints

S-grade

Athena Federated Query for Multi-Source Iceberg Pipelines

S-grade

Reclaiming Storage in Amazon Redshift Materialized Views

S-grade

Practice All DEA-C01 Questions

Access 100 questions with complete answers and detailed explanations.

View Full DEA-C01 Practice Test →

← Back to DEA-C01 Study Guide