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

Question 1

A company is using Amazon RDS for SQL Server with Multi-AZ. The database has a 500 GB data file and 100 GB log file. The application experiences high latency during peak hours. Monitoring shows high WriteIOPS on the primary. Which change will reduce latency without losing the ability to failover?

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

Accepted Answer

Increase the provisioned IOPS on the RDS instance. The high WriteIOPS on the primary indicates that the storage layer is saturated, causing latency. Increasing provisioned IOPS on the RDS instance directly addresses the bottleneck by providing more I/O capacity, and since Multi-AZ is already enabled, the standby remains synchronized and failover capability is preserved.

Answer

Reduce the log file size by changing recovery model

Answer

Create a Read Replica in a different Availability Zone

Answer

Switch to Multi-AZ with two readable standbys

Question 2

Which THREE are valid Amazon Redshift distribution styles? (Choose 3.)

Accepted Answer

ALL. Option B (ALL) is a valid Redshift distribution style in which a full copy of the table is distributed to every compute node, useful for small dimension tables that are frequently joined. Option C (AUTO) is valid because Redshift can automatically assign ALL, EVEN, or KEY distribution based on table size and query patterns, and it is the default distribution style. Option E (KEY) is valid because it distributes rows according to the values in a specified column (the distribution key), co-locating matching rows on the same node slice to optimize joins. Option A (HASH) is not a Redshift distribution style name—the hashing mechanism underlies KEY distribution, but the style itself is called KEY, not HASH. Option D (RANDOM) is not valid either; the even-distribution style in Redshift is called EVEN, not RANDOM, and it spreads rows round-robin without a distribution key.

Answer

HASH

Answer

RANDOM

Question 3

A company is running a production Amazon RDS for MySQL Multi-AZ DB instance. The database experiences a sudden spike in read requests, causing performance degradation. The company needs to improve read scalability with minimal application changes. Which solution should the data engineer recommend?

Accepted Answer

Create an Amazon RDS Read Replica and update the application to use it for read queries.. Amazon RDS Read Replicas allow you to offload read traffic from the primary DB instance by creating asynchronous replicas that can serve read queries. This directly addresses the spike in read requests with minimal application changes—only the connection string for read queries needs to be updated. Multi-AZ is for high availability, not read scaling, and increasing instance size is a vertical scaling approach that doesn't leverage the horizontal read scalability of replicas.

Answer

Implement DynamoDB Accelerator (DAX) in front of the database.

Answer

Enable Multi-AZ on the existing DB instance.

Answer

Increase the DB instance size to a larger instance class.

Question 4

A data engineer needs to store semi-structured JSON data that is accessed infrequently but must be retrievable within 5 minutes. The data is immutable once stored. Which storage solution is MOST cost-effective?

Accepted Answer

Amazon S3 Glacier Instant Retrieval. Amazon S3 Glacier Instant Retrieval is the most cost-effective choice for long-term archival of infrequently accessed, immutable data with a 5-minute retrieval requirement. It offers a storage cost of $0.004/GB/month, significantly lower than One Zone-IA at $0.01/GB/month, and retrieval times of milliseconds to minutes, easily meeting the 5-minute requirement. One Zone-IA is cheaper than S3 Standard but more expensive than Glacier Instant Retrieval, and its single-AZ design introduces availability risk. Glacier Deep Archive has a higher retrieval cost and typically takes hours, exceeding the 5-minute requirement.

Answer

Amazon S3 Glacier Deep Archive

Answer

Amazon S3 Standard

Answer

Amazon S3 One Zone-IA

Question 5

A company uses Amazon Redshift for its data warehouse. The data engineer notices that the most frequently accessed table is sorted by date, but queries often filter by customer_id. The table has 500 million rows and uses AUTO distribution style. What change would MOST improve query performance?

Accepted Answer

Change the sort key to include customer_id as a compound sort key.. Since queries frequently filter by customer_id but the table is sorted only by date, Redshift must scan all rows that match the date range and then filter by customer_id. By adding customer_id as a compound sort key (date, customer_id), Redshift can use zone maps to skip blocks that don't contain the requested customer_id within the date range, dramatically reducing the number of rows scanned and improving query performance.

Answer

Change distribution style to KEY on customer_id.

Answer

Change distribution style to EVEN.

