Lesson 135 · AWS Learning Path

AWS 135: Amazon Redshift

Anil Jalela · Published Nov 7, 2025 · 10 min read

Why this lesson matters

Use a columnar data warehouse for analytical workloads and distinguish provisioned clusters from Redshift Serverless.

Redshift is a columnar analytics warehouse, not a general replacement for an OLTP database. Architecture starts with analytical query shape, ingestion/freshness, distribution/sort, workload isolation, security, recovery, and cost across provisioned and serverless models.

What you will be able to do

By the end, you can:

• explain amazon redshift in plain language;
• locate the current service controls in the AWS Management Console;
• run the matching CloudShell or AWS CLI queries and explain every important field;
• draw the identity, network, data, failure, and monitoring path;
• choose the service from requirements and reject it when those requirements are absent;
• diagnose a failed or misleading result from evidence;
• state the cost owner and prove cleanup or a no-create result.

Before you start

• Use a personal AWS account only when its owner has approved the lesson. Do not use the root user for daily work.
• CloudShell is the default command environment. AWS028 explains CloudShell; AWS029 and AWS030 explain local AWS CLI installation and profiles.
• The course example Region is ap-south-1. Global services and services with a required control Region are called out in their commands.
• Run aws sts get-caller-identity privately. Redact the account number before sharing evidence.
• Never paste access keys, passwords, secret values, private object data, presigned URLs, or full account-specific ARNs into a submission.
• This is a no-create lesson. Every Console action and AWS CLI command is read-only. Create the practical artifact locally.
• Console wording can change. Use the Console service search if a menu label has moved, then confirm the current field in the official documentation.

The core model

How the request flows

+----------------------------+
|  Operational data sources  |
+----------------------------+
              |
              v
+-----------------------------------+
|  Ingestion and warehouse storage  |
+-----------------------------------+
                 |
                 v
+--------------------------+
|  Analytical SQL compute  |
+--------------------------+
             |
             v
+--------------------------------------------+
|  Dashboard result and query-cost evidence  |
+--------------------------------------------+

For Amazon Redshift, the important boundary is this: Redshift organizes databases, schemas, tables, compute, storage, workgroups or clusters, snapshots, and integrations. Workload management and data layout affect performance. The solution proves data ingestion, query plan, concurrency, isolation from OLTP, encryption, network access, backup or recovery, and cost controls. That is why the lesson pairs the Console with CLI output and a practical artifact. One interface may hide a field, use a cached view, or be scoped differently. Matching evidence is stronger than a screenshot alone.

Architecture decision table

Warehouse architecture

Provisioned Redshift uses a cluster endpoint, leader/coordinator behavior, and compute nodes. Modern RG/RA3 families separate managed storage economics from local high-performance cache. Redshift Serverless uses a namespace for data/security configuration and a workgroup for compute/network configuration, billed in Redshift Processing Units (RPUs) as work runs.

BI/SQL/ETL clients -> endpoint + IAM/DB auth + TLS
                         |
                         v
query planning / workload management
                         |
            +------------+-------------+
            v                          v
distributed columnar compute       concurrency/serverless scaling
            |
            v
Redshift managed storage + S3 data lake/Spectrum + snapshots/recovery

Columnar encoding reduces analytical I/O when queries read selected columns over many rows. Massively parallel processing divides work among slices/nodes. Poor distribution creates data movement/skew; poor sort organization increases scanned blocks; tiny transactions and single-row updates waste a warehouse design.

Data model and physical design

Use star/snowflake decisions from queries, not source OLTP schema alone. Fact tables contain measures/events; dimensions provide descriptive joins. Denormalization can reduce joins but increases load/update complexity.

• Distribution AUTO, EVEN, KEY, or ALL controls row placement. Choose a high-cardinality join key to colocate large joins; ALL copies small slow-changing dimensions and magnifies storage/load.
• Sort keys organize block metadata for pruning. Compound keys favor leading-column filters; interleaved behavior/limitations require current validation; automatic table optimization can choose designs but still needs evidence.
• Compression encoding reduces storage/I/O. Use COPY/automatic encoding and analyze rather than guessing.
• Primary/foreign/unique constraints are informational in common Redshift behavior and may not be enforced like OLTP constraints; bad declarations can mislead the optimizer. Validate data in ETL.

