Amazon Web Services Data-Engineer-Associate Dumps

Amazon Athena achieves the fastest query performance when data is stored in columnar formats such as Apache Parquet and when queries can take advantage of partition pruning and predicate pushdown.

Converting CSV files to Parquet significantly reduces the amount of data scanned because Parquet stores data in a column-oriented layout. Since Athena queries only a subset of attributes, it reads only the required columns instead of scanning entire rows, which dramatically improves performance. Predicate pushdown further reduces query time by filtering data at the storage layer.

Partitioning the data by date ensures that Athena scans only the relevant partitions for same-day queries, minimizing unnecessary data reads. Storing one Parquet file per day is efficient and avoids the overhead of managing excessive small files.

ORC is also a columnar format, but Parquet is more commonly optimized and recommended for Athena workloads in AWS exam guidance. JSON and Avro are row-based or semi-row-based formats and result in larger scan sizes and slower query execution.

Therefore, Option D provides the shortest query times and aligns with Athena performance best practices.

In AWS Glue, table partitions are managed as metadata objects within the AWS Glue Data Catalog. To add a new partition to an existing table, the correct and supported approach is to use the AWS Glue Data Catalog API, such as the CreatePartition operation, or equivalent console or SDK actions.

Updating the table schema alone does not create partitions or inform Glue about new partition values. Editing metadata files directly in Amazon S3 is unsupported and can corrupt the Data Catalog. Manually modifying the S3 bucket structure without registering partitions in Glue will result in Athena and other query engines being unable to recognize the partitions.

By adding partitions through the Glue Data Catalog API, query engines such as Amazon Athena and Amazon Redshift Spectrum can perform partition pruning, which significantly improves query performance by scanning only relevant data.

This method aligns with AWS best practices, ensures metadata consistency, and avoids unnecessary operational risk. Therefore, Option C is the correct solution.

Lake Formation requires that S3 locations be registered before they can be accessed by AWS Glue crawlers, jobs, or queries. Registering the location associates it with the Lake Formation permissions framework.

“Before AWS Glue crawlers can access an S3 path managed by Lake Formation, the S3 location must be registered in Lake Formation.”

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

AWS Lake Formation is specifically designed to simplify fine-grained access control for data lakes stored in Amazon S3. By using Lake Formation tag-based access control (LF-TBAC), administrators can define access policies once and apply them dynamically based on tags rather than managing individual IAM policies.

LF-Tags can be assigned to databases, tables, and columns in the AWS Glue Data Catalog. Departments are granted permissions based on tags, ensuring that each department can access only the data it is authorized to view. This approach scales efficiently as datasets and departments grow, which is critical for data lakes ingesting millions of records daily.

Managing IAM policies per department is operationally complex and error-prone. Redshift-based access centralization limits flexibility and introduces unnecessary infrastructure. Syncing data into an RDS database adds cost, latency, and maintenance overhead.

Lake Formation provides centralized governance, auditing, and least-privilege enforcement with minimal administrative effort, making it the optimal solution.

Option C is the best answer because Amazon MWAA is a fully managed Apache Airflow service built specifically for workflow orchestration. Airflow uses Directed Acyclic Graphs (DAGs) to model task dependencies, which directly satisfies the requirement for dependency management. Amazon MWAA also supports orchestration across AWS analytics services such as Amazon S3, Amazon Redshift, and Amazon EMR, making it a natural fit for complex ETL pipelines. AWS documentation describes workflow definitions, tasks, and Airflow-based orchestration as core MWAA concepts.

This choice is stronger than A because the question specifically asks for orchestration of complex ETL workflows with dependency management, retries, and monitoring across multiple data-processing systems. While AWS Step Functions is an orchestration service, the option specifies an express workflow, and AWS states that Express Workflows are designed for high-volume, short-duration runs and can run for only up to five minutes, which is often not the best fit for longer-running ETL jobs on EMR and Glue.

The uploaded study material also points to orchestration services for dependent ETL jobs and identifies both Step Functions and workflow orchestration concepts as core exam knowledge, but for this exact multi-step ETL orchestration pattern, MWAA with DAGs is the most precise match.

 Amazon Athena is a serverless interactive query service that allows you to analyze data in Amazon S3 using standard SQL. Athena supports various data formats, such as CSV, JSON, ORC, Avro, and Parquet. However, not all data formats are equally efficient for querying. Some data formats, such as CSV and JSON, are row-oriented, meaning that they store data as a sequence of records, each with the same fields. Row-oriented formats are suitable for loading and exporting data, but they are not optimal for analytical queries that often access only a subset of columns. Row-oriented formats also do not support compression or encoding techniques that can reduce the data size and improve the query performance.

On the other hand, some data formats, such as ORC and Parquet, are column-oriented, meaning that they store data as a collection of columns, each with a specific data type. Column-oriented formats are ideal for analytical queries that often filter, aggregate, or join data by columns. Column-oriented formats also support compression and encoding techniques that can reduce the data size and improve the query performance. For example, Parquet supports dictionary encoding, which replaces repeated values with numeric codes, and run-length encoding, which replaces consecutive identical values with a single value and a count. Parquet also supports various compression algorithms, such as Snappy, GZIP, and ZSTD, that can further reduce the data size and improve the query performance.

Therefore, changing the data format from CSV to Parquet and applying Snappy compression will most speed up the Athena query performance. Parquet is a column-oriented format that allows Athena to scan only the relevant columns and skip the rest, reducing the amount of data read from S3. Snappy is a compression algorithm that reduces the data size without compromising the query speed, as it is splittable and does not require decompression before reading. This solution will also reduce the cost of Athena queries, as Athena charges based on the amount of data scanned from S3.

The other options are not as effective as changing the data format to Parquet and applying Snappy compression. Changing the data format from CSV to JSON and applying Snappy compression will not improve the query performance significantly, as JSON is also a row-oriented format that does not support columnar access or encoding techniques. Compressing the CSV files by using Snappy compression will reduce the data size, but it will not improve the query performance significantly, as CSV is still a row-oriented format that does not support columnar access or encoding techniques. Compressing the CSV files by using gzjg compression will reduce the data size, but it will degrade the query performance, as gzjg is not a splittable compression algorithm and requires decompression before reading. References:

Amazon Athena

Choosing the Right Data Format

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 5: Data Analysis and Visualization, Section 5.1: Amazon Athena

Option B is the right approach because the requirement is to mask PII in the log destination (the CloudWatch Logs log group). The exam guide explicitly calls out security responsibilities that include “data encryption and masking” and also emphasizes enabling and preparing logs for audit and governance needs. A log-group–level masking mechanism is therefore the most direct control point to prevent sensitive values from being exposed to anyone who can view logs.

Option A (Glue security configuration) is primarily used to apply protections such as encryption settings for Glue jobs and related outputs; it does not inherently solve the problem of masking PII that has already been emitted into application logs. Option C (Macie) is intended to discover and classify sensitive data—most commonly in Amazon S3—and to produce findings; it is not a log-masking control for CloudWatch Logs. The material reinforces Macie as a discovery/classification service for PII rather than a masking mechanism. Option D adds custom code paths in the ETL job and still may miss PII that appears in framework/system logs; it also violates the “least operational effort” spirit compared to applying a centralized log policy.

Therefore, attaching a data protection policy directly to the CloudWatch Logs log group best meets the masking requirement.

Option B is correct because ANALYZE COMPRESSION is the Amazon Redshift command specifically used to evaluate existing table data and recommend better compression encodings for columns. AWS states that ANALYZE COMPRESSION performs compression analysis and produces a report with the suggested compression encoding for the tables analyzed. AWS also states that when you already have data in an existing table, you can use ANALYZE COMPRESSION to view the recommended encodings for that table.

Option A is incorrect because the standard ANALYZE command updates optimizer statistics; it does not recommend column compression settings. Options C and D are vacuum operations related to sorting, reclaiming space, or clustering behavior, not choosing better compression encodings. AWS also notes that Redshift supports automatic encoding management with ENCODE AUTO, but when the question asks how to improve encoding for existing suboptimal tables, the direct diagnostic command is ANALYZE COMPRESSION.

Also, once you finish this review set, consider using the Djamgatech App for deeper DEA-C01 prep and practice tests: iOS, Web, Android, and Microsoft Store editions are available. For job opportunities related to Data Engineering certification, a strong platform to use is LinkedIn Jobs.

To optimize joins between large tables, Redshift recommends using KEY distribution on a common, high-cardinality join key to ensure co-location of data blocks. The Amazon Redshift Advisor identifies performance bottlenecks and provides recommendations for distribution and sort keys.

“Use KEY distribution when joining large tables on a common high-cardinality column. Use Redshift Advisor to identify query optimization opportunities.”

Amazon Athena provides federated query connectors that allow querying multiple data sources, such as Amazon Redshift, Teradata, and Google BigQuery, without needing to extract the data from the original source. This solution is optimal because it offers the least operational effort by avoiding complex data movement and transformation processes.

Amazon Athena Federated Queries:

Athena’s federated queries allow direct querying of data stored across multiple sources, including Amazon Redshift, Teradata, and BigQuery. With Athena’s support for Apache Iceberg, the company can easily run a Merge operation on the Iceberg table.

The solution reduces complexity by centralizing the query execution and transformation process in Athena using SQL queries.

AWS Glue Data Quality is designed specifically to validate data as part of ETL pipelines with minimal cost and operational overhead. By creating a custom data quality rule, the data engineer can enforce business logic such as ensuring that the shipping date is later than the order date and that the delivery date is later than the shipping date.

