CCNA Data Store Management Questions — Page 3 of 5

151

MCQhard

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?

A.Network bandwidth saturation between nodes.

B.Sorting operations are too expensive.

C.The number of concurrent queries exceeds the available query slots.

D.Insufficient disk space on the cluster.

AnswerC

High 'wlm_queued' wait time indicates queries are sitting in a workload management queue rather than executing. With automatic WLM, concurrency scaling is not configured here, so when simultaneous queries exceed the query slots automatic WLM allocates per queue, they queue. This directly matches the stem's observed wait event.

Why this answer

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.

Exam trap

The trap here is that candidates confuse 'wlm_queued' with resource contention (like CPU or I/O), but WLM queuing specifically indicates a concurrency limit, not a performance bottleneck during execution.

How to eliminate wrong answers

Option A is wrong because network bandwidth saturation between nodes would manifest as 'network' or 'distributed' wait events in system tables, not 'wlm_queued' time, which is purely a queuing delay. Option B is wrong because expensive sorting operations would appear as 'sort' or 'hash' wait times in query execution plans, not as WLM queue wait; sorting occurs after a query is assigned a slot. Option D is wrong because insufficient disk space would cause 'disk full' errors or 'resize' operations, not WLM queuing; Redshift's WLM queuing is independent of storage capacity.

Practice this question →

152

MCQmedium

A data engineer is designing a data lake on Amazon S3 for a financial analytics workload. The raw data arrives as JSON files from an on-premises system. Analysts need to query the data using Amazon Athena with fast performance and minimal cost for queries that filter on a specific transaction date and customer ID. The engineer wants to convert the data to a columnar format that supports predicate pushdown and compression. Which storage format should the engineer choose?

A.Apache Avro

B.Apache Parquet

C.JSON

D.CSV

AnswerB

Apache Parquet is a columnar format that stores data by column, enabling Athena to read only the columns needed for a query. It supports predicate pushdown, so filters on transaction date and customer ID skip irrelevant data. Parquet also compresses well, reducing data scanned and query cost. This directly meets the requirement for fast, cost-effective queries on filtered columns.

Why this answer

Apache Parquet is a columnar storage format that allows Athena to read only the columns referenced in a query and to skip row groups using predicate pushdown. This reduces the amount of data scanned, lowering cost and improving performance for filters on transaction date and customer ID. Parquet also compresses efficiently, further reducing storage and scan volume.

Exam trap

The trap here is assuming that any compressed format will improve Athena performance, when only columnar formats like Parquet or ORC enable column pruning and predicate pushdown.

Practice this question →

153

MCQhard

A data engineer manages an Amazon Redshift cluster that experiences performance degradation during peak hours due to concurrent long-running queries and short ad-hoc queries competing for resources. The engineer wants to isolate the workloads so that short queries are not blocked by long-running ones, and to ensure that each workload gets a guaranteed share of memory and CPU. Which Redshift feature should the engineer implement?

A.Redshift Spectrum

B.Workload Management (WLM) with query queues

C.Short Query Acceleration (SQA)

D.Concurrency Scaling

AnswerB

WLM allows defining multiple query queues with different memory and concurrency settings. By configuring separate queues for long-running and short ad-hoc queries, the engineer can isolate workloads, ensuring that short queries are not blocked by long ones and that each queue gets a guaranteed share of resources. This directly addresses the scenario.

Why this answer

Workload Management (WLM) enables the creation of separate query queues with configurable memory and concurrency, allowing isolation of long-running and short ad-hoc queries. This ensures that short queries are not blocked and that each workload receives a guaranteed share of resources. Other features like Concurrency Scaling, SQA, and Redshift Spectrum do not provide the same level of workload isolation.

Exam trap

The trap here is confusing Concurrency Scaling or SQA with workload isolation; these features improve performance for certain query types but do not provide dedicated resource queues.

Practice this question →

154

MCQmedium

A data engineer needs to migrate an on-premises MySQL database to Amazon RDS for MySQL with minimal downtime. Which approach should they use?

A.Use mysqldump to export the database and import into RDS.

B.Use AWS Database Migration Service (DMS) with ongoing replication from the source database.

C.Create an RDS read replica and promote it.

D.Use AWS Schema Conversion Tool (SCT) to convert the schema and then copy data.

AnswerB

DMS performs a full load then applies ongoing change data capture replication from the MySQL source, keeping the target synchronised until cutover. This continuous replication is what achieves minimal downtime rather than a one-off snapshot migration.

Why this answer

AWS DMS with ongoing replication (change data capture, CDC) is the correct approach because it allows continuous synchronization from the on-premises MySQL source to the RDS target, enabling a cutover with minimal downtime. Unlike one-time export/import tools, DMS captures ongoing changes during the migration, so the target stays up-to-date until you switch over.

Exam trap

The trap here is that candidates confuse 'minimal downtime' with 'zero data loss' and assume a simple dump/import or a read replica (which only works for RDS-to-RDS) is sufficient, overlooking the need for ongoing replication to keep the target synchronized during the migration window.

How to eliminate wrong answers

Option A is wrong because mysqldump performs a logical backup that requires the source database to be locked or read-only during the dump, causing significant downtime; it also does not support ongoing replication. Option C is wrong because RDS read replicas can only be created from an existing RDS instance, not from an on-premises database, and promoting a replica does not migrate data from an external source. Option D is wrong because AWS Schema Conversion Tool (SCT) is designed for heterogeneous migrations (e.g., Oracle to Aurora) and does not handle data replication; for a homogeneous MySQL-to-MySQL migration, SCT is unnecessary and does not provide ongoing sync.

Practice this question →

155

MCQhard

A company uses DynamoDB with global tables in two AWS Regions. The data engineer observes that a write to the table in us-east-1 is not immediately visible in a read from eu-west-1. What is the most likely reason?

A.Replication between regions is eventually consistent.

B.The read is using strongly consistent reads.

C.There is a write conflict that needs to be resolved.

D.DynamoDB Streams is not enabled on the table.

AnswerA

DynamoDB global tables replicate asynchronously, so a write committed in us-east-1 propagates to eu-west-1 only after a short delay. Reads in the remote Region can therefore return stale data until replication completes, which explains the observed lag.

Why this answer

DynamoDB global tables use asynchronous replication between regions. When a write occurs in us-east-1, the change is propagated to eu-west-1 with a replication lag that is typically sub-second but not instantaneous. Reads in eu-west-1 are eventually consistent by default, meaning they may not reflect the most recent write until replication completes.

This is the expected behavior of DynamoDB global tables, which prioritize availability and partition tolerance over immediate consistency across regions.

Exam trap

The trap here is that candidates often assume DynamoDB global tables provide strong consistency across regions because they are familiar with single-region strongly consistent reads, but the exam tests the specific knowledge that cross-region replication is always eventually consistent and that strongly consistent reads are only valid within the same region.

How to eliminate wrong answers

Option B is wrong because strongly consistent reads would actually increase the chance of seeing stale data in a cross-region scenario, as they are only guaranteed to return the most recent write within the same region, not across regions; DynamoDB does not support cross-region strongly consistent reads. Option C is wrong because write conflicts in global tables are automatically resolved using a last-writer-wins algorithm based on the timestamp, and they do not cause writes to be invisible; a conflict would result in one write being overwritten, not a delay in visibility. Option D is wrong because DynamoDB Streams is not required for global table replication; global tables use their own internal replication mechanism, and enabling Streams is optional for change data capture or triggering Lambda functions, not for the core replication functionality.

Practice this question →

156

Multi-Selecthard

A company uses Amazon Redshift for analytics. They notice that some queries are slow due to data redistribution. The data engineer wants to minimize data movement across nodes. Which table design strategy should be used? (Choose TWO.)

Select 2 answers

A.Set the distribution style to AUTO for all tables.

B.Define compound sort keys on frequently filtered columns.

C.Choose a distribution key that matches the join key for large tables.

D.Use EVEN distribution for all tables.

E.Use distribution style ALL for small dimension tables.

AnswersC, E

When two large tables share the same distribution key as their join column, matching rows are already co-located on the same slice. Redshift avoids the broadcast or shuffle step during joins, eliminating the cross-node data redistribution that was slowing queries.

Why this answer

Option C is correct because choosing a distribution key that matches the join key on large tables colocates matching rows on the same compute node slice, so joins between those tables can be performed locally without broadcasting or redistributing data across nodes. Option E is correct because using distribution style ALL replicates small dimension tables to every node, eliminating the need to redistribute the dimension during joins with large fact tables and thereby minimizing cross-node data movement. Option A is not ideal here because AUTO lets Redshift decide and may still choose EVEN or KEY distribution that results in redistribution for some workloads, rather than guaranteeing join-key alignment.

Option B addresses sort keys, which optimize range-restricted scans and merge joins but do not control how rows are distributed across nodes, so they do not directly reduce redistribution. Option D is incorrect because EVEN distribution spreads rows round-robin regardless of join keys, which typically forces data redistribution during joins and can worsen the problem.

Exam trap

The trap here is that candidates often confuse distribution keys with sort keys, thinking that sorting alone can reduce data movement, or they assume AUTO distribution always optimizes for joins, when in fact it may default to EVEN or ALL without guaranteeing collocation for specific join patterns.

Practice this question →

157

Drag & Dropmedium

Order the steps to set up an Amazon EMR cluster for processing data in S3 using Spark.

Drag or tap steps into the slots.

Steps

Order

1Step 1

2Step 2

3Step 3

4Step 4

Why this order

First, prepare the S3 bucket. Then launch the EMR cluster with Spark, configure instances, submit the job, and terminate.

Practice this question →

158

MCQmedium

A company is migrating its on-premises Oracle database to Amazon Aurora PostgreSQL. The migration must have minimal downtime. The source database is 2 TB and runs on a single server. Which AWS service should be used for the migration?

A.AWS DataSync

B.Amazon S3 Transfer Acceleration

C.AWS Database Migration Service (DMS)

D.AWS Snowball Edge

AnswerC

DMS provides minimal downtime migration with change data capture.

Why this answer

AWS Database Migration Service (DMS) is the correct choice because it supports homogeneous migrations from Oracle to Amazon Aurora PostgreSQL with minimal downtime using ongoing replication (change data capture). DMS can handle a 2 TB source database by performing a full load followed by continuous replication of changes from the Oracle redo logs, allowing the target Aurora database to stay nearly in sync until cutover.

Exam trap

The trap here is that candidates may confuse AWS DataSync or Snowball Edge as viable for database migrations because they handle large data volumes, but they lack the schema conversion and ongoing replication capabilities required for minimal-downtime database migrations.

How to eliminate wrong answers

Option A is wrong because AWS DataSync is designed for moving large datasets over the network between on-premises storage and AWS storage services (e.g., S3, EFS, FSx), not for database migrations with schema conversion and ongoing replication. Option B is wrong because Amazon S3 Transfer Acceleration only speeds up uploads to S3 buckets over the internet using optimized network paths; it does not migrate databases or handle schema conversion and CDC. Option D is wrong because AWS Snowball Edge is a physical data transfer device for offline bulk data movement, which would introduce significant downtime and cannot perform live replication or schema conversion for a database migration.

Practice this question →

159

MCQeasy

A data engineer is setting up an Amazon Redshift cluster and needs to load data from Amazon S3. The data is in CSV format and contains a large number of rows. The engineer wants to achieve the fastest possible load time. Which method should the engineer use?

A.Use Amazon Kinesis Data Firehose to stream the data from S3 to Redshift.

B.Use AWS Database Migration Service (AWS DMS) to migrate the data from S3 to Redshift.

C.Use the COPY command to load the data from Amazon S3 into the Redshift cluster.

D.Use the INSERT INTO command to load the data row by row from S3.

AnswerC

The COPY command is the most efficient way to load large datasets from Amazon S3 into Redshift. It leverages massively parallel processing, automatically distributes the load across all nodes, and can parse CSV files directly. It also supports compression and columnar formats for even faster loads, making it ideal for this scenario.

Why this answer

The COPY command is specifically designed for bulk loading data from Amazon S3 into Redshift. It uses parallel processing to load data quickly and efficiently, supports CSV format, and can handle large volumes. Other methods like DMS or Firehose are not optimized for this use case, and INSERT is too slow.

Exam trap

The trap here is assuming that any AWS data transfer service can load data quickly, but only COPY is optimized for bulk loading into Redshift from S3.

Practice this question →

160

MCQhard

A data engineer maintains an Amazon DynamoDB table that stores device telemetry. The table uses a partition key of deviceId and a sort key of timestamp, with on-demand capacity mode. A new fleet of devices writes data with a deviceId pattern that hashes to a small number of partitions, and the engineer observes throttling on writes even though consumed capacity is well below any configured limit. Which change addresses the root cause?

A.Create a global secondary index on timestamp and direct all writes through that index to distribute the load.

B.Switch the table from on-demand to provisioned capacity mode and raise the write capacity units substantially.

C.Redesign the key schema to use a higher-cardinality partition key, such as a composite of deviceId and a shard suffix, while keeping timestamp as the sort key.

D.Enable DynamoDB Streams on the table so that write operations are buffered and replayed during peak periods.

AnswerC

DynamoDB partitions data by the hash of the partition key, so a low-cardinality key concentrates writes onto a few partitions and triggers throttling regardless of overall table capacity. Introducing a shard suffix spreads writes across many partitions while the sort key still allows efficient range queries per device, directly removing the hot-partition bottleneck.

Why this answer

Throttling at low consumed capacity on a DynamoDB table almost always indicates a hot partition caused by an uneven partition key. Because data is distributed by the hash of the partition key, a key with few distinct values funnels traffic into a small number of partitions. Adding a shard suffix to the partition key spreads the writes while preserving sort-key range queries.

Exam trap

The trap here is treating throttling as a capacity shortage and raising throughput, when the real cause is a partition key whose hash space is too small.

Practice this question →

161

MCQhard

A data engineer is designing a data lake on Amazon S3 and needs to catalog data using the AWS Glue Data Catalog. The data is stored in Parquet format, partitioned by year/month/day. The engineer wants to query the data using Amazon Athena and ensure that partition pruning occurs to minimize query costs. Which action should the engineer take?

A.Use AWS Glue crawlers to automatically discover the schema and partitions, then run MSCK REPAIR TABLE in Athena.

B.Use AWS Glue ETL jobs to write data to S3 in a non-partitioned layout and rely on Athena's predicate pushdown.

C.Manually define the table in AWS Glue Data Catalog with partition keys and use Athena's ALTER TABLE ADD PARTITION for each partition.

