Data-Engineer-Associate Amazon Web Services AWS Certified Data Engineer - Associate (DEA-C01) Free Practice Exam Questions (2026 Updated)

Option D is correct because Amazon Redshift stored procedures are designed to encapsulate a sequence of SQL statements and business logic inside the database. AWS documentation states that stored procedures are commonly used for data transformation, data validation, and business-specific logic, and that they can combine multiple SQL steps into one procedure. AWS also documents the standard Redshift pattern for deleting stale rows and inserting fresh rows from a staging table, which is exactly the requirement here. Keeping the operation inside Redshift is the most direct way to maintain an up-to-date orders table for downstream consumers.

Option A is incorrect because Redshift Spectrum is for querying external data in S3, not for performing this in-place Redshift table-maintenance pattern. Option B adds unnecessary unload and reload steps, creating delay and operational complexity. Option C is also unsuitable because Athena federated queries are not the right mechanism for transactional maintenance of Redshift tables. The correct DEA-C01-style answer is to use Redshift-native procedural SQL to delete stale rows and insert current rows from staging.

Option B is correct because Amazon Bedrock Knowledge Bases is the managed AWS service for retrieval-augmented generation using an organization’s own content. AWS states that the RetrieveAndGenerate flow queries a knowledge base, generates responses based on retrieved results, and that the response includes citations only for relevant sources. AWS user guide pages also state that Knowledge Bases can return natural-language responses based on retrieved chunks from source documents. This directly fits the requirement for feedback with direct references to textbook sections.

This is also the least operational overhead choice because Amazon Bedrock manages ingestion, chunking, embedding, retrieval, and generation workflow components. Option A would require building and operating a custom vector solution. Option C adds multiple services and custom glue logic. Option D requires custom model hosting and fine-tuning, which is far more operationally heavy. For multilingual processing, Amazon Bedrock supports multilingual-capable models and multilingual embedding options, making it suitable for responses in the student’s selected language when paired with an appropriate model.

The correct answer is A because the requirement specifically says that traffic between Lambda and the Amazon Redshift Data API must remain on the AWS network. AWS documentation states that you can call the Redshift Data API with Amazon VPC endpoints and that this keeps traffic between applications in the VPC and the Data API in the AWS network without public IP addresses. The Data API also removes the need to manage database drivers, persistent connections, and network configuration from Lambda. Creating a VPC endpoint for Lambda is not how Lambda privately reaches Redshift Data API. ODBC and JDBC drivers introduce connection management and do not address the stated Data API traffic requirement. Therefore, Data API plus the Redshift Data API VPC endpoint is correct.

Amazon RDS is a fully managed service that provides relational databases in the cloud. Amazon RDS for MySQL is one of the supported database engines that you can use to run your applications. Amazon RDS provides various features and tools to monitor and optimize the performance of your DB instances, such as Performance Insights, Enhanced Monitoring, CloudWatch metrics and alarms, etc.

Using the Performance Insights feature of Amazon RDS to identify queries that have high CPU utilization and optimizing the problematic queries will help reduce the CPU utilization of the DB instance. Performance Insights is a feature that allows you to analyze the load on your DB instance and determine what is causing performance issues. Performance Insights collects, analyzes, and displays database performance data using an interactive dashboard. You can use Performance Insights to identify the top SQL statements, hosts, users, or processes that are consuming the most CPU resources. You can also drill down into the details of each query and see the execution plan, wait events, locks, etc. By using Performance Insights, you can pinpoint the root cause of the high CPU utilization and optimize the queries accordingly. For example, you can rewrite the queries to make them more efficient, add or remove indexes, use prepared statements, etc.

Implementing caching to reduce the database query load will also help reduce the CPU utilization of the DB instance. Caching is a technique that allows you to store frequently accessed data in a fast and scalable storage layer, such as Amazon ElastiCache. By using caching, you can reduce the number of requests that hit your database, which in turn reduces the CPU load on your DB instance. Caching also improves the performance and availability of your application, as it reduces the latency and increases the throughput of your data access. You can use caching for various scenarios, such as storing session data, user preferences, application configuration, etc. You can also use caching for read-heavy workloads, such as displaying product details, recommendations, reviews, etc.

The other options are not as effective as using Performance Insights and caching. Modifying the database schema to include additional tables and indexes may or may not improve the CPU utilization, depending on the nature of the workload and the queries. Adding more tables and indexes may increase the complexity and overhead of the database, which may negatively affect the performance. Rebooting the RDS DB instance once each week will not reduce the CPU utilization, as it will not address the underlying cause of the high CPU load. Rebooting may also cause downtime and disruption to your application. Upgrading to a larger instance size may reduce the CPU utilization, but it will also increase the cost and complexity of your solution. Upgrading may also not be necessary if you can optimize the queries and reduce the database load by using caching. References:

