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.

Load on a schedule with a Flight​

A Flight is Python that MotherDuck schedules and runs next to your data. Use one instead of running the load by hand when the refresh should repeat on a cron, retry on a transient failure, and keep a run history.

Unlike an extension-based source, there is nothing to attach here: the read_parquet above already runs server-side on MotherDuck compute. So the Flight schedules the load rather than moving the rows itself, picking up whatever Redshift has unloaded to the prefix since the last run:

import duckdb


def main():
con = duckdb.connect("md:")
con.execute("CREATE DATABASE IF NOT EXISTS redshift_ingest")
con.execute(
"CREATE OR REPLACE TABLE redshift_ingest.main.orders AS "
"SELECT * FROM read_parquet('s3://my-bucket/redshift-unload/orders/*.parquet')"
)


if __name__ == "__main__":
main()

The bucket credentials are read by MotherDuck's own compute, so they belong in the TYPE S3 secret created above rather than inline in the Flight's code. A Flight needs a Flight secret only for credentials its Python reads directly, such as the Redshift password if you also trigger the UNLOAD from the Flight.

Create the Flight with MD_CREATE_FLIGHT, passing the Python above as source_code and a schedule_cron for the cadence you want. Ingest partitioned S3 Parquet on a schedule is a fuller version that refreshes only the partition that changed instead of replacing the whole table.

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:

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.