Redshift

AWS Redshift Advanced

~ Last updated on : ~ jayendrapatil ~ 12 Comments

AWS Redshift Advanced

  • Redshift Distribution Style determines how data is distributed across compute nodes and helps minimize the impact of the redistribution step by locating the data where it needs to be before the query is executed.
  • Redshift enhanced VPC routing forces all COPY and UNLOAD traffic between the cluster and the data repositories through the VPC.
  • Redshift workload management (WLM) enables users to flexibly manage priorities within workloads so that short, fast-running queries won’t get stuck in queues behind long-running queries.
  • Redshift Spectrum helps query and retrieve structured and semistructured data from files in S3 without having to load the data into Redshift tables.
  • Redshift Federated Query feature allows querying and analyzing data across operational databases, data warehouses, and data lakes.
  • Zero-ETL Integrations facilitate point-to-point data movement from operational databases to Redshift without the need to build and manage data pipelines.
  • Redshift Data Sharing enables live, transactionally consistent data sharing across Redshift clusters without copying data.
  • Redshift Serverless automatically provisions and scales data warehouse capacity without managing infrastructure.

Distribution Styles

  • Table distribution style determines how data is distributed across compute nodes and helps minimize the impact of the redistribution step by locating the data where it needs to be before the query is executed.
  • Redshift supports four distribution styles; AUTO, EVEN, KEY, or ALL.

KEY distribution

  • A single column acts as a distribution key (DISTKEY) and helps place matching values on the same node slice.
  • As a rule of thumb, choose a column that:
    • Is uniformly distributed – Otherwise skew data will cause unbalances in the volume of data that will be stored in each compute node leading to undesired situations where some slices will process bigger amounts of data than others and causing bottlenecks.
    • acts as a JOIN column – for tables related to dimensions tables (star-schema), it is better to choose as DISTKEY the field that acts as the JOIN field with the larger dimension table, so that matching values from the common columns are physically stored together, reducing the amount of data that needs to be broadcasted through the network.

EVEN distribution

  • distributes the rows across the slices in a round-robin fashion, regardless of the values in any particular column
  • Choose EVEN distribution
    • when the table does not participate in joins
    • when there is not a clear choice between KEY and ALL distribution.

ALL distribution

  • Whole table is replicated in every compute node.
  • ensures that every row is collocated for every join that the table participates in.
  • ideal for relatively slow-moving tables, tables that are not updated frequently or extensively.
  • Small dimension tables DO NOT benefit significantly from ALL distribution, because the cost of redistribution is low.

AUTO distribution

  • Redshift assigns an optimal distribution style based on the size of the table data for e.g. apply ALL distribution for a small table and as it grows changes it to Even distribution
  • Amazon Redshift applies AUTO distribution, by default.
  • Redshift’s automatic table optimization (ATO) continuously monitors query patterns and can automatically adjust distribution keys and sort keys for optimal performance.

Sort Key

  • Sort keys define the order in which the data will be stored.
  • Sorting enables efficient handling of range-restricted predicates.
  • Only one sort key per table can be defined, but it can be composed of one or more columns.
  • Redshift stores columnar data in 1 MB disk blocks. The min and max values for each block are stored as part of the metadata. If the query uses a range-restricted predicate, the query processor can use the min and max values to rapidly skip over large numbers of blocks during table scans
  • The are two kinds of sort keys in Redshift: Compound and Interleaved.

Compound Keys

  • A compound key is made up of all of the columns listed in the sort key definition, in the order, they are listed.
  • A compound sort key is more efficient when query predicates use a prefix, or query’s filter applies conditions, such as filters and joins, which is a subset of the sort key columns in order.
  • Compound sort keys might speed up joins, GROUP BY and ORDER BY operations, and window functions that use PARTITION BY and ORDER BY.

Interleaved Sort Keys

  • An interleaved sort key gives equal weight to each column in the sort key, so query predicates can use any subset of the columns that make up the sort key, in any order.
  • An interleaved sort key is more efficient when multiple queries use different columns for filters.
  • Don’t use an interleaved sort key on columns with monotonically increasing attributes, such as identity columns, dates, or timestamps.
  • Use cases involve performing ad-hoc multi-dimensional analytics, which often requires pivoting, filtering, and grouping data using different columns as query dimensions.
  • Note: AWS recommends using compound sort keys for most workloads. Interleaved sort keys require more maintenance (VACUUM REINDEX) and have higher overhead.

Constraints

  • Redshift does not support Indexes.
  • Redshift supports UNIQUE, PRIMARY KEY, and FOREIGN KEY constraints, however, they are only for informational purposes.
  • Redshift does not perform integrity checks for these constraints and is used by the query planner, as hints, in order to optimize executions.
  • Redshift does enforce NOT NULL column constraints.

Redshift Enhanced VPC Routing

  • Redshift enhanced VPC routing forces all COPY and UNLOAD traffic between the cluster and the data repositories through the VPC.
  • Without enhanced VPC routing, Redshift would route traffic through the internet, including traffic to other services within the AWS network.
  • Enhanced VPC routing is now supported for zero-ETL integration warehouses (as of September 2024), enabling secure data replication within the VPC.

Redshift Workload Management

  • Redshift workload management (WLM) enables users to flexibly manage priorities within workloads so that short, fast-running queries won’t get stuck in queues behind long-running queries.
  • Redshift provides query queues, in order to manage concurrency and resource planning. Each queue can be configured with the following parameters:
    • Slots: number of concurrent queries that can be executed in this queue.
    • Working memory: percentage of memory assigned to this queue.
    • Max. Execution Time: the amount of time a query is allowed to run before it is terminated.
  • Queries can be routed to different queues using Query Groups and User Groups.
  • As a rule of thumb, it is considered a best practice to have separate queues for long running resource-intensive queries and fast queries that don’t require big amounts of memory and CPU.
  • By default, Redshift configures one queue with a concurrency level of five, which enables up to five queries to run concurrently, plus one predefined Superuser queue, with a concurrency level of one.
  • A maximum of eight queues can be defined, with each queue configured with a maximum concurrency level of 50. The maximum total concurrency level for all user-defined queues (not including the Superuser queue) is 50.
  • Redshift WLM supports two modes – Manual and Automatic
    • Automatic WLM supports queue priorities.
    • Automatic WLM is the recommended mode and uses ML to dynamically allocate resources.
  • Query Monitoring Rules (QMR) define metrics-based performance boundaries for WLM queues and specify actions when a query exceeds those boundaries.
    • Up to 25 rules per queue, with a limit of 25 rules across all queues.
    • Each rule includes up to three conditions (predicates) and one action (log, cancel, hop, or change priority).
    • Queue-based QMR is now supported in Redshift Serverless (2026), enabling granular workload control.

Redshift Concurrency Scaling

  • Concurrency Scaling helps support thousands of concurrent users and concurrent queries, with consistently fast query performance.
  • With Concurrency scaling, Redshift automatically adds additional cluster capacity to process an increase in both read and write queries.
  • Queries run on the main cluster or a concurrency-scaling cluster returns the most current data.
  • Queries sent to the concurrency-scaling cluster can be managed by configuring WLM queues.
  • Concurrency scaling now supports more types of write queries (INSERT, CREATE TABLE AS, UPDATE, DELETE), expanding beyond read-only scaling.

Redshift Short Query Acceleration – SQA

  • Short query acceleration (SQA) prioritizes selected short-running queries ahead of longer-running queries.
  • SQA runs short-running queries in a dedicated space, so that SQA queries aren’t forced to wait in queues behind longer queries.
  • SQA only prioritizes queries that are short-running and are in a user-defined queue.

Redshift Loading Data

  • A COPY command is the most efficient way to load a table.
    • COPY command is able to read from multiple data files or multiple data streams simultaneously.
    • Redshift allocates the workload to the cluster nodes and performs the load operations in parallel, including sorting the rows and distributing data across node slices.
    • COPY command supports loading data from S3, EMR, DynamoDB, and remote hosts such as EC2 instances using SSH.
    • COPY supports decryption and can decrypt the data as it performs the load if the data is encrypted
    • COPY can then speed up the load process by uncompressing the files as they are read if the data is compressed.
    • COPY command can be used with COMPUPDATE set to ON to analyze and apply compression automatically based on sample data.
    • Optimizing storage for narrow tables (multiple rows few columns) by using Single COPY command instead of multiple COPY commands, as it would not work well due to hidden fields and compression issues.
  • Auto Copy
    • Auto-copy (GA October 2024) provides the ability to automate copy statements by tracking S3 folders and ingesting new files without customer intervention.
    • Without Auto-copy, a copy statement immediately starts the file ingestion process for existing files.
    • Auto-copy extends the existing copy command and provides the ability to
      • Automate file ingestion process by monitoring specified S3 paths for new files
      • Re-use copy configurations, reducing the need to create and run new copy statements for repetitive ingestion tasks and
      • Keep track of loaded files to avoid data duplication.
  • Streaming Ingestion
    • Redshift supports streaming ingestion from Amazon Kinesis Data Streams, Amazon MSK, Confluent Managed Cloud, and self-managed Apache Kafka clusters.
    • Streaming ingestion uses materialized views to ingest data from streams directly into Redshift tables for near real-time analytics.
    • Supports cascading refresh of nested materialized views on streaming sources (2025).
  • INSERT command
    • Clients can connect to Amazon Redshift using ODBC or JDBC and issue ‘insert’ SQL commands to insert the data.
    • INSERT command is much less efficient than using COPY as they are routed through the single leader node.

Redshift Resizing Cluster

  • Elastic resize
    • Use elastic resize to change the node type, number of nodes, or both.
    • If only the number of nodes is changed, then queries are temporarily paused and connections are held open if possible.
    • During the resize operation, the cluster is read-only.
    • Elastic resize takes 10–15 minutes.
  • Classic resize
    • Use classic resize to change the node type, number of nodes, or both.
    • During the resize operation, data is copied to a new cluster and the source cluster is read-only
    • Classic resize takes 2 hours – 2 days or longer, depending on the data’s size
  • Snapshot and restore with classic resize
    • To keep the cluster available during a classic resize, create a snapshot, make a copy of an existing cluster, then resize the new cluster.

Redshift Spectrum

  • Redshift Spectrum helps query and retrieve structured and semistructured data from files in S3 without having to load the data into Redshift tables.
  • Redshift Spectrum queries employ massive parallelism to execute very fast against large datasets. Much of the processing occurs in the Redshift Spectrum layer, and most of the data remains in S3.
  • Multiple clusters can concurrently query the same dataset in S3 without the need to make copies of the data for each cluster.
  • Redshift Spectrum resides on dedicated Redshift servers that are independent of the existing cluster.
  • Redshift Spectrum pushes many compute-intensive tasks, such as predicate filtering and aggregation, down to the Redshift Spectrum layer.
  • Redshift Spectrum also scales automatically, based on the demands of the queries, and can potentially use thousands of instances to take advantage of massively parallel processing.
  • Supports external data catalog using Glue, Athena, or Hive metastore
  • Supports querying Apache Iceberg tables and S3 Tables (purpose-built tabular storage with Iceberg support, launched at re:Invent 2024).
  • Iceberg query performance has improved up to 3x year-over-year through optimizations including Glue Data Catalog statistics, dynamic partition elimination, and parallel manifest file processing.
  • Supports incremental refresh for materialized views on data lake tables, eliminating the need for full recomputation when new data arrives.
  • Redshift cluster and the S3 bucket must be in the same AWS Region.
  • Redshift Spectrum external tables are read-only. You can’t COPY or INSERT to an external table.

