AWS Certified Data Engineer Associate Practice Test (DEA-C01)

Athena partitions are stored as separate folders in Amazon S3. When a query's WHERE clause references the partition columns, Athena prunes the unrelated partitions and reads only the relevant files, which reduces the amount of data scanned and lowers cost. Filtering solely on event_time does not use partition pruning because that column is stored inside the files, not in the partition path. A LIMIT clause, ORDER BY, or a common table expression does not affect how much data is read from S3, so they provide no scan-cost benefit.

AWS Secrets Manager can create a managed secret for an Amazon Redshift cluster whose password is rotated automatically every 30 days. An AWS Glue connection can reference the secret's ARN, so the job continues to run without code changes; the only additional step is to grant the Glue job role permission to call secretsmanager:GetSecretValue. Systems Manager Parameter Store has no built-in rotation, encrypting environment variables with KMS rotates keys rather than credentials, and AWS Glue connections do not provide automatic credential rotation. Therefore the Secrets Manager approach is the only option that satisfies all stated requirements.

To keep every row from web_clicks regardless of whether matching product metadata exists, the query must preserve all rows from the left (click) table and add columns from the right (product) table when matches are found. A left outer join does exactly that, returning NULLs for columns from dim_products when no corresponding product_id exists. An inner join would drop unmatched click events, a right join would keep only rows where a product exists, and a full outer join would add extra rows from dim_products that have never been clicked-none of which satisfy the stated requirement.

Amazon Kinesis Data Streams provides a durable stream that can retain data for hours or days, allowing applications to reread any record for replay or recovery. By registering Amazon Kinesis Data Firehose as a stream consumer and setting the buffering limits to 1 MiB and 10 seconds, records are delivered to Amazon S3 in well under the 30-second requirement. A Firehose delivery stream that ingests data directly, an AWS IoT Core rule, or a self-managed Kafka cluster either lacks built-in replay capability or adds unnecessary operational burden, so they do not satisfy both requirements as effectively.

A Kinesis Data Stream in on-demand mode eliminates shard provisioning and automatically scales. Registering each application as an enhanced fan-out consumer gives every consumer its own 2 MB/s pipe per shard with average propagation latency of about 70 ms, supporting sub-second fraud detection. Attaching a Kinesis Data Firehose consumer writes the same records to Amazon S3 for the EMR job. The stream's default 24-hour retention enables replays. Firehose alone lacks low-latency fan-out, MSK entails cluster administration and higher cost, and DynamoDB Streams targets table-change capture, not high-volume clickstreams.

Amazon Kinesis Data Streams is purpose-built for high-throughput, low-latency ingestion. A stream shard provides single-digit-millisecond put and get latency, and the service can retain data for up to 365 days when the retention period is extended, so a 7-day requirement is easily met. Lambda can be configured as an event source, allowing records to be processed almost immediately after they are written, and the fully managed nature of the service eliminates cluster management while costs scale linearly with provisioned shard capacity.

Writing directly to Amazon S3 via Kinesis Data Firehose meets the retention goal but cannot provide millisecond-level reads or support multiple independent consumer applications in real time. Persisting each record in DynamoDB gives low-latency access, but costs rise rapidly for write-heavy workloads and the service is not optimized for sequential stream processing by multiple consumers. Amazon MSK offers Kafka-compatible streams with the required retention but introduces significant operational burden to manage brokers, scaling, and patching, which contradicts the low-overhead requirement.

Amazon MSK is a fully managed service that can be configured to use IAM access control. When this option is enabled, clients sign their requests with SigV4 by assuming an IAM role, so there are no static usernames or passwords to create, store, or rotate. Deploying Kafka on Amazon EC2-or using Amazon MSK without IAM access control-requires you to configure SASL/SCRAM or mutual TLS, store credentials or certificates (often in AWS Secrets Manager), and implement a rotation process, which contradicts the requirement to avoid managing passwords. Therefore, enabling IAM access control on an Amazon MSK cluster is the only solution that satisfies both the authentication mandate and the low-operations goal.

