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

Partition projection lets Athena derive partition values from object paths at run time, so Athena does not need to store a separate metadata row for every partition. By enabling partition projection on the orders table and defining the year, month, and day ranges, new partitions become instantly queryable without invoking Glue crawlers or MSCK REPAIR TABLE. This reduces latency and catalog overhead. Scheduling a Glue crawler or running MSCK REPAIR TABLE would achieve correctness but adds cost and delay. Using an unpartitioned CTAS table removes the need for partition updates but would scan far more data and raise query costs.

Amazon RDS MySQL read replicas use asynchronous replication to offload read traffic from the primary instance. Placing replicas in separate AZs increases availability for reads, and the application can begin directing SELECT traffic to the replicas with minimal code changes. Multi-AZ deployments improve failover resilience but keep all reads on the primary, so they do not relieve read pressure. Migrating to Aurora or adding ElastiCache introduces higher cost and greater change complexity compared with simply adding read replicas.

Amazon DynamoDB offers on-demand capacity or DynamoDB Serverless, which automatically scales to millions of requests per second without capacity planning. It consistently delivers single-digit millisecond latency for key-value access and supports a TTL attribute that automatically removes items after a specified timestamp, satisfying the 24-hour retention goal. Aurora MySQL Serverless cannot sustain millisecond retrieval under extreme write bursts and introduces connection and cold-start latency. Amazon S3 with Athena provides virtually unlimited storage but has query latencies in seconds and no native per-item TTL. Amazon Redshift is optimized for analytic, not per-row, millisecond queries and requires manual cleanup or VACUUM operations. Therefore, DynamoDB is the most suitable choice.

Sending S3 ObjectCreated events to EventBridge and using the event to start a Step Functions state machine provides a fully serverless, event-driven workflow. Step Functions can invoke a Glue job, wait for completion, and then use the Redshift Data API to run a COPY command. Built-in retry and error-handling policies satisfy the resiliency requirement with no servers to manage.

Airflow on EC2 introduces cluster management and relies on polling, not events. A CloudWatch scheduled rule is time-based rather than event-driven and requires custom logic for retries. Kinesis Data Firehose cannot accept S3 objects as a source, so it cannot react to new S3 files. Therefore, only the Step Functions approach aligns with the constraints.

S3DistCp is an Amazon EMR utility built on Apache DistCp that runs as a step on the cluster. It launches multiple mapper tasks that copy objects in parallel, optionally combines small files, and uses the cluster's network bandwidth instead of a single node. This approach delivers the highest throughput while reducing the number of GET requests. Running the command from the master node (aws s3 cp) would be single-threaded, DataSync adds an external service and cannot write directly into HDFS, and mounting the bucket with s3fs provides no parallelism and incurs high per-object overhead.

Lake Formation governed tables add ACID compliance, automatic compaction, and concurrency controls on S3 objects. When a dataset is registered as a governed table, Lake Formation can apply fine-grained permissions-including row and column filters-through LF tag-based access control. Analysts receive SELECT on the governed table, and update jobs can use the transactional writes that governed tables provide. Merely using bucket policies, Athena workgroups, or object ACLs does not give row-level security or ACID semantics. Importing the data into Amazon Redshift is unnecessary overhead and Redshift sharing via Lake Formation does not cover S3 data updates.

Creating a materialized view inside Redshift stores the pre-computed join result locally. Because materialized views that reference federated tables can't use auto-refresh, schedule REFRESH MATERIALIZED VIEW every 5 minutes (for example, with an EventBridge rule or Redshift Scheduler). The dashboard then queries the materialized view, delivering sub-second latency while reducing Aurora workload to one refresh per 5-minute window. Standard views or the result cache would still hit Aurora each minute, COPY/DMS loads replicate data unnecessarily, and a Spectrum approach adds S3 costs without reducing Aurora load.

An Amazon S3 event-driven, incremental crawler updates only the partitions that correspond to the newly arrived objects. When an object is written to a monitored prefix, S3 publishes an event that triggers the crawler through EventBridge. The crawler quickly adds the new year/month/day partition to the AWS Glue Data Catalog, making the data immediately queryable from Athena. A frequent schedule works but still polls on a fixed interval. Partition projection removes the dependency on the Data Catalog but requires additional table-property configuration and is not necessary when a catalog is already in use. A custom Lambda or Step Functions workflow achieves the same goal but adds more code and maintenance overhead compared with the built-in event-driven crawler capability.

Amazon Redshift supports native streaming ingestion from Amazon Kinesis Data Streams. By creating a materialized view that directly references the stream, Redshift continuously and automatically pulls new records without staging them in Amazon S3 or relying on additional services. The view can be set to AUTO REFRESH so data becomes query-able within seconds, satisfying the one-minute SLA with very little operational overhead.

Sending the stream through Kinesis Data Firehose or Glue adds an extra service and writes first to Amazon S3, increasing latency and management effort. A Lambda function that batches INSERT statements introduces custom code, scaling challenges, and higher maintenance compared with the managed Redshift feature.

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.

