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

Question 1

A data engineer manages an Amazon Redshift cluster that experiences performance degradation during peak hours due to concurrent long-running queries and short ad-hoc queries competing for resources. The engineer wants to isolate the workloads so that short queries are not blocked by long-running ones, and to ensure that each workload gets a guaranteed share of memory and CPU. Which Redshift feature should the engineer implement?

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

Accepted Answer

Workload Management (WLM) with query queues. Workload Management (WLM) enables the creation of separate query queues with configurable memory and concurrency, allowing isolation of long-running and short ad-hoc queries. This ensures that short queries are not blocked and that each workload receives a guaranteed share of resources. Other features like Concurrency Scaling, SQA, and Redshift Spectrum do not provide the same level of workload isolation.

Answer

Redshift Spectrum

Answer

Short Query Acceleration (SQA)

Answer

Concurrency Scaling

Question 2

A data engineer is setting up an Amazon S3 bucket to store large CSV files that will be queried using Amazon Athena. The engineer wants to minimize query costs and improve performance. The files are currently stored in a single prefix without any partitioning. The most common queries filter data by `year` and `month`. What should the engineer do to optimize the Athena queries?

Accepted Answer

Convert the CSV files to Apache Parquet format and partition the data by `year` and `month`.. Converting data to Apache Parquet and partitioning by commonly filtered columns like `year` and `month` are the most effective ways to reduce data scanned by Athena. Parquet's columnar format allows Athena to read only the columns needed, and partitioning enables partition pruning. Together, they minimize query cost and improve performance. Other options either do not address the core optimizations or introduce unnecessary complexity.

Answer

Compress the CSV files using gzip and keep them in a single prefix.

Answer

Create an AWS Glue Data Catalog table with partition keys `year` and `month`, and use Amazon Athena to query the CSV files directly.

Answer

Use Amazon Redshift Spectrum to query the CSV files in S3 instead of Athena.

Question 3

A data engineer manages an Amazon DynamoDB table for order events. Reads and writes are evenly spread across a partition key with very high cardinality, but during flash sales the table throttles with ProvisionedThroughputExceededException even though consumed capacity is below the provisioned total. Which cause is MOST likely?

Accepted Answer

A single partition key value is receiving a disproportionate share of traffic, creating a hot partition that exceeds its per-partition throughput limit.. DynamoDB throughput is provisioned at the table level but enforced at the partition level, and each partition can serve only about 1,000 write units per second. When traffic concentrates on one partition key value, that partition throttles while the table still shows unused capacity. Even distribution across a high-cardinality key normally prevents this, so the flash-sale burst must be funneling requests to a single key value.

Answer

The table's on-demand capacity mode must be enabled to remove per-partition limits.

Answer

The table's items are too large, and each write consumes more than one write capacity unit.

Answer

The table has too many global secondary indexes, which each consume write capacity.

Question 4

A data engineer is designing a data lake on Amazon S3 to store JSON logs from an application. The logs are written once and never modified. The engineer needs to query the data using Amazon Athena with the best performance and lowest cost. The engineer wants to partition the data by year, month, and day based on the log timestamp. Which approach should the engineer use to organize the S3 objects?

Accepted Answer

Use a Hive-style prefix structure such as s3://bucket/year=YYYY/month=MM/day=DD/ and define partitions in the AWS Glue Data Catalog.. Organizing data in a Hive-style prefix hierarchy allows Athena and AWS Glue to recognize partitions. When queries filter on partition columns, only relevant prefixes are scanned, reducing data scanned and cost. This is the recommended practice for time-series data in S3. Other options either do not create real partitions or fail to provide efficient pruning.

Answer

Enable S3 Inventory to generate a manifest of objects and use it to create a partitioned table in Athena.

Answer

Store objects in a flat structure and create an AWS Glue crawler to automatically add partitions based on file metadata.

Answer

Store all objects in a single prefix and create a table with a partition projection based on the timestamp column.

Question 5

A company stores customer transaction data in an Amazon DynamoDB table. The table has a partition key of CustomerID and a sort key of TransactionDate. The data engineering team needs to retrieve all transactions for a specific customer within a date range, and the queries must be efficient. Which DynamoDB operation should the team use?

Accepted Answer

Query the table with a KeyConditionExpression on CustomerID and a condition on TransactionDate.. The Query operation is designed to retrieve items sharing the same partition key value, with optional conditions on the sort key. By specifying CustomerID and a condition on TransactionDate, the team can efficiently fetch the desired transactions. Other operations either scan the entire table or retrieve single items, which are less efficient for this use case.

Answer

Scan the table with a FilterExpression on CustomerID and TransactionDate.

Answer

Use a BatchGetItem request with multiple CustomerID and TransactionDate pairs.

Answer

Use a GetItem request with the CustomerID and TransactionDate as the primary key.

Question 6

