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

Question 1

A company uses Amazon RDS for PostgreSQL. The data engineer needs to ensure that the database is automatically backed up and that backups are retained for 35 days. What is the simplest way to achieve this?

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

Accepted Answer

Enable automated backups with a retention period of 35 days in the RDS instance configuration.. Amazon RDS for PostgreSQL allows you to enable automated backups directly in the instance configuration. By setting the backup retention period to 35 days, RDS automatically performs daily snapshots and retains transaction logs for point-in-time recovery within that window. This is the simplest method because it requires no external services or custom scripting.

Answer

Use AWS Backup to schedule daily backups with a 35-day retention.

Answer

Create a manual snapshot every day and delete them after 35 days using a script.

Answer

Enable automatic export of transaction logs to Amazon S3 and use S3 lifecycle policies.

Question 2

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

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 4

A company is using Amazon S3 as a data lake. The data engineer needs to ensure that all objects uploaded to a specific bucket are automatically replicated to a bucket in another AWS Region for disaster recovery. Which configuration should the engineer implement?

Accepted Answer

Enable S3 Cross-Region Replication (CRR) on the source bucket.. S3 Cross-Region Replication (CRR) is the correct choice because it automatically replicates objects from a source bucket in one AWS Region to a destination bucket in a different AWS Region, meeting the disaster recovery requirement for geographic separation. CRR requires versioning to be enabled on both buckets and replicates new objects asynchronously after upload.

Answer

Enable S3 Same-Region Replication (SRR) on the source bucket.

Answer

Use S3 Transfer Acceleration to copy objects to the destination.

Answer

Use S3 Batch Operations to copy existing objects.

Question 5

A data engineer needs to store and analyze time-series data from IoT devices. The data volume is 10 GB per day, and the queries are mostly on the most recent 7 days of data. The engineer wants to minimize storage costs while retaining historical data for 1 year. Which combination of AWS services is most cost-effective?

Accepted Answer

Amazon Timestream. Amazon Timestream is purpose-built for time-series data, offering automatic tiering between in-memory (for recent 7 days) and magnetic stores (for historical data up to 1 year). This matches the query pattern (mostly recent 7 days) and retention requirement (1 year) while minimizing storage costs through its serverless, pay-per-query model. Timestream also supports time-series-specific functions like interpolation and smoothing, making it more efficient than general-purpose databases for this workload.

Answer

Amazon DynamoDB with TTL and S3 for archival

Answer

Amazon Redshift

Answer

Amazon RDS with MySQL

Question 6

A company is using Amazon EMR to process large datasets stored in Amazon S3. The data engineer wants to reduce the time it takes to read data from S3 by optimizing the data format. Which file format should the engineer recommend?

Accepted Answer

Parquet. Parquet is the correct choice because it is a columnar storage format that significantly reduces the amount of data read from Amazon S3 during analytical queries. By storing data column-wise, Parquet enables predicate pushdown and compression, which minimizes I/O and speeds up data processing in Amazon EMR, especially for large datasets.

Answer

CSV

Answer

ORC

Answer

JSON

Question 7

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 8

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

Accepted Answer

Offloads read traffic from the DynamoDB table.. 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 9

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

Accepted Answer

Use appropriate sort keys for tables.. 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 10

A company runs a real-time analytics platform on Amazon ECS that ingests streaming data from Amazon Kinesis Data Streams, processes it, and stores results in Amazon DynamoDB. The data volume spikes unpredictably, causing DynamoDB to throttle write requests. The application uses on-demand capacity mode. The data engineer notices that the throttling occurs on a specific partition due to a hot key. The hot key is a customer ID that receives a disproportionate number of writes. The application cannot change the partition key design immediately. The engineer needs to reduce throttling while maintaining low latency. Which solution is most effective?

Accepted Answer

Implement a write buffer using Amazon SQS, and have consumers write to DynamoDB at a controlled rate.. Buffering writes through Amazon SQS decouples the ingestion rate from DynamoDB's capacity, allowing consumers to write at a controlled pace. This directly mitigates throttling on the hot key without requiring a partition key redesign, and SQS provides low-latency, durable buffering suitable for real-time analytics.

Answer

Switch to provisioned capacity with auto scaling and increase the write capacity units.

Answer

Enable DynamoDB Accelerator (DAX) to cache the hot key writes.

Answer

Use DynamoDB Streams to trigger a Lambda function that retries throttled writes.

Question 11

A company uses Amazon DynamoDB as its primary data store for a web application. The application experiences high latency during peak hours. The data engineer notices that the table has a large number of items with the same partition key. Which DynamoDB feature should the engineer use to improve performance?

Accepted Answer

Redesign the partition key to use a composite key that includes a timestamp or random suffix.. The high latency is caused by a hot partition, where many items share the same partition key, overwhelming a single DynamoDB partition. Redesigning the partition key to include a timestamp or random suffix distributes the workload evenly across partitions, improving throughput and reducing latency. This directly addresses the root cause of the performance issue.

Answer

Enable DynamoDB Accelerator (DAX) to cache read requests.

Answer

Create a global table to replicate data across multiple Regions.

Answer

Enable auto scaling on the table to increase write capacity.

Question 12

A data engineer needs to store semi-structured JSON data that is accessed infrequently but must be retrievable within minutes. The data is generated by IoT devices and each object is about 500 KB. The engineer wants the most cost-effective storage solution. Which AWS service should be used?

Accepted Answer

Amazon S3 Standard-Infrequent Access (S3 Standard-IA). Amazon S3 Standard-Infrequent Access (S3 Standard-IA) is the correct choice because it is designed for data that is accessed infrequently but requires rapid retrieval (within minutes). The 500 KB JSON objects from IoT devices fit the use case, and S3 Standard-IA offers lower storage costs than S3 Standard while maintaining the same low-latency retrieval performance, making it the most cost-effective option for this scenario.

Answer

Amazon S3 Glacier Deep Archive

Answer

Amazon S3 Standard

Answer

Amazon Elastic Block Store (EBS)

Question 13

A data engineer needs to store log files from multiple applications in a centralized location. The logs are generated in JSON format and each log entry is about 1 KB. The engineer needs to query the logs occasionally using SQL-like queries. Which AWS service is most appropriate?

Accepted Answer

Amazon Athena with data stored in S3. Amazon Athena is the most appropriate service because it allows you to query log files stored in S3 directly using standard SQL, without needing to load or transform the data. Since the logs are in JSON format and each entry is about 1 KB, Athena's schema-on-read approach works perfectly for occasional SQL-like queries, and you only pay for the data scanned per query, making it cost-effective for infrequent access.

Answer

Amazon DynamoDB

Answer

Amazon Redshift

Answer

Amazon RDS for MySQL

Question 14

A data engineer is migrating a large Oracle data warehouse to Amazon Redshift. The engineer needs to ensure optimal performance. Which TWO practices should the engineer follow?

Accepted Answer

Choose appropriate sort keys based on common query patterns.. Amazon Redshift uses sort keys to physically order data on disk, which allows the query optimizer to skip large blocks of data during scans via zone maps. Choosing sort keys based on common query patterns (e.g., range filters or frequent GROUP BY columns) dramatically reduces I/O and improves query performance, especially for large tables.

Answer

Design the schema as a normalized star schema with row-based storage.

Answer

Manually define compression encodings for each column.

Answer

Stage data in Amazon S3 before loading into Redshift.

Question 15

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.