Glue Data Quality rules run natively within AWS Glue ETL jobs, eliminating the need for additional services, duplicate queries, or post-processing steps. The service supports automatically identifying failed records and routing them to a separate Amazon S3 bucket, which directly satisfies the requirement to isolate invalid orders.

Using AWS Glue DataBrew introduces additional profiling and transformation steps that are unnecessary for a simple rule-based validation. Athena-based validation would require scanning data repeatedly, increasing query costs and adding complexity. AWS Glue crawlers only discover schema metadata and cannot enforce data quality rules.

From a cost and architecture perspective, Glue Data Quality integrates directly into existing Glue pipelines, avoids additional compute usage, and provides centralized monitoring of data quality metrics. Therefore, Option C is the most cost-effective and exam-aligned solution.

Problem Analysis:

The company uses AWS Glue for ETL pipelines and must enforce data quality checks during pipeline execution.

The goal is to implement quality checks with minimal implementation effort.

Key Considerations:

AWS Glue provides an Evaluate Data Quality transform that allows for defining quality checks directly in the pipeline.

DQDL (Data Quality Definition Language) simplifies the process by allowing declarative rule definitions.

Solution Analysis:

Option A: SQL Transform

SQL queries can implement rules but require manual effort for each rule and do not integrate natively with Glue.

Option B: Evaluate Data Quality Transform + DQDL

AWS Glue’s built-in Evaluate Data Quality transform is designed for this use case.

Allows defining thresholds and rules in DQDL with minimal coding effort.

Option C: Custom Transform with PyDeequ

PyDeequ is a powerful library but adds unnecessary complexity compared to Glue’s native features.

Option D: Custom Transform with Great Expectations

Similar to PyDeequ, Great Expectations adds operational complexity and external dependencies.

Final Recommendation:

Use the Evaluate Data Quality transform with DQDL to implement data quality rules in AWS Glue pipelines.

AWS Glue Data Quality

DQDL Syntax and Examples

AWS Glue Studio Documentation

For exactly-once delivery and processing in Amazon Kinesis Data Streams, the best approach is to design the application so that it handles idempotency. By embedding a unique ID in each record, the application can identify and remove duplicate records during processing.

Exactly-Once Processing:

Kinesis Data Streams does not natively support exactly-once processing. Therefore, idempotency should be designed into the application, ensuring that each record has a unique identifier so that the same event is processed only once, even if it is ingested multiple times.

This pattern is widely used for achieving exactly-once semantics in distributed systems.

AWS Database Migration Service (AWS DMS) is a cloud service that makes it possible to migrate relational databases, data warehouses, NoSQL databases, and other types of data stores to AWS quickly, securely, and with minimal downtime and zero data loss1. AWS DMS supports migration between 20-plus database and analytics engines, such as Microsoft SQL Server to Amazon RDS for SQL Server2. AWS DMS takes over many of the difficult or tedious tasks involved in a migration project, such as capacity analysis, hardware and software procurement, installation and administration, testing and debugging, and ongoing replication and monitoring1. AWS DMS is a cost-effective solution, as you only pay for the compute resources and additional log storage used during the migration process2. AWS DMS is the best solution for the company to migrate the financial transaction data from the on-premises Microsoft SQL Server database to AWS, as it meets the requirements of minimal downtime, zero data loss, and low cost.

Option A is not the best solution, as AWS Lambda is a serverless compute service that lets you run code without provisioning or managing servers, but it does not provide any built-in features for database migration. You would have to write your own code to extract, transform, and load the data from the source to the target, which would increase the operational overhead and complexity.

Option C is not the best solution, as AWS Direct Connect is a service that establishes a dedicated network connection from your premises to AWS, but it does not provide any built-in features for database migration. You would still need to use another service or tool to perform the actual data transfer, which would increase the cost and complexity.

Option D is not the best solution, as AWS DataSync is a service that makes it easy to transfer data between on-premises storage systems and AWS storage services, such as Amazon S3, Amazon EFS, and Amazon FSx for Windows File Server, but it does not support Amazon RDS for SQL Server as a target. You would have to use another service or tool to migrate the data from Amazon S3 to Amazon RDS for SQL Server, which would increase the latency and complexity. References:

Database Migration - AWS Database Migration Service - AWS

What is AWS Database Migration Service?

AWS Database Migration Service Documentation

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide

AWS Data Exchange is a service that makes it easy to find, subscribe to, and use third-party data in the cloud. It provides a secure and reliable way to access and integrate data from various sources, such as data providers, public datasets, or AWS services. Using AWS Data Exchange, you can browse and subscribe to data products that suit your needs, and then use API calls or the AWS Management Console to export the data to Amazon S3, where you can use it with your existing analytics platform. This solution minimizes the effort and time required to incorporate third-party datasets, as you do not need to set up and manage data pipelines, storage, or access controls. You also benefit from the data quality and freshness provided by the data providers, who can update their data products as frequently as needed12.

The other options are not optimal for the following reasons:

B. Use API calls to access and integrate third-party datasets from AWS. This option is vague and does not specify which AWS service or feature is used to access and integrate third-party datasets. AWS offers a variety of services and features that can help with data ingestion, processing, and analysis, but not all of them are suitable for the given scenario. For example, AWS Glue is a serverless data integration service that can help you discover, prepare, and combine data from various sources, but it requires you to create and run data extraction, transformation, and loading (ETL) jobs, which can add operational overhead3.

C. Use Amazon Kinesis Data Streams to access and integrate third-party datasets from AWS CodeCommit repositories. This option is not feasible, as AWS CodeCommit is a source control service that hosts secure Git-based repositories, not a data source that can be accessed by Amazon Kinesis Data Streams. Amazon Kinesis Data Streams is a service that enables you to capture, process, and analyze data streams in real time, such as clickstream data, application logs, or IoT telemetry. It does not support accessing and integrating data from AWS CodeCommit repositories, which are meant for storing and managing code, not data .

D. Use Amazon Kinesis Data Streams to access and integrate third-party datasets from Amazon Elastic Container Registry (Amazon ECR). This option is also not feasible, as Amazon ECR is a fully managed container registry service that stores, manages, and deploys container images, not a data source that can be accessed by Amazon Kinesis Data Streams. Amazon Kinesis Data Streams does not support accessing and integrating data from Amazon ECR, which is meant for storing and managing container images, not data .

1: AWS Data Exchange User Guide

2: AWS Data Exchange FAQs

3: AWS Glue Developer Guide

AWS CodeCommit User Guide

Amazon Kinesis Data Streams Developer Guide

Amazon Elastic Container Registry User Guide

Build a Continuous Delivery Pipeline for Your Container Images with Amazon ECR as Source

Option B is correct because Amazon MSK Replicator is the AWS-managed feature specifically designed to reliably replicate data across Amazon MSK clusters in different AWS Regions. AWS states that MSK Replicator can be used to build regionally resilient streaming applications for availability and business continuity, which directly supports the disaster recovery requirement. It is also the choice with the least operational effort because it avoids deploying and managing self-hosted replication tools such as Kafka Connect or MirrorMaker 2. AWS documentation for cross-Region setup also shows support for secure connectivity and notes that MSK environments support IAM, TLS, and SASL/SCRAM authentication schemes for private connectivity scenarios.

Option A and C would require the company to deploy, scale, patch, and monitor custom replication infrastructure in every Region, which increases operational overhead. Option D is not appropriate because Kinesis Data Firehose is not the native service for replicating data between MSK clusters. For ordering and consistency, Kafka-based replication preserves partitioned stream semantics better than mixing in a different streaming service. Therefore, a mesh of MSK Replicator relationships between regional MSK clusters is the best AWS-native answer for near real-time, encrypted, cross-Region replication with minimal administration.

The best solution for managing SSL/TLS certificates on EC2 instances with minimal operational overhead is to use AWS Certificate Manager (ACM). ACM simplifies certificate management by automating the provisioning, renewal, and deployment of certificates.

AWS Certificate Manager (ACM):

ACM manages SSL/TLS certificates for EC2 and other AWS resources, including automatic certificate renewal. This reduces the need for manual management and avoids operational complexity.

ACM also integrates with other AWS services to simplify secure connections between AWS infrastructure and customer-managed environments.

Problem Analysis:

The company runs a daily AWS Glue ETL pipeline to clean and transform files received in an S3 bucket.

If a file is incomplete or empty, the previous day’s file should be retained.

Need a solution to validate files before overwriting the existing file.

Key Considerations:

Automate data validation with minimal human intervention.

Use built-in AWS Glue capabilities for ease of integration.

Ensure robust validation for missing or incomplete data.

Solution Analysis:

Option A: Lambda Function for Validation

Lambda can validate files, but it would require custom code.

Does not leverage AWS Glue’s built-in features, adding operational complexity.

Option B: AWS Glue Data Quality Rules

AWS Glue Data Quality allows defining Data Quality Definition Language (DQDL) rules.

Rules can validate if required fields are missing or if the file is empty.

Automatically integrates into the existing ETL pipeline.

If validation fails, retain the previous day’s file.

Option C: AWS Glue Studio with Filling Missing Values

Modifying ETL code to fill missing values with most common values risks introducing inaccuracies.

Does not handle empty files effectively.

Option D: Athena Query for Validation

Athena can drop rows with missing values, but this is a post-hoc solution.

Requires manual intervention to copy the corrected file to S3, increasing complexity.

Final Recommendation:

Use AWS Glue Data Quality to define validation rules in DQDL for identifying missing or incomplete data.

This solution integrates seamlessly with the ETL pipeline and minimizes manual effort.

Implementation Steps:

Enable AWS Glue Data Quality in the existing ETL pipeline.

Define DQDL Rules, such as:

Check if a file is empty.

Verify required fields are present and non-null.

