ExamPark
Course menuDEA-C01

Exams / DEA-C01 / Study guide / Querying with Athena

Querying with Athena: DEA-C01 study notes and practice questions

What to know about Querying with Athena for the DEA-C01 exam, from the Data Operations and Support domain (22% of the exam guide), then 3 free questions to test it. Part of the DEA-C01 study guide.

Last verified against current AWS documentation ·How we write our questions

What to know for the exam

Athena questions ask how to make queries cheaper and faster, or how to control who runs what.

Cutting cost and time

  • Columnar formats (Parquet, ORC) read only the columns a query selects; switching from CSV or JSON often cuts scanned data by most of it.
  • Compression (Snappy, ZSTD, gzip) reduces bytes scanned.
  • Partitioning and partition projection skip irrelevant data.
  • File sizes of roughly 128 MB or more avoid small-file overhead.
  • SELECT only needed columns; use LIMIT for exploring.
  • Query result reuse returns cached results for repeated queries within a set age.
  • CTAS (CREATE TABLE AS SELECT) converts data to partitioned Parquet in one step; INSERT INTO appends.
  • Provisioned capacity gives dedicated processing for steady, heavy workloads.

Workgroups

  • Separate teams or apps, each with its own query result location, encryption settings and engine version.
  • Data usage limits per query or per workgroup stop runaway scans, with CloudWatch alarms.
  • Workgroup settings can override client settings so users can’t change result location or encryption.
  • Tag-based IAM policies control access per workgroup.

Beyond S3 tables

  • Federated queries use Lambda-based connectors to read DynamoDB, RDS, Redshift, CloudWatch Logs and others.
  • Iceberg tables: MERGE INTO, UPDATE, DELETE, time travel, OPTIMIZE and VACUUM.
  • Athena for Apache Spark runs notebooks for Python processing.
  • Views save logic; prepared statements take parameters.
  • Athena ML can call SageMaker endpoints in SQL.

Common traps

  • Querying raw CSV every day. Convert once to partitioned Parquet with CTAS or a Glue job.
  • SELECT * on wide tables.
  • No per-query limit when an analyst can scan petabytes by mistake.

Try the 3 practice questions

Practice Querying with AthenaTake the free practice exam

Question 1

Medium ·Link to this question (question 1)

A public library system receives a nightly CSV export of patron feedback in Amazon S3. Each file has a header row, and the comments field is enclosed in double quotes because comments often contain commas.

The Athena table was created with the default LazySimpleSerDe. Queries return the header as a data row, and comments that contain commas spill text into the wrong columns. The export format cannot change.

Which change meets these requirements?

Choose one answer.

  1. A. Recreate the table with OpenCSVSerDe, set its quote character to a double quote, and set skip.header.line.count to 1.
  2. B. Keep the table, add a WHERE clause that drops rows matching the header text, and rebuild the comments with string functions.
  3. C. Change the table's storage format to Parquet in its table properties, so that Athena reads the files as columnar data.
  4. D. Change the field delimiter in the table definition to a tab character, so that commas inside comments no longer split the fields.
Show the answer and explanation

Answer: A

  • A. Right: Correct. OpenCSVSerDe understands quoted fields, so commas inside a quoted comment stay in that column. The skip.header.line.count table property tells Athena to skip the header row in each file. Learn more: OpenCSVSerDe for processing CSV (opens in a new tab)
  • B. Wrong: Filtering the header works, but text already split into the wrong columns cannot be rebuilt reliably, because the number of commas varies. Every analyst would also need to repeat the workaround.
  • C. Wrong: The files are still CSV, so declaring them Parquet makes every query fail to read them. Converting the data would need a separate job, which does not fix the table definition.
  • D. Wrong: The files are comma-delimited, so a tab delimiter would read each whole line as a single field. Every column after the first would come back empty.

Explanation

Quoted CSV fields need a SerDe that understands quotes. OpenCSVSerDe keeps commas inside quoted values in the right column, and skip.header.line.count removes the header row, with no change to the export. Query-time workarounds, a wrong format and a different delimiter do not read the files correctly.

Study

Question 2

