Free DEA-C01Practice Test Questions and Answers (2026)

The optimal solution is AWS Glue. It is a fully managed, serverless extract, transform, and load (ETL) service designed for this exact use case. AWS Glue crawlers can automatically scan various data sources (including JDBC sources like SAP HANA and SQL Server, and NoSQL sources like MongoDB and DynamoDB), infer the schema, and populate the AWS Glue Data Catalog. This directly addresses the requirement for handling undefined or changing schemas. The subsequent ETL jobs, which run on a serverless Apache Spark environment, can then use this catalog to process and load the data into Amazon S3. This serverless architecture ensures the "LEAST operational overhead" as there are no clusters to provision or manage.

1. AWS Glue Developer Guide: "AWS Glue provides a serverless environment to run your ETL jobs on a fully managed

scale-out Apache Spark environment... AWS Glue crawlers scan your data stores to determine the schema for your data and then create a metadata table in your AWS Glue Data Catalog."

Source: AWS Glue Developer Guide

"What is AWS Glue?"

Introduction section.

2. AWS Documentation - Choosing between AWS Glue and Amazon EMR: "AWS Glue is a good choice when your use case is ETL and you are looking for a serverless offering... If you want to avoid managing servers

you can use AWS Glue to run your Spark and Python shell workloads." This highlights Glue's lower operational overhead.

Source: AWS Big Data Blog

"Choosing between AWS Glue and Amazon EMR".

3. AWS Glue Developer Guide - Crawlers: "A crawler connects to a data store

progresses through a prioritized list of classifiers to determine the schema for your data

and then creates metadata tables in your AWS Glue Data Catalog." This confirms its schema detection capability for diverse sources.

Source: AWS Glue Developer Guide

"Defining Crawlers"

Crawler concepts section.

4. AWS Lambda Developer Guide - Quotas: "Timeout: 900 seconds (15 minutes)". This confirms the execution time limit that makes Lambda unsuitable for the described large-scale ETL task.

Source: AWS Lambda Developer Guide

"Lambda quotas".

5. Amazon Redshift Database Developer Guide - Redshift Spectrum: "Amazon Redshift Spectrum is a feature of Amazon Redshift that enables you to run SQL queries against exabytes of unstructured data in Amazon S3." This shows Redshift Spectrum is for querying data already in S3

not for performing the initial ETL to S3.

Source: Amazon Redshift Database Developer Guide

"Getting started with Amazon Redshift Spectrum".

AWS Step Functions is a serverless orchestration service that allows developers to coordinate multiple AWS services into flexible workflows. It provides direct, state-based integration for both AWS Lambda (Lambda:Invoke) and AWS Glue (Glue:StartJobRun). This serverless nature means there is no underlying infrastructure like servers or clusters to provision, patch, or manage, directly fulfilling the requirement for the "LEAST management overhead." The visual workflow also simplifies development, monitoring, and debugging of the multi-step pipeline.

1. AWS Step Functions Developer Guide: "AWS Step Functions is a serverless orchestration service that lets you combine AWS Lambda functions and other AWS services to build business-critical applications." It explicitly lists AWS Glue and AWS Lambda as supported service integrations.

Source: AWS Step Functions Developer Guide

"What is AWS Step Functions?" and "Supported AWS Service Integrations".

2. AWS Step Functions Developer Guide - Managing AWS Glue Jobs: "You can use Step Functions to start an AWS Glue job... Step Functions can wait for the job to complete

and then retrieve the results." This confirms the direct integration required by the scenario.

Source: AWS Step Functions Developer Guide

"Manage AWS Glue jobs with Step Functions".

3. AWS Glue Developer Guide - AWS Glue Workflows: "A workflow in AWS Glue is a container for a set of related jobs

crawlers

and triggers... You can use a workflow to create a complex multi-job extract

transform

and load (ETL) pipeline." The documentation focuses on orchestrating Glue-native components

not external services like Lambda as a primary step.

Source: AWS Glue Developer Guide

"Overview of workflows in AWS Glue".

4. AWS Whitepaper - Practicing Continuous Integration and Continuous Delivery on AWS: This paper discusses the operational burden of self-managed solutions. "When you manage your own... servers on Amazon EC2

you are responsible for scaling

managing

and maintaining the infrastructure... AWS-managed services... reduce your operational overhead." This principle supports choosing a managed service (Step Functions) over a self-hosted one (Airflow on EC2/EKS).

