DEA-C01 Data Ingestion and Transformation Practice Test 18 — 15 Questions

Question 1

A company is ingesting streaming data from IoT devices into Amazon Kinesis Data Streams. The data must be transformed into Parquet format and stored in Amazon S3. Which AWS service can perform the transformation in near real-time with minimal operational overhead?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Amazon Kinesis Data Firehose with a transformation Lambda function. Amazon Kinesis Data Firehose is the fully managed service designed to load streaming data into S3 with built-in data format conversion. By attaching a Lambda transformation function, you can convert incoming records to Parquet format in near real-time without managing any infrastructure, making it the lowest-operational-overhead choice for this task.

Answer

Amazon EMR cluster running Spark Streaming

Answer

AWS Glue ETL job triggered by Kinesis stream

Answer

Amazon Kinesis Data Analytics for Apache Flink

Question 2

A data engineer needs to load data from an on-premises Oracle database to Amazon S3 daily. The table is 500 GB and grows by 50 MB per day. The load must capture only new and changed rows since the last run. Which solution is MOST cost-effective and requires the least maintenance?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication (CDC). AWS DMS with ongoing replication (CDC) is the most cost-effective and low-maintenance solution because it continuously captures only new and changed rows from the Oracle source using its built-in CDC mechanism (reading redo logs), without requiring custom scripting or full table exports. It automatically handles schema changes, resumability, and incremental loading to S3, minimizing operational overhead and data transfer costs.

Answer

Write a custom Python script on EC2 to query the Oracle redo logs and upload to S3

Answer

Export the entire table to CSV daily using a script and upload to S3

Answer

Use AWS Glue ETL job with a JDBC connection and a timestamp filter

Question 3

A company uses AWS Glue to process JSON logs from S3. The logs have a nested structure and the schema evolves over time. The data engineer needs to ensure the Glue job can handle schema changes without failing. Which configuration should be used?

Accepted Answer

Set the job parameter '--enable-glue-datacatalog' and '--mergeDynamicColumns' to true. Setting '--enable-glue-datacatalog' allows the Glue job to use the Data Catalog as the metastore, and '--mergeDynamicColumns' (or the equivalent '--enable-schema-evolution' in newer Glue versions) instructs the job to dynamically merge new columns from the evolving JSON schema into the existing table schema during runtime, preventing job failures due to schema mismatches. This is specifically designed for nested, schema-evolving data like JSON logs, as it automatically reconciles differences between the source data and the catalog definition.

Answer

Manually update the table schema in the Glue Data Catalog before each run

Answer

Use Spark SQL with a static schema definition in the script

Answer

Enable AWS Glue Schema Registry and define a schema version

Question 4

A data engineer is designing a data ingestion pipeline for clickstream data. The data arrives in batches of 10-50 MB every 5 seconds. The engineer needs to buffer the data, perform simple transformations (e.g., add timestamp, remove PII), and land it in S3 within 10 minutes. Which TWO services should be combined? (Choose TWO.)

Accepted Answer

Amazon Kinesis Data Firehose. Amazon Kinesis Data Firehose is the correct choice because it is a fully managed service designed to ingest streaming data, buffer it, perform simple transformations (such as adding timestamps or removing PII) via built-in Lambda functions, and automatically deliver the data to S3. It can handle the 10-50 MB batches every 5 seconds and meet the 10-minute delivery window without requiring custom code for buffering or delivery.

Answer

Amazon Simple Queue Service (SQS)

Answer

AWS Glue ETL

Answer

Amazon Kinesis Data Streams

Question 5

A company needs to ingest data from multiple SaaS applications (Salesforce, Marketo) into Amazon S3 for analytics. The data volume is moderate (~100 GB per day). The pipeline must handle schema changes, deduplicate records, and provide low latency (under 1 hour). Which THREE services should be used? (Choose THREE.)

Accepted Answer

Amazon AppFlow. Amazon AppFlow is the correct choice because it is a fully managed integration service specifically designed to transfer data from SaaS applications like Salesforce and Marketo to AWS services such as Amazon S3. It supports incremental transfers, handles schema changes automatically via its schema evolution feature, and can achieve sub-hour latency for moderate data volumes (~100 GB/day) without custom coding.

Answer

Amazon Kinesis Data Streams

Answer

AWS Database Migration Service (DMS)

