Data-Engineer-Associate AWS Certified Data Engineer - Associate (DEA-C01) Question and Answers

 AWS DataSync is an online data movement and discovery service that simplifies and accelerates data migrations to AWS as well as moving data to and from on-premises storage, edge locations, other cloud providers, and AWS Storage services1. AWS DataSync can copy data to and from various sources and targets, including Amazon S3, and handle files in multiple formats. AWS DataSync also supports incremental transfers, meaning it can detect and copy only the changes to the data, reducing the amount of data transferred and improving the performance. AWS DataSync can automate and schedule the transfer process using triggers, and monitor the progress and status of the transfers using CloudWatch metrics and events1.

AWS DataSync is the most operationally efficient way to transfer the data in this scenario, as it meets all the requirements and offers a serverless and scalable solution. AWS Glue, AWS Direct Connect, and Amazon S3 Transfer Acceleration are not the best options for this scenario, as they have some limitations or drawbacks compared to AWS DataSync. AWS Glue is a serverless ETL service that can extract, transform, and load data from various sources to various targets, including Amazon S32. However, AWS Glue is not designed for large-scale data transfers, as it has some quotas and limits on the number and size of files it can process3. AWS Glue also does not support incremental transfers, meaning it would have to copy the entire data set every time, which would be inefficient and costly.

AWS Direct Connect is a service that establishes a dedicated network connection between your on-premises data center and AWS, bypassing the public internet and improving the bandwidth and performance of the data transfer. However, AWS Direct Connect is not a data transfer service by itself, as it requires additional services or tools to copy the data, such as AWS DataSync, AWS Storage Gateway, or AWS CLI. AWS Direct Connect also has some hardware and location requirements, and charges you for the port hours and data transfer out of AWS.

Amazon S3 Transfer Acceleration is a feature that enables faster data transfers to Amazon S3 over long distances, using the AWS edge locations and optimized network paths. However, Amazon S3 Transfer Acceleration is not a data transfer service by itself, as it requires additional services or tools to copy the data, such as AWS CLI, AWS SDK, or third-party software. Amazon S3 Transfer Acceleration also charges you for the data transferred over the accelerated endpoints, and does not guarantee a performance improvement for every transfer, as it depends on various factors such as the network conditions, the distance, and the object size. References:

AWS DataSync

AWS Glue

AWS Glue quotas and limits

[AWS Direct Connect]

[Data transfer options for AWS Direct Connect]

[Amazon S3 Transfer Acceleration]

[Using Amazon S3 Transfer Acceleration]

Amazon Kinesis Data Streams is a service that enables you to collect, process, and analyze real-time streaming data. You can use Kinesis Data Streams to ingest data from various sources, such as Amazon CloudWatch Logs, and deliver it to different destinations, such as Amazon S3 or Amazon Redshift. To use Kinesis Data Streams to deliver the security logs from the production AWS account to the security AWS account, you need to create a destination data stream in the security AWS account. This data stream will receive the log data from the CloudWatch Logs service in the production AWS account. To enable this cross-account data delivery, you need to create an IAM role and a trust policy in the security AWS account. The IAM role defines the permissions that the CloudWatch Logs service needs to put data into the destination data stream. The trust policy allows the production AWS account to assume the IAM role. Finally, you need to create a subscription filter in the production AWS account. A subscription filter defines the pattern to match log events and the destination to send the matching events. In this case, the destination is the destination data stream in the security AWS account. This solution meets the requirements of using Kinesis Data Streams to deliver the security logs to the security AWS account. The other options are either not possible or not optimal. You cannot create a destination data stream in the production AWS account, as this would not deliver the data to the security AWS account. You cannot create a subscription filter in the security AWS account, as this would not capture the log events from the production AWS account. References:

Using Amazon Kinesis Data Streams with Amazon CloudWatch Logs

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 3: Data Ingestion and Transformation, Section 3.3: Amazon Kinesis Data Streams

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

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

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

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.

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.

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

AWS Lake Formation

Row-Level Security

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

The correct answer is B because the workload requires complex processing that can run for 5 to 30 minutes, automatic scaling, and exactly-once-style avoidance of reprocessing source files. AWS Glue jobs are designed for serverless ETL processing over S3 data, and job bookmarks track previously processed source data so the job processes only new data on later runs. AWS documentation states that Glue job bookmarks help prevent reprocessing of old data and can track S3 source files for supported formats. Lambda is a poor fit because it has execution-duration limits and is not ideal for 30-minute complex transformations. EMR and EC2 polling add cluster and infrastructure management. Glue jobs with bookmarks provide the least operational overhead for this data engineering pattern.

In Amazon Managed Workflows for Apache Airflow (MWAA), the type of log that is most useful for diagnosing workflow (DAG) failures is the Task logs. These logs provide detailed information on the execution of each task within the DAG, including error messages, exceptions, and other critical details necessary for diagnosing failures.

Option D: YourEnvironmentName-TaskTask logs capture the output from the execution of each task within a workflow (DAG), which is crucial for understanding what went wrong when a DAG fails. These logs contain detailed execution information, including errors and stack traces, making them the best source for debugging.

Other options (WebServer, Scheduler, and DAGProcessing logs) provide general environment-level logs or logs related to scheduling and DAG parsing, but they do not provide the granular task-level execution details needed for diagnosing workflow failures.

The correct answer is A because Athena can query new partitions only after the partitions exist in the AWS Glue Data Catalog. Waiting for a nightly crawler creates unnecessary delay. The direct fix is to update the Data Catalog as part of the ETL process by calling the AWS Glue CreatePartition API immediately after each new S3 partition is written. AWS Glue documentation states that CreatePartition creates a new partition and requires the database name, table name, and partition input structure. Copying data to another bucket does not register partitions. DynamoDB Streams is unrelated unless the partition lifecycle is already stored in DynamoDB. StartImportLabelsTaskRun is not the API for partition registration. The correct operational pattern is event/write partition, then immediately register metadata.

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

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

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

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

Step 1: Understanding the Data Use Case

The company has data stored in an Amazon S3 bucket and needs to provide teams access for analysis, ensuring that PII data is not included in the analysis. The solution should be simple to implement and maintain, ensuring minimal operational overhead.

Step 2: Why Option D is Correct

Option D (AWS Glue DataBrew) allows you to visually prepare and transform data without needing to write code. By using a DataBrew job, the company can:

Automatically detect and separate PII data from non-PII data.

Store PII data in a second S3 bucket for security, while keeping the original S3 bucket clean for analysis.

This approach keeps operational overhead low by utilizing DataBrew ' s pre-built transformations and the easy-to-use interface for non-technical users. It also ensures compliance by separating sensitive PII data from the main dataset.

Step 3: Why Other Options Are Not Ideal

Option A (Amazon Macie) is a powerful tool for detecting sensitive data, but Macie doesn ' t inherently remove or mask PII. You would still need additional steps to clean the data after Macie identifies PII.

Option B (S3 Object Lambda with Amazon Comprehend) introduces more complexity by requiring custom logic at the point of data access. Amazon Comprehend can detect PII, but using S3 Object Lambda to filter data would involve more overhead.

Option C (Kinesis Data Firehose and Comprehend) is more suitable for real-time streaming data use cases rather than batch analysis. Setting up and managing a streaming solution like Kinesis adds unnecessary complexity.

