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

Question 1

A company uses Amazon Redshift for analytics. The data engineer notices that some queries are slow and the EXPLAIN plan shows a 'Seq Scan' on a large table. Which data store management action would most likely improve query performance?

Accepted Answer

Define appropriate sort keys and distribution styles.. Option D is correct because using sort keys and distribution styles can significantly improve query performance by reducing scans and data shuffling. Option A is incorrect because VACUUM reclaims space but does not directly improve scan performance. Option B is incorrect because ANALYZE updates statistics but does not change the physical layout. Option C is incorrect because compression is for storage, not query speed.

Answer

Run the ANALYZE command to update table statistics.

Answer

Enable automatic compression on the table.

Answer

Run the VACUUM command to reclaim space.

Question 2

A company stores IoT sensor data in an Amazon S3 bucket. The data is ingested every minute and each object is about 10 KB. The data must be stored for at least 7 years for compliance. Which lifecycle policy configuration minimizes storage costs?

Accepted Answer

Transition to S3 Glacier Deep Archive after 30 days and expire after 2555 days.. Option D is correct because transitioning to S3 Glacier Deep Archive after 30 days and then to S3 Glacier Deep Archive is redundant; actually the correct strategy is to transition to S3 Glacier Deep Archive after 30 days and then expire after 7 years. But option D says transition to S3 Glacier Deep Archive after 30 days and expire after 2555 days (7 years). That is correct and cost-effective. Option A is incorrect because S3 Standard for 1 year then Glacier is more expensive. Option B is incorrect because S3 Standard-IA for 90 days then Glacier Flexible Retrieval is not optimal. Option C is incorrect because S3 One Zone-IA is not durable enough for compliance.

Answer

Transition to S3 One Zone-IA after 30 days, then to S3 Glacier Deep Archive after 365 days, and expire after 2555 days.

Answer

Transition to S3 Glacier Flexible Retrieval after 90 days and expire after 2555 days.

Answer

Transition to S3 Standard-IA after 30 days, then to S3 Glacier Deep Archive after 365 days, and expire after 2555 days.

Question 3

A data engineer is designing a data lake on Amazon S3. The data includes personally identifiable information (PII) that must be encrypted at rest. Which encryption option provides the most control over encryption keys?

Accepted Answer

Server-side encryption with AWS KMS managed keys (SSE-KMS).. Option C is correct because SSE-KMS allows the customer to manage and control KMS keys. Option A is incorrect because SSE-S3 uses Amazon-managed keys. Option B is incorrect because SSE-C uses customer-provided keys but requires managing keys on client side. Option D is incorrect because client-side encryption is not server-side.

Answer

Client-side encryption using Amazon S3 Encryption Client.

Answer

Server-side encryption with S3 managed keys (SSE-S3).

Answer

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

Question 4

A company uses Amazon RDS for MySQL with Multi-AZ deployment. The primary instance fails, and automatic failover occurs. After failover, the application experiences higher latency. What is the most likely cause?

Accepted Answer

The new primary instance is in a different Availability Zone, increasing network latency.. Option A is correct because after failover, the standby becomes the new primary, and a new standby is created in a different AZ, which may have higher latency. Option B is incorrect because the DNS record is updated. Option C is incorrect because read replicas are not affected. Option D is incorrect because failover is automatic.

Answer

The read replica is now the primary and cannot handle write traffic.

Answer

The failover process disabled automatic backups.

Answer

The DNS endpoint did not update to point to the new primary.

Question 5

A data engineer is migrating an on-premises Apache HBase workload to Amazon DynamoDB. The HBase table has a row key with composite structure: customer_id (10 chars) + timestamp (10 digits). The access pattern is to query by customer_id and retrieve the latest entries. How should the DynamoDB table be designed to optimize performance?

Accepted Answer

