DEA-C01 Data Store Management Practice Test 23 — 15 Questions

Question 1

A company uses Amazon Redshift for analytics. The data engineer notices that queries are slow due to many small inserts. Which technique would improve write performance?

Accepted Answer

Use the COPY command to load data from Amazon S3.. The COPY command is the recommended way to load data into Amazon Redshift because it performs bulk inserts in parallel across all nodes, leveraging the cluster's distributed architecture. Small individual INSERT statements cause high overhead due to transaction logging and commit processing, leading to slow write performance. By loading data from Amazon S3 using COPY, you bypass these per-row overheads and achieve optimal throughput.

Answer

Define DISTKEY and SORTKEY on the table.

Answer

Increase the number of nodes in the cluster.

Answer

Configure workload management (WLM) queues.

Question 2

A company uses Amazon S3 to store sensitive data. The security team requires that all data be encrypted at rest using a customer-managed key that is rotated annually. Which encryption option should be used?

Accepted Answer

SSE-KMS (Server-Side Encryption with AWS KMS).. SSE-KMS is the correct choice because it allows you to use a customer-managed key (CMK) in AWS KMS, which you can configure to rotate automatically on an annual schedule. This satisfies the security team's requirement for encryption at rest with a key you control and rotate yearly, while still leveraging server-side encryption that integrates with S3's existing infrastructure.

Answer

SSE-S3 (Server-Side Encryption with S3-managed keys).

Answer

Client-side encryption.

Answer

SSE-C (Server-Side Encryption with Customer-Provided keys).

Question 3

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?

Accepted Answer

Use EXPLAIN to review the query plan.. 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.

Answer

Run ANALYZE on the table.

Answer

Check the concurrency scaling status.

Answer

Run VACUUM on the table.

Question 4

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?

Accepted Answer

Replication between regions is eventually consistent.. 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.

Answer

The read is using strongly consistent reads.

Answer

There is a write conflict that needs to be resolved.

Answer

DynamoDB Streams is not enabled on the table.

Question 5

A data engineer needs to store event data from IoT devices that arrives in bursts. The data is key-value and requires single-digit millisecond read and write latency. The engineer also needs to run complex analytical queries on the data for reporting. Which TWO services should be used together? (Choose TWO.)

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB (A) is correct because it is a fully managed key-value and document NoSQL database that delivers consistent single-digit millisecond read and write latency at any scale, making it ideal for bursty IoT event ingestion. Amazon Redshift (C) is correct because it is a petabyte-scale, columnar data warehouse designed for complex analytical queries and reporting, so it complements DynamoDB by handling the analytics workload. Together, DynamoDB captures the high-throughput key-value events while Redshift serves the reporting and complex query needs. Amazon ElastiCache for Redis (B) is an in-memory cache, not a durable primary store for event data, and it does not natively run complex analytical SQL queries. Amazon S3 (D) is object storage with higher latency and no native single-digit millisecond key-value access or complex query engine. Amazon RDS for MySQL (E) is a relational database that generally cannot match DynamoDB's single-digit millisecond latency at burst scale and is not optimized for large-scale complex analytical reporting like Redshift.

Answer

Amazon ElastiCache for Redis

Answer

Amazon S3

Answer

Amazon RDS for MySQL

Question 6

Refer to the exhibit. A data engineer has attached this bucket policy to an S3 bucket named data-lake-bucket. The engineer wants to allow only GET requests from the corporate network (10.0.0.0/16) over HTTPS. However, users report that they cannot access objects even when connected to the corporate network. What is the issue?

Accepted Answer

The Deny statement blocks all requests that are not using HTTPS, including those from the corporate network.. The Deny statement with `aws:SecureTransport` set to `false` blocks all HTTP requests. Since the Allow statement only permits GET requests from the corporate network (10.0.0.0/16) but does not require HTTPS, any request from that network that uses HTTP is denied by the explicit Deny. The Deny statement overrides the Allow, so even legitimate corporate users are blocked if they use HTTP.

Answer

The Deny statement should include a condition on the source IP.

Answer

The Allow statement should include a condition for SecureTransport.

Answer

The Allow statement should specify s3:GetObject instead of s3:GetObject.

Question 7

Refer to the exhibit. A data engineer needs to connect to the Redshift cluster from an EC2 instance in the same VPC. The engineer can ping the EC2 instance but cannot connect to Redshift using the endpoint address and port 5439. What is the most likely cause?

Accepted Answer

The security group for the Redshift cluster does not allow inbound traffic on port 5439 from the EC2 instance.. The most likely cause is that the security group associated with the Redshift cluster does not have an inbound rule allowing TCP traffic on port 5439 from the security group or IP address of the EC2 instance. Since the engineer can ping the EC2 instance (ICMP works), but cannot connect to Redshift on port 5439, this points to a firewall or security group rule blocking the specific port, not a network reachability issue.

Answer

The Redshift cluster is in a different VPC.

Answer

