Medium DEA-C01 practice questions

A data engineering team runs a nightly reporting pipeline that executes several Amazon Athena queries orchestrated by AWS Step Functions. Recently, some queries intermittently fail, and the team needs to programmatically detect which specific queries failed and capture the error reason so they can automatically retry or alert. What is the MOST effective way to identify the exact failure cause for each Athena query execution?

A data engineering team runs ad hoc Athena queries against a large S3 data lake. Finance reports that Athena costs have spiked because analysts frequently run SELECT * queries that scan entire multi-terabyte tables. The team wants to enforce a hard limit on how much data any single query can scan, and automatically cancel queries that exceed that limit, without modifying the underlying tables or query patterns. Which approach meets this requirement with the least operational effort?

A data engineer maintains a nightly Athena CREATE TABLE AS SELECT (CTAS) job that transforms raw event logs into a curated Parquet dataset in an S3 prefix, registered as a table in the Glue Data Catalog. The job is orchestrated by Step Functions. Occasionally the workflow retries a failed step, and the engineer notices that the CTAS statement fails on rerun with an error indicating the target location already exists. The team wants the pipeline to be safely re-runnable without manual cleanup. Which approach best resolves this while keeping the transformation logic in Athena?

A data engineer stores clickstream data in S3 under the prefix s3://analytics-lake/events/year=2024/month=03/day=15/ using Hive-style partition folders. A Glue Data Catalog table was created by a crawler two months ago. New daily folders are being written continuously by a Firehose stream, but Athena queries that filter on recent dates return zero rows even though the files exist in S3. The engineer confirms the schema and file format are correct. What is the MOST cost-effective way to ensure Athena can query the newly added partitions going forward with minimal ongoing operational effort?

