Why This Matters

A data warehouse is the analytical backbone of any enterprise. AWS Redshift powers petabyte-scale analytics for thousands of companies, and understanding warehouse patterns is a prerequisite for nearly every data engineering role. This guide covers the core modeling techniques, Redshift-specific optimizations, and production patterns you will encounter in real-world deployments and interviews.

AWS Data Warehouse Architecture

What is a Data Warehouse?

A Data Warehouse is a centralized repository designed for analytical processing (OLAP) of structured data from multiple sources. Unlike operational databases optimized for transactions (OLTP), data warehouses are optimized for complex queries, reporting, and business intelligence.

Key Characteristics

• Subject-Oriented: Organized around key business subjects like sales, customers, or products
• Integrated: Combines data from diverse sources with consistent formats and naming conventions
• Time-Variant: Stores historical data spanning years for trend analysis
• Non-Volatile: Data is stable; updates are batch-loaded, not modified in real-time

Star Schema Design

The Star Schema is the most widely used data warehouse modeling technique. It organizes data into a central fact table surrounded by dimension tables, forming a star-like structure.

Core Components

• Fact Table: Contains measurable business events with foreign keys to dimensions and numeric measures
• Dimension Tables: Contain descriptive attributes (who, what, where, when) for filtering and grouping

Star Schema SQL Example

-- Create Dimension: Date
CREATE TABLE dim_date (
    date_key        INT PRIMARY KEY,
    full_date       DATE NOT NULL,
    year            SMALLINT,
    quarter         SMALLINT,
    month           SMALLINT,
    month_name      VARCHAR(10),
    day_of_week     VARCHAR(10),
    is_weekend      BOOLEAN
);

-- Create Dimension: Product
CREATE TABLE dim_product (
    product_key     INT PRIMARY KEY,
    product_id      VARCHAR(20),
    product_name    VARCHAR(100),
    category        VARCHAR(50),
    subcategory     VARCHAR(50),
    brand           VARCHAR(50),
    unit_price      DECIMAL(10,2)
);

-- Create Fact Table
CREATE TABLE fact_sales (
    sale_id             BIGINT PRIMARY KEY,
    date_key            INT REFERENCES dim_date(date_key),
    product_key         INT REFERENCES dim_product(product_key),
    customer_key        INT REFERENCES dim_customer(customer_key),
    quantity_sold       INT,
    unit_price          DECIMAL(10,2),
    total_amount        DECIMAL(12,2)
);

Snowflake Schema vs Star Schema

The Snowflake Schema normalizes dimension tables into multiple related tables, reducing data redundancy but increasing query complexity.

Slowly Changing Dimensions (SCD)

SCDs handle how historical data in dimension tables is managed when attributes change over time.

SCD Type Comparison

SCD Type 2 Implementation SQL

-- SCD Type 2 Table Design
CREATE TABLE dim_customer_scd2 (
    customer_key    INT IDENTITY(1,1) PRIMARY KEY,
    customer_id     VARCHAR(20),
    customer_name   VARCHAR(100),
    city            VARCHAR(50),
    effective_date  DATE,
    expiry_date     DATE DEFAULT '9999-12-31',
    is_current      BOOLEAN DEFAULT TRUE,
    version         INT DEFAULT 1
);

-- SCD Type 2 Merge Operation
MERGE INTO dim_customer_scd2 AS target
USING staging_customer AS source
ON target.customer_id = source.customer_id
AND target.is_current = TRUE
WHEN MATCHED AND (
    target.customer_name != source.customer_name OR
    target.city != source.city
) THEN
    UPDATE SET
        is_current = FALSE,
        expiry_date = CURRENT_DATE
WHEN NOT MATCHED THEN
    INSERT (customer_id, customer_name, city, effective_date, version)
    VALUES (
        source.customer_id,
        source.customer_name,
        source.city,
        CURRENT_DATE,
        COALESCE((SELECT MAX(version) + 1
                  FROM dim_customer_scd2
                  WHERE customer_id = source.customer_id), 1)
    );

Amazon Redshift Cluster Configuration