A data engineer manages an Amazon Redshift cluster that experiences performance degradation during peak hours due to concurrent long-running queries and short ad-hoc queries competing for resources. The engineer wants to isolate the workloads so that short queries are not blocked by long-running ones, and to ensure that each workload gets a guaranteed share of memory and CPU. Which Redshift feature should the engineer implement?

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

Accepted Answer

Analyze the query execution plans to identify tables with high redistribution or broadcast steps.. Analyzing query execution plans is the essential first step to identify performance bottlenecks such as data redistribution or broadcast operations. These indicate suboptimal distribution styles. Once identified, the engineer can make informed changes to distribution keys and sort keys. The other options are either premature or not targeted at the root cause.

Answer

Change all tables to use EVEN distribution style to balance data across nodes.

Answer

Set the sort key to the column most frequently used in WHERE clauses for all tables.

Answer

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

Question 7

A data engineer needs to keep a near-real-time copy of an Amazon DynamoDB table in Amazon S3 for analytics, capturing every item-level change with the before and after images and no impact on table write latency. Which approach meets these requirements with the LEAST operational effort?

Accepted Answer

Enable DynamoDB Streams on the table and write an AWS Lambda function to batch stream records into S3.. DynamoDB Streams records every item-level change in order and can be configured to include both the old and new images of each item. A Lambda function consuming the stream writes batched records to S3 asynchronously, so table write latency is unaffected, and the serverless subscription keeps operational effort low. The alternative approaches are periodic, snapshot-based, or heavier to operate.

Answer

Enable DynamoDB point-in-time recovery and export the recovery window to S3 on a schedule.

Answer

Schedule an AWS Glue job every five minutes to run a full table scan and overwrite the S3 objects.

Answer

Use AWS Database Migration Service with change data capture from DynamoDB to Amazon S3.

Question 8

A company uses Amazon DynamoDB to store user session data. The table has a partition key of user_id and a sort key of session_start. The workload is read-heavy and eventually consistent reads are acceptable. The table is provisioned with 1000 RCUs and 500 WCUs. During peak hours, the application experiences throttling on read operations, but CloudWatch shows that the consumed read capacity is well below the provisioned amount. What is the most likely cause of the throttling?

Accepted Answer

The table has a hot partition because user_id values are not evenly distributed, causing some partitions to exceed their read capacity limits.. DynamoDB throttling can occur even when overall consumed capacity is below provisioned if a single partition exceeds its per-partition limit. With a partition key like user_id, if some users are much more active, their items concentrate on one partition, causing that partition to throttle. The solution is to distribute the workload more evenly, such as by adding a random suffix to the partition key or using a composite partition key.

Answer

The table's sort key is causing uneven data distribution across partitions, leading to throttling.

Answer

The application is using strongly consistent reads, which consume twice the read capacity units and cause throttling.

Answer

The table's provisioned read capacity is too low for the workload, and the engineer should increase it to resolve throttling.

Question 9

A data engineer is managing an Amazon S3 data lake that contains millions of small files. The engineer needs to optimize query performance in Amazon Athena and reduce costs. The data is stored in Parquet format and is partitioned by date. Which action should the engineer take to improve performance and reduce costs?

Accepted Answer

Use AWS Glue ETL to compact small files into larger files.. Compacting small files into larger files using AWS Glue ETL reduces the number of files and S3 requests, improving Athena query performance and reducing costs. Other options either do not address the small file issue or would degrade performance. This is a best practice for optimizing data lakes.

Answer

Convert the data to CSV format to enable faster scans.

Answer

Enable S3 Versioning on the bucket to improve read performance.

Answer

Increase the number of partitions to reduce the amount of data scanned per query.

Question 10

A data engineer manages an Amazon S3 data lake where analytics queries run through Amazon Athena. Monthly partition folders hold Parquet files, and each partition contains tens of thousands of small files averaging 40 KB. Athena queries that scan a single month take much longer than expected and consume far more bytes scanned than the actual data volume. The engineer must improve query performance without changing the table schema or the folder layout. What should the engineer do?

Accepted Answer

Run an AWS Glue ETL job that reads each partition and rewrites it as fewer, larger Parquet files of roughly 128 MB, then update the partitions in the Data Catalog.. Athena performance degrades when a partition holds a huge number of very small objects because each object requires a separate request and contributes metadata overhead, inflating both runtime and reported bytes scanned. Compacting the files with an AWS Glue job into larger Parquet objects preserves the schema and partition layout while cutting that overhead, which is the standard remedy for this pattern.

Answer

Enable S3 Transfer Acceleration on the bucket so that Athena can retrieve the small objects with lower latency across edge locations.

Answer

Attach an S3 Lifecycle policy that transitions the Parquet objects to S3 Glacier Instant Retrieval after 30 days to improve read throughput.

Answer

