DEA-C01 Practice Test 25 — 15 Questions

Question 1

A data engineer needs to store transaction data that requires strong consistency, ACID transactions, and complex join queries. Which AWS service is most appropriate?

Accepted Answer

Amazon RDS for PostgreSQL. Amazon RDS for PostgreSQL is the most appropriate choice because it provides full ACID transaction support, strong consistency, and the ability to perform complex join queries using standard SQL. Unlike NoSQL or data warehouse solutions, PostgreSQL is a relational database that excels at enforcing referential integrity and supporting multi-table joins with advanced indexing.

Answer

Amazon DynamoDB

Answer

Amazon S3

Answer

Amazon Redshift

Question 2

A company needs to ingest data from an external API that returns CSV files daily. The files range from 100 MB to 2 GB. The data should be landed in Amazon S3 and then transformed using AWS Glue. Which ingestion method is most cost-effective and requires the least operational overhead?

Accepted Answer

Schedule an AWS Lambda function to download the CSV file and upload it to Amazon S3. Scheduling an AWS Lambda function to download the CSV file from the external API and upload it to Amazon S3 is the most cost-effective and operationally lightweight approach. Lambda can handle files up to 2 GB (with appropriate memory and timeout settings) and runs on a serverless, pay-per-execution model, eliminating the need for infrastructure management. This method directly addresses the daily, batch-oriented nature of the data ingestion without requiring additional services or complex configurations.

Answer

Set up AWS DataSync to transfer the file from the API endpoint to S3

Answer

Use Amazon Kinesis Data Firehose with a direct PUT

Answer

Deploy an AWS Direct Connect connection to the external API for faster transfer

Question 3

Refer to the exhibit.

Exhibit:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::123456789012:role/DataEngineer"
      },
      "Action": [
        "kms:Decrypt",
        "kms:ReEncrypt*"
      ],
      "Resource": "*"
    }
  ]
}

A data engineer tries to encrypt data using the KMS key associated with this key policy and receives an access denied error. What is the cause?

Accepted Answer

The policy does not include kms:Encrypt action. The key policy grants Decrypt and ReEncrypt* actions, but not Encrypt. Therefore, the DataEngineer role does not have permission to encrypt data using this key. Option A is incorrect because IAM roles can be used as principals in key policies. Option B is incorrect because there is no explicit deny statement in the policy. Option C is incorrect because "Resource": "*" is valid in KMS key policies and refers to the current key.

Answer

The principal is an IAM role, which is not allowed

Answer

There is an explicit deny in the policy

Answer

The Resource element is set to "*", which is invalid for KMS key policies

Question 4

Refer to the exhibit. An S3 bucket policy allows the DataEngineerRole to get objects only if the request uses HTTPS. However, requests from this role are being denied even when using HTTPS. What is the MOST likely reason?

Accepted Answer

The IAM role's attached policy does not allow s3:GetObject on the bucket.. Even though the bucket policy condition requires HTTPS, the IAM role must also have an IAM policy that explicitly allows s3:GetObject on the bucket. Without this, the request is denied regardless of the bucket policy. Option A is incorrect because SSE-S3 does not require explicit IAM permissions; it is a server-side encryption option that works by default. Option B is incorrect because aws:SecureTransport is spelled correctly; misspelling would cause the condition to not apply, potentially allowing HTTP, but the issue is requests being denied. Option C is incorrect because the bucket policy uses a condition to require HTTPS, which effectively denies HTTP requests even without a separate Deny statement.

Answer

The IAM role does not have permission to use SSE-S3.

Answer

The condition key aws:SecureTransport is misspelled.

Answer

The bucket policy does not include a Deny statement for HTTP requests.

Question 5

A data engineer applies the above IAM policy to a user. The user attempts to upload an object to the bucket 'my-data-lake' without specifying server-side encryption. What will happen?

Accepted Answer

The upload fails because the condition requires encryption. The IAM policy includes a condition that requires the `s3:x-amz-server-side-encryption` header to be present with a value of `AES256`. When the user attempts to upload an object without specifying server-side encryption, the condition is not satisfied, so the `s3:PutObject` permission is denied. This causes the upload to fail, regardless of any bucket default encryption settings or bucket policies.

