Amazon Web Services Data-Engineer-Associate - AWS Certified Data Engineer - Associate (DEA-C01)

 AWS Glue is a fully managed service that provides a serverless data integration platform. It can automatically discover and categorize data from various sources, including SAP HANA, Microsoft SQL Server, MongoDB, Apache Kafka, and Amazon DynamoDB. It can also infer the schema of the data and store it in the AWS Glue Data Catalog, which is a central metadata repository. AWS Glue can then use the schema information to generate and run Apache Spark code to extract, transform, and load the data into an Amazon S3 bucket. AWS Glue can also monitor and optimize the performance and cost of the data pipeline, and handle any schema changes that may occur in the source data. AWS Glue can meet the SLA of loading the data into the S3 bucket within 15 minutes of data creation, as it can trigger the data pipeline based on events, schedules, or on-demand. AWS Glue has the least operational overhead among the options, as it does not require provisioning, configuring, or managing any servers or clusters. It also handles scaling, patching, and security automatically. References:

AWS Glue

[AWS Glue Data Catalog]

[AWS Glue Developer Guide]

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

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

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

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

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

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

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

Amazon Athena Federated Queries:

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

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

Option A is the best answer because AWS DMS documentation recommends that, when practical, you create the replication instance in the same Region as your target endpoint, and in the same VPC or subnet as your target endpoint. In this scenario, the target is the Amazon Redshift cluster in eu-west-1 in Account_B, so placing the replication instance there is the most appropriate design. AWS DMS uses the replication instance to connect to the source, read the source data, transform it as needed, and load it into the target.

This also makes architectural sense because the replication instance must have network connectivity to both endpoints, and colocating it with the Redshift target usually simplifies connectivity to the target warehouse and aligns with AWS guidance. A replication instance in Account_B, eu-west-1 can still connect to the PostgreSQL source in Account_A, eu-east-1, provided the required networking and permissions are configured. AWS DMS supports replication tasks by defining a replication instance plus source and target endpoints.

The study guide also identifies AWS DMS as the correct managed service for database migration and continuous replication scenarios, including full load and change data capture. That matches this cross-account, cross-Region migration use case.

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

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

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

Option B is correct because IAM policies are the standard AWS mechanism to control access to Amazon S3, and SSE-KMS encrypts S3 data with AWS KMS keys. AWS S3 documentation for SSE-KMS explains that it uses AWS Key Management Service keys for server-side encryption, and Secrets Manager documentation states that you can store secrets securely and configure automatic rotation. AWS further explains that rotation updates the credentials in both the secret and the target database or service, and that Secrets Manager uses a Lambda rotation function for supported rotation patterns.

Option A is weaker because Lambda environment variables are not the right service for managed secret storage and automatic rotation. Option C is not the best answer because S3 ACLs are not the preferred modern access-control model compared with IAM and bucket policies, and Parameter Store is not the main AWS service for built-in managed database credential rotation. Option D uses SSE-S3, not KMS-based encryption, and relies on a custom rotation approach instead of the native managed secret-rotation service. The study guide also highlights AWS KMS for encryption-key management and Secrets Manager for rotating credentials.

Option B is correct because the requirement is to retain a very large dataset indefinitely and analyze it from Amazon Redshift in the most cost-effective way. Amazon Redshift Spectrum allows Redshift to query data directly in Amazon S3 without loading all of the data into Redshift-managed storage. That reduces warehouse storage cost for long-term retained data. AWS documentation also recommends using Apache Parquet for Spectrum because Parquet is a columnar format, which allows Redshift Spectrum to read only the columns needed instead of scanning entire text files. This improves performance and lowers query cost.

Option A and D are less cost-effective because auto-copy loads data into the Redshift cluster, which means the company pays to store all historical data in Redshift even though the requirement is indefinite retention of a large volume of IoT data. Option C is worse than B because JSON is a row-oriented text format, and AWS guidance says columnar formats such as Parquet or ORC are preferred for Redshift Spectrum for better scan efficiency and lower cost.

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