Inspect query plan, bytes scanned, rows, data redistribution/broadcast, skew, spill, queue time, execution time, and statistics. VACUUM/automatic sort maintenance and ANALYZE/automatic statistics behavior matter after heavy changes.

Ingestion and export

Prefer parallel bulk COPY from S3 using multiple reasonably sized compressed files and an IAM role. Manifest files provide exact object control. Validate load errors, row counts, checksums, duplicate/idempotency keys, and source-to-target reconciliation. One huge file underuses parallelism; millions of tiny files add request/list overhead.

Streaming ingestion, zero-ETL integrations, federated query, Glue/catalog integrations, DMS, and application loaders have different freshness and ownership. “Zero-ETL” reduces custom movement code; it does not remove source prerequisites, target case sensitivity/encryption, schema compatibility, lag, monitoring, transformations, cost, or failure recovery.

UNLOAD writes parallel results to S3; secure role/bucket/KMS, file format, manifest, retention, and sensitive data. Spectrum queries external S3/catalog data without loading every row, but file format, partition pruning, small files, permissions, and scanned-byte cost determine performance.

Workload management and scaling

Automatic or manual WLM separates workloads into queues and allocates resources. Query monitoring rules can abort/log/hop expensive work. Short query acceleration and result cache help eligible queries but do not repair bad SQL.

Concurrency Scaling adds temporary capacity for eligible queued reads and supported writes. It has operation/table/network restrictions and billing beyond earned credits. Monitor which queries used it and why others stayed queued.

Elastic/classic resize, pause/resume, RA3 managed storage, and Serverless scaling solve different capacity/cost problems. Serverless base/max capacity and AI-driven scaling settings influence performance and spend; changing base capacity can affect running queries. Set usage limits/budgets and test peak concurrency.

Security

Keep workgroups/clusters private unless a reviewed requirement exists. Use subnet groups, SGs, enhanced VPC routing where applicable, TLS, IAM temporary database credentials or Secrets Manager, and database users/roles/grants. IAM control-plane permission does not grant SQL table access.

Encrypt with KMS and protect snapshot/cross-Region key paths. Govern S3 COPY/UNLOAD roles against confused-deputy and broad data access. Enable audit/user activity/connection logs to protected destinations, CloudTrail for API, and data classification/masking/row/column policies where supported.

Recovery and multi-Region

Automated/manual snapshots, snapshot copy, restore, Serverless recovery points, and data lake source replay provide recovery options. Restore creates new compute/namespace paths and needs users, network, roles, integrations, BI endpoints, and validation. Multi-AZ and relocation features vary by Redshift model/Region; verify current support rather than treating warehouse nodes like RDS standby.

Cross-Region snapshot copy has nonzero RPO; test restore and cutover. Zero-ETL/federated sources are dependencies, not backup of warehouse transformations and permissions.

Worked architectures

• Nightly sales warehouse: S3 columnar files, manifest COPY into staging, transactional merge/reconciliation, star schema, BI queue, snapshots and tested restore.
• Bursty analyst team: compare Serverless RPU usage/limits with an always-on provisioned cluster; isolate ETL and BI and inspect queue time.
• Data lake query: Spectrum over partitioned Parquet for rarely queried history, hot aggregates in managed storage. Measure scanned bytes and joins.
• Near-real-time source: zero-ETL only after source/target prerequisites, lag and schema-change failure tests; preserve ETL for business transformations/data quality.

Required tests

1. COPY a manifest-controlled dataset and reconcile source file rows/checksums, load errors, and duplicates.
2. Compare columnar/pruned query with an unselective query using plan, bytes, skew, queue and runtime evidence.
3. Create a distribution-skew or stale-statistics case, correct it, and repeat identical SQL.
4. Saturate two workload classes; prove WLM/usage limits and eligible Concurrency Scaling behavior.
5. Deny S3 role, SG, TLS, and database grant separately; classify each failure.
6. Snapshot/recovery-point restore to isolated compute, validate SQL objects/data/users/integrations and measured RPO/RTO.
7. Calculate nodes or RPU seconds, managed storage, snapshots, concurrency scaling, Spectrum scanned bytes, S3/Glue, transfer, KMS and logs.
8. Remove integrations/shares/endpoints, snapshots, namespace/workgroup or cluster, roles/secrets/logs and S3 lab data in dependency order.

