What is Redshift Spectrum?

Redshift Spectrum is a feature of Amazon Redshift that enables you to run SQL queries against exabytes of data in Amazon S3 without loading or moving data into Redshift clusters. It acts as a bridge between your Redshift data warehouse and your S3 data lake, providing a unified query experience across both structured and semi-structured data.

Core Concepts

Spectrum works by extending the Redshift query engine to access external data stored in S3. When you query an external table, Redshift delegates the data retrieval to the Spectrum layer, which reads the data directly from S3, applies filters and transformations, and returns the results back to Redshift for final processing.

Key characteristics of Redshift Spectrum:

• No data loading required: Query data in S3 as if it were a Redshift table
• Massively parallel: Spectrum scales to thousands of nodes to scan large datasets
• Pay-per-query pricing: $5 per TB of data scanned, with no upfront costs
• Schema-on-read: Define schemas externally without altering source data
• Federation: Join external S3 tables with internal Redshift tables seamlessly
• Partition pruning: Skip irrelevant data using partitioned columns

When to Use Spectrum

Use Redshift Spectrum when you have large volumes of historical or archival data in S3 that you need to query occasionally, when you want to avoid the cost and complexity of loading all data into Redshift, or when you need to query data in its native format without ETL pipelines.

Spectrum vs Athena vs Redshift External Tables

External Tables and Schemas

External tables in Redshift Spectrum are the primary mechanism for querying S3 data. An external table maps to files in S3 and defines the schema that Spectrum uses to read those files.

Creating External Schemas

An external schema maps a Redshift database schema to a data catalog (typically AWS Glue or Athena) and provides the IAM role that Spectrum uses to access S3.

-- Create an external schema using AWS Glue Data Catalog
CREATE EXTERNAL SCHEMA analytics_lake
FROM DATA CATALOG
DATABASE 'production_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole'
CREATE EXTERNAL DATABASE IF NOT EXISTS;

-- Create an external schema pointing to a specific database
CREATE EXTERNAL SCHEMA legacy_data
FROM DATA CATALOG
DATABASE 'legacy_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/SpectrumRole';

Creating External Tables

External tables define the column structure and location of data in S3. You can create them manually or use AWS Glue crawlers to auto-detect schemas.

-- Create a partitioned external table for sales data
CREATE EXTERNAL TABLE analytics_lake.sales (
    sale_id BIGINT,
    customer_id BIGINT,
    product_name VARCHAR(200),
    quantity INT,
    unit_price DECIMAL(10,2),
    total_amount DECIMAL(12,2),
    sale_date DATE
)
PARTITIONED BY (sale_year INT, sale_month INT)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
LOCATION 's3://data-lake-analytics/silver/sales/'
TABLE PROPERTIES (
    'parquet.compression'='SNAPPY',
    'classification'='parquet'
);