Create a table with partition key = customer_id and sort key = timestamp.. Option A is correct because using customer_id as partition key and timestamp as sort key allows efficient queries for latest entries per customer. Option B is incorrect because using timestamp as partition key leads to hot partitions. Option C is incorrect because a single partition key cannot support range queries efficiently. Option D is incorrect because S3 is not suitable for low-latency queries.

Answer

Use Amazon S3 with customer_id as prefix and timestamp as object name.

Answer

Create a table with partition key = concatenated customer_id and timestamp.

Answer

Create a table with partition key = timestamp and sort key = customer_id.

Question 6

A company is designing a disaster recovery strategy for an Amazon RDS for SQL Server database. The database must be recoverable in another AWS region within 15 minutes of a regional outage. Which TWO actions should the data engineer take?

Accepted Answer

Configure automated cross-region snapshots to be copied to the recovery region.. Options A and D are correct. A cross-region read replica can be promoted to a standalone instance in another region, providing recovery within minutes. Automated cross-region snapshots can also be used to restore to another region. Option B is incorrect because Multi-AZ is within a region, not cross-region. Option C is incorrect because cross-region automated backups are not supported; you need snapshots.

Answer

Enable Multi-AZ deployment on the primary instance.

Answer

Configure automated backups to copy to the recovery region.

Question 7

A data engineer is using Amazon Athena to query data stored in an S3 bucket. The queries are running slowly. Which THREE actions can improve query performance?

Accepted Answer

Partition the data on commonly filtered columns.. Options A, B, and C are correct. Partitioning reduces data scanned. Columnar formats like Parquet improve compression and query speed. Compression reduces data size. Option D is incorrect because S3 Standard-IA may have higher retrieval costs, not performance. Option E is incorrect because converting to JSON (text) would increase data size and slow queries.

Answer

Convert the data to JSON format for better schema evolution.

Answer

Move the data to S3 Standard-IA storage class.

Question 8

A company is building a data pipeline that ingests streaming data from IoT devices. The data must be stored in a durable, scalable, and cost-effective manner for batch processing. Which TWO AWS services should be used together?

Accepted Answer

Amazon Kinesis Data Streams. Options A and C are correct. Kinesis Data Streams ingests streaming data, and S3 stores the data durably and cost-effectively. Option B (Redshift) is for analytics, not raw storage. Option D (DynamoDB) is for low-latency queries, not cost-effective bulk storage. Option E (ElastiCache) is a cache, not durable storage.

Answer

Amazon ElastiCache

Answer

Amazon Redshift

Answer

Amazon DynamoDB

Question 9

A data engineer applies the above IAM policy to an IAM user. The user attempts to download an object from the bucket 'example-bucket' that is encrypted with SSE-S3 (AES256). Will the request succeed?

Accepted Answer

Yes, because the object is encrypted with SSE-S3 which uses AES256.. Option A is correct because the condition requires the object to be encrypted with AES256, and SSE-S3 uses AES256. Option B is incorrect because the condition checks the encryption header, not the key type. Option C is incorrect because the condition is satisfied. Option D is incorrect because the condition is satisfied.

Answer

Yes, but only if the user also has s3:ListBucket permission.

Answer

No, because the policy requires the encryption to be specified in the request.

Answer

No, because the policy does not allow the s3:GetObject action for encrypted objects.

Question 10

An application uses the 'orders' DynamoDB table with the schema and provisioned throughput shown in the exhibit. The application frequently queries by customer_id (range key) without specifying the order_id (partition key). What is the most likely impact on performance?

Accepted Answer

Queries will require a full table scan, consuming significant read capacity.. Option B is correct because queries without a partition key result in a full table scan, which is inefficient and consumes read capacity. Option A is incorrect because the query will not use the sort key efficiently. Option C is incorrect because the issue is not hot partitions but full scans. Option D is incorrect because it will not cause throttling immediately but will consume capacity.

Answer

Queries will be throttled because the table does not have a global secondary index.

Answer

Queries will be fast because the sort key is indexed.