Conclusion:

Using AWS Glue DataBrew provides a low-overhead, no-code solution to detect and separate PII data, ensuring the analysis teams only have access to non-sensitive data. This approach is simple, compliant, and easy to manage compared to other options.

Option B best meets a highly resilient DR requirement with an RTO under 1 hour because a Multi-AZ deployment is designed to provide rapid recovery through redundancy across Availability Zones, rather than requiring a restore-and-rebuild process after a failure. A snapshot-based strategy (Option C) can be valuable for backup and regional recovery, but restoring a new cluster from snapshots is a heavier operation and is not the most reliable path to consistently achieving sub-hour RTO during real incidents. The study material highlights snapshot/restore as an operational mechanism for creating a new environment from a Redshift cluster’s saved state, which inherently implies a restore process and associated time to bring resources online.

Option D (cluster relocation) is primarily an operational move mechanism and is not the same as maintaining continuously resilient capacity for unplanned AZ-impacting events. Option A is not the best choice because Multi-AZ resiliency is aligned with modern Redshift architectures, and RA3 is the recommended node family for contemporary Redshift deployments where performance and managed storage characteristics are key for enterprise analytics. The study material reinforces Amazon Redshift as the platform for high-performance enterprise analytics, so a resilience-first configuration is appropriate.

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.

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.

The Amazon Redshift Data API enables you to interact with your Amazon Redshift data warehouse in an easy and secure way. You can use the Data API to run SQL commands, such as loading data into tables, without requiring a persistent connection to the cluster. The Data API also integrates with Amazon EventBridge, which allows you to monitor the execution status of your SQL commands and trigger actions based on events. By using the Data API to publish an event to EventBridge, the data engineer can invoke the Lambda function that writes the load statuses to the DynamoDB table. This solution is scalable, reliable, and cost-effective. The other options are either not possible or not optimal. You cannot use a second Lambda function to invoke the first Lambda function based on CloudWatch or CloudTrail events, as these services do not capture the load status of Redshift tables. You can use the Data API to publish a message to an SQS queue, but this would require additional configuration and polling logic to invoke the Lambda function from the queue. This would also introduce additional latency and cost. References:

Using the Amazon Redshift Data API

Using Amazon EventBridge with Amazon Redshift

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

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

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

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

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

From the study guide:

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

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

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

Amazon Neptune supports graph applications using Gremlin and SPARQL as query languages. Neptune is a fully managed graph database service that supports both property graph and RDF graph models.

Option A: GremlinGremlin is a query language for property graph databases, which is supported by Amazon Neptune. It allows the traversal and manipulation of graph data in the property graph model.

Option D: SPARQLSPARQL is a query language for querying RDF graph data in Neptune. It is used to query, manipulate, and retrieve information stored in RDF format.

Other options:

SQL (Option B) and ANSI SQL (Option C) are traditional relational database query languages and are not used for graph databases.

Spark SQL (Option E) is related to Apache Spark for big data processing, not for querying graph databases.

To create a new table named cities_usa in Amazon Athena based on a subset of data from the existing cities_world table, you should use an INSERT INTO statement combined with a SELECT statement to filter only the records where the country is ' usa ' . The correct SQL syntax would be:

Option A: INSERT INTO cities_usa (city, state) SELECT city, state FROM cities_world WHERE country= ' usa ' ;This statement inserts only the cities and states where the country column has a value of ' usa ' from the cities_world table into the cities_usa table. This is a correct approach to create a new table with data filtered from an existing table in Athena.

Options B, C, and D are incorrect due to syntax errors or incorrect SQL usage (e.g., the MOVE command or the use of UPDATE in a non-relevant context).

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.

The most efficient and low-operational-overhead solution for ingesting data into Amazon Redshift from Amazon Kinesis Data Streams is to use Amazon Redshift streaming ingestion. This feature allows Redshift to directly ingest streaming data from Kinesis Data Streams and process it in real-time.

Amazon Redshift Streaming Ingestion:

Redshift supports native streaming ingestion from Kinesis Data Streams, allowing real-time data to be queried using materialized views.

This solution reduces operational complexity because you don ' t need intermediary services like Amazon Kinesis Data Firehose or S3 for batch loading.

According to the guide:

“For integrating streaming data from Amazon MSK into Amazon Redshift efficiently and cost-effectively, AWS Glue streaming jobs can process and transform the data, storing it in Amazon S3. Amazon Redshift Spectrum can then directly query the data from S3, minimizing operational overhead and reducing storage costs.”

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

This setup offers:

Low latency via Glue streaming.

Low storage cost by using Parquet/ORC on S3.

Minimal operational overhead by avoiding complex pipelines or constantly updated materialized views.

Problem Analysis:

The company uses DynamoDB for gaming data storage and needs to ingest data into Amazon OpenSearch Service in near real time.

Data updates must propagate quickly to OpenSearch for analytics or search purposes.

Key Considerations:

DynamoDB Streams provide near-real-time capture of table changes (inserts, updates, and deletes).

Integration with AWS Lambda allows seamless processing of these changes.

OpenSearch offers APIs for indexing and updating documents, which Lambda can invoke.

Solution Analysis:

Option A: Step Functions with Periodic Export

Not suitable for near-real-time updates; introduces significant latency.

Operationally complex to manage periodic exports and S3 data ingestion.

Option B: AWS Glue Job

AWS Glue is designed for ETL workloads but lacks real-time processing capabilities.

Option C: DynamoDB Streams + Lambda

DynamoDB Streams capture changes in near real time.

Lambda can process these streams and use the OpenSearch API to update the index.

This approach provides low latency and seamless integration with minimal operational overhead.

Option D: Custom OpenSearch Plugin

Writing a custom plugin adds complexity and is unnecessary with existing AWS integrations.

Implementation Steps:

Enable DynamoDB Streams for the relevant DynamoDB tables.

Create a Lambda function to process stream records:

Parse insert, update, and delete events.

Use OpenSearch APIs to index or update documents based on the event type.

Set up a trigger to invoke the Lambda function whenever there are changes in the DynamoDB Stream.

Monitor and log errors for debugging and operational health.

Amazon DynamoDB Streams Documentation

AWS Lambda and DynamoDB Integration

Amazon OpenSearch Service APIs

Problem Analysis:

The company needs near real-time replication of MySQL updates to Amazon Redshift.

Minimal development effort is required for this solution.

Key Considerations:

AWS DMS provides a full load + CDC (Change Data Capture) mode for continuous replication of database changes.

DMS integrates natively with both MySQL and Redshift, simplifying setup.

Solution Analysis:

Option A: AWS Glue Job

Glue is batch-oriented and does not support near real-time replication.

Option B: DMS with Full Load + CDC

Efficiently handles initial database load and continuous updates.

Requires minimal setup and operational overhead.

Option C: AppFlow SDK

AppFlow is not designed for database replication. Custom connectors increase development effort.

Option D: DataSync

DataSync is for file synchronization and not suitable for database updates.

Final Recommendation:

Use AWS DMS in full load + CDC mode for continuous replication.

AWS Database Migration Service Documentation

Setting Up DMS with Redshift

