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

Question 1

A data engineer is designing a data ingestion pipeline for JSON files landing in an Amazon S3 bucket. The pipeline must transform the data (e.g., flatten nested structures) and load it into Amazon Redshift. The transformation logic is complex and may evolve frequently. Which approach provides the MOST flexibility and ease of maintenance?

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

Accepted Answer

Use AWS Glue ETL jobs to transform the data and load into Redshift.. AWS Glue ETL jobs provide a serverless, code-based environment using Apache Spark, which offers flexibility for complex transformations like flattening nested JSON structures. Glue can be configured to support exactly-once semantics through Spark checkpointing and transactional writes to Redshift, making it reliable for critical data pipelines. It handles varying file sizes and can be easily updated as transformation logic evolves. Option A is incorrect because Lambda has execution time and memory limits, making it unsuitable for large JSON files or complex transformations, and achieving exactly-once requires careful idempotency design. Option B is incorrect because the Redshift COPY command loads raw JSON without transformation. Option D is incorrect because Athena is primarily for querying data in S3, not for performing ETL transformations and loading into Redshift.

Answer

Use AWS Lambda functions to transform each file and load into Redshift.

Answer

Use the Amazon Redshift COPY command to load raw JSON directly.

Answer

Use Amazon Athena to query the raw data and insert into Redshift.

Question 2

A company needs to ingest streaming data from multiple sources and store it in Amazon S3. The data volume is up to 5 GB per hour. What is the MOST cost-effective ingestion service?

Accepted Answer

Amazon Kinesis Data Firehose. Amazon Kinesis Data Firehose is the most cost-effective service for ingesting streaming data into Amazon S3 at 5 GB/hour. It is fully managed, automatically scales, and charges only for data ingested (per GB), with no upfront provisioning. While Amazon Kinesis Data Streams (KDS) may have lower throughput cost for steady loads, it requires manual shard management and typically needs additional components (e.g., Lambda functions) to deliver data to S3, increasing operational overhead and total cost. AWS Glue is a batch ETL service, not designed for streaming. AWS Lambda is a compute service and would require custom code and scaling logic, making it more expensive and complex for this use case. Therefore, Kinesis Data Firehose provides the simplest and most cost-effective solution for streaming data ingestion directly to S3.

Answer

AWS Glue

Answer

Amazon Kinesis Data Streams

Answer

AWS Lambda

Question 3

A company is using AWS Glue ETL to transform and load data from Amazon S3 to Amazon Redshift. The data engineer notices that the job is taking longer than expected. Which TWO actions can improve the job performance?

Accepted Answer

Partition the source data in S3.. Options B and C are correct because partitioning the source data in S3 reduces the amount of data scanned by Glue, improving I/O efficiency, and increasing the number of DPUs adds more parallelism for transformations. Option A is incorrect because Redshift Spectrum is for querying data in S3 directly from Redshift, not for Glue ETL jobs. Option D is incorrect because S3 Transfer Acceleration speeds up uploads to S3 but does not affect Glue job performance during ETL processing. Option E is incorrect because larger Redshift node types do not impact Glue job execution; they only affect Redshift query performance.

Answer

Use Amazon Redshift Spectrum to query data directly.

Answer

Enable S3 Transfer Acceleration.

Answer

Use a larger Redshift node type.

Question 4

A company needs to ingest data from a MySQL database into Amazon S3 using AWS DMS. The data changes frequently and the requirement is to capture changes in near real-time. Which THREE configurations are necessary?

Accepted Answer

Create an S3 target endpoint in DMS.. To capture near real-time changes from a MySQL database to S3 using AWS DMS, you need: B) an S3 target endpoint in DMS to write the data; C) binary logging (binlog) enabled on MySQL to capture changes; and D) a DMS replication instance to process and transfer data. Options A and E are unnecessary: a VPC endpoint for S3 is not required (DMS can use public endpoints or VPC peering), and S3 event notifications are not used to trigger DMS—they are typically for post-processing.

Answer

Create a VPC endpoint for S3.

Answer

Configure an S3 event notification to trigger DMS.

Question 5

A company is building a data lake on Amazon S3 and needs to ingest data from various on-premises sources. Which TWO AWS services can be used to transfer data securely over the internet?

Accepted Answer

AWS DataSync. AWS DataSync is designed to securely transfer large amounts of data from on-premises storage to AWS over the internet. It uses TLS encryption for data in transit and can automate and accelerate transfers by leveraging parallel multi-threading and incremental updates. This makes it a correct choice for securely ingesting data into an S3-based data lake.

