Amazon Redshift Features: Serverless, ML, Spectrum, and Data Sharing Explained

Redshift’s core value — columnar storage and MPP for fast analytical queries — has been well established for a decade. But the features added since 2019 have transformed Redshift from a traditional data warehouse into something closer to a data platform. This article covers the features that go beyond the basics and explains when each one is worth using.

Redshift Serverless

Redshift Serverless removes the requirement to size, manage, or maintain a cluster. You define a base and maximum capacity in Redshift Processing Units (RPUs), and Redshift automatically scales within that range based on query demand.

Traditional Provisioned Cluster
  You choose: ra3.4xlarge, 4 nodes
  You pay: 24 hours/day, 7 days/week
  Utilisation at 2am: ~0%  <-- you still pay

Redshift Serverless
  You define: base 8 RPU, max 128 RPU
  Redshift scales to match workload
  You pay: per RPU-second during actual query execution
  Utilisation at 2am: idle = near-zero cost

RPU pricing: approximately $0.375 per RPU-hour. A query that uses 16 RPUs and runs for 10 seconds costs$0.375 × 16 × (10/3600) = $0.017.

When Serverless makes sense:

• Variable or unpredictable query load
• Development and test environments that are idle most of the day
• Departments that run occasional reports rather than continuous workloads

When provisioned clusters are still better:

• Predictable, continuous workloads where always-on compute is cheaper than per-second billing
• Need for specific node types (DC2 for lowest latency with local SSD)
• Workloads requiring manual WLM queue configuration

Redshift Spectrum

Spectrum lets you query data in S3 directly from Redshift without loading it into the cluster. It uses a dedicated fleet of Spectrum nodes that read S3 data in parallel and return results to the Redshift compute nodes for final aggregation.

Your Redshift Cluster
       |
       |-- Query includes reference to external table
       v
[Leader Node: generates query plan]
       |
       +-- Local data: computed by cluster nodes
       |
       +-- External table (S3): sent to Spectrum nodes
                |
                v
         [Spectrum Nodes]  <-- dedicated, separate fleet
                |
         Read Parquet/ORC/JSON from S3
                |
         Return filtered, projected data
                |
       [Cluster aggregates combined result]
       |
       v
Client receives final result

You pay $5 per TB scanned by Spectrum queries. Using columnar formats (Parquet, ORC) and partitioned data reduces the amount scanned and therefore the cost.

Practical use case: Your Redshift cluster holds 18 months of “hot” transactional data. Historical data beyond 18 months sits in S3 in Parquet format. A regulatory report needs 3 years of data. Rather than loading 18 months of historical S3 data into the cluster, write a single SQL query that unions the cluster table with an external Spectrum table pointing to the S3 data. Redshift handles the join transparently.

-- Hot data in cluster
SELECT date, revenue FROM sales
WHERE date >= DATEADD(month, -18, GETDATE())

UNION ALL

-- Historical data in S3 via Spectrum
SELECT date, revenue FROM spectrum.sales_archive
WHERE date < DATEADD(month, -18, GETDATE())
ORDER BY date;

Redshift ML

Redshift ML lets you train and deploy machine learning models using SQL. You write a CREATE MODEL statement, Redshift exports the training data to S3, invokes Amazon SageMaker Autopilot to train the model, and registers the resulting model as a SQL function in Redshift.

-- Train a model to predict customer churn
CREATE MODEL customer_churn_model
FROM (
    SELECT
        days_since_last_purchase,
        total_orders_last_90_days,
        avg_order_value,
        support_tickets_last_30_days,
        churned  -- target label
    FROM customer_features
    WHERE training_set = true
)
TARGET churned
FUNCTION predict_churn
IAM_ROLE 'arn:aws:iam::123456789:role/RedshiftMLRole'
AUTO OFF
MODEL_TYPE XGBoost
SETTINGS (S3_BUCKET 'my-redshift-ml-bucket');

Once the model is trained (which can take minutes to hours via SageMaker Autopilot), you call it as a SQL function:

SELECT
    customer_id,
    predict_churn(
        days_since_last_purchase,
        total_orders_last_90_days,
        avg_order_value,
        support_tickets_last_30_days
    ) AS churn_probability
FROM active_customers
WHERE churn_probability > 0.7;

The model runs on inference nodes, not on Redshift compute nodes, so scoring does not consume cluster capacity.

When to use Redshift ML vs SageMaker directly:

• Use Redshift ML when the data lives in Redshift and analysts who know SQL need to score records
• Use SageMaker when you need custom model architectures, complex feature engineering pipelines, or production ML serving infrastructure

Redshift Data Sharing

