DEA-C01 Data Store Management — 100 Questions

Question 1

A data engineering team uses Amazon Redshift for analytics. They notice that queries on a large fact table are slow. The table is distributed using DISTSTYLE ALL. Which design change would most likely improve query performance?

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

Accepted Answer

Change DISTSTYLE to KEY on a column used in frequent joins.. DISTSTYLE ALL copies the entire table to every node, which is inefficient for large fact tables because it wastes storage and network bandwidth during data loading and query execution. Changing to DISTSTYLE KEY on a column used in frequent joins collocates related rows on the same slice, reducing the need to broadcast or redistribute data across the network during joins, which directly improves query performance.

Answer

Change DISTSTYLE to EVEN to distribute rows evenly across slices.

Answer

Increase the number of nodes in the Redshift cluster.

Answer

Change the table to use a SORTKEY on the most frequently filtered column.

Question 2

Which TWO actions should a data engineer take to encrypt data at rest in an Amazon S3 bucket? (Select TWO.)

Accepted Answer

Configure the bucket to use SSE-KMS.. Options C and D are correct because both configure server-side encryption at rest for objects stored in the S3 bucket: SSE-KMS (C) encrypts objects with AWS KMS customer master keys, giving key management, auditability via CloudTrail, and granular access control, while enabling default encryption with SSE-S3 (D) applies AES-256 encryption managed by Amazon S3 to every object put into the bucket without requiring per-request headers. Both satisfy the requirement to encrypt data at rest in Amazon S3. Option A is wrong because S3 Transfer Acceleration only speeds up uploads over AWS edge locations and does not encrypt data. Option B is not among the marked correct answers here even though client-side encryption can protect data, and Option E is wrong because a bucket policy denying unencrypted PUT requests only enforces that encryption headers are present; it does not itself encrypt data at rest.

Answer

Enable S3 Transfer Acceleration on the bucket.

Answer

Use client-side encryption before uploading objects to S3.

Answer

Attach a bucket policy that denies unencrypted PUT requests.

Question 3

A data engineer is designing a data lake on Amazon S3. The data must be immutable and support high-throughput streaming ingestion. Which THREE features should the engineer consider? (Select THREE.)

Accepted Answer

S3 Lifecycle policies to transition data to Amazon S3 Glacier. Option B is correct because S3 Lifecycle policies can transition aging data to Amazon S3 Glacier storage classes, which supports the data lake's long-term retention and cost-efficiency needs while keeping data durable and immutable once archived. Option C is correct because the S3 Multipart Upload API allows large objects to be uploaded in parallel parts, which is essential for high-throughput streaming ingestion and for efficiently handling large data files. Option D is correct because S3 Object Lock in governance mode enforces immutability by preventing object deletion or overwrite for a defined retention period, directly satisfying the immutability requirement. Option A is not correct because S3 Transfer Acceleration speeds up uploads over long distances using edge locations, but it does not provide immutability or specifically enable high-throughput streaming ingestion. Option E is not correct because Cross-Region Replication copies objects to another region for durability and compliance, but it is not required for immutability or high-throughput ingestion and can actually complicate immutability guarantees if not carefully configured.

Answer

S3 Transfer Acceleration

Answer

S3 Cross-Region Replication (CRR)

Question 4

A company uses Amazon DynamoDB with on-demand capacity. They notice higher than expected costs due to a sudden spike in read traffic from a reporting job. The reporting job scans the entire table daily. What is the most cost-effective way to reduce costs while maintaining the same reporting output?

Accepted Answer

Use a Global Secondary Index (GSI) with a sort key that matches the reporting query pattern.. Using a Global Secondary Index (GSI) with a sort key tailored to the reporting query pattern allows the reporting job to query only the relevant items instead of scanning the entire table. This reduces the read capacity units consumed per operation, directly lowering costs under on-demand capacity, which charges per RCU consumed. The reporting output remains identical because the GSI returns the same data filtered by the query pattern.

Answer

Enable DynamoDB Accelerator (DAX) for caching.

Answer

