Skip to main content

Amazon Redshift

Amazon Redshift is AWS's cloud data warehouse. Move data into MotherDuck by unloading Parquet to S3, or read Redshift Spectrum tables in place through the AWS Glue Data Catalog.

The path into MotherDuck goes through S3: Redshift writes the data out with UNLOAD, and MotherDuck reads the files. There is no DuckDB extension that attaches Redshift directly.

Unload to S3 and read the files​

In Redshift, unload the table or query result to your bucket as Parquet:

UNLOAD ('SELECT * FROM public.orders')
TO 's3://my-bucket/redshift-unload/orders/'
IAM_ROLE 'arn:aws:iam::<account_id>:role/<redshift_unload_role>'
FORMAT AS PARQUET
MAXFILESIZE 256 MB
ALLOWOVERWRITE;

The IAM role needs s3:PutObject on the destination prefix. Parquet keeps the column types, so prefer it over CSV.

In MotherDuck, store the bucket credentials in a secret and read the files:

CREATE SECRET my_s3_secret IN MOTHERDUCK (
    TYPE S3,
    KEY_ID '<aws_access_key_id>',
    SECRET '<aws_secret_access_key>',
    REGION '<aws_region>'
);

CREATE TABLE orders AS
SELECT * FROM read_parquet('s3://my-bucket/redshift-unload/orders/*.parquet');

For a whole schema, script the UNLOAD per table from SVV_TABLES, then load each prefix in MotherDuck. For repeat loads, unload only new rows and append with the watermark patterns in Data loading patterns.

Read Spectrum tables in place​

If you query external tables with Redshift Spectrum, the data already sits in S3 and is registered in the AWS Glue Data Catalog. MotherDuck can read the same data without a copy:

  • For Iceberg tables, attach the Glue Data Catalog as a MotherDuck database. See AWS Glue in the Apache Iceberg page.
  • For plain Parquet or CSV prefixes, read the files directly with read_parquet or read_csv, using hive_partitioning when the prefix encodes partition columns. See S3 import best practices.

Use an ingestion tool​

Ingestion tools that list Redshift as a source can load into MotherDuck as a destination: dlt, Sling, Airbyte, and Fivetran. Use one when Redshift is one source among several and you already run the tool.

Things to know​

  • Don't attach Redshift with the postgres extension. Redshift speaks a Postgres-derived protocol, but DuckDB's PostgreSQL extension targets PostgreSQL and attaching Redshift is not a supported configuration. Route bulk reads through UNLOAD instead.
  • Distribution and sort keys don't carry over. MotherDuck has no DISTKEY or SORTKEY. Physical layout is handled for you, so drop those definitions rather than translating them. If a query is slow, see Query performance.
  • Type mapping. Redshift SUPER unloads as JSON text; cast it to DuckDB JSON or a STRUCT after loading. Redshift VARCHAR(max) becomes a plain DuckDB VARCHAR with no length limit to declare.
  • Unload region. Put the S3 bucket in the same region as your Redshift cluster to avoid cross-region transfer costs on the way out.

Related content​