Configure the pipeline to proceed with overwriting only if the file passes validation.

In case of failure, retain the previous day’s file.

AWS Glue Data Quality Overview

Defining DQDL Rules

AWS Glue Studio Documentation

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;

Amazon S3 natively supports checksum validation during uploads when objects are uploaded by using the AWS SDK. The SDK can automatically calculate a checksum, such as CRC32, on the client side and send it with the upload request. Amazon S3 validates the checksum upon receipt and stores it with the object, ensuring that the data uploaded is exactly the same as the source data.

Storing the checksum in the S3 object metadata provides a lightweight and cost-effective way to verify data integrity without requiring additional compute, storage, or post-processing steps. This approach validates the logs at upload time, which is the most efficient and reliable method.

Using AWS Lambda to calculate SHA-256 checksums would require additional compute, orchestration, and storage, increasing cost and operational complexity. Amazon S3 Object Lock enforces immutability and retention but does not validate data integrity between source and destination. Governance or compliance mode Object Lock cannot detect whether uploaded data matches the original logs.

Therefore, using the AWS SDK checksum validation during upload is the simplest, most efficient, and AWS-recommended approach for ensuring log integrity.

Option D is correct because Amazon DynamoDB is a serverless NoSQL database that is well suited for semi-structured data, and DynamoDB Accelerator (DAX) is designed to improve read performance for read-heavy applications. AWS documentation states that DAX can improve DynamoDB performance from milliseconds to microseconds, and the DynamoDB guide says DAX is most effective for applications with a high read-to-write ratio, which exactly matches this workload of infrequent writes and frequent reads.

Option A and B are incorrect because Amazon S3 and Iceberg are object storage and table-format solutions, not low-latency transactional databases for millisecond retrieval. Option C is not serverless and requires managing a database cluster, even if optimized reads improve performance. By contrast, DynamoDB is fully managed and serverless, and DAX adds the lowest-latency read path for hot data. The uploaded study guide also identifies DynamoDB as the right fit for dynamic or semi-structured data that requires low-latency access at scale.

To achieve the most cost-effective storage solution, the data engineer needs to use an S3 Lifecycle policy that transitions objects to lower-cost storage classes based on their access patterns, and deletes them when they are no longer needed. The storage classes should also provide high availability, which means they should be resilient to the loss of data in a single Availability Zone1. Therefore, the solution must include the following steps:

Transition objects to S3 Standard-Infrequent Access (S3 Standard-IA) after 6 months. S3 Standard-IA is designed for data that is accessed less frequently, but requires rapid access when needed. It offers the same high durability, throughput, and low latency as S3 Standard, but with a lower storage cost and a retrieval fee2. Therefore, it is suitable for data files that are accessed once or twice each month. S3 Standard-IA also provides high availability, as it stores data redundantly across multiple Availability Zones1.

Transfer objects to S3 Glacier Deep Archive after 2 years. S3 Glacier Deep Archive is the lowest-cost storage class that offers secure and durable storage for data that is rarely accessed and can tolerate a 12-hour retrieval time. It is ideal for long-term archiving and digital preservation3. Therefore, it is suitable for data files that are accessed only once or twice each year. S3 Glacier Deep Archive also provides high availability, as it stores data across at least three geographically dispersed Availability Zones1.

Delete objects when they are no longer needed. The data engineer can specify an expiration action in the S3 Lifecycle policy to delete objects after a certain period of time. This will reduce the storage cost and comply with any data retention policies.

Option C is the only solution that includes all these steps. Therefore, option C is the correct answer.

Option A is incorrect because it transitions objects to S3 One Zone-Infrequent Access (S3 One Zone-IA) after 6 months. S3 One Zone-IA is similar to S3 Standard-IA, but it stores data in a single Availability Zone. This means it has a lower availability and durability than S3 Standard-IA, and it is not resilient to the loss of data in a single Availability Zone1. Therefore, it does not provide high availability as required.

Option B is incorrect because it transfers objects to S3 Glacier Flexible Retrieval after 2 years. S3 Glacier Flexible Retrieval is a storage class that offers secure and durable storage for data that is accessed infrequently and can tolerate a retrieval time of minutes to hours. It is more expensive than S3 Glacier Deep Archive, and it is not suitable for data that is accessed only once or twice each year3. Therefore, it is not the most cost-effective option.

Option D is incorrect because it combines the errors of option A and B. It transitions objects to S3 One Zone-IA after 6 months, which does not provide high availability, and it transfers objects to S3 Glacier Flexible Retrieval after 2 years, which is not the most cost-effective option.

1: Amazon S3 storage classes - Amazon Simple Storage Service

2: Amazon S3 Standard-Infrequent Access (S3 Standard-IA) - Amazon Simple Storage Service

3: Amazon S3 Glacier and S3 Glacier Deep Archive - Amazon Simple Storage Service

[4]: Expiring objects - Amazon Simple Storage Service

[5]: Managing your storage lifecycle - Amazon Simple Storage Service

[6]: Examples of S3 Lifecycle configuration - Amazon Simple Storage Service

[7]: Amazon S3 Lifecycle further optimizes storage cost savings with new features - What’s New with AWS

S3 Lifecycle policies automate data retention and deletion. By specifying an expiration rule for 7 years, objects older than that period are permanently deleted without manual intervention.

“To automatically delete aged data, configure an S3 Lifecycle rule with an expiration policy for objects older than the retention period.”

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

When AWS Glue jobs run inside a private subnet, they must use secure and supported methods to access external databases. AWS Glue supports JDBC connections to on-premises databases, but best practices require secure credential management and explicit driver configuration.

Using a JDBC driver stored in Amazon S3 allows Glue to connect to PostgreSQL without relying on default drivers. Storing credentials in AWS Secrets Manager eliminates hard-coded credentials and enables secure rotation, aligning with AWS security best practices.

Simply specifying credentials inline is less secure and not recommended. SASL connections are not supported for PostgreSQL JDBC connections. Security groups alone do not establish connectivity or authentication.

Therefore, Option D is the correct and production-grade solution.

Option A is the most direct and operationally efficient way to trigger the existing Lambda-based load into Amazon Redshift whenever a new CSV file is uploaded to Amazon S3. The key requirement is event-driven automation on “new object created” events, and the study material explicitly describes using Amazon EventBridge to react to S3 uploads by creating a rule on the default event bus and routing matching events to a target application.

Compared with queue-based designs (Options B and D), Option A reduces components: there is no need to manage an SQS queue, batching/visibility timeout behavior, or retry semantics at the consumer layer. This is especially appropriate because the files are small (≤ 5 MB) and the Lambda function is already implemented to perform the copy/load step. Using DMS (Option C) is not designed for “S3 object arrival → Lambda → Redshift” event triggering; it introduces unnecessary services and operational work for a simple file-ingestion trigger.

Therefore, integrating S3 event notifications with EventBridge and targeting the Lambda function best meets the requirement with the least added complexity.

The company is facing performance issues loading data into Amazon Redshift because it is issuing separate COPY commands for each data file location. The most efficient way to increase the speed of data ingestion into Redshift without increasing the cost is to use a manifest file.

Option D: Create a manifest file that contains the data file locations. Use a COPY command to load the data into Amazon Redshift.A manifest file provides a list of all the data files, allowing the COPY command to load all files in parallel from different locations in Amazon S3. This significantly improves the loading speed without adding costs, as it optimizes the data loading process in a single COPY operation.

Other options (A, B, C) involve additional steps that would either increase the cost (provisioning clusters, using Glue, etc.) or do not address the core issue of needing a unified and efficient COPY process.

Amazon RDS controls network-level access through security groups, which act as virtual firewalls. To allow an EC2 instance to connect to an RDS database using JDBC, the RDS security group must explicitly allow inbound traffic from the EC2 instance or its associated security group on the appropriate database port.

IAM roles do not control network connectivity to RDS databases. The ec2_authorized_hosts parameter does not exist for RDS engines. The Amazon RDS Data API is not supported for standard JDBC connections and is only available for Aurora Serverless v1 and v2.

Updating the RDS security group is the correct and simplest approach. By adding an inbound rule that allows traffic from the EC2 instance’s security group, the application can securely connect to the database without additional configuration or restarts.

Therefore, Option C is the correct solution.

The query editor v2 in Amazon Redshift is a web-based tool that allows users to run SQL queries and scripts on Amazon Redshift clusters. The query editor v2 supports creating and managing materialized views, which are precomputed results of a query that can improve the performance of subsequent queries. The query editor v2 also supports scheduling queries to run at specified intervals, which can be used to refresh materialized views automatically. This solution requires the least effort, as it does not involve any additional services, coding, or configuration. The other solutions are more complex and require more operational overhead. Apache Airflow is an open-source platform for orchestrating workflows, which can be used to refresh materialized views, but it requires setting up and managing an Airflow environment, creating DAGs (directed acyclic graphs) to define the workflows, and integrating with Amazon Redshift. AWS Lambda is a serverless compute service that can run code in response to events, which can be used to refresh materialized views, but it requires creating and deploying Lambda functions, defining UDFs within Amazon Redshift, and triggering the functions using events or schedules. AWS Glue is a fully managed ETL service that can run jobs to transform and load data, which can be used to refresh materialized views, but it requires creating and configuring Glue jobs, defining Glue workflows to orchestrate the jobs, and scheduling the workflows using triggers. References:

Query editor V2

Working with materialized views

Scheduling queries

[AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide]

Problem Analysis:

The pipeline processes compressed files in S3 and must support incremental data processing.

AWS Glue features must facilitate tracking progress to avoid reprocessing the same data.

Key Considerations:

Incremental data processing requires tracking which files or partitions have already been processed.