Question 6

A data engineer needs to transform CSV files in S3 to Parquet format using a serverless solution. The files are large (up to 5 GB each) and arrive irregularly. Which TWO services can accomplish this with minimal operational overhead? (Choose TWO.)

Accepted Answer

AWS Glue ETL job. AWS Glue ETL job is correct because it is a fully managed, serverless service that can automatically convert CSV to Parquet without provisioning infrastructure. It handles large files (up to 5 GB) by scaling Spark executors dynamically, and can be triggered by S3 events for irregular arrivals, minimizing operational overhead.

Answer

Amazon EC2 with a script

Answer

Amazon EMR cluster

Answer

Amazon Redshift Spectrum

Question 7

A company uses Amazon S3 Event Notifications to trigger a Lambda function that processes incoming files. Recently, the Lambda function has been timing out for large files (>100 MB). The data engineer wants to improve the pipeline to handle large files reliably. Which solution is the MOST scalable and cost-effective?

Accepted Answer

Use S3 Event Notification to send to an SQS queue, then have Lambda poll the queue. Decoupling S3 event notifications via an SQS queue allows Lambda to poll messages at its own pace, preventing timeouts from large files. The SQS queue acts as a buffer, enabling Lambda to process files asynchronously and scale based on the queue depth without being constrained by the synchronous S3 trigger timeout (typically 15 minutes for Lambda, but large files can still cause issues with concurrent execution limits). This approach is both scalable and cost-effective, as it avoids idle wait time and allows Lambda to process files in smaller chunks or with longer execution times as needed.

Answer

Use Amazon SNS to fan out the event to multiple Lambda functions

Answer

Use AWS Step Functions to orchestrate multiple Lambda functions for parallel processing

Answer

Increase the Lambda timeout to 15 minutes

Question 8

A data engineer is designing a streaming pipeline that ingests data from an Amazon Kinesis Data Stream (with 5 shards) into Amazon S3. The data must be transformed using a complex stateful operation that cannot be done in a Lambda function (limited to 15 minutes). The engineer needs a solution that can maintain state across multiple records. Which service should be used?

Accepted Answer

Amazon Kinesis Data Analytics for Apache Flink. Amazon Kinesis Data Analytics for Apache Flink is the correct choice because it supports stateful stream processing with exactly-once semantics, can maintain state across multiple records, and has no 15-minute execution limit like AWS Lambda. It natively integrates with Kinesis Data Streams and can sink transformed data to S3, meeting all requirements for complex stateful operations.

Answer

Amazon EMR running Spark Structured Streaming

Answer

Amazon Kinesis Data Firehose with Lambda transformation

Answer

AWS Glue streaming ETL job

Question 9

A company needs to ingest data from a MySQL database into Amazon S3 in near real-time. The database is running on EC2. The data engineer wants to minimize the impact on the source database. Which service should be used?

Accepted Answer

AWS Database Migration Service (DMS) with ongoing replication. AWS DMS with ongoing replication (change data capture) is the correct choice because it can continuously replicate changes from a MySQL source database to Amazon S3 with minimal performance impact. DMS uses a transactional log-based approach (MySQL binlog) to capture changes as they occur, avoiding heavy SELECT queries on the source. This enables near real-time ingestion without adding significant load to the production database.

Answer

AWS Glue ETL job with a JDBC connection

Answer

Amazon RDS for MySQL with read replica

Answer

AWS Schema Conversion Tool (SCT)

Question 10

A data engineer uses AWS Glue to process data from S3. The Glue job frequently fails with 'Out of Memory' errors. The job reads several large compressed files. What is the MOST effective way to resolve this issue without changing the code?

Accepted Answer

Increase the number of G.1X workers or use G.2X workers. Increasing the number of G.1X workers or switching to G.2X workers directly addresses the 'Out of Memory' errors by allocating more memory per Spark executor. G.1X provides 16 GB of memory per worker, while G.2X provides 32 GB, which is critical when processing large compressed files because decompression and transformation require additional heap space. This approach resolves the issue without modifying the job code, as it only changes the resource configuration.

Answer

Convert the compressed files to uncompressed format before processing

Answer

Repartition the data to fewer partitions

Answer

Increase the job timeout setting

Question 11

