Amazon Redshift: Building a Production Data Warehouse on AWS

Redshift is a columnar MPP (Massively Parallel Processing) data warehouse. It stores data in columns rather than rows, compresses each column independently, and distributes query execution across all nodes in the cluster. For analytical workloads reading millions of rows from a subset of columns, it is dramatically faster than a row-oriented database like PostgreSQL or MySQL.

In this article I will walk through provisioning a production Redshift cluster with RA3 nodes, loading data efficiently from S3, designing distribution and sort keys, and using Redshift Spectrum to query data directly in S3 without loading it into the warehouse.

RA3 Nodes and Managed Storage

RA3 is the current generation of Redshift nodes and the right default for new clusters. RA3 decouples compute from storage: your data lives in Redshift Managed Storage, which is backed by S3 and can grow to petabytes without resizing the cluster. You pay for compute (the nodes) and storage (per GB of managed storage) independently.

resource "aws_redshift_subnet_group" "main" {
  name       = "redshift-subnet-group"
  subnet_ids = var.data_subnet_ids

  tags = { ManagedBy = "terraform" }
}

resource "aws_redshift_parameter_group" "main" {
  name   = "production-params"
  family = "redshift-1.0"

  parameter {
    name  = "enable_user_activity_logging"
    value = "true"
  }

  parameter {
    name  = "require_ssl"
    value = "true"
  }

  parameter {
    name  = "max_concurrency_scaling_clusters"
    value = "3"
  }

  parameter {
    name  = "auto_mv"
    value = "true"
  }
}

resource "aws_redshift_cluster" "main" {
  cluster_identifier        = "production-warehouse"
  database_name             = "analytics"
  master_username           = "admin"
  manage_master_password    = true
  master_password_secret_kms_key_id = aws_kms_key.redshift.arn
  node_type                 = "ra3.xlplus"
  cluster_type              = "multi-node"
  number_of_nodes           = 4
  cluster_parameter_group_name = aws_redshift_parameter_group.main.name
  cluster_subnet_group_name = aws_redshift_subnet_group.main.name
  vpc_security_group_ids    = [aws_security_group.redshift.id]
  encrypted                 = true
  kms_key_id                = aws_kms_key.redshift.arn
  enhanced_vpc_routing      = true
  publicly_accessible       = false
  skip_final_snapshot       = false
  final_snapshot_identifier = "production-warehouse-final"
  automated_snapshot_retention_period = 7
  preferred_maintenance_window        = "sun:05:00-sun:06:00"
  iam_roles                           = [aws_iam_role.redshift_s3.arn]

  logging {
    enable               = true
    bucket_name          = aws_s3_bucket.redshift_logs.bucket
    s3_key_prefix        = "redshift-logs/"
  }

  tags = {
    Environment = "production"
    ManagedBy   = "terraform"
  }
}

enhanced_vpc_routing = true forces all COPY and UNLOAD traffic between Redshift and S3 to route through your VPC rather than over the public internet. This is required for data that must not traverse shared infrastructure and enables VPC flow log visibility into all S3 data movement. Enable it on every production cluster.

manage_master_password = true stores the master password in Secrets Manager automatically and handles rotation. The password never appears in your Terraform state file or shell history.

Loading Data from S3

The COPY command is how data gets into Redshift efficiently. It reads from S3 in parallel across all nodes simultaneously, which is orders of magnitude faster than INSERT statements.

COPY orders
FROM 's3://your-data-lake/curated/orders/'
IAM_ROLE 'arn:aws:iam::123456789:role/redshift-s3-role'
FORMAT AS PARQUET
REGION 'us-east-1';

COPY orders_incremental
FROM 's3://your-data-lake/curated/orders/year=2026/month=11/day=04/'
IAM_ROLE 'arn:aws:iam::123456789:role/redshift-s3-role'
FORMAT AS PARQUET
REGION 'us-east-1';

Parquet is the most efficient format for COPY because it is already columnar and compressed. Redshift reads only the columns it needs rather than the entire row. For high-frequency incremental loads, partition your S3 data by date so each COPY operation reads only the current day’s files rather than scanning the entire dataset.

Distribution and Sort Keys

Distribution style controls how Redshift distributes table rows across nodes. Getting this right is the most impactful performance decision for join-heavy analytical workloads.

CREATE TABLE orders (
    order_id     BIGINT      NOT NULL,
    customer_id  BIGINT      NOT NULL,
    product_id   BIGINT      NOT NULL,
    order_date   DATE        NOT NULL,
    amount       DECIMAL(10,2),
    status       VARCHAR(20)
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (order_date);

CREATE TABLE customers (
    customer_id  BIGINT      NOT NULL,
    email        VARCHAR(255),
    country      VARCHAR(50),
    created_at   TIMESTAMP
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (customer_id);

CREATE TABLE products (
    product_id   BIGINT      NOT NULL,
    name         VARCHAR(255),
    category     VARCHAR(100),
    price        DECIMAL(10,2)
)
DISTSTYLE ALL
SORTKEY (product_id);

Both orders and customers use customer_id as the distribution key. When you join them, Redshift can perform the join locally on each node without shuffling data across the network because both tables have matching rows on the same node. This is called a collocated join and it is the fastest join pattern in Redshift.

Products uses DISTSTYLE ALL, which copies the entire table to every node. This makes sense for small dimension tables. Any join with products is always local regardless of how the fact table is distributed, at the cost of storing the product table N times for N nodes.

Redshift Spectrum

Redshift Spectrum lets you query data in S3 directly using Redshift SQL without loading it into the warehouse. It uses the Glue Data Catalog for schema information and processes queries in a dedicated Spectrum layer that scales independently of your cluster nodes.

CREATE EXTERNAL SCHEMA raw_data
FROM DATA CATALOG
DATABASE 'raw_data'
IAM_ROLE 'arn:aws:iam::123456789:role/redshift-s3-role'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

SELECT
    o.customer_id,
    COUNT(*)               AS order_count,
    SUM(o.amount)          AS total_spent,
    MAX(r.created_at)      AS last_review_date
FROM
    orders o
    JOIN raw_data.customer_reviews r ON o.customer_id = r.customer_id
WHERE
    o.order_date >= '2026-01-01'
    AND r.year = '2026'
GROUP BY
    o.customer_id
ORDER BY
    total_spent DESC
LIMIT 100;

This query joins orders in the Redshift warehouse with customer reviews that live in S3 as Parquet files. The warehouse handles the join and aggregation. Spectrum reads only the relevant partitions and columns from S3. You get the query power of Redshift against data that would be too expensive to keep in managed storage full-time.

Closing Thoughts

Redshift rewards upfront investment in schema design. Distribution keys that enable collocated joins, sort keys that match your query filter patterns, and partition pruning through Spectrum all compound over time as data volumes grow. Design these before you load data, because changing distribution style or sort keys on a large table requires rebuilding it.

Use RA3 nodes for all new clusters. Enable enhanced VPC routing. Load data from Parquet files on S3 via COPY. Query historical data in S3 directly via Spectrum without loading it. These four decisions cover the architecture decisions that matter most in production Redshift environments.

Enjoy the cloud.

Osama

#AWS #Redshift #DataWarehouse #Analytics #CloudArchitecture #Terraform #InfrastructureAsCode #AmazonWebServices #SolutionsArchitect #CloudComputing #DataEngineering #BigData #SQL #TechBlog #CloudInfrastructure #RedshiftSpectrum #Parquet #Glue #BusinessIntelligence #FinOps