Set a TTL attribute to automatically expire old data.

Answer

Reduce the read capacity units (RCU) in the table.

Question 5

A data engineer has set up an Amazon S3 lifecycle policy to transition objects to Glacier Instant Retrieval after 30 days. After 60 days, objects should transition to Deep Archive. However, objects are not transitioning to Deep Archive. What is the most likely cause?

Accepted Answer

Objects are smaller than 128 KB.. Amazon S3 lifecycle policies have a minimum object size requirement for transitions to certain storage classes. Objects must be at least 128 KB to transition to S3 Glacier Instant Retrieval. Since the policy first transitions objects to Glacier Instant Retrieval after 30 days, objects smaller than 128 KB cannot undergo that transition, and subsequent transitions to Deep Archive will also fail. The 30-day minimum interval requirement is met (30 days between transitions), so option D is not the cause. Versioning (A) does not prevent transitions, and Deep Archive (B) is supported in all commercial AWS regions. Therefore, the most likely cause is that objects are smaller than 128 KB.

Answer

The bucket has versioning enabled.

Answer

Deep Archive is not supported in the bucket's region.

Answer

The transition to Deep Archive requires a minimum of 30 days after the previous transition.

Question 6

A data engineer attaches the above IAM policy to an IAM user. The user tries to download an object from my-bucket using the AWS CLI without specifying SSE headers. The object is stored with SSE-S3. Will the download succeed?

Accepted Answer

Yes, because the policy allows s3:GetObject on the bucket.. For SSE-S3 objects, no additional encryption headers are required on GET requests because S3 handles decryption transparently for authorized users. The IAM policy grants s3:GetObject, so the download succeeds.

Answer

No, because the object is encrypted and the user does not have decrypt permission.

Answer

No, because the request does not include the required encryption header.

Answer

Yes, because the object is encrypted with SSE-S3, which uses AES256.

Question 7

A company uses Amazon DynamoDB for a gaming leaderboard. The table has a partition key of 'game_id' and a sort key of 'score'. The read capacity is provisioned at 1000 RCUs. During peak hours, users report high latency when querying the top 10 scores for a specific game. The DynamoDB metrics show ConsumedReadCapacityUnits averaging 800 but occasional throttling. What is the most likely cause and solution?

Accepted Answer

The hot game_id partition is exceeding its throughput; add DynamoDB Accelerator (DAX) to cache reads. The hot game_id partition is exceeding its provisioned throughput because DynamoDB distributes RCUs evenly across partitions, and a single partition can only handle up to (1000 RCUs / number of partitions) per second. When a specific game_id becomes popular, all reads hit the same partition, causing throttling despite low overall consumed capacity. Adding DynamoDB Accelerator (DAX) caches the top 10 scores for that partition, reducing read pressure and eliminating throttling without increasing RCUs.

Answer

Create a global secondary index with the same key schema to distribute reads

Answer

Remove the sort key and use a global secondary index

Answer

Increase the provisioned RCUs to 2000

Question 8

A data engineer runs the above AWS CLI command and receives the output. The object is part of an S3 Lifecycle policy that transitions objects to Glacier Instant Retrieval after 30 days. The object was created on January 1, 2023. Why is the object still in STANDARD_IA storage class?

Accepted Answer

The object has not reached the transition age of 30 days yet. The S3 Lifecycle rule transitions objects to Glacier Instant Retrieval after 30 days, but the object was created on January 1, 2023, and the current date (implied by the command output) is before January 31, 2023. The transition age is calculated from the object's LastModified date, not from any other timestamp, and the object must be at least 30 days old before S3 applies the transition. Since the object is only 20 days old (as of January 21, 2023, based on the output showing STANDARD_IA), it has not yet met the 30-day threshold.

Answer

The Lifecycle policy has a filter that excludes this object's prefix

Answer

Versioning is enabled and the current version is not the oldest

Answer

The metadata timestamp is used for lifecycle transitions instead of LastModified

Question 9

