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

Question 1

A data engineer is managing an Amazon Redshift cluster and needs to improve query performance for a workload that involves frequent complex joins and aggregations on large tables. The engineer wants to optimize the distribution and sort keys. Which TWO actions should the engineer take? (Choose two.)

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

Accepted Answer

Choose a distribution key that evenly distributes data across all nodes to minimize data movement during joins.. Choosing an appropriate distribution key reduces data movement during joins, and using a sort key on frequently filtered columns enables efficient range scans. These two actions directly address the performance of complex joins and aggregations on large tables. Other options like ALL distribution or interleaved sort keys are not optimal for large tables or all workloads.

Answer

Use a compound sort key with the most frequently filtered column as the first key.

Answer

Set the distribution style to ALL for all large tables to ensure every node has a full copy.

Answer

Apply interleaved sort keys on all columns to maximize query flexibility.

Question 2

A data engineer needs to store large volumes of semi-structured JSON data in Amazon S3. The data will be queried occasionally using Amazon Athena. The engineer wants to minimize storage costs while ensuring the data is immediately accessible when needed. Which S3 storage class should the engineer choose?

Accepted Answer

S3 Standard-Infrequent Access (S3 Standard-IA). S3 Standard-IA is ideal for data that is accessed infrequently but requires quick access when needed. It provides lower storage costs than S3 Standard while maintaining low latency. Other storage classes either cost more for frequent access or have retrieval delays that do not meet the immediate accessibility requirement. Therefore, S3 Standard-IA is the correct choice.

Answer

S3 Intelligent-Tiering

Answer

S3 Glacier Deep Archive

Answer

S3 Standard

Question 3

A data engineer manages an Amazon DynamoDB table that stores user clickstream events. The table uses a partition key of user_id and a sort key of event_timestamp. The workload is write-heavy during peak hours and read-heavy for recent events. The engineer notices that some partitions are throttled while others have low utilization. The engineer wants to distribute the write load more evenly across partitions without changing the application's access pattern. Which action should the engineer take?

Accepted Answer

Add a random suffix to the partition key and use a composite key.. Write sharding by adding a random suffix to the partition key distributes writes across multiple partitions, mitigating hot partitions. The application can still query by user_id by issuing parallel queries for each suffix and merging results. This approach directly addresses the uneven write load without changing the logical access pattern. Increasing capacity or switching modes does not solve per-partition throttling.

Answer

Enable DynamoDB Accelerator (DAX) for the table.

Answer

Switch the table to on-demand capacity mode.

Answer

Increase the provisioned write capacity units (WCUs) for the table.

Question 4

A data engineer is designing an Amazon DynamoDB table for an application that requires strongly consistent reads and must handle sudden spikes in traffic. The table currently uses on-demand capacity mode. The engineer observes that some read requests are throttled during spikes despite the on-demand mode. What is the most likely cause of the throttling?

Accepted Answer

The table's partition key is causing hot partitions, exceeding the per-partition throughput limit.. In on-demand mode, DynamoDB automatically scales to handle traffic, but each partition has a maximum throughput of 3,000 RCUs. If the partition key design causes hot partitions, those partitions can throttle even when the table has ample capacity. This is the most likely cause of throttling during spikes.

Answer

The table is configured with a global secondary index (GSI) that has insufficient write capacity.

Answer

The application is using eventually consistent reads instead of strongly consistent reads.

Answer

The table's read capacity is set too low in provisioned mode.

Question 5

A company is building a data lake on Amazon S3 and needs to ensure that data is encrypted at rest and in transit. They also need to enforce encryption for all future PUT requests. Which two actions should the data engineer take to meet these requirements? (Choose two.)

Accepted Answer

Create a bucket policy that denies PUT requests without the x-amz-server-side-encryption header.. To enforce encryption at rest and in transit for all future PUT requests, the engineer should enable default encryption on the bucket and use a bucket policy to deny unencrypted PUT requests. These two actions together ensure that data is encrypted both at rest and during upload.

Answer

Use AWS Certificate Manager (ACM) to provision an SSL/TLS certificate for the S3 bucket.

Answer

Enable versioning on the S3 bucket to protect against accidental deletion.

Answer

Configure S3 Block Public Access to prevent public access to the bucket.

Question 6

