AWS Certified Data Engineer - Associate (DEA-C01)

Incorrect. Lake Formation data filters are used to scope permissions (row/column filtering) on Data Catalog tables, not to register S3 prefixes as data locations. Registering data locations is a separate Lake Formation action (bucket or prefix registration). While prefixes can be registered, the mechanism is not “using data filters.” This option conflates two different Lake Formation features.

Incorrect. You do not attach a Lake Formation data filter to an IAM role. Instead, you create a data filter in Lake Formation and then grant Lake Formation permissions to an IAM principal (role/user) referencing that filter. IAM policies can allow/deny API actions, but the row/partition restriction is enforced by Lake Formation permission grants, not by attaching filters to IAM roles.

Incorrect. Creating separate buckets per Region violates the requirement to avoid new buckets and data duplication. Even if implemented with S3 prefix/bucket IAM policies, it provides coarse object-level access control rather than query-time row-level governance. It also increases operational overhead (more buckets, replication/ingestion changes, more policies) compared to centralized Lake Formation governance.

GZIP can reduce storage and transfer size, but for Spectrum the key is scan efficiency and parallelism. GZIP-compressed text files are typically non-splittable, limiting parallel reads and increasing latency. Also, 1–5 GB per file is often too large for optimal parallelism and recovery. Better is columnar Parquet/ORC with splittable compression (e.g., Snappy) and appropriately sized files.

Incorrect. Many tiny files (<10 KB) create a small-file problem: high S3 request overhead, excessive metadata operations, and inefficient task scheduling. Spectrum performs better with fewer, larger files because it reduces per-file overhead and improves throughput. Parallelism is important, but it should be achieved with reasonably sized files (often 100s of MB) and partitioning, not tiny objects.

Incorrect. Non-splittable formats/codecs (e.g., CSV with GZIP) generally hurt Spectrum performance because they limit parallel reads and prevent efficient skipping of irrelevant data. Even if compression reduces bytes stored, Spectrum may need to read and decompress large portions of files to evaluate predicates. Columnar, splittable formats (Parquet/ORC with Snappy/ZSTD) are preferred for fastest queries.

Incorrect. Lack of date partitioning in the Glue Data Catalog impacts query performance and cost (Athena scans more data) but does not inherently produce an authorization failure. You might see long runtimes, high scanned bytes, or timeouts, but “Insufficient permissions” points to IAM/S3/KMS policy issues rather than table design or partition strategy.

Incorrect. SPICE vs Direct Query changes whether QuickSight imports data into SPICE or queries live through Athena. However, a refresh (SPICE ingestion) still needs to run Athena queries and read/write S3 objects (including results). Therefore, SPICE itself is not a root cause of permissions errors; missing S3/KMS permissions would break either mode depending on the operation.

Incorrect. Using a non-default Athena workgroup (wg-prod) is not inherently a permissions problem. Workgroups can enforce settings (output location, encryption, engine version) and can be governed by IAM permissions (athena:StartQueryExecution on that workgroup). But the option states only that it’s set to wg-prod rather than primary; that alone does not explain an “Insufficient permissions” error without additional constraints.

Transitioning data to S3 Glacier Deep Archive after 1 day would make the data unsuitable for an actively queried Athena dashboard. Athena cannot directly analyze data in Glacier Deep Archive without first restoring the objects, which introduces major delays and operational complexity. That directly conflicts with the requirement to refresh the dashboard every 15 minutes. This option is an archival cost strategy, not an analytics query cost optimization strategy.

Athena query result reuse can reduce cost only when the same query text is rerun and Athena can safely reuse a previous result set. In dashboard environments, queries often vary by time window, filters, parameters, or generated SQL text, so reuse is not guaranteed to provide broad savings. It also does not reduce the cost of the first execution of each query pattern, whereas Parquet lowers scan costs for all queries. Therefore, result reuse is helpful in some cases but is not the best overall solution for reducing Athena costs on a very large dataset.

Amazon ElastiCache would introduce new infrastructure, which the question explicitly says to avoid. It would also add operational overhead for provisioning, scaling, monitoring, cache invalidation, and integration with the dashboard application. While caching can reduce repeated query load, it is not the least operationally intensive option in this scenario. Native data format optimization in S3 with Parquet is simpler and more aligned with Athena best practices.

