DEA-C01 Mock Exam 91 — 25 Questions

Question 1

A financial services company is designing a data store for transaction records that must be immutable and auditable. The data must be stored for 7 years. Which AWS services can be combined to meet these requirements? (Choose TWO.)

Accepted Answer

Amazon S3 Glacier Deep Archive. Amazon S3 Glacier Deep Archive is correct because it provides the lowest-cost storage for long-term retention of immutable data, with a 7-year lifecycle meeting compliance requirements. Amazon S3 with Object Lock enabled is correct because it enforces a write-once-read-many (WORM) model, preventing records from being deleted or overwritten for a specified retention period, ensuring immutability and auditability.

Answer

Amazon EBS volume with snapshots

Answer

Amazon RDS with automated backups

Answer

Amazon DynamoDB with point-in-time recovery

Question 2

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 3

An IAM policy is attached to a user who tries to upload an object to the S3 bucket example-bucket using the AWS CLI without specifying the --server-side-encryption flag. What will happen?

Accepted Answer

The upload fails with an AccessDenied error.. The IAM policy denies s3:PutObject unless the request includes the `x-amz-server-side-encryption` header with a value of `AES256`. Since the user did not specify `--server-side-encryption` in the AWS CLI command, the request lacks this required header, causing S3 to evaluate the policy and return an AccessDenied error. The upload fails before any default encryption setting on the bucket is applied.

Answer

The upload succeeds and the object is encrypted with SSE-S3 by default.

Answer

The upload fails because the user does not have permission to use KMS.

Answer

The upload succeeds because the policy allows s3:PutObject.

Question 4

A data engineer created the IAM policy shown in the exhibit. The engineer then attempts to upload an object to 'my-bucket' using the AWS CLI with the command: aws s3 cp file.txt s3://my-bucket/ --sse aws:kms. The upload fails with an 'AccessDenied' error. What is the most likely cause?

Accepted Answer

The policy requires SSE-S3 (AES256), but the command uses SSE-KMS. The IAM policy in the exhibit requires the `s3:x-amz-server-side-encryption` header to be set to `AES256`, which corresponds to SSE-S3. The AWS CLI command uses `--sse aws:kms`, which sets the header to `aws:kms` for SSE-KMS. This mismatch causes the request to fail the `s3:PutObject` condition check in the policy, resulting in an 'AccessDenied' error.

Answer

The policy resource is incorrect

Answer

The policy does not allow the s3:PutObject action

Answer

The command is missing the --sse-customer-algorithm parameter

Question 5

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;

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

Which THREE of the following are benefits of using Amazon DynamoDB Accelerator (DAX)? (Choose three.)

Accepted Answer

Offloads read traffic from the DynamoDB table.. Option A is correct because DAX acts as a read-through cache that offloads read traffic from the DynamoDB table, reducing the number of read requests that hit the underlying table and thus lowering the consumed read capacity units (RCUs). This allows the table to handle more concurrent reads without scaling up provisioned capacity.

Answer

Improves write throughput by batching writes.

Answer

Supports write-through caching to improve write performance.

Question 8

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

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 12

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 13

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 14

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?

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 15

A company is using Amazon Redshift for data warehousing. The data engineer notices that the STL_ALERT_EVENT_LOG table shows many 'missing statistics' alerts. What is the best course of action to address this issue?

Accepted Answer

Run ANALYZE on the tables.. The STL_ALERT_EVENT_LOG table records alerts about query performance issues, including 'missing statistics' alerts. This indicates that the query optimizer lacks up-to-date table statistics, leading to suboptimal query plans. Running the ANALYZE command updates table statistics, enabling the optimizer to generate efficient execution plans. Therefore, option D is the correct course of action.

Answer

Increase the WLM concurrency slots.

Answer

Run VACUUM on the tables.

Answer

Enable compression on the tables.

Question 16

Which TWO actions can help improve query performance in Amazon Redshift? (Choose two.)

Accepted Answer

Use appropriate sort keys for tables.. Option A is correct because defining appropriate sort keys in Amazon Redshift enables the query optimizer to use zone maps to skip irrelevant data blocks during table scans, significantly reducing the amount of data read from disk. Sort keys also improve the effectiveness of merge joins and the performance of range-restricted queries by physically co-locating rows with similar sort key values on disk.

Answer

Disable SSL encryption for connections.

Answer

Use VARCHAR instead of CHAR for fixed-length strings.

Answer

Increase the number of nodes in the cluster.

Question 17

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 18

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 19

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 20

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.