Skip to content

Skill 1.1.1: Extract data from data sources

Question 1 - Format Selection for Athena Query Patterns

A data engineering team at a financial services company is building a curated data lake on Amazon S3 for downstream analytics and ML model training. The raw data arrives as JSON files from multiple application sources and is transformed using AWS Glue ETL jobs.

The analytics team primarily queries the data using Amazon Athena, where their queries filter on transaction dates and aggregate only 5–8 columns out of the 200+ columns present in each record. The team must minimize both query costs and response times while maintaining the ability to query directly from S3 with no additional infrastructure management.

Which storage format should the data engineering team use for the curated layer? (Select ONE)

  • A) GZIP-compressed JSON files partitioned by date
  • B) Apache Parquet files partitioned by date and compressed with Snappy
  • C) Uncompressed CSV files stored in a single prefix
  • D) Apache ORC files stored in a single prefix with ZLIB compression
Click to reveal Answer & Explanation

Correct Answer: B - Apache Parquet files partitioned by date and compressed with Snappy

Correct Answer Analysis

  • B is Correct: Apache Parquet is a columnar storage format. When Athena queries only 5–8 columns out of 200+, columnar storage enables column pruning—Athena reads only the storage blocks containing the requested columns, dramatically reducing scanned bytes, query costs, and execution time. Partitioning by date additionally enables partition pruning so only relevant date partitions are scanned. Snappy compression further reduces storage footprint and I/O while being fast to decompress.

Distractor Analysis

  • A is Incorrect: JSON is row-oriented; every field in every matching record must be read and parsed regardless of whether it appears in the SELECT clause. GZIP compression reduces storage cost but not scanned bytes.
  • C is Incorrect: CSV is row-oriented and uncompressed, causing maximum scan overhead. A single prefix means no partition pruning; every query scans all data.
  • D is Incorrect: While ORC is columnar (correct idea), storing in a single prefix means no partition pruning by date, so queries filter by scanning the entire dataset. This violates the requirement for minimal query costs.

Key Takeaway: Use columnar formats (Parquet/ORC) with partition keys aligned to query filters to enable both column pruning and partition pruning in Athena.

Question 2 - Real-Time IoT Data Format for Kinesis

An industrial IoT company ingests temperature, vibration, and pressure readings from 50,000 sensors into Amazon Kinesis Data Streams. A downstream Amazon Managed Service for Apache Flink application processes these records in real time to detect anomalies and writes the processed output to S3 for later ML model training.

Each sensor emits small, flat records (10–15 fields) at one-second intervals. The Flink application must process records with the lowest possible latency, and records must be written to the stream in a human-readable format for immediate debugging by operations engineers. The S3 output used for training will be queried by Athena for wide-ranging time-series analyses.

Which data format should the Flink application use for the stream records? (Select ONE)

  • A) Apache Parquet
  • B) Apache ORC
  • C) JSON
  • D) CSV
Click to reveal Answer & Explanation

Correct Answer: C - JSON

Correct Answer Analysis

  • C is Correct: For streaming ingestion into Kinesis Data Streams, records must be serialized as they are produced. JSON is row-oriented, human-readable, and ideal for record-at-a-time writes to a stream, making it easy to debug and inspect. Kinesis Data Streams and Flink handle JSON natively with low latency. The need for a human-readable format for debugging rules out binary columnar formats.

Distractor Analysis

  • A is Incorrect: Parquet is a columnar, binary format optimized for batch analytics, not streaming writes to Kinesis Data Streams. It is not human-readable and adds serialization overhead that contradicts the low-latency requirement.
  • B is Incorrect: ORC, like Parquet, is a binary columnar format designed for data lake analytics, not low-latency stream ingestion or human-readable debugging.
  • D is Incorrect: CSV is row-oriented and human-readable. However, CSV is schema-less and fragile for nested or evolving data; JSON is preferred for streaming because it handles nested structures and schema evolution better, plus Kinesis and Flink have native JSON support.

Key Takeaway: Choose row-oriented formats like JSON for streaming ingestion; reserve columnar formats (Parquet/ORC) for the curated analytics layer where large scans benefit from column pruning.

Question 3 - Merging Data Across Disparate Sources with Conflict Resolution

A retail company wants to build a 360-degree customer profile dataset for ML model training. Customer data resides in three sources: Amazon RDS (transactional records), Amazon DynamoDB (real-time clickstream events), and a legacy on-premises data warehouse exported to S3 as CSV files.