Amazon Kinesis Data Streams is a fully managed streaming service with elastic scaling (via shard management or on-demand mode), but it is not Kafka-compatible. Migrating from Kafka/Debezium would require refactoring producers/consumers to Kinesis APIs and rethinking offsets/consumer groups and partitioning semantics. It can meet throughput needs, but it violates the requirement to preserve Kafka APIs under a replatform (not refactor) strategy.

Amazon MSK provisioned cluster preserves Kafka APIs and is a common replatform target for lift-and-shift Kafka migrations. However, it requires more operational management than serverless: you must choose broker instance types/count, plan capacity for bursts, manage scaling operations, and handle partition/broker balancing considerations. It is managed (patching, replacements), but it is not the least-overhead option when automatic scaling is explicitly required.

Amazon Kinesis Data Firehose is designed for delivery to destinations (S3, Redshift, OpenSearch, Splunk) with optional buffering and transformation, not as a general-purpose Kafka-compatible streaming platform. It does not provide Kafka broker semantics, topics/partitions, or consumer group coordination. Using Firehose would require redesigning the CDC pipeline and consumer behavior, making it a refactor and not suitable for preserving Kafka APIs.

AWS Data Exchange is a service for finding, subscribing to, and using third-party datasets in AWS. It does not provide workflow orchestration, scheduling, or DAG execution. Even though data pipelines may consume external datasets, Data Exchange is not an orchestrator and cannot satisfy requirements like running the same open-source DAG definitions on premises and in a managed cloud service.

Amazon SWF is an AWS-native workflow coordination service. While it can orchestrate tasks, it is not Apache Airflow and does not use Airflow DAG definitions. Using SWF would require redesigning the workflow logic and application integration, increasing vendor lock-in. It also does not provide a “fully managed equivalent” of an on-prem open-source DAG engine with shared DAG code.

AWS Glue is a serverless data integration (ETL/ELT) service with crawlers, jobs, and Glue Workflows. However, Glue is not Apache Airflow and does not allow running the same Airflow DAGs on premises and in AWS without rewriting. Glue also cannot be deployed as the same open-source engine on an on-prem Kubernetes cluster, so it fails the portability and lock-in requirements.

Incorrect. Manually generating certificates and rotating them with scripts and cron jobs creates unnecessary operational complexity and increases the risk of outages during renewal events. This approach also requires the team to handle validation, secure storage, deployment timing, and rollback logic themselves. It does not take advantage of ACM’s native integration with CloudFront for managed renewal and seamless deployment. For an exam question emphasizing least operational overhead and zero-downtime renewals, a manual process is clearly inferior to ACM.

Incorrect. AWS Secrets Manager is intended for storing and rotating secrets such as database credentials, API keys, and other application secrets, not as the primary certificate lifecycle manager for CloudFront viewer certificates. CloudFront does not natively source its public custom-domain certificate from Secrets Manager, so this design would still require custom automation to import and attach certificates. That adds complexity, maintenance burden, and more opportunities for deployment errors or downtime. In contrast, ACM already provides direct certificate issuance, renewal, and CloudFront integration for this exact use case.

Incorrect. Amazon ECS Service Connect is a service-to-service networking feature for ECS workloads and can help with internal connectivity patterns, but it is unrelated to managing public certificates for CloudFront or API Gateway custom domains. The architecture in the question is based on API Gateway and CloudFront, not ECS-hosted services requiring mesh-style connectivity. Service Connect does not provision or rotate the public edge certificate used by clients connecting to CloudFront. Therefore, it does not address the stated TLS, renewal, or multi-Region certificate management requirements.

ALL distribution replicates the entire table to every node, eliminating redistribution for joins. However, it is intended for small dimension/lookup tables. Replicating a table that will grow to ~3 TB across a 4-node cluster would massively increase storage consumption, load times, and vacuum/maintenance overhead, and can negatively impact performance. This directly conflicts with the requirement to minimize ongoing maintenance as the table scales.