A data engineer is designing a data lake on Amazon S3 for analytics. The data includes sensitive PII that must be encrypted at rest. The company requires that the encryption keys be managed by the company's own hardware security module (HSM) and rotated every 90 days. Which TWO options meet these requirements? (Choose TWO.)

Accepted Answer

Use S3 server-side encryption with customer-provided keys (SSE-C). Option B (SSE-C) is correct because S3 server-side encryption with customer-provided keys lets the company supply and fully control its own encryption keys, which can be generated and stored in the company's own HSM, and the company can rotate those keys every 90 days by simply providing new keys on each request. Option D (SSE-KMS with a customer-managed key using imported key material from your HSM) is correct because a customer-managed KMS key gives the company control over the key, imported key material can originate from the company's own HSM, and the customer-managed key can be rotated on a 90-day schedule (manually or via automatic rotation where supported). Option A is incorrect because an AWS managed key is created and managed by AWS, so the company cannot control it with its own HSM or set a 90-day rotation. Option C is incorrect because storing client-side encryption keys in AWS Secrets Manager does not satisfy the requirement that keys be managed by the company's own HSM. Option E is incorrect because SSE-S3 uses keys fully managed and rotated by AWS, giving the company no HSM control or custom 90-day rotation.

Answer

Use S3 server-side encryption with AWS KMS and an AWS managed key

Answer

Use client-side encryption with keys stored in AWS Secrets Manager

Answer

Use S3 server-side encryption with S3 managed keys (SSE-S3)

Question 10

A data engineering team is designing a data lake on Amazon S3 with a folder structure that separates raw, transformed, and curated data. The team needs to implement lifecycle policies to minimize storage costs while ensuring that data in the 'raw' zone is retained for 90 days before being moved to Amazon S3 Glacier Deep Archive. Additionally, data in the 'curated' zone should be deleted after 365 days. What is the MOST cost-effective way to achieve these requirements?

Accepted Answer

Create a single lifecycle policy with two rules: one to transition raw zone objects to S3 Glacier Deep Archive after 90 days, and another to delete curated zone objects after 365 days.. A single S3 lifecycle policy can contain multiple rules, each applying to different prefixes. This allows you to transition raw zone objects to S3 Glacier Deep Archive after 90 days and delete curated zone objects after 365 days, minimizing storage costs without needing separate policies. S3 lifecycle policies are evaluated per object based on creation date, and using one policy reduces management overhead.

Answer

Configure S3 Intelligent-Tiering on both prefixes with automatic archiving to Glacier Deep Archive after 90 days in raw and deletion after 365 days in curated.

Answer

Create separate lifecycle policies for each prefix: one to transition raw to S3 Glacier Deep Archive after 90 days, another to delete curated after 365 days.

Answer

Create a lifecycle policy that transitions raw zone data to S3 Standard-IA after 90 days and deletes curated zone data after 365 days.

Question 11

A data engineer is setting up an Amazon Redshift cluster for a data warehouse. The cluster will store historical sales data and support complex analytical queries. To optimize query performance and manage storage, the engineer needs to choose appropriate distribution styles and sort keys for a large fact table 'sales' and several dimension tables. Which TWO of the following design decisions are BEST practices?

Accepted Answer

Use interleaved sort keys on columns that are frequently used in filter predicates (e.g., date, region, product).. Interleaved sort keys in Amazon Redshift give equal weight to each column in the sort key, making them ideal for queries with filter predicates on multiple columns (e.g., date, region, product). This design optimizes zone maps and minimizes the amount of data scanned, significantly improving query performance for complex analytical workloads on large fact tables.

Answer

Use ALL distribution for the 'sales' fact table to replicate data to every node and avoid data movement.

Answer

Use a compound sort key with the most frequently filtered column first.

Answer

Choose AUTO distribution style for all tables and let Amazon Redshift automatically assign distribution.

Question 12

A data engineer ran the above CLI command to describe an Amazon DynamoDB table named 'Orders'. The table has a key schema with 'OrderID' as the partition key and 'CustomerID' as the sort key. The table currently has no items. The engineer wants to add a new attribute 'OrderDate' and then query all orders for a specific customer within a date range. Which of the following actions is the MOST efficient approach to support this query pattern?

