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

Question 1

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 accessed via a REST API. Which AWS services should the engineer consider? (Choose THREE.)

Accepted Answer

Amazon ElastiCache for Redis. Amazon ElastiCache for Redis is correct because it provides an in-memory data store that delivers sub-millisecond read and write latency, ideal for real-time analytics. Redis supports data structures like strings, hashes, and sorted sets, and can be accessed via REST API through a caching layer or directly with Redis commands. This makes it suitable for low-latency, high-throughput workloads where disk-based storage would introduce unacceptable delays.

Answer

Amazon S3

Answer

Amazon RDS for MySQL

Question 2

A data engineer reviewed the S3 lifecycle policy shown in the exhibit. The engineer notices that objects under the 'logs/' prefix are being deleted after 365 days. The business requirement is to retain logs for at least 5 years. What should the engineer change in the lifecycle policy?

Accepted Answer

Set the expiration days to 1825. The business requirement is to retain logs for at least 5 years, which is 1,825 days (5 × 365). The current lifecycle policy sets expiration to 365 days, causing premature deletion. By setting the expiration days to 1,825, the S3 lifecycle policy will delete objects under the 'logs/' prefix only after 5 years, meeting the retention requirement.

Answer

Change the prefix to 'logs/archive/'

Answer

Change the transition to GLACIER on day 365

Answer

Remove the expiration action

Question 3

A data engineer runs the describe-table command shown in the exhibit. The application frequently queries by CustomerID alone. Currently, these queries result in full table scans. Which action should the engineer take to improve query performance?

Accepted Answer

Create a global secondary index on CustomerID. The `describe-table` output shows CustomerID is the partition key, but the application frequently queries by CustomerID alone, which currently causes full table scans because there is no index supporting that query pattern. Creating a global secondary index (GSI) on CustomerID allows efficient querying by CustomerID without scanning the entire table, as the GSI provides a separate data structure with its own read/write capacity that can be queried directly.

Answer

Change the sort key to OrderID

Answer

Create a local secondary index on CustomerID

Answer

Increase the read capacity units to 500

Question 4

A data engineer is troubleshooting a slow Amazon Redshift query that joins several large tables. The query plan shows a large number of broadcasts. Which design change would most likely reduce the broadcast operations?

Accepted Answer

Change the DISTKEY on all tables to match the join column.. Option C is correct because setting the DISTKEY on all tables to the join column ensures that rows with the same join key value are co-located on the same compute node. This allows Redshift to perform a collocated join, eliminating the need to broadcast entire tables across the network, which is the primary cause of the slow query.

Answer

Change the SORT KEY on all tables to match the join column.

Answer

Change the DISTSTYLE to EVEN on all tables.

Answer

Change the DISTSTYLE to ALL on all large tables.

Question 5

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?

Accepted Answer

Enable DynamoDB auto scaling for write capacity with a target utilization of 70%.. Option C is correct because 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.

Answer

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

Answer

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

Answer

Enable DynamoDB Accelerator (DAX) to cache write operations.

Question 6

A data engineer needs to store semi-structured JSON log files from multiple sources and query them using SQL. The data is rarely updated and access frequency is low. Which storage solution is MOST cost-effective?

Accepted Answer

Amazon S3 with Amazon Athena for querying.. Amazon S3 with Athena is the most cost-effective solution because the data is semi-structured JSON, rarely updated, and accessed infrequently. S3 provides low-cost storage for static data, and Athena uses a serverless, pay-per-query model, eliminating the need for a running cluster or provisioned capacity. This combination avoids the fixed costs of Redshift, DynamoDB, or RDS, making it ideal for low-frequency SQL querying of archival logs.

Answer

Amazon Redshift with JSON ingestion and compression.

Answer

Amazon DynamoDB with JSON documents.

Answer

Amazon RDS for PostgreSQL with JSONB columns.

Question 7

A data engineer is designing a data lake on Amazon S3. The data consists of sensitive personally identifiable information (PII) that must be encrypted at rest. The company requires that encryption keys be rotated every 90 days and that access to the keys be logged. Which encryption solution meets these requirements?

Accepted Answer

Use server-side encryption with AWS KMS (SSE-KMS) and enable automatic key rotation.. SSE-KMS with automatic key rotation (Option D) meets the requirements because it encrypts data at rest in S3, allows key rotation every 90 days via AWS KMS automatic rotation, and logs all key usage in AWS CloudTrail for auditing. This provides the necessary encryption, rotation, and access logging without managing keys externally.

Answer

Use server-side encryption with customer-provided keys (SSE-C).

Answer

Use client-side encryption with a master key stored in AWS Secrets Manager.

Answer

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

Question 8

A company uses Amazon RDS for MySQL with Multi-AZ deployment. During a recent failover, the application experienced a brief outage because it cached the old database endpoint. Which solution would minimize application disruption during future failovers?

Accepted Answer

Use the RDS cluster endpoint in the application configuration.. The correct answer is D because the RDS cluster endpoint (also known as the writer endpoint) automatically points to the primary instance in a Multi-AZ deployment. During a failover, DNS is updated to resolve the cluster endpoint to the new primary instance, so the application does not need to cache or change the endpoint. This minimizes disruption by ensuring the application always connects to the current primary without manual intervention.

Answer

Use Amazon ElastiCache to cache database queries and absorb the failover delay.

Answer

Create a read replica in another Availability Zone and promote it during failover.

Answer

Configure the application to connect using the RDS instance's private IP address.

Question 9