The solution must be automated and efficient for large-scale ETL jobs.

Solution Analysis:

Option A: Workflows

Workflows organize and orchestrate multiple Glue jobs but do not track progress for incremental data processing.

Option B: Triggers

Triggers initiate Glue jobs based on a schedule or events but do not track which data has been processed.

Option C: Job Bookmarks

Job bookmarks track the state of the data that has been processed, enabling incremental processing.

Automatically skip files or partitions that were previously processed in Glue jobs.

Option D: Classifiers

Classifiers determine the schema of incoming data but do not handle incremental processing.

Final Recommendation:

Job bookmarks are specifically designed to enable incremental data processing in AWS Glue ETL pipelines.

AWS Glue Job Bookmarks Documentation

AWS Glue ETL Features

Options A and B are correct because both are managed, serverless orchestration approaches for AWS Glue ETL pipelines.

AWS Step Functions is a serverless orchestration service that can coordinate AWS services, including Glue jobs, as a sequence of steps. AWS documentation states that Step Functions supports built-in error handling with Retry and Catch, which directly fits the requirement to coordinate ETL jobs and handle failures automatically. AWS Prescriptive Guidance also shows ETL pipelines that move data from Amazon S3 through AWS Glue and onward in a Step Functions workflow.

AWS Glue workflows are also correct because AWS documentation states that Glue workflows can create and visualize complex ETL activities involving multiple jobs, crawlers, and triggers. They provide a graph of dependencies and can manage chained execution of interdependent tasks. That matches the requirement for serverless ETL orchestration.

Option C is not serverless because it requires an always-on EC2 instance. Option D can start jobs from events, but it is not the best service for managing complex chained dependencies and workflow state. Option E is primarily a CI/CD orchestration service, not the best fit for ETL job orchestration.

For Amazon QuickSight to access data in an Amazon S3 bucket that is encrypted with an AWS KMS key in a different AWS account, two distinct permissions are required: access to the S3 bucket and access to the KMS key.

First, the QuickSight service role in the BI-Account must be granted permission to access the S3 bucket. This is accomplished by adding the S3 bucket as a resource that the QuickSight service role can access and configuring the bucket policy in the Hub-Account to trust that role. Without explicit S3 permissions, QuickSight cannot read the objects.

Second, because the S3 bucket uses SSE-KMS encryption, QuickSight must also be authorized to use the KMS key. This requires adding an IAM policy to the QuickSight service role that allows kms:Decrypt and related permissions on the KMS key. Without KMS permissions, S3 access will fail even if the bucket policy allows access.

AWS RAM cannot be used to share S3 buckets, encryption of network connections is irrelevant to KMS permissions, and adding the KMS key as a “resource” alone is not sufficient without IAM permissions.

Therefore, Options B and D are correct.

The requirement is to detect violations of data access policies and receive notifications with the username of the violator. AWS CloudTrail can provide object-level tracking for S3 to capture detailed API actions on specific S3 objects, including the user who performed the action.

AWS CloudTrail:

CloudTrail can monitor API calls made to an S3 bucket, including object-level API actions such as GetObject, PutObject, and DeleteObject. This will help detect access violations based on the API calls made by different users.

CloudTrail logs include details such as the user identity, which is essential for meeting the requirement of including the username in notifications.

The CloudTrail logs can be forwarded to Amazon CloudWatch to trigger alarms based on certain access patterns (e.g., violations of specific policies).

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

Amazon EC2 instances can use two types of storage volumes: instance store volumes and Amazon EBS volumes. Instance store volumes are ephemeral, meaning they are only attached to the instance for the duration of its life cycle. If the instance is stopped, terminated, or fails, the data on the instance store volume is lost. Amazon EBS volumes are persistent, meaning they can be detached from the instance and attached to another instance, and the data on the volume is preserved. To meet the requirement of persisting the data even if the EC2 instances are terminated, the data engineer must use Amazon EBS volumes to store the application data. The solution is to launch new EC2 instances by using an AMI that is backed by an EC2 instance store volume, which is the default option for most AMIs. Then, the data engineer must attach an Amazon EBS volume to each instance and configure the application to write the data to the EBS volume. This way, the data will be saved on the EBS volume and can be accessed by another instance if needed. The data engineer can apply the default settings to the EC2 instances, as there is no need to modify the instance type, security group, or IAM role for this solution. The other options are either not feasible or not optimal. Launching new EC2 instances by using an AMI that is backed by an EC2 instance store volume that contains the application data (option A) or by using an AMI that is backed by a root Amazon EBS volume that contains the application data (option B) would not work, as the data on the AMI would be outdated and overwritten by the new instances. Attaching an additional EC2 instance store volume to contain the application data (option D) would not work, as the data on the instance store volume would be lost if the instance is terminated. References:

Amazon EC2 Instance Store

Amazon EBS Volumes

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 2: Data Store Management, Section 2.1: Amazon EC2

The best solution to ensure that the AWS Glue Data Catalog synchronizes with the S3 storage when the company adds new partitions to the bucket with the least latency is to use code that writes data to Amazon S3 to invoke the Boto3 AWS Glue create partition API call. This way, the Data Catalog is updated as soon as new data is written to S3, and the partition information is immediately available for querying by other services. The Boto3 AWS Glue create partition API call allows you to create a new partition in the Data Catalog by specifying the table name, the database name, and the partition values1. You can use this API call in your code that writes data to S3, such as a Python script or an AWS Glue ETL job, to create a partition for each new S3 object key that matches the partitioning scheme.

Option A is not the best solution, as scheduling an AWS Glue crawler to run every morning would introduce a significant latency between the time new data is written to S3 and the time the Data Catalog is updated. AWS Glue crawlers are processes that connect to a data store, progress through a prioritized list of classifiers to determine the schema for your data, and then create metadata tables in the Data Catalog2. Crawlers can be scheduled to run periodically, such as daily or hourly, but they cannot run continuously or in real-time. Therefore, using a crawler to synchronize the Data Catalog with the S3 storage would not meet the requirement of the least latency.

Option B is not the best solution, as manually running the AWS Glue CreatePartition API twice each day would also introduce a significant latency between the time new data is written to S3 and the time the Data Catalog is updated. Moreover, manually running the API would require more operational overhead and human intervention than using code that writes data to S3 to invoke the API automatically.

Option D is not the best solution, as running the MSCK REPAIR TABLE command from the AWS Glue console would also introduce a significant latency between the time new data is written to S3 and the time the Data Catalog is updated. The MSCK REPAIR TABLE command is a SQL command that you can run in the AWS Glue console to add partitions to the Data Catalog based on the S3 object keys that match the partitioning scheme3. However, this command is not meant to be run frequently or in real-time, as it can take a long time to scan the entire S3 bucket and add the partitions. Therefore, using this command to synchronize the Data Catalog with the S3 storage would not meet the requirement of the least latency. References:

AWS Glue CreatePartition API

Populating the AWS Glue Data Catalog

MSCK REPAIR TABLE Command

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide

Problem Analysis:

The company processes 2 GB of daily sales records and 100 GB of Salesforce sales opportunities.

The goal is to analyze and correlate the two datasets with low operational overhead.

The process must run once nightly.

Key Considerations:

Amazon AppFlow simplifies data integration with Salesforce.

AWS Glue can extract data from MySQL and perform ETL operations.

Step Functions can orchestrate workflows with minimal manual intervention.

Apache Airflow and Flink add complexity, which conflicts with the requirement for low operational overhead.

Solution Analysis:

Option A: MWAA + Lambda + Step Functions

Requires custom Lambda code for dataset correlation, increasing development and operational complexity.

Option B: AppFlow + Glue + MWAA

MWAA adds orchestration overhead compared to the simpler Step Functions.

Option C: AppFlow + Glue + Step Functions

AppFlow fetches Salesforce data, Glue extracts MySQL data, and Step Functions orchestrate the entire process.

Minimal setup and operational overhead, making it the best choice.

Option D: AppFlow + Kinesis + Flink + Step Functions

Using Kinesis and Flink for batch processing introduces unnecessary complexity.

Final Recommendation:

Use Amazon AppFlow to fetch Salesforce data, AWS Glue to process MySQL data, and Step Functions for orchestration.

Amazon AppFlow Overview

AWS Glue ETL Documentation

AWS Step Functions

For processing the incoming car listings in a cost-effective, scalable, and automated way, the ideal approach involves using AWS Glue for data processing, AWS Lambda with S3 Event Notifications for orchestration, Amazon Athena for one-time queries and analytical reporting, and Amazon QuickSight for visualization on the dashboard. Let’s break this down:

AWS Glue: This is a fully managed ETL (Extract, Transform, Load) service that automatically processes the incoming data files. Glue is serverless and supports diverse data sources, including Amazon S3 and Redshift.

AWS Lambda and S3 Event Notifications: Using Lambda and S3 Event Notifications allows near real-time triggering of processing workflows as soon as new data is uploaded into S3. This approach is event-driven, ensuring that the listings are processed as soon as they are uploaded, reducing the latency for data processing.

Amazon Athena: A serverless, pay-per-query service that allows interactive queries directly against data in S3 using standard SQL. It is ideal for the requirement of one-time queries and analytical reporting without the need for provisioning or managing servers.

Amazon QuickSight: A business intelligence tool that integrates with a wide range of AWS data sources, including Athena, and is used for creating interactive dashboards. It scales well and provides real-time insights for the car listings.

This solution (Option D) is the most cost-effective, because both Glue and Athena are serverless and priced based on usage, reducing costs when compared to provisioning EMR clusters in the other options. Moreover, using Lambda for orchestration is more cost-effective than AWS Step Functions due to its lightweight nature.