-- Create an external table for JSON data
CREATE EXTERNAL TABLE analytics_lake.clickstream (
    session_id VARCHAR(100),
    user_id VARCHAR(50),
    page_url VARCHAR(500),
    event_type VARCHAR(50),
    event_timestamp BIGINT,
    user_agent STRING
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://data-lake-analytics/raw/clickstream/';

-- Create an external table for CSV data with header
CREATE EXTERNAL TABLE analytics_lake.inventory (
    sku VARCHAR(50),
    product_name VARCHAR(200),
    warehouse_id VARCHAR(20),
    quantity_on_hand INT,
    reorder_point INT,
    last_updated TIMESTAMP
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LOCATION 's3://data-lake-analytics/staging/inventory/'
TABLE PROPERTIES (
    'skip.header.line.count'='1'
);

Managing Partitions

Partitions are critical for Spectrum performance. They allow Spectrum to skip irrelevant data during queries, reducing the amount of data scanned and lowering costs.

-- Add a partition manually
ALTER TABLE analytics_lake.sales ADD PARTITION (sale_year=2024, sale_month=6)
LOCATION 's3://data-lake-analytics/silver/sales/sale_year=2024/sale_month=6/';

-- Repair partitions after new directories are added to S3
ALTER TABLE analytics_lake.sales REPAIR PARTITION;

-- Show all partitions for a table
SHOW PARTITIONS analytics_lake.sales;

-- Drop a specific partition
ALTER TABLE analytics_lake.sales DROP PARTITION (sale_year=2024, sale_month=6);

AWS Glue Integration

AWS Glue crawlers can automatically discover schema and partitions in your S3 data, creating or updating external table definitions in the Glue Data Catalog.

import boto3

glue = boto3.client('glue')

# Create a crawler
glue.create_crawler(
    Name='sales-crawler',
    Role='arn:aws:iam::123456789012:role/GlueCrawlerRole',
    DatabaseName='production_db',
    Targets={
        'S3Targets': [
            {'Path': 's3://data-lake-analytics/silver/sales/'}
        ]
    },
    SchemaChangePolicy={
        'UpdateBehavior': 'UPDATE_IN_DATABASE',
        'DeleteBehavior': 'LOG'
    }
)

# Start the crawler
glue.start_crawler(Name='sales-crawler')

# List tables in the database
response = glue.get_tables(DatabaseName='production_db')
for table in response['TableList']:
    print(f"Table: {table['Name'
  ]}, Location: {table['StorageDescriptor']['Location'
  ]}")

Hybrid Queries

Hybrid queries combine data from Redshift internal tables with external S3 tables in a single query. This is one of Spectrum's most powerful capabilities, enabling you to join frequently accessed data (stored internally) with historical or archival data (stored in S3).

How Hybrid Queries Work

When you execute a hybrid query, Redshift's query planner splits the work. Internal table operations execute on Redshift compute nodes, while external table operations are delegated to Spectrum nodes. The results are combined at the leader node before returning to the client.

The key flow is:

1. Leader node receives the SQL query
2. Query planner identifies internal vs external tables
3. Internal table scans run on Redshift compute nodes
4. External table scans are delegated to Spectrum nodes
5. Spectrum nodes read directly from S3, apply filters
6. Results are returned to Redshift compute nodes
7. JOINs and aggregations happen on Redshift
8. Final result is returned to the client

-- Hybrid query: Join internal customer table with external sales
SELECT
    c.customer_id,
    c.customer_name,
    c.segment,
    SUM(s.total_amount) AS total_sales,
    COUNT(s.sale_id) AS transaction_count
FROM dev.customers c
JOIN analytics_lake.sales s ON c.customer_id = s.customer_id
WHERE s.sale_year = 2024 AND s.sale_month IN (1, 2, 3)
GROUP BY c.customer_id, c.customer_name, c.segment
ORDER BY total_sales DESC;

-- Hybrid query with CTEs spanning internal and external sources
WITH top_products AS (
    SELECT product_name, SUM(total_amount) AS revenue
    FROM analytics_lake.sales
    WHERE sale_year = 2024
    GROUP BY product_name
    ORDER BY revenue DESC
    LIMIT 100
)
SELECT
    tp.product_name,
    tp.revenue,
    p.category,
    p.supplier,
    p.unit_cost
FROM top_products tp
JOIN dev.products p ON tp.product_name = p.product_name;

-- Hybrid query aggregating monthly revenue
SELECT
    DATE_TRUNC('month', s.sale_date) AS month,
    c.region,
    COUNT(*) AS total_orders,
    SUM(s.total_amount) AS revenue,
    AVG(s.total_amount) AS avg_order_value
FROM analytics_lake.sales s
JOIN dev.customers c ON s.customer_id = c.customer_id
WHERE s.sale_year = 2024
  AND s.sale_month BETWEEN 1 AND 12
GROUP BY DATE_TRUNC('month', s.sale_date), c.region
ORDER BY month, revenue DESC;

Performance Considerations for Hybrid Queries

• Data movement: Redshift may need to move data between Spectrum and compute nodes. Minimize this by filtering early.
• Join strategies: Redshift uses broadcast or hash joins. Large external tables may be broadcast to all compute nodes.
• Predicate pushdown: Filters on external tables are pushed to Spectrum, reducing data scanned from S3.
• Sort key alignment: If possible, align sort keys between internal and external tables to improve join performance.

Performance Optimization

Optimizing Spectrum queries requires attention to data layout, file formats, partitioning strategies, and query patterns. Since Spectrum charges $5 per TB scanned, every optimization directly reduces cost.

File Format Selection

Choose file formats based on your query patterns. Columnar formats like Parquet and ORC are strongly recommended for analytical workloads because they support column pruning, reading only the columns your query needs.

Partition Strategy

Effective partitioning is the single most important optimization for Spectrum. It enables partition pruning, which skips entire directories of data.

-- Good: Partition by date for time-series queries
-- s3://bucket/sales/sale_year=2024/sale_month=06/sale_day=15/

-- Good: Partition by region for geographic queries
-- s3://bucket/sales/region=us-east-1/sale_year=2024/

-- Optimal partition column cardinality:
-- Date columns: year/month/day hierarchy (most common)
-- Low-cardinality columns: region, status, category
-- Avoid high-cardinality: user_id, transaction_id, order_id

Reducing Data Scanned

Several strategies minimize the volume of data Spectrum reads from S3:

1. Column pruning: Only SELECT the columns you need. Parquet and ORC support this natively.
2. Predicate pushdown: Filter early using WHERE clauses on partitioned columns.
3. Compression: Use Snappy or Zstd compression for smaller file sizes.
4. File sizing: Target 128MB to 1GB per file for optimal parallelism.
5. **Avoid SELECT ***: Always specify columns explicitly.

-- Bad: Reads all columns and all data
SELECT * FROM analytics_lake.sales;

-- Good: Reads only 3 columns with partition pruning
SELECT customer_id, total_amount, sale_date
FROM analytics_lake.sales
WHERE sale_year = 2024 AND sale_month = 6;

-- Good: Use approximate functions for large scans
SELECT APPROX_COUNT(DISTINCT customer_id)
FROM analytics_lake.sales
WHERE sale_year = 2024;

-- Good: Use LIMIT to reduce data processed
SELECT product_name, SUM(total_amount)
FROM analytics_lake.sales
WHERE sale_year = 2024 AND sale_month = 12
GROUP BY product_name
ORDER BY SUM(total_amount) DESC
LIMIT 10;

Monitoring Spectrum Costs

Track your Spectrum usage to identify optimization opportunities.

-- Query SVL_S3QUERY to see data scanned per query
SELECT
    query,
    userid,
    querytxt,
    starttime,
    endtime,
    bytes_scanned,
    lines_scanned,
    filename,
    url,
    extended_error
FROM stl_s3query
WHERE userid = 1
ORDER BY starttime DESC
LIMIT 20;

-- Calculate estimated cost ($5 per TB scanned)
SELECT
    query,
    starttime,
    ROUND(bytes_scanned / 1099511627776.0, 4) AS tb_scanned,
    ROUND(bytes_scanned / 1099511627776.0 * 5.0, 2) AS estimated_cost_usd
FROM stl_s3query
WHERE bytes_scanned > 0
ORDER BY bytes_scanned DESC
LIMIT 20;

Advanced: Spectrum and Lake Formation

AWS Lake Formation provides fine-grained access control for data stored in S3 and managed through the Glue Data Catalog. When used with Spectrum, Lake Formation enables row-level and column-level security on external tables.

-- Lake Formation grants fine-grained permissions
-- These are managed through the Lake Formation console or API

-- Query with Lake Formation enforced access control
SELECT
    customer_name,
    email,
    total_purchases
FROM analytics_lake.customer_profiles
WHERE sale_year = 2024;

-- Users without proper Lake Formation grants will get
-- AccessDeniedException even if IAM permissions allow S3 access

Best Practices Summary

Architecture Flow

Interview Q&A

Q1: What is Redshift Spectrum and when would you use it?

Answer: Redshift Spectrum is a feature of Amazon Redshift that allows you to query data directly in Amazon S3 without loading it into Redshift. It extends the Redshift query engine to access external data stored in S3 through external tables and schemas.

Use Spectrum when you have large volumes of historical data in S3 that you query infrequently, when you want to avoid the cost of loading all data into Redshift, or when you need to query data in its native format without building ETL pipelines.

Q2: How does Redshift Spectrum charge for queries?

Answer: Spectrum charges $5 per terabyte of data scanned, with a minimum of 10MB per query. There are no upfront costs or reserved capacity options for Spectrum itself (though your Redshift cluster has its own pricing). This makes data organization critical for cost control.

Q3: What is predicate pushdown and how does it affect Spectrum performance?

Answer: Predicate pushdown is the process of filtering data at the S3 storage layer before it is transferred to Redshift. When you apply WHERE clauses on external tables, Spectrum pushes those filters down to read only the relevant row groups from Parquet/ORC files. This reduces the amount of data scanned, lowering both cost and query time.

Q4: Can Spectrum join external tables with internal Redshift tables?

Answer: Yes, this is called a hybrid query. Redshift's query planner splits the work: internal table operations run on Redshift compute nodes, while external table operations are delegated to Spectrum nodes. Results are combined at the leader node. The internal table is typically broadcast to all nodes for the JOIN.

Q5: What file formats does Spectrum support?

Answer: Spectrum supports Parquet, ORC (best for columnar analytics), Avro, JSON (with SerDe), CSV, TSV, and Sequence files. Parquet and ORC are strongly recommended because they support column pruning, compression, and predicate pushdown at the file level.

Q6: How do you optimize a Spectrum query that scans too much data?

Answer: Five key strategies: (1) Use partitioned columns in WHERE clauses for partition pruning. (2) SELECT only needed columns instead of using SELECT star. (3) Store data in Parquet/ORC format. (4) Ensure files are 128MB-1GB for optimal parallelism. (5) Use APPROX functions for approximate distinct counts on large datasets.

Q7: What is the relationship between Spectrum and AWS Glue?

Answer: Spectrum uses the AWS Glue Data Catalog to discover table schemas and partition information. When you create an external schema in Redshift, it references a Glue database. The Glue catalog stores the metadata (column names, types, locations) while Spectrum reads the actual data files from S3. Glue crawlers can auto-discover and update these schemas.

Q8: What are the limitations of Redshift Spectrum?

Answer: Key limitations include: (1) Max 100 Spectrum nodes per query. (2) No write operations, only reads. (3) External tables cannot be vacuumed or analyzed. (4) Limited DDL operations on external tables. (5) Complex data types like STRUCT or ARRAY have limited support. (6) Performance depends on file format and S3 performance characteristics.

Q9: How does partition pruning work in Spectrum?

Answer: Partition pruning eliminates entire directories from being scanned. When you filter on partitioned columns (e.g., sale_year, sale_month), Spectrum identifies which S3 directories match the filter and skips all others. This is the most impactful optimization because it prevents S3 LIST and GET operations entirely for irrelevant data.

Q10: How would you design a data lake for optimal Spectrum performance?

Answer: Design principles: (1) Use Parquet with Snappy compression. (2) Partition by columns used in most queries (typically date hierarchies). (3) Target file sizes of 128MB-1GB. (4) Use consistent naming conventions for partition directories. (5) Organize data by access patterns (hot vs cold). (6) Use Glue crawlers to keep metadata current. (7) Implement Lake Formation for access control.

Summary

• Architecture: Redshift cluster connects to Spectrum layer which reads directly from S3
• Cost: $5 per TB scanned, optimize with partitioning and column pruning
• External Tables: Map S3 files to queryable schemas via Glue Data Catalog
• Hybrid Queries: Join internal Redshift tables with external S3 tables seamlessly
• File Formats: Parquet and ORC recommended for columnar access and compression
• Performance: Partition pruning, predicate pushdown, and optimal file sizing are critical
• Best Practice: Partition by date or low-cardinality columns, use Snappy compression, target 128MB-1GB files

Knowledge Check

See Also

• AWS Redshift
• Redshift Serverless
• AWS S3