Answer

Change the sort key to an interleaved sort key on date and customer_id.

Question 6

A company uses Amazon DynamoDB to store session data for a web application. The application experiences occasional spikes in traffic, causing throttling on the table. The data engineer needs to implement a solution that handles traffic spikes without manual intervention and minimizes cost. What should the data engineer do?

Accepted Answer

Enable DynamoDB Auto Scaling for the table.. DynamoDB Auto Scaling (option D) automatically adjusts the provisioned read and write capacity based on actual traffic patterns, using CloudWatch alarms and the Application Auto Scaling service. This handles traffic spikes without manual intervention and minimizes cost by scaling down during low traffic periods, making it the ideal solution for variable workloads like session data.

Answer

Switch to provisioned capacity with a high fixed read/write capacity.

Answer

Implement DynamoDB Accelerator (DAX) to cache read requests.

Answer

Purchase DynamoDB reserved capacity.

Question 7

A data engineer is designing a data pipeline that ingests streaming data from IoT devices into Amazon S3 using Amazon Kinesis Data Firehose. The data must be transformed from JSON to Parquet format before storage. Which TWO actions should the data engineer take to achieve this?

Accepted Answer

Create a Glue Data Catalog table defining the schema and configure Firehose to use the table for Parquet conversion.. Amazon Kinesis Data Firehose can directly convert incoming JSON data to Parquet format by referencing a table schema defined in the AWS Glue Data Catalog. This allows Firehose to perform the schema-aware conversion without custom code, leveraging the Glue table's column definitions and SerDe for Parquet serialization.

Answer

Enable Firehose's built-in Parquet conversion without any additional configuration.

Answer

Use Amazon Kinesis Data Analytics to convert the data format.

Answer

Configure Firehose to convert the data to Apache Avro format.

Question 8

A data engineer is designing a data lake on Amazon S3. The data is ingested from multiple sources in Parquet format, partitioned by date. The engineer needs to ensure that queries using Amazon Athena are cost-effective and perform well. Which approach should the engineer take?

Accepted Answer

Use Parquet format with Snappy compression and partition by year, month, day.. Parquet is a columnar storage format that reduces the amount of data scanned by Athena, and Snappy compression provides a good balance between compression ratio and decompression speed. Partitioning by year, month, and day allows Athena to use partition pruning to skip irrelevant data, minimizing scanned bytes and reducing query cost.

Answer

Store data in uncompressed CSV format and partition by year, month, day, hour.

Answer

Use JSON format with Snappy compression and partition by date only.

Answer

Use Gzip-compressed CSV files with no partitioning.

Question 9

A data engineer is migrating an on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The database is 2 TB in size and has a tight migration window. Which migration approach minimizes downtime?

Accepted Answer

Use AWS DMS with ongoing replication to capture changes during migration.. AWS DMS with ongoing replication (change data capture, CDC) allows you to perform a full load of the existing data and then continuously replicate incremental changes from the source PostgreSQL database to the target RDS for PostgreSQL. This minimizes downtime because you can keep the source database fully operational during the migration and only switch applications over after the target is fully synchronized, resulting in a very short cutover window.

Answer

Use AWS Database Migration Service (DMS) with full load only.

Answer

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

Answer

Create a read replica in RDS and promote it when ready.

Question 10

A company stores sensitive customer data in an Amazon S3 bucket. The security team requires that all data be encrypted at rest using a key that is automatically rotated every year. Which encryption solution should the data engineer use?

Accepted Answer

SSE-KMS with a customer managed key. SSE-KMS with a customer managed key is correct because it allows you to use an AWS KMS key that you control, enabling automatic annual key rotation via the KMS key rotation feature. This satisfies the security team's requirement for encryption at rest with a key that is automatically rotated every year, while maintaining control over the key lifecycle.

Answer

SSE-S3

Answer

SSE-C

Answer

Client-side encryption

Question 11

A company runs a production Amazon Redshift cluster with a 5-node ra3.4xlarge configuration. The data engineer observes that write operations are failing with 'Disk Full' errors on some nodes. The cluster has not reached its total capacity. What should the engineer do to resolve this issue?

Accepted Answer