To build an enterprise data catalog with metadata for storage formats, the easiest and most efficient solution is using an AWS Glue crawler. The Glue crawler can scan Amazon S3 buckets and Amazon RDS databases to automatically create a data catalog that includes metadata such as the schema and storage format (e.g., CSV, Parquet, etc.). By using AWS Glue crawler classifiers, you can configure the crawler to recognize the format of the data and store this information directly in the catalog.

Option B: Use an AWS Glue crawler to build a data catalog. Use AWS Glue crawler classifiers to recognize the format of data and store the format in the catalog.This option meets the requirements with the least effort because Glue crawlers automate the discovery and cataloging of data from multiple sources, including S3 and RDS, while recognizing various file formats via classifiers.

Other options (A, C, D) involve additional manual steps, like having data stewards inspect the data, or using services like Amazon Macie that focus more on sensitive data detection rather than format cataloging.

AWS Glue is a fully managed service that provides a serverless data integration platform for data preparation, data cataloging, and data loading. AWS Glue Studio is a graphical interface that allows you to easily author, run, and monitor AWS Glue ETL jobs. AWS Glue Data Quality is a feature that enables you to validate, cleanse, and enrich your data using predefined or custom rules. AWS Step Functions is a service that allows you to coordinate multiple AWS services into serverless workflows.

Using the Detect PII transform in AWS Glue Studio, you can automatically identify and label the PII in your dataset, such as names, addresses, phone numbers, email addresses, etc. You can then create a rule in AWS Glue Data Quality to obfuscate the PII, such as masking, hashing, or replacing the values with dummy data. You can also use other rules to validate and cleanse your data, such as checking for null values, duplicates, outliers, etc. You can then use an AWS Step Functions state machine to orchestrate a data pipeline to ingest the data into the S3 data lake. You can use AWS Glue DataBrew to visually explore and transform the data, AWS Glue crawlers to discover and catalog the data, and AWS Glue jobs to load the data into the S3 data lake.

This solution will meet the requirement with the least operational effort, as it leverages the serverless and managed capabilities of AWS Glue, AWS Glue Studio, AWS Glue Data Quality, and AWS Step Functions. You do not need to write any code to identify or obfuscate the PII, as you can use the built-in transforms and rules in AWS Glue Studio and AWS Glue Data Quality. You also do not need to provision or manage any servers or clusters, as AWS Glue and AWS Step Functions scale automatically based on the demand.

The other options are not as efficient as using the Detect PII transform in AWS Glue Studio, creating a rule in AWS Glue Data Quality, and using an AWS Step Functions state machine. Using an Amazon Kinesis Data Firehose delivery stream to process the dataset, creating an AWS Lambda transform function to identify the PII, using an AWS SDK to obfuscate the PII, and setting the S3 data lake as the target for the delivery stream will require more operational effort, as you will need to write and maintain code to identify and obfuscate the PII, as well as manage the Lambda function and its resources. Using the Detect PII transform in AWS Glue Studio to identify the PII, obfuscating the PII, and using an AWS Step Functions state machine to orchestrate a data pipeline to ingest the data into the S3 data lake will not be as effective as creating a rule in AWS Glue Data Quality to obfuscate the PII, as you will need to manually obfuscate the PII after identifying it, which can be error-prone and time-consuming. Ingesting the dataset into Amazon DynamoDB, creating an AWS Lambda function to identify and obfuscate the PII in the DynamoDB table and to transform the data, and using the same Lambda function to ingest the data into the S3 data lake will require more operational effort, as you will need to write and maintain code to identify and obfuscate the PII, as well as manage the Lambda function and its resources. You will also incur additional costs and complexity by using DynamoDB as an intermediate data store, which may not be necessary for your use case. References:

AWS Glue

AWS Glue Studio

AWS Glue Data Quality

[AWS Step Functions]

[AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide], Chapter 6: Data Integration and Transformation, Section 6.1: AWS Glue

AWS Glue ETL is designed for scalable and serverless data processing, and it supports integrated quality enforcement using AWS Glue Data Quality, which makes it the most cost-effective and integrated option when combined with Amazon RDS for MySQL as the relational database.

“AWS Glue can perform data validation as part of the ETL process, ensuring data quality before storing the data in the target data store.”

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

Using AWS Glue Data Quality directly in the ETL workflow is simpler and more cost-effective than separating transformation (Glue) and validation (DataBrew) into different services.

Option D is correct because the problem is lock contention between ETL writes and analytical reads on the same Redshift tables. AWS documents that table locks can block both reads and writes, and that Redshift data sharing lets you share live data across Redshift clusters and workgroups without manually moving or copying the data. Because the shared data is live, consumer users see the most up-to-date information as soon as it is updated. Separating ETL and analytics onto different Redshift clusters while using data sharing is therefore the cleanest way to isolate workloads and still provide consistent data views.

Option A reduces contention only by changing schedules and does not fully solve concurrent peak-hour conflicts. Option B is not correct because Redshift federated queries are read-only against external sources and do not provide a Redshift “read replica” design for this case. Option C does not address the locking problem on the dimension tables that ETL is updating. The official Redshift guidance and service capabilities point to workload isolation plus data sharing as the best fix. This also matches the exam guide’s emphasis on choosing architectures that maintain performance and consistency without unnecessary copying.

Option C is correct because the company only needs to keep the original files for 90 days and then delete them. AWS states that S3 Lifecycle can be used to delete expired objects automatically, which directly matches this requirement. There is no need to transition the objects to another storage class because the files are not needed beyond day 90. Adding a transition step would add complexity and, in this case, would not improve cost-effectiveness.

Option A is not optimal because S3 Glacier Flexible Retrieval has a 90-day minimum storage duration and is intended for longer-term archival. Transitioning objects right before deleting them is unnecessary overhead and can add minimum-duration charges. Option B is incorrect because Object Lock is for retention and write-once-read-many protection, not for normal temporary retention with lowest cost. Option D is also less cost-effective because S3 Intelligent-Tiering is useful when access patterns are uncertain, but here the retention period and deletion timing are already known. The simplest and cheapest solution is to keep the originals in S3 Standard for the required 90 days and then expire them with Lifecycle.

In AWS Glue’s SQL implementation (Spark SQL-compatible), the MERGE INTO statement supports conditional actions.

The clause WHEN MATCHED THEN DELETE deletes matching records from the target table (accounts) where the join condition is true.

“A MERGE INTO statement can perform updates, inserts, or deletes based on the match condition between source and target tables.”

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

Option C is correct because the main performance problem here is the large number of very small files. AWS Glue documentation explains that for Apache Iceberg tables, managed compaction reduces metadata overhead and improves read performance by compacting many small objects into larger ones. AWS Glue also provides table optimizers for Iceberg, and compaction can be configured to run automatically. This directly addresses the requirement that performance should not degrade as more files are added over time.

Option A could help somewhat, but it adds more operational work and the crawler step is unnecessary noise for this question. With Iceberg tables in the Glue Data Catalog, the better answer is the native automatic compaction capability rather than a manually scheduled Glue job. Option B is incorrect because the Data Catalog does not simply compact files “every minute” by default; compaction is enabled and configured through the Iceberg table optimizer settings. Option D is insufficient because partitioning alone does not solve the small-files problem, and frequent crawler runs do not prevent long-term Iceberg degradation. The AWS-native, least-operations answer is to enable automatic compaction based on thresholds.

Option C is the correct answer because the Map state is designed to process a collection of data in parallel by applying the same transformation to each element. The Map state can invoke a nested workflow for each element, which can be another state machine or a Lambda function. The Map state will wait until all the parallel executions are completed before moving to the next state.

Option A is incorrect because the Parallel state is used to execute multiple branches of logic concurrently, not to process a collection of data. The Parallel state can have different branches with different logic and states, whereas the Map state has only one branch that is applied to each element of the collection.

Option B is incorrect because the Choice state is used to make decisions based on a comparison of a value to a set of rules. The Choice state does not process any data or invoke any nested workflows.

Option D is incorrect because the Wait state is used to delay the state machine from continuing for a specified time. The Wait state does not process any data or invoke any nested workflows.

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 5: Data Orchestration, Section 5.3: AWS Step Functions, Pages 131-132

Building Batch Data Analytics Solutions on AWS, Module 5: Data Orchestration, Lesson 5.2: AWS Step Functions, Pages 9-10

AWS Documentation Overview, AWS Step Functions Developer Guide, Step Functions Concepts, State Types, Map State, Pages 1-3

AWS Step Functions is a fully managed, serverless orchestration service designed to coordinate multiple AWS services into reliable workflows. Step Functions natively provide automatic retries, error handling, state-level visibility, and execution history, which directly satisfy the requirements for retry logic and monitoring individual pipeline stages.

In this solution, AWS Lambda functions are used for batch data ingestion, AWS Glue jobs handle transformation, and Amazon S3 is used for storage. Step Functions orchestrate these stages while maintaining clear separation of responsibilities. Each step can be configured with retry and catch policies, allowing the workflow to automatically recover from transient failures without manual intervention.

Chaining AWS Glue jobs alone does not provide detailed stage-level visibility or flexible retry control, and setting MaxRetries to 0 contradicts the retry requirement. EventBridge-based pipelines lack native workflow state tracking. Amazon MWAA introduces unnecessary operational overhead, including environment management and Airflow maintenance.

Therefore, using AWS Step Functions to orchestrate Lambda and Glue provides the most scalable, observable, and low-maintenance solution.

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.