Amazon RDS

Performance Insights

Amazon ElastiCache

[AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide], Chapter 3: Data Storage and Management, Section 3.1: Amazon RDS

In Amazon Athena, you can use CREATE TABLE AS SELECT with WITH NO DATA to create an empty copy of an existing table’s schema:

“The query CREATE TABLE new_table AS SELECT * FROM old_table WITH NO DATA; creates a new table with the same schema but without copying over the data.”

– Ace the AWS Certified Data Engineer - Associate Certification - version 2 - apple.pdf

This is the most efficient way to create an empty version of the existing table.

To identify the reason why the Step Functions state machine is not able to run the EMR jobs, the company should take the following steps:

Verify that the Step Functions state machine code has all IAM permissions that are necessary to create and run the EMR jobs. The state machine code should have an IAM role that allows it to invoke the EMR APIs, such as RunJobFlow, AddJobFlowSteps, and DescribeStep. The state machine code should also have IAM permissions to access the Amazon S3 buckets that the EMR jobs use as input and output locations. The company can use Access Analyzer for S3 to check the access policies and permissions of the S3 buckets12. Therefore, option B is correct.

Query the flow logs for the VPC. The flow logs can provide information about the network traffic to and from the EMR cluster that is launched in the VPC. The company can use the flow logs to determine whether the traffic that originates from the EMR cluster can successfully reach the data providers, such as Amazon RDS, Amazon Redshift, or other external sources. The company can also determine whether any security group that might be attached to the EMR cluster allows connections to the data source servers on the informed ports. The company can use Amazon VPC Flow Logs or Amazon CloudWatch Logs Insights to query the flow logs3 . Therefore, option D is correct.

Option A is incorrect because it suggests using AWS CloudFormation to automate the Step Functions state machine deployment. While this is a good practice to ensure consistency and repeatability of the deployment, it does not help to identify the reason why the state machine is not able to run the EMR jobs. Moreover, creating a step to pause the state machine during the EMR jobs that fail and wait for a human user to send approval through an email message is not a reliable way to troubleshoot the issue. The company should use the Step Functions console or API to monitor the execution history and status of the state machine, and use Amazon CloudWatch to view the logs and metrics of the EMR jobs .

Option C is incorrect because it suggests changing the AWS Step Functions state machine code to use Amazon EMR on EKS. Amazon EMR on EKS is a service that allows you to run EMR jobs on Amazon Elastic Kubernetes Service (Amazon EKS) clusters. While this service has some benefits, such as lower cost and faster execution time, it does not support all the features and integrations that EMR on EC2 does, such as EMR Notebooks, EMR Studio, and EMRFS. Therefore, changing the state machine code to use EMR on EKS may not be compatible with the existing data pipeline and may introduce new issues.

Option E is incorrect because it suggests checking the retry scenarios that the company configured for the EMR jobs. While this is a good practice to handle transient failures and errors, it does not help to identify the root cause of why the state machine is not able to run the EMR jobs. Moreover, increasing the number of seconds in the interval between each EMR task may not improve the success rate of the jobs, and may increase the execution time and cost of the state machine. Configuring an Amazon SNS topic to store the error messages may help to notify the company of any failures, but it does not provide enough information to troubleshoot the issue.

1: Manage an Amazon EMR Job - AWS Step Functions

2: Access Analyzer for S3 - Amazon Simple Storage Service

3: Working with Amazon EMR and VPC Flow Logs - Amazon EMR

[4]: Analyzing VPC Flow Logs with Amazon CloudWatch Logs Insights - Amazon Virtual Private Cloud

[5]: Monitor AWS Step Functions - AWS Step Functions

[6]: Monitor Amazon EMR clusters - Amazon EMR

[7]: Amazon EMR on Amazon EKS - Amazon EMR

The requirement is to manage the size of an Amazon DynamoDB table by automatically deleting data older than 1 month without disrupting ongoing read or write operations. The simplest and most maintenance-free solution is to use DynamoDB Time-to-Live (TTL).

Option A: Use the DynamoDB TTL feature to automatically expire data based on timestamps.DynamoDB TTL allows you to specify an attribute (e.g., a timestamp) that defines when items in the table should expire. After the expiration time, DynamoDB automatically deletes the items, freeing up storage space and keeping the table size under control without manual intervention or disruptions to ongoing operations.

Other options involve higher maintenance and manual scheduling or scanning operations, which increase complexity unnecessarily compared to the native TTL feature.