A service control policy (SCP) applied at the AWS Organizations level can evaluate every API request before it is allowed. By using the aws:RequestedRegion global condition key, the SCP can explicitly Deny any action requested in Regions other than eu-west-1 or eu-central-1. This prevents engineers-and even automated services-from creating S3 buckets, enabling cross-Region replication, or launching resources outside the approved EU Regions, fully satisfying data-sovereignty requirements.

Enabling S3 Object Lock only stops object deletion or modification; it does not stop data being stored in other Regions. Requiring SSE-KMS with EU-based keys encrypts data but does not restrict its geographic location. Amazon Macie with Security Hub can detect non-compliant storage locations, but it is reactive and cannot block operations. Therefore, the SCP with a Region deny condition is the most effective preventive control.

Landing the raw JSON objects in Amazon S3 keeps storage costs lower than storing them inside the data warehouse. When an AWS Glue crawler catalogs the objects, it applies schema-on-read semantics and automatically adds any new or missing attributes it discovers. Creating an external schema in Amazon Redshift that points to the Glue Data Catalog lets analysts query the data through Redshift Spectrum. Because Spectrum consults the Data Catalog at query time, new attributes become available to analysts immediately-no ALTER TABLE commands and no downtime.

Why the other designs fall short:

• Streaming the JSON directly into a Redshift table that uses the SUPER data type does allow schemaless ingestion without DDL, but all raw data is stored in Redshift Managed Storage, which is more expensive than S3, so it does not satisfy the cost constraint.
• Using AWS DMS to load the events and running nightly ALTER TABLE commands adds operational overhead and locks the table during DDL, interrupting queries.
• Persisting the events in an Amazon RDS PostgreSQL database and querying through Redshift federated queries duplicates data in a full relational database, incurs higher storage and compute costs, and still doesn't provide automatic schema evolution for semi-structured JSON columns.

An internal Application Load Balancer (ALB) can terminate TLS and perform mutual TLS verification. The team uploads a CA bundle that trusts the partners' intermediate CAs to an ELB trust store stored in Amazon S3, attaches the trust store to an HTTPS listener in mutual TLS verify mode, and targets the ALB at the ingestion service. The ALB authenticates client certificates during the TLS handshake and blocks untrusted connections, so no backend changes are needed. Because the ALB is internal and secured by VPC security groups, the endpoint is accessible only from the company VPC. The other options either rely on presigned URLs, IAM Signature Version 4, or OAuth tokens-which are key- or token-based, not certificate-based-or attempt to use mutual TLS with a private API Gateway REST API, which is not supported.

Amazon QuickSight is a fully managed, serverless BI service. It can connect to Amazon S3 data through an Athena data source, and the in-memory SPICE engine can be scheduled for hourly refreshes while still allowing analysts to perform fast, interactive filtering. Dashboards can be shared with users federated by Amazon Cognito without the team managing any infrastructure. AWS Glue DataBrew is designed for data preparation and profiling, not for hosting interactive dashboards. Creating an Amazon Redshift cluster or an Amazon EMR-based Presto query layer would satisfy the visualization need only after the team provisions, secures, and scales additional infrastructure, which contradicts the low-overhead requirement.

Materialized views that reference external schemas cannot be configured with AUTO REFRESH. They remain unchanged until explicitly refreshed. The least-cost approach is to leave the data in Amazon S3 and schedule the REFRESH MATERIALIZED VIEW command-using Amazon EventBridge, the Redshift scheduler API, or another orchestrator-to run immediately after the nightly file replacement. Loading the external table into Redshift or switching to a late-binding view would either add storage cost or reduce performance. ALTER MATERIALIZED VIEW … AUTO REFRESH YES is unsupported for external schemas, so it would fail.

For Athena to run a query, Lake Formation must be able to read metadata in the Glue Data Catalog. The service looks in the default database, so the querying principal needs at least DESCRIBE on that database. Without it, Lake Formation blocks the request and Athena reports "Access Denied." Granting DESCRIBE on the default database satisfies the metadata check; DATA_LOCATION_ACCESS is only required for creating resources, and adding direct S3 permissions or ALTER on the table would bypass governance or still fail the metadata lookup.