Increase the number of partitions by splitting each monthly folder into daily folders and repointing the table at the new prefixes.

Question 11

A data engineer is configuring an Amazon Redshift cluster and needs to optimize query performance for complex analytical queries that involve large joins. The engineer wants to reduce the amount of data movement during query execution. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Define sort keys on columns frequently used in join and filter conditions.. To reduce data movement during joins in Amazon Redshift, the engineer should choose a distribution style that colocates joined tables on the same node slices and define sort keys on columns used in joins and filters. These actions minimize network traffic and enable efficient merge joins. Increasing nodes, enabling concurrency scaling, or using columnar storage do not directly address data movement within a query.

Answer

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

Answer

Enable concurrency scaling to handle concurrent queries.

Answer

Use columnar storage for all tables.

Question 12

A data engineer is building a near-real-time ingestion pipeline into Amazon S3. Small JSON files arrive continuously from thousands of devices, and the engineer must optimize the data lake for downstream Amazon Athena queries while minimizing storage cost and query latency. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Use AWS Glue ETL jobs to compact small JSON files into larger Parquet files partitioned by ingestion date.. The pipeline suffers from many small JSON files, which increase Athena query overhead and cost. Compacting files into larger Parquet objects with AWS Glue ETL and using Kinesis Data Firehose to buffer and aggregate records both reduce object count and improve columnar query performance. Transfer Acceleration, Glacier Instant Retrieval, and aborting multipart uploads do not address file size or format for analytics.

Answer

Store the raw JSON files in S3 Glacier Instant Retrieval to reduce storage cost immediately.

Answer

Configure an S3 Lifecycle rule to abort incomplete multipart uploads after 7 days.

Answer

Enable S3 Transfer Acceleration on the ingestion bucket to speed up uploads from devices.

Question 13

A data engineer is using AWS Glue to process data stored in Amazon S3. The engineer needs to ensure that the AWS Glue job can access the S3 bucket securely without hardcoding credentials. Which approach should the engineer use?

Accepted Answer

Create an IAM role with the necessary S3 permissions and attach it to the AWS Glue job.. Attaching an IAM role with the necessary S3 permissions to the AWS Glue job is the secure and recommended way to grant access without hardcoding credentials. IAM roles provide temporary credentials and follow the principle of least privilege. The other options either introduce security risks or unnecessary complexity.

Answer

Embed the AWS access key and secret key directly in the Glue job script.

Answer

Store AWS credentials in AWS Secrets Manager and retrieve them within the Glue job script.

Answer

Use an Amazon S3 bucket policy that allows public read access to the data.

Question 14

A data engineer is designing a real-time analytics pipeline that ingests clickstream data into Amazon Kinesis Data Streams. The data must be stored in Amazon S3 for later analysis with Amazon Athena. The engineer needs the data to be queryable with minimal latency and wants to avoid managing complex ETL jobs. Which solution should the engineer use?

Accepted Answer

Use Amazon Kinesis Data Firehose to deliver data to Amazon S3 with record format conversion to Parquet using an AWS Glue table.. Amazon Kinesis Data Firehose can ingest streaming data and deliver it to Amazon S3 with automatic record format conversion to Parquet using an AWS Glue table. This provides near-real-time, queryable data in a columnar format without custom ETL code. Other options require writing and managing Lambda functions, Kinesis Data Analytics applications, or Glue streaming jobs, which add complexity and do not offer the same level of managed simplicity.

Answer

Use Amazon Kinesis Data Analytics to run SQL queries on the stream and write results to Amazon S3.

Answer

Use AWS Lambda to read from Kinesis Data Streams and write JSON files to Amazon S3.

Answer

Use AWS Glue streaming ETL jobs to read from Kinesis Data Streams and write Parquet to Amazon S3.

Question 15

A data engineer maintains an Amazon DynamoDB table that stores device telemetry. The table uses a partition key of deviceId and a sort key of timestamp, with on-demand capacity mode. A new fleet of devices writes data with a deviceId pattern that hashes to a small number of partitions, and the engineer observes throttling on writes even though consumed capacity is well below any configured limit. Which change addresses the root cause?

Accepted Answer

Redesign the key schema to use a higher-cardinality partition key, such as a composite of deviceId and a shard suffix, while keeping timestamp as the sort key.. Throttling at low consumed capacity on a DynamoDB table almost always indicates a hot partition caused by an uneven partition key. Because data is distributed by the hash of the partition key, a key with few distinct values funnels traffic into a small number of partitions. Adding a shard suffix to the partition key spreads the writes while preserving sort-key range queries.

Answer

Create a global secondary index on timestamp and direct all writes through that index to distribute the load.

Answer

Switch the table from on-demand to provisioned capacity mode and raise the write capacity units substantially.

Answer

Enable DynamoDB Streams on the table so that write operations are buffered and replayed during peak periods.