Querying Transaction Dates in Centrally Stored S3 CSV Objects with Athena CTAS and No Data Movement

MLA-C01 By islever Editorial Last updated

Ingest and store data.

Short answer

A Athena queries the central S3 CSV objects in place with SQL, and a CTAS statement materializes the selection with no data movement or extra bucket.

Question

An ML engineer needs to process thousands of existing CSV objects and new CSV objects that are uploaded. The CSV objects are stored in a central Amazon S3 bucket and have the same number of columns. One of the columns is a transaction date. The ML engineer must query the data based on the transaction date. Which solution will meet these requirements with the LEAST operational overhead?
  1. Use an Amazon Athena CREATE TABLE AS SELECT (CTAS) statement to create a table based on the transaction date from data in the central S3 bucket. Query the objects from the table. Correct Answer
  2. Create a new S3 bucket for processed data. Set up S3 replication from the central S3 bucket to the new S3 bucket. Use S3 Object Lambda to query the objects based on transaction date.
  3. Create a new S3 bucket for processed data. Use AWS Glue for Apache Spark to create a job to query the CSV objects based on transaction date. Configure the job to store the results in the new S3 bucket. Query the objects from the new S3 bucket.
  4. Create a new S3 bucket for processed data. Use Amazon Data Firehose to transfer the data from the central S3 bucket to the new S3 bucket. Configure Firehose to run an AWS Lambda function to query the data based on transaction date.

Key Takeaways

Amazon Athena queries data in place in S3 with standard SQL, so a CTAS statement materializes a table selected by transaction date directly from the central bucket with no data movement, no second bucket, and no pipeline to operate.

Thousands of existing and newly uploaded CSV objects sit in a central S3 bucket with the same column structure, including a transaction date column, and the engineer must query the data by transaction date with the least operational overhead. The data stays in the central bucket and new objects keep arriving.

Common mistake: Setting up a second S3 bucket with replication, or a Spark or Firehose pipeline to copy data before querying. Each of those adds buckets, jobs, and operational surface area, and Firehose in particular cannot consume from S3 at all as a source.

Expert Analysis

Why the Answer Is Correct

The requirement is to query centrally stored S3 CSV objects by transaction date with the least operational overhead, and Athena queries data directly where it already lives in S3 using standard SQL, with no data movement or transformation required. A CREATE TABLE AS SELECT statement can select the records filtered by transaction date from the central bucket and materialize the result, so new uploads keep flowing into the same source location without any pipeline. The vote was unanimous at 100 for A. motk123 noted that Athena queries S3 data with SQL without movement or transformation and that CTAS stores the selected results in S3, and feelgoodfactor called it the simplest and most efficient path with minimal operational effort.

Why the Other Options Are Wrong

Creating a new bucket with S3 replication and using S3 Object Lambda (B) requires standing up replication and a Lambda-backed access point just to filter by date; S3 Object Lambda is built for on-the-fly object transformation rather than efficient querying, and ninomfr64 judged the approach cumbersome. Creating a new bucket and using AWS Glue for Spark to query and store results (C) does work technically, as ninomfr64 conceded that Spark SQL can query files, but it means provisioning and operating a Spark job and an unnecessary second bucket where Athena needs neither. Using Data Firehose to transfer data to a new bucket and trigger Lambda to query it (D) fails on a hard constraint, because as ninomfr64 pointed out Firehose cannot consume from S3 directly, so it cannot serve as the transfer path from the central bucket.

Community Comment Notes

The community was unanimous at 100 for A, with full agreement in the comments. ninomfr64 delivered the sharpest elimination, accepting that options B and C could technically work but calling them cumbersome and more work than Athena, and identifying Firehose's inability to read from S3 as the disqualifier for D. motk123 systematically dismissed the alternatives, noting that S3 Object Lambda is for on-the-fly transformation rather than querying, which is the same distinction that makes Athena the natural fit.

Official Reference

https://docs.aws.amazon.com/athena/latest/ug/ctas.html

This link opens the official vendor documentation in a new tab.

Community Votes

A
100%

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

Discussion 4 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 4

  1. motk123 👍 2 Answered A
    Athena allows direct querying of data stored in Amazon S3 using SQL without requiring data movement or transformation. CTAS (CREATE TABLE AS SELECT): Creates a new table based on a filtered or transformed dataset, such as transaction dates, and stores the results in S3. Why Not the Other Options? B. S3 Object Lambda is designed for on-the-fly data transformation, not querying data efficiently. Adding replication increases complexity without addressing the querying requirement directly. C. Glue is suited for complex ETL workflows, but it introduces significant operational overhead for a task that Athena can handle more easily. D. Firehose is designed for streaming data, not processing large existing datasets.
  2. ninomfr64 👍 1 Answered A
    A. Yes, Athena is the right service to query data in S3. B. No, maybe this might also work, but it is quite cumbersome C. No, SparkSQL can be used to query files on data, but it is more work than Athena and creating a new S3 bucket is not needed D. No, Data Firehose cannot consume from S3 directly
  3. feelgoodfactor 👍 1 Answered A
    Using Amazon Athena with a CREATE TABLE AS SELECT (CTAS) statement is the simplest and most efficient way to query the CSV objects based on the transaction date, while requiring minimal operational effort.
  4. GiorgioGss 👍 2 Answered A
    Base usage of CTAS

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.

Choosing LightGBM in SageMaker for Fraud Detection with Class Imbalance and Correlated Features

S-grade

Processing Mixed CSV JSON XLSX Parquet Files in One S3 Folder with DataBrew and Outputting Apache Parquet

S-grade

Rebuilding the Model Monitor Baseline After a Model Update Causes Data Quality Violations

S-grade

Orchestrating SageMaker ML Workflows as DAGs in Studio with ML Lineage Tracking for Governance

S-grade

Practice All MLA-C01 Questions

Access 115 questions with complete answers and detailed explanations.

View Full MLA-C01 Practice Test →

← Back to MLA-C01 Study Guide