Amazon Redshift Fundamentals: MPP Data Warehouse

-- Create cluster via AWS CLI
-- aws redshift create-cluster \
--   --cluster-identifier my-cluster \
--   --node-type ra3.4xlarge \
--   --number-of-nodes 4 \
--   --master-username admin \
--   --master-user-password xxx \
--   --db-name analytics

-- Create table with distribution key and sort key
CREATE TABLE fact_orders (
    order_id        BIGINT NOT NULL,
    customer_key    INT NOT NULL,
    product_key     INT NOT NULL,
    order_date      DATE NOT NULL,
    quantity        INT,
    unit_price      DECIMAL(12,2),
    net_amount      DECIMAL(14,2),
    order_status    VARCHAR(20),
    region          VARCHAR(20)
)
DISTSTYLE KEY
DISTKEY(customer_key)           -- Distribute by customer
COMPOUND SORTKEY(order_date, order_status);  -- Sort within slices

-- Dimension table with ALL distribution (small table)
CREATE TABLE dim_date (
    date_key        INT NOT NULL,
    full_date       DATE,
    day_of_week     SMALLINT,
    month_name      VARCHAR(10),
    quarter         SMALLINT,
    year            INT
)
DISTSTYLE ALL
SORTKEY(full_date);

-- Auto distribution (Redshift Spectrum managed)
CREATE TABLE external_orders (
    order_id        BIGINT,
    customer_id     INT,
    order_date      DATE,
    amount          DECIMAL(12,2)
)
STYLE AUTO  -- Redshift automatically selects distribution
EXTERNAL DISTRIBUTION;

-- COPY from S3
COPY fact_orders
FROM 's3://data-lake/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS PARQUET
REGION 'us-east-1';

-- COPY with options
COPY fact_orders
FROM 's3://data-lake/orders/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS CSV
IGNOREHEADER 1
DATEFORMAT 'YYYY-MM-DD'
EMPTYASNULL
BLANKSASNULL
MAXERROR 100
COMPUPDATE OFF
STATUPDATE ON;

-- Analyze distribution skew
SELECT
    stv_partitions.node,
    stv_partitions.slice,
    SUM(stv_partitions.rows) AS row_count,
    SUM(stv_partitions.bytes) / 1024 / 1024 AS mb_used
FROM stv_partitions
WHERE tbl = (SELECT oid FROM pg_class WHERE relname = 'fact_orders')
GROUP BY stv_partitions.node, stv_partitions.slice
ORDER BY node, slice;

-- Identify skew with svv_table_info
SELECT
    tbl,
    tbl_rows,
    skew_sortkey1,
    skew_rows,
    CASE
        WHEN skew_rows > 10 THEN 'HIGH SKEW - CHANGE DISTKEY'
        WHEN skew_rows > 5 THEN 'MODERATE SKEW'
        ELSE 'ACCEPTABLE'
    END AS skew_assessment
FROM svv_table_info
WHERE schema = 'public';

-- Compare distribution effectiveness
EXPLAIN
SELECT
    c.customer_name,
    COUNT(*) AS order_count,
    SUM(f.net_amount) AS total_spent
FROM fact_orders f
JOIN dim_customer c ON f.customer_key = c.customer_key
GROUP BY c.customer_name;

-- Compound sort key (best for single-column filters)
CREATE TABLE fact_clickstream (
    click_id        BIGINT,
    session_id      BIGINT,
    event_timestamp TIMESTAMP,
    page_url        VARCHAR(500),
    user_agent      VARCHAR(500)
)
DISTSTYLE KEY
DISTKEY(session_id)
COMPOUND SORTKEY(event_timestamp);

-- Interleaved sort key (best for multi-column filters)
CREATE TABLE fact_transactions (
    transaction_id  BIGINT,
    account_id      INT,
    transaction_date DATE,
    merchant_id     INT,
    category        VARCHAR(50),
    amount          DECIMAL(12,2)
)
DISTSTYLE KEY
DISTKEY(account_id)
INTERLEAVED SORTKEY(transaction_date, merchant_id, category);

-- Analyze sort key effectiveness
SELECT
    tbl,
    skew_sortkey1,
    CASE
        WHEN skew_sortkey1 < 1.5 THEN 'EFFECTIVE'
        WHEN skew_sortkey1 < 3.0 THEN 'MODERATE'
        ELSE 'INEFFECTIVE'
    END AS sort_effectiveness
FROM svv_table_info;

-- Vacuum to re-sort after bulk loads
VACUUM SORT ONLY fact_orders;
VACUUM FULL fact_orders;
VACUUM DELETE ONLY fact_orders;

-- Create external schema pointing to Glue catalog
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'analytics_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/SpectrumRole'
REGION 'us-east-1';

-- Query external tables (Parquet in S3)
CREATE EXTERNAL TABLE spectrum_schema.events (
    event_id    BIGINT,
    event_type  VARCHAR(50),
    user_id     BIGINT,
    event_ts    TIMESTAMP,
    properties  STRING  -- JSON string
)
PARTITIONED BY (event_date STRING)
STORED AS PARQUET
LOCATION 's3://data-lake/events/'
TABLE PROPERTIES ('parquet.compression' = 'SNAPPY');

-- Query combining internal and external data
SELECT
    c.customer_name,
    COUNT(e.event_id) AS event_count,
    SUM(CASE WHEN e.event_type = 'purchase' THEN 1 ELSE 0 END) AS purchases
FROM spectrum_schema.events e
JOIN dim_customer c ON e.user_id = c.customer_id
WHERE e.event_date = '2025-01-15'
GROUP BY c.customer_name;

-- Federated query to Aurora PostgreSQL
CREATE EXTERNAL SCHEMA aurora_schema
FROM POSTGRES
DATABASE 'app_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/FederatedRole'
URI 'my-aurora-cluster.cluster-xxxxx.us-east-1.rds.amazonaws.com:5432'
SECRET_ARN 'arn:aws:secretsmanager:us-east-1:123456789012:secret:redshift-aurora';

SELECT * FROM aurora_schema.users WHERE created_at > '2025-01-01';