Answer

The upload fails only if the bucket has a default encryption setting

Answer

The upload succeeds if the bucket policy allows unencrypted uploads

Answer

The upload succeeds because the policy allows s3:PutObject

Question 6

A data engineer is troubleshooting an Amazon Redshift cluster that has been experiencing slow query performance. The engineer checks the system tables and finds that many queries are waiting on 'wlm_queued' time. The cluster has 10 nodes and uses automatic WLM. What is the most likely cause?

Accepted Answer

The number of concurrent queries exceeds the available query slots.. When queries show 'wlm_queued' time in Amazon Redshift, it indicates they are waiting in the Workload Management (WLM) queue before execution. With automatic WLM, the system dynamically manages concurrency, but if the number of concurrent queries exceeds the available query slots (determined by the cluster's memory and concurrency scaling settings), queries will be queued. This is the most direct cause of 'wlm_queued' wait events, as WLM queues queries when all slots are occupied.

Answer

Network bandwidth saturation between nodes.

Answer

Sorting operations are too expensive.

Answer

Insufficient disk space on the cluster.

Question 7

A company runs a data warehouse on Amazon Redshift. The data engineer notices that some queries are running slowly. Upon reviewing the system tables, the engineer finds that the 'svv_table_info' shows high 'unsorted' percentage for several large tables. What is the MOST effective action to improve query performance?

Accepted Answer

Run the VACUUM command on the tables.. VACUUM sorts the data and reclaims space, improving query performance. Option A is wrong because ANALYZE updates statistics but does not sort. Option C is wrong because increasing the number of nodes may help but is not the most direct fix for unsorted data. Option D is wrong because changing distribution style would require recreating the table.

Answer

Run the ANALYZE command on the tables.

Answer

Change the distribution style of the tables to ALL.

Answer

Increase the number of nodes in the Redshift cluster.

Question 8

A company uses Amazon Kinesis Data Streams to ingest clickstream data from a website. The data is then consumed by a custom application for real-time analytics. Recently, the application has been experiencing high latency. The operations team suspects the shard count is insufficient. How should the team increase the shard count of the existing stream?

Accepted Answer

Use the UpdateShardCount API to increase the shard count for the stream.. The UpdateShardCount API is the correct method to increase the shard count of an existing Kinesis Data Stream without data loss or downtime. It allows you to specify a target shard count, and Kinesis automatically splits shards to achieve that count, redistributing the hash key range across the new shards. This directly addresses the high latency caused by insufficient shard count by increasing the stream's throughput capacity.

Answer

Delete the existing stream and create a new one with a higher shard count.

Answer

Manually split a shard using the SplitShard API on each existing shard.

Answer

Modify the PutRecord calls to include a new shard key that distributes data across more shards.

Question 9

A company runs a MySQL database on Amazon RDS. The database size is 500 GB and is experiencing high read traffic. The team wants to improve read performance with minimal operational overhead. Which action should they take?

Accepted Answer

Create a read replica in the same region. Creating a read replica in the same region offloads read traffic from the primary RDS instance by providing a separate, read-only copy of the database. This directly addresses high read traffic with minimal operational overhead, as RDS manages the asynchronous replication using MySQL's native binlog-based replication. The replica can serve SELECT queries, reducing load on the primary instance without requiring application changes.

Answer

Enable Multi-AZ deployment

Answer

Implement Amazon ElastiCache for caching

Answer

Upgrade to a larger instance class

Question 10

A data engineer is troubleshooting a slow-running Amazon Athena query. The query scans a large amount of data. Which TWO actions can improve query performance? (Choose TWO.)

Accepted Answer

Convert the data to Parquet or ORC format.. Converting data to columnar formats like Parquet or ORC reduces the amount of data scanned, and partitioning the table on frequently filtered columns allows Athena to skip reading irrelevant partitions. Both actions improve query performance. Option B (encryption) does not affect performance. Option C (increasing timeout) only allows more time for a slow query, not improving performance. Option E (SELECT *) scans all columns, which increases data scanned and worsens performance.

Answer

Enable encryption at rest.

Answer

Increase the Athena query timeout.

Answer

Use SELECT * to retrieve all columns.

Question 11

A company is using Amazon Redshift for its data warehouse. A data engineer notices that COPY commands from S3 are failing intermittently with 'S3ServiceException: Access Denied'. The IAM role used by Redshift has the correct permissions. What is the MOST likely cause?

Accepted Answer

The S3 bucket policy denies access to the Redshift cluster's IP addresses.. S3 bucket policies may deny access even if the role allows it. Option A is wrong because the role is already attached. Option B is wrong because encryption would cause different errors. Option C is wrong because if the role exists, it should work; the issue is likely external.

Answer

The IAM role is not attached to the Redshift cluster.

Answer

The S3 bucket uses SSE-KMS encryption and the role lacks kms:Decrypt.

Answer

The IAM role name contains a typo in the COPY command.

Question 12

A data engineer is monitoring an Amazon Kinesis Data Streams application that processes real-time events. The application uses a Kinesis Client Library (KCL) consumer. The engineer notices that the consumer is lagging behind the producer, and the lag is increasing over time. The stream has 10 shards. Which action will MOST effectively reduce the lag?

Accepted Answer

Increase the number of shards in the Kinesis data stream.. Increase the number of shards in the Kinesis data stream. Increasing shards increases the stream's read capacity and allows more parallel consumers to process data, which reduces lag. Option A is incorrect because using multiple KCL workers per shard is not recommended; each shard should be processed by one worker to avoid duplicate processing. Option C is incorrect because decreasing the number of records per shard per second would reduce throughput, not help catch up. Option D is incorrect because decreasing shards reduces capacity, worsening lag.

Answer

Use multiple KCL workers per shard to increase processing capacity.

Answer

Decrease the number of records per shard per second.

Answer

Decrease the number of shards in the Kinesis data stream.

Question 13

A data engineer is designing a data storage solution for IoT sensor data that is ingested at high velocity. The data is time-series and needs to be queried by time range. Which TWO AWS services are suitable for this use case? (Choose TWO)

Accepted Answer

Amazon Timestream. Amazon Timestream is a purpose-built time-series database that automatically scales to handle high-velocity IoT sensor data, with built-in time-based partitioning and query optimization for time-range queries. It supports SQL-like queries with time-series functions (e.g., `BETWEEN`, `DATE_BIN`) and separates storage into a memory store for recent data and a magnetic store for historical data, enabling efficient querying by time range.

Answer

Amazon RDS

Answer

Amazon Redshift

Answer

Amazon S3

Question 14

A data engineer is designing a data ingestion pipeline to load JSON files from Amazon S3 into Amazon Redshift. Which TWO methods can be used to load the data efficiently?

Accepted Answer

Use the Redshift COPY command to load from S3.. The Redshift COPY command is specifically designed to efficiently load large datasets from Amazon S3 by automatically parallelizing the data across cluster nodes, leveraging the cluster's compute resources for high-throughput ingestion. It supports JSON data natively via the 'json' option, making it ideal for loading JSON files directly from S3 without intermediate transformations.

Answer

Use Amazon Kinesis Data Firehose to directly load into Redshift.

Answer

Use AWS DMS to replicate from S3 to Redshift.

Answer

Use individual INSERT statements in a loop.

Question 15

A data engineer needs to design a data ingestion pipeline that captures change data capture (CDC) events from an on-premises SQL Server database to Amazon S3 with low latency. The pipeline must handle schema changes and ensure exactly-once delivery semantics. Which combination of AWS services should the engineer use?

Accepted Answer

AWS Database Migration Service (DMS) with Amazon Kinesis Data Firehose to Amazon S3. AWS DMS can capture ongoing changes from SQL Server using its CDC capability and stream them directly into Amazon Kinesis Data Firehose, which buffers and delivers data to Amazon S3 with low latency. DMS handles schema changes by propagating them to the target, and Kinesis Data Firehose, combined with DMS's transactional integrity, supports exactly-once delivery semantics when configured with a primary key and appropriate error handling.

Answer

AWS AppFlow with SQL Server connector to Amazon S3

Answer

AWS Glue ETL job with JDBC connection to SQL Server and writing to Amazon S3

Answer

Amazon Kinesis Data Streams with AWS Lambda consumer writing to Amazon S3