D.Configure the table in AWS Glue Data Catalog with partition projection using the appropriate storage descriptor and table properties.

AnswerD

Partition projection allows Athena to calculate partition locations dynamically based on table properties, eliminating the need to manually add partitions or run crawlers. This enables efficient partition pruning and reduces query costs, especially for highly partitioned datasets like year/month/day.

Why this answer

Partition projection in AWS Glue Data Catalog enables Athena to dynamically compute partition locations, avoiding the need to manage partitions manually. It supports efficient partition pruning, reducing the amount of data scanned and lowering query costs. This is the recommended approach for highly partitioned datasets with a predictable structure.

Exam trap

The trap here is assuming that running MSCK REPAIR TABLE or using Glue crawlers is sufficient for partition pruning, when in fact partition projection provides a more scalable and cost-effective solution.

Practice this question →

162

MCQmedium

A data engineer runs the AWS CLI command to retrieve the lifecycle configuration of the 'my-data-lake' bucket. The output is shown in the exhibit. What is the effect of this lifecycle policy?

A.Objects in the 'logs/' prefix are deleted after 365 days and their delete markers are removed.

B.All objects in the bucket are moved to STANDARD_IA after 30 days.

C.Objects in the 'logs/' prefix are moved to S3 Standard-IA after 30 days, to Glacier after 90 days, and deleted after 365 days.

D.Objects in the 'logs/' prefix are moved to Glacier after 90 days and expired after 90 days.

AnswerC

The lifecycle rule scopes transitions to the logs/ prefix: objects transition to S3 Standard-IA at day 30, then to Glacier at day 90, and expire at day 365. This matches the exhibit's prefix filter and staged transition actions, satisfying the retention requirement stated in the policy.

Why this answer

The lifecycle policy explicitly applies to the 'logs/' prefix, transitioning objects to S3 Standard-IA after 30 days, then to Glacier after 90 days, and finally expiring (deleting) them after 365 days. The 'Expiration' action with 'Days: 365' permanently removes the objects, while the 'Transitions' define the storage class changes at the specified intervals.

Exam trap

The trap here is that candidates often overlook the prefix filter and assume the policy applies to the entire bucket, or they misread the expiration as occurring at 90 days instead of 365 days, leading to incorrect answers like B or D.

How to eliminate wrong answers

Option A is wrong because the lifecycle policy does not include any action to remove delete markers; the 'Expiration' action simply deletes the objects after 365 days, and delete marker removal would require a separate 'ExpiredObjectDeleteMarker' setting. Option B is wrong because the policy only applies to objects under the 'logs/' prefix, not to all objects in the bucket, and the transition to STANDARD_IA occurs after 30 days, not immediately. Option D is wrong because it omits the initial transition to S3 Standard-IA after 30 days and incorrectly states that objects are expired after 90 days, whereas the actual expiration is after 365 days.

Practice this question →

163

Multi-Selectmedium

A data engineer is optimizing an Amazon S3 data lake that stores large volumes of JSON logs. The engineer wants to reduce storage costs and improve query performance in Amazon Athena. Which TWO actions should the engineer take? (Choose two.)

Select 2 answers

A.Enable S3 Transfer Acceleration for the bucket.

B.Convert the JSON logs to Apache Parquet format.

C.Use S3 Standard-Infrequent Access (S3 Standard-IA) storage class for all log data.

D.Enable S3 server access logging on the bucket.

E.Partition the data by date and other commonly filtered columns.

AnswersB, E

Parquet is a columnar format that compresses data more efficiently than JSON and allows Athena to read only the columns needed for a query. This reduces storage costs and improves query performance by minimizing data scanned. Converting to Parquet is a best practice for optimizing Athena queries and reducing S3 storage costs.

Why this answer

Converting JSON logs to Parquet reduces storage size and improves Athena query performance by enabling columnar reads and better compression. Partitioning the data by commonly filtered columns like date allows Athena to skip irrelevant data, reducing the amount scanned and lowering costs. These two actions together address both storage cost reduction and query performance improvement.

Exam trap

The trap here is selecting actions that improve data transfer or auditing but do not affect storage costs or query performance in Athena.

Practice this question →

164

MCQhard

A data engineer is building an Amazon DynamoDB table that will store IoT sensor readings. Each reading has a deviceId (partition key) and a timestamp (sort key). The team wants to retrieve all readings for a device within the last 24 hours, and they also want to minimize the number of read capacity units consumed. Which access pattern should the engineer implement?

A.Create a global secondary index on timestamp and Query it with a key condition on the timestamp range.

B.Issue a Query operation with the partition key equal to the deviceId and a key condition expression on the sort key using a BETWEEN clause for the timestamp range.

C.Use a parallel Scan with multiple segments and a FilterExpression on deviceId.

D.Issue a Scan operation on the table with a FilterExpression on deviceId and timestamp.

AnswerB

Query targets a single partition, and adding a key condition on the sort key restricts the read to the requested timestamp range. DynamoDB reads only the items within that range, so consumed read capacity is proportional to the matching items, which is the most efficient way to satisfy the access pattern described.

Why this answer

The table already has deviceId as the partition key and timestamp as the sort key, which is the ideal design for retrieving a device's readings over a time range. Querying that partition with a BETWEEN condition on the sort key reads only the matching items, minimizing consumed read capacity. Scan, parallel Scan, and a GSI on timestamp all read or return excessive data.

Exam trap

The trap here is reaching for a secondary index or a Scan when the base table's composite key already supports the access pattern, wasting capacity and money.

Practice this question →

165

MCQmedium

Refer to the exhibit. A data engineer runs the above CLI command and sees the output. The security team requires that the RDS instance not be accessible from the internet. Which change should the engineer make?

A.Change the storage type to io1 for better performance.

B.Modify the DB instance to set PubliclyAccessible to false.

C.Enable Multi-AZ deployment to improve security.

D.Update the VPC security group to deny inbound traffic from 0.0.0.0/0.

AnswerB

Setting PubliclyAccessible to false removes the instance's public IP and prevents internet routing, directly satisfying the security team's requirement. Security group rules alone do not make an RDS instance private, so this attribute change is the correct control.

Why this answer

Setting the `PubliclyAccessible` attribute to `false` ensures that the RDS instance is not assigned a public IP address and is not reachable from the internet. This directly satisfies the security team's requirement, as the instance will only be accessible from within the VPC. The CLI command shown modifies the DB instance, and this parameter is the standard AWS mechanism to control internet accessibility for RDS.

Exam trap

The DEA-C01 exam often tests the misconception that modifying a security group to block all inbound traffic is sufficient to make an RDS instance private, but the trap here is that the instance can still have a public IP address and be reachable from the internet if the security group rule is later removed or if the instance is in a public subnet.

How to eliminate wrong answers

Option A is wrong because changing the storage type to io1 (provisioned IOPS) improves I/O performance, not security or internet accessibility. Option C is wrong because enabling Multi-AZ deployment provides high availability and failover support, but does not restrict internet access; it can still leave the instance publicly accessible. Option D is wrong because updating the VPC security group to deny inbound traffic from 0.0.0.0/0 is a valid security measure, but it does not prevent the RDS instance from having a public IP address; the instance could still be assigned a public IP and be reachable if the security group rule is misconfigured or overridden, making this an incomplete solution compared to directly setting PubliclyAccessible to false.

Practice this question →

166

MCQmedium

A data engineer is designing a data lake on Amazon S3 and needs to ensure that objects are automatically encrypted at rest using server-side encryption with AWS KMS. Which bucket policy statement achieves this?

A.Deny PutObject requests where the x-amz-server-side-encryption header is not set to aws:kms.

B.Deny PutObject requests that do not include the x-amz-server-side-encryption header.

C.Deny PutObject requests where the x-amz-server-side-encryption header is not set to AES256.

D.Allow PutObject requests only if the x-amz-server-side-encryption header is set to AES256.

AnswerA

A bucket policy condition on `s3:PutObject` can require the `x-amz-server-side-encryption` header to equal `aws:kms`, denying any upload that omits SSE-KMS. This enforces encryption at rest with AWS KMS at the API layer, satisfying the stem's automatic SSE-KMS requirement regardless of client defaults.

Why this answer

It enforces server-side encryption with AWS KMS (SSE-KMS) by denying any PutObject request that does not include the `x-amz-server-side-encryption` header set to `aws:kms`. This bucket policy ensures that all objects written to the S3 bucket are automatically encrypted at rest using AWS KMS, meeting the requirement for mandatory encryption with a specific key management service.

Exam trap

The trap here is that candidates often confuse the encryption header values (`aws:kms` vs `AES256`) and mistakenly choose an option that enforces SSE-S3 (AES256) instead of SSE-KMS, or they pick a Deny statement that only checks for the presence of the header without validating its specific value.

How to eliminate wrong answers

Option B is wrong because it denies PutObject requests that do not include the `x-amz-server-side-encryption` header at all, but it does not enforce the use of `aws:kms`; a request with the header set to `AES256` (SSE-S3) would still be denied, which is overly restrictive and not aligned with the requirement for KMS encryption. Option C is wrong because it denies PutObject requests where the header is not set to `AES256`, which would enforce SSE-S3 instead of SSE-KMS, directly contradicting the requirement for AWS KMS encryption. Option D is wrong because it allows PutObject requests only if the header is set to `AES256`, which again enforces SSE-S3, not SSE-KMS, and an Allow statement alone does not block requests that omit the header entirely, leaving a gap for unencrypted uploads.

Practice this question →

167

MCQmedium

A data engineer manages an Amazon DynamoDB table that stores IoT sensor readings. Each item has a partition key of deviceId and a sort key of timestamp. The table is configured with on-demand capacity mode. The engineer needs to retrieve all readings for a specific device within the last 24 hours, and the query must return results sorted by timestamp in ascending order. Which operation should the engineer use?

A.Call the Query API with the partition key value and a condition on the sort key using the BETWEEN operator.

B.Call the GetItem API with the partition key and sort key values.

C.Call the Scan API with a FilterExpression on deviceId and timestamp.

D.Call the BatchGetItem API with a list of partition and sort key pairs.

AnswerA

The Query API requires a partition key value and allows an optional sort key condition. Using BETWEEN on the timestamp sort key efficiently retrieves the desired range. Results are returned in sorted order by the sort key, satisfying the ascending requirement. This is the most efficient and appropriate operation for this access pattern.

Why this answer

The Query API is designed for efficient retrieval of items sharing a partition key, with optional sort key conditions. Using BETWEEN on the timestamp sort key retrieves exactly the last 24 hours of readings for a device. Results are automatically sorted by the sort key, meeting the ascending order requirement.

Scan, GetItem, and BatchGetItem do not provide this combination of range filtering and sorted output.

Exam trap

The trap here is assuming that Scan with a filter is equivalent to Query for range-based access patterns, but Scan is inefficient and does not return sorted results.

Practice this question →

168

MCQhard

A data engineer is managing an Amazon DynamoDB table that stores user session data. The table has a partition key of user_id and a sort key of session_start_time. The engineer needs to retrieve all sessions for a specific user that started within the last 30 days. Which operation should the engineer use to achieve the lowest latency?

A.Use the Scan operation with a filter expression on user_id and session_start_time.

B.Use the GetItem operation with the user_id and a session_start_time range.

C.Use the Query operation with the partition key set to the user_id and a condition on the sort key to filter sessions from the last 30 days.

D.Create a global secondary index (GSI) on user_id and session_start_time, then query the index.

AnswerC

The Query operation retrieves items with a specific partition key and can apply a condition on the sort key. Since the table's sort key is session_start_time, the engineer can query for a specific user_id and use a condition like session_start_time >= <30 days ago>. This efficiently retrieves only the relevant items and provides low latency by avoiding a full table scan.

Why this answer

The Query operation is designed to retrieve multiple items with the same partition key and can filter on the sort key. Since the table's schema matches the access pattern, querying the base table with a sort key condition yields the lowest latency and consumes minimal read capacity. Other operations either scan the entire table or are not suited for range retrievals.

Exam trap

The trap here is thinking that a GSI is needed for efficient queries, but the base table already has the right key schema, so a GSI would add unnecessary overhead.

Practice this question →

169

MCQmedium

A company is using an Amazon RDS for MySQL database for an e-commerce application. During a sales event, the database experiences high read traffic, causing slow query performance. The company wants to reduce the read load on the primary database without changing the application code. Which solution meets these requirements?

A.Enable Multi-AZ on the RDS instance.

B.Create an Amazon RDS read replica and direct read traffic to it.

C.Increase the instance size of the RDS database.

D.Deploy Amazon ElastiCache to cache query results.

AnswerB

Read replicas handle read-only traffic, reducing load on the primary.

Why this answer

An Amazon RDS read replica is a read-only copy of the primary database that can offload read traffic without requiring any application code changes. By directing read queries to the replica, the primary database's load is reduced, improving performance during high-read events. This solution is specifically designed for read-heavy workloads and integrates seamlessly with existing MySQL connections.

Exam trap

The trap here is that candidates often confuse Multi-AZ with read replicas, assuming Multi-AZ provides read scaling, when in fact Multi-AZ only ensures failover redundancy and does not serve read traffic.

How to eliminate wrong answers

Option A is wrong because enabling Multi-AZ provides high availability through a standby replica in a different Availability Zone, but it does not offload read traffic; the standby is not used for reads unless a failover occurs. Option C is wrong because increasing the instance size scales the primary database vertically, which can improve performance but does not reduce read load on the primary instance and may incur higher costs without addressing the read traffic distribution. Option D is wrong because deploying Amazon ElastiCache caches query results in memory, which can reduce database load, but it requires application code changes to implement caching logic, violating the requirement of no code changes.

Practice this question →

170

MCQeasy

A data engineer is setting up an Amazon S3 bucket to store large CSV files that will be queried using Amazon Athena. The engineer wants to minimize query costs and improve performance. The files are currently stored in a single prefix without any partitioning. The most common queries filter data by `year` and `month`. What should the engineer do to optimize the Athena queries?

A.Compress the CSV files using gzip and keep them in a single prefix.

B.Convert the CSV files to Apache Parquet format and partition the data by `year` and `month`.

C.Create an AWS Glue Data Catalog table with partition keys `year` and `month`, and use Amazon Athena to query the CSV files directly.

D.Use Amazon Redshift Spectrum to query the CSV files in S3 instead of Athena.

AnswerB

Converting to Parquet reduces the amount of data scanned because Parquet is columnar and compressed, and partitioning by `year` and `month` allows Athena to prune partitions based on query filters. This significantly lowers query cost and improves performance. Athena charges based on data scanned, so both optimizations directly reduce cost. This is a best practice for optimizing Athena queries on large datasets.

