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

Question 1

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 2

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 3

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 (B) is correct because it is an infrequent-access class that stores data in a single Availability Zone while still providing millisecond retrieval latency. S3 Intelligent-Tiering (D) is correct because it automatically moves objects between access tiers based on changing access patterns, and its Frequent and Infrequent Access tiers both deliver millisecond retrieval times. S3 Standard-IA (E) is correct because it is explicitly designed for infrequently accessed data and provides the same low-latency, millisecond retrieval performance as S3 Standard. S3 Glacier Flexible Retrieval (A) is not correct here because its retrieval options range from minutes to hours, not milliseconds, and S3 Glacier Deep Archive (C) is not correct because it is the lowest-cost archive class with retrieval times typically within 12 hours.

Answer

S3 Glacier Flexible Retrieval

Answer

S3 Glacier Deep Archive

Question 4

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 5

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 6

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.. Option C is correct because partitioning the S3 data by frequently filtered columns such as date or region lets Athena prune partitions and scan only the relevant prefixes, drastically reducing the amount of data read and thus improving query speed and lowering cost. Option D is correct because columnar formats like Parquet or ORC allow Athena to read only the columns referenced in the query and benefit from compression and predicate pushdown, minimizing I/O compared to row-based formats. Options A and B do not improve query performance: S3 Versioning only retains object versions for recovery, and SSE-KMS only encrypts data at rest, adding no scan optimization (and KMS calls can even add latency). Option E is incorrect because CSV is a row-based, uncompressed text format that forces Athena to scan entire rows and cannot skip unneeded columns, making queries slower and more expensive than Parquet/ORC.

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 7

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).. Option A is correct because table size (number of rows) is a primary driver of distribution style choice: small tables are typically assigned ALL distribution so they can be broadcast to every node, eliminating data movement during joins, while large tables need KEY or EVEN distribution. Option B is correct because the whole purpose of choosing a distribution style is to co-locate matching rows on the same slice; if a table is frequently joined on a specific column, distributing both tables on that join key keeps the join local and avoids network redistribution. Option D is correct because fact and dimension tables play different roles in a star schema: large fact tables are usually distributed on their most common join key (or EVEN), while smaller dimension tables are often set to ALL so they are replicated to every node and can be joined without movement. Option C is not a factor because the number of columns does not affect how rows are distributed across slices; it only affects storage width and I/O, not join data movement. Option E is not a factor because Redshift supports distribution keys of various data types, and the data type itself does not determine whether a join requires redistribution — what matters is whether the joined columns match and are used as distribution keys.

Answer

The number of columns in the table.

Answer

The data type of the distribution key column.

Question 8

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.

Question 9

A company is migrating its on-premises MySQL database to Amazon RDS for MySQL. They want to minimize downtime and ensure data consistency. Which AWS service should be used for the migration?

Accepted Answer

AWS Database Migration Service (DMS). AWS Database Migration Service (DMS) is the correct choice because it is specifically designed for migrating databases to AWS with minimal downtime. DMS supports homogeneous migrations like MySQL to Amazon RDS for MySQL, and it uses ongoing replication (change data capture) to keep the source and target databases in sync during the migration, ensuring data consistency and allowing a cutover with only seconds of downtime.

Answer

AWS S3 Transfer Acceleration

Answer

AWS Glue

Answer

AWS Snowball Edge

Question 10

A data engineer is troubleshooting a slow-running query on an Amazon Redshift cluster. The query involves joining two large tables. The engineer notices that the query plan shows a large number of distribution and broadcast operations. Which design change would most likely improve query performance?

Accepted Answer

Change the distribution style of both tables to KEY on the join column. Changing the distribution style of both tables to KEY on the join column ensures that rows with the same join key value are co-located on the same node. This eliminates the need for expensive broadcast or redistribution operations during the join, as Redshift can perform the join locally on each slice without moving data across the network.

Answer

Change the distribution style of both tables to ALL

Answer

Change the distribution style of both tables to EVEN

Answer

Add a sort key on the join column

Question 11

