DEA-C01 Practice Test 50 — 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 financial services company uses a multi-account AWS Organization with hundreds of accounts. The data engineering team needs to enable cross-account access to an encrypted S3 bucket in the data lake account (account ID 111111111111) for a Glue ETL job running in the analytics account (account ID 222222222222). The S3 bucket uses AWS KMS customer managed key (CMK) for server-side encryption (SSE-KMS). The Glue job fails with an AccessDenied error when trying to read data from the bucket. The IAM roles in both accounts have the necessary S3 permissions and the bucket policy allows access from the analytics account. What is the most likely cause of the failure?

Accepted Answer

The KMS key policy does not grant the analytics account's IAM role permission to use the key for decryption.. When an S3 bucket is encrypted with SSE-KMS using a customer managed key (CMK), cross-account access requires that the KMS key policy explicitly grants the external account's IAM role permission to use the key for decryption. Even if the S3 bucket policy allows access, KMS enforces its own key policy, and without a grant for kms:Decrypt, the Glue job in the analytics account cannot decrypt the objects. This is the most likely cause because the scenario states that S3 permissions and bucket policy are already correctly configured.

Answer

The S3 bucket is in a different region than the Glue job.

Answer

The Glue job does not have an IAM role assigned.

Answer

The S3 bucket policy does not allow the s3:GetObject action for the analytics account's IAM role.

Question 3

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 4

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 5

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 6

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 7

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 8

A data engineer needs to create a table in Amazon Athena that reads JSON data stored in Amazon S3. The JSON records are stored in a single file, one JSON object per line. The engineer wants Athena to automatically discover the schema and create the table without manually defining columns. Which AWS service or feature should the engineer use?

Accepted Answer

AWS Glue crawler. An AWS Glue crawler automatically scans data in Amazon S3, infers the schema, and creates table definitions in the AWS Glue Data Catalog. Athena uses this catalog to query the data without manual schema definition. This is the standard method for automatic schema discovery in a data lake.

Answer

Amazon Athena CREATE TABLE AS SELECT (CTAS) statement

Answer

AWS Glue DataBrew

Answer

Amazon S3 Inventory

Question 9

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.

Question 10

A data engineer is managing an Amazon S3 data lake that contains raw JSON data. The engineer needs to optimize the data lake for query performance and cost when using Amazon Athena. The data is currently stored in a single S3 prefix without partitioning, and queries often filter on `event_type` and `event_date`. The engineer wants to implement best practices for Athena. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Convert the JSON data to Apache Parquet format.. The two most effective actions are converting JSON to Parquet and partitioning by `event_type` and `event_date`. Parquet's columnar format reduces data scanned, and partitioning enables partition pruning. Together, they minimize query cost and improve performance. Other options either do not affect Athena queries or are less effective. These are core best practices for optimizing Athena on S3 data lakes.

Answer

Enable S3 Transfer Acceleration on the bucket.

Answer

Compress the JSON files using gzip.

Answer

Use Amazon S3 Select to filter data before querying with Athena.

Question 11

A data engineer is using Amazon Redshift and needs to improve query performance for a workload that involves frequent joins between a large fact table and a small dimension table. The dimension table is updated daily with new records. The engineer wants to minimize data movement during joins. Which Redshift distribution style should the engineer use for the dimension table?

Accepted Answer

ALL. ALL distribution replicates the entire dimension table to every node, eliminating the need to redistribute data during joins with the fact table. This minimizes network traffic and speeds up join operations. It is best suited for small dimension tables that are frequently joined with larger tables.

Answer

AUTO

Answer

EVEN

Answer

KEY

Question 12

A data engineer is designing a real-time analytics solution using Amazon DynamoDB. The workload requires capturing all changes to a DynamoDB table and processing them in near-real-time to update a materialized view in Amazon Redshift. Which approach should the engineer use to capture and process the changes?

Accepted Answer

Enable DynamoDB Streams and use AWS Lambda to process the stream and write to Redshift.. DynamoDB Streams captures item-level changes in near-real-time, and AWS Lambda can process these changes and write to Amazon Redshift. This serverless architecture is scalable, cost-effective, and requires minimal operational overhead. It is the standard pattern for change data capture from DynamoDB to other data stores.

Answer

Enable DynamoDB global tables and use Amazon Kinesis Data Streams to replicate changes to Redshift.

Answer

Use AWS Database Migration Service (AWS DMS) with ongoing replication from DynamoDB to Redshift.

Answer

Use DynamoDB Accelerator (DAX) to cache changes and periodically export to Redshift.

Question 13

A company stores application logs in Amazon S3 in JSON format. The logs are partitioned by year/month/day. A data engineer needs to create a table in the AWS Glue Data Catalog so that Amazon Athena can query the logs efficiently. The engineer wants to minimize query costs and ensure that new partitions are automatically recognized. Which combination of actions should the engineer take?

Accepted Answer

Create a table with partition projection enabled for year/month/day and set the storage location to the S3 prefix.. Partition projection in Athena allows the table to compute partition locations dynamically based on a defined pattern, so new partitions are recognized immediately without manual intervention or crawlers. This reduces metadata operations and enables partition pruning, which lowers query costs. Manual partition management, non-partitioned tables, or scheduled crawlers either add overhead or fail to provide immediate partition recognition.

Answer

Create a table with partitions and configure an AWS Glue crawler to run on a schedule to discover new partitions.

Answer

Create a table with partitions defined manually and run ALTER TABLE ADD PARTITION for each new day.

Answer

Create a table without partitions and rely on Athena to scan the entire S3 prefix.

Question 14

A data engineer is configuring an Amazon S3 bucket that will receive raw clickstream files from a mobile application. The engineer must ensure that the objects are protected against accidental overwrites and deletions for a defined retention period, and that the protection cannot be removed or shortened by any user, including the account root user. Which S3 feature should the engineer use?

Accepted Answer

S3 Object Lock in compliance mode with a retention period matching the required window.. S3 Object Lock provides write-once-read-many protection by binding a retention period to object versions. In compliance mode, no principal, including the account root user, can shorten or remove that retention, which is the only option that satisfies an absolute immutability requirement. Governance mode, by contrast, allows privileged users to bypass retention.

Answer

S3 Versioning with a lifecycle rule that transitions objects to S3 Standard-IA after 30 days.

Answer

A bucket policy that denies s3:DeleteObject and s3:PutObject to all principals except the ingestion role.

Answer

S3 default encryption with AWS KMS customer managed keys and a restrictive key policy.

Question 15

A data engineer is using Amazon Redshift and needs to improve query performance for a large fact table that is frequently joined with a much smaller dimension table. The engineer wants to minimize data movement during joins. Which distribution style should be used for the dimension table?

Accepted Answer

DISTSTYLE ALL. For a small dimension table that is frequently joined with a large fact table, DISTSTYLE ALL replicates the dimension table to all nodes. This ensures that each node has a local copy, eliminating the need to broadcast or redistribute the dimension data during joins. As a result, data movement is minimized, and join performance is significantly improved.

Answer

DISTSTYLE AUTO

Answer

DISTSTYLE KEY

Answer

DISTSTYLE EVEN