The three sources use different customer identifiers. The team discovers that a customer's email address and shipping address differ across sources. The ML model will make churn predictions, and data consistency between training and inference is critical.

What must the team establish before merging these datasets into a single training dataset? (Select ONE)

  • A) The storage class of the output S3 bucket
  • B) The number of DPUs to allocate to the AWS Glue merge job
  • C) A master data management (MDM) strategy that defines which source is authoritative when fields conflict
  • D) Whether to use AWS Glue or Apache Spark on Amazon EMR for the merge
Click to reveal Answer & Explanation

Correct Answer: C - A master data management (MDM) strategy that defines which source is authoritative when fields conflict

Correct Answer Analysis

  • C is Correct: When merging data from multiple sources with overlapping fields, entity resolution and conflict resolution become the core challenge. The team must decide which source is authoritative for each field (e.g., "shipping address comes from RDS, not DynamoDB") to create a consistent, trustworthy training dataset. Without this, the model trains on contradictory or ambiguous data, harming prediction reliability.

Distractor Analysis

  • A is Incorrect: Storage class affects cost and access latency, not data correctness. Choosing a storage class is a later deployment decision, not a prerequisite for merging.
  • B is Incorrect: The number of DPUs is a performance configuration. It determines processing speed and cost, not data quality or conflict resolution.
  • D is Incorrect: The merge tool (Glue vs. Spark on EMR) is an implementation detail. Both tools can execute the same logic; neither resolves data disputes.

Key Takeaway: Data quality and authoritative-source rules must be settled before engineering the merge pipeline—conflict resolution is a semantic decision, not a compute decision.

Question 4 - Optimizing Training Data Format for SageMaker

A healthcare analytics company stores 5 years of patient outcome records (15 TB total) in Amazon S3 as gzip-compressed CSV files. The team uses SageMaker training jobs with a distributed XGBoost model. Each training job reads 4 columns from the 80-column dataset.

The team observes that training jobs take 3 hours just to load the dataset from S3 before actual training begins. They need to reduce data loading time for faster experimentation cycles, with no change to the model's accuracy and minimal operational overhead.

Which format and layout strategy should they implement? (Select ONE)

  • A) Recompress the CSV files with Zstandard (Zstd) to reduce file size
  • B) Convert the dataset to Apache Parquet stored in S3 and let SageMaker read the required columns natively
  • C) Convert the dataset to Apache ORC and store it in a single prefix for faster listing
  • D) Load all CSV data into Amazon Redshift and use the SageMaker Redshift connector
Click to reveal Answer & Explanation

Correct Answer: B - Convert the dataset to Apache Parquet stored in S3 and let SageMaker read the required columns natively

Correct Answer Analysis

  • B is Correct: SageMaker training jobs integrate natively with Parquet via Pipe mode or distributed data readers. Converting from CSV to Parquet (a columnar format) allows the training job to read only the 4 required columns, reducing I/O and load time significantly. Parquet's columnar design and predicate pushdown directly address the observed bottleneck (3-hour load time) without changing the model's underlying algorithm or accuracy.

Distractor Analysis

  • A is Incorrect: Recompressing CSV does not change the row-oriented nature of CSV. The training job still reads and parses all 80 columns, just with slightly smaller file sizes. Gains are minimal and do not address the core I/O inefficiency.
  • C is Incorrect: While ORC is columnar, storing all data in a single prefix means no partition pruning. The 15 TB dataset in one location still requires a massive listing operation and lacks the organizational structure for efficient column pruning across the dataset. (Also, SageMaker Pipe mode has stronger Parquet support.)
  • D is Incorrect: Redshift adds operational overhead and license/modeling complexity. Redshift is also columnar, but moving data into a data warehouse is unnecessary when S3 + Parquet suffices for SageMaker training.

Key Takeaway: Columnar formats like Parquet slash training data load times in SageMaker by enabling column pruning—reading only what the model uses.

Question 5 - Streaming Backlog Diagnosis

A security analytics team runs a streaming pipeline that consumes events from Amazon Kinesis Data Streams and loads them into an Amazon OpenSearch Service domain for near-real-time threat detection. The pipeline is built with AWS Lambda consumers.

Over the last 6 hours, the Kinesis stream's iterator age has grown from seconds to several minutes. The team observes that Lambda invocations are successful with no error metrics, but OpenSearch shows high CPU utilization and write throttling. They need to restore normal stream processing as quickly as possible.