-- Create Redshift Cluster with RA3 nodes
CREATE CLUSTER warehouse_cluster
    NODETYPE ra3.xlplus
    CLUSTERNUMBER 3
    MASTER_USERNAME admin
    MASTER_USERPASSWORD 'SecurePassword123!'
    DBNAME analytics
    VPCSECURITYGROUPID sg-xxxxxxxx
    IAMROLE ARN:aws:iam::123456789012:role/RedshiftRole
    ENCRYPTION YES
    KMSKEYID arn:aws:kms:us-east-1:123456789012:key/xxx
    PUBLICALLYACCESSIBLE NO;

-- Configure Redshift Spectrum for S3 access
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 's3_data'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

ETL vs ELT for Data Warehouses

AWS Glue ELT Example

import sys
from awsglue.transforms import *
from awsglue.utils import getResolvedOptions
from pyspark.context import SparkContext
from awsglue.context import GlueContext
from awsglue.job import Job

args = getResolvedOptions(sys.argv, ['JOB_NAME'])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args['JOB_NAME'], args)

source_dyf = glueContext.create_dynamic_frame.from_options(
    connection_type="s3",
    format="parquet",
    connection_options={"paths": ["s3://raw-data/sales/"]},
    format_options={"compression": "snappy"}
)

mapped_dyf = ApplyMapping.apply(
    frame=source_dyf,
    mappings=[
        ("sale_id", "long", "sale_id", "long"),
        ("date", "string", "sale_date", "date"),
        ("product_id", "string", "product_key", "int"),
        ("amount", "double", "total_amount", "decimal(12,2)")
    ]
)

filtered_dyf = Filter.apply(
    frame=mapped_dyf,
    f=lambda x: x["total_amount"] > 0
)

glueContext.write_dynamic_frame.from_jdbc_conf(
    frame=filtered_dyf,
    catalog_connection="redshift-connection",
    connection_options={
        "dbtable": "fact_sales",
        "database": "analytics"
    },
    redshift_tmp_dir="s3://tmp/redshift/"
)

job.commit()

Real-World Project Structure

data-warehouse-project/
├── infrastructure/
│   ├── redshift-cluster.tf
│   ├── iam-roles.tf
│   ├── vpc-config.tf
│   └── kms-key.tf
├── etl/
│   ├── glue-jobs/
│   │   ├── dim_customer_etl.py
│   │   ├── dim_product_etl.py
│   │   ├── fact_sales_etl.py
│   │   └── scd2_processor.py
│   └── step-functions/
│       └── warehouse-pipeline.asl.json
├── sql/
│   ├── ddl/
│   │   ├── create_dim_date.sql
│   │   ├── create_dim_product.sql
│   │   └── create_fact_sales.sql
│   └── dml/
│       ├── load_dimensions.sql
│       └── load_facts.sql
├── monitoring/
│   ├── cloudwatch-alarms.json
│   └── dashboards/
└── tests/
    ├── test_schema.py
    └── test_etl.py

Performance Considerations

Security Considerations

Mathematical Formulas

Storage cost estimation:

Monthly Storage Cost = Data Size (TB) x Price per TB/month
RA3 Storage Cost = Managed Storage (TB) x $0.024/TB/month

Query performance estimation:

Query Time = (Data Scanned / Throughput) x Complexity Factor
Throughput = Nodes x Cores per Node x 1GB/core/sec

Compute pricing:

Hourly Cost = Node Count x Price per Node/Hour
Reserved Savings = On-Demand x (1 - Discount%)
1-Year RI Discount = ~32-40%
3-Year RI Discount = ~55-65%

Interview Questions & Answers

Q1: What is the difference between a data warehouse and a data lake?

Answer: A data warehouse stores structured, processed data optimized for analytical queries (OLAP). It follows a predefined schema and is designed for BI and reporting. A data lake stores raw data of any format (structured, semi-structured, unstructured) at scale, using schema-on-read. Data lakes support ML and exploration, while warehouses optimize for consistent, governed analytics. Many modern architectures use both: raw data in a lake, curated data in a warehouse.

Q2: When would you choose a snowflake schema over a star schema?