EVEN distribution spreads rows round-robin across slices, which is a reasonable default when there is no good distkey and helps avoid skew. However, it is a fixed, manual choice. As the table grows and query patterns evolve, EVEN may not be optimal compared to Redshift-managed decisions (e.g., choosing ALL for small tables or adjusting strategies). It does not best satisfy the “minimize ongoing maintenance” requirement.

KEY distribution colocates rows with the same distkey value on the same node, which can greatly improve join performance when tables share the same join key and the key has high cardinality with even distribution. The prompt explicitly says there is no stable join column or uniformly high-cardinality field suitable for a distribution key, making KEY likely to cause data skew, uneven node utilization, and future rework—opposite of minimizing maintenance.

AWS Config rules evaluate the configuration state of AWS resources (e.g., bucket policy, public access settings) and report compliance over time. They do not capture individual S3 object API calls like GetObject/PutObject, nor do they provide the exact username for each denied request. Config is not designed for near-real-time per-access violation alerting, so it cannot meet the under-3-minute, per-user requirement.

Amazon CloudWatch metrics for S3 (including request metrics) are primarily aggregated counts/latency and do not provide per-request details such as the exact username or the specific AccessDenied event context. Even with S3 request metrics enabled, you cannot reliably identify which specific user violated a policy on a given prefix. This fails the attribution requirement.

S3 server access logs can show object-level requests, but delivery is not near-real-time; logs are typically delivered with significant delay (often tens of minutes to hours). Additionally, operationalizing them into CloudWatch Logs adds ingestion/processing latency and complexity. While they can include requester information, the timing requirement (under 3 minutes) is the main reason this option is not suitable.

A bucket policy could block access, but the prompt already states the role has s3:ListBucket and s3:GetObject and the failure occurs specifically when reading SSE-KMS encrypted objects. In many real cases, the same role can list and attempt to read, but decryption fails due to KMS. Unless the question mentions an explicit bucket policy deny or cross-account restrictions, KMS permission gaps are the more likely cause.

Athena engine version issues typically cause query syntax/feature incompatibilities or performance differences, not an S3 AccessDenied error when fetching objects. AccessDenied is an authorization failure from S3/KMS, not a compute engine mismatch. On exams, treat “outdated engine version” as a distractor unless the symptom is unsupported SQL functions, Parquet reader bugs, or documented engine-specific behavior.

A Region mismatch does not manifest as AccessDenied due to latency. S3 is a regional service, and accessing a bucket in another Region is still possible via the global endpoint/redirect behavior, but it would not cause an authorization error. Athena queries run in a specific Region and generally require the data sources and Glue catalog in the same Region for typical setups; however, the error described (AccessDenied on object read) points to permissions, not latency.

AWS Step Functions can orchestrate ETL steps, but it is a general-purpose workflow service rather than the most natural feature for a Glue-centric pipeline. In this scenario, the pipeline already depends on Glue-native capabilities such as crawling the JDBC source and running the ETL job, so adding Step Functions introduces an extra orchestration service that is not necessary. While Step Functions is serverless and low overhead, the exam-oriented best answer is the Glue-native orchestration feature when the workflow is primarily crawler-to-job sequencing. Therefore, Step Functions is viable but not the most cost-effective or direct fit among the listed choices.

AWS Glue Studio is a visual development interface for creating, editing, and monitoring Glue ETL jobs. It helps data engineers design transformations and generate Glue job code, but it is not the primary orchestration feature for chaining crawlers, jobs, and scheduled dependencies end to end. The question asks for the service or feature that will orchestrate the pipeline on a daily schedule with minimal overhead, which points to Glue workflows rather than the Studio UI. Choosing Glue Studio confuses job authoring with workflow orchestration.

Amazon MWAA provides managed Apache Airflow for complex DAG-based orchestration across many systems, but it is usually excessive for a single daily Glue-oriented ETL pipeline. MWAA requires a continuously running Airflow environment, which increases both cost and operational complexity compared with Glue workflows. The question emphasizes minimal managed service overhead and cost control, making MWAA a poor fit. It is better suited for organizations that already standardize on Airflow or need extensive custom orchestration beyond Glue-native capabilities.