What is the root cause of the growing iterator age, and what action should the team take? (Select ONE)

  • A) The Lambda functions are under-provisioned; increase reserved concurrency for all Lambda consumers.
  • B) The Kinesis stream has too many shards; reduce shard count to decrease record throughput.
  • C) The OpenSearch domain is the bottleneck; scale up the OpenSearch cluster or optimize its indexing.
  • D) The events have changed size; add more shards to the Kinesis stream to handle larger records.
Click to reveal Answer & Explanation

Correct Answer: C - The OpenSearch domain is the bottleneck; scale up the OpenSearch cluster or optimize its indexing

Correct Answer Analysis

  • C is Correct: Iterator age in Kinesis measures how far behind the consumer is from the current record. Since Lambda shows no errors but OpenSearch exhibits high CPU and write throttling, the OpenSearch domain is the binding constraint. The Lambda functions are blocked on OpenSearch write latency, so adding more Lambda capacity would only push the queue further. Scaling up OpenSearch (more data nodes, higher instance types, or reducing unnecessary document indexing) resolves the root cause.

Distractor Analysis

  • A is Incorrect: The scenario explicitly states that Lambda invocations are successful and no error metrics are present, meaning the Lambda functions are not the constraint. Adding reserved concurrency without fixing OpenSearch would cause even more throttling at the downstream target.
  • B is Incorrect: Reducing shard count would decrease the entity throughput of the Kinesis stream, worsening the backlog, not resolving it. The source throughput is not the issue.
  • D is Incorrect: The iterator age suggests the stream is producing records faster than the pipeline can process, but the cause is downstream (OpenSearch writes are slow). Adding Kinesis shards would increase the stream's capacity to hold more records, but would not speed up the writes to OpenSearch.

Key Takeaway: In streaming pipelines, when the consumer falls behind and the downstream write target is at capacity, fixing the downstream bottleneck—not adding consumer concurrency—is the correct resolution.

Question 6 - Choosing the Right Storage for Large-Scale Feature Lookup

A fraud detection team uses AWS Glue to train a model on 40 TB of historical transaction data stored in S3. The model inference runs on Amazon SageMaker endpoints. The inference code needs to look up real-time features for a given account ID with latency under 10 ms, and the feature values should reflect the most recent transaction.

The team also needs to maintain a historical copy of all feature values for model retraining, with the ability to retrieve point-in-time snapshots. They want to minimize operational overhead and avoid writing custom feature management code.

Which AWS service or combination should the team use? (Select ONE)

  • A) Amazon S3 with Athena and an S3 Select query in the inference code
  • B) Amazon DynamoDB with DynamoDB Streams to maintain a historical table backed up to S3
  • C) Amazon SageMaker Feature Store
  • D) Amazon ElastiCache for Redis with a daily job to write snapshots to S3
Click to reveal Answer & Explanation

Correct Answer: C - Amazon SageMaker Feature Store

Correct Answer Analysis

  • C is Correct: SageMaker Feature Store provides both an online store (low-latency, high-throughput reads for inference) and an offline store (historical, partitioned data for training). This dual-store design directly satisfies the 10 ms lookups for real-time feature values and the historical snapshot requirement. The service is fully managed, so no custom code for feature versioning or synchronization is needed, minimizing operational overhead.

Distractor Analysis

  • A is Incorrect: Athena and S3 Select have query latencies in the hundreds of milliseconds to seconds, far exceeding the 10 ms requirement. S3 is not designed for low-latency point lookups in high-throughput inference workloads.
  • B is Incorrect: DynamoDB offers low-latency reads, but maintaining a historical copy with point-in-time snapshots requires building and managing DynamoDB Streams consumers, backup pipelines, and versioning logic—significant operational overhead compared to a managed feature store.
  • D is Incorrect: ElastiCache for Redis provides low-latency reads, but it lacks built-in feature versioning or historical offline storage. Writing custom code to periodically export Redis snapshots to S3 for training introduces custom infrastructure and complexity.

Key Takeaway: SageMaker Feature Store solves both low-latency inference lookups and historical feature retrieval with a single managed service—avoid custom pipelines for feature synchronization.

Question 7 - Vector Store Selection for RAG Application