Answer: Choose snowflake when storage costs are a concern (normalized tables reduce redundancy), data integrity is critical (single source of truth per attribute), dimensions have deep hierarchies, and multiple fact tables share normalized dimensions. Choose star when query performance is paramount (fewer JOINs), BI tools need simple flat structures, development speed is critical, and storage is cheap relative to compute. On Redshift, star schemas typically perform better due to the columnar storage minimizing the redundancy penalty.

Q3: Explain SCD Type 2 and why it is commonly used in data warehouses.

Answer: SCD Type 2 preserves full historical tracking by creating new rows for each change. Each version has effective_date, expiry_date, and is_current flags. It is preferred because it provides a complete audit trail, historical reports remain accurate, no data loss occurs, it supports time-travel queries, and it is the industry standard. The trade-off is increased storage and slightly more complex ETL logic. Implementation typically uses MERGE statements to detect changes and insert new versions.

Q4: What are distribution keys in Amazon Redshift and why do they matter?

Answer: Distribution keys (DISTKEY) determine how data is distributed across cluster nodes. Redshift uses hash distribution: KEY distribution sends rows with the same DISTKEY value to the same node (ideal for large tables frequently JOINed); EVEN distribution uses round-robin (good for small dimension tables); ALL distribution copies every row to every node (best for small, rarely-changing tables). Choosing the right DISTKEY minimizes data movement during JOINs, which is the most expensive operation in MPP systems.

Q5: How does Redshift Spectrum differ from Redshift itself?

Answer: Redshift stores data in cluster-optimized SSD storage for fastest queries. Redshift Spectrum extends queries to data sitting in S3 without loading it into Redshift. Spectrum queries data directly in S3 (Parquet, ORC, JSON, CSV), uses external tables defined in AWS Glue Data Catalog, leverages the Redshift cluster for compute and S3 for storage, and is ideal for infrequently accessed historical data or data lake exploration. It is cost-effective for archival data that does not need to be loaded.

Q6: What is the role of AWS Glue in a data warehouse architecture?

Answer: AWS Glue serves multiple roles: ETL Jobs for extract, transform, and load operations; Data Catalog as a central metadata repository; Crawlers for auto-discovering schema from data sources; Studio for visual ETL job building; and Schema Registry for managing Avro schemas for streaming data. Glue connects data sources to Redshift, S3, and other analytics services, forming the backbone of AWS data integration.

Q7: How do you handle late-arriving data in a data warehouse?

Answer: Strategies include: Buffer Windows (wait 24-48 hours before finalizing fact loads), SCD Type 2 (create new version rows for late updates), Partition Redesign (use hourly/daily partitions that can be reprocessed), Lambda Architecture (combine batch and streaming layers), UPSERT Patterns (use MERGE statements to update existing records), and Staging Layers (stage late data, validate, then merge into facts). The choice depends on latency requirements and data volume.

Q8: What are materialized views and when would you use them in Redshift?

Answer: Materialized views pre-compute and store query results physically. Use them when dashboards need fast response on complex aggregations, queries join multiple large tables frequently, the same expensive computation runs repeatedly, and real-time freshness is not required. Redshift auto-refreshes materialized views when underlying tables change. They reduce query latency from minutes to milliseconds for common analytical patterns. They are particularly effective for dashboards that run the same aggregation queries repeatedly.

Q9: Explain the difference between OLTP and OLAP systems.

Answer:

Q10: How would you design a data warehouse for an e-commerce company?

Answer: Key design decisions: Fact Tables (fact_orders, fact_order_items, fact_payments, fact_page_views), Dimensions (dim_customer, dim_product, dim_date, dim_promotion, dim_warehouse, dim_shipping), Grain (one row per order item), SCD Strategy (Type 2 for customer attributes, Type 1 for product pricing), Distribution (DISTKEY on customer_key), Sort Key (order_date for time-range queries), Partitions by order_date month for Spectrum queries, and Aggregations via materialized views for daily summaries.

Common Pitfalls

Knowledge Check

See Also

• AWS Redshift
• Redshift Spectrum
• Redshift Serverless