Top 20 AWS Redshift Interview Questions and Answers

1. What is Amazon Redshift?
2. What is Redshift architecture?
3. What are Redshift node types?
4. What are distribution styles in Redshift?
5. What are sort keys in Redshift?
6. What is Redshift Spectrum?
7. How do you load data into Redshift?
8. What is the COPY command?
9. What is Redshift Serverless?
10. How do you optimize query performance?
11. What is workload management (WLM)?
12. What are materialized views in Redshift?
13. How do you implement data sharing?
14. What is concurrency scaling?
15. How do you handle vacuuming and analyzing?
16. What are Redshift security best practices?
17. How do you monitor Redshift?
18. What is Redshift ML?
19. How do you migrate to Redshift?
20. What are Redshift best practices?

1. What is Amazon Redshift?

Amazon Redshift is a fully managed, petabyte-scale cloud data warehouse that uses columnar storage and parallel query execution for fast analytics.

Key Features:
- Columnar storage with compression
- Massively Parallel Processing (MPP)
- SQL-based interface (PostgreSQL compatible)
- Automatic backups and snapshots
- Integration with AWS ecosystem

Redshift Capabilities:
├── Traditional BI/Analytics
├── Operational Analytics
├── Data Lake Queries (Spectrum)
├── Federated Queries (RDS, Aurora)
├── Machine Learning (Redshift ML)
├── Data Sharing (cross-account)
└── Streaming Ingestion (Kinesis, MSK)

# Connect to Redshift
import psycopg2

