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:
- For Iceberg tables, attach the Glue Data Catalog as a MotherDuck database. See the AWS Glue tab under Catalog-specific setup in the Apache Iceberg page.
- For plain Parquet or CSV prefixes, read the files directly with
read_parquetorread_csv, usinghive_partitioningwhen 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
postgresextension. Redshift speaks a Postgres-derived protocol, but DuckDB's PostgreSQL extension targets PostgreSQL and attaching Redshift is not a supported configuration. Route bulk reads throughUNLOADinstead. - Distribution and sort keys don't carry over. MotherDuck has no
DISTKEYorSORTKEY. 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
SUPERunloads as JSON text; cast it to DuckDBJSONor aSTRUCTafter loading. RedshiftVARCHAR(max)becomes a plain DuckDBVARCHARwith 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.