A data engineer manages an Athena-based reporting pipeline that queries a QuickSight dashboard backed by S3 data in Parquet format. New data arrives hourly into date-partitioned S3 prefixes (s3://logs/year=/month=/day=/hour=/). Analysts complain that queries frequently miss the most recent hour of data even though the files exist in S3. The team currently runs a Glue crawler once per day to update partitions. The engineer needs the most operationally efficient way to ensure Athena always recognizes newly arrived partitions without manual intervention or frequent crawler runs. What should the engineer do?

A data engineering team runs ad-hoc analytics on a data lake using Amazon Athena. Analysts have complained about unexpectedly high monthly Athena costs. The team needs to identify which specific queries and users are scanning the most data so they can take corrective action, without deploying additional infrastructure. What is the MOST operationally efficient way to gain this visibility?

A data engineering team maintains a daily reporting pipeline. A Glue job writes curated Parquet data to Amazon S3 each morning at 06:00 UTC, and a Glue crawler updates the Data Catalog afterward. Business analysts use an Amazon QuickSight dashboard backed by a SPICE dataset built from an Athena query. Analysts report that the dashboard shows yesterday's numbers even though the morning pipeline completed successfully. The team wants the dashboard to reflect the latest data automatically each day with minimal ongoing management. What should the team do?

A data engineering team runs ad hoc Athena queries against a data lake containing regulated financial data. A compliance requirement states that all Athena query result sets written to Amazon S3 must be encrypted with a customer-managed KMS key, and this encryption must be enforced consistently for every analyst regardless of individual query settings. What is the most effective way to satisfy this requirement?

A retail company is building a product catalog service for its e-commerce platform. Each product record has a different set of attributes depending on category (electronics have specifications like resolution and ports, while apparel have size and material). Products are looked up by product ID at very high request rates with single-digit millisecond latency requirements, and the schema evolves frequently as new categories are added. Which data store best fits these requirements?

A gaming company runs a multiplayer platform that must store player session state, such as current score, position, and inventory. The application requires single-digit millisecond read and write latency at any scale, and access is always by a known player ID. Traffic is highly unpredictable, spiking during tournaments to millions of requests per second. The data engineering team wants a fully managed data store that automatically scales without capacity planning and minimizes operational overhead. Which AWS data store best meets these requirements?

An IoT company ingests billions of sensor readings per day. Each reading has a device ID, timestamp, and metric value. The primary access pattern is to retrieve the most recent readings for a specific device by device ID, and single-digit millisecond read/write latency is required at massive scale. The data volume grows continuously and the team wants a fully managed, serverless data store that scales automatically without capacity planning for servers. Which data store best fits this use case?

A financial services company must produce an audit trail showing every individual GetObject and PutObject API call made against a specific S3 bucket that stores regulated customer data. Their existing CloudTrail trail already captures management events across all regions, but auditors report they cannot see object-level read and write activity for the bucket. What is the most appropriate way to meet this requirement?

A financial services company runs a data lake on Amazon S3 governed by AWS Lake Formation, with the Glue Data Catalog holding table definitions. Compliance auditors require a complete, queryable record of every user and role that has read specific sensitive tables, including which columns were accessed and when. The data engineering team needs to enable this auditing with the least custom development. Which approach satisfies the requirement?

A data engineering team runs a nightly AWS Glue ETL job that populates a reporting table. Recently, the job has intermittently failed, but the team only discovers the failures the next morning when business users complain about missing dashboard data. The team wants to be notified automatically within minutes whenever the Glue job enters a failed state, with the least operational overhead. Which approach should they implement?

A data engineering team runs a nightly pipeline that has three sequential stages: a Glue ingestion job, a Glue transform job, and a Redshift COPY load. Each stage currently has its own CloudWatch alarm that sends an SNS notification on failure. On-call engineers complain that a single upstream failure produces a cascade of three separate pages, because when the ingestion job fails the downstream stages also alarm. The team wants a single actionable notification that fires only when the pipeline as a whole is in a failed state, while keeping the individual alarms for dashboards. What is the MOST operationally efficient way to achieve this?

A data engineering team runs a nightly Glue ETL pipeline orchestrated by AWS Step Functions. Business rules require that after the transformation step completes and Glue Data Quality checks pass, a data steward must manually review a summary report before the curated data is published to the production S3 prefix that feeds Athena and QuickSight. The team wants the workflow to pause and wait for the steward's explicit approval, then continue automatically, without polling or wasting compute. What is the MOST appropriate way to implement this pause-and-approve behavior?

A data engineering team runs a nightly AWS Glue ETL job that normally completes in about 25 minutes. Over the past two weeks, the job's runtime has gradually crept up to over 90 minutes, though it still succeeds. Leadership wants proactive alerting when future jobs deviate significantly from their historical baseline, without hard-coding a fixed threshold that must be manually adjusted as data volume grows. Which approach best meets this requirement?

A data engineering team runs a nightly ingestion pipeline. A custom application writes structured log lines to Amazon CloudWatch Logs, including a line formatted as "REJECTED_RECORDS count=<n>" whenever malformed records are skipped. The team wants to be automatically alerted whenever the number of rejected records exceeds 500 in any single run, without modifying the application or building a separate log-processing service. What is the most operationally efficient way to achieve this?

A media company needs to migrate 40 TB of archived video files from an on-premises NFS file server to Amazon S3. After the initial transfer, they must automatically sync any newly added or modified files every night with minimal engineering effort, and they require data integrity verification during each transfer. Which approach best meets these requirements?

A retail company is migrating a 2 TB Oracle database running on-premises to Amazon Aurora PostgreSQL. The database is actively used by production applications during business hours, and the business requires that the source database remain fully operational with minimal downtime during the migration. After the initial data copy, all new and changed records in the source must continue to be replicated to Aurora until the final cutover. Which AWS DMS approach meets these requirements?

A retail company stores order records in a DynamoDB table using OrderId as the partition key. Business analysts increasingly need to query orders by CustomerId and by OrderStatus, but these attributes are not part of the table's primary key. The queries must return results with low latency and must not require scanning the entire table. What is the MOST appropriate way to enable these access patterns?

A data engineering team has an existing library of custom Apache Spark applications written and tuned by their engineers, including several jobs that require specific open-source Spark configurations and third-party JAR dependencies. They want to run these jobs on a fully managed, auto-scaling service without provisioning or managing clusters, and they want to pay only for the resources consumed during job execution. Which service best meets these requirements with the least migration effort?

A data engineering team runs a nightly Spark job on Amazon EMR that joins a 2 TB fact table of transactions with a 40 MB reference table containing store metadata. The job is slow because Spark performs a shuffle-based sort-merge join, redistributing the massive fact table across the cluster over the network. The reference table easily fits in each executor's memory. What is the most effective change to accelerate this join with minimal code effort?

A data engineering team runs a nightly batch job that processes 8 TB of raw JSON logs stored in Amazon S3. The transformation logic is written in custom Apache Spark and Scala code that relies on several third-party JAR libraries and requires fine-grained tuning of Spark executor memory and shuffle configurations. The job runs once per day for about two hours and cost efficiency is a priority. Which approach best meets these requirements?

A data engineering team runs a nightly AWS Glue ETL job triggered by an Amazon EventBridge cron rule at 02:00 UTC. Due to a multi-hour AWS maintenance window in a downstream dependency, the source data was not available for three consecutive nights, so the job ran but produced empty output files that were written to the curated S3 zone. Analysts querying the data with Athena now see gaps and empty partitions. The team wants to prevent the pipeline from writing output when the expected upstream data has not landed, while keeping the automated schedule. Which approach BEST addresses this requirement?