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

Question 1

A data engineer manages an Amazon Redshift provisioned cluster that ingests clickstream data every minute into a staging table and then runs a nightly ELT job joining it to dimension tables. The cluster's disk usage is high, and VACUUM runs are taking hours. The engineer wants to reduce disk usage and improve query performance for the nightly joins without changing the cluster size. Which action should the engineer take?

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

Accepted Answer

Define a distribution key on the join column of the staging table and a matching distribution key on the dimension table, then run VACUUM FULL.. Redshift join performance improves when joined tables share a distribution key on the join column, enabling collocated joins that avoid network redistribution. VACUUM FULL reclaims space from deleted rows and re-sorts data, which lowers disk usage and reduces future vacuum time. Combining distribution alignment with a full vacuum addresses both the space and performance problems within the existing cluster.

Answer

Apply a sort key on the join column of the staging table and run VACUUM SORT ONLY during a maintenance window.

Answer

Run a deep copy of the staging table using CREATE TABLE AS with a new sort key, then drop and rename the original table.

Answer

Enable automatic table optimization on the staging and dimension tables so Redshift manages sort keys and distribution keys.

Question 2

A data engineer is optimizing an Amazon Redshift cluster that runs complex analytical queries. The engineer notices that some queries perform large hash joins and spill to disk, causing performance degradation. The engineer wants to reduce disk spills and improve query performance by using appropriate distribution styles and sort keys. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Set the distribution style of both tables to KEY on the join column to colocate matching rows.. To reduce disk spills in hash joins, the engineer should minimize data movement. Distributing both tables by the join key (KEY distribution) colocates matching rows on the same node, eliminating shuffling. Alternatively, broadcasting the smaller table (ALL distribution) and distributing the larger table by the join key also avoids shuffling the larger table. Both approaches reduce network traffic and disk spills, improving query performance.

Answer

Set the distribution style of the larger table to ALL and the smaller table to EVEN.

Answer

Set the sort key of the larger table to the column used in the join and the sort key of the smaller table to the same column.

Answer

Use AUTO distribution and AUTO sort keys to let Redshift manage the table design.

Question 3

A logistics company uses Amazon Redshift for analytics. The cluster is experiencing high disk usage due to large, unsorted tables. The data engineer needs to improve query performance and reduce storage consumption. Which action should the engineer take?

Accepted Answer

Choose an appropriate sort key and run a deep copy to re-sort the tables.. In Amazon Redshift, sort keys determine the order in which data is stored on disk. A well-chosen sort key improves query performance by allowing the query engine to skip irrelevant blocks using zone maps. Performing a deep copy re-sorts the data according to the new sort key, which can also improve compression and reduce storage usage. This directly addresses the high disk usage and slow queries.

Answer

Run VACUUM and ANALYZE on the tables to reclaim space and update statistics.

Answer

Increase the number of nodes in the cluster to add more storage and compute.

Answer

Enable concurrency scaling to offload queries to additional clusters.

Question 4

A financial services company stores sensitive customer data in an Amazon S3 bucket. The data engineer must ensure that all data is encrypted at rest using AWS Key Management Service (AWS KMS) customer managed keys, and that the encryption key is automatically rotated every year. The engineer also needs to audit key usage. Which combination of actions should the engineer take to meet these requirements?

Accepted Answer

Create a customer managed key in AWS KMS, enable automatic key rotation, and configure the S3 bucket to use SSE-KMS with this key.. To meet encryption at rest with customer managed keys, automatic annual rotation, and auditability, the engineer must create a customer managed key in AWS KMS, enable automatic rotation, and configure the S3 bucket to use SSE-KMS with that key. AWS managed keys do not support automatic rotation, and SSE-S3 does not use KMS keys.

Answer

Enable default encryption on the S3 bucket using SSE-KMS with an AWS managed key, and enable automatic key rotation.

Answer

Configure S3 bucket encryption using SSE-KMS with an AWS managed key, and set up a bucket policy to log key usage to AWS CloudTrail.

Answer

Use server-side encryption with Amazon S3 managed keys (SSE-S3) and enable versioning on the bucket.

Question 5

A financial services company stores transactional data in Amazon DynamoDB. The table uses on-demand capacity mode and has a partition key of TransactionID (a random UUID) and a sort key of Timestamp. A new compliance requirement mandates that all data older than 90 days be moved to Amazon S3 Glacier Instant Retrieval for long-term archival, while keeping the last 90 days in DynamoDB for low-latency queries. The data engineer needs to implement this with minimal operational overhead. What should the engineer do?

Accepted Answer

Configure DynamoDB TTL to expire items after 90 days, and use an AWS Lambda function triggered by TTL deletions to archive the expired items to Amazon S3.. DynamoDB TTL automatically expires items based on a timestamp attribute, and TTL deletions generate records in DynamoDB Streams. By enabling TTL and attaching a Lambda function to the stream, the engineer can archive expired items to Amazon S3 before they are deleted, achieving the 90-day retention policy with minimal operational effort. Other options either cannot identify static old items or require heavy scanning and custom deletion logic.