Option A minimizes administration because it keeps one base IAM role for the pipeline and enforces regional locality at session time by attaching a session policy when the broker calls AssumeRole. This aligns with the security best practice of applying only what is necessary, because permissions should be limited to the exact scope required for the session. The material explicitly emphasizes the principle of least privilege, stating that permissions “should apply only to what is necessary,” and the most appropriate approach is to grant only the minimum actions needed for the job.

With session policies, the organization avoids creating and maintaining separate per-Region roles (Option B), avoids managing users and group memberships for federated sessions (Option C), and avoids individually attaching policies per engineer (Option D). Instead, the broker can dynamically apply a Region-specific restriction for each login, ensuring the temporary credentials are valid only for the engineer’s working Region. This design reduces ongoing operational burden while still enforcing strict locality controls through temporary, scoped credentials and least-privilege boundaries.

Option A is correct because when many AWS Glue jobs start at the same time in the same subnet, Glue needs to create network interfaces in that subnet. If the subnet does not have enough free private IP addresses available, jobs can fail with exactly this type of error. AWS service documentation for VPC-based managed data services consistently notes that if there is no available free IP address in a specified subnet, the service cannot create or add the required ENIs and the workload can fail or degrade.

This is a subnet-level exhaustion issue, not a VPC-wide issue. A VPC can still have free addresses in other subnets while the specific subnet chosen for the jobs is out of usable addresses. That makes D incorrect. Option B is incorrect because G.1X is a valid Glue worker type and does not inherently prevent subnet access. Option C is also incorrect because the error is about address availability, not IAM authorization. In practice, this problem is usually resolved by using a larger subnet or spreading workloads across additional subnets so enough IPs are available when multiple Glue jobs launch concurrently. This aligns with the exam’s focus on networking capacity planning for managed data services.

AWS Database Migration Service (AWS DMS) is purpose-built to migrate and continuously replicate data from both AWS-hosted and on-premises databases. It supports full-load (existing data) and change data capture (CDC) for ongoing changes, making it the most cost-effective and operationally simple solution in this scenario.

“DMS supports both full-load and continuous replication via CDC. This enables replicating existing and future data from various sources to a data lake in Amazon S3.”

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

AWS Glue is not suitable for real-time CDC replication across hybrid environments.

 The best solutions to resolve the performance bottleneck and reduce Athena query planning time are to create an AWS Glue partition index and enable partition filtering, and to use Athena partition projection based on the S3 bucket prefix.

AWS Glue partition indexes are a feature that allows you to speed up query processing of highly partitioned tables cataloged in AWS Glue Data Catalog. Partition indexes are available for queries in Amazon EMR, Amazon Redshift Spectrum, and AWS Glue ETL jobs. Partition indexes are sublists of partition keys defined in the table. When you create a partition index, you specify a list of partition keys that already exist on a given table. AWS Glue then creates an index for the specified keys and stores it in the Data Catalog. When you run a query that filters on the partition keys, AWS Glue uses the partition index to quickly identify the relevant partitions without scanning the entire table metadata. This reduces the query planning time and improves the query performance1.

Athena partition projection is a feature that allows you to speed up query processing of highly partitioned tables and automate partition management. In partition projection, Athena calculates partition values and locations using the table properties that you configure directly on your table in AWS Glue. The table properties allow Athena to ‘project’, or determine, the necessary partition information instead of having to do a more time-consuming metadata lookup in the AWS Glue Data Catalog. Because in-memory operations are often faster than remote operations, partition projection can reduce the runtime of queries against highly partitioned tables. Partition projection also automates partition management because it removes the need to manually create partitions in Athena, AWS Glue, or your external Hive metastore2.

Option B is not the best solution, as bucketing the data based on a column that the data have in common in a WHERE clause of the user query would not reduce the query planning time. Bucketing is a technique that divides data into buckets based on a hash function applied to a column. Bucketing can improve the performance of join queries by reducing the amount of data that needs to be shuffled between nodes. However, bucketing does not affect the partition metadata retrieval, which is the main cause of the performance bottleneck in this scenario3.

Option D is not the best solution, as transforming the data that is in the S3 bucket to Apache Parquet format would not reduce the query planning time. Apache Parquet is a columnar storage format that can improve the performance of analytical queries by reducing the amount of data that needs to be scanned and providing efficient compression and encoding schemes. However, Parquet does not affect the partition metadata retrieval, which is the main cause of the performance bottleneck in this scenario4.

Option E is not the best solution, as using the Amazon EMR S3DistCP utility to combine smaller objects in the S3 bucket into larger objects would not reduce the query planning time. S3DistCP is a tool that can copy large amounts of data between Amazon S3 buckets or from HDFS to Amazon S3. S3DistCP can also aggregate smaller files into larger files to improve the performance of sequential access. However, S3DistCP does not affect the partition metadata retrieval, which is the main cause of the performance bottleneck in this scenario5. References:

Improve query performance using AWS Glue partition indexes

Partition projection with Amazon Athena

Bucketing vs Partitioning

Columnar Storage Formats

S3DistCp

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide

Option B is most cost-effective because it combines a serverless ETL service for overnight processing with a serverless warehouse for partner SQL access during business hours. The material describes AWS Glue as a “fully managed, serverless, scalable ETL service” that can extract, transform, and load data with minimal operational overhead, which fits the requirement for multiple transformations and data cleaning.

For the SQL access layer, the study material highlights the cost advantage of Amazon Redshift Serverless: it automatically provisions and scales capacity and you pay only for the compute capacity provisioned, with no compute costs when no workloads are running. That directly supports “multiple business partners” querying during business hours while avoiding the always-on cost of a provisioned cluster.

Options C and D use a provisioned Redshift cluster, which is less cost-effective because capacity must be paid for even when partners are not querying. Option A adds EMR cluster management cost and operational overhead compared to using Glue for ETL.

Step 1: Understand the Data Access Requirements

The question presents distinct access needs for three teams:

Marketing team: Needs full access to customer contact info but only obfuscated claim information.

Claims team: Needs access to customer information relevant to the claims they process.

Analytics team: Needs only obfuscated PII data.

These teams require different levels of access, and the solution needs to enforce data security while keeping administrative overhead low.

Step 2: Why Option B is Correct

Option B (Creating Views) is a common best practice in Amazon Redshift to restrict access to specific data without duplicating data or managing multiple clusters. By creating views:

You can define customized views of the data with obfuscated fields for the analytics team and marketing team while still providing full access where necessary.

Views provide a logical separation of data and allow Redshift administrators to grant access permissions based on roles or groups, ensuring that each team sees only what they are allowed to.

Obfuscation or masking of PII can be easily applied to the views by transforming or hiding sensitive data fields.

This approach avoids the complexity of managing multiple Redshift clusters or S3-based data lakes, which introduces higher operational and administrative overhead.

Step 3: Why Other Options Are Not Ideal

Option A (Separate Redshift Clusters) introduces unnecessary administrative overhead by managing multiple clusters. Maintaining several clusters for each team is costly, redundant, and inefficient.

Option C (Separate Redshift Roles) involves creating multiple roles and managing complex masking policies, which adds to administrative burden and complexity. While Redshift does support column-level access control, it ' s still more overhead than managing simple views.

Option D (Move to S3 and Lake Formation) is a more complex and heavy-handed solution, especially when the data is already stored in Redshift. Migrating the data to S3 and setting up a data lake with Lake Formation introduces significant operational complexity that isn ' t needed for this specific requirement.

Conclusion:

Creating views in Amazon Redshift allows for flexible, fine-grained access control with minimal overhead, making it the optimal solution to meet the data access requirements of the marketing, claims, and analytics teams.

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

Option B is the most operationally efficient because it checks the business requirement directly: whether the target table contains the most recent data, not merely whether a job failed. Monitoring only failures (Options C and D) can produce false positives (a job failure might not impact freshness) and false negatives (a job can succeed but still load stale or incomplete data). The study material emphasizes implementing data quality validation as part of the ETL process so data can be verified before or as it is stored, rather than relying only on pipeline execution status.

Using a data quality rule focused on freshness (for example, validating that a “max event timestamp” or “latest partition date” meets today’s expected value) lets the pipeline detect stale loads even when the workflow runs. Then, an EventBridge rule can route failures of that data quality check to SNS for immediate notification, keeping operations serverless and centralized. Macie (Option A) is designed for sensitive-data discovery/classification, not operational “freshness” checks on Redshift tables, so it adds unnecessary services and effort compared to a Glue-native data quality validation approach.

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.

AWS Glue Workflows can coordinate multiple ETL jobs and triggers. They support parallel execution and sequential dependencies, which is ideal for concurrent data processing followed by correlation steps, all with minimal operational overhead.

“Use AWS Glue Workflows to orchestrate multiple ETL jobs in sequence or in parallel, supporting conditional triggers and dependency management.”

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

Option A (AWS DMS): Lets you replicate only selected tables from Aurora to Redshift. It supports ongoing replication (CDC) and reduces unnecessary data transfers.

Option C (Zero-ETL with Aurora to Redshift): This new integration allows real-time, serverless, and low-maintenance data replication. It ' s designed to reduce operational overhead drastically.

Other options introduce manual processing or inefficient query access (Option D). Glue workflows (Option E) add unnecessary complexity and lag for near-real-time needs.

“Zero-ETL integration between Amazon Aurora and Redshift enables real-time analytics without building complex pipelines.”

The ecommerce company wants to migrate its data pipelines into the AWS Cloud without managing servers, and the solution must orchestrate Python and Bash scripts without refactoring code. Amazon Managed Workflows for Apache Airflow (Amazon MWAA) is the most suitable solution for this scenario.

