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

Question 1

Arrange the steps to create an AWS Glue job that transforms data from Amazon S3 to Amazon Redshift in the correct order.

Accepted Answer

Step 1: Catalog the source data with a crawler. Step 2: Prepare the ETL script. Step 3: Configure the job with connections. Step 4: Run the job. Step 5: Verify the results in Amazon Redshift.. First, catalog the source data with a crawler. Then, prepare the ETL script. Configure the job with connections, run it, and finally verify the results in Redshift.

Answer

Step 1: Prepare the ETL script. Step 2: Catalog the source data with a crawler. Step 3: Configure the job with connections. Step 4: Run the job. Step 5: Verify the results in Amazon Redshift.

Answer

Step 1: Configure the job with connections. Step 2: Catalog the source data with a crawler. Step 3: Prepare the ETL script. Step 4: Run the job. Step 5: Verify the results in Amazon Redshift.

Answer

Step 1: Catalog the source data with a crawler. Step 2: Configure the job with connections. Step 3: Prepare the ETL script. Step 4: Run the job. Step 5: Verify the results in Amazon Redshift.

Question 2

Arrange the steps to implement a data lake on Amazon S3 with AWS Lake Formation.

Accepted Answer

Create S3 bucket, then register it in Lake Formation, then set up administrators, then define schema in Glue Catalog, then grant access to users. Start by creating the S3 bucket. Then register it in Lake Formation, set up administrators, define the schema in Glue Catalog, and finally grant access to users.

Answer

Set up administrators, then create S3 bucket, then register it in Lake Formation, then define schema in Glue Catalog, then grant access to users

Answer

Create S3 bucket, then define schema in Glue Catalog, then register it in Lake Formation, then set up administrators, then grant access to users

Answer

Register S3 bucket in Lake Formation, then create S3 bucket, then set up administrators, then define schema in Glue Catalog, then grant access to users

Question 3

Order the steps to query data in Amazon Redshift Spectrum from an external table in Athena.

Accepted Answer

Create external schema in Redshift, then create external table, then grant permissions, then run the query, then verify results.. Start by creating the external schema in Redshift, then the external table, grant permissions, run the query, and verify results.

Answer

Run the query, then verify results, then create external table, then grant permissions, then create external schema.

Answer

Create external table, then create external schema, then grant permissions, then run the query, then verify results.

Answer

Create external schema, then create external table, then run the query, then grant permissions, then verify results.

Question 4

Order the steps to troubleshoot a failed AWS Glue job that reads from JDBC and writes to S3.

Accepted Answer

Check AWS Glue job logs, then verify network connectivity, then verify IAM permissions, then test JDBC connection, then review the Glue script.. Start with logs to identify errors, then check connectivity, IAM permissions, test connection, and review script.

Answer

Verify IAM permissions, then check AWS Glue job logs, then verify network connectivity, then test JDBC connection, then review the Glue script.

Answer

Test JDBC connection, then check AWS Glue job logs, then verify network connectivity, then verify IAM permissions, then review the Glue script.

Answer

Review the Glue script, then check AWS Glue job logs, then verify network connectivity, then verify IAM permissions, then test JDBC connection.

Question 5

Match each AWS service to its primary purpose in data engineering.

Accepted Answer

Amazon S3: Object storage for data lakes. The correct matches are: Amazon S3 → object storage for data lakes (A), AWS Glue → serverless ETL (B), Amazon Athena → interactive SQL queries on S3 (C), and Amazon Redshift → data warehousing (D). Common confusions involve mixing up services like Kinesis (streaming) with Glue (ETL) or Data Pipeline (orchestration) with Kinesis (ingestion).

Answer

AWS Kinesis: Serverless ETL service

Answer

AWS Data Pipeline: Real-time data ingestion

Question 6

Match each AWS data migration tool to its primary function.

Accepted Answer

AWS Database Migration Service (DMS): Migrates databases to AWS with minimal downtime. AWS DMS migrates databases with minimal downtime; AWS Snowball transfers large datasets physically; AWS DataSync automates online data transfer. Common confusions include swapping tool functions or attributing schema conversion to DataSync instead of SCT.

Answer

AWS Database Migration Service (DMS): Transfers large datasets via physical devices

Answer

AWS Snowball: Migrates databases to AWS with minimal downtime