A legal research platform built a retrieval-augmented generation (RAG) system using foundation models in Amazon Bedrock. The system stores embeddings of legal documents. The platform's latency requirements are strict: retrieval must complete in under 50 ms. The team also requires the ability to store additional metadata about each document (e.g., jurisdiction, date, court level) for filtering during search.

The team already manages relational metadata in an Amazon RDS for PostgreSQL instance. The RAG system queries both embeddings and relational metadata together for filtering. They want to minimize operational complexity and avoid managing a separate vector database.

Which option best meets these requirements? (Select ONE)

  • A) Store embeddings in Amazon S3 with a vector index file, and query them from S3
  • B) Use Amazon RDS for PostgreSQL with the pgvector extension, storing embeddings alongside relational metadata
  • C) Use Amazon OpenSearch Service with the k-NN plugin for vector search and relational metadata in a separate PostgreSQL table
  • D) Store embeddings in Amazon DynamoDB with a vector search secondary index
Click to reveal Answer & Explanation

Correct Answer: B - Use Amazon RDS for PostgreSQL with the pgvector extension, storing embeddings alongside relational metadata

Correct Answer Analysis

  • B is Correct: The pgvector extension adds vector similarity search (cosine, L2, inner product) directly inside PostgreSQL. Because the team already uses RDS for PostgreSQL for relational metadata, adding pgvector enables querying embeddings and metadata in the same SQL query, avoiding the need to manage, synchronize, and pay for a separate vector database. It satisfies the sub-50 ms latency requirement for millions of vectors with proper indexing and instance sizing.

Distractor Analysis

  • A is Incorrect: S3 is object storage with no native low-latency vector search capability. Querying vectors from S3 would require loading and scanning objects, which cannot meet the 50 ms requirement.
  • C is Incorrect: OpenSearch with the k-NN plugin is a valid vector search solution, but it introduces a second database and requires synchronization between OpenSearch vectors and PostgreSQL metadata. This increases operational complexity and violates the constraint of minimizing operational overhead.
  • D is Incorrect: DynamoDB does not have a native vector search secondary index. Vector search in DynamoDB would require custom application logic to scan or maintain separate structures, which is complex and not performant for nearest-neighbor queries.

Key Takeaway: When relational metadata already lives in PostgreSQL, adding pgvector is often the lowest-overhead way to add vector similarity search without introducing a second database.

Question 8 - Choosing Between JSON and Parquet for Batch ETL Output

A marketing analytics team ingests campaign event data from various APIs into Amazon S3. The raw data is stored as JSON files. A nightly AWS Glue job transforms the raw data into a curated dataset used for BI dashboards and ML training.

The curated dataset is queried by Amazon Athena. Queries filter by campaign ID and aggregate specific metrics (clicks, impressions, conversions) across millions of rows. The team needs to minimize the amount of data scanned and reduce the cost of Athena queries, while maintaining serverless query access.

Which file format should the AWS Glue job write for the curated dataset? (Select ONE)

  • A) JSON with GZIP compression
  • B) Apache Parquet with Snappy compression and partitioned by campaign ID
  • C) CSV with GZIP compression and partitioned by date
  • D) Apache Avro with row-based encoding
Click to reveal Answer & Explanation

Correct Answer: B - Apache Parquet with Snappy compression and partitioned by campaign ID

Correct Answer Analysis

  • B is Correct: Apache Parquet is a columnar format. When Athena queries only a few columns (clicks, impressions, conversions) across many rows, Parquet enables column pruning—Athena reads only the on-disk data for those specific columns. Partitioning by campaign ID adds partition pruning, so queries filtering by campaign ID only scan relevant partitions. Together, these drastically reduce scanned bytes and query cost in Athena.

Distractor Analysis

  • A is Incorrect: JSON is row-oriented, so even with GZIP compression, Athena must read and parse entire records to extract specific fields. This results in much higher scanned bytes.
  • C is Incorrect: CSV is row-oriented and lacks the columnar advantage for aggregate queries. Partitioning by date (instead of campaign ID) doesn't align with the query filter, so Athena scans all campaign IDs within each date partition.
  • D is Incorrect: Avro is a row-oriented format (though more compact than JSON). It does not provide the column pruning benefits of Parquet, and row-based encoding still requires reading complete rows for partial column extraction.

Key Takeaway: Columnar formats (Parquet/ORC) with partitions matching query filters give Athena the best cost-performance for analytic queries over large curated datasets.

Question 9 - Format for Streaming Kinesis Output to S3 for Analytics