Option B: Amazon Managed Workflows for Apache Airflow (Amazon MWAA)MWAA is a managed orchestration service that supports Python and Bash scripts via Directed Acyclic Graphs (DAGs) for workflows. It is a serverless, managed version of Apache Airflow, which is commonly used for orchestrating complex data workflows, making it an ideal choice for migrating existing pipelines without refactoring. It supports Python, Bash, and other scripting languages, and the company would not need to manage the underlying infrastructure.

Other options:

AWS Lambda (Option A) is more suited for event-driven workflows but would require breaking down the pipeline into individual Lambda functions, which may require refactoring.

AWS Step Functions (Option C) is good for orchestration but lacks native support for Python and Bash without using Lambda functions, and it may require code changes.

AWS Glue (Option D) is an ETL service primarily for data transformation and not suitable for orchestrating general scripts without modification.

To ensure that Branch B is up to date with the latest changes in the master branch before submitting a pull request, the correct approach is to perform a git rebase. This command rewrites the commit history so that Branch B will be based on the latest changes in the master branch.

git rebase master:

This command moves the commits of Branch B to be based on top of the latest state of the master branch. It allows the developer to resolve any conflicts and create a clean history.

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 Redshift UNLOAD command is specifically designed to export query results to Amazon S3, and AWS Glue can orchestrate this as part of a scheduled job. This is the cleanest and most appropriate approach for recurring weekly data transfers:

“Use the Redshift UNLOAD command with AWS Glue to export data to Amazon S3. This pattern enables routine exports of selected data to external locations.”

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

This avoids complexities of Redshift Spectrum or unsupported use of COPY commands in Lambda.

AWS Lake Formation is a service that helps you build, secure, and manage data lakes on Amazon S3. You can use AWS Lake Formation to register the S3 path as a data lake location, and enable fine-grained access control to limit access to the records based on the HR department’s Region. You can use data filters to specify which S3 prefixes or partitions each HR department can access, and grant permissions to the IAM roles of the HR departments accordingly. This solution will meet the requirement with the least operational overhead, as it simplifies the data lake management and security, and leverages the existing IAM roles of the HR departments12.

The other options are not optimal for the following reasons:

A. Use data filters for each Region to register the S3 paths as data locations. This option is not possible, as data filters are not used to register S3 paths as data locations, but to grant permissions to access specific S3 prefixes or partitions within a data location. Moreover, this option does not specify how to limit access to the records based on the HR department’s Region.

C. Modify the IAM roles of the HR departments to add a data filter for each department’s Region. This option is not possible, as data filters are not added to IAM roles, but to permissions granted by AWS Lake Formation. Moreover, this option does not specify how to register the S3 path as a data lake location, or how to enable fine-grained access control in AWS Lake Formation.

E. Create a separate S3 bucket for each Region. Configure an IAM policy to allow S3 access. Restrict access based on Region. This option is not recommended, as it would require more operational overhead to create and manage multiple S3 buckets, and to configure and maintain IAM policies for each HR department. Moreover, this option does not leverage the benefits of AWS Lake Formation, such as data cataloging, data transformation, and data governance.

1: AWS Lake Formation

2: AWS Lake Formation Permissions

AWS Identity and Access Management

Amazon S3

The optimal solution for near-real-time querying and visualization of logs is to integrate Amazon CloudWatch Logs with Amazon OpenSearch Service using subscription filters, which stream the logs directly into OpenSearch for querying and dashboarding:

“Use OpenSearch Service with CloudWatch Logs and create a subscription filter to stream log data in near real time into OpenSearch. Then use OpenSearch dashboards for visualization.”

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

This approach offers low latency and avoids batch exports, unlike the scheduled Athena + S3 pattern.

Amazon SageMaker ML Lineage Tracking provides a unified mechanism to automatically capture and track the lineage of ML artifacts (datasets, jobs, and models). It supports auditability, reproducibility, and compliance by maintaining metadata for every stage of the ML lifecycle.

“Use Amazon SageMaker Lineage Tracking to automatically record and track the lineage of data, training jobs, models, and endpoints to ensure auditability and reproducibility.”

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

To migrate data from an Amazon RDS for PostgreSQL DB instance in the eu-east-1 Region (Account_A) to an Amazon Redshift cluster in the eu-west-1 Region (Account_B), AWS DMS needs a replication instance located in the target region (in this case, eu-west-1) to facilitate the data transfer between regions.

Option A: Set up an AWS DMS replication instance in Account_B in eu-west-1.Placing the DMS replication instance in the target account and region (Account_B in eu-west-1) is the most efficient solution. The replication instance can connect to the source RDS PostgreSQL in eu-east-1 and migrate the data to the Redshift cluster in eu-west-1. This setup ensures data is replicated across AWS accounts and regions.

Options B, C, and D place the replication instance in either the wrong account or region, which increases complexity without adding any benefit.

Step 1: Understanding the Problem

The company collects clickstream data via Amazon Kinesis Data Streams and stores it in JSON format in Amazon S3 using Kinesis Data Firehose. They use Amazon Athena to query the data, but they want to reduce Athena costs while maintaining the same data pipeline.

Since Athena charges based on the amount of data scanned during queries, reducing the data size (by converting JSON to a more efficient format like Apache Parquet) is a key solution to lowering costs.

Step 2: Why Option A is Correct

Option A provides a straightforward way to reduce costs with minimal management overhead:

Changing the Firehose output format to Parquet: Parquet is a columnar data format, which is more compact and efficient than JSON for Athena queries. It significantly reduces the amount of data scanned, which in turn reduces Athena query costs.

Custom S3 Object Prefix (YYYYMMDD): Adding a date-based prefix helps in partitioning the data, which further improves query efficiency in Athena by limiting the data scanned to only relevant partitions.

AWS Glue ETL Job for Existing Data: To handle existing data stored in JSON format, a one-time AWS Glue ETL job can combine small JSON files, convert them to Parquet, and apply the YYYYMMDD prefix. This ensures consistency in the S3 bucket structure and allows Athena to efficiently query historical data.

ALTER TABLE ADD PARTITION: This command updates Athena ' s table metadata to reflect the new partitions, ensuring that future queries target only the required data.

Step 3: Why Other Options Are Not Ideal

Option B (Apache Spark on EMR) introduces higher management effort by requiring the setup of Apache Spark jobs and an Amazon EMR cluster. While it achieves the goal of converting JSON to Parquet, it involves running and maintaining an EMR cluster, which adds operational complexity.

Option C (Kinesis and Apache Flink) is a more complex solution involving Apache Flink, which adds a real-time streaming layer to aggregate data. Although Flink is a powerful tool for stream processing, it adds unnecessary overhead in this scenario since the company already uses Kinesis Data Firehose for batch delivery to S3.

Option D (AWS Lambda with Firehose) suggests using AWS Lambda to convert records in real time. While Lambda can work in some cases, it ' s generally not the best tool for handling large-scale data transformations like JSON-to-Parquet conversion due to potential scaling and invocation limitations. Additionally, running parallel Glue jobs further complicates the setup.

Step 4: How Option A Minimizes Costs

By using Apache Parquet, Athena queries become more efficient, as Athena will scan significantly less data, directly reducing query costs.

Firehose natively supports Parquet as an output format, so enabling this conversion in Firehose requires minimal effort. Once set, new data will automatically be stored in Parquet format in S3, without requiring any custom coding or ongoing management.

The AWS Glue ETL job for historical data ensures that existing JSON files are also converted to Parquet format, ensuring consistency across the data stored in S3.

Conclusion:

Option A meets the requirement to reduce Athena costs without recreating the data pipeline, using Firehose’s native support for Apache Parquet and a simple one-time AWS Glue ETL job for existing data. This approach involves minimal management effort compared to the other solutions.

The DatasetMatch rule in DQDL checks for full value equivalence between mapped fields. A value of 1.0 indicates a 100% match. The correct syntax and metric for an exact match scenario are:

“Use DatasetMatch when comparing mapped fields between two datasets. The comparison score of 1.0 confirms a perfect match.”

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

Options with “100” use incorrect syntax since DQDL uses floating-point scores (e.g., 1.0, 0.95), not percentages.

For high-performance analytics in Athena and EMR Spark, the columnar storage formats Parquet and ORC deliver the shortest query times. These formats minimize I/O, enable predicate pushdown, and support efficient compression and parallel reads.

When integrated with the AWS Glue Data Catalog, schema evolution is automatically tracked and managed across queries.

This combination (Parquet/ORC + Glue Catalog) is explicitly recommended by AWS for analytical workloads on S3 data lakes.

“For analytical workloads using Amazon EMR and Athena, store data in columnar formats such as Parquet or ORC. These formats provide optimized compression and query performance.”

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

“Use the AWS Glue Data Catalog to manage table schemas and schema evolution for columnar data formats such as Parquet and ORC.”

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

Therefore, using ORC or Parquet along with the Glue Data Catalog achieves the shortest Athena query times and seamless schema management.

Problem Analysis:

The company needs to migrate both an application and an on-premises Apache Kafka server to AWS.

Incremental updates from an on-premises Oracle database are processed by Kafka.

The solution must follow a replatform migration strategy, prioritizing minimal changes and low management overhead.

Key Considerations:

Replatform Strategy: This approach keeps the application and architecture as close to the original as possible, reducing the need for refactoring.

The solution must provide a managed Kafka service to minimize operational burden.

Low overhead solutions like serverless services are preferred.

Solution Analysis:

Option A: Kinesis Data Streams

Kinesis Data Streams is an AWS-native streaming service but is not a direct substitute for Kafka.

This option would require significant application refactoring, which does not align with the replatform strategy.

Option B: MSK Provisioned Cluster

Managed Kafka service with fully configurable clusters.

Provides the same Kafka APIs but requires cluster management (e.g., scaling, patching), increasing management overhead.