Redshift Federated Query

  • Redshift Federated Query feature allows querying and analyzing data across operational databases, warehouses, and lakes.
  • Redshift Federated Query allows integrating queries on live data in RDS for PostgreSQL, Aurora PostgreSQL, RDS for MySQL, and Aurora MySQL with queries across Redshift and S3.
  • Supports both PostgreSQL and MySQL engines for federated access.

Zero-ETL Integrations

  • Zero-ETL integrations facilitate point-to-point data movement from operational databases to Redshift without the need to build and manage custom data pipelines.
  • Provides near real-time analytics on transactional data within seconds of it being written to the source.
  • Supported Sources:
    • Amazon Aurora MySQL-Compatible Edition (first zero-ETL source)
    • Amazon Aurora PostgreSQL-Compatible Edition (GA October 2024)
    • Amazon RDS for MySQL (GA September 2024)
    • Amazon DynamoDB (GA October 2024)
    • Self-managed databases (MySQL, PostgreSQL) via CDC replication
    • Enterprise Applications (re:Invent 2024): Salesforce, Zendesk, ServiceNow, SAP, Facebook Ads, Instagram Ads, Pardot, and Zoho CRM
  • Key Features:
    • Data filtering to selectively extract tables and schemas using regular expressions
    • Support for incremental and auto-refresh materialized views on replicated data
    • Configurable change data capture (CDC) refresh rates
    • Cross-account integrations within the same region
    • Supports both Redshift Serverless workgroups and provisioned clusters using RA3 instance types
    • Compatible with enhanced VPC routing and Multi-AZ deployments

Redshift Data Sharing

  • Redshift Data Sharing allows securely sharing live, transactionally consistent data between Redshift clusters without physically copying or moving data.
  • Supports cross-account and cross-Region data sharing.
  • For cross-account data sharing, both the producer and consumer cluster must be encrypted.
  • Producer clusters create datashares; consumer clusters associate with them via a two-way handshake for cross-account sharing.
  • Multi-data warehouse writes through data sharing (GA November 2024) allows writing to shared Redshift databases from multiple data warehouses, enabling distributed ETL workloads.
  • Supports data sharing with data lake tables, enabling unified access across warehouses and data lakes.
  • Works with both RA3 provisioned clusters and Redshift Serverless.
  • Integrated with AWS Lake Formation for fine-grained access control on shared data.

Redshift Serverless

  • Redshift Serverless automatically provisions and scales data warehouse capacity to deliver fast performance without managing infrastructure.
  • Pay only for compute capacity when the data warehouse is active, measured in Redshift Processing Units (RPUs).
  • Each RPU provides 16 GB of memory; base capacity ranges from 4 RPUs to 1024 RPUs.
  • Starting capacity as low as 4 RPUs ($1.50/hour), making it cost-effective for smaller workloads.
  • AI-driven scaling and optimization (GA October 2024, default for new workgroups April 2026):
    • Automatically learns workload patterns and adjusts compute resources based on query complexity, data volume, and scan size.
    • Offers a price-performance slider with five profiles from “Optimized for Cost” to “Optimized for Performance.”
    • Deploys automatic optimizations including materialized views and table design optimization.
    • Up to 10x price-performance improvement for variable workloads.
  • Serverless Reservations (April 2025): Commit to specific RPUs for a one-year term with 20% (no-upfront) or 24% (all-upfront) discount off on-demand rates.
  • Supports all Redshift features including data sharing, streaming ingestion, federated queries, and zero-ETL integrations.

Redshift Multi-AZ Deployments

  • Multi-AZ deployments (GA November 2023 for RA3 clusters) support running the data warehouse across multiple Availability Zones simultaneously.
  • Provides high availability by continuing operations during unforeseen failure scenarios in a single AZ.
  • Available for RA3 provisioned clusters in most commercial regions and GovCloud (US).
  • Compatible with zero-ETL integrations for highly available near real-time analytics.

Redshift Node Types

  • RG Instances (GA May 2026) – Latest generation powered by AWS Graviton processors
    • Up to 2.2x faster for data warehouse workloads and 2.4x faster for data lake workloads compared to RA3.
    • 30% lower price per vCPU compared to RA3 instances.
    • Includes a custom-built vectorized data lake query engine that processes Apache Iceberg and Parquet data on cluster nodes.
    • Available in rg.xlarge and rg.4xlarge node types.
    • Recommended upgrade path from RA3 instances.
  • RA3 Instances – Managed storage with separate compute and storage scaling
    • Available in ra3.xlplus, ra3.4xlarge, ra3.16xlarge, and ra3.large sizes.
    • Managed storage automatically tiers data between high-performance SSD and S3.
    • RA3.large (GA October 2024) offers a cost-effective migration path from DC2.large.
  • DC2 Instances – Dense compute with local SSD storage (legacy, migration to RA3/RG recommended)

Redshift Generative AI Integration

  • Amazon Q generative SQL (GA September 2024) in Redshift Query Editor allows users to express queries in natural language and receive SQL code recommendations.
  • Amazon Bedrock Integration (October 2024) enables invoking large language models (LLMs) from SQL commands for tasks like text generation, summarization, sentiment analysis, and language translation.
  • Amazon Bedrock Knowledge Bases supports natural language querying to retrieve structured data from Redshift warehouses, automatically translating questions into SQL.

Redshift SageMaker Lakehouse

  • Amazon SageMaker Lakehouse (re:Invent 2024) unifies data across S3 data lakes and Redshift warehouses.
  • Provides access via Apache Iceberg open standards for use with any Iceberg-compatible engine.
  • Existing Redshift data warehouses can be published to SageMaker Lakehouse, opening warehouse data with Iceberg REST API.
  • Supports creating new data lake tables using Redshift Managed Storage (RMS) as native storage.
  • Offers integrated access controls and fine-grained permissions through Lake Formation across all engines.

Redshift Behavior Changes and Deprecations

  • Python UDFs End of Support (June 30, 2026)
    • Creation of new Python UDFs blocked since October 30, 2025.
    • Existing Python UDFs will stop functioning after June 30, 2026.
    • Migration: Use Lambda UDFs which provide better integration, flexibility, scalability, and security.
  • ODBC 1.x Driver End of Support: September 30, 2026. Migrate to ODBC 2.x driver.
  • Minimum TLS Version: TLS 1.2 minimum required starting January 31, 2026.
  • Materialized View Auto-REFRESH Behavior Change: After February 27, 2026, auto-refresh respects workload priorities.

AWS Certification Exam Practice Questions

  • Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).
  • AWS services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.
  • AWS exam questions are not updated to keep up the pace with AWS updates, so even if the underlying feature has changed the question might not be updated
  • Open to further feedback, discussion and correction.
  1. A Redshift data warehouse has different user teams that need to query the same table with very different query types. These user teams are experiencing poor performance. Which action improves performance for the user teams in this situation?
    1. Create custom table views.
    2. Add interleaved sort keys per team.
    3. Maintain team-specific copies of the table.
    4. Add support for workload management queue hopping.
  2. A company needs to replicate data from their Aurora PostgreSQL database to Redshift for near real-time analytics without building custom ETL pipelines. Which approach requires the LEAST operational overhead?
    1. Set up AWS Glue jobs to periodically extract and load data
    2. Use Amazon Kinesis Data Firehose to stream changes
    3. Configure Aurora PostgreSQL zero-ETL integration with Redshift
    4. Create Lambda functions triggered by DynamoDB Streams
  3. An organization uses Redshift Serverless and wants to optimize for cost during off-peak hours while maintaining performance during peak business hours. Which feature best addresses this requirement?
    1. Manual RPU scaling with CloudWatch alarms
    2. Concurrency Scaling with WLM queue configuration
    3. AI-driven scaling and optimization with cost-optimized profile
    4. Scheduled pause and resume of the Serverless workgroup
  4. A company wants to share live data from their Redshift cluster with a partner organization’s Redshift cluster in a different AWS account without copying data. What is the recommended approach?
    1. Use Redshift Spectrum with cross-account S3 access
    2. Set up AWS Data Exchange for data delivery
    3. Configure cross-account Redshift Data Sharing
    4. Use AWS Glue ETL to replicate data to the partner account
  5. A team wants to perform sentiment analysis on customer feedback stored in Redshift without moving data to a separate ML service. Which Redshift feature enables this?
    1. Redshift ML with SageMaker Autopilot
    2. Export to S3 and use Comprehend
    3. Amazon Redshift integration with Amazon Bedrock using SQL commands
    4. Redshift federated query to an NLP endpoint
  6. A company is migrating from RA3 instances and wants better price-performance for both data warehouse and data lake workloads. Which instance type should they consider?
    1. DC2.8xlarge for compute-intensive workloads
    2. RA3.16xlarge with AQUA enabled
    3. RG instances powered by AWS Graviton
    4. Redshift Serverless with 1024 RPU base capacity

AWS Redshift

Redshift Architecture
~ Last updated on : ~ jayendrapatil ~ 5 Comments

AWS Redshift

  • Amazon Redshift is a fully managed, fast, and powerful, petabyte-scale data warehouse service.
  • Redshift is an OLAP data warehouse solution based on PostgreSQL.
  • Redshift is built on cloud economics that scale with usage — powering modern analytics and autonomous agentic AI workloads on the data warehouse.
  • Redshift delivers up to 2.2x better price-performance and 7x better throughput than other cloud data warehouses.
  • Redshift automatically helps
    • set up, operate, and scale a data warehouse, from provisioning the infrastructure capacity.
    • patches and backs up the data warehouse, storing the backups for a user-defined retention period.
    • monitors the nodes and drives to help recovery from failures.
    • significantly lowers the cost of a data warehouse, but also makes it easy to analyze large amounts of data very quickly
    • provide fast querying capabilities over structured and semi-structured data using familiar SQL-based clients and business intelligence (BI) tools using standard ODBC and JDBC connections.
    • uses replication and continuous backups to enhance availability and improve data durability and can automatically recover from node and component failures.
    • scale up or down with a few clicks in the AWS Management Console or with a single API call
    • distributes & parallelize queries across multiple physical resources
    • supports VPC, SSL, AES-256 encryption, and Hardware Security Modules (HSMs) to protect the data in transit and at rest.
  • Redshift supported only Single-AZ deployments before and the nodes are available within the same AZ, if the AZ supports Redshift clusters. However, Multi-AZ deployments are now supported for RA3 clusters.
  • Redshift provides monitoring using CloudWatch and metrics for compute utilization, storage utilization, and read/write traffic to the cluster are available with the ability to add user-defined custom metrics
  • Redshift provides Audit logging and AWS CloudTrail integration
  • Redshift can be easily enabled to a second region for disaster recovery.

