DEA-C01 practice question: Querying with Athena (1 of 3)
Data Operations and Support A free question from our original DEA-C01 practice exam. Try it, then open the answer to see why each option is right or wrong.
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.
AA. Recreate the table with OpenCSVSerDe, set its quote character to a double quote, and set skip.header.line.count to 1.
BB. Keep the table, add a WHERE clause that drops rows matching the header text, and rebuild the comments with string functions.
CC. Change the table's storage format to Parquet in its table properties, so that Athena reads the files as columnar data.
DD. 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.
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 →