6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum

6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum

High-Throughput Bulk Data Ingestion with the COPY Command

Executing individual SQL INSERT statements is a severe anti-pattern in Amazon Redshift. Single INSERT queries route through the Leader Node, incur individual transaction commit overhead, and process sequentially on a single thread. For bulk data ingestion, Redshift provides the highly optimized COPY command.

Parallel Ingestion Mechanics

The COPY command leverages Redshift's Massively Parallel Processing (MPP) architecture. When a COPY command executes:

1. The Leader Node receives the COPY statement and evaluates the source destination in Amazon S3 (or DynamoDB, EMR, or remote hosts).
2. The Leader Node assigns specific S3 data files to individual compute node slices.
3. Every compute slice opens an independent, concurrent HTTP connection to Amazon S3 and ingests its assigned data file directly into local storage blocks in parallel.

S3 File 01 ---> Compute Node 1 (Slice 0)
S3 File 02 ---> Compute Node 1 (Slice 1)
S3 File 03 ---> Compute Node 2 (Slice 2)
S3 File 04 ---> Compute Node 2 (Slice 3)

File Splitting Rule & Performance Optimization

To achieve maximum ingestion throughput, you must optimize how source data files are partitioned in Amazon S3:

• Parallel file rule: For formats that Redshift cannot split, provide at least enough similarly sized files to keep the slices busy; using a multiple of the slice count is a balancing heuristic, not a correctness requirement. Uncompressed CSV and columnar files of at least 128 MB can be split automatically.
• File Size Sweet Spot: Individual compressed files should be similarly sized; hundreds of megabytes to about 1 GB is a practical starting range to benchmark.
• Anti-Pattern Warning: A single non-splittable file, such as a GZIP-compressed CSV, forces a serialized load. Redshift can automatically split sufficiently large uncompressed CSV, Parquet, and ORC files, but multiple balanced files are still useful for parallel, resilient ingestion.

Manifest Files