Redshift Node Types

  • RG Instances (Current Generation – GA May 2026)
    • Newest generation powered by AWS Graviton processors.
    • Delivers up to 2.2x better performance for data warehouse workloads and up to 2.4x faster for data lake workloads compared to RA3, at 30% lower price per vCPU.
    • Includes a custom-built vectorized data lake query engine that processes Apache Iceberg and Parquet data on cluster nodes.
    • Enables running SQL analytics across data warehouse and data lake using a single engine.
    • Uses Redshift Managed Storage (RMS) with independent scaling of compute and storage.
  • RA3 Instances
    • Previous generation with managed storage, allowing independent scaling of compute and storage.
    • Available in RA3.xlplus, RA3.4xlarge, RA3.16xlarge, and RA3.large sizes.
    • RA3.large launched in 2024 as a cost-effective option for smaller workloads.
    • Uses Redshift Managed Storage (RMS) backed by S3.
    • Supports data sharing, Multi-AZ, zero-ETL integrations.
  • DC2 Instances (DEPRECATED – EOL April 24, 2026)
    • Dense Compute nodes with fast CPUs, large amounts of RAM, and solid-state disks (SSDs).
    • DC2 nodes reached End of Life on April 24, 2026.
    • After May 15, 2025, creation of new DC2 clusters, resizing existing ones, or adding nodes was no longer allowed.
    • Migration options: Upgrade to RA3, RG instances, or Redshift Serverless using elastic resize.
  • DS2 Instances (DEPRECATED)
    • Dense Storage nodes using HDDs, previously used for large data warehouses.
    • DS2 has been deprecated and creation of new DS2 clusters is no longer allowed.
    • Migration options: Upgrade to RA3 or RG instances using elastic resize.

Redshift Performance

  • Massively Parallel Processing (MPP)
    • automatically distributes data and query load across all nodes.
    • makes it easy to add nodes to the data warehouse and enables fast query performance as the data warehouse grows.
  • Columnar Data Storage
    • organizes the data by column, as column-based systems are ideal for data warehousing and analytics, where queries often involve aggregates performed over large data sets
    • columnar data is stored sequentially on the storage media, and require far fewer I/Os, greatly improving query performance
  • Advance Compression
    • Columnar data stores can be compressed much more than row-based data stores because similar data is stored sequentially on a disk.
    • employs multiple compression techniques and can often achieve significant compression relative to traditional relational data stores.
    • doesn’t require indexes or materialized views and so uses less space than traditional relational database systems.
    • automatically samples the data and selects the most appropriate compression scheme, when the data is loaded into an empty table
  • Query Optimizer
    • Redshift query run engine incorporates a query optimizer that is MPP-aware and also takes advantage of columnar-oriented data storage.
  • Result Caching
    • Redshift caches the results of certain types of queries in memory on the leader node.
    • When a user submits a query, Redshift checks the results cache for a valid, cached copy of the query results. If a match is found in the result cache, Redshift uses the cached results and doesn’t run the query.
    • Result caching is transparent to the user.
  • Compiled Code
    • Leader node distributes fully optimized compiled code across all of the nodes of a cluster. Compiling the query decreases the overhead associated with an interpreter and therefore increases the runtime speed, especially for complex queries.
  • AQUA (Advanced Query Accelerator)
    • AQUA is a distributed and hardware-accelerated cache that boosts certain types of queries.
    • Amazon Redshift automatically determines whether to use AQUA — no manual configuration is required.
    • AQUA pushes the computation needed to handle reduction and aggregation queries closer to the data, reducing network traffic and offloading work from the cluster.
  • Autonomics
    • Redshift is a self-learning, self-tuning system that automatically generates and implements optimal data layout recommendations for distribution and sort keys.
    • Autonomics now extend to multi-cluster environments, eliminating manual performance tuning across consumer clusters.

Redshift Architecture

Redshift Architecture

  • Clusters
    • Core infrastructure component of a Redshift data warehouse
    • Cluster is composed of one or more compute nodes.
    • If a cluster is provisioned with two or more compute nodes, an additional leader node coordinates the compute nodes and handles external communication.
    • Client applications interact directly only with the leader node.
    • Compute nodes are transparent to external applications.
  • Leader node
    • Leader node manages communications with client programs and all communication with compute nodes.
    • It parses and develops execution plans to carry out database operations
    • Based on the execution plan, the leader node compiles code, distributes the compiled code to the compute nodes, and assigns a portion of the data to each compute node.
    • Leader node distributes SQL statements to the compute nodes only when a query references tables that are stored on the compute nodes. All other queries run exclusively on the leader node.
  • Compute nodes
    • Leader node compiles code for individual elements of the execution plan and assigns the code to individual compute nodes.
    • Compute nodes execute the compiled code and send intermediate results back to the leader node for final aggregation.
    • Each compute node has its own dedicated CPU, memory, and attached disk storage, which is determined by the node type.
    • As the workload grows, the compute and storage capacity of a cluster can be increased by increasing the number of nodes, upgrading the node type, or both.
  • Node slices
    • A compute node is partitioned into slices.
    • Each slice is allocated a portion of the node’s memory and disk space, where it processes a portion of the workload assigned to the node.
    • Leader node manages distributing data to the slices and apportions the workload for any queries or other database operations to the slices. The slices then work in parallel to complete the operation.
    • Number of slices per node is determined by the node size of the cluster.
    • When a table is created, one column can optionally be specified as the distribution key. When the table is loaded with data, the rows are distributed to the node slices according to the distribution key that is defined for a table.
    • A good distribution key enables Redshift to use parallel processing to load data and execute queries efficiently.
  • Managed Storage
    • Data warehouse data is stored in a separate storage tier Redshift Managed Storage (RMS).
    • RMS provides the ability to scale the storage to petabytes using S3 storage.
    • RMS enables scale, pay for compute and storage independently so that the cluster can be sized based only on the computing needs.
    • RMS automatically uses high-performance SSD-based local storage as tier-1 cache.
    • It also takes advantage of optimizations, such as data block temperature, data block age, and workload patterns to deliver high performance while scaling storage automatically to S3 when needed without requiring any action.
    • RMS tables can now be accessed through Apache Iceberg REST APIs via Amazon SageMaker Lakehouse, enabling other engines (Spark, EMR) to read Redshift data.

Redshift Serverless

  • Redshift Serverless is a serverless option of Redshift that makes it more efficient to run and scale analytics in seconds without the need to set up and manage data warehouse infrastructure.
  • Redshift Serverless automatically provisions and intelligently scales data warehouse capacity to deliver high performance for demanding and unpredictable workloads.
  • Redshift Serverless helps any user to get insights from data by simply loading and querying data in the data warehouse.
  • Redshift Serverless supports concurrency Scaling feature that can support unlimited concurrent users and concurrent queries, with consistently fast query performance.
  • When concurrency scaling is enabled, Redshift automatically adds cluster capacity when the cluster experiences an increase in query queuing.
  • Redshift Serverless measures data warehouse capacity in Redshift Processing Units (RPUs). RPUs are resources used to handle workloads.
  • Redshift Serverless supports workgroups and namespaces to isolate workloads and manage different resources.
  • Redshift Serverless supports a capacity configuration of up to 1024 RPUs for larger workloads.
  • AI-Driven Scaling and Optimization (GA 2024)
    • Redshift Serverless now includes AI-driven scaling and optimization that automatically scales compute resources based on query complexity, data volume, and workload patterns.
    • Users can choose to optimize for cost, performance, or keep it balanced using a simple slider.
    • Delivers up to 10x price-performance improvements for variable workloads.
    • Scales the compute not only based on queuing, but also factoring data volume and query complexity.
  • AWS Graviton is available in Serverless, offering up to 30% better price-performance.

Redshift Single vs Multi-Node Cluster

  • Single Node
    • Single node configuration enables getting started quickly and cost-effectively & scale up to a multi-node configuration as the needs grow
  • Multi-Node
    • Multi-node configuration requires a leader node that manages client connections and receives queries, and two or more compute nodes that store data and perform queries and computations.
    • Leader node
      • provisioned automatically and not charged for
      • receives queries from client applications, parses the queries, and develops execution plans, which are an ordered set of steps to process these queries.
      • coordinates the parallel execution of these plans with the compute nodes, aggregates the intermediate results from these nodes, and finally returns the results back to the client applications.
    • Compute node
      • can contain from 1-128 compute nodes, depending on the node type
      • executes the steps specified in the execution plans and transmits data among themselves to serve these queries.
      • intermediate results are sent back to the leader node for aggregation before being sent back to the client applications.
      • Current node types: RG (Graviton-based, latest), RA3 (managed storage)
        • RG instances — Latest generation, Graviton-powered, up to 2.4x faster than RA3 at 30% lower cost per vCPU, includes integrated data lake query engine
        • RA3 instances — Managed storage with independent compute/storage scaling, supports data sharing and Multi-AZ
        • DC2 (DEPRECATED – EOL April 24, 2026) — Dense Compute with SSDs, no longer available for new clusters
        • DS2 (DEPRECATED) — Dense Storage with HDDs, no longer available for new clusters
      • direct access to compute nodes is not allowed

Redshift Multi-AZ

  • Redshift Multi-AZ deployment runs the data warehouse in multiple AWS AZs simultaneously and continues operating in unforeseen failure scenarios.
  • Multi-AZ deployment is managed as a single data warehouse with one endpoint and does not require any application changes.
  • Multi-AZ deployments support high availability requirements and reduce recovery time by guaranteeing capacity to automatically recover and are intended for customers with business-critical analytics applications that require the highest levels of availability and resiliency to AZ failures.
  • Redshift Multi-AZ supports RPO = 0 meaning data is guaranteed to be current and up to date in the event of a failure. RTO is under a minute.
  • Multi-AZ is supported for RA3 clusters (GA 2024).

Redshift Availability & Durability

  • Redshift replicates the data within the data warehouse cluster and continuously backs up the data to S3 (11 9’s durability).
  • Redshift mirrors each drive’s data to other nodes within the cluster.
  • Redshift will automatically detect and replace a failed drive or node.
  • RA3 and RG clusters and Redshift serverless are not impacted the same way since the data is stored in S3 and the local drive is just used as a data cache.
  • If a drive fails,
    • cluster will remain available in the event of a drive failure.
    • the queries will continue with a slight latency increase while Redshift rebuilds the drive from the replica of the data on that drive which is stored on other drives within that node.
    • single node clusters do not support data replication and the cluster needs to be restored from a snapshot on S3.
  • In case of node failure(s), Redshift
    • automatically provisions new node(s) and begins restoring data from other drives within the cluster or from S3.
    • prioritizes restoring the most frequently queried data so the most frequently executed queries will become performant quickly.
    • cluster will be unavailable for queries and updates until a replacement node is provisioned and added to the cluster.
  • In case of Redshift cluster AZ goes down, Redshift
    • cluster is unavailable until power and network access to the AZ are restored
    • cluster’s data is preserved and can be used once AZ becomes available
    • cluster can be restored from any existing snapshots to a new AZ within the same region
    • Multi-AZ deployments (RA3) continue operating automatically during AZ failures

Redshift Backup & Restore

  • Redshift always attempts to maintain at least three copies of the data – Original, Replica on the compute nodes, and a backup in S3.
  • Redshift replicates all the data within the data warehouse cluster when it is loaded and also continuously backs up the data to S3.
  • Redshift enables automated backups of the data warehouse cluster with a 1-day retention period, by default, which can be extended to max 35 days.
  • Automated backups can be turned off by setting the retention period as 0.
  • Redshift can also asynchronously replicate the snapshots to S3 in another region for disaster recovery.
  • Redshift only backs up data that has changed.
  • Restoring the backup will provision a new data warehouse cluster.
  • Starting June 8, 2026, Amazon Redshift introduced incremental snapshot billing for Serverless and RG instances — customers pay only for unique data blocks across active manual snapshots.

