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

Question 1

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?

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

Accepted Answer

Enable DynamoDB auto scaling for write capacity with a target utilization of 70%.. 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 2

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 3

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 4

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 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 5

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 6

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

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

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 9

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

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

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.

Question 12

A company stores sensitive customer data in Amazon S3. The security team requires that all objects be encrypted at rest using server-side encryption with customer-provided keys (SSE-C). Which bucket policy condition will enforce this requirement?

Accepted Answer

s3:x-amz-server-side-encryption-customer-key. The condition key `s3:x-amz-server-side-encryption-customer-key` is specifically used to enforce that objects uploaded to S3 must use server-side encryption with customer-provided keys (SSE-C). This condition key checks for the presence of the `x-amz-server-side-encryption-customer-key` header in the request, which is required for SSE-C encryption. Without this header, the request is denied, ensuring all objects are encrypted at rest using customer-provided keys.

Answer

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

Answer

s3:x-amz-server-side-encryption

Answer

s3:x-amz-server-side-encryption-customer-algorithm

Question 13

A data engineer needs to store semi-structured JSON data for a real-time analytics application. The data will be queried using SQL-like statements and must support high-speed ingestion with minimal latency. Which AWS service is best suited for this use case?

Accepted Answer

Amazon Kinesis Data Analytics. Amazon Kinesis Data Analytics is best suited because it natively processes streaming JSON data using SQL-like statements (via Kinesis Data Analytics for SQL applications) with sub-second latency, enabling real-time analytics on semi-structured data without requiring a separate storage layer for ingestion. It directly integrates with Kinesis Data Streams or Firehose for high-speed ingestion and supports in-application queries on JSON payloads using the `json_extract` function or schema discovery.

Answer

Amazon S3

Answer

Amazon Redshift

Answer

Amazon DynamoDB

Question 14

A company is running a MySQL database on Amazon RDS. The database size is 2 TB, and the company needs to migrate it to Amazon Aurora MySQL with minimal downtime. Which migration strategy is most appropriate?

Accepted Answer

Create an Aurora MySQL read replica from the RDS instance, then promote it.. Creating an Aurora MySQL read replica from the existing RDS MySQL instance allows the Aurora cluster to stay synchronized with the source using MySQL’s native binlog replication. Once the replica lag reaches zero, you can promote it to a standalone Aurora cluster with minimal downtime, typically just a few seconds to stop writes and redirect traffic. This approach avoids the lengthy export/import process and leverages Amazon’s managed replication for near-zero-downtime migration.

Answer

Use mysqldump to export the database and import it into Aurora.

Answer

Take a snapshot of the RDS instance and restore it as an Aurora cluster.

Answer

Use AWS Database Migration Service (DMS) with full load and ongoing replication.

Question 15

A data engineer notices that an Amazon Redshift cluster is experiencing slow query performance. The engineer suspects that tables are not properly sorted. Which diagnostic query should the engineer run to identify unsorted rows?

Accepted Answer

SELECT * FROM SVV_TABLE_INFO ORDER BY unsorted DESC;. The `SVV_TABLE_INFO` system view in Amazon Redshift provides metadata about each table, including the `unsorted` column which shows the percentage of unsorted rows. By ordering by `unsorted DESC`, the engineer can quickly identify tables with the highest proportion of unsorted data, which directly impacts query performance due to inefficient zone maps and scan pruning.

Answer

SELECT * FROM PG_CATALOG;

Answer

SELECT * FROM STV_TBL_PERM;

Answer

SELECT * FROM STL_LOAD_ERRORS;