AWS Management Console, step by step

Sign in with the normal non-root learning identity. Write the expected starting state before opening the service.

1. Open Amazon Redshift and compare Provisioned clusters with Serverless workgroups and namespaces.
2. Inspect status, network isolation, encryption, node or capacity settings, maintenance, snapshots, and monitoring in supplied evidence.
3. Open Query and database monitoring and identify duration, queue time, scan volume, and failure details without running an unapproved query.

CloudShell and AWS CLI, step by step

Start with a known caller and Region:

export AWS_DEFAULT_REGION="ap-south-1"
aws sts get-caller-identity --query Arn --output text
aws configure list

Redact the account part of the ARN in shared evidence. Now run the topic queries:

aws redshift describe-clusters --query 'Clusters[].{Id:ClusterIdentifier,Status:ClusterStatus,Node:NodeType,Nodes:NumberOfNodes,Encrypted:Encrypted,Public:PubliclyAccessible}' --output table
aws redshift-serverless list-workgroups --query 'workgroups[].{Name:workgroupName,Status:status,BaseRPU:baseCapacity,Public:publiclyAccessible}' --output table
aws redshift-serverless list-namespaces --query 'namespaces[].{Name:namespaceName,Status:status}' --output table

Expected interpretation

The commands distinguish deployment models and security settings. They do not prove good table design, query performance, correct data, or controlled scan cost.

Practical work

Create a warehouse plan for daily order analytics. Include source ingestion, staging, distribution and sort reasoning, workload groups, materialized results, encryption, private access, recovery, and cost guardrails.

Diagnose this topic from its own evidence

Identify whether the delay is queueing, compilation, scan, redistribution, spill, locking, load failure or external-data access. Correlate Query Monitoring Rules/system views with WLM queue time, execution time, scanned bytes, skew, spill, concurrency and workload priority. Validate COPY error records and IAM/S3/KMS paths before changing SQL. Stale statistics, poor sort order, distribution skew, small-file ingestion and unsupported informational constraints can all create misleading plans.

Negative test: compare a selective analytical query with and without appropriate sort/distribution/data pruning using EXPLAIN and runtime evidence. Do not tune from one run; control cache/concurrency effects and state what changed. Prove that an OLTP point-write workload is rejected even if Redshift can technically execute the SQL.

Cost and cleanup

Provisioned node hours, serverless RPU usage, managed storage, snapshots, concurrency scaling, Spectrum scans, transfer, and data sharing can charge.

Knowledge check

1. What operational purpose is this lesson solving?

Expected direction: Use a columnar data warehouse for analytical workloads and distinguish provisioned clusters from Redshift Serverless.

1. Which scope or ownership boundary must be proved first?

Expected direction: Redshift organizes databases, schemas, tables, compute, storage, workgroups or clusters, snapshots, and integrations. Workload management and data layout affect performance.

1. What evidence is strong enough to accept the result?

Expected direction: The solution proves data ingestion, query plan, concurrency, isolation from OLTP, encryption, network access, backup or recovery, and cost controls.

1. Which tempting design or shortcut must be rejected?

Expected direction: Avoid using a warehouse as the transactional order database or scanning uncontrolled datasets without cost and workload controls.

1. Which cost dimensions and retained resources need an owner?

Expected direction: Provisioned node hours, serverless RPU usage, managed storage, snapshots, concurrency scaling, Spectrum scans, transfer, and data sharing can charge.

Lesson acceptance

Pass when the learner can explain MPP/columnar execution, choose Serverless or provisioned RA3 from workload and idle-floor evidence, design distribution/sort/encoding from measured queries, and build secure COPY/UNLOAD or current integration paths. Include WLM/concurrency, resizing, backup/recovery, cross-Region needs, observability, workload isolation, cost and cleanup. Fail if primary/foreign-key declarations are assumed to enforce integrity, Serverless is called free while idle, or Redshift is chosen for transactional OLTP.

Official sources

• Amazon Redshift overview
• Redshift Serverless
• AWS databases
• Database decision guide