Pass the Amazon Web Services AWS Certified Data Engineer Data-Engineer-Associate Questions and answers with CertsForce

The correct answer is B because AWS Glue streaming ETL jobs are built to continuously consume data from streaming sources such as Amazon Kinesis Data Streams, transform the data, and load the results into Amazon S3 data lakes. The requirement is not just delivery to S3; the telemetry JSON must be flattened before storage, and this needs to happen with low latency. Amazon Data Firehose can deliver streaming data, but the option incorrectly assumes automatic flattening without a transformation layer. SQS plus scheduled batch Glue introduces batch delay and does not meet the lowest-latency requirement. Athena is a query engine, not a real-time transformation and ingestion service for writing flattened streaming output to S3. AWS Glue streaming ETL is the correct serverless streaming transformation layer.

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

This solution will meet the requirements with the least operational overhead because it uses the AWS Glue Data Catalog as the central metadata repository for data sources that run in the AWS Cloud. The AWS Glue Data Catalog is a fully managed service that provides a unified view of your data assets across AWS and on-premises data sources. It stores the metadata of your data in tables, partitions, and columns, and enables you to access and query your data using various AWS services, such as Amazon Athena, Amazon EMR, and Amazon Redshift Spectrum. You can use AWS Glue crawlers to connect to multiple data stores, such as Amazon RDS, Amazon Redshift, and Amazon S3, and to update the Data Catalog with metadata changes. AWS Glue crawlers can automatically discover the schema and partition structure of your data, and create or update the corresponding tables in the Data Catalog. You can schedule the crawlers to run periodically to update the metadata catalog, and configure them to detect changes to the source metadata, such as new columns, tables, or partitions12.

The other options are not optimal for the following reasons:

A. Use Amazon Aurora as the data catalog. Create AWS Lambda functions that will connect to the data catalog. Configure the Lambda functions to gather the metadata information from multiple sources and to update the Aurora data catalog. Schedule the Lambda functions to run periodically. This option is not recommended, as it would require more operational overhead to create and manage an Amazon Aurora database as the data catalog, and to write and maintain AWS Lambda functions to gather and update the metadata information from multiple sources. Moreover, this option would not leverage the benefits of the AWS Glue Data Catalog, such as data cataloging, data transformation, and data governance.

C. Use Amazon DynamoDB as the data catalog. Create AWS Lambda functions that will connect to the data catalog. Configure the Lambda functions to gather the metadata information from multiple sources and to update the DynamoDB data catalog. Schedule the Lambda functions to run periodically. This option is also not recommended, as it would require more operational overhead to create and manage an Amazon DynamoDB table as the data catalog, and to write and maintain AWS Lambda functions to gather and update the metadata information from multiple sources. Moreover, this option would not leverage the benefits of the AWS Glue Data Catalog, such as data cataloging, data transformation, and data governance.

D. Use the AWS Glue Data Catalog as the central metadata repository. Extract the schema for Amazon RDS and Amazon Redshift sources, and build the Data Catalog. Use AWS Glue crawlers for data that is in Amazon S3 to infer the schema and to automatically update the Data Catalog. This option is not optimal, as it would require more manual effort to extract the schema for Amazon RDS and Amazon Redshift sources, and to build the Data Catalog. This option would not take advantage of the AWS Glue crawlers’ ability to automatically discover the schema and partition structure of your data from various data sources, and to create or update the corresponding tables in the Data Catalog.

1: AWS Glue Data Catalog

2: AWS Glue Crawlers

Amazon Aurora

AWS Lambda

Amazon DynamoDB

AWS Glue Data Catalog stores metadata about data stored in Amazon S3, including table schemas and partitions. If new partitions are added to S3 and the Glue Data Catalog is not updated—either through crawlers or explicit API calls—query engines such as Amazon Athena and AWS Glue will not be aware of the new data.

This leads to analysis results that appear incomplete or outdated, even though the underlying data exists in S3. This is a common issue in time-sensitive analytics workloads where data arrives frequently and partitions change often.

IAM permission issues would typically cause access failures rather than missing records. S3 versioning affects object history, not query visibility. Overlapping Glue job schedules could cause processing delays but would not directly cause missing data during query time.

Therefore, the most likely cause is that the Glue Data Catalog metadata is not synchronized with the latest S3 partitions.

Option C is correct because SSE-KMS encrypts Amazon S3 objects by using AWS KMS keys, and access to those keys can be controlled through IAM policies and KMS key policies so that only specific employees can use them. This directly satisfies both requirements: encryption of the S3 objects and restriction of key usage to a limited group of authorized users. AWS-managed S3 encryption with KMS also requires far less effort than building or operating a dedicated HSM-based solution. This is the standard AWS answer when the question asks for encrypted S3 data with fine-grained control over who can use the encryption keys.

Option A is far more operationally complex because CloudHSM requires managing dedicated HSM infrastructure. Option B is less desirable operationally because SSE-C requires the customer to provide and manage the keys for every request. Option D is incorrect because SSE-S3 uses Amazon S3 managed keys, and customers do not get the same granular control over key usage by specific employees. Therefore, the least-effort and best-governed solution is SSE-KMS with restricted KMS key access. This aligns with the study guide’s security-and-governance focus on using AWS KMS when controlled access to encryption keys is required.