Why this answer

Converting data to Apache Parquet and partitioning by commonly filtered columns like `year` and `month` are the most effective ways to reduce data scanned by Athena. Parquet's columnar format allows Athena to read only the columns needed, and partitioning enables partition pruning. Together, they minimize query cost and improve performance.

Other options either do not address the core optimizations or introduce unnecessary complexity.

Exam trap

The trap here is thinking that simply defining partition keys in the Glue Data Catalog without reorganizing the data will enable partition pruning.

Practice this question →

171

MCQmedium

A data engineer is troubleshooting a slow-running query on an Amazon Redshift cluster. The query involves joining two large tables. The engineer notices that the query plan shows a large number of distribution and broadcast operations. Which design change would most likely improve query performance?

A.Change the distribution style of both tables to ALL

B.Change the distribution style of both tables to KEY on the join column

C.Change the distribution style of both tables to EVEN

D.Add a sort key on the join column

AnswerB

Distributing both tables on the join column co-locates matching rows on the same slice, so Redshift performs local joins instead of redistributing or broadcasting data across nodes. This directly eliminates the distribution and broadcast operations the plan revealed, satisfying the stem's requirement to reduce network overhead during large-table joins.

Why this answer

Changing the distribution style of both tables to KEY on the join column ensures that rows with the same join key value are co-located on the same node. This eliminates the need for expensive broadcast or redistribution operations during the join, as Redshift can perform the join locally on each slice without moving data across the network.

Exam trap

The trap here is that candidates often confuse distribution and sort keys, thinking a sort key on the join column will reduce data movement, when in fact only distribution key alignment eliminates broadcast/redistribution operations in the query plan.

How to eliminate wrong answers

Option A is wrong because setting both tables to ALL distribution replicates the entire table to every node, which increases storage and maintenance overhead, and does not address the root cause of excessive data movement during joins; it can also degrade performance for large tables due to increased load and memory pressure. Option C is wrong because EVEN distribution distributes rows round-robin across nodes, which does not co-locate join keys and forces Redshift to redistribute or broadcast rows during the join, exacerbating the problem. Option D is wrong because adding a sort key on the join column improves the efficiency of range-restricted scans and merge joins but does not reduce the number of distribution or broadcast operations; the query plan's large number of such operations indicates a distribution mismatch, not a sorting issue.

Practice this question →

172

Matchingmedium

Match each AWS Glue component to its role.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts

Matches

Scans data sources and populates catalog

Central metadata repository

Transform and load data

Orchestrates multiple jobs and crawlers

Interactive development environment

Why these pairings

AWS Glue components work together for ETL. The Data Catalog stores metadata, Crawlers populate the catalog, Classifiers infer schema, and ETL Jobs execute transformations. Connections provide connectivity but are not listed here.

Practice this question →

173

MCQmedium

A data engineer is designing a data lake on Amazon S3 and needs to store data in a format that supports schema evolution and efficient columnar storage. The data will be queried using Amazon Athena and Amazon Redshift Spectrum. The engineer wants to minimize storage costs and improve query performance. Which storage format should the engineer choose?

A.Apache Avro

B.CSV

C.Apache Parquet

D.JSON

AnswerC

Parquet is a columnar storage format that provides efficient compression and encoding schemes, reducing storage costs and improving query performance by allowing column pruning and predicate pushdown. It supports schema evolution and is widely used with Athena and Redshift Spectrum. This makes it ideal for the described requirements.

Why this answer

Apache Parquet is a columnar format that offers high compression and efficient encoding, reducing storage costs and enabling fast query performance through column pruning and predicate pushdown. It supports schema evolution and is well-integrated with Athena and Redshift Spectrum, making it the best choice.

Exam trap

The trap here is assuming that any format supporting schema evolution is sufficient, but columnar storage is critical for analytical query performance and cost reduction.

Practice this question →

174

MCQmedium

A company uses Amazon S3 to store historical stock market data as CSV files. They run daily Amazon Athena queries to generate reports. Recently, the finance team reported that queries are timing out and costs have increased significantly. The data engineering team notices that the S3 bucket contains thousands of small files (average 100 KB) due to a misconfigured ingestion pipeline. They need to improve query performance and reduce costs without changing the existing reporting schedule. The team has access to AWS Glue and can create new tables. Which solution should they implement?

A.Partition the data by date and create a new Athena table with partitions.

B.Use S3 Select to filter rows within each file before Athena processes them.

C.Increase the Athena query timeout to 30 minutes.

D.Use AWS Glue ETL to read the CSV files, convert them to Parquet, and write them back to S3 in fewer, larger files.

AnswerD

Parquet is columnar, so Athena reads only referenced columns, and consolidating thousands of 100 KB files into fewer larger objects cuts per-file overhead and request costs. Glue ETL performs the conversion, improving performance while keeping the daily schedule unchanged.

Why this answer

Converting the thousands of small CSV files into fewer, larger Parquet files using AWS Glue ETL directly addresses the root cause of poor Athena performance and high costs. Parquet is a columnar format that reduces the amount of data scanned per query, and larger files minimize the overhead of S3 LIST and GET operations, improving throughput. This solution does not change the reporting schedule and leverages existing Glue capabilities to create new optimized tables.

Exam trap

The trap here is that candidates often assume partitioning (Option A) is a universal performance fix, but they overlook that partitioning does not address the 'small files problem' which is a distinct performance killer in Athena due to S3 request overhead and file open costs.

How to eliminate wrong answers

Option A is wrong because partitioning by date does not solve the problem of thousands of tiny files; while partitioning can help prune scanned data, the overhead of reading many small files per partition still causes high latency and cost due to excessive S3 API calls. Option B is wrong because S3 Select operates at the object level to filter rows within a single file, but it does not consolidate files or change the file format; Athena would still need to process thousands of small files, and S3 Select cannot be used directly within Athena queries to replace table scans. Option C is wrong because increasing the query timeout does not reduce the amount of data scanned or the number of S3 requests; it merely allows the query to run longer without addressing the performance bottleneck or cost issue.

Practice this question →

175

MCQmedium

A company is migrating an on-premises Hadoop cluster to AWS. The cluster processes large files in CSV format using Apache Spark. Which data store should be used as the primary storage for the data lake to optimize cost and performance?

A.Amazon EMR File System (EMRFS) backed by HDFS

B.Amazon RDS for MySQL

C.Amazon EBS volumes attached to the EMR cluster

D.Amazon S3

AnswerD

Amazon S3 provides durable, virtually unlimited object storage with separate compute and storage scaling, so Spark reads CSV files directly and cost stays low. HDFS on Amazon EMR couples storage to cluster lifetime, raising cost and complicating elasticity.

Why this answer

Amazon S3 is the optimal primary storage for a data lake on AWS because it offers high durability, scalability, and cost-effectiveness. It integrates seamlessly with Apache Spark on Amazon EMR, allowing direct access to data without moving it. S3 also supports various file formats and decouples storage from compute, enabling independent scaling.

Exam trap

DEA-C01 often tests the misconception that HDFS or EBS is suitable for a data lake, but they are not cost-effective or scalable for long-term storage. Candidates might choose EMRFS with HDFS due to familiarity with Hadoop.

How to eliminate wrong answers

Option A is wrong because EMRFS backed by HDFS is not cost-effective for a data lake; HDFS is designed for temporary storage on cluster nodes and is not durable or scalable for long-term data. Option B is wrong because Amazon RDS for MySQL is a relational database, not suitable for storing large CSV files for big data processing. Option C is wrong because EBS volumes are block storage attached to EC2 instances, not a shared data lake storage; they are limited in size and not durable across cluster termination.

Practice this question →

176

MCQhard

A financial services company stores transactional records in Amazon DynamoDB. Auditors require that any item be recoverable to its exact state from any point within the last 30 days, including after an accidental delete or overwrite caused by a faulty deployment. The table uses on-demand capacity and must remain highly available during recovery. Which feature should the data engineer enable?

A.DynamoDB global tables with multi-Region replication

B.DynamoDB on-demand backups

C.DynamoDB Streams with an AWS Lambda consumer writing to Amazon S3

D.DynamoDB point-in-time recovery (PITR)

AnswerD

PITR continuously backs up the table with per-second granularity and lets you restore to any point in the preceding 35 days, which covers the 30-day audit requirement. It protects against accidental writes and deletes because the restore uses the continuous backup stream, not a periodic snapshot. Restores create a new table with the chosen timestamp, preserving availability of the source table.

Why this answer

DynamoDB point-in-time recovery maintains a continuous backup with per-second granularity and supports restore to any moment within the preceding 35 days, which exceeds the 30-day audit window. Because it captures every write and delete, a faulty deployment that corrupts or removes items can be undone by restoring to a timestamp just before the incident. The restore produces a new table, so the production table stays available throughout.

Exam trap

The trap here is confusing periodic on-demand backups or multi-Region replication with continuous point-in-time recovery, when only PITR can restore to an arbitrary second after an accidental delete.

Practice this question →

177

Multi-Selecthard

Which THREE factors should a data engineer consider when choosing between Amazon RDS and Amazon DynamoDB for a new application? (Choose three.)

Select 3 answers

A.Whether the workload requires serverless scaling.

B.Whether the data model is relational or key-value.

C.Whether the data must be encrypted at rest by default.

D.Whether the application requires VPC isolation.

E.Whether the application needs to scale horizontally for high throughput.

AnswersA, B, E

DynamoDB is serverless; RDS requires manual scaling.

Why this answer

Amazon RDS is a relational database service that requires provisioning and managing server capacity, while DynamoDB is a fully managed NoSQL key-value and document database that supports serverless scaling. Option A is correct because DynamoDB can automatically scale throughput capacity up or down based on traffic patterns, making it suitable for unpredictable workloads, whereas RDS requires manual scaling or the use of Auto Scaling with predefined policies.

Exam trap

The trap here is that candidates mistakenly think encryption at rest or VPC isolation are exclusive to one service, when in fact both RDS and DynamoDB support these features, making them irrelevant as differentiators.

Practice this question →

178

MCQeasy

A data engineer needs to set up a new Amazon RDS for MySQL database for a web application. The application experiences variable read traffic and requires low read latency. The engineer needs to minimize downtime during maintenance and provide read scalability. Which configuration meets these requirements?

A.Multi-AZ db.r5.large instance with two Read Replicas

B.Multi-AZ db.r5.large instance

C.Single-AZ db.r5.large instance

D.Single-AZ db.r5.xlarge instance

AnswerA

Multi-AZ deployment provides automatic failover to a standby in a second Availability Zone, minimising downtime during maintenance, while Read Replicas offload read traffic to scale reads and reduce latency. This satisfies the variable read traffic, low read latency, and read scalability requirements.

Why this answer

A Multi-AZ deployment provides high availability and automatic failover to minimize downtime during maintenance, while adding two Read Replicas offloads read traffic from the primary instance, reducing read latency and enabling read scalability. The db.r5.large instance size is sufficient for the variable read workload, and Read Replicas can be promoted to standalone instances if needed.

Exam trap

The trap here is that candidates often assume Multi-AZ alone provides read scalability, but Multi-AZ only provides high availability and failover, not read offloading—Read Replicas are required for read scaling.

How to eliminate wrong answers

Option B is wrong because a Multi-AZ instance alone provides high availability and failover but does not offer read scalability or reduce read latency for variable read traffic, as all reads still hit the primary instance. Option C is wrong because a Single-AZ instance lacks high availability, meaning any maintenance or failure causes downtime, and it provides no read scalability. Option D is wrong because a Single-AZ db.r5.xlarge instance, while larger, still lacks high availability and read scalability; scaling vertically does not address variable read traffic efficiently and does not minimize downtime during maintenance.

Practice this question →

179

MCQeasy

A data engineer is designing a data lake on Amazon S3. The data is ingested from multiple sources and needs to be partitioned by year, month, day, and event type for efficient querying with Amazon Athena. Which S3 key prefix structure is most appropriate?

A.s3://bucket/events/2024-01-01/event_type=data.parquet

B.s3://bucket/2024/01/01/event_type/events/data.parquet

C.s3://bucket/event_type=events/year=2024/month=01/day=01/data.parquet

D.s3://bucket/day=01/month=01/year=2024/event_type=events/data.parquet

AnswerC

Correct Hive-style partitioning with logical key order (year, month, day) and event type, enabling efficient partition pruning.

Why this answer

Uses Hive-style partitioning (event_type=events/year=2024/month=01/day=01), which Athena and other query engines natively support. This structure allows Athena to perform partition pruning, reading only the relevant directories based on WHERE clause filters, significantly reducing data scanned and improving query performance. Option D also uses Hive-style partitioning but with a different order of partition keys (day, month, year).

While still valid, this non-standard order may cause issues with automatic partition discovery when using MSCK REPAIR TABLE, which expects the partition order to match the table definition. Therefore, option C is the most appropriate because it follows the common convention of listing partitions from coarse to fine granularity (year > month > day) and can be easily loaded into Athena without additional configuration.

Exam trap

AWS often tests the distinction between Hive-style partitioning (key=value) and flat or date-only prefixes, where candidates mistakenly choose a structure that does not support partition pruning or is incompatible with Athena's partition discovery.

How to eliminate wrong answers

Option A is wrong because it embeds the date as a single prefix (2024-01-01) and places event_type as a filename suffix, which does not create separate partition directories; Athena cannot prune partitions efficiently without explicit partition columns. Option B is wrong because it uses a date-only hierarchy (year/month/day) but does not include event_type as a partition column, forcing full scans when filtering by event type. Option D is identical to C and is also correct, but the question expects the most appropriate structure; since both C and D are the same, the intended correct answer is C (the first occurrence).

Practice this question →

180

MCQhard

A data engineer is using AWS Lake Formation to manage fine-grained access control on an Amazon S3 data lake. The engineer has registered the S3 bucket as a Lake Formation data location and created a table in the AWS Glue Data Catalog. The engineer needs to grant a data analyst permission to query only specific columns (customer_id, order_date) in the sales table using Amazon Athena, while hiding other columns (credit_card_number, address). The analyst uses an IAM role that has no direct S3 permissions. Which action should the engineer take?

A.Grant the analyst's IAM role SELECT permission on the sales table in Lake Formation, and then use Lake Formation column-level security to include only the customer_id and order_date columns.

B.Create an IAM policy that allows s3:GetObject on the sales table's S3 prefix, and attach it to the analyst's role. Then use Athena to restrict columns via a view.

