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

Question 1

A gaming company uses Amazon DynamoDB to store player profiles and game state. The table has a partition key of 'player_id' and no sort key. The table is provisioned with 5,000 RCUs and 5,000 WCUs. The application performs frequent reads and writes to update player scores. Recently, the company introduced a new feature that allows players to form guilds. The guild data is stored in a separate DynamoDB table with a partition key of 'guild_id'. The application often needs to retrieve all members of a guild. The data engineer is encountering high latency when querying the guild table because the guilds can have up to 100 members. The engineer wants to reduce latency without changing the application architecture. What should the data engineer do?

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

Accepted Answer

Create a global secondary index (GSI) on the guild table with partition key guild_id and sort key member_id.. Adding a global secondary index (GSI) on the guild table with guild_id as the partition key and member_id as the sort key allows efficient queries for all members of a guild. Option A is wrong because increasing capacity may not solve the access pattern issue. Option C is wrong because DynamoDB Streams are for change data capture, not for query optimization. Option D is wrong because DAX caches read results; if the query pattern is inefficient, DAX won't help much.

Answer

Increase the provisioned read and write capacity for the guild table to 10,000 RCUs and 10,000 WCUs.

Answer

Enable DynamoDB Streams on the guild table and process the stream to populate a separate read table.

Answer

Use DynamoDB Accelerator (DAX) to cache the results of the guild queries.

Question 2

A company uses Amazon EMR to run Spark jobs on a cluster of 20 nodes. The cluster stores intermediate data on Amazon S3 using EMRFS. The company's data engineering team notices that the Spark jobs are running slower than expected. Upon investigating, they find that the cluster is experiencing high network I/O and that the S3 storage costs have increased significantly. The team suspects that the Spark jobs are writing too much intermediate data to S3. The jobs are performing many shuffle operations. The team wants to optimize the job performance and reduce costs without modifying the Spark application code. What should the data engineer do?

Accepted Answer

Configure the EMR cluster to use instance store volumes for intermediate data instead of EMRFS.. EMRFS is the default file system for EMR, and Spark writes shuffle data to EMRFS (S3) when using it. This causes high network I/O and S3 costs. Configuring the cluster to use instance store volumes for intermediate data (by setting spark.local.dir to instance store paths) offloads shuffle data to local disks, reducing S3 usage and improving performance. This requires no application code changes.

Answer

Enable S3 server-side encryption on the S3 bucket to reduce storage costs.

Answer

Increase the size of the EBS root volumes on the cluster nodes to store more intermediate data locally.

Answer

Add more nodes to the cluster to distribute the shuffle load.

Question 3

A retail company stores customer transaction data in an Amazon S3 bucket. The data is encrypted using server-side encryption with AWS KMS (SSE-KMS). The company uses an IAM role to allow an Amazon Athena query service to read the data. The data engineer creates a new Athena workgroup and attempts to run a query on the S3 bucket. The query fails with an access denied error. The IAM role has permissions to decrypt the KMS key and read from the bucket. The engineer checks the S3 bucket policy and finds that it does not explicitly allow access. What is the most likely cause of the failure?

Accepted Answer

The S3 bucket policy does not grant the required permissions to the Athena service principal.. The most likely cause is that the S3 bucket policy does not explicitly grant the required permissions to the Athena service principal. Even though the IAM role has permissions to decrypt and read, Athena operates as a service and the bucket policy must allow the `athena.amazonaws.com` principal to perform `s3:GetObject` and `s3:ListBucket` actions. Option B is correct because without this explicit grant, the bucket policy implicitly denies access. Option A is incorrect because the bucket being in a different account would cause an error, but the scenario does not indicate cross-account setup. Option C is incorrect because the IAM role already has decrypt permissions. Option D is incorrect because Athena supports SSE-KMS encrypted data with proper KMS permissions.

Answer

The S3 bucket is in a different AWS account than the Athena workgroup.

Answer

The IAM role does not have permission to use the KMS key for encryption operations.

Answer

Athena does not support querying data encrypted with SSE-KMS.