Answer

Use AWS Glue to crawl the DynamoDB table, create a Data Catalog table, and run a nightly Glue ETL job that reads all items, filters those older than 90 days, writes them to Amazon S3, and deletes them from DynamoDB.

Answer

Create a DynamoDB global secondary index (GSI) on the Timestamp attribute, and use a scheduled AWS Lambda function to query the GSI for items older than 90 days, move them to Amazon S3, and delete them from the base table.

Answer

Enable DynamoDB Streams on the table, and use an AWS Lambda function to process stream records, writing older items to Amazon S3 and deleting them from DynamoDB.

Question 6

A data engineer is using Amazon Redshift to store sales data. The cluster has a single RA3 node. The engineer needs to run a complex query that joins a large fact table with a small dimension table. The dimension table is frequently updated with new rows. Which approach should the engineer take to optimize the join performance?

Accepted Answer

Distribute the fact table by the join key and use ALL distribution for the dimension table.. For star schema joins, distributing the large fact table by the join key and using ALL distribution for small dimension tables minimizes data movement. ALL distribution replicates the small table to all nodes, so each node can perform the join locally without network shuffling.

Answer

Use DISTSTYLE ALL on both tables to avoid data movement.

Answer

Distribute both tables by the join key using EVEN distribution.

Answer

Use DISTSTYLE KEY on the dimension table with the join key, and EVEN on the fact table.

Question 7

A financial services company runs an Amazon Aurora PostgreSQL cluster that stores transaction records. Compliance requires the data to be retained for seven years, and the team needs the ability to restore the database to any point within the last 35 days after an accidental deletion. The team also wants the retained data to remain queryable with SQL long after the cluster is deleted. Which combination of features should the data engineer configure?

Accepted Answer

Enable automated backups with a 35-day retention period and export Aurora snapshots to Amazon S3 in Apache Parquet for long-term SQL querying.. Meeting both requirements needs two mechanisms: continuous backups for fine-grained recovery and an independent, durable copy for long-term SQL access. Automated backups with a 35-day window cover point-in-time recovery inside that window, while exporting snapshots to S3 in Parquet keeps the records queryable after the cluster is gone and supports seven-year retention.

Answer

Enable Aurora backtrack on the cluster and configure a 35-day backtrack window for recovery.

Answer

Take manual DB snapshots daily and keep them indefinitely, relying on snapshot restore for point-in-time recovery.

Answer

Enable automated backups with a 35-day retention period and copy the automated backups into an Amazon S3 Glacier Deep Archive vault.

Question 8

A data engineer is using Amazon Redshift and needs to load data from Amazon S3 into a table. The data is in CSV format and includes a column with timestamps in ISO 8601 format. The engineer wants to ensure that the load operation is efficient and that the timestamps are correctly parsed. Which method should the engineer use?

Accepted Answer

Use the COPY command with the TIMEFORMAT option set to 'auto' and the CSV option.. The COPY command is the most efficient way to load data from Amazon S3 into Amazon Redshift. Using the CSV option and TIMEFORMAT 'auto' ensures that CSV formatting is respected and ISO 8601 timestamps are parsed correctly. This leverages Redshift's parallel load capabilities, reducing load time and resource consumption.

Answer

Use AWS Glue to read the CSV, convert timestamps to strings, and write to Redshift using JDBC.

Answer

Use Amazon Kinesis Data Firehose to stream the CSV data into Redshift with the COPY command.

Answer

Use the INSERT INTO command for each row in the CSV file.

Question 9

A data engineer is designing a data lake on Amazon S3. The engineer needs to optimize storage costs for a large volume of data that is accessed frequently for the first 30 days, infrequently for the next 60 days, and rarely after 90 days. The data must be immediately accessible when needed. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Configure an S3 Lifecycle policy to transition objects to S3 Glacier Instant Retrieval after 90 days.. The correct actions are to transition objects to S3 Standard-IA after 30 days and to S3 Glacier Instant Retrieval after 90 days. These storage classes provide cost savings while maintaining immediate accessibility, matching the specified access patterns. Other options either do not meet the immediate accessibility requirement or introduce unnecessary complexity or reduced durability.

Answer

Enable S3 Intelligent-Tiering to automatically move objects between access tiers based on changing access patterns.

Answer

Configure an S3 Lifecycle policy to transition objects to S3 Glacier Deep Archive after 90 days.

Answer

Configure an S3 Lifecycle policy to transition objects to S3 One Zone-IA after 60 days.

Question 10

A financial services company stores transactional records in Amazon S3 and needs to enforce strict retention policies. Regulatory requirements mandate that certain records be retained for exactly 7 years and then permanently deleted, while other records must be retained for 3 years. The data engineering team wants to automate this lifecycle management with minimal operational overhead. Which solution should the team implement?

Accepted Answer