AWS Step Functions is a service that allows you to coordinate multiple AWS services into serverless workflows. You can use Step Functions to create state machines that define the sequence and logic of the tasks in your workflow. Step Functions supports various types of tasks, such as Lambda functions, AWS Glue jobs, Amazon EMR clusters, Amazon ECS tasks, etc. You can use Step Functions to monitor and troubleshoot your workflows, as well as to handle errors and retries.

Using an AWS Step Functions workflow that includes a state machine to run the Lambda function and then the AWS Glue job will meet the requirements with the least management overhead, as it leverages the serverless and managed capabilities of Step Functions. You do not need to write any code to orchestrate the tasks in your workflow, as you can use the Step Functions console or the AWS Serverless Application Model (AWS SAM) to define and deploy your state machine. You also do not need to provision or manage any servers or clusters, as Step Functions scales automatically based on the demand.

The other options are not as efficient as using an AWS Step Functions workflow. Using an Apache Airflow workflow that is deployed on an Amazon EC2 instance or on Amazon Elastic Kubernetes Service (Amazon EKS) will require more management overhead, as you will need to provision, configure, and maintain the EC2 instance or the EKS cluster, as well as the Airflow components. You will also need to write and maintain the Airflow DAGs to orchestrate the tasks in your workflow. Using an AWS Glue workflow to run the Lambda function and then the AWS Glue job will not work, as AWS Glue workflows only support AWS Glue jobs and crawlers as tasks, not Lambda functions. References:

AWS Step Functions

AWS Glue

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 6: Data Integration and Transformation, Section 6.3: AWS Step Functions

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

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

Amazon Managed Service for Apache Flink

Apache Flink

Window Aggregations in Flink

The best solution to meet the requirements of giving data scientists the ability to query all data sources by using syntax similar to SQL with the least operational overhead is to use AWS Glue to crawl the data sources, store metadata in the AWS Glue Data Catalog, use Amazon Athena to query the data, use SQL for structured data sources, and use PartiQL for data that is stored in JSON format.

AWS Glue is a serverless data integration service that makes it easy to prepare, clean, enrich, and move data between data stores1. 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. The Data Catalog is a persistent metadata store that contains table definitions, job definitions, and other control information to help you manage your AWS Glue components3. You can use AWS Glue to crawl the data sources, such as Amazon S3, Amazon RDS for Microsoft SQL Server, and Amazon DynamoDB, and store the metadata in the Data Catalog.

Amazon Athena is a serverless, interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL or Python4. Amazon Athena also supports PartiQL, a SQL-compatible query language that lets you query, insert, update, and delete data from semi-structured and nested data, such as JSON. You can use Amazon Athena to query the data from the Data Catalog using SQL for structured data sources, such as .csv files and relational databases, and PartiQL for data that is stored in JSON format. You can also use Athena to query data from other data sources, such as Amazon Redshift, using federated queries.

Using AWS Glue and Amazon Athena to query all data sources by using syntax similar to SQL is the least operational overhead solution, as you do not need to provision, manage, or scale any infrastructure, and you pay only for the resources you use. AWS Glue charges you based on the compute time and the data processed by your crawlers and ETL jobs1. Amazon Athena charges you based on the amount of data scanned by your queries. You can also reduce the cost and improve the performance of your queries by using compression, partitioning, and columnar formats for your data in Amazon S3.

Option B is not the best solution, as using AWS Glue to crawl the data sources, store metadata in the AWS Glue Data Catalog, and use Redshift Spectrum to query the data, would incur more costs and complexity than using Amazon Athena. Redshift Spectrum is a feature of Amazon Redshift, a fully managed data warehouse service, that allows you to query and join data across your data warehouse and your data lake using standard SQL. While Redshift Spectrum is powerful and useful for many data warehousing scenarios, it is not necessary or cost-effective for querying all data sources by using syntax similar to SQL. Redshift Spectrum charges you based on the amount of data scanned by your queries, which is similar to Amazon Athena, but it also requires you to have an Amazon Redshift cluster, which charges you based on the node type, the number of nodes, and the duration of the cluster5. These costs can add up quickly, especially if you have large volumes of data and complex queries. Moreover, using Redshift Spectrum would introduce additional latency and complexity, as you would have to provision and manage the cluster, and create an external schema and database for the data in the Data Catalog, instead of querying it directly from Amazon Athena.

Option C is not the best solution, as using AWS Glue to crawl the data sources, store metadata in the AWS Glue Data Catalog, use AWS Glue jobs to transform data that is in JSON format to Apache Parquet or .csv format, store the transformed data in an S3 bucket, and use Amazon Athena to query the original and transformed data from the S3 bucket, would incur more costs and complexity than using Amazon Athena with PartiQL. AWS Glue jobs are ETL scripts that you can write in Python or Scala to transform your data and load it to your target data store. 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 schemes6. While using AWS Glue jobs and Parquet can improve the performance and reduce the cost of your queries, they would also increase the complexity and the operational overhead of the data pipeline, as you would have to write, run, and monitor the ETL jobs, and store the transformed data in a separate location in Amazon S3. Moreover, using AWS Glue jobs and Parquet would introduce additional latency, as you would have to wait for the ETL jobs to finish before querying the transformed data.

Option D is not the best solution, as using AWS Lake Formation to create a data lake, use Lake Formation jobs to transform the data from all data sources to Apache Parquet format, store the transformed data in an S3 bucket, and use Amazon Athena or Redshift Spectrum to query the data, would incur more costs and complexity than using Amazon Athena with PartiQL. AWS Lake Formation is a service that helps you centrally govern, secure, and globally share data for analytics and machine learning7. Lake Formation jobs are ETL jobs that you can create and run using the Lake Formation console or API. While using Lake Formation and Parquet can improve the performance and reduce the cost of your queries, they would also increase the complexity and the operational overhead of the data pipeline, as you would have to create, run, and monitor the Lake Formation jobs, and store the transformed data in a separate location in Amazon S3. Moreover, using Lake Formation and Parquet would introduce additional latency, as you would have to wait for the Lake Formation jobs to finish before querying the transformed data. Furthermore, using Redshift Spectrum to query the data would also incur the same costs and complexity as mentioned in option B. References:

What is Amazon Athena?

Data Catalog and crawlers in AWS Glue

AWS Glue Data Catalog

Columnar Storage Formats

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

AWS Glue Schema Registry

What is AWS Glue?

Amazon Redshift Serverless

Amazon Redshift provisioned clusters

[Querying external data using Amazon Redshift Spectrum]

[Using stored procedures in Amazon Redshift]

[What is AWS Lambda?]

[PartiQL for Amazon Athena]

[Federated queries in Amazon Athena]

[Amazon Athena pricing]

[Top 10 performance tuning tips for Amazon Athena]

[AWS Glue ETL jobs]

[AWS Lake Formation jobs]

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.

 Amazon Redshift Serverless is a new feature of Amazon Redshift that enables you to run SQL queries on data in Amazon S3 without provisioning or managing any clusters. You can use Amazon Redshift Serverless to automatically process the analytics workload, as it scales up and down the compute resources based on the query demand, and charges you only for the resources consumed. This solution will meet the requirements with the least operational overhead, as it does not require the data engineer to create, delete, pause, or resume any Redshift clusters, or to manage any infrastructure manually. You can use the Amazon Redshift Data API to run queries from the AWS CLI, AWS SDK, or AWS Lambda functions12.