Source: AWS Whitepaper: "Practicing Continuous Integration and Continuous Delivery on AWS"

section on "Operational Responsibility".

Amazon Redshift streaming ingestion is a native feature designed for low-latency, high-speed data ingestion directly from Amazon Kinesis Data Streams. This solution has the least operational overhead because it is a fully managed process within Redshift. The user only needs to create an external schema pointing to the Kinesis stream and a materialized view to consume the data. Redshift then automatically and continuously ingests data in the background, making it available for querying in seconds. This directly meets the requirements for real-time queries and minimal management effort.

1. Amazon Redshift Database Developer Guide

"Streaming ingestion for Amazon Redshift": This guide states

"Amazon Redshift streaming ingestion provides low-latency

high-speed ingestion of streaming data from Amazon Kinesis Data Streams... into an Amazon Redshift materialized view... You can get started with streaming ingestion in minutes." This confirms it is a low-latency

low-overhead

native solution.

2. Amazon Redshift Database Developer Guide

"CREATE MATERIALIZED VIEW": The documentation for creating a materialized view from an external source like Kinesis states

"Amazon Redshift automatically refreshes the materialized view with the latest data from the streaming ingestion external table." This highlights the minimal operational overhead.

3. Amazon Data Firehose Developer Guide

"Choosing Amazon Redshift for Your Destination": This document explains the Firehose to Redshift process: "Amazon Data Firehose first delivers your incoming data to your S3 bucket... Then

Amazon Data Firehose issues an Amazon Redshift COPY command to load data...". This confirms the use of an intermediate S3 bucket and a batch COPY command

which is less direct and has higher latency than native streaming.

4. Amazon Managed Service for Apache Flink Developer Guide

"Sinks": The official documentation lists available sinks for Flink applications

which include Amazon S3

Amazon Kinesis Data Streams

Amazon Kinesis Data Firehose

and Amazon OpenSearch Service

but not a direct sink for Amazon Redshift.

The scenario describes a significant workload imbalance, with one node having a CPU load over 90% while others are under 15%. This is a classic symptom of data skew in Amazon Redshift. Data skew occurs when data is not distributed evenly across the compute nodes. In Redshift, the distribution of table rows to nodes is determined by the distribution key (DISTKEY). To resolve the imbalance, the DISTKEY should be set to a column with high cardinality (a large number of unique values), which ensures that the data is spread uniformly across all nodes. This directly addresses the root cause of the problem by balancing the workload, as required.

1. Amazon Redshift Documentation

"Choosing a data distribution style": "The goal of choosing a table distribution style is to distribute data as evenly as possible to parallelize the workload... If you specify KEY distribution

you must name a distribution key (DISTKEY) column... A column with high cardinality (a high number of unique values) helps distribute the data more evenly." This directly supports choosing a proper DISTKEY to resolve data skew.

2. Amazon Redshift Documentation

"Amazon Redshift best practices for designing tables": Under the section "Choose the best distribution style

" the documentation states

"To use KEY distribution

name one column as the distribution key (DISTKEY). The distribution key should have high cardinality... Choosing a column with low cardinality results in data skew

where some nodes process more data than others." This confirms that an improper DISTKEY causes the exact problem described.

3. Amazon Redshift Documentation

"Working with sort keys": "Sorting enables efficient handling of range-restricted predicate queries. Amazon Redshift stores your data on disk in sorted order according to the sort key." This reference clarifies that the sort key's function is for query optimization via data ordering

not data distribution across nodes.

4. Amazon Redshift Documentation

"Defining constraints": "Amazon Redshift doesn't enforce unique

primary key

and foreign key constraints... However

primary keys and foreign keys are used as planning hints and they should be declared if your ETL process or some other process in your application enforces them." This confirms that primary keys do not influence data placement.

The error message explicitly indicates a problem with the Amazon S3 VPC gateway endpoint. A VPC gateway endpoint for S3 functions by adding a specific route to the VPC's route tables. This route directs traffic destined for the S3 service prefix list to the gateway endpoint (vpce-xxxxxxxx) instead of an internet gateway. If the route table associated with the subnet where the AWS Glue job runs is missing this route, the job cannot establish a connection to S3 via the endpoint, resulting in a connectivity failure. Therefore, verifying the route table configuration is the correct and most direct action to resolve the issue.

1. AWS VPC User Guide

Gateway endpoints: "When you create a gateway endpoint