conn = psycopg2.connect(
    host='cluster.us-east-1.redshift.amazonaws.com',
    port=5439,
    database='mydb',
    user='admin',
    password='password'
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM sales LIMIT 10")

2. What is Redshift architecture?

Redshift Cluster Architecture:

┌─────────────────────────────────────────┐
│           Leader Node                    │
│  - SQL parsing and planning             │
│  - Query optimization                   │
│  - Result aggregation                   │
│  - Client connections                   │
└────────────────┬────────────────────────┘
                 │
    ┌────────────┼────────────┐
    ▼            ▼            ▼
┌────────┐  ┌────────┐  ┌────────┐
│Compute │  │Compute │  │Compute │
│Node 1  │  │Node 2  │  │Node 3  │
│┌──────┐│  │┌──────┐│  │┌──────┐│
││Slice1││  ││Slice1││  ││Slice1││
│├──────┤│  │├──────┤│  │├──────┤│
││Slice2││  ││Slice2││  ││Slice2││
│└──────┘│  │└──────┘│  │└──────┘│
└────────┘  └────────┘  └────────┘

Key Concepts:
├── Leader Node: Query coordination
├── Compute Nodes: Data storage and query execution
├── Slices: Portion of compute node's memory/disk
├── Columns: Data stored by column, not row
└── Blocks: Columnar data stored in 1MB blocks

3. What are Redshift node types?

Type	Storage	Use Case
RA3	Managed (RMS)	Scale compute/storage independently
DC2	Local SSD	High performance, smaller datasets
DS2	Local HDD	Legacy, large storage (deprecated)

RA3 Node Types (Recommended):
├── ra3.xlplus: 32 GB, 32 TB managed storage
├── ra3.4xlarge: 96 GB, 128 TB managed storage
└── ra3.16xlarge: 384 GB, 128 TB managed storage

DC2 Node Types:
├── dc2.large: 160 GB SSD, 0.16 TB/node
└── dc2.8xlarge: 2.56 TB SSD, 2.56 TB/node

RA3 Benefits:
- Redshift Managed Storage (RMS)
- Hot data cached on SSD
- Cold data on S3 automatically
- Scale compute without storage limits
- Cross-AZ durability

# Create RA3 cluster
aws redshift create-cluster \
    --cluster-identifier my-cluster \
    --node-type ra3.4xlarge \
    --number-of-nodes 3 \
    --master-username admin \
    --master-user-password MyPassword123

4. What are distribution styles in Redshift?

Distribution style determines how data is distributed across nodes for parallel processing.

Distribution Styles:

1. KEY Distribution
-- Rows with same key on same slice
-- Best for join columns
CREATE TABLE orders (
    order_id INT,
    customer_id INT,
    amount DECIMAL(10,2)
)
DISTKEY(customer_id);

2. EVEN Distribution
-- Round-robin distribution
-- Default, good for uniform access
CREATE TABLE events (
    event_id INT,
    event_data VARCHAR(1000)
)
DISTSTYLE EVEN;

3. ALL Distribution
-- Copy entire table to all nodes
-- Small dimension tables
CREATE TABLE countries (
    country_code CHAR(2),
    country_name VARCHAR(100)
)
DISTSTYLE ALL;

4. AUTO Distribution
-- Redshift chooses automatically
-- Starts as ALL, converts to EVEN if grows
CREATE TABLE products (
    product_id INT,
    name VARCHAR(200)
)
DISTSTYLE AUTO;

Best Practices:
├── KEY: Large fact tables, join columns
├── ALL: Small dimension tables (< 3M rows)
├── EVEN: No clear join pattern
└── AUTO: Let Redshift decide

5. What are sort keys in Redshift?

Sort keys determine the physical order of data on disk, enabling efficient range queries and joins.

Sort Key Types:

1. Compound Sort Key
-- Columns sorted in defined order
-- Best for queries filtering on prefix
CREATE TABLE sales (
    sale_date DATE,
    region VARCHAR(50),
    product_id INT,
    amount DECIMAL(10,2)
)
COMPOUND SORTKEY(sale_date, region);

-- Efficient: WHERE sale_date = '2024-01-01'
-- Efficient: WHERE sale_date = '2024-01-01' AND region = 'US'
-- Inefficient: WHERE region = 'US' (skips first column)

2. Interleaved Sort Key
-- Equal weight to all columns
-- Good for multiple query patterns
CREATE TABLE events (
    event_date DATE,
    user_id INT,
    event_type VARCHAR(50)
)
INTERLEAVED SORTKEY(event_date, user_id, event_type);

-- Efficient for any column filter
-- Higher VACUUM overhead

3. AUTO Sort Key
-- Redshift manages automatically
CREATE TABLE logs (
    log_time TIMESTAMP,
    message TEXT
)
SORTKEY AUTO;

Zone Maps:
-- Redshift maintains min/max per block
-- Skips blocks that can't contain data
-- Sort key maximizes zone map effectiveness

6. What is Redshift Spectrum?

Redshift Spectrum enables querying data in S3 directly from Redshift without loading it.

Spectrum Architecture:
┌──────────────┐     ┌─────────────────────┐
│   Redshift   │────▶│  Spectrum Layer     │
│   Cluster    │     │  (Shared compute)   │
└──────────────┘     └──────────┬──────────┘
                                │
                     ┌──────────▼──────────┐
                     │   Amazon S3         │
                     │   (External data)   │
                     └─────────────────────┘

# Create external schema
CREATE EXTERNAL SCHEMA spectrum_schema
FROM DATA CATALOG
DATABASE 'my_glue_database'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

# Create external table
CREATE EXTERNAL TABLE spectrum_schema.sales (
    sale_id INT,
    customer_id INT,
    amount DECIMAL(10,2),
    sale_date DATE
)
PARTITIONED BY (year INT, month INT)
STORED AS PARQUET
LOCATION 's3://bucket/sales/';

# Add partitions
ALTER TABLE spectrum_schema.sales
ADD PARTITION (year=2024, month=1)
LOCATION 's3://bucket/sales/year=2024/month=1/';

# Query joining local and S3 data
SELECT c.name, SUM(s.amount)
FROM local_schema.customers c
JOIN spectrum_schema.sales s ON c.id = s.customer_id
WHERE s.year = 2024
GROUP BY c.name;

7. How do you load data into Redshift?

Data Loading Methods:

1. COPY Command (Fastest)
COPY sales FROM 's3://bucket/data/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftRole'
FORMAT AS PARQUET;

2. INSERT (Small data)
INSERT INTO sales (id, amount) VALUES (1, 100.00);

3. AWS Glue
-- ETL job writes directly to Redshift

4. Kinesis Data Firehose
-- Streaming data delivery

5. Streaming Ingestion
CREATE MATERIALIZED VIEW sales_stream AS
SELECT *
FROM kinesis_schema.sales_stream
WHERE is_json_valid(json_data);

6. Zero-ETL (Aurora → Redshift)
-- Near real-time replication
-- No ETL code needed

Loading Best Practices:
├── Use COPY, not INSERT for bulk
├── Split files (parallel load)
├── Use columnar formats (Parquet, ORC)
├── Compress data (GZIP, LZO, ZSTD)
├── Sort input files by sort key
└── Load during maintenance window

8. What is the COPY command?

COPY is the most efficient way to load data into Redshift from S3, DynamoDB, or remote hosts.

COPY Command Syntax:
COPY table_name
FROM 's3://bucket/prefix'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftRole'
[options];

# Load CSV with options
COPY sales
FROM 's3://bucket/sales/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftRole'
CSV
IGNOREHEADER 1
DELIMITER ','
DATEFORMAT 'YYYY-MM-DD'
TIMEFORMAT 'auto'
REGION 'us-east-1'
MAXERROR 100
COMPUPDATE ON
STATUPDATE ON;

# Load Parquet (no format options needed)
COPY sales
FROM 's3://bucket/sales/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftRole'
FORMAT AS PARQUET;

# Load from manifest
COPY sales
FROM 's3://bucket/manifest.json'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftRole'
MANIFEST;

# Manifest file
{
  "entries": [
    {"url": "s3://bucket/file1.csv", "mandatory": true},
    {"url": "s3://bucket/file2.csv", "mandatory": true}
  ]
}

# Check load errors
SELECT * FROM stl_load_errors ORDER BY starttime DESC LIMIT 10;

9. What is Redshift Serverless?

Redshift Serverless provides on-demand analytics without managing cluster infrastructure.

Redshift Serverless Components:
├── Namespace: Database objects, users, schemas
├── Workgroup: Compute resources, network config
└── RPU: Redshift Processing Units (billing)

Benefits:
- No cluster management
- Auto-scaling compute
- Pay per use (RPU-hours)
- Ideal for variable workloads

# Create via AWS CLI
aws redshift-serverless create-namespace \
    --namespace-name my-namespace \
    --admin-username admin \
    --admin-user-password MyPassword123 \
    --db-name mydb

aws redshift-serverless create-workgroup \
    --workgroup-name my-workgroup \
    --namespace-name my-namespace \
    --base-capacity 32 \
    --max-capacity 512

# Capacity settings
Base Capacity: 32-512 RPUs (minimum when active)
Max Capacity: Up to 512 RPUs (auto-scale limit)

Pricing:
- RPU-hours for compute
- GB-months for RMS storage
- No charge when idle

# Connection
Endpoint: workgroup-name.account-id.region.redshift-serverless.amazonaws.com:5439

10. How do you optimize query performance?

Query Optimization Strategies:

1. Use EXPLAIN to analyze
EXPLAIN SELECT * FROM sales WHERE date > '2024-01-01';
-- Check for seq scans, sort, distribution

2. Optimize table design
-- Appropriate distribution key (join columns)
-- Sort key (filter/join columns)
-- Compression encoding

3. Write efficient queries
-- Avoid SELECT *
-- Use WHERE to filter early
-- Avoid DISTINCT when possible
-- Use approximate functions

-- Bad
SELECT DISTINCT category FROM products;

-- Better
SELECT category FROM products GROUP BY category;

-- For counts, use approximation
SELECT APPROXIMATE COUNT(DISTINCT user_id) FROM events;

4. Use result caching
-- Enable by default for identical queries
SET enable_result_cache_for_session TO ON;

5. Analyze statistics
ANALYZE sales;
-- Updates statistics for query optimizer

6. Review SVL tables
-- Execution details
SELECT * FROM svl_query_summary WHERE query = 123;

-- Disk-based operations (bad)
SELECT * FROM svl_query_report WHERE query = 123 AND is_diskbased = 't';

11. What is workload management (WLM)?

WLM manages query queues and resource allocation for different workloads.

WLM Modes:

1. Automatic WLM (Recommended)
-- Redshift manages memory and concurrency
-- Uses machine learning

2. Manual WLM
-- Define queues, memory, concurrency manually

# Configure WLM Parameter Group
{
    "wlm_json_configuration": [
        {
            "name": "ETL",
            "query_group": ["etl"],
            "memory_percent_to_use": 40,
            "max_execution_time": 3600000
        },
        {
            "name": "Reporting",
            "user_group": ["analysts"],
            "memory_percent_to_use": 40,
            "concurrency_scaling": "auto"
        },
        {
            "name": "Default",
            "memory_percent_to_use": 20,
            "query_concurrency": 5
        }
    ]
}

# Assign query to queue
SET query_group TO 'etl';
COPY sales FROM 's3://...';
RESET query_group;

# Short Query Acceleration (SQA)
-- Routes short queries to fast lane
-- Enable: Automatic WLM with SQA
-- Identifies queries < threshold (seconds)

# Monitor WLM
SELECT * FROM stv_wlm_query_state;
SELECT * FROM stl_wlm_query;

12. What are materialized views in Redshift?

Materialized views store precomputed query results for faster access to complex aggregations.

# Create materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT 
    sale_date,
    region,
    SUM(amount) as total_amount,
    COUNT(*) as order_count
FROM sales
GROUP BY sale_date, region;

# Refresh options
-- Manual refresh
REFRESH MATERIALIZED VIEW daily_sales;

-- Auto refresh (Redshift manages)
CREATE MATERIALIZED VIEW daily_sales
AUTO REFRESH YES
AS SELECT ...;

# Incremental refresh
-- For views with aggregations on tables with sort keys
-- Much faster than full refresh

# Query rewriting
-- Optimizer automatically uses MV
-- Even if you query base table
SET mv_enable_aqmv_for_session TO TRUE;

SELECT sale_date, SUM(amount)
FROM sales
WHERE sale_date > '2024-01-01'
GROUP BY sale_date;
-- May use daily_sales MV automatically

# Streaming ingestion with MV
CREATE MATERIALIZED VIEW streaming_sales AS
SELECT 
    approximate_date,
    SUM(amount) as total
FROM kinesis_stream
GROUP BY approximate_date;

13. How do you implement data sharing?

Data sharing enables sharing live data across Redshift clusters without copying.

Data Sharing Components:
├── Producer: Cluster that shares data
├── Consumer: Cluster that accesses data
├── Datashare: Collection of shared objects
└── Namespace: Cluster identifier

# On Producer Cluster
-- Create datashare
CREATE DATASHARE sales_share;

-- Add objects to share
ALTER DATASHARE sales_share ADD SCHEMA public;
ALTER DATASHARE sales_share ADD TABLE public.sales;
ALTER DATASHARE sales_share ADD TABLE public.customers;

-- Grant to consumer (same account)
GRANT USAGE ON DATASHARE sales_share
TO NAMESPACE 'consumer-namespace-id';

-- Grant to consumer (cross-account)
GRANT USAGE ON DATASHARE sales_share
TO ACCOUNT '123456789012';

# On Consumer Cluster
-- Create database from datashare
CREATE DATABASE shared_db FROM DATASHARE sales_share
OF NAMESPACE 'producer-namespace-id';

-- Query shared data
SELECT * FROM shared_db.public.sales;

Benefits:
├── Live data (no ETL)
├── No storage cost for consumer
├── Read-only access
├── Cross-account sharing
└── Supports Redshift Serverless

14. What is concurrency scaling?

Concurrency scaling automatically adds temporary cluster capacity during usage spikes.

How it Works:
1. Main cluster queue fills up
2. Redshift launches scaling clusters
3. Queries routed to scaling clusters
4. Results returned seamlessly
5. Scaling clusters terminated when idle

# Enable for WLM queue
{
    "name": "Reporting",
    "concurrency_scaling": "auto"
}

# Monitor scaling
SELECT * FROM svcs_concurrency_scaling_usage;

# Pricing
-- 1 hour free credit per 24 hours (per cluster)
-- Additional: Per-second billing

# Eligible queries
-- Read queries (SELECT)
-- COPY, UNLOAD, INSERT INTO SELECT
-- Not: DDL, maintenance

# Configure via parameter
-- max_concurrency_scaling_clusters: 0-10
-- 0 = disabled
-- 10 = up to 10 additional clusters

Benefits:
├── Predictable performance during spikes
├── No capacity planning needed
├── Pay only when used
└── Transparent to users

15. How do you handle vacuuming and analyzing?

VACUUM reclaims space and re-sorts rows; ANALYZE updates statistics for query optimization.

VACUUM Operations:

# Full vacuum (sort + delete)
VACUUM FULL sales;

# Delete only (reclaim deleted rows)
VACUUM DELETE ONLY sales;

# Sort only (re-sort unsorted rows)
VACUUM SORT ONLY sales;

# Reindex (interleaved sort key)
VACUUM REINDEX sales;

# Automatic vacuum
-- Redshift runs automatically during maintenance
-- Sort and delete thresholds monitored

# Check vacuum status
SELECT * FROM svv_vacuum_progress;
SELECT * FROM svv_vacuum_summary;

# Check table health
SELECT "table", unsorted, vacuum_sort_benefit
FROM svv_table_info
WHERE unsorted > 5;

ANALYZE Operations:

# Analyze all columns
ANALYZE sales;

# Analyze specific columns
ANALYZE sales(sale_date, customer_id);

# Auto analyze
-- Runs automatically after COPY
-- STATUPDATE ON in COPY command

# Check statistics
SELECT * FROM svv_table_info;

Best Practices:
├── Let auto vacuum/analyze run
├── Manual vacuum after large deletes
├── Analyze after schema changes
└── Monitor unsorted percentage

16. What are Redshift security best practices?

Security Layers:

1. Network Security
-- VPC with private subnets
-- Security groups (restrict port 5439)
-- VPC endpoints for S3, Glue

2. Encryption
-- At rest: KMS managed keys
-- In transit: SSL/TLS required

# Force SSL
ALTER USER admin SET require_ssl = true;

3. Authentication
-- Database users
-- IAM authentication
-- Federated access (SAML)

# IAM authentication
CREATE USER iam_user PASSWORD DISABLE;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO iam_user;

# Generate temp credentials
aws redshift get-cluster-credentials \
    --db-user iam_user \
    --cluster-identifier my-cluster

4. Authorization
-- Role-based access control
-- Row-level security
-- Column-level security

# Create role
CREATE ROLE analysts;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ROLE analysts;
GRANT ROLE analysts TO user1;

# Row-level security
CREATE RLS POLICY region_policy
WITH (region VARCHAR(50))
USING (region = current_setting('app.region'));

5. Audit Logging
-- Enable audit logging to S3
-- CloudTrail for API calls

17. How do you monitor Redshift?

Monitoring Tools:

1. CloudWatch Metrics
├── CPUUtilization
├── PercentageDiskSpaceUsed
├── ReadIOPS, WriteIOPS
├── DatabaseConnections
├── QueryDuration
└── WLMQueueLength

2. System Tables (STL, STV, SVL, SVV)
# Recent queries
SELECT query, starttime, endtime, querytxt
FROM stl_query
ORDER BY starttime DESC LIMIT 10;

# Query execution steps
SELECT * FROM svl_query_report WHERE query = 123;

# Disk-based queries (need more memory)
SELECT query, segment, step, is_diskbased
FROM svl_query_summary
WHERE is_diskbased = 't';

# Current running queries
SELECT * FROM stv_recents WHERE status = 'Running';

3. Query Monitoring Rules
# Terminate long queries
CREATE OR REPLACE RULE rule_long_queries AS
ON timeout WHEN (query_execution_time > 3600)
THEN abort;

4. Advisor Recommendations
SELECT * FROM svv_advisor_recommendations;
-- Distribution key suggestions
-- Sort key suggestions
-- Compression changes

5. CloudWatch Logs
-- User activity logs
-- Connection logs
-- User logs

18. What is Redshift ML?

Redshift ML enables creating, training, and deploying machine learning models using SQL.

# Create model (uses SageMaker Autopilot)
CREATE MODEL customer_churn_model
FROM (
    SELECT 
        tenure,
        monthly_charges,
        total_charges,
        contract_type,
        churn  -- Target column
    FROM customers
)
TARGET churn
FUNCTION predict_churn
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftMLRole'
SETTINGS (
    S3_BUCKET 'my-redshift-ml-bucket',
    MAX_RUNTIME 3600
);

# Check model status
SHOW MODEL customer_churn_model;

# Make predictions
SELECT 
    customer_id,
    predict_churn(tenure, monthly_charges, total_charges, contract_type) as predicted_churn
FROM new_customers;

# Supported problem types
├── Binary classification
├── Multi-class classification
├── Regression
└── BYOM (Bring Your Own Model)

# Import existing SageMaker model
CREATE MODEL sentiment_model
FUNCTION predict_sentiment (text VARCHAR)
RETURNS VARCHAR
SAGEMAKER 'sentiment-endpoint'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftMLRole';

19. How do you migrate to Redshift?

Migration Options:

1. AWS Schema Conversion Tool (SCT)
-- Convert schemas from Oracle, SQL Server, etc.
-- Identifies conversion issues
-- Generates target DDL

2. AWS Database Migration Service (DMS)
-- Full load + CDC
-- Minimal downtime
-- Supports many sources

# DMS Task for Redshift
{
    "TargetMetadata": {
        "TargetSchema": "",
        "SupportLobs": false,
        "FullLobMode": false,
        "LobMaxSize": 0
    },
    "FullLoadSettings": {
        "TargetTablePrepMode": "TRUNCATE_BEFORE_LOAD"
    }
}

3. AWS Glue
-- ETL transformations
-- Schema flexibility

4. COPY from S3
-- Export from source to S3
-- COPY into Redshift

Migration Steps:
1. Assess (SCT assessment report)
2. Convert schema (SCT)
3. Migrate data (DMS or COPY)
4. Validate data
5. Cutover application
6. Monitor performance

Best Practices:
├── Start with assessment
├── Test with subset of data
├── Optimize table design
├── Validate row counts
└── Plan maintenance window

20. What are Redshift best practices?

1. Table Design:

-- Choose appropriate distribution
-- Large fact tables: DISTKEY on join column
-- Small dimensions: DISTSTYLE ALL
-- Default: AUTO

-- Choose sort keys wisely
-- Filter columns in COMPOUND
-- Multiple patterns: INTERLEAVED (carefully)

-- Use compression
ANALYZE COMPRESSION sales;
-- Apply recommended encodings

2. Data Loading:
- Use COPY, not INSERT for bulk
- Split files for parallel load
- Use columnar formats (Parquet)
- Compress source files

3. Query Optimization:

-- Avoid SELECT *
-- Filter early with WHERE
-- Use EXPLAIN to analyze
-- Leverage result caching
-- Use materialized views

4. Maintenance:
- Monitor table health (unsorted %)
- Let auto vacuum run
- Update statistics regularly
- Review advisor recommendations

5. Cost Optimization:
- Right-size cluster
- Use Reserved Instances
- Pause clusters when idle
- Use Redshift Serverless for variable workloads
- Archive old data to S3 (query with Spectrum)