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

Question 1

A company is migrating an on-premises PostgreSQL database to Amazon Aurora PostgreSQL. The database is 2 TB in size. The migration must have minimal downtime. Which approach should the data engineer use?

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

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication to minimize downtime.. AWS DMS with ongoing replication (change data capture) is the correct approach because it allows a full load of the 2 TB database followed by continuous replication of changes from the on-premises PostgreSQL source to the Aurora PostgreSQL target, minimizing downtime to a short cutover window. This is the only option that supports near-zero downtime migration for large databases by capturing ongoing transactions.

Answer

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

Answer

Create an Aurora read replica from the on-premises database using native replication.

Answer

Use pg_dump to export the database and pg_restore to import into Aurora.

Question 2

A data engineer is designing a data store for a real-time analytics application that requires sub-millisecond read and write latency. The data is key-value in nature and the workload is both read-heavy and write-heavy. Which AWS service is most suitable?

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is the most suitable service because it is a fully managed NoSQL key-value and document database designed for single-digit millisecond latency at any scale, and with features like DynamoDB Accelerator (DAX) it can achieve sub-millisecond read latency. It supports both read-heavy and write-heavy workloads through its distributed architecture and auto-scaling capabilities, making it ideal for real-time analytics applications.

Answer

Amazon ElastiCache for Redis

Answer

Amazon RDS for MySQL

Answer

Amazon S3

Question 3

A company uses Amazon Redshift for its data warehouse. The data engineer notices that queries are running slower than expected. The system administrator reports that the cluster's disk space is 80% full. Which action should the engineer take to improve query performance?

Accepted Answer

Add more nodes to the cluster to increase storage and compute capacity.. When a Redshift cluster's disk space is 80% full, query performance degrades because Redshift relies on large sequential I/O operations, and high disk utilization forces more random I/O and increases the likelihood of spilling to disk. Adding nodes increases both storage capacity and compute resources, directly alleviating the I/O bottleneck and improving query throughput. This is the recommended scaling action when disk space exceeds 70-80% utilization.

Answer

Redesign the sort keys to optimize query performance.

Answer

Run the VACUUM command to reclaim space.

Answer

Enable concurrency scaling to handle more queries.

Question 4

A data engineer is setting up an Amazon S3 bucket for storing sensitive financial data. The compliance team requires that all data be encrypted at rest using a customer-managed AWS KMS key. Additionally, the bucket must block public access. Which combination of settings should the engineer configure?

Accepted Answer

Enable default encryption with AWS-KMS and a customer managed key. Enable block public access settings.. Enabling default encryption with AWS-KMS using a customer-managed key ensures that all objects uploaded to the S3 bucket are automatically encrypted at rest with the required key type. Additionally, enabling block public access settings prevents any public access to the bucket, satisfying the compliance team's requirements.

Answer

Use S3 Object Ownership to enforce bucket owner enforced. Enable block public access.

Answer

Use S3 Bucket Keys to reduce KMS costs. Enable block public access.

Answer

Create a bucket policy that denies PutObject without encryption. Enable block public access.

Question 5

A data engineer needs to store log files from multiple applications in a centralized location. The logs are generated in JSON format and each log entry is about 1 KB. The engineer needs to query the logs occasionally using SQL-like queries. Which AWS service is most appropriate?

Accepted Answer

Amazon Athena with data stored in S3. Amazon Athena is the most appropriate service because it allows you to query log files stored in S3 directly using standard SQL, without needing to load or transform the data. Since the logs are in JSON format and each entry is about 1 KB, Athena's schema-on-read approach works perfectly for occasional SQL-like queries, and you only pay for the data scanned per query, making it cost-effective for infrequent access.

Answer

Amazon DynamoDB

Answer

Amazon Redshift

Answer

Amazon RDS for MySQL

Question 6

A data engineer is designing a disaster recovery plan for an Amazon RDS for MySQL database. The database must be recoverable within 1 hour in a different AWS Region. Which TWO actions should the engineer take?

Accepted Answer