you specify the VPC route tables that are used to access the service from the VPC. A route is automatically added to each of the route tables that you specify... The route has a destination that is the prefix list for the service and a target that is the gateway endpoint." This confirms the route table is the mechanism for endpoint connectivity.

2. AWS Glue Developer Guide

Setting up a VPC to connect to data stores: In the section "Step 3: Create a VPC endpoint for Amazon S3

" the documentation states: "If your job accesses Amazon S3

you can configure a VPC endpoint... When you configure the endpoint

be sure to select the route table for the subnet that you configured...". This directly links the Glue job's subnet

its associated route table

and the S3 endpoint.

3. AWS Knowledge Center

How do I resolve AWS Glue connectivity issues with a VPC endpoint?: This resource often guides users to "Confirm that the route table associated with the subnet has a route to the Amazon S3 VPC endpoint." This reinforces that the route table is the primary point of failure for this type of error.

The most direct and efficient method to trigger the Lambda function after a Redshift load is by using the native integration between the Amazon Redshift Data API and Amazon EventBridge. When an asynchronous SQL statement executed via the Redshift Data API completes, it automatically emits a "Redshift Data API Statement Status Change" event to EventBridge. A rule can be configured in EventBridge to listen for these specific events (e.g., for a SUCCEEDED or FAILED status). This rule can then directly invoke the target Lambda function, providing it with the context of the completed job. This creates a decoupled, event-driven, and serverless workflow.

1. AWS Documentation: Using the Amazon Redshift Data API. "When a statement finishes

the Data API emits an event to Amazon EventBridge. You can create rules in EventBridge to take action when it receives an event." This confirms the direct integration path.

Source: AWS Documentation

Amazon Redshift Database Developer Guide

"Monitoring events with Amazon EventBridge".

2. AWS Documentation: Amazon Redshift Data API events. This page details the specific event

"Redshift Data API Statement Status Change

" that is sent to the default event bus in EventBridge when a statement changes state.

Source: AWS Documentation

Amazon EventBridge User Guide

"AWS service events"

"Amazon Redshift Data API events".

3. AWS Big Data Blog: Automate and operationalize your Amazon Redshift data loading. This official blog post explicitly describes the architecture in the correct answer. It states

"When the statement is complete

the Data API sends an event to Amazon EventBridge. You can create an EventBridge rule that looks for events from the Data API."

Source: AWS Big Data Blog

"Automate and operationalize your Amazon Redshift data loading"

published November 18

2021. Section: "Solution overview".

4. AWS Documentation: Invoking AWS Lambda functions. This document lists Amazon EventBridge (CloudWatch Events) as a primary

supported event source for invoking Lambda functions synchronously or asynchronously.

Source: AWS Documentation

AWS Lambda Developer Guide

"Using AWS Lambda with other services"

"Invoking Lambda functions".

AWS Glue workflows are specifically designed to orchestrate extract, transform, and load (ETL) activities. They provide a fully managed, serverless environment to create, visualize, and run complex data processing pipelines involving AWS Glue jobs, crawlers, and triggers. For a company already using AWS Glue and Amazon EMR, Glue workflows offer the most direct and native method for automation. This approach minimizes operational overhead as it is a built-in feature of AWS Glue, requiring no separate infrastructure management, unlike other options. It directly addresses the need for automated orchestration with minimal manual effort for the existing ETL processes.

1. AWS Glue Developer Guide

"Building a complex ETL workflow with AWS Glue": "You can use the AWS Glue console to create a complex extract

transform

and load (ETL) workflow with interdependent jobs

crawlers

and triggers. Each workflow manages the execution and monitoring of all its components... This provides a simple

graph-based way to author and visualize your data processing pipeline." This highlights its purpose-built nature for ETL orchestration.

2. AWS Glue Developer Guide

"Overview of AWS Glue": "AWS Glue is a fully managed ETL (extract

transform

and load) service... It is serverless

so there is no infrastructure to set up or manage." This confirms the low operational overhead of the service and its integrated features like workflows.

3. AWS Step Functions Developer Guide

"What is AWS Step Functions?": "AWS Step Functions is a serverless orchestration service that lets you integrate with AWS Lambda functions and other AWS services..." While capable

its general-purpose nature makes it less specific than Glue Workflows for this ETL-focused scenario.

4. Amazon MWAA User Guide