C.Use AWS Glue DataBrew to create a dataset that includes only the allowed columns, and grant the analyst access to that dataset.

D.Grant the analyst's IAM role DESCRIBE and SELECT on the sales table in Lake Formation, and then create an Athena view that selects only the allowed columns.

AnswerA

Lake Formation column-level security allows granting SELECT on specific columns. By granting SELECT on the table and then applying column filters, the analyst can query only the allowed columns. Since the analyst's role has no direct S3 permissions, Lake Formation provides temporary credentials for data access, enforcing the column-level restrictions.

Why this answer

Lake Formation column-level security is designed to grant SELECT on specific columns while hiding others. By granting SELECT on the table and then specifying included columns, the analyst can query only those columns. Because the analyst's IAM role lacks direct S3 permissions, all data access goes through Lake Formation, which enforces the column restrictions and provides temporary credentials.

This meets the requirement without granting broad S3 access.

Exam trap

The trap here is assuming that an Athena view or IAM policy can enforce column-level security when Lake Formation is managing the data lake. Only Lake Formation's column-level permissions prevent direct access to hidden columns.

Practice this question →

181

MCQeasy

A data engineer is configuring an Amazon Redshift cluster for a reporting workload. The team needs to load data from Amazon S3 into a Redshift table and wants the fastest possible load while keeping the data compressed. Which approach should the engineer use?

A.Run individual INSERT statements for each row from an AWS Lambda function that reads the S3 objects.

B.Create an AWS Glue job that writes to Redshift using JDBC in small batches and commits after each batch.

C.Use Redshift Spectrum to create an external table over the S3 data and run CREATE TABLE AS SELECT to materialize it.

D.Use the COPY command to load data from S3 into the Redshift table, specifying the appropriate compression and format options.

AnswerD

COPY is Redshift's native parallel load utility. It reads from Amazon S3 using multiple slices in parallel, applies compression and format options such as GZIP, PARQUET, or ORC, and loads directly into the table. This is the recommended, most efficient path for bulk loads and avoids intermediate staging.

Why this answer

Amazon Redshift COPY is purpose-built for bulk ingestion from Amazon S3. It distributes the read across all compute slices in parallel, supports compressed and columnar formats, and applies options that validate and transform data during load. Alternatives such as row inserts, JDBC batching, or Spectrum materialization add layers that reduce throughput, so COPY remains the fastest supported method.

Exam trap

The trap here is assuming that an orchestration service such as AWS Glue must be faster because it is serverless, when the actual load mechanism, COPY, determines throughput.

Practice this question →

182

MCQmedium

A company stores customer transaction data in an Amazon DynamoDB table. The table has a partition key of CustomerID and a sort key of TransactionDate. The data engineering team needs to retrieve all transactions for a specific customer within a date range, and the queries must be efficient. Which DynamoDB operation should the team use?

A.Scan the table with a FilterExpression on CustomerID and TransactionDate.

B.Use a BatchGetItem request with multiple CustomerID and TransactionDate pairs.

C.Query the table with a KeyConditionExpression on CustomerID and a condition on TransactionDate.

D.Use a GetItem request with the CustomerID and TransactionDate as the primary key.

AnswerC

A Query operation retrieves items based on the primary key. By specifying the partition key value and a condition on the sort key, DynamoDB can efficiently locate the relevant items without scanning the entire table. This is the most efficient way to retrieve a range of transactions for a specific customer.

Why this answer

The Query operation is designed to retrieve items sharing the same partition key value, with optional conditions on the sort key. By specifying CustomerID and a condition on TransactionDate, the team can efficiently fetch the desired transactions. Other operations either scan the entire table or retrieve single items, which are less efficient for this use case.

Exam trap

The trap here is confusing Query with Scan or GetItem; Query is the only operation that efficiently retrieves a range of items based on the sort key.

Practice this question →

183

MCQhard

A data engineer manages an Amazon S3 data lake with a bucket that has S3 Versioning enabled. A downstream analytics job accidentally overwrites thousands of current objects with corrupted data. The engineer must restore the previous good versions quickly and prevent the corrupted versions from being served. Which action should the engineer take?

A.Restore the previous good versions by copying them to overwrite the corrupted current versions

B.Delete the corrupted object versions so the previous versions become current

C.Remove the delete markers and re-upload the original files from a local backup

D.Enable S3 Object Lock in governance mode on the bucket to roll back the changes

AnswerA

With versioning enabled, each overwrite creates a new version while retaining the prior one. Copying the previous good version back onto the same key creates a new current version containing the good data, so downstream readers immediately see correct content without losing the audit trail. This can be scripted across thousands of objects using S3 Batch Operations with a version-aware copy, restoring the data lake quickly and safely.

Why this answer

S3 Versioning retains the prior object version on every overwrite, so the good data still exists as a noncurrent version. Copying that version back to the same key creates a new current version with the correct content, immediately restoring what consumers read while preserving history. Deleting versions, removing delete markers, or enabling Object Lock do not perform this rollback and may cause data loss or fail to address overwrites.

Exam trap

The trap here is confusing overwrite recovery with delete-marker handling or treating Object Lock as a rollback feature, when restoring a prior version requires copying the noncurrent version back to the key.

Practice this question →

184

MCQmedium

A data engineer needs to store JSON documents that are accessed by a key-value pattern. The workload requires single-digit millisecond latency at any scale. Which AWS service is most appropriate?

A.Amazon DocumentDB (with MongoDB compatibility)

B.Amazon RDS for PostgreSQL

C.Amazon DynamoDB

D.Amazon Neptune

AnswerC

Amazon DynamoDB stores JSON as native items and retrieves them by primary key, delivering consistent single-digit millisecond latency regardless of table size. Its partition-based architecture scales horizontally without the query-engine overhead of Amazon Athena or the fixed schema of Amazon RDS, satisfying both the key-value access pattern and the any-scale latency constraint.

Why this answer

Amazon DynamoDB is a fully managed NoSQL key-value and document database that delivers single-digit millisecond latency at any scale. It is optimized for key-value access patterns, making it the ideal choice for storing and retrieving JSON documents by a primary key with consistent low latency.

Exam trap

The trap here is that candidates may confuse DocumentDB's document storage capability with DynamoDB's key-value performance, overlooking that DocumentDB is not designed for single-digit millisecond latency at any scale, especially under high throughput.

How to eliminate wrong answers

Option A is wrong because Amazon DocumentDB is a document database designed for MongoDB workloads, but it does not guarantee single-digit millisecond latency at any scale; its performance can vary with query complexity and indexing. Option B is wrong because Amazon RDS for PostgreSQL is a relational database that requires schema definition and is not optimized for key-value access patterns; it incurs higher latency due to SQL parsing and ACID overhead. Option D is wrong because Amazon Neptune is a graph database built for highly connected data and graph queries (e.g., using Gremlin or SPARQL), not for simple key-value lookups, and its latency profile is not designed for single-digit millisecond key-value access at scale.

Practice this question →

185

Multi-Selectmedium

A data engineer is designing a data lake on Amazon S3 that will be accessed by multiple AWS Glue ETL jobs. The engineer needs to ensure that the data is organized efficiently for querying and that sensitive columns are masked for certain users. Which TWO actions should the engineer take? (Choose TWO.)

Select 2 answers

A.Use AWS Lake Formation to define column-level permissions for sensitive data.

B.Configure AWS Glue Data Catalog to automatically mask sensitive columns in table definitions.

C.Organize data in S3 using a partition structure like 'year=YYYY/month=MM/day=DD/region=XX/'.

D.Use S3 object tags to label sensitive data and apply bucket policies to restrict access.

E.Implement S3 lifecycle policies to transition sensitive data to S3 Glacier after 30 days.

AnswersA, C

Lake Formation provides column-level security to mask sensitive columns.

Why this answer

AWS Lake Formation provides fine-grained access control at the column level, allowing you to mask or restrict sensitive columns (e.g., PII) for specific IAM roles or users without altering the underlying data in S3. This is achieved through Lake Formation’s column-level permissions and data filtering, which integrate directly with the AWS Glue Data Catalog and query engines like Athena and Redshift Spectrum.

Exam trap

The trap here is that candidates often confuse S3 object tags or bucket policies with fine-grained column-level access control, or assume the Glue Data Catalog can natively mask columns, when in fact only Lake Formation provides that capability.

Practice this question →

186

MCQmedium

A data engineer manages an Amazon S3 data lake with millions of small JSON files. To improve query performance with Amazon Athena, the engineer wants to compact these files into larger Parquet files. The engineer must also minimize ongoing storage costs. Which solution should the engineer implement?

A.Create an AWS Lambda function that triggers on each S3 PUT event to merge JSON files into larger files.

B.Use AWS Glue ETL jobs to read the JSON files, transform them to Parquet, and write the output to a new S3 prefix. Then, configure an S3 Lifecycle rule to expire the original JSON objects after a retention period.

C.Use Amazon Kinesis Data Firehose to stream the JSON files into Parquet format in real time.

D.Enable S3 Transfer Acceleration on the bucket to speed up read operations for Athena queries.

AnswerB

AWS Glue ETL can efficiently convert JSON to Parquet, reducing file count and improving Athena query performance. Writing to a new prefix preserves the original data until verified. An S3 Lifecycle rule to expire the original JSON objects after a retention period reduces storage costs without immediate data loss. This approach is scalable and aligns with best practices for data lake optimization.

Why this answer

Converting JSON to Parquet with AWS Glue ETL compacts files and enables columnar storage, which improves Athena query performance. Writing to a new prefix and using an S3 Lifecycle rule to expire original JSON files after a retention period reduces storage costs while preserving data integrity. This combination addresses both performance and cost requirements.

Exam trap

The trap here is assuming that S3 Transfer Acceleration or streaming services like Kinesis Data Firehose can optimize existing batch data in S3, when they are designed for different use cases.

Practice this question →

187

MCQmedium

A data engineer needs to store semi-structured JSON data that is accessed infrequently but must be retrievable within minutes. The data is generated by IoT devices and each object is about 500 KB. The engineer wants the most cost-effective storage solution. Which AWS service should be used?

A.Amazon S3 Glacier Deep Archive

B.Amazon S3 Standard

C.Amazon S3 Standard-Infrequent Access (S3 Standard-IA)

D.Amazon Elastic Block Store (EBS)

AnswerC

S3 Standard-IA charges lower storage rates than S3 Standard while retaining millisecond retrieval, meeting the minutes-level access requirement. At 500 KB per object, each exceeds the 128 KB minimum billable size, so per-object overhead stays negligible for infrequent IoT data.

Why this answer

Amazon S3 Standard-Infrequent Access (S3 Standard-IA) is the correct choice because it is designed for data that is accessed infrequently but requires rapid retrieval (within minutes). The 500 KB JSON objects from IoT devices fit the use case, and S3 Standard-IA offers lower storage costs than S3 Standard while maintaining the same low-latency retrieval performance, making it the most cost-effective option for this scenario.

Exam trap

The trap here is that candidates often confuse 'infrequent access' with 'archival' and choose Glacier Deep Archive, overlooking the retrieval time requirement of 'within minutes' which S3 Standard-IA satisfies but Glacier does not.

How to eliminate wrong answers

Option A is wrong because Amazon S3 Glacier Deep Archive is intended for long-term archival data with retrieval times of 12 hours or more, not within minutes, and its retrieval costs are higher for urgent access. Option B is wrong because Amazon S3 Standard is optimized for frequently accessed data with higher storage costs, making it less cost-effective for infrequently accessed IoT data. Option D is wrong because Amazon Elastic Block Store (EBS) is a block-level storage service designed for EC2 instances, not for storing semi-structured JSON objects as a standalone data store, and it incurs costs even when not in use.

Practice this question →

188

Multi-Selectmedium

A data engineer is managing an Amazon S3 data lake that contains raw JSON data. The engineer needs to optimize the data lake for query performance and cost when using Amazon Athena. The data is currently stored in a single S3 prefix without partitioning, and queries often filter on `event_type` and `event_date`. The engineer wants to implement best practices for Athena. Which TWO actions should the engineer take? (Choose two.)

Select 2 answers

A.Enable S3 Transfer Acceleration on the bucket.

B.Convert the JSON data to Apache Parquet format.

C.Compress the JSON files using gzip.

D.Partition the data by `event_type` and `event_date` in S3.

E.Use Amazon S3 Select to filter data before querying with Athena.

AnswersB, D

Converting JSON to Parquet reduces the amount of data scanned by Athena because Parquet is columnar and compressed. Athena can read only the columns needed for a query, significantly lowering cost and improving performance. JSON is row-based and not splittable, so queries scan more data. Parquet also supports efficient compression and encoding. This is a fundamental optimization for Athena.

Why this answer

The two most effective actions are converting JSON to Parquet and partitioning by `event_type` and `event_date`. Parquet's columnar format reduces data scanned, and partitioning enables partition pruning. Together, they minimize query cost and improve performance.

Other options either do not affect Athena queries or are less effective. These are core best practices for optimizing Athena on S3 data lakes.

Exam trap

The trap here is considering gzip compression as sufficient, but it does not provide the columnar benefits of Parquet and may not be splittable.

Practice this question →

189

MCQeasy

A company stores application logs in Amazon S3 and uses AWS Glue crawlers to populate the AWS Glue Data Catalog. A data engineer needs to query the logs with Amazon Athena. The logs are partitioned by year/month/day in S3, but Athena queries are scanning all partitions and returning errors about missing partitions. What should the engineer do to enable partition pruning?

A.Run the AWS Glue crawler with the option to update the table's partition metadata and ensure the crawler has permission to list the partition folders.

B.Convert the logs to Parquet format and re-crawl the data.

C.Increase the Athena query timeout and retry the queries.

D.Move the logs to a single prefix without date partitions and update the crawler.

AnswerA

The crawler must be able to list the partition folders and add partition metadata to the Data Catalog. If partitions are missing, Athena cannot prune and may error. Configuring the crawler to detect partitions and granting S3 list permissions ensures the catalog is updated, enabling Athena to use partition pruning and avoid scanning all data.

Why this answer

For Athena to use partition pruning, the AWS Glue Data Catalog must contain accurate partition metadata. The crawler needs permission to list the S3 partition folders and must be configured to update partitions. Once partitions are registered, Athena can prune and avoid scanning irrelevant data, resolving the errors and improving performance.

Exam trap

The trap here is focusing on file format or query settings instead of ensuring the crawler can discover and register partitions in the Data Catalog.

Practice this question →

190

MCQhard

