Study guide
6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum
AWS Certified Data Engineer - Associate DEA-C01
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Streaming Data Ingestion
1.1 Kinesis Data Streams vs. Firehose Architecture & Partitioning 1.2 Amazon MSK (Managed Streaming for Apache Kafka) & Serverless Patterns 1.3 Event-Driven Streaming Ingestion with DynamoDB Streams & Lambda 1.4 Streaming Distribution, Replay & Processing State
2 Chapter 2: Batch Data Ingestion & Migration
2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC) 2.2 S3 Batch Operations & High-Throughput Bulk Data Ingestion 2.3 Hybrid Data Transfer with Snow Family & AWS DataSync 2.4 Application, API & Managed File Ingestion
3 Chapter 3: ETL Transformation with AWS Glue
3.1 AWS Glue Jobs, DynamicFrames & PySpark Transformations 3.2 Glue Crawlers, Data Catalog & Schema Evolution 3.3 Data Preparation & Quality Rules with AWS Glue DataBrew & Data Quality 3.4 Incremental Processing with AWS Glue Job Bookmarks & Partition Processing 3.5 Technical and Business Catalogs, Connections & Lineage
4 Chapter 4: Big Data Processing & Workflow Orchestration
4.1 Amazon EMR Cluster Architecture, Spark & Hadoop Ecosystem 4.2 EMR Serverless & Instance Selection (On-Demand vs. Spot Optimization) 4.3 Serverless Orchestration with AWS Step Functions 4.4 Airflow Pipeline Orchestration with Amazon MWAA 4.5 Containers, Lambda & Distributed Processing Performance 4.6 Data Engineering Code, IaC & CI/CD
5 Chapter 5: Object Storage & Data Lake Architecture
5.1 Amazon S3 Storage Classes, Lifecycle Policies & Cost Optimization 5.2 S3 Data Lake Partitioning, Bucketing & Layout Strategies 5.3 Efficient Columnar Formats: Parquet, ORC, and Avro Schema Design 5.4 S3 Performance Optimization, Multipart Uploads & Transfer Acceleration 5.5 Apache Iceberg, S3 Tables & Open Table Operations
6 Chapter 6: Data Warehousing with Amazon Redshift
6.1 Amazon Redshift Architecture, RA3 Nodes & Managed Storage 6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys 6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum
7 Chapter 7: NoSQL, Relational & Specialty Data Stores
7.1 Amazon DynamoDB Partition Keys, Global Secondary Indexes & Data Modeling 7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ 7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena 7.4 Vector Stores, Indexes & LLM Data Processing
8 Chapter 8: Pipeline Operations, Monitoring & Observability
8.1 Monitoring Pipeline Health with Amazon CloudWatch Metrics & Logs 8.2 Event-Driven Alerting & Automation with EventBridge & CloudWatch Alarms 8.3 Centralized Audit Logging with AWS CloudTrail
9 Chapter 9: Troubleshooting, Data Quality & Performance Tuning
9.1 Diagnosing & Resolving AWS Glue, EMR & Lambda Execution Failures 9.2 Redshift & Athena Query Performance Tuning & Memory Management 9.3 Implementing Data Quality Frameworks & Automated Validation Checks 9.4 Visualization, Notebooks, SQL Analytics, Sampling & Skew
10 Chapter 10: High Availability, Backup & Cost Optimization
10.1 Disaster Recovery, Cross-Region Replication & Backup Strategies 10.2 Designing High Availability Architectures Across AWS Data Services 10.3 Cost Optimization & Resource Utilization across Analytics Workloads
11 Chapter 11: Data Security, Encryption & Identity Management
11.1 Fine-Grained Access Control with AWS IAM Policies, Roles & Conditions 11.2 Data Encryption at Rest & in Transit with AWS KMS & S3 Bucket Policies 11.3 Secure Credentials & Secrets Management with AWS Secrets Manager & Parameter Store 11.4 Private Network Connectivity & Isolation with AWS PrivateLink & VPC Endpoints
12 Chapter 12: Data Governance, Privacy & Compliance
12.1 Centralized Data Lake Governance & Access Control with AWS Lake Formation 12.2 Automated Sensitive Data Discovery & PII Protection with Amazon Macie 12.3 Configuration Auditing & Compliance Enforcement with AWS Config 12.4 Data Privacy, Sovereignty, Sharing & Retention Guardrails 12.5 SageMaker Unified Studio Domains, Projects & Catalog Governance
6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum
Key Takeaways Explain This Topic AI
The COPY command is the fastest mechanism to ingest bulk data into Redshift because it loads S3 objects in parallel across all compute node slices simultaneously.
For parallel COPY, provide multiple similarly sized compressed files—generally 1 MB to 1 GB—and at least as many files as compute slices; exact sizing should be tested for the workload.
Manifest files formatted in JSON explicitly list S3 object URIs for the COPY command, preventing duplicate file loads and avoiding bucket scanning overhead.
The UNLOAD command extracts query results from compute slices directly to S3 in parallel, supporting optimized columnar formats such as Apache Parquet with AWS KMS encryption.
Amazon Redshift Spectrum queries petabyte-scale data directly in Amazon S3 without ingestion, delegating scan and aggregation workloads to an independent serverless Spectrum compute fleet referencing the AWS Glue Data Catalog.
Last updated: August 2026
- 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:
The Leader Node receives the COPY statement and evaluates the source destination in Amazon S3 (or DynamoDB, EMR, or remote hosts).
The Leader Node assigns specific S3 data files to individual compute node slices.
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
Query Submission : Client submits a SQL query joining a local Redshift table with an external S3 table.
Query Compilation : The Redshift Leader Node parses the query and generates an execution plan.
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.
Parallel Processing : Thousands of Spectrum compute workers scan S3 data objects concurrently.
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:
CREATE EXTERNAL SCHEMA : Connects Redshift to an AWS Glue Data Catalog database using an IAM Role.
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;