Accepted Answer

Create a Global Secondary Index (GSI) with 'CustomerID' as partition key and 'OrderDate' as sort key.. A Global Secondary Index (GSI) allows querying on a different partition key ('CustomerID') and sort key ('OrderDate') without altering the base table's key schema. This supports efficient range queries on 'OrderDate' for a specific customer, as GSIs provide a separate index with its own provisioned throughput and can be created on existing tables with items. The base table's primary key remains unchanged, and the GSI enables the desired query pattern with low latency.

Answer

Modify the table's primary key to include 'OrderDate' as an additional sort key.

Answer

Use a Scan operation with a filter expression on 'CustomerID' and 'OrderDate' to retrieve the data.

Answer

Create a Local Secondary Index (LSI) with 'CustomerID' as partition key and 'OrderDate' as sort key.

Question 13

Arrange the steps to set up cross-region replication for an S3 bucket.

Accepted Answer

Enable versioning on source and destination, then create the destination bucket, then add a replication rule, then assign an IAM role.. First, enable versioning on source and destination. Then create the destination bucket, add a replication rule, and assign an IAM role for replication.

Answer

Create the destination bucket first, then enable versioning on source and destination, then add a replication rule, then assign an IAM role.

Answer

Enable versioning on source and destination, then add a replication rule, then create the destination bucket, then assign an IAM role.

Answer

Enable versioning on source and destination, then create the destination bucket, then assign an IAM role, then add a replication rule.

Question 14

A company uses Amazon S3 to store sensitive customer data. The security team requires that all objects uploaded to a specific bucket be encrypted at rest using AWS KMS with a customer managed key. Which bucket policy statement should be applied to enforce this requirement?

Accepted Answer

Deny s3:PutObject unless s3:x-amz-server-side-encryption-aws-kms-key-id equals the specific KMS key ARN. The security team requires encryption at rest using AWS KMS with a customer managed key. The bucket policy must deny any s3:PutObject request that does not include the s3:x-amz-server-side-encryption-aws-kms-key-id condition key set to the specific KMS key ARN. This ensures that only objects encrypted with the designated customer managed key are allowed, enforcing the encryption requirement at the bucket policy level.

Answer

Deny s3:PutObject unless s3:x-amz-server-side-encryption is present

Answer

Allow s3:PutObject only if s3:x-amz-server-side-encryption is present

Answer

Deny s3:PutObject unless s3:x-amz-server-side-encryption equals AES256

Question 15

A company runs an Amazon RDS for MySQL database. The database experiences high write latency during peak hours. The data engineer notices that the WriteIOPS metric is consistently at the provisioned limit. Which action would most effectively reduce write latency without increasing costs?

Accepted Answer

Migrate to Amazon Aurora MySQL with appropriate instance size. Migrating to Amazon Aurora MySQL with an appropriate instance size reduces write latency because Aurora’s distributed storage architecture provides up to 20 times the write throughput of standard MySQL on RDS, without requiring additional IOPS provisioning. Aurora automatically scales storage I/O and uses a 6-replica quorum-based write model, which eliminates the bottleneck of hitting a fixed IOPS limit while keeping costs comparable to or lower than provisioned IOPS on RDS.

Answer

Enable Multi-AZ deployment

Answer

Increase the provisioned IOPS on the existing RDS instance

Answer

Add a read replica to offload read traffic

Question 16

A data engineer is configuring Amazon S3 Lifecycle policies to transition objects between storage classes. The data is accessed frequently for the first 30 days, then rarely for the next 90 days, after which it must be archived. The engineer wants to minimize costs while ensuring immediate retrieval for the first 30 days. Which lifecycle policy should the engineer implement?

Accepted Answer