Answer

AWS DataSync: Converts database schema to target platform

Question 7

Match each AWS database service to its primary use case.

Accepted Answer

Amazon RDS: Relational database for online transaction processing (OLTP) workloads.. Correct matches: RDS -> OLTP relational, DynamoDB -> NoSQL low-latency, Redshift -> data warehousing, ElastiCache -> in-memory caching. Common confusions include swapping RDS and DynamoDB use cases.

Answer

Amazon RDS: NoSQL key-value database for low-latency applications.

Answer

Amazon DynamoDB: Relational database for OLTP workloads.

Question 8

Match each AWS security service to its purpose in data protection.

Accepted Answer

AWS KMS: Manage encryption keys for data at rest and in transit. The correct matches are: AWS KMS for encryption key management, AWS IAM for access control, and AWS CloudTrail for API auditing. Common confusions include swapping these services' purposes.

Answer

AWS KMS: Control access to AWS resources through users, groups, and roles

Answer

AWS IAM: Manage encryption keys for data at rest and in transit

Answer

AWS CloudTrail: Manage encryption keys for data at rest and in transit

Question 9

A company is streaming clickstream data from a website into Amazon Kinesis Data Streams. The data is then consumed by a Lambda function that transforms the records and writes them to an S3 bucket in Parquet format. Recently, the Lambda function has been timing out and the S3 bucket is not receiving all expected records. The Kinesis stream has a shard count of 10 and the Lambda function's reserved concurrency is set to the default. Which change would MOST likely resolve the issue?

Accepted Answer

Decrease the batch window from the default 300 seconds to 60 seconds.. The Lambda function is timing out because it cannot process the default batch of 100 records within the function timeout. Decreasing the batch window from 300 seconds to 60 seconds causes Lambda to invoke more frequently with smaller batches, reducing the number of records per invocation. This lowers the processing time per invocation, helping the function complete before the timeout. Increasing the batch size (Option D) would worsen the issue by adding more records per invocation. Increasing reserved concurrency (Option C) does not reduce per-invocation processing time. Using a delivery stream (Option B) changes the architecture unnecessarily and may not preserve the transformation logic.

Answer

Configure the Kinesis stream to directly write to S3 using a delivery stream.

Answer

Increase the Lambda function's reserved concurrency.

Answer

Increase the batch size from the default 100 to 500 records per invocation.

Question 10

A data engineer is using AWS Glue to perform ETL on data stored in an S3 bucket. The source data is in CSV format with a header row, and the target is a set of Parquet files partitioned by date. The engineer notices that the Glue job is reading all files in the source prefix, including temporary files that should be ignored. What is the MOST efficient way to exclude these temporary files?

Accepted Answer

Use an S3 prefix exclusion pattern in the Glue job's source path.. AWS Glue supports S3 path exclusion patterns using glob-style syntax (e.g., `--exclude` or `excludePatterns` in the job parameters). By specifying a pattern like `**/_temporary/**` or `*.tmp`, the Glue job will skip those files during the read phase, avoiding unnecessary data processing and reducing costs. This is the most efficient approach as it requires no additional infrastructure or data movement.

Answer

Change the source format from CSV to Parquet.

Answer

Set up an S3 event notification to trigger a Lambda function that moves temporary files.

Answer

Create a custom classifier in the Glue Data Catalog.

Question 11

A company uses Amazon Kinesis Data Firehose to ingest JSON logs from multiple sources into an S3 data lake. The data is then consumed by Amazon Athena for analysis. Recently, some queries have been failing with the error 'HIVE_BAD_DATA: Field xyz's type is an unsupported type'. The firehose delivery stream transforms the data using a Lambda function that converts timestamps to Unix epoch. What is the MOST likely cause of the query failure?

Accepted Answer

Some records contain timestamps that were not converted to epoch, so Athena infers the column as a string.. The error 'HIVE_BAD_DATA: Field xyz's type is an unsupported type' occurs when Athena's schema inference encounters inconsistent data types for the same column. Since the Lambda function converts timestamps to Unix epoch, but some records may have failed conversion (e.g., due to malformed input or Lambda errors), those records retain the original string timestamp. Athena then sees a mix of numeric epoch values and string timestamps, causing it to infer the column as a string type, which is unsupported for the expected numeric operations in the query.

Answer