Create a cross-Region read replica.. A cross-Region read replica for Amazon RDS for MySQL provides a fully provisioned secondary database in a different AWS Region that can be promoted to a standalone primary in minutes, meeting the 1-hour recovery time objective (RTO). This approach ensures continuous replication from the source database, minimizing data loss and enabling rapid failover without manual snapshot management.

Answer

Enable Multi-AZ deployment.

Answer

Take manual snapshots and copy them to an S3 bucket in the other Region.

Answer

Use Amazon EventBridge to schedule snapshot copies.

Question 7

A company uses Amazon DynamoDB to store session data for a web application. The application experiences throttling during peak hours. The data engineer needs to reduce throttling. Which THREE actions should the engineer take?

Accepted Answer

Use DynamoDB Accelerator (DAX) to cache read requests.. DynamoDB Accelerator (DAX) is an in-memory cache that reduces read latency and offloads read requests from the DynamoDB table, directly decreasing the number of read capacity units consumed. By caching frequently accessed session data, DAX mitigates throttling during peak hours without requiring changes to the table's provisioned capacity.

Answer

Increase the provisioned read capacity units.

Answer

Enable auto scaling on the table.

Question 8

A data engineer is migrating a large Oracle data warehouse to Amazon Redshift. The engineer needs to ensure optimal performance. Which TWO practices should the engineer follow?

Accepted Answer

Choose appropriate sort keys based on common query patterns.. Amazon Redshift uses sort keys to physically order data on disk, which allows the query optimizer to skip large blocks of data during scans via zone maps. Choosing sort keys based on common query patterns (e.g., range filters or frequent GROUP BY columns) dramatically reduces I/O and improves query performance, especially for large tables.

Answer

Design the schema as a normalized star schema with row-based storage.

Answer

Manually define compression encodings for each column.

Answer

Stage data in Amazon S3 before loading into Redshift.

Question 9

A company runs an e-commerce platform on AWS. The product catalog is stored in Amazon DynamoDB with a table that has a partition key of 'product_id' and a sort key of 'category'. The application frequently queries products by category and by product_id. Recently, the operations team noticed that read latency has increased significantly for queries that filter by category. The DynamoDB table has auto scaling enabled. The data engineer examines the CloudWatch metrics and sees that the ReadThrottleEvents metric is non-zero for the table, but the consumed read capacity is well below the provisioned limit. The table has a global secondary index (GSI) on the 'category' attribute. Which action is most likely to resolve the latency issue?

Accepted Answer

Redesign the GSI partition key to include a random suffix to distribute load across multiple partitions.. The issue is that the GSI on 'category' is experiencing hot partitions because 'category' has low cardinality, causing uneven data distribution. The non-zero ReadThrottleEvents on the GSI (not the main table) indicate throttling on the GSI's provisioned capacity, even though the main table's consumed read capacity is below its limit. Adding a random suffix to the GSI partition key distributes reads across multiple physical partitions, reducing hot spots and latency.

Answer

Switch the table to DynamoDB on-demand capacity mode.

Answer

Enable DynamoDB Accelerator (DAX) to cache read queries.

Answer

Increase the provisioned read capacity on the main table.

Question 10

A media company stores video metadata in Amazon RDS for PostgreSQL. The database is 500 GB and experiences high write traffic. The data engineer notices that the transaction log (WAL) is growing rapidly, causing storage issues. The company needs to retain backups for 30 days for compliance. The database is currently using automated backups with a retention period of 7 days. Which solution should the engineer implement to address the WAL growth while meeting compliance requirements?

Accepted Answer

Create manual snapshots daily and delete automated backups.. Creating manual snapshots daily and reducing or eliminating automated backups addresses WAL growth. Automated backups in RDS for PostgreSQL depend on WAL files to enable point-in-time recovery. By disabling automated backups or setting a very short retention period, RDS can purge WAL segments more aggressively, preventing accumulation. Manual snapshots can be retained for 30 days to meet compliance requirements without relying on WAL. Increasing backup retention (Option C) would worsen WAL storage, while streaming WAL to S3 (Option D) is not natively supported in RDS. Option B only adds storage without addressing the root cause.

Answer

Change the instance type to a larger one with more storage.

Answer

Increase the backup retention period to 30 days.

Answer

Configure the database to stream WAL files to Amazon S3.