When specifying an S3 object path prefix in a COPY statement (e.g., s3://my-bucket/data/sales), Redshift attempts to load all objects matching that prefix. This can lead to loading unwanted temporary files or duplicate records. To guarantee deterministic data loading, use a Manifest File.

A manifest file is a JSON-formatted document that explicitly lists the precise S3 URIs to be ingested, along with mandatory flags:

{
  "entries": [
    {"url":"s3://analytics-bucket-prod/2026/08/sales_part_01.parquet", "mandatory":true},
    {"url":"s3://analytics-bucket-prod/2026/08/sales_part_02.parquet", "mandatory":true}
  ]
}

Essential COPY Command Options

• Authorization: Use IAM roles (iam_role 'arn:aws:iam::123456789012:role/RedshiftLoadRole') rather than embedding hardcoded AWS access keys.
• Data Formats: Supports PARQUET, ORC, CSV, JSON (with jsonpath mapping expressions), and AVRO.
• Compression: Automatically decompresses GZIP, BZIP2, and ZSTD files.
• COMPUPDATE ON: Automatically analyzes sample input records to select optimal column compression encodings (e.g., AZ64, ZSTD) for new tables.
• Error Logging (MAXERROR): Specifies the number of bad records allowed before the COPY transaction aborts. Errors are logged in the STL_LOAD_ERRORS system table.

---

High-Speed Data Extraction with the UNLOAD Command

When you need to export query results from Amazon Redshift to Amazon S3 for downstream consumption by data lakes, Machine Learning pipelines, or external teams, use the UNLOAD command.

Parallel Export Architecture

Just like COPY, UNLOAD operates in parallel across all compute node slices. Each slice writes its assigned query result rows directly to S3 objects concurrently, resulting in file outputs named with slice suffixes (e.g., sales_export_part_0000_..., sales_export_part_0001_...).

Key UNLOAD Parameters

• FORMAT AS PARQUET: Exports data in Apache Parquet columnar format, dramatically improving downstream query performance in Amazon Athena or AWS Glue.
• MANIFEST: Automatically generates a manifest file in S3 listing all exported files.
• KMS_KEY_ID: Encrypts exported S3 objects using AWS Key Management Service (KMS) customer managed keys.
• MAXFILESIZE: Controls the maximum size of individual exported S3 files (e.g., MAXFILESIZE 500 MB).
• PARALLEL OFF: Writes output serially to one or more files rather than producing files per slice. A single file is not guaranteed because Redshift creates additional files when the output reaches its file-size limit.

---

Amazon Redshift Spectrum Architecture & Data Lake Federation

What is Redshift Spectrum?

Traditional data warehousing requires ingesting all analytical data into local warehouse storage prior to querying. Amazon Redshift Spectrum is a feature of Redshift that allows you to execute SQL queries directly against petabytes of unstructured, semi-structured, or structured data stored in Amazon S3—without loading data into Redshift tables.

Redshift SQL Query ---> Leader Node ---> Compute Nodes ---> [ Redshift Spectrum Serverless Fleet ] ---> S3 Data Lake
                                                                 (Thousands of EC2 Instances)

Redshift Spectrum Architecture & Query Flow

1. Query Submission: Client submits a SQL query joining a local Redshift table with an external S3 table.
2. Query Compilation: The Redshift Leader Node parses the query and generates an execution plan.
3. Delegation to Spectrum Fleet: Compute nodes delegate S3 file scanning, predicate evaluation (WHERE), header parsing, and aggregations to an independent, serverless Redshift Spectrum Compute Fleet managed by AWS.
4. Parallel Processing: Thousands of Spectrum compute workers scan S3 data objects concurrently.
5. Data Streaming & Final Join: Spectrum workers stream only the filtered, reduced intermediate dataset back to Redshift compute nodes. Redshift compute nodes perform final joins with local cluster tables and return results to the client.

AWS Glue Data Catalog Integration

Redshift Spectrum relies on external table definitions stored in an external catalog—typically the AWS Glue Data Catalog or an Apache Hive metastore.

To query S3 data via Spectrum, data engineers execute two DDL steps:

1. CREATE EXTERNAL SCHEMA: Connects Redshift to an AWS Glue Data Catalog database using an IAM Role.
2. CREATE EXTERNAL TABLE: Defines table schema, column data types, file formats (Parquet/ORC/CSV), S3 bucket location, and partition keys.

Performance & Cost Optimization for Redshift Spectrum

Redshift Spectrum is billed based on data volume scanned from S3 (using bytes-scanned pricing that varies by Region and purchase model). To minimize costs and maximize query speed:

• Use Columnar Formats: Store S3 data in Apache Parquet or Apache ORC. Columnar formats allow Spectrum to read only the specific columns referenced in the query, skipping up to 99% of raw file bytes.
• Implement Partition Pruning: Partition S3 data paths by date or category (e.g., s3://my-lake/orders/year=2026/month=08/). Define partition columns in CREATE EXTERNAL TABLE using PARTITIONED BY. Queries containing WHERE year = '2026' skip scanning all non-matching S3 directory prefixes.
• Use Compression: Compress S3 files with Snappy or GZIP.

---

Federated Queries and Materialized Views

Do not confuse three Redshift access patterns. COPY persists source data in Redshift tables for repeated warehouse processing. Spectrum queries external tables whose files remain in S3. A Redshift federated query creates an external schema over supported Amazon RDS or Aurora PostgreSQL/MySQL databases and reads live relational data without first copying it into Redshift. Configure credentials, IAM, and network reachability, select only needed columns, and filter early so predicate pushdown can reduce remote work. Federated reads are read-only from Redshift's perspective, and broad or long-running queries can consume source-database resources and hold remote transactions or locks.

A materialized view stores the result of a query and can accelerate repeated dashboards or joins over local, Spectrum, or eligible federated sources. The stored result is a snapshot: it is stale until refreshed, and automatic or incremental refresh eligibility depends on the definition and workload. Monitor refresh status and source load. Use federation for selective access to current operational rows, a materialized view when bounded staleness is acceptable for repeated reads, and COPY when data should be isolated and optimized as warehouse history.

Code Example: Ingestion, Extraction & Spectrum DDL

-- 1. High-Performance COPY Command using Manifest & Parquet
COPY fact_sales_staging
FROM 's3://analytics-ingest-bucket/manifests/2026-08-13-load.manifest'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftLoadRole'
FORMAT AS PARQUET
MANIFEST
STATUPDATE ON;

-- 2. Parallel UNLOAD to S3 in Encrypted Parquet Format
UNLOAD ('SELECT customer_id, SUM(total_amount) AS lifetime_value FROM fact_online_sales GROUP BY customer_id')
TO 's3://analytics-export-bucket/customer_lTV/'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftUnloadRole'
FORMAT AS PARQUET
KMS_KEY_ID 'arn:aws:kms:us-east-1:123456789012:key/abc-123-def'
MANIFEST
MAXFILESIZE 256 MB;

-- 3. Redshift Spectrum External Schema & Table DDL
CREATE EXTERNAL SCHEMA external_lake_schema
FROM DATA CATALOG 
DATABASE 'glue_analytics_db'
IAM_ROLE 'arn:aws:iam::123456789012:role/RedshiftSpectrumRole';

CREATE EXTERNAL TABLE external_lake_schema.ext_clickstream_logs (
    user_id         VARCHAR(100),
    event_name      VARCHAR(50),
    page_url        VARCHAR(255),
    timestamp_epoch BIGINT
)
PARTITIONED BY (year VARCHAR(4), month VARCHAR(2))
STORED AS PARQUET
LOCATION 's3://my-enterprise-datalake/clickstream/';

-- Synchronize Glue Data Catalog Partitions
ALTER TABLE external_lake_schema.ext_clickstream_logs RECOVER PARTITIONS;