Creating a materialized view lets Amazon Redshift store the pre-computed, aggregated result set on disk. When analysts query the materialized view, Redshift returns the stored result almost immediately instead of re-scanning and joining the 5-TB fact table, yielding a large runtime reduction. Scheduling an automatic refresh immediately after the nightly data load maintains accuracy while requiring minimal ongoing management.

Changing the fact table to an ALL distribution style would duplicate terabytes of data across every node, greatly increasing storage space and load time. Concurrency scaling adds transient clusters to improve throughput when many queries run simultaneously, but it seldom reduces the elapsed time of a single long query. Adjusting WLM queues or enabling short query acceleration allocates resources differently but will not eliminate the heavy table scan and aggregation work that dominates the query's runtime.

Using an AWSIAMManagedPolicy resource lets you define a separate, reusable policy document that can be limited to the specific S3 bucket. Attaching that managed policy to the role through the ManagedPolicyArns property keeps the role resource small while still granting only s3:GetObject permission on the required bucket, satisfying least-privilege. Relying on the AWS-managed AmazonS3ReadOnlyAccess policy would grant read access to every bucket, which violates least-privilege. An AWSIAMPolicy inline resource or an inline Policies block within the role would work, but they place the entire JSON policy in the role definition, contradicting the requirement to avoid a large inline block. An instance profile is used for EC2 resources and would not attach permissions to an AWS Glue job.

An AWS Glue streaming ETL job is serverless, so there are no clusters to provision or manage. It can read directly from Kinesis Data Streams, perform Spark-based transformations, and write Parquet files to Amazon S3 with micro-batch windows as small as 1 s. Glue streaming jobs support Auto Scaling, so they handle spikes in event volume without manual intervention.

An Amazon EMR cluster running Spark Structured Streaming could work, but the company would still have to size, monitor, and scale the cluster, increasing operational overhead.

An AWS Lambda consumer risks hitting concurrency limits and would struggle to serialize each record to Parquet efficiently at 50 000 TPS.

Kinesis Data Firehose can deliver to S3 and convert formats, but its buffering interval (minimum 60 s) prevents sub-second latency. Therefore, the Glue streaming ETL job best satisfies latency, scalability, and management requirements.

An Amazon EventBridge rule that listens for the s3:ObjectCreated event can start an AWS Glue job whenever the nightly file lands in the bucket, providing a serverless, pay-as-you-go ingestion workflow. Enabling AWS Glue job bookmarks causes the job to record which S3 objects have already been processed, so a retry does not reload the same file. The combination removes the need to manage EC2 instances and is less expensive and simpler than operating Amazon EMR, AWS Data Pipeline, or streaming services for a once-per-day batch workload.

Sending S3 event notifications to an Amazon SQS FIFO queue preserves the order in which PutObject events occur. A Lambda function configured with the queue as an event source polls the messages, ensuring the function is invoked in order with exactly one message at a time (batch size 1). If the function returns an error, the message remains in the queue for retry based on the queue's visibility timeout, eliminating the need for custom retry logic. EventBridge or direct Lambda targets cannot guarantee ordered processing, and using replication or Step Functions adds unnecessary complexity.

Amazon EventBridge publishes built-in "Glue Job State Change" events that include the job's final state. Creating a second EventBridge rule that filters for the SUCCEEDED and FAILED states and targets an Amazon SNS topic requires no new code. SNS natively supports multiple subscription protocols-such as email and SMS-so on-call staff can receive alerts through their preferred channel. The other options either require custom libraries, extra polling infrastructure, or do not natively provide both email and SMS delivery, so they introduce additional operational overhead and complexity.

Creating a Glue crawler to catalog the new log files and letting analysts query them with Amazon Athena is the only option that is fully serverless, requires no cluster or domain management, and becomes available for SQL queries shortly after the data lands in S3. Athena reads directly from S3, and the Glue Data Catalog provides the schema that analysts can join to their reference table, which is already cataloged. An EMR cluster with Presto/Trino would work functionally but introduces nodes to provision, scale, and patch. Streaming the logs to Amazon OpenSearch Service creates an OpenSearch domain that the team must size and maintain, and its query language differs from standard SQL. CloudWatch Logs Insights cannot natively query objects that are already in S3; the logs would first have to be pushed to CloudWatch Logs, adding delay and extra steps.

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.

Using Amazon API Gateway with an attached usage plan lets the company require an API key for every request. A Lambda function behind the API runs simple SELECT statements by calling the Amazon Redshift Data API, formats the result as JSON, and returns it. All components are serverless, no network endpoints for Redshift are exposed, and API Gateway handles throttling and key management. Directly exposing the Redshift Data API would require the partner to sign requests with AWS credentials. Running a container service behind an Application Load Balancer introduces additional infrastructure to operate. Publishing a daily file to Amazon S3 does not provide on-demand queries and relies on presigned URLs rather than API-key authentication.

Using a broadcast join hint copies the small 100 MB reference DataFrame to every executor, so the larger 500 GB DataFrame can be joined locally without shuffling either dataset across the network. Increasing the number of shuffle partitions will not reduce the amount of data shuffled, and persisting the DataFrames adds memory pressure without eliminating the shuffle. Enabling speculative execution only mitigates slow tasks but does not address the fundamental shuffle cost of a standard hash join. Therefore, broadcasting the small table is the most effective solution.