Question 11

A startup uses Amazon S3 to store user-uploaded images. The images are accessed frequently for the first week after upload, but after that they are rarely accessed. The company wants to optimize storage costs without compromising availability. The data engineer must implement a lifecycle policy to transition objects to a more cost-effective storage class after 30 days. The objects must be retrievable within minutes. Which storage class should the engineer transition the objects to?

Accepted Answer

S3 Standard-Infrequent Access. S3 Standard-Infrequent Access (S3 Standard-IA) is the correct choice because it offers a per-GB storage cost lower than S3 Standard while maintaining high durability (99.999999999%) and availability (99.9%), with retrieval times in milliseconds. The lifecycle policy transitions objects after 30 days, aligning with the access pattern where images are rarely accessed after the first week, and the requirement for retrieval within minutes is satisfied by S3 Standard-IA's instant access.

Answer

S3 One Zone-Infrequent Access

Answer

S3 Standard

Answer

S3 Glacier Instant Retrieval

Question 12

A company is using an Amazon RDS for MySQL database for its e-commerce platform. During a recent flash sale, the database experienced high read traffic, causing slow query performance. The company needs a solution that offloads read traffic with minimal application changes. Which action should be taken?

Accepted Answer

Create an Amazon RDS read replica in the same region.. Creating an Amazon RDS read replica in the same region offloads read traffic from the primary DB instance by directing read queries to a read-only copy. This requires minimal application changes—only modifying the database connection string to point read queries to the replica endpoint. RDS read replicas use MySQL's native asynchronous replication, making them ideal for scaling read-heavy workloads like flash sales.

Answer

Enable DynamoDB Accelerator (DAX) on the RDS instance.

Answer

Migrate the database to Amazon Aurora and enable Aurora Global Database.

Answer

Implement Amazon ElastiCache for Redis to cache database queries.

Question 13

A company uses Amazon DynamoDB as the primary data store for a gaming application. The application stores user profiles and game state. During peak hours, the application experiences throttling on writes to the UserProfiles table. The table's read capacity is underutilized. Which solution should resolve the write throttling?

Accepted Answer

Increase the provisioned write capacity units for the table.. Write throttling occurs when the number of write requests exceeds the provisioned write capacity units (WCUs) for the DynamoDB table. Since the read capacity is underutilized, the correct solution is to increase the provisioned WCUs to accommodate the peak write traffic. This directly addresses the capacity deficit without affecting read operations.

Answer

Enable DynamoDB Accelerator (DAX) on the table.

Answer

Add a global secondary index (GSI) to the table.

Answer

Configure auto scaling for read capacity units.

Question 14

A company is running a data warehouse on Amazon Redshift. The data engineering team notices that query performance has degraded over time. They suspect that data distribution is causing excessive data movement between nodes. The table is joined frequently on the customer_id column. Which column should be chosen as the distribution key to optimize join performance?

Accepted Answer

customer_id. (customer_id) because Redshift distributes data across nodes based on the distribution key. When two tables are joined on customer_id, using it as the distribution key ensures that matching rows from both tables are co-located on the same node, eliminating the need for data redistribution (broadcast or shuffle) during the join. This minimizes network traffic and reduces query latency, directly addressing the performance degradation caused by excessive data movement.

Answer

AUTO distribution

Answer

order_date

Answer

EVEN distribution

Question 15

A company uses Amazon S3 to store sensitive financial data. The security team requires that all objects be encrypted at rest using AWS KMS with a customer-managed key. Additionally, they want to audit all KMS decrypt calls for compliance. Which configuration should be used to meet these requirements?

Accepted Answer

Use SSE-KMS with a customer-managed KMS key and enable CloudTrail data events for the key.. SSE-KMS with a customer-managed key allows the company to control the encryption key lifecycle and meet the requirement for customer-managed keys. Enabling CloudTrail data events for the KMS key captures all decrypt API calls, providing the necessary audit trail for compliance.

Answer

Enable default encryption on the bucket with SSE-KMS using an AWS managed key.

Answer

Use SSE-S3 with a bucket policy that denies uploads without encryption.

Answer

Use SSE-C with client-managed keys and log S3 API calls.