In Amazon Redshift, a compound sort key is designed to optimize the performance of queries that use filtering and join conditions on the columns in the sort key. A compound sort key orders the data based on the first column, followed by the second, and so on. In the scenario given, the compound sort key consists of Region ID, Department ID, and Role ID. Therefore, queries that filter on the leading columns of the sort key are more likely to benefit from this order.

Option B: " Select * from Employee where Region ID= ' North America ' and Department ID=20; " This query will perform well because it uses both the Region ID and Department ID, which are the first two columns of the compound sort key. The order of the columns in the WHERE clause matches the order in the sort key, thus allowing the query to scan fewer rows and improve performance.

Option C: " Select * from Employee where Department ID=20 and Region ID= ' North America ' ; " This query also benefits from the compound sort key because it includes both Region ID and Department ID, which are the first two columns in the sort key. Although the order in the WHERE clause does not match exactly, Amazon Redshift will still leverage the sort key to reduce the amount of data scanned, improving query speed.

Options A, D, and E are less optimal because they do not utilize the sort key as effectively:

Option A only filters by the Region ID, which may still use the sort key but does not take full advantage of the compound nature.

Option D uses only Role ID, the last column in the compound sort key, which will not benefit much from sorting since it is the third key in the sort order.

Option E filters on Region ID and Role ID but skips the Department ID column, making it less efficient for the compound sort key.

AWS Glue workflows are a feature of AWS Glue that enable you to create and visualize complex ETL pipelines using AWS Glue components, such as crawlers, jobs, triggers, and development endpoints. AWS Glue workflows provide automated orchestration and require minimal manual effort, as they handle dependency resolution, error handling, state management, and resource allocation for your ETL workflows. You can use AWS Glue workflows to ingest data from your operational databases into your Amazon S3 based data lake, and then use AWS Glue and Amazon EMR to process the data in the data lake. This solution will meet the requirements with the least operational overhead, as it leverages the serverless and fully managed nature of AWS Glue, and the scalability and flexibility of Amazon EMR12.

The other options are not optimal for the following reasons:

B. AWS Step Functions tasks. AWS Step Functions is a service that lets you coordinate multiple AWS services into serverless workflows. You can use AWS Step Functions tasks to invoke AWS Glue and Amazon EMR jobs as part of your ETL workflows, and use AWS Step Functions state machines to define the logic and flow of your workflows. However, this option would require more manual effort than AWS Glue workflows, as you would need to write JSON code to define your state machines, handle errors and retries, and monitor the execution history and status of your workflows3.

C. AWS Lambda functions. AWS Lambda is a service that lets you run code without provisioning or managing servers. You can use AWS Lambda functions to trigger AWS Glue and Amazon EMR jobs as part of your ETL workflows, and use AWS Lambda event sources and destinations to orchestrate the flow of your workflows. However, this option would also require more manual effort than AWS Glue workflows, as you would need to write code to implement your business logic, handle errors and retries, and monitor the invocation and execution of your Lambda functions. Moreover, AWS Lambda functions have limitations on the execution time, memory, and concurrency, which may affect the performance and scalability of your ETL workflows.

D. Amazon Managed Workflows for Apache Airflow (Amazon MWAA) workflows. Amazon MWAA is a managed service that makes it easy to run open source Apache Airflow on AWS. Apache Airflow is a popular tool for creating and managing complex ETL pipelines using directed acyclic graphs (DAGs). You can use Amazon MWAA workflows to orchestrate AWS Glue and Amazon EMR jobs as part of your ETL workflows, and use the Airflow web interface to visualize and monitor your workflows. However, this option would have more operational overhead than AWS Glue workflows, as you would need to set up and configure your Amazon MWAA environment, write Python code to define your DAGs, and manage the dependencies and versions of your Airflow plugins and operators.

1: AWS Glue Workflows

2: AWS Glue and Amazon EMR

3: AWS Step Functions

AWS Lambda

Amazon Managed Workflows for Apache Airflow

The correct answer is B because HNSW with the Faiss engine is the best fit when the system needs approximate k-NN search over millions of high-dimensional vectors while maintaining low latency and strong recall. OpenSearch approximate k-NN is designed for large, high-dimensional datasets where brute-force exact search becomes inefficient, and OpenSearch documentation shows HNSW configured with the Faiss engine for knn_vector fields. Exact search with a scoring script is accurate but does not scale well for 5 million vectors under a strict latency requirement. IVF can scale but usually requires training and tuning and may trade off recall. Binary quantization reduces memory but can reduce accuracy. Therefore, HNSW with Faiss is the strongest exam answer.

The correct answer is A because the workload has grown to petabyte scale, but the performance requirement is focused on recent transactions. The best architecture is to keep hot, frequently queried data inside Amazon Redshift and move historical/cold data to Amazon S3. Amazon Redshift Spectrum lets Redshift efficiently query structured and semi-structured files in S3 without loading that data into Redshift tables, and most of the data remains in S3 while Spectrum performs parallel processing. This reduces pressure on the provisioned cluster while preserving query access to historical data. Decreasing nodes would worsen performance. DISTSTYLE ALL for historical tables can increase storage and maintenance overhead. Redshift is already columnar, so switching to row-based storage is not valid.