Incorrect. While partitioning by reading_date is good, using Amazon Redshift adds cost and operational overhead (loading data from S3, maintaining tables, vacuum/analyze, or paying for Redshift Serverless). For weekly ad hoc KPIs, Athena on partitioned Parquet in S3 is usually cheaper and simpler. Redshift is better when you need consistently high concurrency/latency or complex warehouse workloads.

Incorrect. Partitioning by ingestion_date does not match the filter on reading_date, so Spark jobs may still scan large amounts of data unless additional indexing/partitioning is done. EMR also introduces cluster management and compute costs that are typically not justified for weekly ad hoc SQL KPIs. Spark is appropriate for heavy transformations/ML, not the most cost-effective option for simple date-filtered KPI queries.

Incorrect. Aurora is an OLTP relational database and is not designed for large-scale analytical scans over growing Parquet datasets in S3. You would need to ETL and load data into Aurora tables, increasing cost and complexity, and queries over hundreds of days of data would not be as cost-effective as scanning partition-pruned Parquet with Athena. Aurora also has ongoing instance/storage costs.

Incorrect. SageMaker Data Wrangler can generate data quality/insights reports, but it is oriented toward interactive data preparation and ML workflows rather than enforcing hard fail/pass gates inside an AWS Glue ETL job. It also adds operational components (flows, processing jobs, scheduling) outside the existing Glue pipeline, which conflicts with the “least operational overhead” requirement and the need to enforce checks at the Glue transform stage.

Incorrect. Custom SQL/PySpark transforms (null checks per column plus LEFT ANTI JOIN logic for referential integrity) can satisfy the functional requirements, but they increase operational overhead: more code to write, test, and maintain; more risk of performance issues at scale; and more effort to standardize reporting/metrics. The question explicitly asks for the least operational overhead, which favors managed Glue Data Quality rules over bespoke validation logic.

Incorrect. While Data Wrangler with custom Python can implement both null checks and referential integrity, it introduces additional orchestration and runtime management compared with a Glue-native solution. It also shifts the solution toward SageMaker processing jobs/flows rather than embedding checks directly in the existing Glue ETL transform stage. This is typically more operationally complex than using Evaluate Data Quality within Glue.

Incorrect. Increasing shards raises aggregate capacity, but it does not inherently fix partition-key skew. If merchantId remains the partition key, the flash-sale merchant’s records will still hash to the same shard (or small subset after resharding), keeping those shards hot and throttled while other shards remain underutilized.

Incorrect. Throttling producers to 1,000 records/s reduces ingestion throughput and violates the requirement to keep the same overall throughput. The stream already has enough aggregate capacity; the issue is uneven distribution across shards, not insufficient total capacity.

Incorrect. Reducing record size can help if the shard is hitting the 1 MB/s limit, but hot shards can also be constrained by the 1,000 records/s per-shard limit. With 2 KB records, the dominant merchant can exceed the per-shard record-rate limit even though MB/s is low. The root cause is partition-key skew, not record size.

Incorrect. Creating separate S3 buckets per team/application violates the requirement to continue using the same S3 buckets and to avoid duplicating data. Even if bucket policies restrict data access, this does not directly isolate Athena query execution resources, saved queries, or query history. S3-level isolation is a data access control mechanism, not an Athena operational isolation mechanism.

Incorrect. Creating separate IAM roles can help segregate permissions, but roles alone do not provide the required isolation of Athena query history and saved queries. Multiple roles could still use the same default workgroup unless explicitly restricted, and the question specifically calls for enforcement using resource tag conditions and isolation of workgroup-scoped artifacts, which is best achieved with Athena workgroups.

Incorrect. AWS Glue Data Catalog resource policies (or Lake Formation permissions) can control who can access which databases/tables, but they do not isolate Athena query execution resources, costs, saved queries, or query history. This option addresses data governance at the metadata/table level, not the operational isolation of Athena usage required by the question.

High operational overhead and unnecessary complexity. MWAA requires managing an Airflow environment (workers, schedulers, plugins, dependencies). Using both Lambda for correlation and Step Functions for orchestration duplicates orchestration responsibilities. Also, correlating 100+ GB with Lambda is not ideal due to runtime/memory limits and distributed join needs; Glue/Spark is a better fit for large-scale joins.

