DEA-C01 Practice Question 85: Columnar Format Few Columns
AWS Certified Data Engineer – Associate (DEA-C01) · Test 9 · question 5 of 10
A data lake holds 500 GB per day of JSON files in Amazon S3. Analysts run Athena queries that read only 3 of about 120 columns, and the query cost is high because each query scans most of the data. The team wants to reduce the data scanned without changing the SQL that analysts write. What should they do?
Show answer and explanation
Answer:
Parquet and ORC are *"columnar storage formats that are optimized for fast retrieval of data."* Their compression is applied by column, and *"predicate pushdown ... enables Athena queries to fetch only the blocks it needs."* Because a columnar file stores each column separately, a query that touches 3 of 120 columns reads only those columns. AWS recommends converting with *"CREATE TABLE AS SELECT (CTAS) queries"* or with an AWS Glue job. The SQL queries stay the same (option B).
- A is wrong because CSV is row-based and gzip files cannot be split, so Athena still reads all columns of every row. - C is wrong because result reuse only helps when the same query is repeated. Every new query still scans the JSON files and reads all the columns. - D is wrong because Avro is row-based. It suits row-at-a-time writes and schema evolution, but a query on 3 columns still has to read whole rows.
Reference: Use columnar storage formats