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

Question 1

A data engineering team uses Amazon Redshift for analytics. They notice that queries on a large fact table are slow. The table is distributed using DISTSTYLE ALL. Which design change would most likely improve query performance?

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

Accepted Answer

Change DISTSTYLE to KEY on a column used in frequent joins.. DISTSTYLE ALL copies the entire table to every node, which is inefficient for large fact tables because it wastes storage and network bandwidth during data loading and query execution. Changing to DISTSTYLE KEY on a column used in frequent joins collocates related rows on the same slice, reducing the need to broadcast or redistribute data across the network during joins, which directly improves query performance.

Answer

Change DISTSTYLE to EVEN to distribute rows evenly across slices.

Answer

Increase the number of nodes in the Redshift cluster.

Answer

Change the table to use a SORTKEY on the most frequently filtered column.

Question 2

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 3

Which TWO actions should a data engineer take to encrypt data at rest in an Amazon S3 bucket? (Select TWO.)

Accepted Answer

Configure the bucket to use SSE-KMS.. SSE-KMS (Server-Side Encryption with AWS Key Management Service) encrypts data at rest in S3 by using a KMS key to manage encryption keys. This provides envelope encryption, where a CMK generates a data key that encrypts the object, and the data key is then encrypted by the CMK. Option D is correct because enabling default encryption on an S3 bucket using SSE-S3 (AES-256) ensures that all objects uploaded without explicit encryption headers are automatically encrypted at rest by S3's managed key.

Answer

Enable S3 Transfer Acceleration on the bucket.

Answer

Use client-side encryption before uploading objects to S3.

Answer

Attach a bucket policy that denies unencrypted PUT requests.

Question 4

A data engineer is designing a data lake on Amazon S3. The data must be immutable and support high-throughput streaming ingestion. Which THREE features should the engineer consider? (Select THREE.)

Accepted Answer

S3 Lifecycle policies to transition data to Amazon S3 Glacier. S3 Object Lock in governance mode (Option D) is correct because it enforces immutability by preventing objects from being deleted or overwritten for a specified retention period, which is essential for a data lake requiring immutable data. S3 Multipart Upload API (Option C) is correct because it enables high-throughput streaming ingestion by allowing large objects to be uploaded in parallel parts, improving throughput and resilience. S3 Lifecycle policies to transition data to Amazon S3 Glacier (Option B) is correct because it supports cost-effective storage management for immutable data that is rarely accessed, aligning with the data lake's lifecycle needs.

Answer

S3 Transfer Acceleration

Answer

S3 Cross-Region Replication (CRR)

Question 5

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

Question 6

Refer to the exhibit. A data engineer has attached this bucket policy to an S3 bucket. What is the effect of this policy?

Accepted Answer

It allows the DataLakeRole to read and write objects, but only over HTTPS.. The bucket policy uses a condition key `aws:SecureTransport` set to `true`, which restricts access to HTTPS (TLS) connections only. The `Principal` is `DataLakeRole`, and the `Action` includes `s3:GetObject` and `s3:PutObject`, so the policy allows that role to read and write objects exclusively over HTTPS, enforcing encrypted data in transit.

Answer

It enforces server-side encryption for all objects written to the bucket.

Answer

It allows anonymous access to the bucket for HTTPS requests.

Answer

It denies all access to the bucket except for requests from the DataLakeRole.

Question 7

Refer to the exhibit. A data engineer runs the above AWS CLI command to view the table metadata in the AWS Glue Data Catalog. The data is stored as CSV in S3 with partitions by year and month. When querying the table using Amazon Athena, no data is returned. What is the most likely cause?

Accepted Answer

The partitions have not been added to the Glue Data Catalog.. The AWS CLI command shown only retrieves table metadata, not partition metadata. In AWS Glue, partitions must be explicitly added to the Data Catalog via `MSCK REPAIR TABLE`, `ALTER TABLE ADD PARTITION`, or a Glue crawler. Without partition metadata, Athena cannot locate the data files under the partitioned S3 paths (e.g., `s3://bucket/year=2024/month=01/`), resulting in zero rows returned even though the table schema is defined.

Answer

The SerDe is not compatible with CSV files.

Answer

The S3 location points to a file instead of a folder.

Answer

The column data types are incorrect for the CSV data.

Question 8

Which THREE storage classes in Amazon S3 are designed for infrequently accessed data with millisecond retrieval times? (Select THREE.)

Accepted Answer

S3 One Zone-IA. S3 One Zone-IA is designed for infrequently accessed data that requires millisecond retrieval times, but does not require the resilience of multiple Availability Zones. It stores data in a single AZ and offers the same low-latency performance as S3 Standard, making it suitable for non-critical, infrequently accessed data.

Answer

S3 Glacier Flexible Retrieval

Answer

S3 Glacier Deep Archive

Question 9

A company stores time-series sensor data in Amazon S3. They need to query the data using SQL with minimal latency and no infrastructure management. Which service should they use?

Accepted Answer