The other options are not optimal for the following reasons:

A. Use Amazon Step Functions to pause the Redshift cluster when the analytics processes are complete and to resume the cluster to run new processes every month. This option is not recommended, as it would still require the data engineer to create and delete a new Redshift provisioned cluster every month, which can incur additional costs and time. Moreover, this option would require the data engineer to use Amazon Step Functions to orchestrate the workflow of pausing and resuming the cluster, which can add complexity and overhead.

C. Use the AWS CLI to automatically process the analytics workload. This option is vague and does not specify how the AWS CLI is used to process the analytics workload. The AWS CLI can be used to run queries on data in Amazon S3 using Amazon Redshift Serverless, Amazon Athena, or Amazon EMR, but each of these services has different features and benefits. Moreover, this option does not address the requirement of not managing the infrastructure manually, as the data engineer may still need to provision and configure some resources, such as Amazon EMR clusters or Amazon Athena workgroups.

D. Use AWS CloudFormation templates to automatically process the analytics workload. This option is also vague and does not specify how AWS CloudFormation templates are used to process the analytics workload. AWS CloudFormation is a service that lets you model and provision AWS resources using templates. You can use AWS CloudFormation templates to create and delete a Redshift provisioned cluster every month, or to create and configure other AWS resources, such as Amazon EMR, Amazon Athena, or Amazon Redshift Serverless. However, this option does not address the requirement of not managing the infrastructure manually, as the data engineer may still need to write and maintain the AWS CloudFormation templates, and to monitor the status and performance of the resources.

1: Amazon Redshift Serverless

2: Amazon Redshift Data API

Amazon Step Functions

AWS CLI

AWS CloudFormation

Amazon Athena is a serverless, interactive query service that enables you to analyze data in Amazon S3 using standard SQL. AWS Lake Formation is a service that helps you build, secure, and manage data lakes on AWS. You can use AWS Lake Formation to create data filters that define the level of access for different IAM roles based on the columns, rows, or tags of the data. By using Amazon Athena to query the data and AWS Lake Formation to create data filters, the company can meet the requirements of ensuring that user groups can access only the PII that they require with the least effort. The solution is to use Amazon Athena to query the data in the data lake that is in Amazon S3. Then, set up AWS Lake Formation and create data filters to establish levels of access for the company’s IAM roles. For example, a data filter can allow a user group to access only the columns that contain the PII that they need, such as name and email address, and deny access to the columns that contain the PII that they do not need, such as phone number and social security number. Finally, assign each user to the IAM role that matches the user’s PII access requirements. This way, the user groups can access the data in the data lake securely and efficiently. The other options are either not feasible or not optimal. Using Amazon QuickSight to access the data (option B) would require the company to pay for the QuickSight service and to configure the column-level security features for each user. Building a custom query builder UI that will run Athena queries in the background to access the data (option C) would require the company to develop and maintain the UI and to integrate it with Amazon Cognito. Creating IAM roles that have different levels of granular access (option D) would require the company to manage multiple IAM roles and policies and to ensure that they are aligned with the data schema. References:

Amazon Athena

AWS Lake Formation

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

Option B is the best solution to meet the requirements with the least operational overhead because S3 Select is a feature that allows you to retrieve only a subset of data from an S3 object by using simple SQL expressions. S3 Select works on objects stored in CSV, JSON, or Parquet format. By using S3 Select, you can avoid the need to download and process the entire S3 object, which reduces the amount of data transferred and the computation time. S3 Select is also easy to use and does not require any additional services or resources.

Option A is not a good solution because it involves writing custom code and configuring an AWS Lambda function to load data from the S3 bucket into a pandas dataframe and query the required column. This option adds complexity and latency to the data retrieval process and requires additional resources and configuration. Moreover, AWS Lambda has limitations on the execution time, memory, and concurrency, which may affect the performance and reliability of the data retrieval process.

Option C is not a good solution because it involves creating and running an AWS Glue DataBrew project to consume the S3 objects and query the required column. AWS Glue DataBrew is a visual data preparation tool that allows you to clean, normalize, and transform data without writing code. However, in this scenario, the data is already in Parquet format, which is a columnar storage format that is optimized for analytics. Therefore, there is no need to use AWS Glue DataBrew to prepare the data. Moreover, AWS Glue DataBrew adds extra time and cost to the data retrieval process and requires additional resources and configuration.

Option D is not a good solution because it involves running an AWS Glue crawler on the S3 objects and using a SQL SELECT statement in Amazon Athena to query the required column. 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. Amazon Athena is a serverless interactive query service that allows you to analyze data in S3 using standard SQL. However, in this scenario, the schema and format of the data are already known and fixed, so there is no need to run a crawler to discover them. Moreover, running a crawler and using Amazon Athena adds extra time and cost to the data retrieval process and requires additional services and configuration.

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

S3 Select and Glacier Select - Amazon Simple Storage Service

AWS Lambda - FAQs

What Is AWS Glue DataBrew? - AWS Glue DataBrew

Populating the AWS Glue Data Catalog - AWS Glue

What is Amazon Athena? - Amazon Athena

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).

The correct answer is B because the scenario requires scalable approximate vector search, high recall, incremental updates, and complex filtering. Amazon OpenSearch Service supports k-NN vector search for recommendation use cases, and OpenSearch supports k-NN vector fields for high-dimensional vector search. The Lucene HNSW option is strongest here because OpenSearch documentation specifically states that Lucene supports k-NN searches using HNSW graphs and supports Lucene filters for k-NN searches. That directly matches the need for category and inventory filtering. Exact k-NN with Painless script scoring is accurate but too slow for 10 million vectors. IVF can be efficient but is less ideal for frequent incremental updates and complex filtering. Binary quantization reduces memory but sacrifices accuracy.

 Changing the distribution key to the table column that has the largest dimension will help to balance the load more evenly across all five compute nodes. The distribution key determines how the rows of a table are distributed among the slices of the cluster. If the distribution key is not chosen wisely, it can cause data skew, meaning some slices will have more data than others, resulting in uneven CPU load and query performance. By choosing the table column that has the largest dimension, meaning the column that has the most distinct values, as the distribution key, the data engineer can ensure that the rows are distributed more uniformly across the slices, reducing data skew and improving query performance.

The other options are not solutions that will meet the requirements. Option A, changing the sort key to be the data column that is most often used in a WHERE clause of the SQL SELECT statement, will not affect the data distribution or the CPU load. The sort key determines the order in which the rows of a table are stored on disk, which can improve the performance of range-restricted queries, but not the load balancing. Option C, upgrading the reserved node from ra3.4xlarge to ra3.16xlarge, will not maintain the current number of compute nodes, as it will increase the cost and the capacity of the cluster. Option D, changing the primary key to be the data column that is most often used in a WHERE clause of the SQL SELECT statement, will not affect the data distribution or the CPU load either. The primary key is a constraint that enforces the uniqueness of the rows in a table, but it does not influence the data layout or the query optimization. References:

Choosing a data distribution style

Choosing a data sort key