Answer

AWS Snowcone

Answer

Amazon Kinesis Data Firehose

Answer

AWS Direct Connect

Question 6

A company is ingesting streaming data from IoT devices into Amazon Kinesis Data Streams. The data must be transformed in real-time using custom Python code before being stored in Amazon S3. Which AWS service should be used to perform this transformation?

Accepted Answer

Kinesis Data Analytics for Apache Flink. Amazon EMR with Spark Streaming, which is optimized for large-scale batch and stream processing but is not the simplest or most direct service for this specific use case. Option B is AWS Lambda, which can be used for simple transformations but has limitations on execution time and complexity. Option D is Kinesis Data Firehose with custom data transformation, which supports only built-in transformations or Lambda functions, not arbitrary custom Python code directly. Option C, Kinesis Data Analytics for Apache Flink, is correct because it allows running custom Apache Flink applications, which support custom Python code via the Apache Flink Python API, for real-time data transformation.

Answer

Amazon EMR with Spark Streaming

Answer

AWS Lambda function triggered by Kinesis Data Streams

Answer

Kinesis Data Firehose with custom data transformation

Question 7

A data engineer needs to ingest data from an on-premises Oracle database into Amazon S3 using AWS DMS. The change data capture (CDC) must be enabled to capture ongoing changes. Which additional AWS service is required to store the transaction logs for CDC?

Accepted Answer

Amazon S3. AWS DMS change data capture (CDC) for Oracle databases does not require an additional AWS service to store transaction logs. DMS reads the redo logs directly from the source Oracle database, either using LogMiner or binary reader. Therefore, none of the listed services are needed for storing transaction logs. As none of the provided options are correct, this question contains no valid answer.

Answer

Amazon RDS

Answer

Amazon EBS

Answer

Amazon CloudWatch Logs

Question 8

A company wants to ingest data from multiple SaaS applications into Amazon S3 using a fully managed service that supports schema discovery and transformation. Which AWS service should they use?

Accepted Answer

Amazon AppFlow. Amazon AppFlow is a fully managed integration service that supports SaaS sources, schema discovery, and data transformation. Option A (Amazon Kinesis Data Firehose) is for streaming data but not for SaaS ingestion directly. Option C (AWS Glue) is an ETL service but not for SaaS ingestion directly. Option D (AWS Data Pipeline) is not fully managed for SaaS.

Answer

Amazon Kinesis Data Firehose

Answer

AWS Glue

Answer

AWS Data Pipeline

Question 9

A data engineer is troubleshooting an AWS Glue ETL job that fails with an OutOfMemory error when processing large JSON files from Amazon S3. The files contain deeply nested structures. Which approach should the engineer take to resolve this issue?

Accepted Answer

Increase the DPU (Data Processing Unit) per worker. Increasing the DPU per worker allocates more memory per worker, directly addressing the OutOfMemory error when processing large files. Option A is incorrect because the `recurse` option is not a valid argument for `getResolvedOptions`, which is used for retrieving job parameters. Option B is incorrect because increasing the number of workers adds parallelism but does not increase memory per worker; OOM occurs per worker. Option D is incorrect because decreasing partitions reduces parallelism, potentially causing each partition to be larger, worsening the memory issue.

Answer

Use the `recurse` option with `getResolvedOptions` to limit recursion

Answer

Increase the number of workers in the Glue job configuration

Answer

Decrease the number of partitions while reading the data

Question 10

A company is using AWS Lake Formation to manage permissions on data in Amazon S3. They need to ingest data from an external source into a new database 'sales_db' and a table 'transactions' using AWS Glue. The IAM role used by Glue must have the minimal permissions to create the database and table in the Data Catalog and write data to the S3 location. Which combination of permissions should be granted?

Accepted Answer

Lake Formation permissions: `CREATE_DATABASE` on the catalog, `CREATE_TABLE` on `sales_db`, and S3 location permission. AWS Lake Formation manages permissions on the Data Catalog. To create a database and table, the Glue IAM role must have Lake Formation `CREATE_DATABASE` on the catalog and `CREATE_TABLE` on the `sales_db` database. Additionally, to write data to the S3 location, the role needs S3 write permissions (either via an IAM policy or Lake Formation data location permissions). Option A lacks Lake Formation permissions. Option B grants overly broad `lakeformation:GrantPermissions` which is not needed for creation. Option C provides only S3 permissions, missing Data Catalog permissions. Thus, D is the minimal combination.

Answer

IAM policy with `glue:CreateDatabase`, `glue:CreateTable`, and `s3:PutObject`