A company runs a real-time analytics platform using Amazon Kinesis Data Streams with a shard count of 10. The data is consumed by an AWS Lambda function that writes to an Amazon DynamoDB table. The DynamoDB table has a partition key of 'user_id' and a sort key of 'timestamp'. The table is provisioned with 5000 RCUs and 5000 WCUs. Recently, the application experienced increased write latency and throttling errors (ProvisionedThroughputExceededException) on the DynamoDB table. The CloudWatch metrics show that ConsumedWriteCapacityUnits averages 4500 with occasional spikes to 6000. The Lambda function’s concurrency is set to 1000. The data engineer suspects the issue is due to hot partitions. Upon investigation, the engineer finds that a small number of users generate a disproportionately large amount of data. Which course of action would best resolve the throttling while minimizing cost?

Accepted Answer

Enable DynamoDB adaptive capacity and implement write sharding by adding a suffix to the partition key for high-volume users. The root cause is hot partitions caused by a small number of high-volume users. Enabling DynamoDB adaptive capacity allows the table to automatically adjust throughput to accommodate uneven access patterns, but the key fix is write sharding — adding a random or calculated suffix to the partition key for those high-volume users. This distributes writes across multiple physical partitions, eliminating the hot spot without requiring a global increase in provisioned capacity, thus resolving throttling while minimizing cost.

Answer

Increase the provisioned WCUs to 10000 to handle spikes

Answer

Switch the DynamoDB table to on-demand capacity mode

Answer

Reduce the Lambda function concurrency to 100 to limit write requests

Question 12

Order the steps to set up an Amazon EMR cluster for processing data in S3 using Spark.

Accepted Answer

Prepare the S3 bucket, then launch the EMR cluster with Spark, then configure instances, then submit the job, then terminate the cluster.. First, prepare the S3 bucket. Then launch the EMR cluster with Spark, configure instances, submit the job, and terminate.

Answer

Launch the EMR cluster with Spark, then prepare the S3 bucket, then configure instances, then submit the job, then terminate the cluster.

Answer

Prepare the S3 bucket, then configure instances, then launch the EMR cluster with Spark, then submit the job, then terminate the cluster.

Answer

Submit the job, then launch the EMR cluster with Spark, then configure instances, then prepare the S3 bucket, then terminate the cluster.

Question 13

Match each AWS Glue component to its role.

Accepted Answer

Data Catalog: Central metadata repository for storing table definitions and schema.. AWS Glue components work together for ETL. The Data Catalog stores metadata, Crawlers populate the catalog, Classifiers infer schema, and ETL Jobs execute transformations. Connections provide connectivity but are not listed here.

Answer

Data Catalog: Executes transformation scripts.

Answer

Crawler: Stores configuration for connecting to data stores.

Question 14

Match each AWS monitoring tool to its primary use.

Accepted Answer

CloudWatch: Monitor metrics, logs, and alarms. CloudWatch monitors metrics and logs, CloudTrail audits API calls, X-Ray traces requests, and Trusted Advisor provides optimization recommendations. Common confusions include swapping the roles of CloudWatch and CloudTrail.

Answer

CloudWatch: Auditing API activity

Answer

CloudTrail: Monitor metrics, logs, and alarms

Question 15

A data engineer is designing a data lake on Amazon S3. The data is ingested from multiple sources and needs to be partitioned by year, month, day, and event type for efficient querying with Amazon Athena. Which S3 key prefix structure is most appropriate?

Accepted Answer

s3://bucket/event_type=events/year=2024/month=01/day=01/data.parquet. Uses Hive-style partitioning (event_type=events/year=2024/month=01/day=01), which Athena and other query engines natively support. This structure allows Athena to perform partition pruning, reading only the relevant directories based on WHERE clause filters, significantly reducing data scanned and improving query performance. Option D also uses Hive-style partitioning but with a different order of partition keys (day, month, year). While still valid, this non-standard order may cause issues with automatic partition discovery when using MSCK REPAIR TABLE, which expects the partition order to match the table definition. Therefore, option C is the most appropriate because it follows the common convention of listing partitions from coarse to fine granularity (year > month > day) and can be easily loaded into Athena without additional configuration.

Answer

s3://bucket/events/2024-01-01/event_type=data.parquet

Answer

s3://bucket/2024/01/01/event_type/events/data.parquet

Answer

s3://bucket/day=01/month=01/year=2024/event_type=events/data.parquet