Working with primary keys

 Amazon Redshift Spectrum is a feature that allows you to run SQL queries directly against data in Amazon S3, without loading or transforming the data. Redshift Spectrum can query 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, using a columnar storage file format, such as Parquet, will provide faster queries, as it allows Redshift Spectrum to scan only the relevant columns and skip the rest, reducing the amount of data read from S3. Additionally, partitioning the data based on the most common query predicates, such as date, time, region, etc., will provide faster queries, as it allows Redshift Spectrum to prune the partitions that do not match the query criteria, reducing the amount of data scanned from S3. Partitioning also improves the performance of joins and aggregations, as it reduces data skew and shuffling.

The other options are not as effective as using a columnar storage file format and partitioning the data. Using gzip compression to compress individual files to sizes that are between 1 GB and 5 GB will reduce the data size, but it will not improve the query performance significantly, as gzip is not a splittable compression algorithm and requires decompression before reading. Splitting the data into files that are less than 10 KB will increase the number of files and the metadata overhead, which will degrade the query performance. Using file formats that are not supported by Redshift Spectrum, such as XML, will not work, as Redshift Spectrum will not be able to read or parse the data. References:

Amazon Redshift Spectrum

Choosing the Right Data Format

AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 4: Data Lakes and Data Warehouses, Section 4.3: Amazon Redshift Spectrum

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.

AWS Glue Data Quality transforms allow you to define built-in rules like IsComplete for null validation and ReferentialIntegrity for relationship validation—all with minimal code and operational overhead.

“Use AWS Glue Data Quality rules such as IsComplete and ReferentialIntegrity within ETL jobs to automatically validate incoming data.”

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

The company wants to use Amazon Kinesis Data Firehose to transform CSV files into JSON format and store the files in Apache Parquet format with the least development effort.

Option B: Use Kinesis Data Firehose to convert the CSV files to JSON and to store the files in Parquet format.Kinesis Data Firehose supports data format conversion natively, including converting incoming CSV data to JSON format and storing the resulting files in Parquet format in Amazon S3. This solution requires the least development effort because it uses built-in transformation features of Kinesis Data Firehose.

Other options (A, C, D) involve invoking AWS Lambda functions, which would introduce additional complexity and development effort compared to Kinesis Data Firehose’s native format conversion capabilities.

Option D is the best solution to meet the requirements with the least operational overhead because AWS Lake Formation is a fully managed service that simplifies the process of building, securing, and managing data lakes. AWS Lake Formation allows you to define granular data access policies at the row and column level for different users and groups. AWS Lake Formation also integrates with Amazon Athena, Amazon Redshift Spectrum, and Apache Hive on Amazon EMR, enabling these services to access the data in the data lake through AWS Lake Formation.

Option A is not a good solution because S3 access policies cannot restrict data access by rows and columns. S3 access policies are based on the identity and permissions of the requester, the bucket and object ownership, and the object prefix and tags. S3 access policies cannot enforce fine-grained data access control at the row and column level.

Option B is not a good solution because it involves using Apache Ranger and Apache Pig, which are not fully managed services and require additional configuration and maintenance. Apache Ranger is a framework that provides centralized security administration for data stored in Hadoop clusters, such as Amazon EMR. Apache Ranger can enforce row-level and column-level access policies for Apache Hive tables. However, Apache Ranger is not a native AWS service and requires manual installation and configuration on Amazon EMR clusters. Apache Pig is a platform that allows you to analyze large data sets using a high-level scripting language called Pig Latin. Apache Pig can access data stored in Amazon S3 and process it using Apache Hive. However, Apache Pig is not a native AWS service and requires manual installation and configuration on Amazon EMR clusters.

Option C is not a good solution because Amazon Redshift is not a suitable service for data lake storage. Amazon Redshift is a fully managed data warehouse service that allows you to run complex analytical queries using standard SQL. Amazon Redshift can enforce row-level and column-level access policies for different users and groups. However, Amazon Redshift is not designed to store and process large volumes of unstructured or semi-structured data, which are typical characteristics of data lakes. Amazon Redshift is also more expensive and less scalable than Amazon S3 for data lake storage.

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

What Is AWS Lake Formation? - AWS Lake Formation

Using AWS Lake Formation with Amazon Athena - AWS Lake Formation

Using AWS Lake Formation with Amazon Redshift Spectrum - AWS Lake Formation

Using AWS Lake Formation with Apache Hive on Amazon EMR - AWS Lake Formation

Using Bucket Policies and User Policies - Amazon Simple Storage Service

Apache Ranger

Apache Pig

What Is Amazon Redshift? - Amazon Redshift

AWS Glue DataBrew is purpose-built for data profiling and data preparation with minimal operational overhead. A DataBrew profile job automatically analyzes datasets stored in Amazon S3 and generates detailed statistics such as column completeness, distinct values, data types, min/max values, and potential data quality issues. This is exactly what is required before data preparation steps.

The dataset size (100 MB) and Parquet format are fully supported by DataBrew, and no infrastructure provisioning, cluster management, or SQL development is required. The profiling results are produced automatically and can be reviewed directly in the AWS Management Console, making this the most efficient and lowest-effort solution.

Amazon Managed Service for Apache Flink is designed for real-time streaming analytics and introduces unnecessary complexity. Amazon Redshift Spectrum requires schema definition, SQL development, and additional setup, which is excessive for simple profiling. Amazon QuickSight is a visualization and BI service, not a data profiling tool.

Therefore, creating a DataBrew profile job is the most operationally efficient and exam-aligned solution.

Option C is correct because Amazon Redshift data sharing is built for exactly this use case: providing live, immediate, read-only access to data across Redshift clusters and AWS accounts without copying or moving the data. AWS states that data sharing lets you share live data “without having to create a copy or move it,” and that users can access the most up-to-date and consistent information as it is updated in the producer cluster. AWS also documents that data sharing provides instant, granular, and high-performance access across Amazon Redshift clusters and AWS accounts, which directly matches the requirement to preserve current Redshift performance. Since the downstream clusters use RA3 nodes, they satisfy the node-family requirement commonly associated with Redshift data sharing scenarios.

Option A would move the data to S3 and query it through Spectrum, which changes the performance profile and does not maintain native Redshift performance characteristics as well as direct Redshift data sharing. Option B introduces delay and duplicated storage through ETL copies. Option D is not an appropriate method for synchronizing Redshift datasets between clusters. Therefore, Redshift data sharing is the most direct, immediate, and performance-preserving solution.

Options B and D are correct. AWS Prescriptive Guidance states that shuffle is a major cause of Spark performance issues and can exhaust local disk space on executors, leading to “No space left on device” failures. AWS specifically recommends assessing shuffle performance in CloudWatch metrics and in the Spark UI, and for data skew, it says to examine metrics in the Spark UI, including task duration and spill behavior across executors. That makes B the correct low-cost way to identify the source of the problem.

For remediation, AWS Glue documentation states that Spark can throw “No space left on device” when there is insufficient local disk on the executor, and that you can use Amazon S3 to store Spark shuffle data by enabling the AWS Glue Spark shuffle plugin. The S3 shuffle approach is specifically presented as a way to run shuffle-intensive jobs more reliably when they are bound by local disk capacity. In skew scenarios, using a skew-mitigation technique such as salting is also a standard way to distribute hot keys more evenly. That makes D the most cost-effective corrective step.

Options A and C increase cost by adding capacity before diagnosing or directly fixing the shuffle bottleneck. Option E is less precise than the Spark UI and Glue metrics for identifying skew.

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 correct answer is D: Enable native Amazon RDS database audit logging with CloudWatch and CloudTrail integration.