A company is building a data lake on S3. They have a large volume of CSV files (hundreds of GB) in a source bucket. They need to convert them to Parquet, partition by date, and ensure the data is encrypted at rest with SSE-KMS. The pipeline must be triggered automatically when new files arrive. Which THREE steps should be part of the solution? (Choose THREE.)

Accepted Answer

Configure S3 Event Notification to send events to an SQS queue. S3 Event Notifications can be configured to send events to an SQS queue when new CSV files arrive. This decouples the ingestion pipeline, allowing the Glue job to poll the queue for new file notifications and trigger processing without tight coupling or polling the S3 bucket directly. SQS provides reliable, scalable message delivery that can trigger downstream ETL workflows.

Answer

Use Amazon Kinesis Data Firehose to ingest new files

Answer

Use Amazon Athena CTAS query to convert files in batch

Question 12

A company is streaming clickstream data from a website into Amazon Kinesis Data Streams. The data must be transformed in near real-time and stored in Amazon S3 for analytics. Which AWS service should be used to transform the data as it is ingested?

Accepted Answer

Amazon Kinesis Data Analytics. Amazon Kinesis Data Analytics is the correct choice because it can process and transform streaming data in near real-time using SQL or Apache Flink, and then output the transformed data to destinations like Amazon S3. This service is specifically designed for real-time stream processing, making it ideal for transforming clickstream data as it is ingested into Kinesis Data Streams.

Answer

AWS Lambda (streaming function)

Answer

Amazon EMR (Spark Streaming)

Answer

AWS Glue (ETL jobs)

Question 13

A data engineer needs to ingest data from an on-premises Oracle database to Amazon S3 daily. The data volume is 500 GB per day, and the network bandwidth is 200 Mbps. The requirement is to minimize the impact on the source database and ensure data integrity. Which combination of AWS services should be used?

Accepted Answer

AWS Database Migration Service (DMS) with S3 as target. AWS DMS with S3 as target is correct because it supports continuous change data capture (CDC) from Oracle, minimizing impact on the source database by reading redo logs instead of querying tables directly. It can handle 500 GB/day over 200 Mbps (which yields ~2.16 TB/day theoretical max) and ensures data integrity via transactional consistency and validation checksums. DMS also automatically partitions large datasets and can resume from failures, making it ideal for daily bulk loads.

Answer

AWS Glue ETL jobs with JDBC connection

Answer

Amazon Kinesis Data Firehose with Oracle as source

Answer

AWS Data Pipeline with SQLActivity

Question 14

A company uses Amazon Kinesis Data Firehose to deliver data to Amazon S3. The Firehose delivery stream has a buffer size of 64 MB and a buffer interval of 300 seconds. The data volume is 1 GB per minute, and the average record size is 1 KB. The data must be delivered to S3 within 5 minutes of ingestion. The engineer notices that some files are being delivered after 10 minutes. What is the most likely cause?

Accepted Answer

The S3 bucket is throttling PUT requests due to high throughput. Amazon S3 buckets have a default limit of 3,500 PUT requests per second per prefix. With a data volume of 1 GB per minute and an average record size of 1 KB, Firehose generates approximately 1,000,000 records per minute, resulting in roughly 16,667 PUT requests per second (since each 64 MB buffer yields about 65,536 records, and 1 GB/min ÷ 64 MB = ~15.6 buffers per minute, each requiring a PUT). This far exceeds the S3 PUT request limit, causing throttling (HTTP 503 Slow Down errors) and delivery delays beyond the 5-minute target.

Answer

The buffer size of 64 MB is too small for the data volume

Answer

The data is not compressed, causing larger file sizes

Answer

The buffer interval of 300 seconds is too long

Question 15

A data engineering team needs to transform CSV files to Parquet format after they land in an S3 bucket. The transformation should be triggered automatically as soon as a new file arrives. Which AWS service is best suited for this task?

Accepted Answer

AWS Lambda function triggered by S3 event. AWS Lambda functions can be directly triggered by S3 events (e.g., `s3:ObjectCreated:*`) to process newly uploaded CSV files. This serverless approach provides near-instantaneous, event-driven transformation to Parquet without managing any infrastructure, making it the most cost-effective and simplest solution for this specific use case.

Answer

AWS Batch job submitted by S3 event

Answer

Amazon EMR cluster running continuously

Answer

AWS Glue ETL job scheduled every 5 minutes