"What is Amazon MWAA?": "Amazon Managed Workflows for Apache Airflow (MWAA) is a managed orchestration service for Apache Airflow... You can choose a range of available compute resources to execute your workflows." The need to choose and manage compute resources indicates higher operational overhead compared to a fully serverless option.

S3 Object Lambda is the ideal solution as it is purpose-built to modify data as it is retrieved from Amazon S3. By creating an S3 Object Lambda endpoint, a Lambda function can be invoked to process the data from any S3 GET request made through that endpoint. This function can contain logic to inspect the request context and dynamically redact Personally Identifiable Information (PII) before the data is returned to the calling application. This approach maintains a single, original copy of the dataset in S3, fulfilling the dynamic redaction requirement with the least operational overhead and without data duplication.

1. Amazon S3 User Guide. The official documentation explicitly lists PII redaction as a primary use case for S3 Object Lambda. It states

"With S3 Object Lambda

you can add your own code to S3 GET...requests to modify and process data as it is returned to an application." It further clarifies a use case: "Redacting personally identifiable information (PII) – You can redact PII data...for any application that does not need access to this data." This avoids creating derivative copies.

Source: AWS Documentation

Amazon S3 User Guide

"Transforming objects with S3 Object Lambda".

2. AWS Blog: "Filter and redact sensitive data from your data lake using Amazon S3 Object Lambda". This official AWS blog post provides a detailed architectural pattern for the exact scenario in the question. It demonstrates using S3 Object Lambda to "dynamically filter and redact data based on the user’s permissions" without creating multiple copies of the dataset

highlighting it as a low-overhead solution.

Source: AWS Big Data Blog

November 15

2021.

3. AWS Whitepaper: "Best Practices for Organizing

Training

and Securing Your Data Lake". While discussing data governance and security

such whitepapers advocate for mechanisms that can apply policies dynamically at the time of access. S3 Object Lambda is a direct implementation of this principle

allowing for fine-grained

dynamic data masking without the overhead of pre-processing and storing multiple data versions.

Source: AWS Whitepaper

"Best Practices for Organizing

Training

and Securing Your Data Lake"

section on "Data Governance and Security".

The most cost-effective and scalable solution for performing Change Data Capture (CDC) on large, file-based snapshots in a data lake is to use an open-source transactional data lake format like Apache Hudi, Apache Iceberg, or Delta Lake. These formats are specifically designed to bring database-like capabilities, such as MERGE (upsert) operations, directly to data stored in Amazon S3. Using a service like AWS Glue or Amazon EMR, the daily snapshot can be efficiently merged with the existing data lake table, applying only the inserts and updates. This avoids the high cost and architectural complexity of loading terabyte-scale files into a relational database.

1. AWS Big Data Blog

"Implement a CDC-based ETL pipeline using Amazon S3

AWS Glue

and Apache Hudi": This article details the exact pattern described in the correct answer. It states

"Apache Hudi enables you to manage data at the record level in Amazon S3 to perform inserts

updates

and deletes... This helps in use cases like change data capture (CDC)..." This directly supports using a format like Hudi for the described CDC operation.

2. AWS Glue Developer Guide

"Using transactional data lake frameworks with AWS Glue": The documentation confirms native support for these formats. "AWS Glue supports the open-source transactional data lake frameworks: Apache Hudi

Apache Iceberg

and Linux Foundation Delta Lake. These frameworks allow you to run ACID transactions on your Amazon S3 based data lake." This shows that the solution in option C is a well-supported pattern on AWS.

3. AWS Lambda Developer Guide

"Lambda quotas": The official documentation lists the "Function timeout" as 900 seconds (15 minutes). This technical limitation makes option A infeasible for processing terabyte-scale files

which would take significantly longer.

4. AWS Database Migration Service User Guide

"What Is AWS Database Migration Service?": The guide describes AWS DMS as a tool to "migrate your data to and from most widely used commercial and open-source databases." Its primary use case is database-to-database replication

not performing CDC on flat files in S3. This makes options B and D architecturally inappropriate.

The most secure and recommended approach for managing database credentials within AWS services is to use AWS Secrets Manager. Storing the Amazon Redshift credentials in AWS Secrets Manager (D) ensures they are encrypted at rest and managed centrally. To allow the AWS Glue job to retrieve these credentials at runtime, the IAM role associated with the job must be granted the appropriate permissions (E), specifically secretsmanager:GetSecret-Value, to access the specific secret. This combination eliminates hardcoded credentials from the script, addressing the security vulnerability while following AWS best practices for secret management.