Native RDS database audit logging captures SQL-level activities, including which user is modifying data, which is essential for meeting audit requirements.

Amazon CloudWatch Logs can be used to centralize and store logs.

CloudTrail logs API calls related to instance-level changes, ensuring all modifications are traceable.

This approach provides a comprehensive audit trail and supports compliance with security governance policies.

“Enable and configure native Amazon RDS database audit logging. Enable Amazon CloudWatch Logs. Configure metric filters and alarms. Configure AWS CloudTrail audit logging.”

The requirement is to update the AWS Glue Data Catalog incrementally based on S3 events. Using an S3 event-based approach is the most automated and operationally efficient solution.

A. Create an S3 event-based AWS Glue crawler:

An event-based Glue crawler can automatically update the Data Catalog when new data arrives in the S3 bucket. This ensures incremental updates with minimal operational overhead.

The requirement to retain immutable object versions using a WORM model mandates the use of S3 Object Lock, not just S3 Versioning. Object Lock enforces immutability by preventing deletion or modification of objects for a specified retention period, which is required for compliance scenarios such as regulatory data retention.

To meet the access requirement of retrieving data within 5 hours, the appropriate archival storage class is S3 Glacier Flexible Retrieval, which supports expedited and standard retrieval within minutes to hours. S3 Glacier Deep Archive does not meet this requirement because retrieval times are typically 12 hours or longer.

Using S3 Intelligent-Tiering for active data optimizes storage costs automatically before transitioning older objects. A lifecycle policy can then transition objects older than 1 year to Glacier Flexible Retrieval and delete them after the 7-year retention period expires.

Options that rely only on S3 Versioning do not provide immutability guarantees. Options using Glacier Deep Archive fail the access-time requirement. Therefore, Option C is the only solution that satisfies WORM compliance, retrieval-time constraints, and cost optimization.

Option C is correct because the fastest design combines a columnar storage format with partitioning on the most common query predicate. AWS Athena guidance says that Parquet and ORC are optimized columnar formats and that columns frequently used as filters are good candidates for partitioning. AWS also states that when a query filters on a partition key, the engine reads only matching partitions, which significantly reduces data scanned. Since the company’s main analytical need is insights specific to sensor categories, partitioning by sensor category provides the strongest pruning. Sorting by date then helps organize time-based data within each category partition.

Options B and D use CSV, which is row-based and much slower for analytical scans than Parquet or ORC. Option A uses a good format, but partitioning by date is less optimal than partitioning by sensor category when category is the main filter in the company’s queries. The study guide also identifies Parquet as the preferred analytic storage format for efficient columnar queries.

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.

Athena workgroups are a way to isolate query execution and query history among users, teams, and applications that share the same AWS account. By creating a workgroup for each use case, the company can control the access and actions on the workgroup resource using resource-level IAM permissions or identity-based IAM policies. The company can also use tags to organize and identify the workgroups, and use them as conditions in the IAM policies to grant or deny permissions to the workgroup. This solution meets the requirements of separating query processes and access to query history among users, teams, and applications that are in the same AWS account. References:

Athena Workgroups

IAM policies for accessing workgroups

Workgroup example policies

The company wants to ensure that no customer records are deleted or modified for 7 years, and even the root user should not have the ability to change the data. S3 Object Lock in Compliance Mode is the correct solution for this scenario.

Option B: Enable compliance mode on the S3 bucket. Use a default retention period of 7 years.In Compliance Mode, even the root user cannot delete or modify locked objects during the retention period. This ensures that the data is protected for the entire 7-year duration as required. Compliance mode is stricter than governance mode and prevents all forms of alteration, even by privileged users.

Option A (Governance Mode) still allows certain privileged users (like the root user) to bypass the lock, which does not meet the company ' s requirement. Option C (legal hold) and Option D (setting retention per object) do not fully address the requirement to block root user modifications.

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

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.

The best solution for fast, event-driven processing of unpredictable file uploads is to use S3 event notifications, CloudTrail, and EventBridge to automatically trigger the AWS Glue workflow:

“You can configure S3 PutObject events to be captured by CloudTrail and forwarded through EventBridge to trigger an AWS Glue job or workflow. This allows Glue to begin processing as soon as the file arrives, with minimal latency.”

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

This option provides the lowest latency and least manual overhead compared to polling or scheduling solutions.

The correct answer is A because the pipeline requires scalable ingestion, PII removal, ETL transformation, centralized storage, and automatic retry handling. Amazon S3 is the correct landing and central storage layer for large hospital data files. AWS Glue ETL is appropriate for managed data transformation at this scale, and AWS Glue DataBrew is the best fit among the options for low-code data preparation and PII handling. Step Functions is the correct orchestration service because it supports retry policies for task states, parallel states, and map states without manual intervention. EC2 custom scripts and AWS Batch add more operational work. Lambda is not suitable for 5 GB per hospital with heavier transformation steps. Therefore, S3, Glue, DataBrew, and Step Functions is the least-operational-overhead design.

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]

The issue described is that the AWS Glue job is reprocessing files from previous runs despite the bookmark feature being enabled. Bookmarks in AWS Glue allow jobs to keep track of which files or data have already been processed to avoid reprocessing. The most likely reason for reprocessing the files is missing S3 permissions, specifically s3

s3

is a permission required by AWS Glue when bookmarks are enabled to ensure Glue can retrieve metadata from the files in S3, which is necessary for the bookmark mechanism to function correctly. Without this permission, Glue cannot track which files have been processed, resulting in reprocessing during subsequent runs.

Concurrency settings (Option B) and the version of AWS Glue (Option C) do not affect the bookmark behavior. Similarly, the lack of a commit statement (Option D) is not applicable in this context, as Glue handles commits internally when interacting with Redshift and S3.

Thus, the root cause is likely related to insufficient permissions on the S3 bucket, specifically s3

, which is required for bookmarks to work as expected.

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 B is the best solution to meet the requirements with the least operational overhead because S3 Object Lambda is a feature that allows you to add your own code to process data retrieved from S3 before returning it to an application. S3 Object Lambda works with S3 GET requests and can modify both the object metadata and the object data. By using S3 Object Lambda, you can implement redaction logic within an S3 Object Lambda function to dynamically redact PII based on the needs of each application that accesses the data. This way, you can avoid creating and maintaining multiple copies of the dataset with different levels of redaction.

Option A is not a good solution because it involves creating and managing multiple copies of the dataset with different levels of redaction for each application. This option adds complexity and storage cost to the data protection process and requires additional resources and configuration. Moreover, S3 bucket policies cannot enforce fine-grained data access control at the row and column level, so they are not sufficient to redact PII.

Option C is not a good solution because it involves using AWS Glue to transform the data for each application. AWS Glue is a fully managed service that can extract, transform, and load (ETL) data from various sources to various destinations, including S3. AWS Glue can also convert data to different formats, such as Parquet, which is a columnar storage format that is optimized for analytics. However, in this scenario, using AWS Glue to redact PII is not the best option because it requires creating and maintaining multiple copies of the dataset with different levels of redaction for each application. This option also adds extra time and cost to the data protection process and requires additional resources and configuration.