A data engineer needs to store time-series sensor data from thousands of IoT devices. The data is written once, read frequently for the last 24 hours, and rarely accessed after 30 days. Which storage solution is MOST cost-effective?

Accepted Answer

Amazon Timestream with a 30-day retention policy.. Amazon Timestream is purpose-built for time-series data, offering automatic storage tiering where recent data resides in memory for fast queries and historical data is moved to a cost-optimized magnetic store. A 30-day retention policy aligns perfectly with the requirement to keep data accessible for frequent reads over the last 24 hours while automatically expiring older data, minimizing storage costs without manual intervention.

Answer

Amazon Redshift with automatic compression and distribution keys.

Answer

Amazon DynamoDB with TTL to expire data after 30 days.

Answer

Amazon S3 with lifecycle policies to transition to S3 Glacier after 30 days.

Question 10

A company is migrating an on-premises Apache Cassandra database to Amazon Keyspaces. The database has a table with a partition key of 'user_id' and a clustering column of 'timestamp'. The application frequently queries the last 10 records for a given user. Which table design in Keyspaces would provide the BEST query performance for this access pattern?

Accepted Answer

Partition key: user_id, clustering column: timestamp (descending order).. Option D is correct because it preserves the original Cassandra table design with 'user_id' as the partition key and 'timestamp' as the clustering column in descending order. This allows Keyspaces to efficiently retrieve the last 10 records for a given user by performing a range query on the clustering column within a single partition, avoiding full table scans or cross-partition queries.

Answer

Partition key: random column, clustering column: none.

Answer

Partition key: timestamp, clustering column: user_id.

Answer

Partition key: user_id, clustering column: none.

Question 11

A data engineer notices that an Amazon Redshift cluster's storage utilization has grown unexpectedly. The cluster uses automatic compression and has a mix of fact and dimension tables. The engineer runs VACUUM and ANALYZE, but storage does not decrease. Which action is most likely to reduce storage consumption?

Accepted Answer

Perform a DEEP COPY on the largest tables.. Option A is correct because DEEP COPY recreates the table with a fresh, optimally sorted and compressed storage layout, reclaiming space that VACUUM alone cannot recover. In Redshift, VACUUM reorganizes and reclaims space from deleted rows but does not re-apply compression or rebuild the underlying storage blocks; DEEP COPY (e.g., using CREATE TABLE AS or the DEEP COPY command) physically rewrites the data, eliminating fragmentation and applying the current compression encoding, which can significantly reduce storage consumption when automatic compression has left suboptimal encodings or when historical updates have bloated the table.

Answer

Run VACUUM with the BOOST option.

Answer

Run ANALYZE with the FULL keyword on all tables.

Answer

Modify the sort key on the largest tables to a more selective column.

Question 12

A company uses Amazon RDS for PostgreSQL. The data engineer needs to ensure that the database is automatically backed up and that backups are retained for 35 days. What is the simplest way to achieve this?

Accepted Answer

Enable automated backups with a retention period of 35 days in the RDS instance configuration.. Amazon RDS for PostgreSQL allows you to enable automated backups directly in the instance configuration. By setting the backup retention period to 35 days, RDS automatically performs daily snapshots and retains transaction logs for point-in-time recovery within that window. This is the simplest method because it requires no external services or custom scripting.

Answer

Use AWS Backup to schedule daily backups with a 35-day retention.

Answer

Create a manual snapshot every day and delete them after 35 days using a script.

Answer

Enable automatic export of transaction logs to Amazon S3 and use S3 lifecycle policies.

Question 13

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.)

Accepted Answer

Use AWS Lake Formation to define column-level permissions for sensitive data.. Option A is correct because 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.

Answer

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

Answer

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

Answer

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

Question 14

A company uses Amazon DynamoDB for a gaming application that requires single-digit millisecond read and write latencies. The application experiences throttling on the 'GameScores' table during peak hours. The table has a partition key of 'game_id' and a sort key of 'player_id'. The data engineer needs to improve performance without changing the table's provisioned capacity. Which THREE actions should the engineer take? (Choose THREE.)

Accepted Answer

Enable DynamoDB auto scaling to adjust capacity based on traffic patterns.. Option B is correct because DynamoDB auto scaling automatically adjusts the provisioned read and write capacity based on actual traffic patterns, preventing throttling during peak hours without manual intervention. This allows the table to handle bursts while maintaining single-digit millisecond latencies, as long as the traffic stays within the auto scaling limits.

Answer

Enable DynamoDB adaptive capacity to allow more throughput per partition.

Answer

Increase the read capacity units (RCUs) to twice the peak observed value.

Question 15

A data engineer is migrating an on-premises Microsoft SQL Server database to Amazon RDS for SQL Server. The database is 2 TB in size and has a 4-hour maintenance window. The company needs to minimize downtime and ensure data consistency. Which TWO methods should the engineer use? (Choose TWO.)

Accepted Answer

Use AWS Database Migration Service (AWS DMS) with ongoing replication to minimize downtime.. AWS DMS with ongoing replication (change data capture) is correct because it allows continuous synchronization from the on-premises SQL Server to Amazon RDS for SQL Server, minimizing downtime by keeping the target database up-to-date until the final cutover. This approach ensures data consistency by capturing and applying ongoing changes without requiring a long outage window.

Answer

Use SQL Server Management Studio (SSMS) export wizard to transfer data.

Answer

Use AWS Schema Conversion Tool (AWS SCT) to convert the schema and migrate data.

Answer

Export the database to CSV files and use BULK INSERT to load into RDS.