Converting the most recent 90-day slice of the .txt logs to a columnar, compressed format such as Parquet sharply reduces the amount of data that Amazon Athena needs to scan, lowering both latency and per-query cost. A serverless AWS Glue job can write the converted data to a new S3 prefix, so the original uncompressed text files remain untouched. Athena can then query only the Parquet objects without requiring an always-on cluster. Pointing Athena directly at the .txt files still incurs high scan costs even with partitioning, while Amazon Redshift or Amazon RDS would require provisioning and managing database instances, increasing operational overhead and cost.

CPU throttling occurs when a container exhausts the CPU limit defined in its pod specification. For Spark workloads this causes tasks to pause even though idle CPU is still available on the node. Removing the CPU limit (or increasing it well above the request) lets the executor containers borrow spare vCPUs and eliminates cgroup throttling, reducing job runtime without needing additional EC2 capacity. Increasing disk throughput, enabling dynamic allocation, or migrating to a different service may improve performance in some cases but do not directly address the throttling caused by the Kubernetes CPU limit in this scenario.

Create an Amazon Macie sensitive-data discovery job for the lake buckets. Configure an Amazon EventBridge rule that triggers an AWS Step Functions state machine whenever Macie publishes a sensitive-data finding. In the workflow, use an AWS SDK task to call the Lake Formation AddLFTagsToResource API and attach an LF-tag such as Classification=Sensitive to the object (or its corresponding catalog columns). Lake Formation tag-based access-control policies then deny the analyst principal and allow the governance principal for resources tagged Classification=Sensitive. This uses only managed integrations (Macie, EventBridge, Step Functions, Lake Formation) and requires minimal code-no bespoke parsing beyond the workflow definition.

The other options either rely on custom parsing inside Lambda, do not use Macie for detection, or cannot apply Lake Formation permissions automatically.

The correct query uses multiple qualifiers in the appropriate order: WHERE filters rows that fall within the last 7 days, GROUP BY aggregates by page, HAVING keeps only pages whose aggregated count of distinct session_id values meets the threshold, ORDER BY sorts on the derived average, and LIMIT returns the top five results. Aggregated conditions cannot appear in a WHERE clause, and non-grouped columns such as event_time cannot appear in HAVING without aggregation. Ordering by the session count would not satisfy the requirement to sort by the highest average load_time_ms.

Using DynamoDB Streams with an AWS Lambda function satisfies all stated requirements. Streams delivers every write in near-real time. Lambda can be configured as a native event source and automatically scales with the number of stream shards, giving at-least-once processing without server management. The function can batch the records and invoke an Amazon Kinesis Data Firehose delivery stream, which efficiently loads the data into Amazon S3. Glue streaming jobs cannot consume DynamoDB Streams, DMS introduces additional infrastructure and higher latencies, and frequent full-table exports are inefficient and would miss the one-minute latency target.

Switching the table and any global secondary indexes to on-demand capacity mode removes the need to provision or scale capacity manually. DynamoDB automatically accommodates sudden traffic spikes (up to tens of thousands of requests per second) and charges only for the read and write requests actually made, eliminating throttling during peaks and cost during idle periods. Simply raising provisioned capacity or tweaking auto-scaling still incurs idle cost and requires tuning. DAX improves read latency but does not increase write throughput. DynamoDB Streams with Lambda off-loads data elsewhere but leaves the original write traffic and throttling issues unresolved.

Amazon S3 can emit an event for every new object. Publishing that event to Amazon EventBridge allows a rule to start an AWS Glue job only when a file is written. The Glue job can issue a COPY command that loads the single object into Amazon Redshift, giving near-real-time latency without running servers between arrivals. A cron-based Glue schedule polls rather than reacts to events and could miss the five-minute window or waste resources. AWS DMS cannot use S3 as a change-data-capture source for Redshift in this scenario, and Kinesis Data Firehose expects streaming records, not entire objects already in S3, so it does not satisfy the event-driven batch requirement.