Why This Matters

A data warehouse on AWS is a centralized repository that consolidates data from multiple sources for analytical processing and business intelligence. Amazon Redshift serves as the core analytical engine, providing massively parallel processing (MPP), columnar storage, and SQL-based querying optimized for analytics workloads. Understanding data warehouse patterns is critical for data engineers because the choice between provisioned and serverless, distribution styles, and sort key design directly impacts query performance, cost, and scalability. Mastering these patterns enables you to build warehouses that serve BI dashboards, ad-hoc analytics, and machine learning pipelines with sub-second response times.

Data Warehouse Architecture

Serverless vs Provisioned

Real-World Project Structure

aws-data-warehouse/
├── infrastructure/
│   ├── terraform/
│   │   ├── redshift.tf              # Cluster, parameters, roles
│   │   ├── spectrum.tf              # External schemas, IAM roles
│   │   └── vpc.tf                   # VPC, subnets, security groups
│   └── cloudformation/
│       └── warehouse-stack.yaml     # Full stack definition
├── schema/
│   ├── dimensions/
│   │   ├── dim_date.sql             # Date dimension
│   │   ├── dim_customer.sql         # Customer dimension
│   │   ├── dim_product.sql          # Product dimension
│   │   └── dim_store.sql            # Store dimension
│   ├── facts/
│   │   ├── fact_sales.sql           # Sales fact table
│   │   └── fact_orders.sql          # Orders fact table
│   └── staging/
│       ├── stg_orders.sql           # Staging tables
│       └── stg_customers.sql
├── etl/
│   ├── glue/
│   │   ├── etl_bronze_to_silver.py  # Glue ETL jobs
│   │   └── etl_silver_to_gold.py
│   ├── redshift_sql/
│   │   ├── copy_commands.sql        # COPY from S3
│   │   ├── transform_queries.sql    # ELT transformations
│   │   └── vacuum_analyze.sql       # Maintenance
│   └── orchestration/
│       └── etl_workflow.asl.json    # Step Functions
├── optimization/
│   ├── distribution/
│   │   └── analyze_skew.sql         # Skew detection
│   ├── sort_keys/
│   │   └── query_pattern_analysis.sql
│   └── materialized_views/
│       └── create_views.sql         # MV definitions
├── monitoring/
│   ├── queries/
│   │   ├── performance_monitor.sql  # Query performance
│   │   ├── storage_monitor.sql      # Storage usage
│   │   └── wlm_monitor.sql          # Workload management
│   └── alerts/
│       └── cloudwatch_alarms.json   # Alert configuration
└── tests/
    ├── data_quality/
    │   ├── dimension_checks.sql     # Dimension validation
    │   └── fact_checks.sql          # Fact validation
    └── integration/
        └── query_regression.sql     # Performance regression

Dimensional Modeling

Star Schema Creation

-- Date Dimension (small, static)
CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,
    full_date DATE NOT NULL,
    year INT NOT NULL,
    quarter INT NOT NULL,
    month INT NOT NULL,
    month_name VARCHAR(10),
    day INT NOT NULL,
    day_of_week VARCHAR(10),
    is_weekend BOOLEAN
)
DISTSTYLE ALL
SORTKEY(full_date);

-- Customer Dimension (medium, joins to facts)
CREATE TABLE dim_customer (
    customer_key INT PRIMARY KEY IDENTITY(1,1),
    customer_id VARCHAR(50) NOT NULL,
    name VARCHAR(100),
    segment VARCHAR(50),
    region VARCHAR(50),
    tier VARCHAR(20)
)
DISTSTYLE KEY
DISTKEY(customer_key)
SORTKEY(customer_id);

-- Product Dimension
CREATE TABLE dim_product (
    product_key INT PRIMARY KEY IDENTITY(1,1),
    product_id VARCHAR(50) NOT NULL,
    product_name VARCHAR(200),
    category VARCHAR(100),
    subcategory VARCHAR(100),
    price DECIMAL(10,2)
)
DISTSTYLE ALL
SORTKEY(category);