Question 4

A company runs a transactional database on Amazon RDS for PostgreSQL with Multi-AZ deployment. The database size is 2 TB and experiences moderate write load. The company recently enabled RDS Performance Insights and noticed a high number of 'TupleLock' wait events during peak hours. The development team reports that a batch update job runs every hour, updating millions of rows in a large table. The job takes longer than expected. The DBA suspects that excessive row-level locking is causing contention. The team wants to minimize lock contention without changing the application code. Which solution should be implemented?

Accepted Answer

Tune the autovacuum settings (e.g., autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold) to run more frequently and aggressively.. Tuning autovacuum settings (autovacuum_vacuum_scale_factor and autovacuum_vacuum_threshold) reduces lock contention by cleaning up dead tuples more frequently. In PostgreSQL, row-level locks on heavily updated tables can cause 'TupleLock' wait events. Frequent autovacuum prevents accumulation of dead tuples, reducing the need for lock escalation and shortening update times. Option B (increasing instance size) may improve throughput but does not directly address lock contention. Option C (RDS Proxy) manages connections, not locks. Option D (pg_partman partitioning) reduces row contention but requires application code changes (stem prohibits code changes).

Answer

Increase the RDS instance size to a larger instance class with more vCPUs and memory.

Answer

Enable RDS Proxy to manage database connections and reduce connection overhead.

Answer

Implement table partitioning using the pg_partman extension to split the large table into smaller partitions.

Question 5

A data engineering team uses AWS Glue ETL jobs to process data from Amazon S3 and load it into an Amazon Redshift cluster. The cluster has a single node of type dc2.large. The team notices that the ETL jobs are failing intermittently with errors related to disk space. The Redshift cluster shows that the disk is nearly full. The team needs to resolve the disk space issue and ensure the ETL jobs can complete successfully without increasing costs significantly. Which solution should the team implement?

Accepted Answer

Convert the cluster to use RA3 node types (e.g., ra3.xlarge) with managed storage.. RA3 nodes decouple compute from storage using Redshift Managed Storage (RMS), so the cluster is not limited by local disk. This resolves the disk-full condition without adding nodes, and RA3 supports scaling storage independently, keeping costs controlled.

Answer

Load data into a staging table first, then perform a VACUUM and ANALYZE on the target tables.

Answer

Add more nodes to the Redshift cluster by resizing to a multi-node dc2.large configuration.

Answer

Set the table's distribution style to ALL for fact tables to avoid data redistribution during joins.

Question 6

A financial services company uses Amazon DynamoDB to store transaction records. The table has a partition key of AccountID and a sort key of TransactionDate. The workload is read-heavy, with frequent queries for transactions within a specific date range for a given account. The company needs to optimize read performance and reduce latency. Which action should a data engineer take?

Accepted Answer

Enable DynamoDB Accelerator (DAX) for the table to cache read results.. DAX is a fully managed, highly available in-memory cache for DynamoDB that delivers fast read performance for eventually consistent reads. By caching the results of read operations, DAX reduces the need to access the underlying table, lowering latency. This directly addresses the read-heavy workload and the need for lower latency without requiring changes to the table schema or application logic.

Answer

Increase the provisioned read capacity units (RCUs) for the table.

Answer

Use Amazon ElastiCache for Redis to cache query results at the application layer.

Answer

Create a global secondary index (GSI) with AccountID as the partition key and TransactionDate as the sort key.

Question 7

A company stores sensitive customer data in an Amazon S3 bucket. A data engineer needs to ensure that the data is encrypted at rest using a key that is managed by AWS and automatically rotated every year. The engineer wants to minimize management effort. Which encryption option should the engineer choose?

Accepted Answer

Server-side encryption with Amazon S3 managed keys (SSE-S3). SSE-S3 provides encryption at rest with keys fully managed by AWS, including automatic rotation. It requires no additional configuration or key management from the user, making it the best choice to minimize effort. The other options either require customer management of keys or add complexity, which does not meet the requirement.

Answer

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

Answer