The data is in JSON format instead of Parquet.

Answer

The S3 partitions are not registered in the Glue Data Catalog.

Answer

The IAM role for Firehose does not have permission to write to S3.

Question 12

A data engineer is designing a data ingestion pipeline to load data from an on-premises Oracle database to Amazon S3. The pipeline should capture changes in near real-time (within minutes) and minimize impact on the source database. The source table has a 'last_modified' timestamp column. Which service combination would meet these requirements?

Accepted Answer

AWS DMS with a replication task in CDC mode, writing to S3 in Parquet format.. AWS DMS with a replication task in CDC (Change Data Capture) mode is the correct choice because it continuously reads the Oracle redo logs to capture near real-time changes (within seconds to minutes) with minimal impact on the source database. It can directly write to S3 in Parquet format, meeting the requirement for low-latency ingestion without full table scans.

Answer

Amazon Kinesis Data Firehose with a Lambda function that queries Oracle.

Answer

AWS Data Pipeline with a periodic SQL query activity to copy full table snapshots.

Answer

AWS Glue with a JDBC connection to Oracle, running a crawler every 5 minutes.

Question 13

A company uses AWS Glue to process data from multiple S3 buckets. The Glue job runs daily and reads data from a bucket that contains millions of small files (each < 1 MB). The job has been running for hours and is often close to the 8-hour timeout limit. Which optimization would MOST reduce the job's runtime?

Accepted Answer

Pre-process the data to consolidate small files into larger files before the Glue job.. The most impactful optimization for reducing the runtime of a Glue job that reads millions of small files is to consolidate those files into larger files prior to processing. Each small file incurs overhead for listing, opening, and reading, and Spark's task scheduler must create a separate task for each file or partition. By grouping small files (e.g., via S3 batch operations or a compaction job), the number of files decreases dramatically, reducing scheduling overhead and I/O operations. While converting to Parquet (Option B) and increasing DPUs (Option C) can improve performance, their benefits are limited if the underlying file count remains high. A larger Spark shuffle partition size (Option D) only affects shuffle operations, not the initial file read overhead. Therefore, file consolidation is the most effective single change.

Answer

Convert the source data from CSV to Parquet format.

Answer

Increase the number of DPUs allocated to the Glue job.

Answer

Use a larger Spark shuffle partition size.

Question 14

A data engineer is setting up an Amazon Kinesis Data Firehose delivery stream to load data into Amazon Redshift. The data is coming from an application that produces JSON records. The engineer needs to transform the data to match the Redshift table schema. Which approach is the MOST cost-effective and requires the least operational overhead?

Accepted Answer

Configure a Lambda function in the Firehose delivery stream to transform records before delivery.. Kinesis Data Firehose natively supports invoking a Lambda function as a transformation step within the delivery stream. This allows the engineer to write a simple Lambda function that parses the incoming JSON records and transforms them to match the Redshift table schema, all without provisioning or managing any additional infrastructure. This approach is the most cost-effective (pay per invocation) and requires the least operational overhead since Firehose handles the orchestration, retries, and delivery to Redshift automatically.

Answer

Use AWS Glue as a transformation step between Firehose and Redshift, with a trigger on S3.

Answer

Use Kinesis Data Firehose with direct PUT to Redshift and rely on Redshift's COPY command to transform.

Answer

Use the Kinesis Client Library (KCL) to consume the stream, transform in an EC2 instance, and then load to Redshift.

Question 15

A company uses Amazon S3 to store raw data and AWS Glue to run ETL jobs that transform the data into analytics-ready tables. The Glue job reads from a source with a schema that changes frequently (new columns added). The engineer wants the Glue job to automatically adapt to schema changes without manual intervention. Which configuration should the engineer use?

Accepted Answer

Enable the 'Update schema' option in the Glue job's output target configuration.. Enabling the 'Update schema' option in the Glue job's output target configuration allows the job to automatically add new columns to the target table in the Data Catalog when the source schema changes. This setting directly addresses the requirement for automatic adaptation to schema changes without manual intervention, as it updates the table definition during the ETL job run.

Answer

Schedule a Glue crawler to run after each ETL job to update the Data Catalog.

Answer

Set the job to use schema-on-read by storing data in Parquet format.

Answer

Use Glue's partition indexes to automatically detect new columns.