A media company stores millions of small JSON files in an Amazon S3 bucket and queries them with Amazon Athena. Analysts report that queries scan far more data than expected, and costs are rising. The data engineer confirms that the files are uncompressed, use no partitioning, and are stored as newline-delimited JSON. Which change will MOST reduce the data scanned per query?

A.Convert the files to Apache Parquet, compress with Snappy, and partition the S3 prefix by event date.

B.Add more small files to increase parallelism across Athena workers.

C.Enable S3 Transfer Acceleration on the bucket.

D.Increase the Athena workgroup's data usage control limit.

AnswerA

Parquet is columnar, so Athena reads only the columns referenced in the query, and Snappy compression reduces bytes scanned further. Partitioning by event date lets the query engine prune irrelevant prefixes entirely. Together these three changes attack data scanned from three angles: column pruning, compression, and partition pruning, delivering the largest reduction.

Why this answer

Converting to a columnar format, compressing with a splittable codec, and partitioning by a common filter column collectively minimize bytes read. Parquet enables column pruning, Snappy reduces stored size, and date partitioning enables partition pruning so only relevant prefixes are read. This combination produces the largest reduction in data scanned and therefore in Athena query cost.

Exam trap

The trap here is treating operational knobs such as Transfer Acceleration or workgroup limits as performance optimizations for Athena scan volume.

Practice this question →

191

Multi-Selecthard

A data engineer is designing a data lake on Amazon S3 for a retail company. The company ingests point-of-sale data as small JSON files every few minutes, totaling about 5 GB per day. Analysts query the data with Amazon Athena, and costs are rising due to many small files and full scans. The engineer wants to reduce Athena query costs and improve performance while keeping the data in S3. Which TWO actions should the engineer take? (Choose two.)

Select 2 answers

A.Increase the Athena workgroup data usage control limit to allow larger scans.

B.Run an AWS Glue ETL job or AWS Glue compaction to merge small files into larger files of 128 MB or more.

C.Enable Amazon S3 Intelligent-Tiering on the bucket to automatically move data to lower-cost storage classes.

D.Convert the JSON files to Apache Parquet and store them partitioned by date and store ID.

E.Enable S3 Transfer Acceleration on the bucket to speed up Athena queries.

AnswersB, D

Compacting many small files into larger ones reduces the number of S3 GET requests and metadata overhead, which lowers Athena query latency and cost. Glue jobs or Glue compaction can perform this merge while keeping the data in S3, complementing the Parquet and partition changes.

Why this answer

Athena cost and performance depend on bytes scanned and the number of files read. Converting JSON to partitioned Parquet enables column pruning and partition pruning, while compacting small files into larger ones reduces request overhead and metadata processing. These two actions together cut scanned bytes and improve query speed without leaving S3.

Exam trap

The trap here is confusing storage-cost optimizations like Intelligent-Tiering with query-cost optimizations, which depend on scanned bytes and file layout.

Practice this question →

192

MCQmedium

A data engineer needs to migrate an on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The database is 2 TB and has a continuous stream of write operations. The migration should minimize downtime. Which AWS service should be used?

A.AWS DataSync

B.AWS Database Migration Service (DMS)

C.AWS Snowball Edge

D.AWS Glue

AnswerB

AWS Database Migration Service performs continuous replication from the on-premises PostgreSQL source to Amazon RDS for PostgreSQL while the source stays live, then cuts over during a brief window. This satisfies the requirement to minimise downtime for a 2 TB database with ongoing writes.

Why this answer

AWS Database Migration Service (DMS) is the correct choice because it supports ongoing replication (change data capture) from an on-premises PostgreSQL source to Amazon RDS for PostgreSQL, enabling a near-zero downtime migration. DMS can handle the 2 TB dataset and continuous write stream by performing a full load followed by continuous replication of changes until the cutover. Other services lack the ability to perform live, transactional replication with minimal interruption.

Exam trap

The trap here is that candidates often choose AWS DataSync (Option A) because they confuse it with a database migration tool, but DataSync cannot replicate live transactional changes and is meant for file or object storage, not relational databases with ongoing writes.

How to eliminate wrong answers

Option A is wrong because AWS DataSync is designed for one-time or periodic bulk data transfers between on-premises storage and AWS, not for continuous database replication or minimizing downtime during a live database migration. Option C is wrong because AWS Snowball Edge is a physical device for offline data transfer, which would require stopping writes to the database to export the data, causing significant downtime and not supporting ongoing replication. Option D is wrong because AWS Glue is a serverless data integration service for ETL (extract, transform, load) jobs, not a database migration tool; it cannot perform live replication or handle continuous write streams from a source database.

Practice this question →

193

Multi-Selecthard

A company runs an Amazon RDS for PostgreSQL instance for an OLTP application. The database size is 500 GB. The company wants to minimize downtime during backups and ensure point-in-time recovery (PITR) for the last 7 days. Which TWO features should the company use? (Choose TWO.)

Select 2 answers

A.Enable Multi-AZ deployment for high availability.

B.Create a read replica in a different Availability Zone.

C.Enable automated backups with a retention period of 7 days.

D.Create daily manual snapshots and copy them to another region.

E.Enable Enhanced Monitoring to track backup progress.

AnswersA, C

Multi-AZ reduces downtime during automated backups by taking backups from the standby.

Why this answer

Multi-AZ deployment for Amazon RDS provides high availability by automatically provisioning and maintaining a synchronous standby replica in a different Availability Zone. This minimizes downtime during backups by allowing automated backups to be taken from the standby instance, eliminating I/O suspension on the primary. Option C is correct because enabling automated backups with a retention period of 7 days enables point-in-time recovery (PITR) within that window, restoring the database to any second within the retention period using transaction logs.

Exam trap

The trap here is that candidates often confuse read replicas or manual snapshots with backup and recovery features, failing to recognize that only automated backups with a retention period enable point-in-time recovery, and that Multi-AZ is required to minimize downtime during backups by offloading them to the standby instance.

Practice this question →

194

MCQmedium

A company stores sensitive data in an Amazon S3 bucket. A compliance requirement mandates that all data must be encrypted at rest with a key that is automatically rotated every year. The company also needs to maintain an audit trail of who used the key. Which solution meets these requirements?

A.Use AWS KMS customer managed keys (SSE-KMS) with automatic key rotation enabled.

B.Use customer-provided encryption keys (SSE-C) and rotate keys manually.

C.Use S3 managed keys (SSE-S3) and enable S3 server access logs.

D.Configure a bucket policy to enforce encryption using the 'aws:SecureTransport' condition.

AnswerA

AWS KMS customer managed keys with SSE-KMS satisfy both constraints: automatic annual rotation is configurable, and every cryptographic operation is logged to CloudTrail, providing the required audit trail of key usage. AWS managed keys rotate automatically but cannot be audited per-customer or configured to a yearly schedule.

Why this answer

AWS KMS customer managed keys (SSE-KMS) with automatic key rotation enabled satisfies both requirements: it encrypts data at rest in S3 and automatically rotates the KMS key every year. Additionally, KMS integrates with AWS CloudTrail to log every API call (e.g., Decrypt, GenerateDataKey) that uses the key, providing an audit trail of who used the key and when.

Exam trap

The trap here is that candidates confuse SSE-S3's automatic rotation (which is invisible and lacks audit trails) with SSE-KMS's automatic rotation (which provides both rotation and CloudTrail logging), or they mistakenly think SSE-C or bucket policies can satisfy the audit trail requirement.

How to eliminate wrong answers

Option B is wrong because SSE-C requires the customer to provide and manage their own encryption keys, and AWS does not support automatic rotation for customer-provided keys — rotation must be done manually, which violates the compliance requirement for automatic yearly rotation. Option C is wrong because SSE-S3 uses S3-managed keys that are automatically rotated by AWS, but it does not provide a per-key audit trail of who used the key; S3 server access logs only record requests to the bucket, not granular key usage events. Option D is wrong because the 'aws:SecureTransport' condition enforces encryption in transit (HTTPS), not encryption at rest, and it does not involve key rotation or audit trails for key usage.

Practice this question →

195

MCQmedium

A data engineer is designing a solution to ingest streaming data from Amazon Kinesis Data Streams into an Amazon Redshift cluster for near-real-time analytics. The engineer needs to ensure that data is loaded efficiently and that the Redshift cluster can handle the ingestion load without impacting query performance. Which approach should the engineer use?

A.Use the Amazon Redshift Streaming Ingestion feature to directly ingest from Kinesis Data Streams into Redshift materialized views.

B.Use Amazon Kinesis Client Library (KCL) on an Amazon EC2 instance to read from Kinesis and insert data into Redshift using INSERT statements.

C.Use Amazon Kinesis Data Firehose to deliver the stream to an Amazon S3 bucket, then use a COPY command to load data into Redshift.

D.Use AWS Glue streaming ETL to read from Kinesis and write to Redshift using JDBC connections.

AnswerA

Redshift Streaming Ingestion allows you to create materialized views that directly consume from Kinesis Data Streams, providing low-latency ingestion without intermediate storage. This reduces load on the cluster and enables near-real-time analytics by querying the materialized views, which can be refreshed automatically.

Why this answer

Amazon Redshift Streaming Ingestion is designed to ingest data directly from Kinesis Data Streams into Redshift materialized views with low latency. It eliminates the need for intermediate storage and complex ETL, and it offloads ingestion from the cluster's query processing. This meets the near-real-time analytics requirement while minimizing impact on query performance.

Exam trap

The trap here is assuming that Kinesis Data Firehose with S3 and COPY is the only way to load streaming data into Redshift, overlooking the native streaming ingestion feature.

Practice this question →

196

MCQeasy

A data engineer needs to store large volumes of semi-structured JSON data in Amazon S3 and query it with Amazon Athena. The engineer wants to minimize query costs and improve performance. Which action should be taken?

A.Increase the number of partitions to one per day.

B.Store the data in a single large JSON file.

C.Use gzip compression on each JSON file.

D.Convert the data to columnar format like Apache Parquet and partition it.

AnswerD

Converting to a columnar format such as Parquet allows Athena to read only the columns referenced in the query, reducing data scanned and thus cost. Partitioning further limits the amount of data scanned by filtering on partition keys. This combination significantly improves performance and reduces query costs.

Why this answer

Using a columnar format like Parquet with partitioning allows Athena to scan only the necessary columns and partitions, drastically reducing the amount of data scanned and thus the cost. This is the recommended best practice for optimizing Athena queries on S3 data.

Exam trap

The trap here is focusing only on compression or partitioning while overlooking the benefit of columnar storage for column pruning.

Practice this question →

197

Multi-Selecthard

A data engineer is troubleshooting slow query performance on an Amazon Redshift cluster. The cluster has 10 nodes and is using automatic distribution style. The engineer suspects that data distribution is causing excessive data movement. Which steps should the engineer take to diagnose and resolve the issue? (Choose THREE.)

Select 3 answers

A.Choose appropriate distribution keys for large tables

B.Use the EXPLAIN command to analyze query plans

C.Run the VACUUM command to reclaim space

D.Query the STL_DIST and STL_BCAST system tables

E.Increase the number of nodes in the cluster

AnswersA, B, D

Proper distribution keys minimize data movement.

Why this answer

Choosing appropriate distribution keys for large tables ensures that data is evenly distributed across the cluster slices, minimizing the need for data redistribution during joins and aggregations. Automatic distribution style may not always select the optimal key, leading to excessive data movement and slow query performance.

Exam trap

The trap here is that candidates often confuse VACUUM (which only reorganizes data within slices) with distribution optimization, or assume scaling out nodes automatically resolves distribution-related performance issues without addressing the underlying key choice.

Practice this question →

198

MCQhard

An application uses the 'orders' DynamoDB table with the schema and provisioned throughput shown in the exhibit. The application frequently queries by customer_id (range key) without specifying the order_id (partition key). What is the most likely impact on performance?

A.Queries will require a full table scan, consuming significant read capacity.

B.Queries will be throttled because the table does not have a global secondary index.

C.Queries will be fast because the sort key is indexed.

D.Queries will cause hot partitions on the table.

AnswerA

DynamoDB queries must target a single partition key value; omitting order_id forces a Scan across every partition, reading all items and consuming large amounts of provisioned read capacity. The range key alone cannot route the request, so performance degrades proportionally to table size.

Why this answer

The application queries by customer_id (the sort key) without specifying order_id (the partition key). In DynamoDB, a Query operation requires the partition key to be specified; without it, the only way to retrieve items is a full table Scan, which reads every item in the table. This consumes read capacity proportional to the entire table size, leading to high latency and cost.

Exam trap

The trap here is that candidates assume the sort key alone can be used for efficient queries, forgetting that DynamoDB's indexing requires the partition key to be specified for a Query operation.

How to eliminate wrong answers

Option B is wrong because throttling is not caused by the absence of a GSI; throttling occurs when consumed capacity exceeds provisioned throughput, and a Scan can cause throttling indirectly by consuming high capacity, but the lack of a GSI itself does not throttle queries. Option C is wrong because the sort key (customer_id) is only indexed within the context of a specific partition key (order_id); without the partition key, the sort key index cannot be used for efficient lookup. Option D is wrong because hot partitions are caused by uneven access patterns on a single partition key, not by queries that omit the partition key; a Scan reads all partitions evenly, so it does not create hot spots.

Practice this question →

199

MCQmedium

A company is using Amazon RDS for MySQL with Multi-AZ deployment. The database size is 2 TB and the workload is read-heavy. To improve read performance, which option should be used?

A.Use Amazon ElastiCache to cache database queries

B.Increase the instance size to 16xlarge

C.Create Read Replicas in the same or different regions

D.Enable Multi-AZ on additional instances

AnswerC

Read Replicas asynchronously copy the primary via MySQL's binlog, and applications can direct read-only queries to them, offloading the read-heavy workload from the primary. Multi-AZ standby exists solely for failover and cannot serve reads, so it adds no read throughput.

Why this answer

Amazon RDS for MySQL Read Replicas offload read traffic from the primary DB instance, directly improving read performance for a read-heavy workload. With a 2 TB database, Read Replicas can be created in the same or different regions, providing horizontal read scaling without impacting the primary instance's write capacity. Multi-AZ deployment already provides high availability but does not improve read performance, as the standby instance is not used for reads.

Exam trap

The trap here is that candidates often confuse Multi-AZ with read scaling, assuming the standby instance can serve reads, but Multi-AZ is strictly for high availability and disaster recovery, not for read traffic.

How to eliminate wrong answers