Answer

IAM policy with `lakeformation:GrantPermissions` on the database and table

Answer

IAM policy with `s3:GetObject` and `s3:PutObject` on the target location

Question 11

A company needs to ingest data from an on-premises SQL Server database into Amazon Redshift. The data volume is less than 1 TB and the network bandwidth is limited. Which AWS service should be used for the initial full load?

Accepted Answer

AWS Database Migration Service (DMS). AWS DMS is designed for migrating databases to AWS, including to Redshift. Option A (AWS Snowball) is for large data volumes (petabytes) and not efficient for <1 TB. Option C (Amazon S3 Transfer Acceleration) speeds up uploads to S3 but not directly to Redshift. Option D (AWS Direct Connect) is a network connection, not a migration service.

Answer

AWS Snowball Edge

Answer

Amazon S3 Transfer Acceleration

Answer

AWS Direct Connect

Question 12

A data engineer is designing a data ingestion pipeline for JSON files landing in an Amazon S3 bucket. The pipeline must transform the data (e.g., flatten nested structures) and load it into Amazon Redshift. The transformation logic is complex and may evolve frequently. Which approach provides the MOST flexibility and ease of maintenance?

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

Accepted Answer

Set batch size to 10000 records and batch window to 60 seconds. A batch size of 10,000 records combined with a batch window of 60 seconds maximizes the number of records per Lambda invocation while respecting the 60-second processing requirement, thereby minimizing the total number of invocations. Option A (batch size 100, no batch window) leads to many small invocations. Option C (batch window 0 seconds) triggers immediate processing, increasing invocation frequency. Option D (batch window 5 seconds) forces more frequent invocations than necessary, even with a large batch size.

Answer

Set batch size to 100 records and disable batch window

Answer

Set batch size to 100 records and batch window to 0 seconds

Answer

Set batch size to 10000 records and batch window to 5 seconds

Question 13

A company is using Amazon MSK (Managed Streaming for Apache Kafka) to ingest real-time data. They need to transform the data using custom Java code before writing to Amazon S3. The transformation must be fault-tolerant and exactly-once semantics are required. Which AWS service should be used?

Accepted Answer

Kafka Connect with S3 Sink Connector. Kafka Connect with S3 Sink Connector supports exactly-once semantics and custom transformations via Single Message Transforms (SMTs) or custom connectors. Option A (Amazon EMR with Spark Streaming) is for batch processing, not real-time exactly-once from MSK. Option B (AWS Lambda) does not provide exactly-once semantics from MSK. Option D (Kinesis Data Analytics for Apache Flink) is for Flink, not Kafka.

Answer

Amazon EMR with Spark Streaming

Answer

AWS Lambda consumer for MSK

Answer

Kinesis Data Analytics for Apache Flink

Question 14

A company needs to ingest data from Amazon S3 into Amazon Redshift for analytics. The data arrives in CSV format with headers and may contain duplicate rows. Which Redshift command should be used to load the data while handling duplicates?

Accepted Answer

Use a staging table with COPY and then MERGE into the target table. The correct approach to handle duplicates when loading data from S3 into Redshift is to use a staging table with the COPY command, followed by a MERGE (or UPSERT) operation to insert distinct rows into the target table. Option A is incorrect because `REMOVEDUPLICATES` is not a valid COPY option. Option B is incorrect because INSERT INTO ... SELECT DISTINCT via Spectrum does not use Redshift's native ingestion and may be slower. Option D is incorrect because CREATE TABLE AS SELECT DISTINCT does not integrate with existing tables and is not a standard loading pattern.

Answer

COPY command with the `REMOVEDUPLICATES` option

Answer

INSERT INTO ... SELECT DISTINCT from S3 via Spectrum

Answer

CREATE TABLE AS SELECT DISTINCT from the S3 bucket

Question 15

A company is building a data lake on Amazon S3 and needs to ingest data from multiple sources. Which of the following AWS services can be used to ingest and transform data in near real-time? (Select TWO.)

Accepted Answer

AWS Glue. AWS Glue is correct because it provides a serverless ETL (Extract, Transform, Load) service that can ingest data from various sources and transform it in near real-time using its streaming ETL capabilities. Glue can consume data from Amazon Kinesis Data Streams or Apache Kafka, apply transformations using Apache Spark, and write the results to Amazon S3 or other destinations, making it suitable for near real-time data ingestion and transformation.

Answer

Amazon Athena

Answer

AWS Step Functions

Answer

Amazon Simple Queue Service (SQS)