A data engineer is using AWS Glue to catalog data stored in Amazon S3. The data is in Parquet format and partitioned by year, month, and day. The engineer runs an AWS Glue crawler, but the resulting table in the AWS Glue Data Catalog shows all partitions as separate columns instead of partition keys. Queries in Amazon Athena return incorrect results because the partition columns are not recognized. What is the most likely cause of this issue?

Accepted Answer

The Parquet files contain the partition columns as regular columns in the schema.. The AWS Glue crawler infers table schema from both the S3 path structure and the data file contents. When Parquet files already contain columns named year, month, and day, the crawler treats them as data columns. It may not also create partition keys from the path, especially if the path is not in Hive-style. Removing those columns from the data or using a crawler configuration to ignore them resolves the issue.

Answer

The S3 bucket does not have the correct bucket policy for AWS Glue.

Answer

The S3 path does not follow the Hive-style partitioning convention (e.g., year=2023/month=01/day=01).

Answer

The AWS Glue crawler is configured with a custom classifier that overrides partition detection.

Question 7

A company is using Amazon Redshift for its data warehouse. The data engineering team wants to ensure that the cluster can recover from a failure with minimal data loss. The recovery point objective (RPO) is 15 minutes, and the recovery time objective (RTO) is 1 hour. The team needs a solution that automatically backs up the cluster and allows restoration to a specific point in time. Which feature should the team use?

Accepted Answer

Amazon Redshift automated snapshots. Amazon Redshift automated snapshots provide scheduled backups and enable point-in-time recovery to any second within the retention period, defaulting to 1 day and configurable up to 35 days. They are taken approximately every 8 hours or after 5 GB of data change, which supports a 15-minute RPO when combined with the continuous backup of changes. Restoration creates a new cluster, meeting the RTO.

Answer

Amazon Redshift Spectrum

Answer

Amazon Redshift manual snapshots

Answer

Amazon Redshift cross-region snapshot copy

Question 8

A data engineer is using AWS Database Migration Service (AWS DMS) to migrate an on-premises Oracle database to Amazon Aurora PostgreSQL. The engineer needs to perform a heterogeneous migration with minimal downtime. Which combination of DMS features should be used?

Accepted Answer

Use full load plus change data capture (CDC).. For a heterogeneous migration with minimal downtime, you need to convert the schema using AWS SCT and then use AWS DMS with full load and change data capture. Full load copies existing data, while CDC replicates ongoing changes, allowing a near-zero-downtime cutover.

Answer

Use CDC only without full load.

Answer

Use full load only and then switch over.

Answer

Use AWS Schema Conversion Tool (AWS SCT) to convert the schema and then use full load only.

Question 9

A data engineer is managing an Amazon S3 data lake that contains sensitive customer data. The engineer needs to implement a cost-effective storage solution that automatically transitions objects between storage classes based on access patterns. The data is accessed frequently for the first 30 days, infrequently for the next 60 days, and rarely after 90 days. The engineer also wants to minimize retrieval costs for the infrequent access period. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Create an S3 Lifecycle policy to transition objects to S3 Standard-IA after 30 days.. The engineer should use lifecycle policies to transition objects to S3 Standard-IA after 30 days for infrequent access, and to S3 Glacier Deep Archive after 90 days for long-term archival. These transitions match the access pattern and minimize retrieval costs. S3 Intelligent-Tiering adds monitoring charges and is less cost-effective for predictable patterns. One Zone-IA reduces durability, and Cross-Region Replication increases costs.

Answer

Use S3 Cross-Region Replication to copy objects to another region for cost savings.

Answer

Enable S3 Intelligent-Tiering on the bucket to automatically move objects between access tiers.

Answer

Create an S3 Lifecycle policy to transition objects to S3 One Zone-IA after 30 days.

Question 10

A data engineer is configuring an Amazon Redshift cluster and needs to improve query performance for complex analytical queries that involve large joins and aggregations. The engineer has already enabled concurrency scaling. Which TWO additional actions should be taken to optimize performance? (Choose two.)

Accepted Answer

Define sort keys on columns frequently used in WHERE clauses.. For complex analytical queries with large joins, using distribution keys to collocate joined tables reduces data movement, and defining sort keys on frequently filtered columns enables zone map filtering. These two actions directly address the performance bottlenecks in join and aggregation operations.