Option A is wrong because Amazon ElastiCache caches query results, reducing database load, but it does not directly improve read performance for all queries, especially those that cannot be cached (e.g., write-heavy or dynamic queries), and it introduces cache invalidation complexity. Option B is wrong because increasing the instance size to 16xlarge vertically scales the database, which can improve performance, but it is less cost-effective and does not provide the same read scalability as horizontal scaling with Read Replicas; it also does not leverage the Multi-AZ deployment's standby for reads. Option D is wrong because enabling Multi-AZ on additional instances is not a valid configuration; Multi-AZ is a feature of the primary instance that provisions a standby in a different Availability Zone for failover, not for read scaling, and additional instances cannot have Multi-AZ enabled independently.

Practice this question →

200

MCQhard

A data engineer is using AWS Glue to catalog data stored in Amazon S3. The engineer needs to run an AWS Glue ETL job that reads from a large dataset in Parquet format, performs transformations, and writes the output to Amazon Redshift. The job must handle data skew and optimize performance. Which AWS Glue feature should the engineer use to address data skew during the join operation?

A.Configure the job to use the `spark.sql.adaptive.enabled` and `spark.sql.adaptive.skewJoin.enabled` properties.

B.Use the AWS Glue DynamicFrame and apply the `Relationalize` transform to flatten nested data.

C.Enable job bookmarks to track processed data and avoid reprocessing.

D.Increase the number of DPUs allocated to the job to provide more resources for handling skewed data.

AnswerA

Enabling adaptive query execution (AQE) with skew join optimization allows Spark to dynamically handle data skew during joins. It detects skewed partitions and splits them into smaller sub-partitions, balancing the workload. This directly addresses the skew issue and improves job performance. These properties are supported in AWS Glue jobs running Spark 3.0 and later.

Why this answer

Data skew in joins can be mitigated by enabling Spark's adaptive query execution (AQE) with skew join optimization. This feature dynamically detects and splits skewed partitions, balancing the workload across executors. Other options do not directly address skew: job bookmarks track processed data, Relationalize flattens nested data, and adding DPUs increases capacity without redistributing data.

Exam trap

The trap here is thinking that adding more resources (DPUs) will automatically resolve data skew, when in fact skew requires specific algorithmic handling.

Practice this question →

201

MCQmedium

A data engineer is troubleshooting a slow-running query on Amazon Redshift. The query scans a large table but returns few rows. Which diagnostic step should be taken first?

A.Use EXPLAIN to review the query plan.

B.Run ANALYZE on the table.

C.Check the concurrency scaling status.

D.Run VACUUM on the table.

AnswerA

EXPLAIN reveals the query plan, exposing sequential scans, missing sort keys, or distribution distortions causing the large scan. Reviewing it first identifies the actual bottleneck before altering the query or cluster, satisfying the stem's requirement to diagnose the slow query.

Why this answer

When a query scans a large table but returns few rows, the most likely cause is an inefficient query plan—such as a full table scan instead of using indexes or zone maps. Using EXPLAIN first reveals the execution plan, allowing the engineer to identify whether the query is performing unnecessary sequential scans, missing filter pushdown, or using suboptimal join strategies. This diagnostic step should always precede tuning actions like ANALYZE or VACUUM, which address data distribution or storage bloat rather than query planning.

Exam trap

The trap here is that candidates often jump to performance-tuning commands like ANALYZE or VACUUM without first diagnosing the query plan, but the DEA-C01 exam emphasizes that EXPLAIN is the foundational step for identifying inefficient scan patterns before applying any corrective actions.

How to eliminate wrong answers

Option B is wrong because ANALYZE updates table statistics for the query optimizer, but if the query plan is already suboptimal (e.g., missing a WHERE clause filter), fresh statistics won't fix the root cause—EXPLAIN must be checked first. Option C is wrong because concurrency scaling handles increased query load by adding cluster capacity, but it does not improve the efficiency of a single slow query that scans many rows unnecessarily. Option D is wrong because VACUUM reclaims disk space and sorts rows for better compression, but it does not change the query execution path—a full table scan will remain a full table scan even after a vacuum.

Practice this question →

202

Multi-Selecthard

A company is migrating a large Oracle database to Amazon Aurora PostgreSQL. The migration must have minimal downtime and preserve data consistency. Which THREE AWS services or features should be used?

Select 3 answers

A.Amazon RDS for Oracle as the target

B.AWS DataSync for initial load

C.AWS Schema Conversion Tool (SCT) for schema conversion

D.Amazon Aurora PostgreSQL as the target database

E.AWS Database Migration Service (DMS) for continuous replication

AnswersC, D, E

AWS Schema Conversion Tool converts Oracle PL/SQL, sequences and data types into Aurora PostgreSQL-compatible DDL, satisfying the heterogeneous-engine schema transformation the migration demands. Without it, stored procedures and proprietary constructs would fail on PostgreSQL, blocking the minimal-downtime cutover that AWS DMS alone cannot address.

Why this answer

Option C (AWS Schema Conversion Tool) is correct because migrating from Oracle to Aurora PostgreSQL requires converting the Oracle schema, stored procedures, and PL/SQL objects into PostgreSQL-compatible DDL, which SCT automates. Option D (Amazon Aurora PostgreSQL as the target database) is correct because the scenario explicitly states the migration target is Aurora PostgreSQL, so the target engine must be Aurora PostgreSQL. Option E (AWS DMS for continuous replication) is correct because DMS supports ongoing change data capture (CDC) from Oracle to Aurora PostgreSQL, enabling minimal downtime by replicating changes until cutover.

Option A is incorrect because RDS for Oracle is the source engine, not the target, and would not accomplish the migration to PostgreSQL. Option B is incorrect because AWS DataSync is a file/object transfer service for data like NFS/SMB/S3, not a database migration tool, and cannot preserve relational data consistency for an Oracle-to-PostgreSQL migration.

Exam trap

The trap here is that candidates often confuse AWS DataSync (a file-transfer service) with database migration tools, or mistakenly think RDS for Oracle can serve as a migration target when the question explicitly specifies Aurora PostgreSQL.

Practice this question →

203

MCQeasy

A data engineer is using Amazon Redshift to store sales data. The engineer needs to ensure that the data is encrypted at rest and that encryption keys are managed by AWS. The engineer also wants to minimize administrative overhead. Which Redshift encryption option should the engineer use?

A.AWS Key Management Service (AWS KMS) customer managed key

B.AWS KMS AWS managed key (aws/redshift)

C.Client-side encryption with a custom key

D.Hardware security module (HSM) encryption

AnswerB

The AWS managed key for Redshift is automatically created and managed by AWS, providing encryption at rest with minimal administrative effort. You do not need to manage key rotation or policies; AWS handles these tasks. This meets the requirement for encryption at rest while minimizing overhead, making it the ideal choice for this scenario.

Why this answer

Amazon Redshift supports encryption at rest using AWS KMS. The AWS managed key (aws/redshift) is automatically created and managed by AWS, providing encryption without the need for manual key management. This minimizes administrative overhead while ensuring data is encrypted at rest.

Customer managed keys offer more control but require more management, and HSM or client-side encryption add complexity.

Exam trap

The trap here is equating 'managed by AWS' with 'customer managed key', which actually requires more administrative effort.

Practice this question →

204

MCQmedium

A company is using Amazon RDS for PostgreSQL with Multi-AZ deployment. The primary instance fails and a failover occurs. After the failover, the application cannot connect to the database. What is the MOST likely cause?

A.The database instance is in a 'stopped' state after failover.

B.The Multi-AZ failover requires manual intervention to complete.

C.The security group for the RDS instance was not updated during failover.

D.The application is using the old primary instance endpoint instead of the RDS CNAME.

AnswerD

RDS failover repoints the cluster's CNAME to the standby, which becomes the new primary. Applications hard-coded to the former primary's instance endpoint keep hitting a host that is no longer writable, so connections fail until they use the DNS name that RDS updates automatically.

Why this answer

After a Multi-AZ failover in Amazon RDS for PostgreSQL, the DNS CNAME record automatically updates to point to the new primary instance in the standby Availability Zone. If the application hardcodes the old primary instance's endpoint (specific IP or DNS name) instead of using the RDS CNAME (which remains constant), it will attempt to connect to the failed instance, causing connectivity loss. The CNAME is the stable connection point that always resolves to the current primary instance.

Exam trap

The trap here is that candidates may assume security groups or instance state are the issue, but AWS explicitly tests the concept that the RDS CNAME is the correct connection target and that hardcoding endpoints leads to failover failures.

How to eliminate wrong answers

Option A is wrong because RDS Multi-AZ failover does not stop the database instance; the new primary is promoted and remains in an 'available' state. Option B is wrong because Multi-AZ failover is fully automated and requires no manual intervention to complete. Option C is wrong because security groups are associated with the RDS instance itself, not with a specific AZ or IP, and they remain unchanged during failover; the new primary inherits the same security group configuration.

Practice this question →

205

MCQmedium

A company is storing sensitive user data in an Amazon S3 bucket. The security team requires that all data be encrypted at rest using a customer-managed key stored in AWS KMS. The bucket policy must deny any PUT request that does not include the appropriate encryption header. Which bucket policy condition key should be used?

A.s3:x-amz-server-side-encryption-aws-kms-key-id

B.s3:x-amz-server-side-encryption

C.s3:x-amz-acl

D.aws:SourceArn

AnswerA

The condition key `s3:x-amz-server-side-encryption-aws-kms-key-id` validates the exact KMS key ID supplied in the `x-amz-server-side-encryption-aws-kms-key-id` header, satisfying the requirement that objects use a customer-managed key. Denying PUT requests lacking this header enforces encryption at rest with the mandated key, not merely any SSE-KMS key.

Why this answer

The `s3:x-amz-server-side-encryption-aws-kms-key-id` condition key allows the bucket policy to enforce that PUT requests include a specific customer-managed KMS key ID in the `x-amz-server-side-encryption-aws-kms-key-id` header, ensuring encryption at rest with the required key. This directly meets the security team's requirement to deny PUT requests that lack the appropriate encryption header tied to a customer-managed KMS key.

Exam trap

The trap here is that candidates often confuse `s3:x-amz-server-side-encryption` (which only checks the encryption algorithm, not the key) with `s3:x-amz-server-side-encryption-aws-kms-key-id` (which checks the specific KMS key ID), leading them to pick option B when the requirement explicitly demands a customer-managed key.

How to eliminate wrong answers

Option B is wrong because `s3:x-amz-server-side-encryption` only checks whether the `x-amz-server-side-encryption` header is present (e.g., `AES256` or `aws:kms`), but it cannot enforce that a specific customer-managed KMS key ID is used; it would allow any KMS key, including AWS-managed keys. Option C is wrong because `s3:x-amz-acl` is used to control access control list (ACL) headers in requests, not encryption headers, so it is irrelevant to encryption enforcement. Option D is wrong because `aws:SourceArn` is a global condition key used to restrict requests based on the ARN of the source resource (e.g., an SNS topic or Lambda function), not to enforce encryption headers in S3 PUT requests.

Practice this question →

206

Multi-Selectmedium

A company is designing a data lake on Amazon S3 for analytics. The data includes sensitive personally identifiable information (PII). Which TWO actions should the company take to protect the data? (Choose TWO.)

Select 2 answers

A.Enable S3 Block Public Access.

B.Enable Requester Pays.

C.Enable S3 Transfer Acceleration.

D.Enable cross-region replication.

E.Enable default encryption with SSE-KMS.

AnswersA, E

S3 Block Public Access overrides bucket policies and ACLs that would otherwise expose objects publicly, preventing accidental PII leakage. This satisfies the protection requirement by enforcing account- and bucket-level guardrails against public exposure of the data lake.

Why this answer

S3 Block Public Access (Option A) prevents any public access to S3 buckets and objects, which is critical for protecting PII from unintended exposure. Default encryption with SSE-KMS (Option E) ensures that all data written to S3 is encrypted at rest using AWS KMS-managed keys, providing both encryption and centralized key management for sensitive data.

Exam trap

The trap here is that candidates often confuse operational features like Requester Pays or Transfer Acceleration with security controls, or they think replication alone provides data protection, when in fact encryption and access blocking are the direct mechanisms for safeguarding PII.

Practice this question →

207

MCQmedium

A data engineer runs the above CLI command to describe the DynamoDB table 'Orders'. The table has a partition key 'OrderID' and sort key 'CustomerID'. Which query operation is most efficient for retrieving all orders for a specific customer?

A.Query the table using CustomerID as the partition key

B.Scan the table and filter by CustomerID

C.Use GetItem with CustomerID as the key

D.Create a Global Secondary Index on CustomerID and query the index

AnswerD

Queries must target the partition key, so retrieving orders by CustomerID needs a Global Secondary Index with CustomerID as its partition key. A Scan reads the whole table; a Query on OrderID cannot filter efficiently by customer.

Why this answer

A Global Secondary Index (GSI) on CustomerID allows you to query efficiently using CustomerID as the partition key, avoiding a full table scan. Since the base table's primary key is (OrderID, CustomerID), you cannot directly query by CustomerID alone; a GSI provides an alternative access pattern optimized for this query.

Exam trap

The trap here is that candidates assume the sort key can be used as a query filter without an index, but DynamoDB requires the partition key for Query operations, and a Scan is often mistakenly chosen as a simpler alternative despite its performance cost.

How to eliminate wrong answers

Option A is wrong because CustomerID is the sort key, not the partition key, so a Query operation requires the partition key (OrderID) to be specified; you cannot query using only the sort key. Option B is wrong because a Scan reads every item in the table, which is inefficient and costly for large datasets, especially when a targeted query is possible. Option C is wrong because GetItem requires both the partition key and sort key to retrieve a single item; it cannot return multiple orders for a customer.

Practice this question →

208

MCQhard

Refer to the exhibit. A data engineer applies this bucket policy to an S3 bucket. A user within the 10.0.0.0/24 IP range attempts to upload an object to the bucket using an HTTP (non-HTTPS) request. What is the outcome?

A.The upload succeeds because the Allow statement grants permission.

B.The upload succeeds because the user's IP is allowed.

C.The upload fails because the user's IP is not in the allowed range for PutObject.

D.The upload fails because the request is not using HTTPS.

AnswerD

The bucket policy includes a Deny statement conditioned on aws:SecureTransport being false, which rejects any non-HTTPS request regardless of source IP. The 10.0.0.0/24 user's HTTP upload therefore fails, satisfying the explicit deny that overrides the IP-based allowance.

Why this answer