AppFlow + Glue is a strong ingestion/ETL approach, but adding MWAA increases operational overhead versus Step Functions for a once-nightly pipeline. MWAA still needs environment management, scaling, and DAG maintenance. Unless the company already standardizes on Airflow or needs complex DAG patterns/operators, Step Functions is typically the lower-ops orchestration choice.

Over-engineered for the requirement. Kinesis Data Streams and Managed Service for Apache Flink are designed for real-time streaming ingestion and stream processing. The question requires a nightly batch run, not continuous correlation. Additionally, pulling from PostgreSQL into Kinesis is non-trivial and often requires custom producers or CDC tooling, increasing operational burden compared to Glue JDBC extraction.

AWS Glue is a serverless ETL service (Spark-based) with a Data Catalog and managed job execution, great for batch ETL and schema discovery. However, it does not provide managed Apache HBase or Apache Oozie, and it is not designed to lift-and-shift a full Hadoop ecosystem. The strict requirement to keep Spark, Flink, HBase, and Oozie together makes Glue an incomplete match despite its serverless appeal.

AWS Lambda is serverless compute for event-driven functions with short execution durations and limited memory/runtime constraints. It is not suitable for running distributed big data frameworks like Spark/Flink, nor can it host HBase or Oozie. While Lambda can orchestrate or trigger ETL steps, it cannot replace a Hadoop/Spark/Flink execution environment for multi-petabyte daily processing with sub-90-second ETL runs.

Amazon Redshift is a managed data warehouse optimized for SQL analytics and can perform ELT patterns (e.g., COPY, SQL transforms) and integrate with tools for ingestion. It does not run Apache Spark, Flink, HBase, or Oozie workflows directly. Redshift could be a target store for curated data, but it is not the ETL service that maintains the existing Hadoop ecosystem frameworks and operational model.

This option places data quality and transformation logic in AWS Glue DataBrew, which is more oriented toward interactive, visual data preparation than a production-style scheduled ETL pipeline loading into RDS. DataBrew can perform profiling and transformations, but AWS Glue Data Quality is the more directly relevant service for formal rule-based quality checks in Glue-centric ETL workflows. The option is attractive because it stores quality results in S3, which is low cost, but it sacrifices the better service fit for the required data quality implementation. For an exam question focused on scheduled ETL plus explicit quality rules, Glue ETL with Glue Data Quality is the stronger answer pattern.

This option fails a core requirement because it stores the processed dataset in S3 instead of loading it into a single Amazon RDS for MySQL instance for future SQL queries. While S3 is cost-effective for retaining logs and intermediate outputs, it does not satisfy the stated destination requirement for the curated dataset. It also uses DataBrew for quality checks instead of the more appropriate Glue Data Quality capability. Because it misses the required target system, it cannot be the correct answer.

This option uses DataBrew for both transformations and quality checks and then stores both curated data and quality results in Amazon RDS for MySQL. That design is not cost-effective because detailed quality-check logs are better retained in low-cost object storage such as Amazon S3 rather than in a relational database with higher storage and backup costs. It also relies on DataBrew instead of Glue Data Quality for the rule-based validation requirement. As a result, it is weaker both on service fit and on storage-cost optimization.

Incorrect. Hive-style bucketing (rebucketing files by a commonly filtered column) can help certain query patterns (e.g., joins/aggregations) by reducing shuffle and improving parallelism, but it does not address the core issue: Athena’s planning overhead from millions of partitions in the Glue Data Catalog. Bucketing changes file layout within partitions, not the number of partitions or the need to resolve partition metadata.

Incorrect. Converting to Parquet is a strong optimization for Athena because it is columnar, supports predicate pushdown, and reduces scanned bytes, improving runtime and cost. However, it does not inherently reduce the number of partitions or the need for Athena to resolve partition metadata during planning. If planning is the bottleneck (not scan), Parquet alone will not remove it.

Incorrect. Combining many small objects into larger objects reduces S3 request overhead and can improve Athena runtime by reducing the number of splits and file-open operations. But it does not reduce Glue partition metadata volume or the partition enumeration that drives planning time. It’s a good optimization for the “small files problem,” not for “too many partitions in the catalog.”