Option C: Amazon Kinesis Data Firehose

Kinesis Data Firehose is designed for data delivery rather than real-time streaming and processing.

Not suitable for Kafka-based applications.

Option D: MSK Serverless

MSK Serverless eliminates the need for cluster management while maintaining compatibility with Kafka APIs.

Automatically scales based on workload, reducing operational overhead.

Ideal for replatform migrations, as it requires minimal changes to the application.

Final Recommendation:

Amazon MSK Serverless is the best solution for migrating the Kafka server and application with minimal changes and the least management overhead.

Amazon MSK Serverless Overview

Comparison of Amazon MSK and Kinesis

Option A is the most operationally efficient way to meet the requirements because it minimizes the number of steps and services involved in the data export process. AWS Glue is a fully managed service that can extract, transform, and load (ETL) data from various sources to various destinations, including Amazon S3. AWS Glue can also convert data to different formats, such as Parquet, which is a columnar storage format that is optimized for analytics. By creating a view in the SQL Server databases that contains the required data elements, the AWS Glue job can select the data directly from the view without having to perform any joins or transformations on the source data. The AWS Glue job can then transfer the data in Parquet format to an S3 bucket and run on a daily schedule.

Option B is not operationally efficient because it involves multiple steps and services to export the data. SQL Server Agent is a tool that can run scheduled tasks on SQL Server databases, such as executing SQL queries. However, SQL Server Agent cannot directly export data to S3, so the query output must be saved as .csv objects on the EC2 instance. Then, an S3 event must be configured to trigger an AWS Lambda function that can transform the .csv objects to Parquet format and upload them to S3. This option adds complexity and latency to the data export process and requires additional resources and configuration.

Option C is not operationally efficient because it introduces an unnecessary step of running an AWS Glue crawler to read the view. An AWS Glue crawler is a service that can scan data sources and create metadata tables in the AWS Glue Data Catalog. The Data Catalog is a central repository that stores information about the data sources, such as schema, format, and location. However, in this scenario, the schema and format of the data elements are already known and fixed, so there is no need to run a crawler to discover them. The AWS Glue job can directly select the data from the view without using the Data Catalog. Running a crawler adds extra time and cost to the data export process.

Option D is not operationally efficient because it requires custom code and configuration to query the databases and transform the data. An AWS Lambda function is a service that can run code in response to events or triggers, such as Amazon EventBridge. Amazon EventBridge is a service that can connect applications and services with event sources, such as schedules, and route them to targets, such as Lambda functions. However, in this scenario, using a Lambda function to query the databases and transform the data is not the best option because it requires writing and maintaining code that uses JDBC to connect to the SQL Server databases, retrieve the required data, convert the data to Parquet format, and transfer the data to S3. This option also has limitations on the execution time, memory, and concurrency of the Lambda function, which may affect the performance and reliability of the data export process.

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide

AWS Glue Documentation

Working with Views in AWS Glue

Converting to Columnar Formats

Problem Analysis:

The company must log all user activities and connection activities in Amazon Redshift for security compliance.

Key Considerations:

Redshift supports audit logging, which can be configured to write logs to an S3 bucket.

S3 provides durable, scalable, and cost-effective storage for logs.

Solution Analysis:

Option A: S3 for Logging

Standard approach for storing Redshift logs.

Easy to set up and manage with minimal cost.

Option B: Amazon EFS

EFS is unnecessary for this use case and less cost-efficient than S3.

Option C: Aurora MySQL

Using a database to store logs increases complexity and cost.

Option D: EBS Volume

EBS is not a scalable option for log storage compared to S3.

Final Recommendation:

Enable Redshift audit logging and specify an S3 bucket as the destination.

Amazon Redshift Audit Logging

Storing Logs in Amazon S3

 The Amazon Redshift Data API is a built-in feature that allows you to run SQL queries on Amazon Redshift data with web services-based applications, such as AWS Lambda, Amazon SageMaker notebooks, and AWS Cloud9. The Data API does not require a persistent connection to your database, and it provides a secure HTTP endpoint and integration with AWS SDKs. You can use the endpoint to run SQL statements without managing connections. The Data API also supports both Amazon Redshift provisioned clusters and Redshift Serverless workgroups. The Data API is the best solution for running real-time queries on the financial data from within the trading application, as it has the least operational overhead compared to the other options.

Option A is not the best solution, as establishing WebSocket connections to Amazon Redshift would require more configuration and maintenance than using the Data API. WebSocket connections are also not supported by Amazon Redshift clusters or serverless workgroups.

Option C is not the best solution, as setting up JDBC connections to Amazon Redshift would also require more configuration and maintenance than using the Data API. JDBC connections are also not supported by Redshift Serverless workgroups.

Option D is not the best solution, as storing frequently accessed data in Amazon S3 and using Amazon S3 Select to run the queries would introduce additional latency and complexity than using the Data API. Amazon S3 Select is also not optimized for real-time queries, as it scans the entire object before returning the results. References:

Using the Amazon Redshift Data API

Calling the Data API

Amazon Redshift Data API Reference

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide

AWS Database Migration Service (AWS DMS) is a service that helps you migrate databases to AWS quickly and securely. You can use AWS DMS to migrate the Hive metastore from the on-premises Hadoop clusters into Amazon S3, which is a highly scalable, durable, and cost-effective object storage service. AWS Glue Data Catalog is a serverless, managed service that acts as a central metadata repository for your data assets. You can use AWS Glue Data Catalog to scan the Amazon S3 bucket that contains the migrated Hive metastore and create a data catalog that is compatible with Apache Hive and other AWS services. This solution meets the requirements of migrating the data catalog into a persistent storage solution and using a serverless solution. This solution is also the most cost-effective, as it does not incur any additional charges for running Amazon EMR or Amazon Aurora MySQL clusters. The other options are either not feasible or not optimal. Configuring a Hive metastore in Amazon EMR (option B) or an external Hive metastore in Amazon EMR (option C) would require running and maintaining Amazon EMR clusters, which would incur additional costs and complexity. Using Amazon Aurora MySQL to store the company’s data catalog (option C) would also incur additional costs and complexity, as well as introduce compatibility issues with Apache Hive. Configuring a new Hive metastore in Amazon EMR (option D) would not migrate the existing data catalog, but create a new one, which would result in data loss and inconsistency. References:

Using AWS Database Migration Service

Populating the AWS Glue Data Catalog

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 4: Data Analysis and Visualization, Section 4.2: AWS Glue Data Catalog

Athena data source is a solution that allows you to use Spark to access Athena by using the Athena JDBC driver and the Spark SQL interface. You can use the Athena data source to create Spark DataFrames from Athena tables, run SQL queries on the DataFrames, and write the results back to Athena. The Athena data source supports various data formats, such as CSV, JSON, ORC, and Parquet, and also supports partitioned and bucketed tables. The Athena data source is a cost-effective and scalable way to use Spark to access Athena, as it does not require any additional infrastructure or services, and you only pay for the data scanned by Athena.

The other options are not solutions that give the company the ability to use Spark to access Athena. Option A, Athena query settings, is a feature that allows you to configure various parameters for your Athena queries, such as the output location, the encryption settings, the query timeout, and the workgroup. Option B, Athena workgroup, is a feature that allows you to isolate and manage your Athena queries and resources, such as the query history, the query notifications, the query concurrency, and the query cost. Option D, Athena query editor, is a feature that allows you to write and run SQL queries on Athena using the web console or the API. None of these options enable you to use Spark instead of SQL to generate analytics on Athena. References:

Using Apache Spark in Amazon Athena

Athena JDBC Driver

Spark SQL

Athena query settings

[Athena workgroups]

[Athena query editor]

AWS Glue DataBrew provides a no-code data preparation and validation interface. It allows you to set data profiling, completeness checks, and numeric range validations directly through its UI—ideal for IoT validation use cases.

“AWS Glue DataBrew enables users to define validation rules such as completeness and value range checks without writing code.”

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

This fulfills the requirement for minimal coding and automatic data quality flagging.

Option D provides the lowest runtime because it uses a table format designed for efficient incremental upserts on Amazon S3, rather than repeatedly scanning and rewriting large portions of a 1 TB dataset. Although Spark on its own (Options A and C) can perform joins/merges, updating files stored in S3 typically requires expensive rewrites, especially as data grows. By contrast, Apache Hudi is purpose-built for maintaining large datasets on object storage with incremental updates, which directly fits “update up to 10,000 records every day” without reprocessing the full historical footprint.

For the compute layer, the document highlights that Amazon EMR provides a fully managed environment for running Apache Spark and other big data frameworks to process and analyze large datasets, making it appropriate for high-scale processing where performance matters. This is a better fit than using Pandas on 1 TB (Option B), which is not designed for distributed processing at that scale.

Therefore, combining EMR + Spark with an incremental storage framework (Hudi) is the most runtime-efficient approach for daily record-level updates on S3.

To reclaim the most storage space from a materialized view in Amazon Redshift, you should use a DELETE operation that removes all rows from the view. The most efficient way to remove all rows is to use a condition that always evaluates to true, such as 1=1. This will delete all rows without needing to evaluate each row individually based on specific column values like load_date.

Option A: DELETE FROM materialized_view_name WHERE 1=1;This statement will delete all rows in the materialized view and free up the space. Since materialized views in Redshift store precomputed data, performing a DELETE operation will remove all stored rows.

Other options either involve inappropriate SQL statements (e.g., VACUUM in option C is used for reclaiming storage space in tables, not materialized views), or they don’t remove data effectively in the context of a materialized view (e.g., TRUNCATE cannot be used directly on a materialized view).