The bucket policy includes a condition `aws:SecureTransport` set to `false`, which explicitly denies any request that does not use HTTPS. Since the user is making an HTTP (non-HTTPS) request, the Deny statement overrides any Allow statement, causing the upload to fail. The correct answer is D.

Exam trap

The DEA-C01 exam often tests the precedence of explicit Deny over Allow in IAM policies, and the trap here is that candidates focus on the IP range in the Allow statement and overlook the Deny condition that blocks non-HTTPS requests.

How to eliminate wrong answers

Option A is wrong because the Allow statement is overridden by the explicit Deny when the condition `aws:SecureTransport` equals `false`; the upload does not succeed. Option B is wrong because even though the user's IP is within the allowed range (10.0.0.0/24), the Deny statement for non-HTTPS requests takes precedence and blocks the upload. Option C is wrong because the user's IP is actually within the allowed range for PutObject; the failure is due to the lack of HTTPS, not the IP range.

Practice this question →

209

MCQhard

A data engineer maintains an Amazon DynamoDB table that stores IoT telemetry. The table uses a partition key of deviceId and a sort key of timestamp, with on-demand capacity. A few very active devices generate millions of writes per hour while thousands of other devices write sporadically. The engineer observes throttling on writes for the active devices and wants to reduce it with the least application change. Which action should the engineer take?

A.Enable DynamoDB Streams and process writes asynchronously

B.Switch the table to provisioned capacity with a high write capacity unit setting

C.Add a random suffix to the partition key to spread writes across partitions

D.Create a global secondary index on timestamp and write through the index

AnswerC

Write sharding by appending a random or calculated suffix to the partition key distributes a hot device's writes across multiple partitions, each with its own throughput budget. This directly relieves the per-partition write limit that causes throttling for high-volume devices. Queries must then fan out across the shards and aggregate results, but the change is confined to the key composition and read logic, keeping application change moderate and targeted.

Why this answer

Throttling from a few high-volume partition keys is a hot-partition problem, not a capacity problem. DynamoDB partitions have a fixed write ceiling regardless of table-level capacity mode. Write sharding by adding a suffix to the partition key spreads a single device's writes across many partitions, each with independent throughput.

Provisioned capacity, streams, and GSIs do not raise the per-partition write limit, so they fail to address the observed throttling.

Exam trap

The trap here is thinking that switching to provisioned capacity or raising WCUs fixes throttling, when the real constraint is the per-partition write limit on a single hot partition key.

Practice this question →

210

MCQmedium

A data engineer is migrating an on-premises MongoDB database to Amazon DocumentDB. Which migration strategy minimizes downtime?

A.Take a snapshot of the MongoDB database and restore it to DocumentDB.

B.Use AWS Database Migration Service (AWS DMS) with full load only.

C.Export data using mongodump and import using mongorestore.

D.Use AWS DMS with full load and ongoing replication from MongoDB to DocumentDB.

AnswerD

AWS DMS performs a full load then applies ongoing change data capture replication from MongoDB to DocumentDB, keeping the target continuously synchronised until cutover. This continuous replication is what minimises downtime compared with one-off dump-and-restore migrations.

Why this answer

AWS DMS with full load and ongoing replication (change data capture) minimizes downtime by continuously synchronizing changes from the source MongoDB to the target DocumentDB after the initial full load, allowing a cutover with only a brief pause. This is the only option that supports near-zero downtime migration for live databases.

Exam trap

The trap here is that candidates assume any AWS DMS migration automatically minimizes downtime, but only the full load plus ongoing replication (CDC) option achieves near-zero downtime, while full load only still requires a write stop.

How to eliminate wrong answers

Option A is wrong because taking a snapshot and restoring it captures only a point-in-time copy, requiring the source database to be offline or read-only during the snapshot, causing downtime. Option B is wrong because AWS DMS full load only transfers the current data once, without capturing ongoing changes, so any writes during the migration are lost and downtime is needed to stop writes before cutover. Option C is wrong because mongodump and mongorestore are offline tools that require the source MongoDB to stop accepting writes during the export, resulting in significant downtime.

Practice this question →

211

MCQhard

A data engineer is using AWS Glue to catalog data stored in Amazon S3. The data is in Apache Parquet format and partitioned by year, month, and day. The engineer notices that AWS Glue crawlers are taking a long time to run and are not correctly identifying new partitions. The engineer needs to improve the crawler performance and ensure new partitions are added automatically. Which action should the engineer take?

A.Use AWS Glue partition projection instead of crawling for partitions.

B.Enable the crawler option to update the Data Catalog with new partitions only, and set a partition index.

C.Increase the number of data processing units (DPUs) for the crawler.

D.Configure the crawler to use a custom classifier for Parquet.

AnswerA

AWS Glue partition projection allows you to define partition patterns and ranges without running crawlers. This eliminates the need for crawlers to discover partitions, significantly improving performance and ensuring new partitions are automatically available. It is ideal for data with a known, regular partitioning scheme like year/month/day.

Why this answer

Partition projection in AWS Glue allows you to specify the partitioning scheme directly in the table properties, so the crawler does not need to discover partitions. This reduces crawler runtime and ensures that new partitions are immediately available for query engines like Athena. It is the most efficient solution for regularly partitioned data.

Exam trap

The trap here is assuming that increasing crawler resources or using custom classifiers will fix partition detection, but the real solution is to bypass crawling altogether with partition projection.

Practice this question →

212

MCQhard

A financial services company stores transaction data in Amazon RDS for PostgreSQL. The company requires that all changes to the database be logged for audit purposes, including before and after images of updated rows. Which feature should the data engineer enable?

A.Enable automated backups and export logs to Amazon S3

B.Enable Enhanced Monitoring and publish logs to CloudWatch Logs

C.Set up logical replication using pglogical or native publication/subscription

D.Enable Multi-AZ deployment and read replicas

AnswerC

Logical replication provides row-level changes with before and after images.

Why this answer

Logical replication, using either pglogical or native PostgreSQL publication/subscription, captures row-level changes (INSERT, UPDATE, DELETE) and can include both the old and new values of updated rows. This meets the audit requirement for before-and-after images, as logical replication decodes the write-ahead log (WAL) to produce a change stream that includes full row snapshots.

Exam trap

The trap here is that candidates confuse database-level logging features (like Enhanced Monitoring or automated backups) with row-level change data capture, assuming any logging mechanism will capture before-and-after images, when only logical replication (or triggers with audit tables) provides that granularity.

How to eliminate wrong answers

Option A is wrong because automated backups capture point-in-time snapshots of the entire database, not a continuous, row-level change stream with before-and-after images; exporting logs to S3 provides error logs or slow query logs, not row-level audit trails. Option B is wrong because Enhanced Monitoring collects OS-level metrics (CPU, memory, disk I/O) and publishes them to CloudWatch Logs, not database row changes. Option D is wrong because Multi-AZ deployment provides high availability via synchronous standby replication, and read replicas serve read traffic; neither logs individual row modifications or provides before-and-after images.

Practice this question →

213

MCQeasy

A data engineer needs to store archival data that is rarely accessed but must be retained for 7 years. The data should be retrievable within 12 hours. Which Amazon S3 storage class is MOST cost-effective?

A.S3 Intelligent-Tiering

B.S3 Glacier Flexible Retrieval

C.S3 Standard

D.S3 Glacier Deep Archive

AnswerD

S3 Glacier Deep Archive offers the lowest storage cost of all S3 classes and supports a standard retrieval time within 12 hours, matching the stated access and retrieval constraints. Its 7-year retention suitability makes it the most cost-effective choice for rarely accessed archival data.

Why this answer

S3 Glacier Deep Archive is the most cost-effective storage class for archival data that is rarely accessed and requires a 7-year retention period, with retrieval times up to 12 hours. It offers the lowest storage cost among S3 classes, making it ideal for long-term retention of data that does not need immediate access.

Exam trap

The trap here is that candidates often confuse S3 Glacier Flexible Retrieval (which offers faster retrieval but higher cost) with S3 Glacier Deep Archive, failing to recognize that the 12-hour retrieval requirement is easily met by Deep Archive's standard retrieval, making it the most cost-effective choice for long-term archival.

How to eliminate wrong answers

Option A is wrong because S3 Intelligent-Tiering is designed for data with unknown or changing access patterns, automatically moving data between tiers based on usage, which incurs monitoring and automation costs that are unnecessary for rarely accessed archival data. Option B is wrong because S3 Glacier Flexible Retrieval offers retrieval times from minutes to hours (typically 1-5 minutes for expedited, 3-5 hours for standard), but its storage cost is higher than Glacier Deep Archive, making it less cost-effective for data that only needs retrieval within 12 hours. Option C is wrong because S3 Standard is designed for frequently accessed data with millisecond retrieval times, and its storage cost is significantly higher than archival classes, making it prohibitively expensive for data that is rarely accessed and retained for 7 years.

Practice this question →

214

MCQeasy

A data engineer needs to store semi-structured JSON files that are accessed infrequently but must be retrievable within minutes. The data should be stored cost-effectively. Which storage solution meets these requirements?

A.Amazon S3 Glacier Flexible Retrieval storage class.

B.Amazon S3 Glacier Deep Archive storage class.

C.Amazon S3 Standard-Infrequent Access (S3 Standard-IA) storage class.

D.Amazon S3 Standard storage class.

AnswerC

S3 Standard-IA matches the stated access pattern: infrequent retrieval with millisecond availability, satisfying the "within minutes" constraint. It costs less than S3 Standard for storage while retaining the same low-latency access, unlike Glacier tiers, which impose retrieval delays measured in minutes to hours.

Why this answer

Amazon S3 Standard-Infrequent Access (S3 Standard-IA) is the correct choice because it is designed for data accessed infrequently but requires rapid retrieval (within milliseconds). It offers lower storage costs than S3 Standard while maintaining low-latency access, meeting the requirement of retrievability within minutes cost-effectively.

Exam trap

The trap here is that candidates often confuse retrieval time with retrieval cost, assuming that 'infrequent access' implies slower retrieval, but S3 Standard-IA provides the same low-latency access as S3 Standard, unlike Glacier classes which have significantly longer retrieval times.

How to eliminate wrong answers

Option A is wrong because Amazon S3 Glacier Flexible Retrieval is optimized for archival data where retrieval times range from minutes to hours, but it incurs higher retrieval costs and is not designed for frequent or rapid access within minutes. Option B is wrong because Amazon S3 Glacier Deep Archive is the lowest-cost storage class for long-term archival, but retrieval times are typically 12 hours or more, far exceeding the 'within minutes' requirement. Option D is wrong because Amazon S3 Standard is designed for frequently accessed data with high durability and low latency, but it is more expensive than S3 Standard-IA for infrequently accessed data, making it less cost-effective for this use case.

Practice this question →

215

MCQmedium

A company stores sensitive data in Amazon S3. They need to ensure that all objects are encrypted at rest. Which approach meets this requirement with minimal effort?

A.Use client-side encryption before uploading

B.Enable default encryption on the S3 bucket with SSE-S3

C.Enable S3 Versioning and MFA Delete

D.Use a bucket policy to deny PutObject without encryption

AnswerB

Enabling SSE-S3 default encryption on the bucket applies AES-256 encryption to every object at rest automatically, including future uploads, with no application changes. This meets the requirement with minimal operational effort compared to client-side or KMS-based approaches.

Why this answer

Enabling default encryption on an S3 bucket with SSE-S3 (Server-Side Encryption with S3-Managed Keys) automatically encrypts all objects at rest using AES-256, with no additional effort from the user. This ensures that any object uploaded without explicit encryption headers is encrypted by default, meeting the requirement with minimal configuration overhead.

Exam trap

The trap here is that candidates often confuse enforcing encryption at upload (via bucket policy) with automatically encrypting data at rest, leading them to choose option D, which requires additional policy management and does not guarantee encryption of all objects without explicit headers.

How to eliminate wrong answers

Option A is wrong because client-side encryption requires the application to manage encryption keys and perform encryption before upload, adding significant operational effort and complexity, which contradicts the 'minimal effort' requirement. Option C is wrong because S3 Versioning and MFA Delete provide data protection against accidental deletion and overwrites, but they do not encrypt objects at rest. Option D is wrong because a bucket policy to deny PutObject without encryption only enforces encryption on upload but does not encrypt existing objects or objects uploaded without the required headers; it also requires additional policy management and does not automatically encrypt data at rest.

Practice this question →

216

MCQmedium

A data engineer needs to create a table in Amazon Athena that reads JSON data stored in Amazon S3. The JSON records are stored in a single file, one JSON object per line. The engineer wants Athena to automatically discover the schema and create the table without manually defining columns. Which AWS service or feature should the engineer use?

A.Amazon Athena CREATE TABLE AS SELECT (CTAS) statement

B.AWS Glue crawler

C.AWS Glue DataBrew

D.Amazon S3 Inventory

AnswerB

AWS Glue crawler scans data in S3, infers the schema, and populates the AWS Glue Data Catalog. Athena can then query the table using the catalog metadata. This meets the requirement of automatic schema discovery without manual column definition.

Why this answer

An AWS Glue crawler automatically scans data in Amazon S3, infers the schema, and creates table definitions in the AWS Glue Data Catalog. Athena uses this catalog to query the data without manual schema definition. This is the standard method for automatic schema discovery in a data lake.

Exam trap

The trap here is assuming that Athena can automatically infer schemas from raw data without a crawler or manual DDL.

Practice this question →

217

MCQeasy

A data engineer is designing a data lake on Amazon S3. The data includes customer PII that must be encrypted at rest. The company also requires that the encryption keys be rotated automatically every year. Which encryption solution should the engineer use?

A.SSE-KMS with automatic key rotation enabled

B.SSE-S3

C.SSE-C

D.Client-side encryption with AWS KMS

AnswerA

SSE-KMS encrypts objects at rest using AWS KMS keys and supports automatic annual key rotation, meeting both the PII encryption and rotation requirements. SSE-S3 rotation is managed by AWS without configurable schedules, so it cannot satisfy the stated yearly rotation constraint.

Why this answer

SSE-KMS with automatic key rotation enabled meets both requirements: it encrypts data at rest in S3 and allows the company to automatically rotate the customer master key (CMK) every year. AWS KMS supports automatic annual rotation for symmetric CMKs, which satisfies the compliance need without manual intervention.

Exam trap

The trap here is that candidates often assume SSE-S3 provides customer-controlled key rotation, but SSE-S3 uses keys fully managed by AWS with no customer control over rotation frequency, whereas SSE-KMS with automatic key rotation lets the company meet the explicit annual rotation requirement.

How to eliminate wrong answers