Server-side encryption with AWS KMS keys (SSE-KMS)

Answer

Client-side encryption using the AWS Encryption SDK

Question 8

A data engineer is managing an Amazon S3 data lake that contains sensitive financial data. The company must ensure that data is encrypted at rest and that access is audited. The engineer needs to implement encryption using AWS Key Management Service (AWS KMS) and enable logging of all access requests. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Configure S3 server access logging to a separate bucket.. Enabling default encryption with SSE-KMS ensures all objects are encrypted at rest using AWS KMS, meeting the encryption requirement. Configuring S3 server access logging captures detailed records of all requests to the bucket, providing the necessary audit trail. Together, these actions satisfy both the encryption and auditing needs for the sensitive financial data.

Answer

Enable versioning on the S3 bucket.

Answer

Use S3 client-side encryption with a customer-provided key.

Answer

Enable AWS CloudTrail data events for the S3 bucket.

Question 9

A retail company runs an Amazon RDS for PostgreSQL database that supports an order-management application. The data engineer needs to create a read replica in a different AWS Region for disaster recovery and analytics. The primary database is encrypted with an AWS KMS customer managed key. What must the data engineer do to create the cross-Region read replica?

Accepted Answer

Create a KMS multi-Region key, configure the primary to use it, and specify the replica Region when creating the read replica.. Encrypted RDS instances can only be replicated across Regions when both the source and the replica use the same multi-Region KMS key. The engineer must first convert the primary to use a multi-Region key, then create the read replica in the target Region, which replicates the encrypted data and decrypts it with the replicated key material.

Answer

Disable encryption on the primary, create the read replica, then re-enable encryption.

Answer

Copy the KMS key policy to the destination Region and reuse the same key ID.

Answer

Use an AWS managed key instead of the customer managed key on the primary.

Question 10

A financial analytics team stores daily trade logs in an Amazon S3 bucket using the prefix trades/year=2025/month=04/day=15/. New objects arrive continuously throughout the day, and analysts query the data with Amazon Athena. They notice that Athena queries scanning a single day still read the entire month of data and cost more than expected. The team wants to reduce the bytes scanned without changing the data format or rewriting existing objects. Which solution meets these requirements?

Accepted Answer

Enable S3 Partition Projection in Athena for the table using the year, month, and day columns, and add the partition columns to the AWS Glue Data Catalog table.. Athena reduces scanned bytes when it can prune partitions from the query predicate. Partition projection computes partition locations from table properties instead of relying on catalog entries, and declaring the partition columns in the Glue table makes the day filter effective. This satisfies the constraints of no format change and no object rewrite while cutting the bytes read for single-day queries.

Answer

Convert the daily trade logs to Apache Parquet and store the new files under the same prefix, leaving historical files in their original format.

Answer

Run MSCK REPAIR TABLE on the trades table in Athena each hour to discover the new day partitions, then query with a WHERE clause on the day column.

Answer

Create a separate S3 bucket for each day and update the Athena table to point at the new bucket, then delete the old monthly prefix.

Question 11

A data engineer is managing an Amazon Redshift cluster that experiences performance degradation during peak query hours. The cluster uses a dc2.large node type with 2 slices per node. The engineer needs to improve query performance for complex joins and aggregations without changing the overall data volume. Which action should the engineer take?

Accepted Answer

Choose a sort key on the columns frequently used in joins and WHERE clauses.. Sort keys are a fundamental Redshift optimization that physically orders data on disk, enabling efficient merge joins and range-restricted scans. Selecting a sort key on columns commonly used in joins and filters reduces the amount of data read and processed, directly improving complex query performance. Other options either do not address query efficiency for large tables or involve broader changes like resizing.

Answer

Enable concurrency scaling to handle peak query loads.

Answer

Change the distribution style of large dimension tables to ALL.

Answer

Increase the number of slices per node by resizing the cluster to a node type with more slices.

Question 12