Medium ·Link to this question (question 2)

A logistics broker's dashboard is based on an Athena view that joins shipments, carriers and invoices across three large tables. Forty analysts open the dashboard many times a day, and each open runs the whole join again, which makes the monthly cost of the dashboard very high.

The dashboard can use data that is refreshed once a night, and the team wants the change to be a single, easily maintained step.

Which change meets these requirements?

Choose one answer.

  1. A. Keep the view, and turn on query result reuse for the workgroup, so that identical dashboard queries reuse cached results.
  2. B. Replace the view with a CTAS query that materializes the joined result into a table, refreshed nightly, and point the dashboard at it.
  3. C. Connect the dashboard to the three source tables directly, and add more Athena workgroups so that the joins run in parallel.
  4. D. Export the join result to a CSV file in S3 by hand each month, and point the dashboard at the file.
Show the answer and explanation

Answer: B

  • A. Wrong: Result reuse helps only identical queries that run within the reuse window, and dashboards with different filters or refresh times see little benefit. The view still recomputes the join for most loads. Learn more: Reuse query results in Athena (opens in a new tab)
  • B. Right: Correct. The join runs once each night and writes its result to a table, so the dashboard scans the smaller materialized table. Analysts see nightly data, which matches the requirement, and the change is one scheduled statement. Learn more: Create a table from query results (CTAS) (opens in a new tab)
  • C. Wrong: Direct queries repeat the same join for every open, and more workgroups do not reduce the data scanned. The option keeps the cost problem the team wants to solve.
  • D. Wrong: A monthly export is too stale for a nightly refresh, and it depends on a manual step. The option does not match the requirement.

Explanation

Repeated joins are cheaper when the result is materialized once and read many times. A nightly CTAS writes the joined result to a table, so the dashboard reads a smaller table instead of joining the source tables on every open. Result reuse, direct queries and manual exports do not remove the repeated join.

Study

Question 3

Medium ·Link to this question (question 3)

A bank receives change files every hour that contain new, updated and deleted customer records. The team stores the customer table as an Apache Iceberg table and queries it with Amazon Athena. Each hourly change must be applied to the table without duplicates, and the team wants to use Athena for the upserts rather than a Spark cluster.

The table already holds 5 years of data, and the team wants to keep the cost of the hourly loads low.

Which action meets these requirements?

Choose one answer.

  1. A. Run an INSERT INTO statement that appends the hourly change file to the table, and let the queries de-duplicate the rows at read time.
  2. B. Load the hourly change file into a staging table, and run a MERGE INTO that matches on customer ID and updates, inserts or deletes rows.
  3. C. Rebuild the whole customer table with a CTAS query every hour, and drop the previous version after each rebuild.
  4. D. Run UPDATE and DELETE statements against the customer table as it is stored in S3 as Hive tables, one statement for each changed customer.
Show the answer and explanation

Answer: B

  • A. Wrong: Appending the changes stores duplicate and deleted rows, and every query must filter them out again. The option pushes the cleanup onto every reader and does not meet the no-duplicates requirement.
  • B. Right: Correct. MERGE INTO applies inserts, updates and deletes from a staging table to the Iceberg table in one statement. Matching on the customer ID keeps the result free of duplicates, and Athena runs it without a Spark cluster. Learn more: MERGE INTO (opens in a new tab)
  • C. Wrong: A full rebuild every hour scans and writes all 5 years of data each time, which is far more expensive than applying the changes. The option meets the result requirement but fails the cost goal.
  • D. Wrong: Athena does not run row-level updates and deletes on plain Hive tables. The option uses a capability the table format does not have, and it would be slow for thousands of changes.

Explanation

Iceberg tables in Athena support MERGE INTO, which applies a batch of inserts, updates and deletes from a staging table in one statement without duplicates, and it touches only the affected data. Appending changes leaves duplicates, a full CTAS rebuild every hour is expensive, and Athena cannot update or delete rows in plain Hive tables.

Study

Want more on Querying with Athena? The 5 full-length practice sets have 14 more Querying with Athena questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets

Study Querying with Athena

More Data Operations and Support topics