Option B (SSE-S3) is wrong because while it encrypts data at rest, it does not support automatic key rotation; the encryption keys are managed and rotated by S3 but not on a customer-defined schedule. Option C (SSE-C) is wrong because it requires the customer to provide and manage their own encryption keys, and AWS does not handle key rotation, making it unsuitable for automated annual rotation. Option D (Client-side encryption with AWS KMS) is wrong because it encrypts data before sending it to S3, but the key rotation applies only to the KMS key used for client-side encryption, not to the S3-side encryption; moreover, client-side encryption adds complexity and does not directly address the requirement for encryption at rest within S3.

Practice this question →

218

MCQhard

A company uses Amazon DynamoDB with provisioned capacity. During a sales event, write traffic spikes and some requests receive ProvisionedThroughputExceeded exceptions. The reads are within limits. The data engineer needs to minimize latency for the spike without manual intervention. Which solution is MOST cost-effective?

A.Use Amazon SQS to buffer write requests and process them in batches.

B.Disable auto scaling and set write capacity to the peak observed value.

C.Enable DynamoDB auto scaling for write capacity with a target utilization of 70%.

D.Enable DynamoDB Accelerator (DAX) to cache write operations.

AnswerC

DynamoDB auto scaling adjusts provisioned write capacity automatically in response to CloudWatch utilisation metrics, directly resolving the ProvisionedThroughputExceeded exceptions during the spike without manual intervention. A 70% target keeps headroom while avoiding over-provisioning, satisfying the cost-effectiveness constraint. Reads already sit within limits, so scaling writes alone is sufficient.

Why this answer

DynamoDB auto scaling for write capacity automatically adjusts the provisioned write capacity units (WCUs) based on the actual traffic pattern, using a target utilization of 70% to balance cost and performance. This eliminates manual intervention and handles spikes efficiently by scaling up before throttling occurs, while remaining cost-effective since capacity scales down when traffic subsides.

Exam trap

The trap here is that candidates may confuse DAX as a solution for write performance, but DAX only accelerates reads (via caching) and does not mitigate write throttling, leading to an incorrect choice of Option D.

How to eliminate wrong answers

Option A is wrong because using Amazon SQS to buffer write requests introduces additional latency for processing batches, which contradicts the requirement to minimize latency during the spike, and it adds complexity and cost for queue management. Option B is wrong because disabling auto scaling and setting write capacity to the peak observed value is wasteful and costly, as it permanently allocates high capacity that is only needed during spikes, and it requires manual intervention to adjust. Option D is wrong because DynamoDB Accelerator (DAX) is an in-memory cache for read operations, not writes; it does not reduce write throttling or ProvisionedThroughputExceeded exceptions, and it adds cost without addressing the write capacity issue.

Practice this question →

219

MCQeasy

A company stores critical financial data in Amazon DynamoDB. To meet compliance requirements, the data must be encrypted at rest with a customer-managed key. Which solution should the data engineer implement?

A.Configure the DynamoDB table to use a customer managed key from AWS KMS.

B.Use AWS CloudHSM to generate a key and import it into DynamoDB.

C.Enable default encryption on the DynamoDB table using S3-managed keys.

D.Use AWS Certificate Manager to issue a certificate and configure TLS.

AnswerA

DynamoDB supports encryption at rest using AWS KMS customer managed keys, configured per table. Selecting a customer managed key rather than the AWS owned default key satisfies the compliance requirement for a key the company controls and can audit.

Why this answer

AWS DynamoDB integrates with AWS KMS to support encryption at rest using customer-managed keys (CMKs). By selecting a customer-managed key from KMS during table creation or via an update, the company can meet compliance requirements for controlling the encryption key lifecycle, including rotation and access policies. This approach ensures that the data is encrypted using AES-256 encryption, with the key material managed by the customer rather than AWS.

Exam trap

The trap here is that candidates may confuse encryption at rest with encryption in transit (TLS) or assume that CloudHSM can be directly used with DynamoDB, when in fact DynamoDB only supports KMS for encryption at rest, and CloudHSM requires a custom integration layer.

How to eliminate wrong answers

Option B is wrong because AWS CloudHSM provides hardware-based key storage but does not directly integrate with DynamoDB for encryption at rest; DynamoDB only supports KMS keys, not CloudHSM-generated keys imported via the HSM. Option C is wrong because DynamoDB does not use S3-managed keys; default encryption in DynamoDB uses AWS-owned keys or AWS-managed keys, not S3-managed keys, and S3-managed keys are specific to Amazon S3. Option D is wrong because AWS Certificate Manager and TLS are used for encryption in transit, not encryption at rest, and do not address the requirement for encrypting stored data with a customer-managed key.

Practice this question →

220

Multi-Selecthard

A data engineer is designing a data lake on Amazon S3 using AWS Lake Formation. The engineer needs to grant fine-grained access to specific columns and rows of a table to different analysts. Which two actions should the engineer take to meet these requirements? (Choose two.)

Select 2 answers

A.Define a Lake Formation data filter that specifies column and row expressions.

B.Use AWS Glue crawlers to update the Data Catalog with new partitions.

C.Register the S3 data location with Lake Formation.

D.Enable S3 Block Public Access on the bucket.

E.Create an IAM policy that allows s3:GetObject on the entire bucket.

AnswersA, C

Data filters in Lake Formation allow you to define column-level and row-level access controls. You can grant permissions on a filtered view of the table, restricting which columns and rows an analyst can see. This directly meets the requirement for fine-grained access.

Why this answer

To enforce column- and row-level security with Lake Formation, the engineer must first register the S3 location so Lake Formation can manage the data. Then, data filters can be created to define which columns and rows are accessible. These two steps together enable fine-grained permissions, while other options do not provide the required access controls.

Exam trap

The trap here is assuming that broad IAM policies or S3 Block Public Access can provide fine-grained access, when Lake Formation requires explicit registration and data filters.

Practice this question →

221

MCQmedium

A data engineer is using Amazon Redshift and needs to improve the performance of complex queries that join large tables. The engineer has already set the distribution style to KEY on the join columns. What additional step should the engineer take to optimize the join performance?

A.Change the distribution style to ALL for all large tables.

B.Enable concurrency scaling to handle concurrent queries.

C.Set the sort key on the columns used in the join and in the WHERE clause.

D.Increase the number of nodes in the Redshift cluster to add more compute resources.

AnswerC

Sort keys enable efficient range scans and merge joins by co-locating data on disk in sorted order. When joining large tables, having the join columns as sort keys can allow Redshift to use merge joins instead of hash joins, reducing data movement and improving performance. This is a best practice for join optimization.

Why this answer

Sort keys on join and filter columns allow Redshift to perform merge joins and skip unnecessary data blocks, reducing I/O and data movement. This optimizes join performance without adding resources. The other options either scale resources or change distribution in ways that are not optimal for large tables.

Exam trap

The trap here is assuming that adding more nodes or enabling concurrency scaling will automatically improve join performance, when the real gain comes from sort keys and proper data layout.

Practice this question →

222

MCQhard

A company is using Amazon DynamoDB with on-demand capacity for a gaming application. During a new game launch, write traffic spikes to 50,000 writes per second, but the application experiences throttling. The DynamoDB table has a partition key of 'game_id' and a sort key of 'timestamp'. What is the MOST likely cause of throttling?

A.The table has not enabled auto-scaling for writes.

B.The table's on-demand capacity is insufficient for the write spike.

C.Hot partitions due to a skewed access pattern on the partition key 'game_id'.

D.The sort key is not optimal for write-heavy workloads.

AnswerC

Skewed writes concentrate on a few popular game_id values, so individual partitions exceed their per-partition throughput ceiling even though on-demand capacity scales table-wide. DynamoDB distributes capacity per partition, and a single partition key value cannot exceed roughly 1,000 write capacity units per second, causing throttling despite aggregate headroom.

Why this answer

DynamoDB on-demand capacity automatically scales to handle traffic spikes, but it still has per-partition throughput limits. With 'game_id' as the partition key, a single popular game can create a hot partition where all writes target the same partition, exceeding the partition's maximum write capacity (1,000 write capacity units per partition) and causing throttling, even though the overall table capacity is sufficient.

Exam trap

The trap here is that candidates assume on-demand capacity eliminates all throttling, but they overlook DynamoDB's per-partition throughput limits, which can cause throttling on hot partitions even with on-demand mode.

How to eliminate wrong answers

Option A is wrong because on-demand capacity does not use auto-scaling; it automatically adjusts capacity without needing auto-scaling enabled. Option B is wrong because on-demand capacity is designed to handle sudden spikes without manual provisioning, so insufficient capacity is not the issue—the problem is partition-level limits. Option D is wrong because the sort key does not affect write throughput distribution; partition key selection determines write distribution, and a sort key is irrelevant to throttling caused by hot partitions.

Practice this question →

223

MCQmedium

A data engineer needs to store semi-structured JSON logs from multiple microservices in a cost-effective manner for ad-hoc querying using SQL. Which AWS service should be used?

A.Amazon Athena with data in S3

B.Amazon DynamoDB

C.Amazon RDS for MySQL

D.Amazon Kinesis Data Analytics

AnswerA

Amazon Athena queries JSON directly from S3 using schema-on-read, so no transformation or loading is needed before ad-hoc SQL analysis. S3 provides the cheapest durable storage for semi-structured logs, satisfying the cost-effectiveness constraint, while Athena's pay-per-query model avoids provisioning clusters for intermittent querying.

Why this answer

Amazon Athena is the correct choice because it allows you to query semi-structured JSON logs stored in S3 directly using standard SQL, without needing to load or transform the data. Athena's schema-on-read approach and pay-per-query pricing make it highly cost-effective for ad-hoc analysis of large volumes of log data, as you only pay for the data scanned during queries.

Exam trap

The trap here is that candidates often confuse Amazon Athena with Amazon Kinesis Data Analytics, mistakenly thinking that Kinesis is the go-to service for SQL-based log analysis, when in fact Kinesis is for real-time streaming and Athena is the correct serverless query service for stored data in S3.

How to eliminate wrong answers

Option B (Amazon DynamoDB) is wrong because it is a NoSQL key-value and document database optimized for low-latency, high-throughput transactional workloads, not for ad-hoc SQL querying of semi-structured logs; it lacks native SQL support and would require expensive scanning of large datasets. Option C (Amazon RDS for MySQL) is wrong because it requires you to predefine a schema, load the JSON logs into relational tables, and pay for provisioned compute and storage even when idle, making it less cost-effective for sporadic ad-hoc queries compared to Athena's serverless model. Option D (Amazon Kinesis Data Analytics) is wrong because it is designed for real-time stream processing and analytics on streaming data using SQL, not for querying stored JSON logs in S3; it would require continuous ingestion and incurs ongoing costs regardless of query frequency.

Practice this question →

224

MCQeasy

A company is using Amazon S3 as a data lake. The data engineer needs to ensure that all objects uploaded to a specific bucket are automatically replicated to a bucket in another AWS Region for disaster recovery. Which configuration should the engineer implement?

A.Enable S3 Same-Region Replication (SRR) on the source bucket.

B.Enable S3 Cross-Region Replication (CRR) on the source bucket.

C.Use S3 Transfer Acceleration to copy objects to the destination.

D.Use S3 Batch Operations to copy existing objects.

AnswerB

S3 Cross-Region Replication automatically copies objects to a bucket in a different AWS Region, satisfying the disaster recovery requirement. Configuring CRR on the source bucket replicates new uploads asynchronously, providing cross-Region durability without application changes. Versioning must be enabled on both source and destination buckets for replication to function.

Why this answer

S3 Cross-Region Replication (CRR) is the correct choice because it automatically replicates objects from a source bucket in one AWS Region to a destination bucket in a different AWS Region, meeting the disaster recovery requirement for geographic separation. CRR requires versioning to be enabled on both buckets and replicates new objects asynchronously after upload.

Exam trap

The trap here is that candidates confuse S3 Transfer Acceleration (which speeds up uploads) with replication, or assume S3 Batch Operations can be used for ongoing replication, when only CRR provides automatic, cross-region object replication for disaster recovery.

How to eliminate wrong answers

Option A is wrong because S3 Same-Region Replication (SRR) replicates objects within the same AWS Region, not across regions, so it does not provide disaster recovery across geographic boundaries. Option C is wrong because S3 Transfer Acceleration speeds up uploads over long distances using AWS edge locations but does not replicate objects to another bucket; it only improves transfer performance for clients. Option D is wrong because S3 Batch Operations is used for bulk actions like copying existing objects or tagging, but it is a one-time operation, not an automatic, ongoing replication configuration for new objects.

Practice this question →

225

MCQhard

A company is using Amazon DynamoDB with auto scaling enabled. During a marketing campaign, write traffic spikes, and some write requests fail with ProvisionedThroughputExceededException. The auto scaling policy has a target utilization of 70% and a maximum capacity that is high enough. What is the most likely cause of the throttling?

A.The table has a global secondary index that is throttling.

B.Auto scaling cannot react quickly enough to sudden traffic spikes.

C.The table does not have enough maximum capacity.

D.The auto scaling policy is not configured correctly.

AnswerB

DynamoDB auto scaling adjusts capacity through CloudWatch alarms over minutes, so it lags behind abrupt spikes. The maximum capacity being sufficient is irrelevant; the reaction delay itself causes ProvisionedThroughputExceededException when demand outpaces the scaling response.

Why this answer

Auto scaling in DynamoDB adjusts capacity based on the average utilization over a period (typically 5-10 minutes). When a sudden traffic spike occurs, the write requests can exceed the current provisioned capacity before the auto scaling policy has time to react and increase the capacity. This delay causes ProvisionedThroughputExceededException errors, even though the maximum capacity is set high enough.

Exam trap

The trap here is that candidates assume a correctly configured auto scaling policy with sufficient maximum capacity will always prevent throttling, ignoring the inherent latency in auto scaling's reaction to sudden, short-lived traffic spikes.

How to eliminate wrong answers

Option A is wrong because a throttling global secondary index (GSI) would cause its own ProvisionedThroughputExceededException, but the question states the write requests fail directly on the table, and a GSI throttling would typically manifest as errors on writes that affect the index, not necessarily all table writes. Option C is wrong because the question explicitly states that the maximum capacity is high enough, so insufficient maximum capacity is not the cause. Option D is wrong because the auto scaling policy is configured with a target utilization of 70% and a high enough maximum capacity, which is a standard and correct configuration; the issue is the inherent lag in auto scaling's response to sudden spikes, not a misconfiguration.

Practice this question →