Redshift Scalability

  • Redshift allows scaling of the cluster either by
    • increasing the node instance type (Vertical scaling)
    • increasing the number of nodes (Horizontal scaling)
  • Redshift scaling changes are usually applied during the maintenance window or can be applied immediately
  • Elastic Resize
    • Elastic resize allows changing node types within minutes with a single operation.
    • Supports migration from deprecated DC2/DS2 to RA3 or RG instances.
    • Can be run at any time or scheduled for a future time.
  • Redshift scaling process
    • existing cluster remains available for read operations only, while a new data warehouse cluster gets created during scaling operations
    • data from the compute nodes in the existing data warehouse cluster is moved in parallel to the compute nodes in the new cluster
    • when the new data warehouse cluster is ready, the existing cluster will be temporarily unavailable while the canonical name record of the existing cluster is flipped to point to the new data warehouse cluster
  • Concurrency Scaling
    • Automatically adds query processing power in seconds when concurrency increases.
    • Supports thousands of concurrent users and queries with consistently fast performance.
    • Extra processing power is automatically removed when demand subsides — pay only for usage.
    • Each cluster earns up to one hour of free Concurrency Scaling credits per day.
    • Now supports auto-copy, zero-ETL ingestion workloads, and COPY queries from S3 (2026).

Redshift Zero-ETL Integrations

  • Zero-ETL integrations seamlessly make transactional or operational data available in Redshift without building and managing complex ETL pipelines.
  • Data replicates in near real-time from the source into Redshift.
  • Supported Sources (GA):
    • Amazon Aurora MySQL-Compatible Edition — First zero-ETL source, supports petabytes of transactional data
    • Amazon Aurora PostgreSQL-Compatible Edition — GA October 2024
    • Amazon RDS for MySQL — GA September 2024
    • Amazon DynamoDB — GA October 2024
    • Enterprise Applications — Salesforce, Zendesk, ServiceNow, SAP, Facebook Ads, Instagram Ads, Pardot, Zoho CRM (GA December 2024)
  • Key Features:
    • Data filtering to selectively extract tables and schemas using regular expressions
    • Support for incremental and auto-refresh materialized views on replicated data
    • Configurable change data capture (CDC) refresh rates
    • Supports Enhanced VPC Routing warehouses
    • Concurrency scaling support for zero-ETL (2026)
  • Zero-ETL integrations can be created on Redshift Serverless workgroups or provisioned clusters using RA3/RG instance types.

Redshift Data Ingestion

  • Auto-Copy (GA 2024)
    • Simplifies continuous data ingestion from Amazon S3 into Redshift.
    • Set up ingestion rules to track S3 paths and automatically load new files without additional tools or custom solutions.
    • Uses S3 event integrations to detect and copy new files automatically.
    • Concurrency scaling support for auto-copy (2026).
  • Streaming Ingestion
    • Enables near real-time analytics by ingesting streaming data directly into Redshift without staging in S3.
    • Creates materialized views directly on top of data streams.
    • Supports Amazon Kinesis Data Streams, Amazon MSK, Confluent Managed Cloud, and self-managed Apache Kafka clusters.
    • Achieves low latency (measured in seconds) while ingesting hundreds of megabytes per second.
  • COPY Command — Traditional bulk loading from S3, DynamoDB, EMR, or remote hosts via SSH.

Redshift Data Sharing

  • Redshift data sharing allows sharing live, transactionally consistent data across different Redshift clusters without data copies or movement.
  • Supports sharing within the same AWS account, across accounts, and across regions.
  • Multi-Data Warehouse Writes (GA 2024) — Enables writing to shared Redshift databases from multiple Redshift data warehouses. Written data is available to all data warehouses as soon as committed.
  • Data sharing with data lake tables is now generally available — enables sharing Apache Iceberg/open format data.
  • Supports data sharing for use cases like customer 360, data monetization, and distributed ETL workloads.
  • First query execution for data sharing is up to 4x faster with improved metadata handling (2024).

Redshift and Amazon SageMaker Lakehouse

  • Amazon SageMaker Lakehouse (GA re:Invent 2024) unifies data across Amazon S3 data lakes and Redshift data warehouses.
  • Provides flexibility to access and query data using Apache Iceberg open standards.
  • Existing Redshift data warehouses can be made available through SageMaker Lakehouse via a simple publish step, exposing all data warehouse data with Iceberg REST API.
  • New data lake tables can be created using Redshift Managed Storage (RMS) as a native storage option.
  • Integrates with AWS Glue Data Catalog for unified metadata management.
  • Supports fine-grained permissions consistently applied across all analytics engines and AI tools.
  • Apache Iceberg Support in Redshift:
    • Query Iceberg tables stored in S3 and S3 Table Buckets.
    • Write support: INSERT, DELETE, UPDATE, and MERGE operations on Iceberg tables (2025-2026).
    • 2x faster data lake query performance for Iceberg workloads (2025).
    • Incremental refresh for materialized views on data lake tables.

Redshift Generative AI and ML

  • Amazon Q Generative SQL for Redshift (GA 2024)
    • Uses generative AI to analyze user intent, query patterns, and schema metadata.
    • Allows users to express queries in natural language and receive SQL code recommendations.
    • Leverages query history and custom context (table/column descriptions, sample queries) for more relevant SQL recommendations.
    • Available in the Redshift Query Editor.
  • Amazon Bedrock Integration (GA 2024)
    • Enables invoking Large Language Models (LLMs) from simple SQL commands on Redshift data.
    • Supports generative AI tasks: language translation, text generation, summarization, sentiment analysis, and data enrichment.
    • Supports models from Anthropic Claude, Amazon Titan, Meta Llama, and Mistral AI.
  • Redshift as Knowledge Base in Amazon Bedrock
    • Amazon Bedrock Knowledge Bases supports natural language querying to retrieve structured data from Redshift.
    • Transforms natural language queries into SQL queries automatically.
    • Users can ask questions like “What were my top 5 selling products?” without writing SQL.
  • Redshift ML
    • Provides integration between Redshift and Amazon SageMaker.
    • Enables creating, training, and deploying ML models using SQL.
    • Supports inference within the Redshift cluster for predictions in queries and applications.
    • Note: Scalar Python UDFs will reach end of support after June 30, 2026. Migrate to Lambda UDFs or Redshift ML.

Redshift Security

  • Redshift supports encryption at rest and in transit
  • Redshift provides support for role-based access control – RBAC. Row-level access control helps assign one or more roles to a user and assign system and object permissions by role.
  • Redshift supports Lambda User-defined Functions – UDFs to enable external tokenization, data masking, identification or de-identification of data by integrating with vendors like Protegrity, and protect or unprotect sensitive data based on a user’s permissions and groups, in query time.
  • Redshift supports Single Sign-On (SSO) and integrates with third-party corporate or SAML-compliant identity providers.
  • Redshift supports federated permissions with AWS IAM Identity Center, now available in multiple AWS Regions (2026).
  • Redshift supports multi-factor authentication (MFA) for additional security when authenticating to the Redshift cluster.
  • Redshift supports encrypting an unencrypted cluster using KMS. However, you can’t enable hardware security module (HSM) encryption by modifying the cluster. Instead, create a new, HSM-encrypted cluster and migrate your data to the new cluster.
  • Redshift enhanced VPC routing forces all COPY and UNLOAD traffic between the cluster and the data repositories through the VPC.
  • Minimum TLS version requirement changes effective January 31, 2026 — ensure clients support TLS 1.2 or higher.

Redshift Advanced Topics

Redshift Best Practices

Refer blog post Redshift Best Practices

Redshift vs EMR vs RDS

  • RDS is ideal for
    • structured data and running traditional relational databases while offloading database administration
    • for online-transaction processing (OLTP) and for reporting and analysis
  • Redshift is ideal for
    • large volumes of structured data that needs to be persisted and queried using standard SQL and existing BI tools
    • analytic and reporting workloads against very large data sets by harnessing the scale and resources of multiple nodes and using a variety of optimizations to provide improvements over RDS
    • preventing reporting and analytic processing from interfering with the performance of the OLTP workload
    • unified analytics across data warehouses and data lakes using a single engine (especially with RG instances)
  • EMR is ideal for
    • processing and transforming unstructured or semi-structured data to bring in to Amazon Redshift and
    • for data sets that are relatively transitory, not stored for long-term use.

AWS Certification Exam Practice Questions

  • Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).
  • AWS services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.
  • AWS exam questions are not updated to keep up the pace with AWS updates, so even if the underlying feature has changed the question might not be updated
  • Open to further feedback, discussion and correction.
  1. With which AWS services CloudHSM can be used (select 2)
    1. S3
    2. DynamoDB
    3. RDS
    4. ElastiCache
    5. Amazon Redshift
  2. You have recently joined a startup company building sensors to measure street noise and air quality in urban areas. The company has been running a pilot deployment of around 100 sensors for 3 months. Each sensor uploads 1KB of sensor data every minute to a backend hosted on AWS. During the pilot, you measured a peak of 10 IOPS on the database, and you stored an average of 3GB of sensor data per month in the database. The current deployment consists of a load-balanced auto scaled Ingestion layer using EC2 instances and a PostgreSQL RDS database with 500GB standard storage. The pilot is considered a success and your CEO has managed to get the attention or some potential investors. The business plan requires a deployment of at least 100K sensors, which needs to be supported by the backend. You also need to store sensor data for at least two years to be able to compare year over year Improvements. To secure funding, you have to make sure that the platform meets these requirements and leaves room for further scaling. Which setup will meet the requirements?
    1. Add an SQS queue to the ingestion layer to buffer writes to the RDS instance (RDS instance will not support data for 2 years)
    2. Ingest data into a DynamoDB table and move old data to a Redshift cluster (Handle 10K IOPS ingestion and store data into Redshift for analysis)
    3. Replace the RDS instance with a 6 node Redshift cluster with 96TB of storage (Does not handle the ingestion issue)
    4. Keep the current architecture but upgrade RDS storage to 3TB and 10K provisioned IOPS (RDS instance will not support data for 2 years)
  3. Which two AWS services provide out-of-the-box user configurable automatic backup-as-a-service and backup rotation options? Choose 2 answers
    1. Amazon S3
    2. Amazon RDS
    3. Amazon EBS
    4. Amazon Redshift
  4. Your department creates regular analytics reports from your company’s log files. All log data is collected in Amazon S3 and processed by daily Amazon Elastic Map Reduce (EMR) jobs that generate daily PDF reports and aggregated tables in CSV format for an Amazon Redshift data warehouse. Your CFO requests that you optimize the cost structure for this system. Which of the following alternatives will lower costs without compromising average performance of the system or data integrity for the raw data?
    1. Use reduced redundancy storage (RRS) for PDF and CSV data in Amazon S3. Add Spot instances to Amazon EMR jobs. Use Reserved Instances for Amazon Redshift. (Spot instances impacts performance)
    2. Use reduced redundancy storage (RRS) for all data in S3. Use a combination of Spot instances and Reserved Instances for Amazon EMR jobs. Use Reserved instances for Amazon Redshift (Combination of the Spot and reserved with guarantee performance and help reduce cost. Also, RRS would reduce cost and guarantee data integrity, which is different from data durability)
    3. Use reduced redundancy storage (RRS) for all data in Amazon S3. Add Spot Instances to Amazon EMR jobs. Use Reserved Instances for Amazon Redshift (Spot instances impacts performance)
    4. Use reduced redundancy storage (RRS) for PDF and CSV data in S3. Add Spot Instances to EMR jobs. Use Spot Instances for Amazon Redshift. (Spot instances impacts performance and Spot instance not available for Redshift)
  5. A company needs to make their operational database data available in near real-time in their Redshift data warehouse for analytics. They want to eliminate custom ETL pipeline management. Which approach is recommended?
    1. Use AWS DMS to continuously replicate data from the source database to Redshift
    2. Use zero-ETL integration between the source database and Amazon Redshift (Zero-ETL eliminates the need to build and manage data pipelines and makes transactional data available in near real-time)
    3. Export data to S3 and use auto-copy to load into Redshift
    4. Use Kinesis Data Streams to stream data into Redshift
  6. A company wants to run analytics queries across both their Redshift data warehouse and Apache Iceberg tables stored in S3, using a single engine. Which Amazon Redshift capability best supports this requirement?
    1. Redshift Spectrum
    2. Redshift Federated Query
    3. Amazon SageMaker Lakehouse with Redshift (SageMaker Lakehouse unifies data across S3 data lakes and Redshift warehouses using Apache Iceberg open standards, and RG instances include an integrated data lake query engine)
    4. AWS Glue ETL to move data between S3 and Redshift
  7. A company is migrating from Amazon Redshift DC2 node types that are reaching End of Life. Which of the following are valid migration options? (Select 2)
    1. Migrate to RA3 instances using elastic resize
    2. Create a new DC2 cluster with more nodes
    3. Migrate to Amazon Redshift Serverless
    4. Migrate to DS2 instances
    5. Contact AWS to extend DC2 support
  8. An analytics team wants to use natural language queries to get insights from their Redshift data warehouse without writing SQL. Which AWS service integration enables this?
    1. Amazon Athena with natural language processing
    2. Amazon Bedrock Knowledge Bases with Amazon Redshift as a structured data source (Bedrock Knowledge Bases supports natural language querying to retrieve structured data from Redshift by automatically translating natural language to SQL)
    3. Amazon Q Business connected to Redshift
    4. Amazon SageMaker Canvas with Redshift