A data engineer manages an Amazon DynamoDB table that stores user activity events. The table uses a partition key of `userId` and a sort key of `eventTimestamp`. The workload is read-heavy with many queries that retrieve events for a specific user within a date range. The engineer notices high read latency and wants to optimize the table. What should the engineer do to improve read performance?

Accepted Answer

Ensure that queries use the partition key and sort key with a condition on the sort key to retrieve a contiguous range.. DynamoDB query performance is optimized when the partition key and sort key are used together with a condition on the sort key to retrieve a contiguous range. This leverages the underlying B-tree storage and avoids full table scans. DAX, GSIs, or increased capacity do not address the inefficient access pattern described.

Answer

Create a global secondary index (GSI) with `eventTimestamp` as the partition key and `userId` as the sort key.

Answer

Enable DynamoDB Accelerator (DAX) for the table to cache reads.

Answer

Increase the provisioned read capacity units (RCUs) for the table.

Question 13

A data engineer is building a data lake on Amazon S3. The raw data must be immutable and retained for seven years for compliance. Transformed data is frequently overwritten by daily ETL jobs. The engineer must enforce a policy that prevents any user, including administrators, from deleting or overwriting objects in the raw data prefix, while allowing full control over the transformed prefix. What should the engineer do?

Accepted Answer

Apply an S3 Object Lock in compliance mode with a seven-year retention period on the raw data prefix, and use a separate bucket or prefix without Object Lock for transformed data.. S3 Object Lock in compliance mode provides WORM protection that even the root user cannot bypass, ensuring raw data cannot be deleted or overwritten for seven years. Scoping it to the raw prefix leaves the transformed prefix mutable for daily ETL. Governance mode and IAM policies lack the required administrative immutability, and versioning alone does not prevent deletion.

Answer

Enable S3 Versioning on the bucket and configure a lifecycle rule to transition raw data to S3 Glacier Deep Archive after 30 days.

Answer

Enable S3 Object Lock in governance mode with a seven-year retention period for the entire bucket.

Answer

Create an IAM policy that denies s3:DeleteObject and s3:PutObject for the raw data prefix and attach it to all users except the root account.

Question 14

A data engineering team manages an AWS Glue Data Catalog with hundreds of tables. They need to keep table definitions in sync with an external Apache Hive metastore and also allow analysts to query the tables from Amazon Athena. Which TWO actions should the team take? (Choose two.)

Accepted Answer

Configure the AWS Glue Data Catalog as the metastore for the Hive clusters using the Hive metastore compatibility endpoint.. Pointing external Hive clients at the AWS Glue Data Catalog Hive metastore compatibility endpoint makes the Data Catalog the single authoritative metastore, so both Hive and Athena read the same table definitions. Registering and updating tables through that endpoint keeps the catalog current without duplicate metadata stores.

Answer

Create an Athena data source connector for Apache Hive to query the external metastore directly.

Answer

Export the Hive metastore schema to Amazon S3 and register each table manually in Athena.

Answer

Use an AWS Glue crawler with a JDBC connection to the Hive metastore to populate the Data Catalog.

Question 15

A data engineer is deploying an Amazon RDS for PostgreSQL DB instance that will serve an analytics workload. The workload requires low-latency random reads of a 2 TB dataset, and the team wants to minimize cost while meeting that requirement. The DB instance is not part of a Multi-AZ deployment and does not need to survive an Availability Zone failure. Which storage configuration should the engineer choose?

Accepted Answer

Provision the DB instance with gp3 volumes, setting the storage size to 2 TB and leaving baseline IOPS.. The workload needs low-latency random reads on a 2 TB non-Multi-AZ database while minimizing cost. SSD-backed gp3 offers the baseline IOPS and consistent low latency needed, and it is cheaper per GB than io1/io2 volumes. HDD-backed and magnetic storage cannot meet the latency requirement, and io2 Block Express is over-provisioned for the stated cost goal.

Answer

Provision the DB instance with magnetic standard storage sized at 2 TB.

Answer

Provision the DB instance with io2 Block Express volumes sized at 2 TB.

Answer

Provision the DB instance with st1 throughput-optimized HDD volumes sized at 2 TB.