Answer

Queries will cause hot partitions on the table.

Question 11

A data engineer runs the above SQL commands on an Amazon Redshift cluster. The table 'users' is created with DISTSTYLE EVEN. What is the effect of the DISTSTYLE EVEN on query performance?

Accepted Answer

It ensures data is evenly distributed across all nodes to prevent data skew.. Option B is correct because DISTSTYLE EVEN distributes rows evenly across nodes, which is beneficial when there is no clear join or aggregation key. Option A is incorrect because it does not optimize for joins on user_id. Option C is incorrect because EVEN distribution does not minimize data movement for joins. Option D is incorrect because EVEN distribution distributes data, not co-locates.

Answer

It stores all data on a single node for fast local queries.

Answer

It reduces data movement during queries by co-locating data based on user_id.

Answer

It improves join performance when joining on user_id.

Question 12

A company is using an Amazon RDS for MySQL database for an e-commerce application. During a sales event, the database experiences high read traffic, causing slow query performance. The company wants to reduce the read load on the primary database without changing the application code. Which solution meets these requirements?

Accepted Answer

Create an Amazon RDS read replica and direct read traffic to it.. Option B is correct because an RDS read replica offloads read queries from the primary DB without application changes. Option A is wrong because ElastiCache requires code changes to cache queries. Option C is wrong because Multi-AZ is for high availability, not read scaling. Option D is wrong because increasing instance size helps but may require downtime and is less efficient.

Answer

Enable Multi-AZ on the RDS instance.

Answer

Increase the instance size of the RDS database.

Answer

Deploy Amazon ElastiCache to cache query results.

Question 13

A data engineer needs to store semi-structured JSON data that is accessed infrequently but must be retrievable within minutes when needed. The data will be stored for 7 years for compliance. Which storage solution is MOST cost-effective?

Accepted Answer

Amazon S3 Glacier Deep Archive. Option D is correct because S3 Glacier Deep Archive is the lowest-cost storage for long-term archival with retrieval times within minutes (standard retrieval). Option A is wrong because S3 Standard is more expensive for infrequent access. Option B is wrong because S3 Intelligent-Tiering has monitoring costs and may not be optimal for long-term archival. Option C is wrong because S3 One Zone-IA is not durable for long-term compliance.

Answer

Amazon S3 One Zone-Infrequent Access

Answer

Amazon S3 Standard

Answer

Amazon S3 Intelligent-Tiering

Question 14

A company uses Amazon DynamoDB as the primary data store for a gaming application. The application experiences sudden spikes in traffic. The data engineer notices that write requests are throttled during peak times. The partition keys are well-distributed. What should the data engineer do to reduce throttling?

Accepted Answer

Configure DynamoDB auto scaling to adjust write capacity automatically.. Option C is correct because DynamoDB auto scaling adjusts capacity based on load, preventing throttling during spikes. Option A is wrong because DAX is a cache for reads, not writes. Option B is wrong because global tables improve latency and disaster recovery, not write capacity. Option D is wrong because increasing partition count is automatic if throughput increases, but without auto scaling, throttling still happens.

Answer

Use DynamoDB global tables to distribute writes across regions.

Answer

Increase the number of partition keys to improve write distribution.

Answer

Enable DynamoDB Accelerator (DAX) to cache write operations.

Question 15

A company is migrating an on-premises MongoDB database to Amazon DocumentDB. The data engineer needs to ensure minimal downtime during migration. Which AWS service should be used to facilitate the migration?

Accepted Answer

AWS Database Migration Service (DMS). Option B is correct because AWS DMS supports live migration with minimal downtime. Option A is wrong because S3 is for object storage, not database migration. Option C is wrong because Snowball is for large data transfers, not continuous replication. Option D is wrong because AWS Glue is for ETL, not live database migration.

Answer

AWS Glue

Answer

AWS Snowball

Answer

Amazon S3 Transfer Acceleration