AWS Certified Data Engineer Associate · Free Practice Question Medium
Question 31
A market research firm collects extensive survey data in .csv format, stored with numerous columns. The data analysts primarily focus on a subset of columns for most of their queries. The firm needs an efficient way to ingest this data into their Amazon S3 data lake to facilitate cost-effective querying with Amazon Athena, especially given that full file scans are rare.
Which method should the data engineer use to ingest the survey data into the S3 data lake to optimize for Athena querying and cost efficiency?
-
A
Implement an AWS Glue ETL job to transform the .csv files into JSON format, focusing on the columns most frequently queried.
-
B
Utilize AWS Glue PySpark job to convert the .csv files into .orc format before ingesting them into the data lake.
-
C
Use AWS Lambda to parse the .csv files and reformat them into a normalized Amazon Redshift database for Athena querying.
-
D
Configure an AWS Glue ETL job to process the .csv files and store them in the data lake in Apache Parquet format.
Reveal correct answer
Correct answer: D
Explanation
Apache Parquet is a columnar storage file format that is optimized for querying with services like Amazon Athena. It allows for efficient compression and encoding schemes, leading to cost savings in both storage and query execution.
Since the data analysts primarily query only a few columns at a time, storing the data in Parquet format means that Athena queries will scan only the necessary columns, reducing the amount of data processed and thus the query costs.
This approach is well-suited for datasets with many columns where only a subset is frequently accessed.
CORRECT: "Configure an AWS Glue ETL job to process the .csv files and store them in the data lake in Apache Parquet format" is the correct answer (as explained above.)
INCORRECT: "Utilize AWS Glue PySpark job to convert the .csv files into .orc format before ingesting them into the data lake" is incorrect.
While ORC is also a columnar format like Parquet and offers similar benefits, Parquet is generally preferred for its better integration and performance with Amazon Athena, making it a more cost-effective choice for this specific use case.
INCORRECT: "Implement an AWS Glue ETL job to transform the .csv files into JSON format, focusing on the columns most frequently queried" is incorrect.
JSON is not a columnar format, and it's less efficient for the type of querying described. Querying JSON data typically requires more data to be scanned compared to Parquet, leading to higher query costs in Athena.
INCORRECT: " Use AWS Lambda to parse the .csv files and reformat them into a normalized Amazon Redshift database for Athena querying " is incorrect.
This approach introduces unnecessary complexity and cost. Amazon Redshift is a powerful data warehousing solution but overkill for this scenario. Additionally, using AWS Lambda for this task would require custom scripting and wouldn't leverage the columnar storage benefits provided by formats like Parquet
References:
https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-format-parquet-home.html
Save time with our AWS cheat sheets:
https://digitalcloud.training/aws-certified-data-engineer-associate-cheat-sheet/
Discussion
Think the marked answer is wrong, or have a better explanation? Share it below — comments appear after review.