Transition to Standard-IA after 30 days, then to Glacier Deep Archive after 120 days. It transitions objects from S3 Standard (immediate retrieval, frequent access) to S3 Standard-IA (lower cost for infrequent access, immediate retrieval) after 30 days, then to S3 Glacier Deep Archive (lowest-cost archival storage) after 120 days. This matches the access pattern: frequent for 30 days, rare for 90 days, then archived, while minimizing cost and maintaining immediate retrieval for the first 30 days.

Answer

Transition to Glacier Flexible Retrieval after 30 days, then delete after 120 days

Answer

Transition to One Zone-IA after 30 days, then to Glacier Deep Archive after 120 days

Answer

Transition to Glacier Deep Archive after 30 days, then delete after 120 days

Question 17

A data engineer needs to store a large number of small files (each a few KB) from IoT sensors. The data is written once and never modified. The primary requirement is high write throughput and low latency for writes. Which storage solution is most suitable?

Accepted Answer

Amazon DynamoDB with on-demand capacity. Amazon DynamoDB with on-demand capacity is the most suitable because it is a NoSQL key-value and document database designed for single-digit millisecond latency at any scale. It supports high write throughput by automatically distributing data across multiple partitions, and on-demand capacity eliminates the need for provisioning, allowing it to absorb unpredictable write spikes from many IoT sensors without throttling.

Answer

Amazon RDS for MySQL with InnoDB

Answer

Amazon S3 with standard storage class

Answer

Amazon Elastic Block Store (EBS) volumes

Question 18

A company uses Amazon Redshift for analytics. A data engineer notices that queries are slow due to high disk usage on the compute nodes. The engineer needs to reclaim disk space without interrupting ongoing queries. Which action should the engineer take?

Accepted Answer

Run VACUUM DELETE to reclaim space from deleted rows. VACUUM DELETE specifically reclaims disk space from deleted rows without requiring an exclusive table lock, allowing ongoing queries to continue. In Amazon Redshift, deleted rows consume disk space until reclaimed, and VACUUM DELETE operates in the background to free that space while maintaining query concurrency.

Answer

Use the COPY command to reload data

Answer

Run VACUUM FULL on all tables

Answer

Resize the cluster to a larger instance type

Question 19

A data engineer is designing a data lake on Amazon S3. The data lake must support both batch and streaming ingestion. Which TWO AWS services can ingest data directly into S3? (Choose TWO.)

Accepted Answer

AWS Glue. AWS Glue is correct because it can ingest data directly into S3 via AWS Glue crawlers and ETL jobs, which read from various sources and write the processed data to S3. Amazon Kinesis Data Firehose is correct because it is a fully managed service that can capture, transform, and load streaming data directly into S3 without requiring custom code.

Answer

Amazon RDS

Answer

Amazon EMR

Answer

Amazon DynamoDB

Question 20

A company is migrating an on-premises Apache Hadoop cluster to Amazon EMR. The data is stored in HDFS and must be moved to Amazon S3. Which THREE considerations are important when designing the migration? (Choose THREE.)

Accepted Answer

EMR can read data directly from S3 using EMRFS. Option C is correct because EMRFS (EMR File System) is the Hadoop-compatible file system implementation in Amazon EMR that lets Spark, Hive, and MapReduce jobs read and write data directly in Amazon S3, so HDFS data can be migrated to S3 and accessed without a persistent HDFS layer. Option D is correct because S3 provides eventual consistency for overwrite PUTs and DELETEs (while new object PUTs are read-after-write consistent), which matters when designing migration workflows that overwrite or delete objects, since jobs may briefly see stale data or listings. Option E is correct because decoupling storage (S3) from compute (EMR) enables independent scaling — you can size and terminate EMR clusters based on compute needs while data persists in S3, unlike an HDFS-based cluster where storage scales with the nodes. Option A is incorrect because S3 is an object store and does not provide POSIX file system semantics such as true rename, file locking, or random in-place writes. Option B is incorrect because HDFS cannot be directly mounted as an S3 bucket; S3 is accessed through the S3 API or EMRFS, not as an HDFS mount point.

Answer

S3 supports POSIX file system semantics

Answer

HDFS can be directly mounted as an S3 bucket