References

AWS Database Services Cheat Sheet – RDS, DynamoDB, Aurora

~ Last updated on : ~ jayendrapatil ~ 9 Comments

AWS Database Services Cheat Sheet

AWS Database Services

📋 Last Updated: June 2026

This cheat sheet has been updated to include Aurora DSQL, Aurora storage increase to 256 TiB, ElastiCache for Valkey, ElastiCache Serverless, Redshift Multi-AZ and Serverless, DynamoDB multi-Region strong consistency, zero-ETL integrations, RDS Multi-AZ DB Clusters with readable standbys, and RDS Extended Support.

Relational Database Service – RDS

  • provides Relational Database service
  • supports MySQL, MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, Amazon Aurora, and IBM Db2 (added in 2023) DB engines
  • as it is a managed service, shell (root ssh) access is not provided
  • manages backups, software patching, automatic failure detection, and recovery
  • supports use initiated manual backups and snapshots
  • daily automated backups with database transaction logs enables Point in Time recovery up to the last five minutes of database usage
  • snapshots are user-initiated storage volume snapshot of DB instance, backing up the entire DB instance and not just individual databases that can be restored as a independent RDS instance
  • RDS Security
    • support encryption at rest using KMS as well as encryption in transit using SSL endpoints
    • supports IAM database authentication, which prevents the need to store static user credentials in the database, because authentication is managed externally using IAM.
    • supports Encryption only during creation of an RDS DB instance
    • existing unencrypted DB cannot be encrypted and you need to create a snapshot, create an encrypted copy of the snapshot and restore as encrypted DB
    • supports Secrets Manager for storing and rotating secrets
    • for encrypted database
      • logs, snapshots, backups, read replicas are all encrypted as well
      • cross region replicas and snapshots are supported for encrypted instances
  • Multi-AZ deployment
    • provides high availability and automatic failover support and is NOT a scaling solution
    • maintains a synchronous standby replica in a different AZ
    • transaction success is returned only if the commit is successful both on the primary and the standby DB
    • Oracle, PostgreSQL, MySQL, and MariaDB DB instances use Amazon technology, while SQL Server DB instances use SQL Server Always On Availability Groups
    • snapshots and backups are taken from standby & eliminate I/O freezes
    • during automatic failover, its seamless and RDS switches to the standby instance and updates the DNS record to point to standby
    • failover can be forced with the Reboot with failover option
  • Multi-AZ DB Cluster (Readable Standbys)
    • provides a primary DB instance and two readable standby DB instances in different AZs
    • standby instances can serve read traffic, providing additional read capacity
    • uses semi-synchronous replication with transaction log-based replication
    • provides faster failover (typically under 35 seconds) compared to Multi-AZ instance deployment
    • supports MySQL and PostgreSQL engines
    • offers lower write latency compared to Multi-AZ instance deployments
  • Read Replicas
    • uses the PostgreSQL, MySQL, and MariaDB DB engines’ built-in replication functionality to create a separate Read Only instance
    • updates are asynchronously copied to the Read Replica, and data might be stale
    • can help scale applications and reduce read only load
    • requires automatic backups enabled
    • replicates all databases in the source DB instance
    • for disaster recovery, can be promoted to a full fledged database
    • can be created in a different region for disaster recovery, migration and low latency across regions
    • can’t create encrypted read replicas from unencrypted DB or read replica
  • RDS does not support all the features of underlying databases, and if required the database instance can be launched on an EC2 instance
  • RDS Components
    • DB parameter groups contains engine configuration values that can be applied to one or more DB instances of the same instance type for e.g. SSL, max connections etc.
    • Default DB parameter group cannot be modified, create a custom one and attach to the DB
    • Supports static and dynamic parameters
      • changes to dynamic parameters are applied immediately (irrespective of apply immediately setting)
      • changes to static parameters are NOT applied immediately and require a manual reboot.
  • RDS Monitoring & Notification
    • integrates with CloudWatch and CloudTrail
    • CloudWatch provides metrics about CPU utilization from the hypervisor for a DB instance, and Enhanced Monitoring gathers its metrics from an agent on the instance
    • Performance Insights is a database performance tuning and monitoring feature that helps illustrate the database’s performance and help analyze any issues that affect it
    • supports RDS Event Notification which uses the SNS to provide notification when an RDS event like creation, deletion or snapshot creation etc occurs
  • RDS Blue/Green Deployments
    • creates a staging (green) environment that mirrors the production (blue) environment
    • enables safer database updates, major version upgrades, and schema changes with minimal downtime (under 5 seconds)
    • supports Aurora MySQL, Aurora PostgreSQL, RDS for MySQL, RDS for MariaDB, and RDS for PostgreSQL
    • now supports Aurora Global Database (2025)
  • RDS Extended Support
    • allows running databases on a major engine version up to 3 years past its RDS end of standard support date at an additional cost
    • provides critical security and bug fixes after the community ends support for a major version
    • databases are automatically enrolled if not upgraded before the end of standard support date
  • Zero-ETL Integrations
    • RDS for MySQL and Aurora support zero-ETL integration with Amazon Redshift
    • enables near real-time analytics on transactional data without building ETL pipelines
    • data is automatically replicated to Amazon Redshift within seconds of being written

⚠️ RDS Custom for Oracle – End of Support (March 31, 2027)

AWS will end support for Amazon RDS Custom for Oracle on March 31, 2027. After this date, you will no longer be able to access the RDS Custom for Oracle console or resources.

Migration Options: Migrate to Amazon RDS for Oracle (standard) or run Oracle on Amazon EC2 bare metal instances.

Aurora

  • is a relational database engine that combines the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open source databases
  • is a managed service and handles time-consuming tasks such as provisioning, patching, backup, recovery, failure detection and repair
  • is a proprietary technology from AWS (not open sourced)
  • provides PostgreSQL and MySQL compatibility
  • is “AWS cloud optimized” and claims 5x performance improvement over MySQL on RDS, over 3x the performance of PostgreSQL on RDS
  • scales storage automatically in increments of 10GB, up to 256 TiB (increased from 128 TiB in July 2025) with no impact to database performance. Storage is striped across 100s of volumes.
  • no need to provision storage in advance.
  • provides self-healing storage. Data blocks and disks are continuously scanned for errors and repaired automatically.
  • provides instantaneous failover
  • replicates each chunk of the database volume six ways across three Availability Zones i.e. 6 copies of the data across 3 AZ
    • requires 4 copies out of 6 needed for writes
    • requires 3 copies out of 6 need for reads
  • costs more than RDS (20% more) – but is more efficient
  • Read Replicas
    • can have 15 replicas while MySQL has 5, and the replication process is faster (sub 10 ms replica lag)
    • share the same data volume as the primary instance in the same AWS Region, there is virtually no replication lag
    • supports Automated failover for master in less than 30 seconds
    • supports Cross Region Replication using either physical or logical replication.
  • Security
    • supports Encryption at rest using KMS
    • supports Encryption in flight using SSL (same process as MySQL or Postgres)
    • Automated backups, snapshots and replicas are also encrypted
    • Possibility to authenticate using IAM token (same method as RDS)
    • supports protecting the instance with security groups
    • does not support SSH access to the underlying servers
  • Aurora I/O-Optimized
    • a cluster configuration that provides predictable pricing with no charges for I/O operations
    • ideal for I/O-intensive applications such as e-commerce, payment processing, and SaaS applications
    • can deliver up to 40% cost savings for I/O-intensive workloads
    • supports both Aurora Serverless and provisioned instances
    • can switch between I/O-Optimized and Standard configurations (once every 30 days to I/O-Optimized, back to Standard anytime)
  • Aurora Serverless
    • provides automated database instantiation and on-demand autoscaling based on actual usage
    • provides a relatively simple, cost-effective option for infrequent, intermittent, or unpredictable workloads
    • automatically starts up, shuts down, and scales capacity up or down based on the application’s needs. No capacity planning needed
    • Pay per second, can be more cost-effective
    • Aurora Serverless v1 reached end of life on March 31, 2025 – all clusters have been migrated to Aurora Serverless v2 (now simply called “Aurora Serverless”)
    • Aurora Serverless (v2) supports features like read replicas, Multi-AZ, Global Database, and logical replication that v1 did not
    • supports scale to zero capability and up to 30% better performance with smarter scaling (2026 enhancement)
  • Aurora Global Database
    • allows a single Aurora database to span multiple AWS regions.
    • provides Physical replication, which uses dedicated infrastructure that leaves the databases entirely available to serve the application
    • supports 1 Primary Region (read / write)
    • replicates across up to 5 secondary (read-only) regions, replication lag is less than 1 second
    • supports up to 16 Read Replicas per secondary region
    • recommended for low-latency global reads and disaster recovery with an RTO of < 1 minute
    • supports managed failover (Global Database Failover) which automates the cross-Region failover process, reducing operational overhead (introduced August 2023)
    • supports Blue/Green Deployments for Global Database (2025) for safer major version upgrades across all regions
    • supports a global writer endpoint for simplified application connectivity
  • Aurora Backtrack
    • Backtracking “rewinds” the DB cluster to the specified time
    • Backtracking performs in place restore and does not create a new instance. There is a minimal downtime associated with it.
  • Aurora Clone feature allows quick and cost-effective creation of Aurora Cluster duplicates
  • supports parallel or distributed query using Aurora Parallel Query, which refers to the ability to push down and distribute the computational load of a single query across thousands of CPUs in Aurora’s storage layer.
  • Aurora Optimized Reads
    • delivers up to 8x improved query latency for applications with datasets exceeding instance memory
    • uses local NVMe-based storage on Graviton-based instances to extend caching capacity
    • available for both PostgreSQL and MySQL compatible editions