When processing many small files, the overhead of managing partitions can degrade performance. Rather than scaling workers manually, enabling AWS Glue auto scaling dynamically adjusts worker count based on resource requirements, which is cost-effective and addresses the performance concern.

“AWS Glue auto scaling automatically adds or removes workers based on the workload, which is efficient for handling performance bottlenecks when working with many small files.”

– Ace the AWS Certified Data Engineer - Associate Certification - version 2 - apple.pdf

Manually scaling up workers or increasing their size (A, B) can be more expensive and less adaptive.

Option C is correct because the required access pattern is: find all completed rides for a specific driver. In DynamoDB, when you need to query data efficiently by attributes that are not part of the base table primary key, you typically create a global secondary index (GSI). AWS documentation states that a GSI can have a key schema that is different from the base table and can use top-level attributes such as DriverID and RideStatus as its partition and sort keys. That makes it possible to query directly for a given driver and then narrow the results to completed rides, without scanning the full table.

Option A is incorrect because a local secondary index (LSI) must use the same partition key as the base table. Since the table partition key is RideID, an LSI cannot be created with DriverID as the partitioning access path.

Option B uses RiderID, which does not satisfy the requirement to retrieve rides by driver. Option D is also wrong because filter expressions are applied after items are read and therefore do not avoid scanning large amounts of data. The study guide emphasizes choosing the correct data model and access pattern for the workload, which places this question in the Data Storage and Management domain.

AWS Lake Formation is a service that allows you to easily set up, secure, and manage data lakes. One of the features of Lake Formation is row-level security, which enables you to control access to specific rows or columns of data based on the identity or role of the user. This feature is useful for scenarios where you need to restrict access to sensitive or regulated data, such as customer data from different countries. By registering the S3 bucket as a data lake location in Lake Formation, you can use the Lake Formation console or APIs to define and apply row-level security policies to the data in the bucket. You can also use Lake Formation blueprints to automate the ingestion and transformation of data from various sources into the data lake. This solution requires the least operational effort compared to the other options, as it does not involve creating or moving data, or managing multiple tables, views, or roles. References:

AWS Lake Formation

Row-Level Security

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 4: Data Lakes and Data Warehouses, Section 4.2: AWS Lake Formation

Option A is correct because Amazon Redshift supports the EXCLUDE clause in the SELECT list for exactly this use case: returning all columns from a wide table except a small set of unwanted columns. AWS documentation states that “EXCLUDE column_list names the columns that are excluded from the query results” and notes that the option is helpful when only a subset of columns must be excluded from a wide table. That matches this question precisely because the table contains 100 columns and only company_id and unique_system_id need to be omitted.

Option B is invalid SQL syntax in Amazon Redshift because NOT IN is used in predicates, not in the select list to remove columns. Option C is also incorrect because EXCEPT in Redshift is a set operator used between query result sets, not a select-list column exclusion feature. AWS documents EXCEPT alongside UNION and INTERSECT as result-set comparison operators, not as a way to omit columns from SELECT *. Option D is not valid Redshift SQL syntax for column selection. Therefore, the correct and documented Redshift statement is SELECT * EXCLUDE company_id, unique_system_id FROM orders.complete_orders_history;

To enable the Lambda function to connect to the RDS DB instance privately without using the public internet, the best combination of steps is to configure the Lambda function to run in the same subnet that the DB instance uses, and attach the same security group to the Lambda function and the DB instance. This way, the Lambda function and the DB instance can communicate within the same private network, and the security group can allow traffic between them on the database port. This solution has the least operational overhead, as it does not require any changes to the public access setting, the network ACL, or the security group of the DB instance.

The other options are not optimal for the following reasons:

A. Turn on the public access setting for the DB instance. This option is not recommended, as it would expose the DB instance to the public internet, which can compromise the security and privacy of the data. Moreover, this option would not enable the Lambda function to connect to the DB instance privately, as it would still require the Lambda function to use the public internet to access the DB instance.

B. Update the security group of the DB instance to allow only Lambda function invocations on the database port. This option is not sufficient, as it would only modify the inbound rules of the security group of the DB instance, but not the outbound rules of the security group of the Lambda function. Moreover, this option would not enable the Lambda function to connect to the DB instance privately, as it would still require the Lambda function to use the public internet to access the DB instance.

E. Update the network ACL of the private subnet to include a self-referencing rule that allows access through the database port. This option is not necessary, as the network ACL of the private subnet already allows all traffic within the subnet by default. Moreover, this option would not enable the Lambda function to connect to the DB instance privately, as it would still require the Lambda function to use the public internet to access the DB instance.

1: Connecting to an Amazon RDS DB instance

2: Configuring a Lambda function to access resources in a VPC

3: Working with security groups

Network ACLs

