How to Copy Tables Between Redshift Databases

How do I copy a table from one Redshift database to another?

Copies a table’s schema and data from one Amazon Redshift database to another via UNLOAD/COPY, Data Sharing, or CREATE TABLE AS.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why copy a table between Redshift databases?

Copying lets you migrate data to a new cluster, refresh staging environments, or share read-only datasets without exposing the entire source database.

Which methods can I use?

Three reliable choices exist: 1) UNLOAD to Amazon S3 then COPY on the target, 2) Redshift Data Sharing for same-region clusters, and 3) CREATE TABLE AS using Redshift Spectrum or federated queries.

When is UNLOAD/COPY the best option?

Use UNLOAD/COPY when clusters are in different AWS accounts or regions, or when you need full control over file format, compression, and parallelism.

What permissions are required?

The source and target clusters need an IAM role with GetObject, PutObject, and ListBucket on the S3 bucket, plus USAGE/SELECT on the source schema.

How do I retain distribution and sort keys?

Before running COPY, create the table on the target with identical DISTKEY, SORTKEY, and ENCODE settings using SHOW CREATE TABLE output from the source.

Step-by-step UNLOAD/COPY workflow

1) UNLOAD the data to S3 in Parquet. 2) Optionally grant bucket access to the target account. 3) Recreate the table definition on the target. 4) COPY the files into the new table.

Can I copy only recent rows?

Yes. Pass a WHERE clause inside the UNLOAD query, e.g., order_date > current_date - 30.

How to monitor progress?

Check STL_UNLOAD_LOG and STL_LOAD_COMMITS views. They show file counts, row counts, and errors.

Best practices for large tables?

Unload in Parquet with MAXFILESIZE 250 MB, set COMPUPDATE OFF during COPY, and leverage manifest files to avoid missing objects.

How to automate the process?

Wrap UNLOAD and COPY commands in an AWS Step Functions workflow or a Galaxy SQL notebook scheduled by cron.

Why How to Copy Tables Between Redshift Databases is important

How to Copy Tables Between Redshift Databases Example Usage


-- Copy Orders table for last month only
UNLOAD ('SELECT * FROM public.orders WHERE order_date >= date_trunc(\'month\', current_date)')
TO 's3://acme-export/orders_'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift_s3_access'
FORMAT AS PARQUET;

CREATE TABLE public.orders LIKE public.orders;

COPY public.orders
FROM 's3://acme-export/'
IAM_ROLE 'arn:aws:iam::210987654321:role/redshift_s3_access'
FORMAT AS PARQUET;

How to Copy Tables Between Redshift Databases Syntax


-- 1. UNLOAD from source cluster
UNLOAD ('SELECT * FROM public.customers')
TO 's3://acme-export/customers_'
IAM_ROLE 'arn:aws:iam::123456789012:role/redshift_s3_access'
FORMAT AS PARQUET
MAXFILESIZE 250 MB;

-- 2. Recreate table on target cluster (keep keys)
CREATE TABLE public.customers (
    id          BIGINT   ENCODE az64,
    name        VARCHAR(255) ENCODE lzo,
    email       VARCHAR(255) ENCODE lzo,
    created_at  TIMESTAMP   ENCODE az64
)
DISTKEY(id)
SORTKEY(created_at);

-- 3. COPY on target cluster
COPY public.customers
FROM 's3://acme-export/'
IAM_ROLE 'arn:aws:iam::210987654321:role/redshift_s3_access'
FORMAT AS PARQUET;

Common Mistakes

Using CSV instead of Parquet: CSV inflates file size and slows COPY. Fix by adding "FORMAT AS PARQUET" in both UNLOAD and COPY.
Forgetting to recreate distribution/sort keys: the target table defaults to EVEN distribution, hurting performance. Fix by running SHOW CREATE TABLE and applying the same keys.

Frequently Asked Questions (FAQs)

Is Data Sharing faster than UNLOAD/COPY?

Yes when clusters are in the same region and account, because no data lands on S3. However, Data Sharing provides read-only access and cannot move data permanently.