Amazon Aurora DSQL (New – GA May 2025)

  • a serverless, distributed SQL database optimized for transaction processing
  • the fastest serverless distributed SQL database with active-active high availability
  • provides PostgreSQL compatibility (subset of features)
  • designed for 99.99% availability in single-Region and 99.999% availability in multi-Region configurations
  • delivers strong consistency for all reads and writes to any Regional endpoint
  • provides virtually unlimited scalability with zero infrastructure management and zero downtime maintenance
  • offers the fastest distributed SQL reads and writes with 4x faster reads and writes compared to other popular distributed SQL databases
  • employs an active-active deployment model where all database resources function as peers capable of handling both read and write traffic
  • supports up to 256 TiB of storage per database cluster
  • ideal for globally distributed applications requiring strong consistency, such as financial transactions, gaming, and SaaS applications

DynamoDB

  • fully managed NoSQL database service
  • synchronously replicates data across three facilities in an AWS Region, giving high availability and data durability
  • runs exclusively on SSDs to provide high I/O performance
  • provides provisioned table reads and writes
  • automatically partitions, reallocates, and re-partitions the data and provisions additional server capacity as data or throughput changes
  • creates and maintains indexes for the primary key attributes for efficient access to data in the table
  • DynamoDB Table classes currently support
    • DynamoDB Standard table class is the default and is recommended for the vast majority of workloads.
    • DynamoDB Standard-Infrequent Access (DynamoDB Standard-IA) table class which is optimized for tables where storage is the dominant cost.
  • supports Secondary Indexes
    • allows querying attributes other than the primary key attributes without impacting performance.
    • are automatically maintained as sparse objects
  • Local secondary index vs Global secondary index
    • shares partition key + different sort key vs different partition + sort key
    • search limited to partition vs across all partition
    • unique attributes vs non-unique attributes
    • linked to the base table vs independent separate index
    • only created during the base table creation vs can be created later
    • cannot be deleted after creation vs can be deleted
    • consumes provisioned throughput capacity of the base table vs independent throughput
    • returns all attributes for item vs only projected attributes
    • Eventually or Strongly vs Only Eventually consistent reads
    • size limited to 10Gb per partition vs unlimited
  • DynamoDB Consistency
    • provides Eventually consistent (by default) or Strongly Consistent option to be specified during a read operation
    • supports Strongly consistent reads for a few operations like Query, GetItem, and BatchGetItem using the ConsistentRead parameter
  • DynamoDB Throughput Capacity
    • supports On-demand and Provisioned read/write capacity modes
    • Provisioned mode requires the number of reads and writes per second as required by the application to be specified
    • On-demand mode provides flexible billing option capable of serving thousands of requests per second without capacity planning
    • On-demand pricing reduced by 50% in November 2024
    • supports switching from provisioned to on-demand up to 4 times in a rolling 24-hour period (2025 improvement)
  • DynamoDB Auto Scaling helps dynamically adjust provisioned throughput capacity on your behalf, in response to actual traffic patterns.
  • DynamoDB Adaptive capacity is a feature that enables DynamoDB to run imbalanced workloads indefinitely.
  • DynamoDB Global Tables
    • provide multi-active, cross-region replication capability of DynamoDB to support data access locality and regional fault tolerance for database workloads.
    • provide up to 99.999% availability
    • Multi-Region Strong Consistency (MRSC) – GA June 2025
      • enables applications to always read the latest version of data from any Region in a global table
      • provides zero RPO (Recovery Point Objective) for the highest application resilience
      • removes the need to manage consistency across multiple Regions manually
      • slightly higher write latencies compared to eventually consistent (MREC) mode
    • Global tables pricing reduced by up to 67% in November 2024
  • DynamoDB Streams provides a time-ordered sequence of item-level changes made to data in a table
  • DynamoDB Time to Live (TTL)
    • enables a per-item timestamp to determine when an item expiry
    • expired items are deleted from the table without consuming any write throughput.
  • DynamoDB Accelerator (DAX) is a fully managed, highly available, in-memory cache for DynamoDB that delivers up to a 10x performance improvement – from milliseconds to microseconds – even at millions of requests per second.
  • DynamoDB Triggers (just like database triggers) are a feature that allows the execution of custom actions based on item-level updates on a table.
  • VPC Gateway Endpoints provide private access to DynamoDB from within a VPC without the need for an internet gateway or NAT gateway.
  • DynamoDB Zero-ETL Integrations
    • Zero-ETL with Amazon Redshift (GA October 2024) – automatically replicates DynamoDB tables into Redshift for SQL analytics without building ETL pipelines
    • Zero-ETL with Amazon OpenSearch Service – provides seamless, code-free data replication for vector search and near real-time analytics
    • enables analytics on DynamoDB data without impacting production workload performance

ElastiCache

  • managed web service that provides in-memory caching to deploy and run Valkey, Redis OSS, or Memcached protocol-compliant cache clusters
  • ElastiCache for Valkey (Recommended – default since October 2024)
    • Valkey is an open-source fork of Redis OSS 7.2, maintained by the Linux Foundation with contributions from AWS, Google, Microsoft, and others
    • is a drop-in replacement for Redis OSS – supports the same data structures, commands, and protocols
    • all features available with Redis OSS 7.2 are available in Valkey 7.2 and above
    • AWS recommends Valkey for new deployments and offers migration paths from existing Redis OSS clusters
    • like Redis OSS, supports Multi-AZ, Read Replicas and Snapshots
    • supports cluster mode for horizontal scaling
  • ElastiCache with Redis OSS
    • available up to version 7.1 (the last BSD-licensed release); now a maintenance track with no active new feature development from AWS
    • Redis 8.0+ is licensed under AGPLv3, which is not supported by ElastiCache
    • Standard support for versions 4 and 5 ends January 31, 2026; clusters will be enrolled in Extended Support after that date
    • like RDS, supports Multi-AZ, Read Replicas and Snapshots
    • Read Replicas are created across AZ within same region using Redis’s asynchronous replication technology
    • Multi-AZ differs from RDS as there is no standby, but if the primary goes down a Read Replica is promoted as primary
    • allows snapshots for backup and restore
    • AOF can be enabled for recovery scenarios, to recover the data in case the node fails or service crashes. But it does not help in case the underlying hardware fails
    • Enabling Redis Multi-AZ as a Better Approach to Fault Tolerance
  • ElastiCache with Memcached
    • can be scaled up by increasing size and scaled out by adding nodes
    • nodes can span across multiple AZs within the same region
    • cached data is spread across the nodes, and a node failure will always result in some data loss from the cluster
    • supports auto discovery
    • every node should be homogenous and of same instance type
  • ElastiCache Valkey/Redis vs Memcached
    • complex data objects vs simple key value storage
    • persistent vs non persistent, pure caching
    • automatic failover with Multi-AZ vs Multi-AZ not supported
    • scaling using Read Replicas vs using multiple nodes
    • backup & restore supported vs not supported
  • ElastiCache Serverless (launched November 2023)
    • creates a cache in under a minute with zero capacity planning
    • instantly scales capacity based on application traffic patterns
    • provides zero infrastructure management and zero downtime maintenance
    • supports Valkey 7.2+, Redis OSS 7.0+, and Memcached 1.6+
    • pay-per-use pricing based on data stored and requests executed
    • automatically provisions resources across multiple AZs for high availability
  • can be used for state management to keep the web application stateless

Redshift

  • fully managed, fast and powerful, petabyte scale data warehouse service
  • uses replication and continuous backups to enhance availability and improve data durability and can automatically recover from node and component failures
  • provides Massive Parallel Processing (MPP) by distributing & parallelizing queries across multiple physical resources
  • columnar data storage improving query performance and allowing advance compression techniques
  • now supports Multi-AZ deployments for RA3 clusters (GA 2024), running the data warehouse in two AZs simultaneously with 99.99% SLA
  • spot instances are NOT an option
  • Redshift Serverless
    • enables running and scaling analytics without provisioning or managing clusters
    • automatically scales compute up or down based on workload demands
    • AI-driven scaling and optimization (default for new workgroups since April 2026) uses machine learning to predict compute needs and automatically adjust resources
    • offers minimum capacity as low as 4 RPUs for cost-effective development workloads
    • supports Serverless Reservations (2025) for discounted pricing and cost predictability
    • pay-as-you-go pricing based on compute used
  • Zero-ETL Integrations
    • supports zero-ETL from Aurora MySQL, Aurora PostgreSQL, RDS for MySQL, DynamoDB, and self-managed databases
    • automatically replicates data from source to Redshift without building ETL pipelines
    • enables near real-time analytics on transactional data
  • Enhanced Security Defaults (2025)
    • new clusters default to public accessibility disabled, encryption enabled, and secure connections enforced

AWS Redshift Best Practices

~ Last updated on : ~ jayendrapatil ~ 2 Comments

AWS Redshift Best Practices

📌 Last Updated: June 2026. Covers RG instances (Graviton-powered), Multidimensional Data Layouts (MDDL), Zero-ETL integrations, Auto-copy, Streaming Ingestion, AI-driven scaling for Serverless, and concurrency scaling for COPY commands.

Designing Tables

Distribution Style Selection

  • Distribute the fact table and one dimension table on their common columns.
    • A fact table can have only one distribution key. Any tables that join on another key aren’t collocated with the fact table.
    • Choose one dimension to collocate based on how frequently it is joined and the size of the joining rows.
    • Designate both the dimension table’s primary key and the fact table’s corresponding foreign key as the DISTKEY.
  • Choose the largest dimension based on the size of the filtered dataset.
    • Only the rows that are used in the join need to be distributed, so consider the size of the dataset after filtering, not the size of the table.
  • Choose a column with high cardinality in the filtered result set.
    • If you distribute a sales table on a date column, for e.g, you should probably get fairly even data distribution, unless most of the sales are seasonal
    • However, if you commonly use a range-restricted predicate to filter for a narrow date period, most of the filtered rows occur on a limited set of slices and the query workload is skewed.
  • Change some dimension tables to use ALL distribution.
    • If a dimension table cannot be collocated with the fact table or other important joining tables, query performance can be improved significantly by distributing the entire table to all of the nodes.
    • Using ALL distribution multiplies storage space requirements and increases load times and maintenance operations.
  • Use AUTO distribution for tables where optimal distribution is unclear.
    • With AUTO distribution (the default), Redshift assigns an optimal distribution style based on the table size — using ALL for small tables and EVEN for larger tables, then adjusting automatically.
    • Combined with Automatic Table Optimization (ATO), Redshift can monitor workloads and automatically apply optimal distribution keys without manual intervention.

Sort Key Selection

  • Redshift stores the data on disk in sorted order according to the sort key, which helps query optimizer to determine optimal query plans.
  • If recent data is queried most frequently, specify the timestamp column as the leading column for the sort key.
    • Queries are more efficient because they can skip entire blocks that fall outside the time range.
  • If you do frequent range filtering or equality filtering on one column, specify that column as the sort key.
    • Redshift can skip reading entire blocks of data for that column.
    • Redshift tracks the minimum and maximum column values stored on each block and can skip blocks that don’t apply to the predicate range.
  • If you frequently join a table, specify the join column as both the sort key and the distribution key.
    • Doing this enables the query optimizer to choose a sort merge join instead of a slower hash join.
    • As the data is already sorted on the join key, the query optimizer can bypass the sort phase of the sort merge join.
  • Use AUTO sort key or Multidimensional Data Layouts (MDDL) for complex workloads.
    • When you set SORTKEY AUTO, Redshift’s Automatic Table Optimization (ATO) analyzes your query history and automatically selects either a single-column sort key or Multidimensional Data Layouts based on which is better for your workload.
    • Multidimensional Data Layouts (MDDL) — GA since September 2025 — dynamically sort data based on actual query filter patterns rather than a single column, accelerating performance for workloads with multiple filter predicates.
    • MDDL constructs a multidimensional virtual sort key that co-locates rows typically accessed by the same queries, enabling data block skipping across multiple predicate columns.