A connected vehicle platform receives telemetry data from 100,000 vehicles in real time via Amazon Kinesis Data Streams. An Amazon Managed Service for Apache Flink application processes the stream and writes raw data directly to Amazon S3 for archival. A separate daily batch job (using AWS Glue) reads this S3 data, transforms it, and writes a curated dataset for Athena analytics.

The raw S3 layer must support high-throughput streaming writes from Flink with minimal write latency and no additional database overhead. The daily batch job then reads the raw data and produces Parquet files. The team wants to minimize operational complexity and ensure the pipeline is cost-efficient.

Which format should the Flink application write for the raw S3 layer? (Select ONE)

  • A) Apache Parquet
  • B) Apache ORC
  • C) JSON
  • D) Apache Avro with compression
Click to reveal Answer & Explanation

Correct Answer: C - JSON

Correct Answer Analysis

  • C is Correct: JSON is a row-oriented format ideal for streaming writes because each record can be written independently without needing to accumulate batches to build columnar stripes. Flink's S3 sink natively supports JSON. It allows the daily batch job (Glue) to read and transform data easily, then write curated Parquet. JSON is also human-readable for debugging raw streams.

Distractor Analysis

  • A is Incorrect: Parquet is optimized for batch writes to a data lake, not high-throughput streaming writes. Building columnar files in a stream would require buffering and complexity (e.g., a streaming file sink with rollover), increasing write latency and operational burden.
  • B is Incorrect: ORC, like Parquet, is a columnar format intended for analytic batch workloads, not low-latency streaming writes. Writing ORC from Flink would require additional plumbing (e.g., batching) that adds latency and complexity.
  • D is Incorrect: Avro is row-oriented and compact, but it is a binary format requiring schema management (schema registry or embedded schemas). JSON is simpler for the raw archive layer and is easier to inspect and debug, with no schema management overhead. Avro's binary nature also makes ad-hoc debugging more difficult.

Key Takeaway: For raw streaming layers, use row-oriented formats (JSON/Avro) that support low-latency record-at-a-time writes; use columnar formats for the downstream curated layer where analytic queries dominate.

Question 10 - Scaling Streaming Consumer to Match Throughput

An e-commerce company processes clickstream events with Amazon Kinesis Data Streams. The stream has 50 shards. A consumer application running on Amazon EC2 instances reads from the stream and writes to Amazon Redshift for analytics.

The Redshift cluster is performing well, with CPU utilization under 30%. However, the Kinesis iterator age has been steadily increasing for the past 4 hours. The EC2 instances show CPU utilization of 95%, and the application uses a single-threaded per-instance consumer.

What is the most likely cause of the growing iterator age? (Select ONE)

  • A) The Redshift cluster is slowing down and causing write backpressure
  • B) The Kinesis stream is over-provisioned with too many shards
  • C) The EC2 consumer instances are under-provisioned; they lack enough compute capacity to keep up with the stream's record rate
  • D) The data size of each record is growing, causing the Kinesis shards to throttle producers
Click to reveal Answer & Explanation

Correct Answer: C - The EC2 consumer instances are under-provisioned; they lack enough compute capacity to keep up with the stream's record rate

Correct Answer Analysis

  • C is Correct: The ECS consumer instances show 95% CPU utilization, meaning they are compute-bound and cannot process records fast enough to keep up with the incoming rate. The iterator age increases because the consumer falls behind. Redshift is not the bottleneck (CPU under 30%), and the single-threaded consumers are the limiting factor. Scaling the EC2 consumer fleet vertically (larger instance types) or horizontally (more instances with multi-threading) would increase processing capacity.

Distractor Analysis

  • A is Incorrect: Redshift CPU is at under 30%, indicating the database is not the constraint. The high CPU utilization of the EC2 consumers is the clear indicator.
  • B is Incorrect: Too many shards would not cause the consumer to fall behind. If anything, more shards would increase the total capacity of the Kinesis stream to receive records, not slow down consumers.
  • D is Incorrect: The Kinesis stream's shards have defined capacity (1 MB/s write and 2 MB/s read per shard). Even if record sizes grew, the issue described is a consumer lag due to compute constraints on the EC2 instances, not a producer throttling issue.

Key Takeaway: High CPU on consumer instances with healthy downstream systems indicates the consumers are under-provisioned—scale up the consumer fleet rather than adjusting the stream or downstream store.