1. AWS Glue Developer Guide

"Using secrets in AWS Glue connections": "You can use AWS Secrets Manager to store your credentials and then refer to them in your connection options... To use a secret with your connection

you must add secretsmanager:GetSecretValue permission to the IAM role that you use to access the connection." This source directly validates the combination of storing credentials in Secrets Manager (D) and granting the IAM role access (E).

2. AWS Secrets Manager User Guide

"What Is AWS Secrets Manager?": "Secrets Manager helps you protect secrets needed to access your applications

services

and IT resources. The service enables you to easily rotate

manage

and retrieve database credentials

API keys

and other secrets throughout their lifecycle." This supports the choice of Secrets Manager (D) as the appropriate service for this task.

3. AWS Glue Developer Guide

"Connection properties": "We recommend that you use AWS Secrets Manager to store your credentials and then refer to them in your connection options. Don't store passwords in the connection properties." This explicitly advises against insecure practices like using job parameters (A) and recommends the chosen solution.

The most secure and recommended method for granting AWS permissions to applications running in Amazon EKS is to use IAM Roles for Service Accounts (IRSA). This feature associates an IAM role with a Kubernetes service account. Pods that use this service account can then assume the associated IAM role and receive temporary, short-lived security credentials from the AWS Security Token Service (STS). This approach adheres to the principle of least privilege and completely avoids the need to store or manage long-lived IAM user credentials (access keys) within the EKS cluster, pods, or container images, directly meeting the stated requirements.

1. AWS Documentation

Amazon EKS User Guide: "IAM roles for service accounts." This document states

"With IAM roles for service accounts

you can associate an IAM role with a Kubernetes service account. This service account can then provide AWS permissions to the containers in any pod that uses that service account." It details how the pod receives a web identity token and exchanges it for temporary AWS credentials.

Source: AWS Documentation

Amazon EKS User Guide

"IAM roles for service accounts".

2. AWS Documentation

IAM User Guide: "IAM best practices." This guide strongly advises against using long-term credentials like IAM user access keys when a temporary alternative is available. It recommends using IAM roles to grant temporary permissions.

Source: AWS Documentation

IAM User Guide

"Security best practices in IAM"

section "Grant least privilege".

3. AWS Documentation

Amazon EKS User Guide: "Amazon EKS node IAM role." This document explains the role of the worker node's IAM policy

which is a prerequisite for the cluster to function but is distinct from the application-specific permissions granted via IRSA. The IRSA mechanism is the specific solution for pod-level permissions.

Source: AWS Documentation

Amazon EKS User Guide

"Amazon EKS node IAM role".

The problem states that AWS Glue jobs are running for a long time, which in turn slows down Amazon QuickSight queries. The goal is to improve the performance of the Glue jobs.

1. Partitioning Data (A): Partitioning the data in Amazon S3 by a logical key, such as date (year, month, day), is a fundamental performance optimization technique. When the Glue job runs, it can use partition pruning to read only the specific folders (partitions) of data it needs, rather than scanning the entire dataset in the S3 bucket. This dramatically reduces the amount of data read, which is often the primary bottleneck, thus speeding up the job.

2. Scaling Up Workers (B): AWS Glue jobs run on a serverless Apache Spark environment. Increasing the worker type (e.g., from G.1X to G.2X) provides each worker node with more vCPU, memory, and disk space. This vertical scaling is effective for jobs that are memory-intensive or CPU-bound, allowing them to process data faster.

1. AWS Glue Developer Guide

"Managing partitions for ETL output in AWS Glue": "You can partition your data to speed up queries. AWS Glue can prune the partitions that are not required by a query

which can speed up queries on large datasets." This directly supports option A.

2. AWS Glue Developer Guide

"Job properties": This section details the Worker type parameter. It states

"For memory-intensive jobs

you can specify the G.1X

G.2X

G.4X

G.8X

or Z.2X worker types... Choosing a more powerful worker type can help your job run faster." This directly supports option B.

3. AWS Glue Developer Guide

"Working with DynamicFrames": This documentation describes DynamicFrames as the fundamental data structure used for ETL operations in AWS Glue

not as a performance tuning destination. This refutes option C.