Automatic Table Optimization (ATO)

  • ATO is a self-tuning capability that automatically optimizes table design by applying sort and distribution keys without administrator intervention.
  • ATO monitors cluster workload and table metadata, runs AI algorithms over observations, and implements sort and distribution keys online in the background without interrupting running queries.
  • When ATO is enabled, you don’t need to manually choose sort keys or distribution styles — Redshift will determine optimal settings based on actual query patterns.
  • To enable ATO on an existing table: ALTER TABLE tablename ALTER SORTKEY AUTO; ALTER TABLE tablename ALTER DISTSTYLE AUTO;
  • Best practice for new tables: use AUTO distribution and AUTO sort key unless you have specific, well-understood access patterns.

Other Practices

  • Automatic compression produces the best results
  • COPY command analyzes the data and applies compression encodings to an empty table automatically as part of the load operation
  • Define primary key and foreign key constraints between tables wherever appropriate. Even though they are informational only, the query optimizer uses those constraints to generate more efficient query plans.
  • Don’t use the maximum column size for convenience.
  • Use RA3 or RG instances with Redshift Managed Storage (RMS) to decouple compute from storage and enable independent scaling.

Loading Data

  • You can load data into the tables using the following methods:
    • Using Multi-Row INSERT
    • Using Bulk INSERT
    • Using COPY command
    • Staging tables
    • Auto-copy from S3 (continuous automatic ingestion)
    • Streaming Ingestion (from Kinesis Data Streams or Amazon MSK)
    • Zero-ETL integrations (from Aurora, DynamoDB, RDS, and SaaS applications)
  • Copy Command
    • COPY command loads data in parallel from S3, EMR, DynamoDB, or multiple data sources on remote hosts.
    • COPY loads large amounts of data much more efficiently than using INSERT statements, and stores the data more effectively as well.
    • Use a Single COPY Command to Load from Multiple Files
    • DON’T use multiple concurrent COPY commands to load one table from multiple files as Redshift is forced to perform a serialized load, which is much slower.
    • Concurrency Scaling for COPY (May 2026): Redshift now extends concurrency scaling to support high-volume data ingestion workloads, automatically scaling for COPY queries in Parquet and ORC formats from S3. Data pipelines no longer need to choose between ingestion speed and query performance during peak demand.
  • Split the Load Data into Multiple Files
    • Divide the data in multiple files with equal size (between 1MB and 1GB)
    • Number of files should be a multiple of the number of slices in the cluster
    • Helps to distribute workload uniformly in the cluster.
  • Use a Manifest File
    • S3 provides eventual consistency for some operations — Note: Since December 2020, Amazon S3 provides strong read-after-write consistency for all operations. Redshift COPY, UNLOAD, and Spectrum operations benefit from this consistency automatically.
    • Manifest files are still recommended to explicitly specify the exact list of files to load, preventing accidental inclusion/exclusion of files.
    • Manifest file helps specify different S3 locations in a more efficient way than with the use of S3 prefixes.
  • Compress Data Files
    • Individually compress the load files using gzip, lzop, bzip2, or Zstandard for large datasets
    • Avoid using compression, if small amount of data because the benefit of compression would be outweighed by the processing cost of decompression
    • If the priority is to reduce the time spent by COPY commands use LZO compression. If the priority is to reduce the size of the files in S3 and the network bandwidth use GZIP or Zstandard (ZSTD) compression.
  • Load Data in Sort Key Order
    • Load the data in sort key order to avoid needing to vacuum.
    • As long as each batch of new data follows the existing rows in the table, the data will be properly stored in sort order, and you will not need to run a vacuum.
    • Presorting rows is not needed in each load because COPY sorts each batch of incoming data as it loads.
  • Load Data using IAM role
    • Attach an IAM role to the cluster rather than embedding credentials in the COPY command.

Auto-copy from S3

  • Auto-copy enables continuous, automatic file ingestion from Amazon S3 into Redshift tables without additional tools or custom solutions.
  • Set up ingestion rules using COPY JOB to track S3 paths and automatically load new files as they arrive.
  • Auto-copy uses S3 event notifications to detect new files and trigger loads automatically.
  • Concurrency scaling for auto-copy (March 2026): Auto-copy now supports concurrency scaling, ensuring ingestion performance doesn’t degrade during peak query workloads.
  • Best suited for continuous batch data arriving in S3 (e.g., log files, IoT data, CDC exports).

Streaming Ingestion

  • Streaming Ingestion enables near real-time analytics by creating materialized views directly on top of data streams from Amazon Kinesis Data Streams or Amazon MSK (Managed Streaming for Apache Kafka).
  • Eliminates the need to stage data in S3 before loading — data flows directly from streams to Redshift.
  • Use CREATE MATERIALIZED VIEW with stream source to define ingestion, then REFRESH MATERIALIZED VIEW to consume latest data.
  • Supports JSON, CSV, and other formats directly from streams.
  • Available on both provisioned clusters and Redshift Serverless.

Zero-ETL Integrations

  • Zero-ETL integrations provide fully managed, near real-time data replication from operational databases and SaaS applications to Redshift without building ETL pipelines.
  • Supported sources:
    • Amazon Aurora (MySQL-compatible and PostgreSQL-compatible)
    • Amazon RDS (MySQL and PostgreSQL — PostgreSQL GA July 2025)
    • Amazon DynamoDB (GA October 2024)
    • Self-managed databases (MySQL, PostgreSQL)
    • SaaS applications — Salesforce, SAP, ServiceNow, Zendesk, and others (via Amazon SageMaker Lakehouse)
  • History mode (April 2025) preserves complete history of data changes for auditing and trend analysis without maintaining duplicate copies.
  • Concurrency scaling support for zero-ETL (March 2026) ensures replication performance during high query loads.
  • Available on RA3, RG instances and Redshift Serverless workgroups.

Designing Queries

  • Avoid using select *. Include only the columns you specifically need.
  • Use a CASE Expression to perform complex aggregations instead of selecting from the same table multiple times.
  • Don’t use cross-joins unless absolutely necessary
  • Use subqueries in cases where one table in the query is used only for predicate conditions and the subquery returns a small number of rows (less than about 200).
  • Use predicates to restrict the dataset as much as possible.
  • In the predicate, use the least expensive operators that you can.
  • Avoid using functions in query predicates.
  • If possible, use a WHERE clause to restrict the dataset.
  • Add predicates to filter tables that participate in joins, even if the predicates apply the same filters.
  • Use materialized views for frequently executed queries to pre-compute results and reduce query latency.
  • Leverage data sharing to share live data across multiple Redshift clusters/workgroups without copying, enabling workload isolation.

Cluster Configuration Best Practices

Instance Types

  • RG Instances (GA May 2026): New Graviton-powered nodes delivering up to 2.2x faster for data warehouse workloads and up to 2.4x faster for data lake workloads at 30% lower price per vCPU compared to RA3. Recommended for new provisioned deployments.
  • RA3 Instances: Previous generation with Redshift Managed Storage (RMS) that separates compute and storage. Still fully supported.
  • DC2/DS2 Instances: Legacy instance types. Migrate to RA3 or RG for better price-performance and managed storage benefits.
  • Both RG and RA3 use Redshift Managed Storage — data is automatically stored in S3 with intelligent caching on local SSDs.

Multi-AZ Deployments

  • Redshift supports Multi-AZ deployments for RG and RA3 clusters, providing high availability across two Availability Zones.
  • All nodes in both AZs are used for read and write workloads during normal operation.
  • If one AZ experiences an outage, the cluster continues operating in the other AZ.
  • Can convert existing Single-AZ clusters to Multi-AZ or restore from snapshot into Multi-AZ configuration.

Redshift Serverless

  • Redshift Serverless automatically provisions and scales data warehouse capacity without cluster management.
  • Capacity measured in RPUs (Redshift Processing Units). Range: 4 RPUs to 512 RPUs (4 RPU minimum available since June 2025, starting at $1.50/hour).
  • AI-driven scaling and optimization (default for new workgroups since April 2026) uses ML to predict compute needs and automatically adjust resources before queries queue, supporting 8–512 RPU base range.
  • Pay only for compute consumed when the warehouse is active — ideal for intermittent or unpredictable workloads.
  • Supports all the same features as provisioned: data sharing, streaming ingestion, zero-ETL, auto-copy.

UDF Best Practices

  • ⚠️ Python UDFs Deprecated: Amazon Redshift no longer supports creation of new Python UDFs (since November 1, 2025). Existing Python UDFs will reach end of support after June 30, 2026.
  • Use Lambda UDFs as the recommended replacement — they provide Python 3 support, access to external services, better scalability, and enhanced security.
  • SQL UDFs remain fully supported for simple transformations.
  • Migrate existing Python UDFs to Lambda UDFs before the end-of-support date.

AWS Certification Exam Practice Questions

  • Questions are collected from Internet and the answers are marked as per my knowledge and understanding (which might differ with yours).
  • AWS services are updated everyday and both the answers and questions might be outdated soon, so research accordingly.
  • AWS exam questions are not updated to keep up the pace with AWS updates, so even if the underlying feature has changed the question might not be updated
  • Open to further feedback, discussion and correction.
  1. An administrator needs to design a strategy for the schema in a Redshift cluster. The administrator needs to determine the optimal distribution style for the tables in the Redshift schema. In which two circumstances would choosing EVEN distribution be most appropriate? (Choose two.)
    1. When the tables are highly denormalized and do NOT participate in frequent joins.
    2. When data must be grouped based on a specific key on a defined slice.
    3. When data transfer between nodes must be eliminated.
    4. When a new table has been loaded and it is unclear how it will be joined to dimension.
  2. An administrator has a 500-GB file in Amazon S3. The administrator runs a nightly COPY command into a 10-node Amazon Redshift cluster. The administrator wants to prepare the data to optimize performance of the COPY command. How should the administrator prepare the data?
    1. Compress the file using gz compression.
    2. Split the file into 500 smaller files.
    3. Convert the file format to AVRO.
    4. Split the file into 10 files of equal size.
  3. A company needs to load data from multiple operational databases into Amazon Redshift in near real-time for analytics without building ETL pipelines. Which feature should they use?
    1. Redshift Streaming Ingestion
    2. Amazon Kinesis Data Firehose
    3. Zero-ETL integrations
    4. AWS Database Migration Service (DMS)
  4. An organization wants to optimize Redshift sort key selection for a workload that filters on multiple columns across different queries. The current single-column sort key only benefits a subset of queries. What should they use?
    1. Create compound sort keys with all filter columns
    2. Switch to interleaved sort keys
    3. Enable Automatic Table Optimization with SORTKEY AUTO to leverage Multidimensional Data Layouts (MDDL)
    4. Create multiple copies of the table with different sort keys
  5. A data engineering team needs to continuously ingest new files arriving in S3 into Redshift without managing external schedulers or custom Lambda triggers. Which Redshift feature addresses this requirement?
    1. Redshift Spectrum
    2. Auto-copy (COPY JOB)
    3. Streaming Ingestion from Kinesis
    4. Zero-ETL integration with S3
  6. A company is deploying a new Redshift provisioned cluster and wants the best price-performance. They need both data warehouse and data lake query capabilities. Which instance type should they select? (June 2026)
    1. DC2 instances
    2. DS2 instances
    3. RA3 instances
    4. RG instances (Graviton-powered)
  7. A team wants to enable near real-time analytics on streaming data from Amazon MSK without staging data in S3. Which approach should they use?
    1. Use AWS Glue streaming ETL job to load into Redshift
    2. Create a streaming materialized view in Redshift that reads directly from the MSK topic
    3. Use Kinesis Data Firehose to deliver to Redshift
    4. Use Lambda to consume MSK events and INSERT into Redshift