Answer

Increase the number of slices per node by adding more nodes.

Answer

Use column encoding to improve compression.

Answer

Enable result caching for all queries.

Question 11

A media company stores years of video metadata in an Amazon S3 bucket and queries it with Amazon Athena through the AWS Glue Data Catalog. Analysts report that queries against a frequently filtered release_date column are slow and expensive, and the team wants to reduce bytes scanned without rewriting existing objects. Which two actions should the data engineer take? (Choose two.)

Accepted Answer

Generate column statistics for the Glue table so the optimizer can skip row groups.. Reducing Athena scan cost without rewriting objects comes from metadata that lets the engine skip data. Partitioning the Glue table on release_date fields enables prefix-level pruning, and column statistics enable row-group and file skipping within partitions. Together they shrink the bytes read for filtered queries. Requester Pays, additional workgroups, and SerDe changes affect billing or parsing but not the volume of data scanned.

Answer

Enable S3 Requester Pays on the bucket to shift scan costs to the analysts.

Answer

Increase the number of Athena workgroups and assign analysts to different groups.

Answer

Switch the table to an Apache Hive SerDe and keep the existing file layout.

Question 12

A data engineer is designing an Amazon Redshift workload that ingests streaming events into a staging table and then merges them into a large target table. The target table has a sort key on event_time and a distribution style of KEY on customer_id. The engineer notices that the merge step is slow and that some nodes process far more rows than others. Which TWO changes should the engineer make to improve merge performance? (Choose two.)

Accepted Answer

Run the merge as a single transaction that deletes matching rows and inserts new rows, then run VACUUM and ANALYZE on the target table afterward.. Redshift merges are sensitive to sort order, statistics, and data distribution. Deleting and inserting in one transaction keeps the operation atomic, while VACUUM restores physical sort order and ANALYZE refreshes planner statistics. Pre-sorting staging data by the sort key and distribution key reduces vacuum work and cross-slice movement. Together these changes cut the rows each node processes and reduce skew.

Answer

Disable automatic compression on the target table and store all columns as VARCHAR to simplify the merge.

Answer

Increase the number of slices by resizing the cluster to a larger node type before each merge operation.

Answer

Change the target table distribution style to ALL so every node holds a full copy of the table.

Question 13

A company has an Amazon RDS for MySQL DB instance with read replicas. The primary DB instance fails. What is the correct procedure to promote a read replica to become the new primary?

Accepted Answer

Manually promote the read replica to a standalone DB instance.. When an Amazon RDS for MySQL primary DB instance fails, read replicas do not automatically become the new primary. The correct procedure is to manually promote the read replica using the AWS Management Console, CLI, or API, which converts it into a standalone DB instance. After promotion, you must update your application endpoints to point to the new primary, as RDS does not handle this automatically.

Answer

Modify the read replica to be a Multi-AZ deployment and failover will occur.

Answer

RDS automatically fails over to the read replica within 5 minutes.

Answer

Delete the primary and the read replica will automatically become the primary.

Question 14

A company uses Amazon DynamoDB for a gaming application. They need to store player session data that expires after 24 hours. Which DynamoDB feature should they use to automatically delete expired items?

Accepted Answer

Time to Live (TTL). DynamoDB Time to Live (TTL) is the correct feature because it allows you to define a per-item timestamp attribute (e.g., `expireAt`) that DynamoDB automatically deletes once that timestamp is reached. This is ideal for expiring session data after 24 hours without requiring custom code or scheduled jobs to scan and delete items, reducing cost and operational overhead.

Answer

DynamoDB auto scaling

Answer

DynamoDB Streams

Answer

Point-in-time recovery

Question 15

An e-commerce application uses Amazon ElastiCache for Redis to cache product catalog data. The cache currently uses lazy loading. The team wants to ensure that frequently accessed product data is always fresh. Which caching strategy should they implement?

Accepted Answer

Write-through caching. Write-through caching ensures that data is written to the cache simultaneously with the database, guaranteeing that frequently accessed product data is always fresh. This strategy eliminates stale reads by synchronously updating the cache on every write, which directly addresses the requirement for freshness without relying on expiration or lazy population.

Answer

Set a TTL of 5 minutes for all cached items

Answer

Use database read replicas to serve data

Answer

Lazy loading with TTL