The core requirement is to implement row-level security on data stored in Amazon S3 with minimal operational overhead. AWS Lake Formation is specifically designed for this purpose. By registering the S3 bucket with Lake Formation, a governance team can centrally define and enforce fine-grained access control policies, including row-level security. This is achieved by creating data filters based on attributes (like the 'country' column) and granting permissions to principals (analysts). This approach avoids data duplication or movement and centralizes security management, directly aligning with the principle of least operational effort.

1. AWS Lake Formation Developer Guide

"Data filtering and cell-level security": This section explicitly describes how to use Lake Formation to grant access to specific rows and columns in your data lake. It states

"You can implement column-level

row-level

and cell-level security by granting the SELECT Lake Formation permission on tables with data filters." This directly supports option B as the intended mechanism.

2. AWS Lake Formation Developer Guide

"How data filtering works": The documentation details the process: "When a principal runs a query against a table that has data filters

Lake Formation evaluates the filters for that principal to determine which rows they are allowed to see." This confirms Lake Formation's capability to meet the requirement.

3. AWS Documentation

"Getting started with Amazon Redshift": The documentation outlines the steps to set up a Redshift cluster and load data. This process involves creating a cluster

designing tables

and running COPY commands

which represents significantly more operational effort than applying a Lake Formation policy to existing S3 data

making option D less efficient.

4. AWS Big Data Blog

"Implement row-level security with Amazon Redshift": While this blog shows row-level security is possible in Redshift

it involves creating views and managing user-to-role mappings within the database. This is more complex and service-specific than the centralized

cross-service governance provided by Lake Formation for a data lake

reinforcing that option D has higher operational effort.

To meet the requirements with the least operational overhead, the data engineer should leverage the native, managed features of AWS Glue. AWS Glue connections are Data Catalog objects that securely store and manage the connection information for data sources like Amazon RDS and MongoDB, and targets like Amazon Redshift. This centralizes connectivity management and simplifies the ETL script.

For the hourly execution requirement, AWS Glue triggers provide a built-in, serverless scheduling mechanism. A time-based trigger can be configured to run the ETL job every hour without the need to provision or manage external scheduling infrastructure like AWS Lambda functions, thus minimizing operational overhead.

1. AWS Glue Developer Guide

"Defining connections in the AWS Glue Data Catalog": This guide explains that an AWS Glue connection is a Data Catalog object used to store connection information for data stores. It explicitly lists Amazon RDS

MongoDB

and Amazon Redshift as supported through JDBC and MongoDB connection types

respectively. This supports the use of AWS Glue connections (Option D) as the standard

managed approach.

2. AWS Glue Developer Guide

"Triggering jobs in AWS Glue": This section details the different types of triggers

including scheduled triggers. It states

"You can define a time-based schedule for your jobs... This scheduling capability runs on a cron-like schedule." This confirms that triggers (Option A) are the native

low-overhead mechanism for hourly job execution.

3. AWS Glue Developer Guide

"Moving data to and from Amazon Redshift": This documentation describes the recommended method for loading data into Amazon Redshift from an AWS Glue ETL job

which involves using a JDBC connection (managed by AWS Glue Connections) and a temporary Amazon S3 directory. This reinforces that Option D is the correct approach for connectivity

making Option E a less standard

higher-overhead alternative.

Amazon Redshift recommends ALL distribution only for small, infrequently-updated reference tables (≈10 MB or less). Replicating those tables to every node eliminates redistribution during joins and speeds queries without adding cluster capacity. Large fact tables should keep EVEN or KEY distribution. Defining primary and foreign keys further helps the optimizer choose join plans, but changing distribution only on the small tables meets the performance goal while respecting budget limits.

1. Amazon Redshift Developer Guide

“Choosing a Table Distribution Style – ALL Distribution