Option D is not a good solution because it involves creating and configuring an API Gateway endpoint that has custom authorizers. API Gateway is a service that allows you to create, publish, maintain, monitor, and secure APIs at any scale. API Gateway can also integrate with other AWS services, such as Lambda, to provide custom logic for processing requests. However, in this scenario, using API Gateway to redact PII is not the best option because it requires writing and maintaining custom code and configuration for the API endpoint, the custom authorizers, and the REST API call. This option also adds complexity and latency to the data protection process and requires additional resources and configuration.

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

Introducing Amazon S3 Object Lambda – Use Your Code to Process Data as It Is Being Retrieved from S3

Using Bucket Policies and User Policies - Amazon Simple Storage Service

AWS Glue Documentation

What is Amazon API Gateway? - Amazon API Gateway

 This solution meets the requirements of optimizing the performance of table SQL queries without increasing the size of the cluster. By using the ALL distribution style for rarely updated small tables, you can ensure that the entire table is copied to every node in the cluster, which eliminates the need for data redistribution during joins. This can improve query performance significantly, especially for frequently joined dimension tables. However, using the ALL distribution style also increases the storage space and the load time, so it is only suitable for small tables that are not updated frequently or extensively. By specifying primary and foreign keys for all tables, you can help the query optimizer to generate better query plans and avoid unnecessary scans or joins. You can also use the AUTO distribution style to let Amazon Redshift choose the optimal distribution style based on the table size and the query patterns. References:

Choose the best distribution style

Distribution styles

Working with data distribution styles

The best solution to cost optimize the company’s use of Amazon Athena without adding any additional infrastructure costs is to use the query result reuse feature of Amazon Athena for the SQL queries. This feature allows you to run the same query multiple times without incurring additional charges, as long as the underlying data has not changed and the query results are still in the query result location in Amazon S31. This feature is useful for scenarios where you have a petabyte-scale dataset that is updated infrequently, such as once a day, and you have a BI application that runs the same queries repeatedly, such as every hour. By using the query result reuse feature, you can reduce the amount of data scanned by your queries and save on the cost of running Athena. You can enable or disable this feature at the workgroup level or at the individual query level1.

Option A is not the best solution, as configuring an Amazon S3 Lifecycle policy to move data to the S3 Glacier Deep Archive storage class after 1 day would not cost optimize the company’s use of Amazon Athena, but rather increase the cost and complexity. Amazon S3 Lifecycle policies are rules that you can define to automatically transition objects between different storage classes based on specified criteria, such as the age of the object2. S3 Glacier Deep Archive is the lowest-cost storage class in Amazon S3, designed for long-term data archiving that is accessed once or twice in a year3. While moving data to S3 Glacier Deep Archive can reduce the storage cost, it would also increase the retrieval cost and latency, as it takes up to 12 hours to restore the data from S3 Glacier Deep Archive3. Moreover, Athena does not support querying data that is in S3 Glacier or S3 Glacier Deep Archive storage classes4. Therefore, using this option would not meet the requirements of running on-demand SQL queries on the dataset.

Option C is not the best solution, as adding an Amazon ElastiCache cluster between the BI application and Athena would not cost optimize the company’s use of Amazon Athena, but rather increase the cost and complexity. Amazon ElastiCache is a service that offers fully managed in-memory data stores, such as Redis and Memcached, that can improve the performance and scalability of web applications by caching frequently accessed data. While using ElastiCache can reduce the latency and load on the BI application, it would not reduce the amount of data scanned by Athena, which is the main factor that determines the cost of running Athena. Moreover, using ElastiCache would introduce additional infrastructure costs and operational overhead, as you would have to provision, manage, and scale the ElastiCache cluster, and integrate it with the BI application and Athena.

Option D is not the best solution, as changing the format of the files that are in the dataset to Apache Parquet would not cost optimize the company’s use of Amazon Athena without adding any additional infrastructure costs, but rather increase the complexity. 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, changing the format of the files that are in the dataset to Apache Parquet would require additional processing and transformation steps, such as using AWS Glue or Amazon EMR to convert the files from their original format to Parquet, and storing the converted files in a separate location in Amazon S3. This would increase the complexity and the operational overhead of the data pipeline, and also incur additional costs for using AWS Glue or Amazon EMR. References:

Query result reuse

Amazon S3 Lifecycle

S3 Glacier Deep Archive

Storage classes supported by Athena

[What is Amazon ElastiCache?]

[Amazon Athena pricing]

[Columnar Storage Formats]

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

Amazon Macie is a fully managed data security and privacy service that uses machine learning to automatically discover, classify, and protect sensitive data in AWS, such as PII. By configuring Macie for automated sensitive data discovery, the company can minimize manual intervention while ensuring PII is identified before data is shared.

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

Apache Iceberg query performance depends heavily on metadata optimization and file layout efficiency. Generating column-level statistics allows query engines such as AWS Glue, Amazon Athena, and Amazon Redshift to perform predicate pushdown and data skipping. These statistics help avoid scanning unnecessary Parquet files and significantly reduce query execution time.

Real-time streaming workloads often generate many small files, which degrades query performance. Table compaction consolidates small Parquet files into fewer, larger files, improving scan efficiency and reducing metadata overhead. Iceberg supports compaction as a core optimization technique, and Glue can schedule these operations automatically.

Iceberg does not use traditional indexes, so index optimization is not applicable. Copy-on-write affects write semantics and consistency, not query speed. Views do not improve physical query performance.

Therefore, generating statistics and compacting files are the two most effective optimizations.

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

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

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.

The company ' s Apache Spark ETL job on Amazon EMR uses high CPU but low memory, meaning that compute-optimized EC2 instances would be the most cost-effective choice. These instances are designed for high-performance compute applications, where CPU usage is high, but memory needs are minimal, which is exactly the case here.

Compute Optimized Instances:

Compute-optimized instances, such as the C5 series, provide a higher ratio of CPU to memory, which is more suitable for jobs with high CPU usage and relatively low memory consumption.

Switching from general-purpose EC2 instances to compute-optimized instances can reduce costs while improving performance, as these instances are optimized for workloads like Spark jobs that perform a lot of computation.

 This solution meets the requirement of gaining access to SageMaker Studio to use AWS Glue interactive sessions. AWS Glue interactive sessions are a way to use AWS Glue DataBrew and AWS Glue Data Catalog from within SageMaker Studio. To use AWS Glue interactive sessions, the data engineer’s IAM user needs to have permissions to assume the AWS Glue service role and the SageMaker execution role. By adding a policy to the data engineer’s IAM user that includes the sts:AssumeRole action for the AWS Glue and SageMaker service principals in the trust policy, the data engineer can grant these permissions and avoid the access denied error. The other options are not sufficient or necessary to resolve the error. References:

Get started with data integration from Amazon S3 to Amazon Redshift using AWS Glue interactive sessions

Troubleshoot Errors - Amazon SageMaker

AccessDeniedException on sagemaker:CreateDomain in AWS SageMaker Studio, despite having SageMakerFullAccess

Problem Analysis:

The company uses Kinesis Data Streams for both inventory management and reordering.

The Kinesis Producer Library (KPL) publishes data, and the Kinesis Client Library (KCL) consumes data.

Duplicate records were observed in the inventory reordering system.

Key Considerations:

Kinesis streams are designed for durability but may produce duplicates under certain conditions.