The Redshift cluster is not in an available state.

Answer

The Redshift cluster is publicly accessible and requires an internet gateway.

Question 8

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?

Accepted Answer

Amazon Athena with data in S3. 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.

Answer

Amazon DynamoDB

Answer

Amazon RDS for MySQL

Answer

Amazon Kinesis Data Analytics

Question 9

A company uses Amazon S3 as its data lake. A data engineer needs to enforce encryption of data at rest using server-side encryption with AWS KMS. Which S3 bucket property should be configured?

Accepted Answer

Default encryption. Configuring default encryption on an S3 bucket ensures that all objects stored in the bucket are encrypted at rest using server-side encryption. When AWS KMS is specified as the encryption type, S3 automatically encrypts objects with a KMS key (SSE-KMS) upon upload, even if the upload request does not include encryption headers. This enforces encryption at rest without requiring changes to client applications.

Answer

Server access logging

Answer

Versioning

Answer

Bucket policy

Question 10

A data engineer notices that an Amazon Redshift cluster’s storage usage is increasing rapidly due to many UPDATE and DELETE operations. The engineer needs to reclaim storage space and improve query performance. Which action should be taken?

Accepted Answer

Run VACUUM command. The VACUUM command in Amazon Redshift reclaims disk space occupied by deleted or updated rows and re-sorts the data according to the table's sort keys. This directly addresses the storage increase from UPDATE/DELETE operations and improves query performance by restoring the physical order of rows, which reduces the number of blocks scanned.

Answer

UNLOAD the table to S3 and reload

Answer

Increase cluster node count

Answer

Run ANALYZE command

Question 11

A data engineer is troubleshooting an Amazon Redshift cluster that is running out of disk space. The engineer runs STV_PARTITIONS and notices that some slices have significantly more data than others. What is the most likely cause and solution?

Accepted Answer

Data distribution skew due to uneven distribution style; change distribution style to EVEN or correct KEY. B is correct because STV_PARTITIONS shows per-slice disk usage, and significant variation indicates data distribution skew. Uneven distribution causes some slices to fill faster, leading to premature disk-full errors. Changing the distribution style to EVEN (for tables without join keys) or correcting the KEY distribution style (using a high-cardinality, evenly distributed column) rebalances data across slices.

Answer

Poorly chosen sort keys; redefine sort keys

Answer

Some nodes are underutilized; add more nodes

Answer

Concurrency scaling is disabled; enable concurrency scaling

Question 12

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

Accepted Answer

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

Answer

Amazon DynamoDB

Answer

Amazon S3

Answer

Amazon Redshift

Question 13

A data engineer is designing a multi-Region disaster recovery solution for an Amazon DynamoDB table. The table must be available in a secondary Region with minimal data loss and automatic failover. Which feature should be used?

Accepted Answer

DynamoDB global tables. DynamoDB global tables provide a fully managed, multi-Region, multi-active database solution that replicates data automatically across selected AWS Regions. This ensures automatic failover with eventual consistency and minimal data loss, meeting the disaster recovery requirements for high availability and automatic failover without manual intervention.

Answer

DynamoDB on-demand backup and restore in the secondary Region

Answer

DynamoDB point-in-time recovery (PITR)

Answer

DynamoDB cross-Region snapshot export to S3

Question 14

Which THREE factors should be considered when choosing a partition key for an Amazon DynamoDB table?

Accepted Answer

If the table has a write-heavy workload, the partition key should distribute writes evenly.. Option B is correct because a write-heavy workload requires the partition key to spread write traffic across many partitions; otherwise a hot partition throttles throughput, since each partition supports a limited write capacity (up to 1,000 WCU per partition). Option C is correct because DynamoDB retrieves items by partition key, so aligning the key with the most common query access pattern enables efficient Query operations instead of expensive Scan operations. Option E is correct because high cardinality produces many distinct partition key values, which distributes items and traffic evenly across partitions and avoids hot partitions. Option A is not a valid factor because item size does not determine partition key choice; DynamoDB partitions data by key value, and large items only consume more capacity, not improve partitioning. Option D is not a valid factor because RCU consumption is driven by item size and consistency model, not by selecting a partition key to minimize reads.

Answer

The partition key should be chosen to maximize the size of items in each partition.

Answer

The partition key should be chosen to minimize read capacity unit consumption.

Question 15

A data engineer needs to store semi-structured JSON transaction logs for analytics. The logs are written once and rarely accessed. The storage must be cost-effective. Which AWS service should be used?

Accepted Answer

Amazon S3. Amazon S3 is the correct choice because it provides highly durable, cost-effective object storage ideal for semi-structured JSON transaction logs that are written once and rarely accessed. S3's lifecycle policies can automatically transition such infrequently accessed data to S3 Glacier or S3 Glacier Deep Archive for even lower storage costs, making it the most economical option for this use case.

Answer

Amazon DynamoDB

Answer

Amazon RDS

Answer

Amazon Redshift