References

AWS Storage Options – SQS & Redshift

~ Last updated on : ~ jayendrapatil ~ 13 Comments

SQS

  • is a fully managed message queuing service that provides a reliable, highly scalable, hosted queue for temporary storage and delivery of messages up to 1 MiB in size (increased from 256 KB in August 2025).
  • supports a virtually unlimited number of queues and supports two queue types:
    • Standard queues – unordered, at-least-once delivery with nearly unlimited throughput.
    • FIFO queues – exactly-once processing with strict message ordering, supporting up to 70,000 messages per second with high throughput mode.

Ideal Usage Patterns

  • is ideally suited to any scenario where multiple application components must communicate and coordinate their work in a loosely coupled manner particularly producer consumer scenarios.
  • can be used to coordinate a multi-step processing pipeline, where each message is associated with a task that must be processed.
  • enables the number of worker instances to scale up or down, and also enable the processing power of each single worker instance to scale up or down, to suit the total workload, without any application changes.
  • ideal for multi-tenant workloads using fair queues (launched July 2025) to mitigate noisy neighbor impact and ensure consistent processing across tenants.
  • supports event-driven architectures with AWS Lambda event source mapping, including provisioned mode for 3x faster scaling and 16x higher concurrency.

Anti-Patterns

  • Binary or Large Messages
    • SQS supports messages up to 1 MiB. If the application requires binary or messages exceeding this limit, it is best to use the Amazon SQS Extended Client Library with Amazon S3 to store the payload and SQS to store the pointer.
  • Long Term storage
    • SQS stores messages for max 14 days and if application requires storage period longer than 14 days, Amazon S3 or other storage options should be preferred.
  • High-speed message queuing or very short tasks
    • If the application requires a very high-speed message send and receive response from a single producer or consumer, use of Amazon DynamoDB or a message-queuing system hosted on Amazon EC2 may be more appropriate.

Performance

  • is a distributed queuing system that is optimized for horizontal scalability, not for single-threaded sending or receiving speeds.
  • Standard queues support nearly unlimited throughput (thousands of transactions per second per API action).
  • FIFO queues support up to 3,000 messages per second with batching by default, or up to 70,000 messages per second (700,000 with batching) in high throughput mode in select regions.
  • FIFO queues support up to 120,000 in-flight messages (increased from 20,000 in November 2024).
  • Higher receive performance can be achieved by requesting multiple messages (up to 10) in a single call.
  • Fair queues (July 2025) automatically reorder messages to maintain consistent dwell time across tenants, preventing noisy neighbors from impacting processing latency.

Durability & Availability

  • are highly durable but temporary.
  • stores all messages redundantly across multiple servers and data centers.
  • Message retention time is configurable on a per-queue basis, from a minimum of one minute to a maximum of 14 days.
  • Messages are retained in a queue until they are explicitly deleted, or until they are automatically deleted upon expiration of the retention time.
  • supports dead-letter queues (DLQ) for isolating messages that fail processing, with DLQ redrive capability to move messages back to the source queue or a custom destination for reprocessing.

Cost Model

  • pricing is based on
    • number of requests (per million requests)
    • the amount of data transferred out (priced per GB per month)
    • First 1 million requests per month are free (Free Tier)

Scalability & Elasticity

  • is both highly elastic and massively scalable.
  • is designed to enable a virtually unlimited number of computers to read and write a virtually unlimited number of messages at any time.
  • supports virtually unlimited numbers of queues and messages per queue for any user.
  • supports dual-stack (IPv4 and IPv6) endpoints for flexible network access.

Key Features (Recent Updates)

  • Message payload size increased to 1 MiB (August 2025) – supports larger messages for both standard and FIFO queues without needing the Extended Client Library.
  • Fair queues (July 2025) – automatically mitigates noisy neighbor impact in multi-tenant standard queues by reordering messages to maintain consistent dwell time across tenants.
  • FIFO high throughput mode – up to 70,000 TPS per API action (November 2023), enabling 700,000 messages per second with batching.
  • FIFO in-flight limit increase (November 2024) – increased from 20,000 to 120,000 in-flight messages per FIFO queue.
  • Lambda provisioned mode for SQS (January 2025) – dedicated polling resources providing 3x faster scaling and 16x higher concurrency for event source mapping.
  • Dead-letter queue redrive – move failed messages from DLQ back to source queue or a custom destination for both standard and FIFO queues.
  • Simplified KMS permissions – SendMessage no longer requires kms:Decrypt permission; only kms:GenerateDataKey is needed.
  • Temporary queues – application-managed virtual queues for request-response patterns that reduce cost and development time.

Amazon Redshift

  • is a fast, fully-managed, petabyte-scale data warehouse service that makes it simple and cost-effective to efficiently analyze all data using existing business intelligence tools.
  • is optimized for datasets that range from a few hundred gigabytes to a petabyte or more.
  • manages the work needed to set up, operate, and scale a data warehouse, from provisioning the infrastructure capacity to automating ongoing administrative tasks such as backups and patching.
  • offers two deployment models: Provisioned clusters (RA3 and new RG instances) and Redshift Serverless (pay-per-use with automatic scaling).
⚠️ Important: Amazon Redshift DC2 instances reached End of Life on April 24, 2026. New DC2 clusters cannot be created since May 15, 2025. Migrate to RA3 instances, RG instances (Graviton-powered, GA May 2026), or Redshift Serverless. DS2 instances were previously deprecated in favor of RA3.

Ideal Usage Pattern

  • is ideal for analyzing large datasets using existing business intelligence tools.
  • Common use cases include
    • Analyze global sales data for multiple products
    • Store historical stock trade data
    • Analyze ad impressions and clicks
    • Aggregate gaming data
    • Analyze social trends
    • Measure clinical quality, operation efficiency, and financial performance in the health care space
    • Near real-time analytics using zero-ETL integrations from Aurora, DynamoDB, RDS, and SaaS applications
    • Data lakehouse analytics querying data in S3 data lakes using Redshift Spectrum
    • Generative AI applications using Amazon Bedrock integration for sentiment analysis, text generation, and summarization directly on warehouse data

Anti-Pattern

  • OLTP workloads
    • Redshift is a column-oriented database and more suited for data warehousing and analytics. If application involves online transaction processing, Amazon RDS or Aurora would be a better choice.
  • Blob data
    • For Blob storage, Amazon S3 would be a better choice with metadata in other storage as RDS or DynamoDB.

Performance

  • Amazon Redshift allows very high query performance on datasets ranging in size from hundreds of gigabytes to a petabyte or more.
  • It uses columnar storage, data compression, and zone maps to reduce the amount of I/O needed to perform queries.
  • It has a massively parallel processing (MPP) architecture that parallelizes and distributes SQL operations to take advantage of all available resources.
  • Underlying hardware is designed for high performance data processing that uses local attached storage to maximize throughput.
  • New RG instances (GA May 2026) powered by AWS Graviton deliver up to 2.4x faster performance than RA3 at 30% lower price per vCPU.
  • AI-driven scaling and optimization in Redshift Serverless automatically provisions and scales capacity for demanding workloads.
  • Query performance improvements (March 2026) speed up new queries in BI dashboards and ETL workloads by up to 7x.
  • Concurrency scaling automatically adds additional cluster capacity to handle burst read and write workloads, with support for data ingestion (COPY queries in Parquet/ORC from S3).

Durability & Availability

  • Amazon Redshift stores three copies of your data—all data written to a node in your cluster is automatically replicated to other nodes within the cluster, and all data is continuously backed up to Amazon S3.
  • Snapshots are automated, incremental, and continuous and stored for a user-defined period (1-35 days).
  • Manual snapshots can be created and are retained until explicitly deleted.
  • Amazon Redshift continuously monitors the health of the cluster and automatically re-replicates data from failed drives and replaces nodes as necessary.
  • Multi-AZ deployments (GA for RA3 clusters) run your data warehouse across two Availability Zones simultaneously, providing continued operation during AZ failure scenarios.

Cost Model

  • Provisioned clusters pricing:
    • Compute node hours – total hours run across all compute nodes (RA3 or RG instances)
    • Redshift Managed Storage (RMS) – billed per GB/month, separate from compute (RA3/RG only)
    • Backup storage – for automated and manual snapshots beyond the free tier
    • Data transfer – standard AWS data transfer charges apply
    • Concurrency scaling – free for 1 hour per day per cluster, then per-second billing
    • Spectrum – per TB of data scanned in S3
  • Redshift Serverless pricing:
    • Compute – per RPU-hour (Redshift Processing Unit), billed per second with no charge when idle
    • Storage – per GB/month for managed storage
  • Reserved Instance pricing available for provisioned clusters (1-year or 3-year terms) for significant discounts.

Scalability & Elasticity

  • Provisioned clusters – Elastic resize allows adding or removing nodes within minutes. Classic resize available for node type changes.
  • Redshift Serverless – automatically scales compute capacity up and down based on workload demands with no cluster management required.
  • Data sharing allows securely sharing live, transactionally consistent data across Redshift clusters (cross-account, cross-region) without copying data.
  • Multi-warehouse writes through data sharing (GA November 2024) enable using different warehouses of different types and sizes for ETL workloads.

Key Features (Recent Updates)

  • RG Instances (GA May 2026) – New Graviton-powered instance family delivering 2.4x faster performance than RA3 at 30% lower price per vCPU.
  • DC2 End of Life (April 24, 2026) – Migrate to RA3, RG, or Serverless. New DC2 cluster creation blocked since May 15, 2025.
  • Redshift Serverless – Pay-per-use model with automatic scaling, AI-driven optimization, and per-second billing with no charge when idle.
  • Zero-ETL integrations – Near real-time data replication from Aurora, DynamoDB, RDS, and self-managed databases to Redshift without building ETL pipelines. Also supports SaaS sources (Salesforce, SAP, Zendesk).
  • Multi-AZ deployments – Run RA3 provisioned clusters across two Availability Zones for high availability.
  • Amazon Bedrock integration (October 2024) – Run generative AI tasks (text generation, sentiment analysis, summarization, classification) directly on Redshift data using foundation models via SQL.
  • Amazon Q generative SQL – Generate SQL from natural language prompts in the Redshift Query Editor.
  • Data sharing – Share live data across clusters, accounts, and regions without data movement. Supports multi-warehouse writes for ETL.
  • Redshift Spectrum – Query exabytes of data in S3 without loading it into Redshift, enabling data lakehouse architectures.
  • Concurrency scaling for ingestion (2026) – Automatically scales for COPY queries in Parquet/ORC formats from S3 during traffic spikes.
  • 7x query performance improvement (March 2026) – Faster response for BI dashboards, ETL pipelines, and near real-time analytics.