” para. 3: “Use ALL only for small tables (typically <10 MB) that aren’t updated often…” (https://docs.aws.amazon.com/redshift/latest/dg/cdistributionstyles.html).

2. Amazon Redshift Developer Guide

“Table Design—Best Practices

” sec. “Distribution Styles”: warns that ALL is unsuitable for large tables and EVEN can cause redistribution during joins (https://docs.aws.amazon.com/redshift/latest/dg/cbest-practices-best-way.html).

3. Amazon Redshift Engineering “Advanced Table Design Playbook

” p. 6: reiterates ALL for small dimension tables

never for large fact tables.

4. Amazon Redshift Developer Guide

“Limitations” - “Table partitioning is not supported for internal tables.” (https://docs.aws.amazon.com/redshift/latest/dg/climitations.html).

The Amazon Redshift query editor v2 provides a native feature to schedule SQL queries directly from the AWS Management Console. This allows a user to write the REFRESH MATERIALIZED VIEW statement and configure a recurring schedule with minimal configuration. This approach is the most direct, integrated, and requires the least operational overhead compared to setting up external orchestration services or misusing other features. It is specifically designed for automating such SQL tasks, perfectly aligning with the "LEAST effort" requirement.

1. Amazon Redshift Database Developer Guide - Scheduling a query in the Amazon Redshift query editor v2: "You can schedule a SQL query to run on a recurring basis using the Amazon Redshift query editor v2. ... You can use this feature to automate your SQL statements

for example to refresh materialized views or run data definition language (DDL) statements." This directly confirms that option C is the intended and lowest-effort method.

2. Amazon Redshift Database Developer Guide - Creating a user-defined function: "A UDF is a scalar function that returns a single value for each row of input." This clarifies that the purpose of a UDF (including Lambda UDFs) is to return a value to a query

not to execute standalone DDL commands

making option B an incorrect application of the technology.

3. AWS Documentation - Using Amazon Redshift with AWS Glue: This documentation outlines the necessary setup for Glue to interact with Redshift

which includes creating connections and IAM roles

demonstrating a higher level of effort than the native scheduler.

4. Amazon Managed Workflows for Apache Airflow (MWAA) User Guide - Amazon Redshift operators: This guide shows that interacting with Redshift from Airflow is possible but requires provisioning and managing an entire Airflow environment

which is a high-effort solution for this specific task.

The most optimal solution is the Amazon Aurora zero-ETL integration with Amazon Redshift. This feature is specifically designed for near real-time analytics on operational data from Aurora within Redshift. It automates the complex process of building and managing data pipelines, thus fulfilling the requirement for the "LEAST operational effort." The integration continuously and automatically replicates data from Aurora to Redshift within seconds of being written, ensuring low latency for reporting without impacting the performance of the source operational Aurora database. Creating a materialized view in Redshift on this replicated data is the correct subsequent step for performant joins with the Campaigns table.

1. Amazon Aurora Documentation - Zero-ETL integrations: "Amazon Aurora zero-ETL integration with Amazon Redshift enables near real-time analytics and machine learning (ML) on petabytes of transactional data stored in Aurora. ... With a few clicks in the Amazon RDS console

you can create a zero-ETL integration... This fully managed solution eliminates the need for you to build and manage complex data pipelines that perform extract

transform

and load (ETL) operations."

Source: AWS Documentation

Working with Amazon Aurora zero-ETL integrations with Amazon Redshift

Introduction section.

2. Amazon Redshift Documentation - Zero-ETL integrations: "An Amazon Aurora zero-ETL integration with Amazon Redshift automatically and continuously replicates data... to an Amazon Redshift data warehouse. This replication enables you to perform analytics on your transactional data in near-real time... without having to build and maintain a data pipeline."

Source: AWS Documentation

Amazon Redshift Database Developer Guide

"Working with zero-ETL integrations"

Overview section.

3. AWS Documentation - Comparing Zero-ETL to other methods: The documentation implicitly positions zero-ETL as the simplest approach. While DMS and Glue are powerful

they are presented as solutions for more complex scenarios involving transformations or heterogeneous sources

which implies more setup. The very name "zero-ETL" is intended to convey minimal operational burden.

Source: AWS News Blog

Announcing Amazon Aurora zero-ETL integration with Amazon Redshift in preview

November 29

2022. This blog post contrasts the new feature with the "complex and manually intensive" process of building ETL pipelines.

The scenario describes a workload that is CPU-bound, indicated by the cluster reaching maximum CPU usage, while memory is significantly underutilized (less than 30%). General-purpose instances provide a balanced ratio of CPU and memory. The most cost-effective solution is to right-size the instances to match the workload's specific resource requirements. Compute-optimized EC2 instances (e.g., C-family) provide a higher ratio of vCPU to memory compared to general-purpose instances. By switching to compute-optimized instances, the company can get more processing power to alleviate the CPU bottleneck without paying for unneeded memory, leading to faster job completion and lower overall costs.

1. Amazon EC2 Documentation

Instance Types: "Compute optimized instances are ideal for compute-bound applications that benefit from high-performance processors. ... Use cases: ... batch processing workloads...". This directly supports using compute-optimized instances for the described ETL job.

Source: AWS Documentation. (n.d.). Amazon EC2 Instance Types. Amazon Web Services. Retrieved from https://aws.amazon.com/ec2/instance-types/

2. AWS Well-Architected Framework

Cost Optimization Pillar: The framework emphasizes right-sizing and selecting the most appropriate resource type. "For example

workloads that are processing-intensive can be deployed on compute-optimized instances." This principle directly applies to the scenario.

Source: AWS Well-Architected Framework. (2023

July). Cost Optimization Pillar. p. 18. Retrieved from https://docs.aws.amazon.com/wellarchitected/latest/cost-optimization-pillar/cost-optimization-pillar.pdf

3. Amazon EMR Management Guide

Hardware and networking: "For workloads that are compute-intensive

consider using an instance type from the compute-optimized family (for example

c5.4xlarge)." This provides specific guidance for EMR cluster configuration based on workload characteristics.

Source: AWS Documentation. (n.d.). Choose EC2 instance types and sizes. Amazon EMR Management Guide. Retrieved from https://docs.aws.amazon.com/emr/latest/ManagementGuide/emr-plan-instances-guidelines.html

The solution must maintain high availability throughout the data lifecycle. S3 One Zone-Infrequent Access (S3 One Zone-IA) stores data in a single Availability Zone and is therefore not a highly available solution, which eliminates options A and D. For data accessed infrequently between 6 months and 2 years, S3 Standard-Infrequent Access (S3 Standard-IA) is the correct choice as it maintains multi-AZ resilience. For data older than 2 years that is accessed only once or twice annually, S3 Glacier Deep Archive is the most cost-effective storage class. It is designed for long-term data archiving and retention at the lowest cost, fitting the access pattern perfectly.

1. Amazon S3 User Guide

"Amazon S3 storage classes." This document details the use cases

availability

and durability of each S3 storage class. It specifies that S3 Standard-IA is for "long-lived

infrequently accessed data" and maintains the same high availability (99.9%) and durability as S3 Standard by storing data across multiple Availability Zones. In contrast

it states S3 One Zone-IA is for "recreatable

infrequently accessed data" and is stored in a single AZ

making it unsuitable for this scenario's high availability requirement. It also identifies S3 Glacier Deep Archive as the "lowest-cost storage class" for long-term archive.

2. Amazon S3 User Guide

"Managing your storage lifecycle." This guide explains how to create lifecycle configuration rules to transition objects between storage classes. The scenario described in the question is a primary use case for S3 Lifecycle policies

transitioning objects to more cost-effective storage tiers based on age and access patterns. The path from S3 Standard -> S3 Standard-IA -> S3 Glacier Deep Archive is a common and recommended lifecycle configuration for cost optimization.

3. AWS Documentation

"Comparing the Amazon S3 storage classes." The comparison table in this official documentation explicitly shows S3 Standard-IA has "≥ 3" Availability Zones

while S3 One Zone-IA has "1" Availability Zone. This directly supports the elimination of options A and D. The table also lists the primary use case for S3 Glacier Deep Archive as "Archiving data that rarely

if ever

is accessed

" which aligns with the question's scenario for data older than two years.

The Amazon Redshift SUPER data type is specifically designed for ingesting, storing, and querying semi-structured data, including JSON, without requiring a predefined schema. Using the SUPER data type allows the data engineer to load the JSON data directly into a Redshift table using a simple COPY command. This approach avoids the overhead of setting up and managing an external ETL process (like AWS Glue) or writing custom transformation code (like an AWS Lambda function), thus representing the solution with the least effort. The data can then be queried natively within Redshift using the PartiQL query language.

1. Amazon Redshift Database Developer Guide. "Ingesting and querying semistructured data." This section introduces the SUPER data type as the native solution for storing schema-less data like JSON directly within Amazon Redshift. It states

"With the SUPER data type

you can ingest

store

and query semistructured data in Amazon Redshift."

2. Amazon Redshift Database Developer Guide. "Loading SUPER data." This page details the COPY command for loading JSON data. It shows that loading can be done with a simple command like COPY mytable FROM 's3://...' FORMAT AS JSON 'auto';

which exemplifies the minimal effort required.

3. Amazon Redshift Database Developer Guide. "Querying semistructured data." This section explains how to use PartiQL to query the data stored in SUPER columns

confirming its usability post-ingestion without complex transformations. This contrasts with the effort needed to set up external services.