-- Fact Table (largest, most joins)
CREATE TABLE fact_sales (
    sale_key BIGINT PRIMARY KEY IDENTITY(1,1),
    date_key INT NOT NULL REFERENCES dim_date(date_key),
    customer_key INT NOT NULL REFERENCES dim_customer(customer_key),
    product_key INT NOT NULL REFERENCES dim_product(product_key),
    store_key INT NOT NULL REFERENCES dim_store(store_key),
    quantity INT,
    amount DECIMAL(12,2),
    discount DECIMAL(12,2),
    profit DECIMAL(12,2)
)
DISTSTYLE KEY
DISTKEY(customer_key)
COMPOUND SORTKEY(date_key, customer_key);

ETL/ELT for Warehouses

COPY Command Optimization

-- Bulk load from S3 with Parquet format
COPY fact_sales
FROM 's3://data-warehouse-raw/sales/2024/01/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS PARQUET
COMPUPDATE ON
STATUPDATE ON;

-- Load with manifest file for reliability
COPY dim_product
FROM 's3://data-warehouse-raw/products/manifest.json'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS JSON 's3://data-warehouse-raw/products/jsonpaths.json'
REGION 'us-east-1';

ELT Transformation in Redshift

-- Create staging table from external source
CREATE TABLE staging_orders AS
SELECT * FROM ext_orders;

-- Transform and load into fact table
INSERT INTO fact_orders (
    order_date_key, customer_key, product_key,
    order_amount, tax_amount, shipping_cost
)
SELECT
    d.date_key,
    c.customer_key,
    p.product_key,
    s.order_amount,
    s.order_amount * 0.08 AS tax_amount,
    CASE
        WHEN s.order_amount > 100 THEN 0
        ELSE 9.99
    END AS shipping_cost
FROM staging_orders s
JOIN dim_date d ON s.order_date = d.full_date
JOIN dim_customer c ON s.customer_id = c.customer_id
JOIN dim_product p ON s.product_id = p.product_id;

-- Maintain statistics
VACUUM fact_orders;
ANALYZE fact_orders;

Query Optimization

Distribution Strategy

Sort Key Design

EXPLAIN and Performance Analysis

-- Analyze query plan
EXPLAIN
SELECT
    d.year,
    d.quarter,
    c.segment,
    SUM(f.amount) AS total_sales,
    COUNT(*) AS transaction_count
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_customer c ON f.customer_key = c.customer_key
WHERE d.year = 2024
GROUP BY d.year, d.quarter, c.segment;

-- Monitor disk usage per slice
SELECT
    slice,
    col_len,
    num_values
FROM svv_diskusage
WHERE tbl = 'fact_sales'
ORDER BY slice;

Mathematical Formulas

Distribution Skew

Storage Cost

Query Performance

Performance Considerations

Security Considerations

Interview Questions & Answers

Q1: Explain the difference between Redshift Provisioned and Serverless.

Answer:

Provisioned clusters require you to specify node types (RA3, DC2), node count, and manage scaling. You pay per node-hour and can purchase reserved instances for up to 75% savings. Best for predictable, steady-state workloads.

Serverless eliminates cluster management entirely. You define base capacity in RPU-hours and the service scales automatically. You pay per RPU-hour consumed. Best for variable workloads, dev/test, and ad-hoc analytics.

Decision framework:

• Predictable workload > 80% utilization: Provisioned (reserved pricing wins)
• Variable/unpredictable workload: Serverless (pay for what you use)
• Need specific instance types: Provisioned only
• Quick setup without DBA knowledge: Serverless

Q2: How do you design a star schema for a sales data warehouse on Redshift?

Answer:

• fact_sales: Contains quantitative measures (amount, quantity, discount) and foreign keys. Uses DISTKEY(customer_key) for joins.
• dim_date: Small static table (<40K rows). Uses DISTSTYLE ALL so every node has a copy.
• dim_customer: Medium-large table (millions). Uses DISTKEY(customer_key) to co-locate with facts.
• dim_product: Medium table. Uses DISTSTYLE ALL if under 2M rows, otherwise KEY.
• Sort keys: fact_sales uses COMPOUND SORTKEY(date_key, customer_key) since date is the most common filter.

Q3: How do you optimize a slow-running Redshift query?

Answer:

1. Diagnose with EXPLAIN: Look for sequential scans, data movement operations (BROADCAST, SHUFFLE)
2. Check sort key alignment: Add interleaved sort keys if queries filter on multiple columns
3. Verify distribution: Align DISTKEYs on join columns; use DISTSTYLE ALL for small tables
4. Create materialized views: For frequently-run aggregations
5. Filter early: Use WHERE clauses aligned with sort keys to minimize data scanned
6. Monitor with system tables: Query STL_SCAN, STL_SORT, STL_WLM_QUERY

Q4: Explain the ETL vs ELT decision for a Redshift warehouse.

Answer:

ETL (Transform before load): Use when data must be cleansed, masked, or conformed before entering the warehouse. AWS Glue reads from RDS, applies PySpark transformations, writes clean data to Redshift.

ELT (Load then transform): Use when raw data should be preserved for audit or reprocessing. COPY raw data from S3 into staging tables, then use SQL to transform and load into dimension/fact tables.

Hybrid approach: Many production systems use both. Raw data lands via COPY (ELT), while specific pipelines requiring complex logic use Glue (ETL).

Q5: How do you handle data skew in a Redshift cluster?

Answer:

Identifying skew:

SELECT slice, COUNT(*) AS row_count
FROM stv_blocks
WHERE tbl = (SELECT oid FROM pg_class WHERE relname = 'fact_sales')
GROUP BY slice ORDER BY slice;

Common causes and fixes:

• Poor DISTKEY choice: Use high-cardinality columns that evenly distribute data
• Hot node: Redistribute using a different DISTKEY or switch to DISTSTYLE EVEN
• Data growth imbalance: Consider compound sort keys aligned with temporal patterns

Target skew ratio should be below 4:1.

Q6: How would you implement data quality checks in a Redshift warehouse pipeline?

Answer:

Layer 1: Table constraints

ALTER TABLE dim_customer ADD CONSTRAINT pk_customer PRIMARY KEY (customer_key);
ALTER TABLE fact_sales ADD CONSTRAINT fk_sales_customer
    FOREIGN KEY (customer_key) REFERENCES dim_customer(customer_key);
ALTER TABLE fact_sales ADD CONSTRAINT nn_amount CHECK (amount > 0);

Layer 2: Assertion tables

CREATE TABLE dq_assertions AS
SELECT 'missing_date_keys' AS check_name, COUNT(*) AS violation_count
FROM fact_sales f
LEFT JOIN dim_date d ON f.date_key = d.date_key
WHERE d.date_key IS NULL;

Layer 3: Monitoring queries for null percentages, duplicates, and range violations.

Q7: How do you implement slowly changing dimensions (SCD) in Redshift?

Answer:

SCD Type 1 (Overwrite): Update existing records. No history preserved.

UPDATE dim_customer SET segment = 'Premium' WHERE customer_id = 'CUST-12345';

SCD Type 2 (Historical): Add new rows with effective dates. Full history preserved.

UPDATE dim_customer SET valid_to = CURRENT_DATE - INTERVAL '1 day'
WHERE customer_key = 5678 AND valid_to = '9999-12-31';

INSERT INTO dim_customer (customer_id, name, segment, valid_from, valid_to, is_current)
VALUES ('CUST-12345', 'Jane Doe', 'Premium', CURRENT_DATE, '9999-12-31', TRUE);

SCD Type 3 (Limited History): Store previous and current values in separate columns.

Q8: How do you monitor and tune Redshift cluster performance in production?

Answer:

Key monitoring queries:

SELECT query, starttime, endtime, elapsed / 1000000 AS seconds, rows
FROM stl_query WHERE userid > 1 ORDER BY starttime DESC LIMIT 20;

SELECT service_class, num_queued_queries, num_executing_queries
FROM stv_wlm_service_class_state WHERE service_class > 4;

Performance tuning checklist:

• Run ANALYZE after every bulk load
• VACUUM after deleting large volumes
• Monitor STL_LOAD_ERRORS for COPY failures
• Use EXPLAIN before deploying complex queries
• Track skew ratio across slices
• Configure CloudWatch alarms on CPU, disk, query duration

Common Pitfalls

See Also

• AWS Redshift
• Redshift Serverless
• Redshift Spectrum
• AWS Data Warehouse