Recreate the table with a different distribution style to avoid data skew.. The 'Disk Full' errors on some nodes, despite the cluster not reaching total capacity, indicate data skew caused by an inappropriate distribution style. Recreating the table with a different distribution style (e.g., DISTKEY on a high-cardinality column or DISTSTYLE EVEN) redistributes data evenly across all nodes, eliminating the hot spots that exhaust local disk space.

Answer

Change the sort keys to distribute data evenly.

Answer

Enable compression on all tables.

Answer

Add more nodes to the cluster.

Question 12

Which THREE factors should a data engineer consider when choosing between Amazon RDS and Amazon DynamoDB for a new application? (Choose THREE.)

Accepted Answer

Requirement for ACID transactions across multiple tables.. Amazon RDS (with engines like PostgreSQL or MySQL) supports ACID transactions across multiple tables, ensuring atomicity, consistency, isolation, and durability for complex operations. DynamoDB, while supporting transactions, is limited to a single AWS account and region, and its transactional API has constraints on item sizes and throughput, making RDS the better choice for multi-table ACID compliance.

Answer

Ability to encrypt data at rest.

Answer

Support for multi-region disaster recovery.

Question 13

A company runs a streaming application on Amazon EC2 instances that writes data to an Amazon DynamoDB table (us-east-1). The data is later consumed by a reporting job that runs every hour. Recently, the reporting job has been failing with ProvisionedThroughputExceededException errors during peak hours. The DynamoDB table uses provisioned capacity with 1000 read capacity units (RCU) and 500 write capacity units (WCU). The reporting job performs scans and reads using eventually consistent reads. The application's write traffic is steady, but the reporting job's reads spike at the top of the hour. The data engineer needs to resolve the throughput exceptions without affecting the application's writes. Which solution should the data engineer implement?

Accepted Answer

Create a global secondary index (GSI) with enough RCU for the reporting job and have the job query the index instead of scanning the table.. The reporting job's scans consume large amounts of read capacity and spike at the top of the hour, causing ProvisionedThroughputExceededException on the shared table. Creating a GSI with dedicated RCU isolates the reporting workload from the application's writes and lets the job query the index instead of scanning the base table, which is far more efficient. This preserves the application's write throughput on the base table.

Answer

Increase the table's RCU to 2000.

Answer

Create a read replica of the DynamoDB table in a different region.

Answer

Switch the table to on-demand capacity mode.

Question 14

A data engineer is managing an Amazon Redshift cluster used for analytics. The cluster has a single node of type dc2.large. The engineer notices that queries are slowing down as data volume grows. The cluster's disk space is at 70% usage. The engineer needs to improve query performance and accommodate future growth. The budget allows for moderate cost increase. Which action should the engineer take?

Accepted Answer

Add another dc2.large node to the cluster.. Adding another dc2.large node to the cluster is the best action because it increases both storage and compute capacity, allowing for better query performance and future growth. The cluster is at 70% disk usage, so adding a node provides additional storage and distributes the data, improving parallelism. The budget allows for moderate cost increase, and adding a node is a straightforward scale-out approach.

Answer

Resize the cluster to a single ds2.xlarge node.

Answer

Migrate the cluster to a single ra3.xlplus node with managed storage.

Answer

Enable concurrency scaling and maintain the current cluster.

Question 15

A company uses Amazon S3 to store images that are accessed by a web application. The application generates presigned URLs for users to download images. Recently, the application has been experiencing errors when generating presigned URLs for objects that were uploaded using multipart upload. The errors indicate that the presigned URL does not work. The data engineer needs to ensure that presigned URLs work for all objects, including those uploaded via multipart upload. What should the data engineer do?

Accepted Answer

Ensure that the IAM user or role used to generate the presigned URL has s3:GetObject permission for the object.. Presigned URLs are generated using the credentials of the IAM principal that signs them, and the URL only works if that principal has the required permission (s3:GetObject) on the target object. Multipart uploads are unrelated to presigning — the failure occurs because the signing identity lacks s3:GetObject on the object, so S3 rejects the request with AccessDenied. Granting s3:GetObject to the IAM user or role that generates the presigned URL resolves the issue for all objects regardless of upload method.

Answer

Use a different signing algorithm when generating the presigned URL.

Answer

Enable S3 Versioning on the bucket.

Answer

Re-upload the objects using single-part upload instead of multipart upload.