Amazon Athena. Amazon Athena is the correct choice because it is a serverless interactive query service that allows you to analyze data directly in Amazon S3 using standard SQL without any infrastructure to manage. It is optimized for querying structured, semi-structured, and unstructured data stored in S3, making it ideal for time-series sensor data with minimal latency requirements.

Answer

Amazon Kinesis Data Analytics

Answer

Amazon Redshift

Answer

Amazon DynamoDB

Question 10

A data engineer needs to migrate an on-premises MySQL database to Amazon RDS for MySQL with minimal downtime. Which approach should they use?

Accepted Answer

Use AWS Database Migration Service (DMS) with ongoing replication from the source database.. AWS DMS with ongoing replication (change data capture, CDC) is the correct approach because it allows continuous synchronization from the on-premises MySQL source to the RDS target, enabling a cutover with minimal downtime. Unlike one-time export/import tools, DMS captures ongoing changes during the migration, so the target stays up-to-date until you switch over.

Answer

Use mysqldump to export the database and import into RDS.

Answer

Create an RDS read replica and promote it.

Answer

Use AWS Schema Conversion Tool (SCT) to convert the schema and then copy data.

Question 11

A company uses Amazon DynamoDB with on-demand capacity. They notice higher than expected costs due to a sudden spike in read traffic from a reporting job. The reporting job scans the entire table daily. What is the most cost-effective way to reduce costs while maintaining the same reporting output?

Accepted Answer

Use a Global Secondary Index (GSI) with a sort key that matches the reporting query pattern.. Using a Global Secondary Index (GSI) with a sort key tailored to the reporting query pattern allows the reporting job to query only the relevant items instead of scanning the entire table. This reduces the read capacity units consumed per operation, directly lowering costs under on-demand capacity, which charges per RCU consumed. The reporting output remains identical because the GSI returns the same data filtered by the query pattern.

Answer

Enable DynamoDB Accelerator (DAX) for caching.

Answer

Set a TTL attribute to automatically expire old data.

Answer

Reduce the read capacity units (RCU) in the table.

Question 12

A data engineer has set up an Amazon S3 lifecycle policy to transition objects to Glacier Instant Retrieval after 30 days. After 60 days, objects should transition to Deep Archive. However, objects are not transitioning to Deep Archive. What is the most likely cause?

Accepted Answer

Objects are smaller than 128 KB.. Amazon S3 lifecycle policies have a minimum object size requirement for transitions to certain storage classes. Objects must be at least 128 KB to transition to S3 Glacier Instant Retrieval. Since the policy first transitions objects to Glacier Instant Retrieval after 30 days, objects smaller than 128 KB cannot undergo that transition, and subsequent transitions to Deep Archive will also fail. The 30-day minimum interval requirement is met (30 days between transitions), so option D is not the cause. Versioning (A) does not prevent transitions, and Deep Archive (B) is supported in all commercial AWS regions. Therefore, the most likely cause is that objects are smaller than 128 KB.

Answer

The bucket has versioning enabled.

Answer

Deep Archive is not supported in the bucket's region.

Answer

The transition to Deep Archive requires a minimum of 30 days after the previous transition.

Question 13

A company is designing a data lake on Amazon S3. Which TWO strategies improve query performance for Amazon Athena?

Accepted Answer

Partition the data by frequently queried columns such as date or region.. Partitioning data by frequently queried columns (e.g., date or region) allows Athena to prune the data scanned by only reading the relevant partitions, reducing the amount of data scanned and improving query performance. This is a core optimization for Athena, which charges based on data scanned and performs better with less I/O.

Answer

Enable S3 Versioning on the bucket.

Answer

Use server-side encryption with AWS KMS (SSE-KMS).

Answer

Store data in CSV format with header rows.

Question 14

A company is migrating a legacy data warehouse to Amazon Redshift. They need to choose a distribution style to minimize data movement during joins. Which THREE factors should they consider?

Accepted Answer

The size of the table (number of rows).. The size of the table (number of rows) directly influences the distribution strategy. In Amazon Redshift, large tables benefit from a distribution style that evenly distributes rows across slices to avoid data skew, which can cause performance bottlenecks during joins. Choosing a distribution key that aligns with the join columns minimizes data movement, but the table size determines whether an ALL distribution (for small tables) or a KEY distribution (for large tables) is more appropriate to reduce shuffling.

Answer

The number of columns in the table.

Answer

The data type of the distribution key column.

Question 15

A company runs a multi-AZ Amazon RDS for PostgreSQL instance. They need to run a one-time analytical query that will take several hours and consume significant I/O. The query should not impact the primary workload. What should the data engineer do?

Accepted Answer

Create a read replica of the RDS instance and run the query on the replica.. Creating a read replica of the RDS for PostgreSQL instance allows the analytical query to run on a separate database engine without affecting the primary workload. Read replicas in Amazon RDS use asynchronous replication from the source instance, so the replica can handle heavy I/O and long-running queries independently. This ensures the primary instance remains available for the production workload without performance degradation.

Answer

Run the query directly on the primary instance during off-peak hours.

Answer

Increase the instance size to handle the load.

Answer

Enable Multi-AZ and run the query on the standby instance.