Factors such as network timeouts, shard splits, or changes in record processors can cause duplication.

Solution Analysis:

Option A: Network-Related Timeouts

If the producer (KPL) experiences network timeouts, it retries data submission, potentially causing duplicates.

Option B: High IteratorAgeMilliseconds

High iterator age suggests delays in processing but does not directly cause duplication.

Option C: Changes in Shards or Processors

Changes in the number of shards or record processors can lead to re-processing of records, causing duplication.

Option D: AggregationEnabled Set to True

AggregationEnabled controls the aggregation of multiple records into one, but it does not cause duplication.

Option E: High max_records Value

A high max_records value increases batch size but does not lead to duplication.

Final Recommendation:

Network-related timeouts and changes in shards or processors are the most likely causes of duplicate data in this scenario.

Amazon Kinesis Data Streams Best Practices

Kinesis Producer Library (KPL) Overview

Kinesis Client Library (KCL) Overview

 Option C is the best solution to meet the requirements with the least effort because server-side encryption with AWS KMS keys (SSE-KMS) is a feature that allows you to encrypt data at rest in Amazon S3 using keys managed by AWS Key Management Service (AWS KMS). AWS KMS is a fully managed service that enables you to create and manage encryption keys for your AWS services and applications. AWS KMS also allows you to define granular access policies for your keys, such as who can use them to encrypt and decrypt data, and under what conditions. By using SSE-KMS, you can protect your S3 objects by using encryption keys that only specific employees can access, without having to manage the encryption and decryption process yourself.

Option A is not a good solution because it involves using AWS CloudHSM, which is a service that provides hardware security modules (HSMs) in the AWS Cloud. AWS CloudHSM allows you to generate and use your own encryption keys on dedicated hardware that is compliant with various standards and regulations. However, AWS CloudHSM is not a fully managed service and requires more effort to set up and maintain than AWS KMS. Moreover, AWS CloudHSM does not integrate with Amazon S3, so you have to configure the process that writes to S3 to make calls to CloudHSM to encrypt and decrypt the objects, which adds complexity and latency to the data protection process.

Option B is not a good solution because it involves using server-side encryption with customer-provided keys (SSE-C), which is a feature that allows you to encrypt data at rest in Amazon S3 using keys that you provide and manage yourself. SSE-C requires you to send your encryption key along with each request to upload or retrieve an object. However, SSE-C does not provide any mechanism to restrict access to the keys that encrypt the objects, so you have to implement your own key management and access control system, which adds more effort and risk to the data protection process.

Option D is not a good solution because it involves using server-side encryption with Amazon S3 managed keys (SSE-S3), which is a feature that allows you to encrypt data at rest in Amazon S3 using keys that are managed by Amazon S3. SSE-S3 automatically encrypts and decrypts your objects as they are uploaded and downloaded from S3. However, SSE-S3 does not allow you to control who can access the encryption keys or under what conditions. SSE-S3 uses a single encryption key for each S3 bucket, which is shared by all users who have access to the bucket. This means that you cannot restrict access to the keys that encrypt the objects by specific employees, which does not meet the requirements.

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

Protecting Data Using Server-Side Encryption with AWS KMS–Managed Encryption Keys (SSE-KMS) - Amazon Simple Storage Service

What is AWS Key Management Service? - AWS Key Management Service

What is AWS CloudHSM? - AWS CloudHSM

Protecting Data Using Server-Side Encryption with Customer-Provided Encryption Keys (SSE-C) - Amazon Simple Storage Service

Protecting Data Using Server-Side Encryption with Amazon S3-Managed Encryption Keys (SSE-S3) - Amazon Simple Storage Service

For low-latency, high-throughput workloads with API-based access and predictable reads/writes, Amazon DynamoDB is the optimal choice. It scales automatically, supports thousands of read/write operations per second, and offers fully managed API-driven access.

“Amazon DynamoDB provides consistent, single-digit millisecond latency for high-traffic applications and scales seamlessly to handle thousands of concurrent reads and writes.”

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

Amazon DynamoDB is a serverless, low-latency, NoSQL database ideal for semi-structured data.

Adding DynamoDB Accelerator (DAX) provides microsecond response times for read-heavy workloads.

“For applications requiring millisecond or sub-millisecond reads with serverless operation, use DynamoDB with DAX caching.”

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

The correct answer is D because Amazon Redshift row-level security provides granular access control over sensitive records by applying security policies at the database object level. This directly supports the requirement to let researchers access only records from their own departments without creating multiple copies of the dataset. Multi-condition SQL predicates are stronger than basic department-only filters because they can include department, role, sensitivity, and authorization logic in the same policy design. Creating separate schemas and views increases administrative overhead and can become inconsistent. Datashares are useful for sharing data but the option creates unnecessary departmental copies and does not solve record-level restriction by itself. Granting EXPLAIN RLS to researchers is also not needed for normal secure access.

The company needs to ingest tables from an on-premises Oracle database into a data lake on Amazon S3 in Apache Parquet format. The most efficient solution, requiring the least manual effort, would be to use AWS Database Migration Service (DMS) for continuous data replication.

Option C: Create an AWS Database Migration Service (AWS DMS) task for ongoing replication. Set the Oracle database as the source. Set Amazon S3 as the target. Configure the task to write the data in Parquet format.AWS DMS can continuously replicate data from the Oracle database into Amazon S3, transforming it into Parquet format as it ingests the data. DMS simplifies the process by providing ongoing replication with minimal setup, and it automatically handles the conversion to Parquet format without requiring manual transformations or separate jobs. This option is the least effort solution since it automates both the ingestion and transformation processes.

Other options:

Option A (Apache Sqoop on EMR) involves more manual configuration and management, including setting up EMR clusters and writing Sqoop jobs.

Option B (AWS Glue bookmark job) involves configuring Glue jobs, which adds complexity. While Glue supports data transformations, DMS offers a more seamless solution for database replication.

Option D (RDS and Lambda triggers) introduces unnecessary complexity by involving RDS and Lambda for a task that DMS can handle more efficiently.

The correct answer is B because the performance issue is caused by reading unnecessary data. The best optimization is to reduce the amount of data and schema fields that the AWS Glue job reads before transformation. Pushdown-style filtering and selecting only required fields reduce scan and serialization overhead, especially when the job transforms only a subset of the JSON structure. Job bookmarks help track previously processed data, but they do not reduce the amount of data read within each file. Splitting the job into several smaller jobs increases orchestration and does not solve unnecessary field reads. An AWS Glue crawler catalogs metadata; it does not convert JSON files into Parquet. Therefore, restricting the source schema and pushing filtering closer to the source is the best available answer.

 This solution meets the requirements of implementing real-time analytics capabilities with the least operational overhead. By creating an external schema in Amazon Redshift, you can access the data from Kinesis Data Streams using SQL queries without having to load the data into the cluster. By creating a materialized view on top of the stream, you can store the results of the query in the cluster and make them available for analysis. By setting the materialized view to auto refresh, you can ensure that the view is updated with the latest data from the stream at regular intervals. This way, you can derive near real-time insights by using existing BI and analytics tools. References:

Amazon Redshift streaming ingestion

Creating an external schema for Amazon Kinesis Data Streams

Creating a materialized view for Amazon Kinesis Data Streams

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