Use S3 Lifecycle policies to transition objects to S3 Glacier Deep Archive after 30 days and configure expiration after the required retention period based on object tags.. S3 Lifecycle policies with tag-based filters allow differentiated retention rules for objects with different regulatory requirements. Transitioning to Glacier Deep Archive reduces storage costs for long-term retention, and expiration automatically deletes objects after the specified period. This solution is fully managed, requires no custom code, and ensures compliance with minimal operational effort.

Answer

Configure an AWS Lambda function triggered by S3 event notifications to delete objects after the retention period.

Answer

Enable S3 Object Lock in compliance mode with a default retention period of 7 years for all objects.

Answer

Use S3 Intelligent-Tiering to automatically move objects to the most cost-effective access tier and rely on S3 analytics to identify objects for manual deletion.

Question 11

A media company stores video metadata in an Amazon DynamoDB table. The table uses on-demand capacity mode and has a partition key of videoId. During a live event, writes spike to 40,000 write request units per second, and the application starts receiving ProvisionedThroughputExceededException errors even though on-demand mode is enabled. The engineer confirms that each item is under 1 KB and that the access pattern is uniformly distributed across videoId values. Which action should the engineer take to resolve the throttling?

Accepted Answer

Verify that the write traffic is not concentrated on a single partition key value and consider using a write sharding strategy if a hot key exists.. On-demand mode scales automatically, but throttling can still occur if traffic concentrates on a single partition key value, creating a hot partition. The best first step is to verify the access distribution and apply write sharding if a hot key exists. This addresses the root cause without changing capacity mode or adding unrelated features.

Answer

Switch the table to provisioned capacity mode and set the write capacity to 40,000 WCUs.

Answer

Increase the table's item size limit by enabling DynamoDB Streams.

Answer

Enable DynamoDB Accelerator (DAX) for the table to absorb the write spike.

Question 12

A company stores sensitive customer data in Amazon S3. A data engineer needs to ensure that all data is encrypted at rest using keys managed by AWS, without managing key rotation manually. Which S3 encryption option should the engineer use?

Accepted Answer

Server-side encryption with Amazon S3 managed keys (SSE-S3). SSE-S3 uses AWS-managed keys and automatically rotates them, requiring no manual key management. It provides encryption at rest for all objects in the bucket. The other options either require manual key management or are not AWS-managed, making SSE-S3 the correct choice for this scenario.

Answer

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

Answer

Server-side encryption with AWS KMS customer managed keys (SSE-KMS with CMK)

Answer

Client-side encryption using the AWS Encryption SDK

Question 13

A data engineer is designing a storage solution for a data lake on Amazon S3. The data includes sensitive financial records that must be retained for seven years. The engineer needs to minimize storage costs while ensuring that data older than one year is rarely accessed but must be retrievable within 12 hours if needed. The engineer also wants to automate the transition of data between storage classes. Which two actions should the engineer take to meet these requirements? (Choose two.)

Accepted Answer

Configure an S3 Lifecycle policy to transition objects older than 365 days to S3 Glacier Deep Archive.. To minimize costs for data older than one year that is rarely accessed and must be retrievable within 12 hours, transition to S3 Glacier Deep Archive. To enforce the seven-year retention, configure an expiration policy to delete objects after seven years. These two actions automate cost-effective long-term storage and compliance.

Answer

Configure an S3 Lifecycle policy to transition objects older than 30 days to S3 Standard-IA.

Answer

Enable S3 Intelligent-Tiering to automatically move data between access tiers.

Answer

Use S3 Cross-Region Replication to replicate data to a second bucket for durability.

Question 14

A data engineer is managing an Amazon S3 data lake that stores millions of small JSON files. The engineer needs to optimize query performance and reduce costs for Amazon Athena queries. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Combine small files into larger files (e.g., 128 MB or larger).. Converting JSON to Parquet reduces data scanned and improves query speed. Combining small files into larger ones reduces overhead and improves Athena performance. Together, these actions address the core inefficiencies of small JSON files in a data lake.

Answer

Use Amazon S3 Select to query the JSON files directly.

Answer

Enable S3 Transfer Acceleration for the bucket.

Answer

Partition the data based on common query filters.

Question 15

A data engineer is building a data lake on Amazon S3 and needs to store large CSV files that are queried frequently by Amazon Athena. The engineer wants to reduce query costs and improve performance by minimizing the amount of data scanned. The files are currently stored as uncompressed CSV, and each file is approximately 1 GB. Which action should the engineer take to meet these requirements?

Accepted Answer

Convert the files to Apache Parquet format and compress them with Snappy.. Converting to a columnar format like Parquet with compression such as Snappy significantly reduces the amount of data Athena scans because only the required columns are read. This lowers query costs and improves performance for frequent queries on large datasets. Partitioning can help but is not as effective without columnar storage and compression.

Answer

Partition the data by date and store it as uncompressed CSV.

Answer

Use Amazon S3 Glacier Instant Retrieval storage class for the files.

Answer

Enable S3 Transfer Acceleration on the bucket.