Option C is correct because the requirement is to verify that at least 90% of values in the Email column are valid email addresses. In AWS Glue Data Quality, rules are written in DQDL, and ColumnValues is the rule component used to validate whether column values satisfy a condition or pattern. AWS Glue Data Quality documentation explains that DQDL is the language used to define rules, and it specifically notes that ColumnValues rules can evaluate column content and that NULL values do not pass during comparisons. That is important here because the problem explicitly says the column contains null entries and invalid email values. A threshold of > 0.9 means the rule passes only when more than 90% of rows satisfy the email-validation condition.

Option A is incorrect because Uniqueness checks the percentage of values that are unique, not whether values are valid email addresses. AWS documents Uniqueness as measuring how many values occur exactly once, which is unrelated to email-format validity. Option D has the same issue because UniqueValueRatio addresses uniqueness characteristics, not format validation. Option B uses the right rule family but the wrong threshold because > 0.1 would require only 10% valid values. Therefore, ColumnValues with a threshold greater than 0.9 is the correct choice.

Option B is correct because access to specific S3 folders for AWS Glue jobs should be restricted by scoping permissions to the correct S3 object ARNs in the IAM policy Resource element, such as bucket paths with wildcard patterns for the allowed prefixes. Time-based access is then enforced by adding a Condition that uses the global IAM condition key aws:CurrentTime with date operators such as DateGreaterThan and DateLessThan. AWS IAM documentation explicitly states that date condition operators are used with aws:CurrentTime or aws:EpochTime in the Condition element.

Option C is not the best answer because s3:prefix is generally used with bucket listing operations rather than as the main mechanism to control object-level access for Glue reads and writes. For object access, the cleanest least-privilege design is to scope the allowed object ARNs directly. Option A is incorrect because $util.time is not an IAM policy condition key. Option D uses the wrong S3 condition key for the stated requirement. The uploaded study guide also emphasizes granting AWS Glue access through an IAM service role with the right scoped permissions, which fits this pattern.

To query the Sales table in Amazon Redshift for city names that start with " San " or " El, " the appropriate query uses a regular expression (regex) pattern to match city names that begin with those prefixes.

Option B: Select * from Sales where city_name ~ ' ^(San|El) ' ;In Amazon Redshift, the ~ operator is used to perform pattern matching using regular expressions. The ^(San|El) pattern matches city names that start with " San " or " El. " This is the correct SQL syntax for this use case.

Other options (A, C, D) contain incorrect syntax or incorrect use of special characters, making them invalid queries.

The best practice to restrict Amazon S3 access to specific VPCs is to use a bucket policy with a StringEquals or StringLike condition on aws:SourceVpc. This ensures only requests from a specified VPC are allowed.

IAM policies (option C) control who can access the resource but are not suitable alone to restrict by VPC.

Security Groups and NACLs (options B and D) do not apply to Amazon S3 because it is a global service and not VPC-bound.

“You can restrict access to your S3 bucket so that only requests coming from a specific VPC endpoint are allowed.”

Source: AWS Documentation – Amazon S3 Bucket Policies for VPC Endpoints

To perform cross-Region snapshot copying of an encrypted Redshift cluster, AWS documentation and the exam study guide clearly outline two essential steps:

You must create a snapshot copy grant in the destination Region. This allows Amazon Redshift to encrypt the snapshots using the specified AWS KMS key.

You must enable snapshot copying in the source Region and specify the name of the snapshot copy grant that was created in the destination Region.

From the study guide:

“To enable cross-region copy of encrypted snapshots, you must create a snapshot copy grant in the destination Region and enable snapshot copying in the source Region by specifying the snapshot copy grant name.”

– Ace the AWS Certified Data Engineer - Associate Certification - version 2 - apple.pdf

Option E (Multi-AZ deployment) is not applicable to Amazon Redshift, which does not support Multi-AZ configurations like Amazon RDS.

This solution meets the requirements of managing the ingestion of real-time streaming data into AWS and performing real-time analytics on the incoming streaming data with the least operational overhead. Amazon Managed Service for Apache Flink is a fully managed service that allows you to run Apache Flink applications without having to manage any infrastructure or clusters. Apache Flink is a framework for stateful stream processing that supports various types of aggregations, such as tumbling, sliding, and session windows, over streaming data. By using Amazon Managed Service for Apache Flink, you can easily connect to Amazon Kinesis Data Streams as the source and sink of your streaming data, and perform time-based analytics over a window of up to 30 minutes. This solution is also highly fault tolerant, as Amazon Managed Service for Apache Flink automatically scales, monitors, and restarts your Flink applications in case of failures. References:

Amazon Managed Service for Apache Flink

Apache Flink

Window Aggregations in Flink