Skill 1.1.5: Ingest from and write by using appropriate data formats (for example, Apache Parquet, JSON, CSV, ORC) based on data access patterns.
| Question | Answer & Explanation |
|---|---|
| ### Question 1 - Optimizing Athena Queries for Selective Column Access A retail company stores user interaction logs in an Amazon S3 data lake. Data scientists will use Amazon Athena to run ad-hoc queries that typically filter on a specific event type and read only 3 columns out of 50 available columns. The queries must minimize the amount of data scanned to reduce Athena costs and execution latency. Constraint: Most cost-effective solution for selective column queries. Which ONE data format should the engineering team use to store the logs in S3? - A) CSV (Comma-Separated Values) - B) JSON (JavaScript Object Notation) - C) Parquet - D) Avro |
Correct Answer: C Correct Option Analysis: - C is Correct: Apache Parquet is a columnar storage format. When a query only requests a subset of columns, Parquet enables column pruning (projection pushdown). The query engine reads only the relevant column blocks from S3, drastically reducing the data scanned, Athena query costs, and execution time. Distractor Analysis: - A is Incorrect: CSV is a row-oriented text format. To read 3 columns, Athena must scan every row and parse all 50 columns, leading to maximum data scanned and higher costs. - B is Incorrect: JSON is a document-oriented (row-based) format. Like CSV, Athena must read the entire JSON record to extract the required 3 fields, resulting in excessive data scanning. - D is Incorrect: Avro is a row-based format optimized for write-heavy workloads and schema evolution. It does not support column pruning, making it inefficient for selective analytical queries compared to Parquet. > Key Takeaway: For analytical workloads that query a subset of columns, always use columnar formats (Parquet or ORC) to leverage projection pushdown and minimize S3 data scanned. |
| ### Question 2 - Ingesting IoT Telemetry for Full-Table Scans A startup is logging structured telemetry data from thousands of IoT devices to an Amazon S3 bucket. The data is ingested in real-time and appended continuously. Data scientists will run infrequent, full-table scans, reading all columns and all rows to generate monthly compliance reports. Constraint: Simplest ingestion process with minimal processing overhead, as all data is read every time. Which ONE data format is most appropriate for this access pattern? - A) Parquet - B) ORC (Optimized Row Columnar) - C) JSON - D) CSV with Gzip compression |
Correct Answer: C Correct Option Analysis: - C is Correct: JSON is a row-oriented format that is ideal for continuous, append-only ingestion. Since the access pattern requires reading all columns and all rows (full-table scans), the columnar advantage of Parquet is lost. JSON avoids the compute overhead of columnar block assembly during ingestion, providing the lowest operational overhead for the pipeline. Distractor Analysis: - A is Incorrect: Parquet is optimized for selective column reads. Converting streaming data to Parquet on ingestion adds processing overhead (requiring buffering and row-group assembly) that provides no benefit if every query reads all columns. - B is Incorrect: Similar to Parquet, ORC is a columnar format that adds unnecessary ingestion complexity when full-row reads are the only access pattern. - D is Incorrect: While CSV is row-based, combining it with Gzip makes the files non-splittable in Athena, which can severely degrade query performance for large datasets. JSON (or uncompressed CSV) would be simpler for splittable full scans. > Key Takeaway: When queries always read full records (all columns and rows), row-based formats like JSON are often simpler to ingest than columnar formats, avoiding unnecessary conversion overhead. |
| ### Question 3 - Handling Schema Evolution with Append-Only Workloads An enterprise is migrating its on-premises Hadoop cluster to Amazon S3 and AWS Glue. The legacy system uses a row-based format that requires schema evolution (frequently adding new fields to records without rewriting existing files) and is heavily optimized for write-heavy, append-only workloads. The new architecture must maintain these properties while remaining queryable by Amazon Athena. Constraint: Optimal schema evolution and write-heavy performance, natively queryable in Athena. Which ONE data format should be used? - A) CSV - B) Parquet - C) ORC - D) JSON |
Correct Answer: D Correct Option Analysis: - D is Correct: JSON is a row-based, self-describing format that handles schema evolution gracefully. New fields can be added to new records without affecting older records, and Athena natively handles missing fields as NULLs. It requires no complex schema management overhead during ingestion, making it ideal for write-heavy, append-only workloads with evolving schemas. Distractor Analysis: - A is Incorrect: CSV does not natively support schema evolution. Adding a new column means altering the header, and existing files lacking that column will cause misalignment or parsing errors in Athena unless strict SerDe configurations are maintained. - B is Incorrect: Parquet requires schema reconciliation when merging files with different schemas. Appending new columns often requires rewriting old files or complex merge-on-read setups (like Apache Iceberg) to handle schema evolution efficiently. - C is Incorrect: ORC, like Parquet, is columnar and rigidly schema-bound. It does not natively tolerate varying schemas across files without similar heavy ETL or table-layer abstractions. > Key Takeaway: For write-heavy workloads with frequently evolving schemas, JSON's self-describing nature allows seamless addition of new fields without breaking older records. |
| ### Question 4 - Efficient Distributed Training Data Extraction A machine learning team is preparing a massive dataset of images and tabular metadata for a SageMaker training job. The metadata contains 100 columns, but the ML model only requires 5 specific feature columns for training. The team uses Amazon Athena to query and extract these features into an S3 bucket before launching the training job. Constraint: Fastest training data extraction query performance. Which TWO data formats satisfy this requirement? (Select TWO) - A) Store the metadata as CSV - B) Store the metadata as Parquet - C) Store the metadata as JSON - D) Store the metadata as ORC |
Correct Answers: B and D Correct Options Analysis: - B is Correct: Parquet is a columnar format. Athena will only scan and read the 5 columns required out of the 100 available, resulting in massive performance improvements over row-based formats. - D is Correct: ORC (Optimized Row Columnar) is also a columnar format. Like Parquet, it supports projection pushdown, allowing the query engine to skip reading the 95 unneeded columns, thus providing the fastest extraction performance. Distractor Analysis: - A is Incorrect: CSV is row-based. To extract the 5 required columns, Athena must scan the entire row (all 100 columns) and discard the unneeded data, resulting in slow performance and high data scanning. - C is Incorrect: JSON is document-oriented and row-based. Extracting 5 fields from a 100-field JSON document requires reading and parsing the entire document, making it highly inefficient for this analytical access pattern. > Key Takeaway: Both Parquet and ORC provide identical column-pruning benefits for analytical queries; choosing between them often comes down to ecosystem compatibility (e.g., Parquet for Spark, ORC for Hive). |
| ### Question 5 - Querying Highly Nested Relational Data A financial services company stores daily trading records in Amazon S3. The data is heavily nested (e.g., a single transaction record contains nested arrays of line items and sub-objects). The analytics team runs Amazon Athena queries that frequently extract specific nested fields using the UNNEST function. Constraint: Most efficient query performance for nested data in Athena. Which ONE data format should be used?- A) CSV - B) Parquet - C) JSON (single document) - D) XML |
Correct Answer: B Correct Option Analysis: - B is Correct: Parquet has native, highly optimized support for nested data structures. It stores nested columns as repeated and optional columns, allowing Athena to efficiently perform projection pushdown on nested fields, scanning only the requested nested data without parsing the entire record. Distractor Analysis: - A is Incorrect: CSV is a flat format and cannot represent nested data natively. It requires complex SerDe configurations to map nested structures, resulting in poor performance and high fragility. - C is Incorrect: While JSON can natively represent nested data, it is row-based. Querying nested fields in JSON requires reading and parsing the entire JSON document from S3 before the UNNEST operation can occur, leading to slow performance.- D is Incorrect: XML is similar to JSON in its document-oriented nature but is more verbose and generally slower to parse in Athena compared to columnar formats. > Key Takeaway: Parquet's columnar architecture handles nested structures efficiently by allowing predicate pushdown into nested fields, making it superior to row-based JSON for nested analytics. |
| ### Question 6 - Third-Party Vendor Data Export Format A data engineering team needs to export a structured relational dataset daily from an Amazon RDS database to Amazon S3. A third-party vendor will download these files and ingest them into their on-premises systems. The vendor requires the data in a flat, human-readable format that can be opened directly in spreadsheet software without any specialized parsing libraries. Constraint: Vendor compatibility and human readability. Which ONE data format is most appropriate? - A) Parquet - B) ORC - C) CSV - D) Avro |
Correct Answer: C Correct Option Analysis: - C is Correct: CSV (Comma-Separated Values) is a universal, flat, row-oriented text format. It is universally compatible with spreadsheet software (like Microsoft Excel) and basic text editors, fulfilling the requirement for human readability and easy ingestion without specialized libraries. Distractor Analysis: - A is Incorrect: Parquet is a binary, columnar format. It cannot be read by standard text editors or spreadsheet software natively and requires libraries like Apache Arrow or PyArrow to parse. - B is Incorrect: ORC is also a binary, columnar format optimized for Hive. It is not human-readable and requires specialized tools to parse. - D is Incorrect: Avro is a binary, row-based format that relies heavily on a defined schema for parsing. It is not human-readable without specialized deserialization libraries. > Key Takeaway: When sharing data with external partners or requiring human inspection, CSV remains the standard format due to its universal flat-file compatibility, despite its performance drawbacks for large-scale analytics. |
| ### Question 7 - Optimal Price-Performance for Curated Data Lakes A company is building a data lake on Amazon S3. Raw data arrives as large JSON files. A downstream AWS Glue ETL job processes this data and writes it to a curated S3 layer. The curated layer will be heavily queried by Amazon Athena. The engineering team wants to ensure the curated data is stored in a format that provides the best price-performance ratio for Athena queries, balancing file size reduction with fast decompression. Constraint: Optimal Athena price-performance using standard AWS best practices. Which ONE configuration should be used? - A) Parquet with Snappy compression - B) CSV with Gzip compression - C) JSON with Bzip2 compression - D) ORC with Zstandard compression |
Correct Answer: A Correct Option Analysis: - A is Correct: Parquet with Snappy compression is the AWS-recommended default for Athena. Parquet provides columnar storage (reducing scanned data), and Snappy compression offers an excellent balance between high compression ratios and extremely fast decompression speeds, minimizing CPU overhead during Athena queries. Distractor Analysis: - B is Incorrect: CSV with Gzip makes files non-splittable, preventing Athena from parallelizing reads across multiple workers. Furthermore, CSV forces full-row scans, negating any analytical performance benefits. - C is Incorrect: JSON with Bzip2 is splittable but remains a row-based format. Bzip2 is also slower to decompress than Snappy, increasing Athena query times. - D is Incorrect: While ORC with Zstandard is highly performant, it is less universally integrated across AWS services compared to Parquet with Snappy, and Snappy is specifically tuned for the Athena/Spark engine's default decompression speed. > Key Takeaway: For AWS Glue and Athena data lakes, Parquet with Snappy compression is the industry-standard best practice for achieving the best balance of storage cost reduction and query execution speed. |
| ### Question 8 - Solving the "Small Files Problem" in S3 A data scientist is running a distributed Amazon SageMaker training job that reads thousands of small CSV files directly from an S3 bucket. The training job is experiencing long startup and read times due to the high overhead of opening and managing thousands of individual S3 objects. Constraint: Least operational overhead to reduce S3 read latency and optimize data access for the training job. Which ONE solution should the engineer implement? - A) Convert the CSV files to Parquet and consolidate them into larger files using AWS Glue. - B) Keep the files as CSV but compress them into a single large ZIP archive. - C) Move the files to Amazon EFS and mount it to the SageMaker training instances. - D) Use Amazon S3 Transfer Acceleration to read the small CSV files faster. |
Correct Answer: A Correct Option Analysis: - A is Correct: Converting CSV to Parquet and consolidating the files resolves the S3 "small files problem." Larger Parquet files reduce the number of S3 GET requests, minimizing read latency. Parquet's columnar format also speeds up the actual data parsing during the SageMaker training job. Distractor Analysis: - B is Incorrect: Compressing CSVs into a single ZIP archive creates a non-splittable file. SageMaker distributed training cannot parallelize reads from a single non-splittable file, causing bottlenecks. - C is Incorrect: Moving the files to Amazon EFS adds significant operational overhead and cost for a one-time batch training job compared to an S3-based ETL transformation. EFS is better suited for shared, mutable file systems during active development. - D is Incorrect: S3 Transfer Acceleration optimizes cross-region or long-distance network transfers, not the local read latency caused by making thousands of individual API calls. The bottleneck is the object count, not network throughput. > Key Takeaway: When dealing with thousands of small files in S3, use AWS Glue to convert and compact them into larger Parquet files to minimize S3 request overhead and improve downstream read performance. |
| ### Question 9 - Serverless Streaming Data Conversion A real-time analytics application ingests clickstream events into Amazon Kinesis Data Firehose. The team wants to deliver the data to Amazon S3 in a format that minimizes storage costs and maximizes query efficiency in Amazon Athena, without requiring a downstream ETL cluster to convert the data. Constraint: Serverless format conversion on ingestion. Which TWO formats can Kinesis Data Firehose natively convert the incoming JSON data into before writing to S3? (Select TWO) - A) Parquet - B) ORC - C) Avro - D) CSV |
Correct Answers: A and B Correct Options Analysis: - A is Correct: Kinesis Data Firehose natively supports converting incoming JSON data into Apache Parquet format before delivering it to S3. This allows immediate, cost-effective querying in Athena without additional ETL. - B is Correct: Kinesis Data Firehose also natively supports converting incoming JSON data into Apache ORC (Optimized Row Columnar) format, providing the same columnar query benefits as Parquet. Distractor Analysis: - C is Incorrect: Firehose does not natively support converting incoming streaming data to Avro format. It would require a custom transformation using AWS Lambda. - D is Incorrect: Firehose does not natively convert JSON to CSV. Furthermore, CSV is a row-based format that does not provide the storage or query performance benefits required for Athena analytics. > Key Takeaway: Kinesis Data Firehose can natively convert streaming JSON to Parquet or ORC, allowing you to bypass downstream ETL clusters and land query-optimized, columnar data directly into S3. |
| ### Question 10 - Optimizing Aggregation Queries An AI research lab is processing large volumes of genomic data stored in Amazon S3. The raw data is heavily structured into over 200 columns. The researchers run Amazon Athena queries that perform complex aggregations (e.g., SUM, AVERAGE) heavily grouped by specific genomic markers. Constraint: Optimal performance for aggregation queries. Which ONE data format should be used? - A) JSON - B) CSV - C) Parquet - D) TXT |
Correct Answer: C Correct Option Analysis: - C is Correct: Parquet is a columnar format. When performing aggregations (like SUM or AVERAGE) on specific columns, Parquet allows the query engine to read only the data blocks of the aggregated columns and the grouping columns. It also supports min/max statistics for row group skipping, drastically speeding up aggregations. Distractor Analysis: - A is Incorrect: JSON is row-based. Aggregating a specific field requires reading and parsing the entire JSON record for every row, causing massive I/O overhead and CPU utilization. - B is Incorrect: CSV is row-based. Similar to JSON, Athena must read all 200 columns from S3 to extract the 2-3 columns needed for the aggregation, making it highly inefficient. - D is Incorrect: TXT is an unstructured format and not suitable for running relational aggregations in Athena without a complex SerDe, which would still suffer from row-based I/O limitations. > Key Takeaway: Columnar formats like Parquet excel at aggregation queries because they physically group column data together, enabling the engine to apply aggregation functions directly to compressed column chunks. |