Data Sharing lets you share live data across Redshift clusters within the same AWS account or across accounts, without copying or moving data.

The producer cluster grants access to specific schemas or tables. Consumer clusters access them as if they were local tables — queries run in real time against the producer’s data, without ETL or synchronisation delay.

Producer Account
+------------------+
| Redshift Cluster |
| sales_db         |
|   orders table   |
|   revenue table  |
+--------+---------+
         |
    [Datashare: analytics_share]
    Grant to Consumer Account 99999
         |
         v
Consumer Account
+------------------+
| Redshift Cluster |
| (read-only view  |
|  of orders and   |
|  revenue)        |
+------------------+

The consumer does not need to provision storage for the shared data — it reads it directly from the producer’s storage. The producer pays storage costs; the consumer pays compute costs for the queries they run.

Use cases for Data Sharing:

• Central data team shares curated datasets with business units that have separate Redshift clusters
• Sharing data with external partners or customers (cross-account)
• Separating read workloads from write workloads — reader clusters query a producer without competing for its compute resources

Concurrency Scaling

Concurrency Scaling automatically adds transient compute capacity when the number of queued queries exceeds a threshold. The transient cluster starts in about 60 seconds and handles overflow queries. When the queue clears, the transient cluster is released.

Normal load: 10 concurrent queries
  --> All handled by main cluster

Sudden spike: 200 concurrent queries
  --> Some go to main cluster
  --> Overflow goes to Concurrency Scaling cluster (auto-provisioned)
  --> Both clusters serve queries simultaneously
  --> After spike: Concurrency Scaling cluster releases

Cost: 1 hour of Concurrency Scaling credit free per 24 hours of main cluster usage
       Extra usage billed at per-second rate

Concurrency Scaling is transparent to clients. WLM routes queries to the scaling cluster automatically based on the queue configuration.

Workload Management (WLM)

WLM lets you define multiple query queues with different resource allocations and routing rules. Without WLM, a single long-running query can block a dashboard refresh that takes 2 seconds to complete.

WLM Configuration Example:

Queue 1: dashboards
  Priority: HIGH
  Memory: 30%
  Concurrency: 10
  Route: user group = bi_users

Queue 2: batch_etl
  Priority: NORMAL
  Memory: 60%
  Concurrency: 3
  Route: query group = etl

Queue 3: default
  Priority: LOW
  Memory: 10%
  Concurrency: 5
  Route: everything else

Short Query Acceleration (SQA): A WLM feature that uses machine learning to predict query duration. Short queries (usually under 30 seconds) are routed to a dedicated mini-queue, bypassing the standard queues. This keeps dashboards responsive even when long ETL queries are running.

Result Caching

Redshift caches the result of every query. If an identical query is submitted and the underlying data has not changed, Redshift returns the cached result in milliseconds — no compute consumed.

The cache is per-cluster and per-user by default. Results are invalidated when:

• The underlying table data changes (inserts, updates, deletes)
• The user runs SET enable_result_cache_for_session = off
• The cached result ages out (cache is bounded by memory)

Result caching is particularly valuable for dashboards that run the same fixed-window queries on static data. A daily report that queries last week’s data will return the cached result on every refresh after the first run.

AQUA (Advanced Query Accelerator)

AQUA is hardware-level query acceleration available on ra3.4xlarge and ra3.16xlarge nodes. It uses custom AWS-designed chips and FPGA-based acceleration to push certain types of computation (especially filter, aggregation, and scan operations) closer to the storage layer.

AQUA is enabled by default on supported node types and requires no code changes. The improvement is most noticeable on scan-heavy queries against large tables. AWS claims AQUA can deliver up to 10x query performance improvement for eligible queries, though real-world results vary based on query type and data layout.

Feature Summary

Interview Notes

Q: What is the difference between Redshift Spectrum and Federated Query? Spectrum queries data in S3 (data lake). Federated Query queries live data in operational databases — Amazon Aurora, RDS PostgreSQL — without extracting it to S3 first. Use Federated Query when you need to join Redshift data with live operational data from RDS or Aurora.

Q: What are RA3 nodes, and why are they preferred over DC2? RA3 nodes separate compute from storage using S3 as the backing store. They scale storage independently from compute and support Data Sharing and Redshift Serverless. DC2 nodes use local SSD with fixed storage per node — cheaper for pure compute-intensive workloads but limited in storage scalability. RA3 is the recommended choice for new deployments.

Q: Can multiple Redshift clusters share the same data with Data Sharing? Yes, one producer can have multiple consumers. Each consumer can be in a different AWS account. Consumer clusters get read-only access to shared tables and query them live — no data is copied to the consumer cluster.