# Migrate from Redshift > Plan and run a migration from Amazon Redshift to MotherDuck, from inventorying a cluster with system views to translating Redshift SQL and cutting over. This guide covers the Redshift-specific steps of a migration: inventorying what your cluster or Serverless workgroup runs, translating Redshift SQL and features, and validating against Redshift before you cut over. It follows the process in [Migrate your data warehouse](/key-tasks/data-warehousing/migrating-your-data-warehouse/), which covers sorting the inventory, grouping it into workloads, and moving one workload at a time. Read that page first. For the mechanics of copying a single table, see the [Amazon Redshift integration page](/integrations/databases/amazon-redshift/). :::info Prerequisites - A [MotherDuck organization](/key-tasks/managing-organizations/), ideally in the same [AWS region](/about-motherduck/cloud-regions/) as your Redshift cluster, so the data you unload never leaves the region. - A Redshift superuser. Other users see only their own rows in the `SYS_` views, and `SVV_TABLE_INFO` is visible only to superusers unless you grant `SELECT` on it. - An S3 bucket and an IAM role that Redshift can `UNLOAD` to. ::: ## Inventory what Redshift runs The queries below run in Redshift. They use the `SVV_` and `SYS_` system views, which work on both provisioned clusters and Redshift Serverless. Run them in each database you're migrating. **What objects exist, and how big are they?** ```sql SELECT "database", "schema", "table", tbl_rows, size AS size_mb, diststyle, sortkey1, unsorted, stats_off FROM svv_table_info ORDER BY size DESC; ``` `SVV_TABLE_INFO` lists only tables that hold data, and `size` counts 1 MB blocks. Views and external tables are in `SVV_TABLES` and `SVV_EXTERNAL_TABLES`. Keep `diststyle` and `sortkey1`: the sort key tells you the `ORDER BY` each table needs on the MotherDuck side. **Which of them does anyone read?** ```sql SELECT table_name, COUNT(DISTINCT query_id) AS query_count, MAX(start_time) AS last_read FROM sys_query_detail WHERE step_name = 'scan' AND table_name IS NOT NULL GROUP BY table_name ORDER BY query_count DESC; ``` **Who and what connects?** ```sql SELECT u.usename AS user_name, COUNT(*) AS queries, MAX(q.start_time) AS last_query FROM sys_query_history AS q JOIN pg_user AS u ON u.usesysid = q.user_id GROUP BY u.usename ORDER BY queries DESC; ``` Pipeline users and BI tool users show up here next to people. Each one is a connection you'll have to repoint later, so treat this list as your cutover checklist. **What shape is the compute?** ```sql SELECT DATE_TRUNC('hour', start_time) AS hour, COUNT(*) AS queries, SUM(elapsed_time) / 1000000.0 / 3600 AS elapsed_hours, SUM(queue_time) / 1000000.0 / 3600 AS queued_hours FROM sys_query_history GROUP BY 1 ORDER BY hour; ``` Elapsed time that comes in two spikes a day is a batch pipeline. Elapsed time spread evenly through business hours is serving dashboards. The two need different Duckling sizing on the other side, so it's worth knowing which is which before you size anything. Queue time is the WLM backlog: hours where it's high are where concurrency scaling or Serverless capacity was doing work, and where you'll want separate Ducklings. On Serverless, `SYS_SERVERLESS_USAGE` gives the billed RPU seconds per minute in `charged_seconds`. **What runs that isn't a query?** ```sql SELECT n.nspname AS schema_name, p.proname AS routine_name, l.lanname AS language FROM pg_proc AS p JOIN pg_namespace AS n ON n.oid = p.pronamespace JOIN pg_language AS l ON l.oid = p.prolang WHERE n.nspname NOT IN ('pg_catalog', 'information_schema') ORDER BY language, schema_name; ``` `plpgsql` rows are stored procedures, `plpythonu` rows are Python UDFs, `exfunc` rows are Lambda UDFs, and `sql` rows are SQL UDFs. All but the SQL UDFs need rewriting, so count them early. Then list what else is attached to the cluster: | System view | What it lists | |---|---| | `SVV_MV_INFO` | Materialized views, including the ones that do streaming ingestion | | `SVV_EXTERNAL_TABLES` | Redshift Spectrum tables, which already live in S3 | | `SVV_DATASHARES` | Datashares you produce and consume | | `SVV_INTEGRATION` | Zero-ETL integrations that write into the cluster | Scheduled queries aren't in the system views. Query Editor v2 stores each schedule as an Amazon EventBridge rule whose name starts with `QS2-`, and keeps only 24 hours of run history, so list the rules with `aws events list-rules --name-prefix QS2-` in each region. :::note Redshift keeps about a week of query history: the `STL_` views and `SYS_SERVERLESS_USAGE` keep seven days. A weekly job can fall out of the window before you see it, and a monthly one almost certainly will. Copy the history into a table every day from the start of the migration, or turn on [audit logging](https://docs.aws.amazon.com/redshift/latest/mgmt/db-auditing.html) to S3 and read the user activity log, which keeps whatever your bucket keeps. ::: With the inventory in hand, [sort it into four buckets](/key-tasks/data-warehousing/migrating-your-data-warehouse/#sort-the-inventory-into-four-buckets) and [group it into workloads](/key-tasks/data-warehousing/migrating-your-data-warehouse/#group-whats-left-into-independent-workloads). ### Ingestion that only writes to Redshift Most Redshift ingestion already passes through S3, which makes it the easiest part to move: - **`COPY` and auto-copy jobs** read files from S3. MotherDuck reads the same prefix with `read_parquet` or `read_csv`, so point a [scheduled Flight](/cookbook/flight-scheduled-s3-ingest/) at it and stop the `COPY`. - **Spectrum tables** are already in S3 and registered in a data catalog, usually AWS Glue. Query them in place, as described on the [Redshift integration page](/integrations/databases/amazon-redshift/#read-spectrum-tables-in-place). - **Zero-ETL integrations** replicate Aurora, RDS, DynamoDB, and applications such as Salesforce, SAP, ServiceNow, and Zendesk into Redshift and nowhere else. For Aurora and RDS for PostgreSQL or MySQL, attach the source database with the PostgreSQL or MySQL extension from a [Flight](/cookbook/flight-postgres-ingest/). For DynamoDB, export the table to S3 and read the export. Applications need a replacement connector, such as [dlt](/integrations/ingestion/dlt/) or [Fivetran](/integrations/ingestion/fivetran/). - **Streaming ingestion** from Kinesis Data Streams or Amazon MSK lands in a materialized view. Send the stream to S3 with Amazon Data Firehose instead, and read the files. To copy the data that has to move, `UNLOAD` it to S3 as Parquet and read it from MotherDuck. The [integration page](/integrations/databases/amazon-redshift/#unload-to-s3-and-read-the-files) has the commands. With the bucket and your MotherDuck organization in the same region, nothing in the path pays cross-region transfer. ## Three habits to unlearn Redshift and MotherDuck organize data and compute differently, and the mistakes below come from carrying the Redshift model across unchanged. Keep them in mind when you [set up the infrastructure](/key-tasks/data-warehousing/migrating-your-data-warehouse/#set-up-the-infrastructure-for-migration). **A Redshift schema is often a MotherDuck database.** Redshift names a table `database.schema.table` and so does MotherDuck, so a one-to-one mapping looks natural. It's usually wrong. Most Redshift clusters keep everything in one database and separate teams and pipeline stages by schema, with grants per schema. In MotherDuck the database is the unit you share, and it takes one writer at a time. Map each schema with its own audience or its own writing pipeline to its own database. Schemas written by the same pipeline stage and read by the same people can stay schemas inside one database. **There's no superuser who can see everything.** In Redshift, a superuser can read every table and reassign ownership with `ALTER TABLE ... OWNER TO`. In MotherDuck a database belongs to the user or service account that created it, nobody else can see it until you share it, and ownership can't be transferred. A colleague querying an unshared database gets `Catalog Error: schema "x" does not exist`, which reads like a typo rather than a permissions problem. Create production databases with a service account, and publish them with a [share](/key-tasks/sharing-data/): ```sql CREATE SHARE IF NOT EXISTS models FROM models (ACCESS RESTRICTED); GRANT READ ON SHARE models TO ROLE explorer; ``` Granting to `explorer` reaches every preset role, because they're concentric. Grant to a [custom role](/concepts/roles-and-access-control/#custom-roles) instead when only one team should see it. **Concurrency comes from more databases, not more WLM slots.** Redshift absorbs concurrent writers in one cluster, queues the overflow in WLM, and adds concurrency scaling clusters when the queue grows. In MotherDuck each database takes one writer at a time and compute belongs to the account that runs the query. A pipeline fanning 20 concurrent writers at a single database (Dagster, Airflow, and Kinesis consumers all do this) needs a database per pipeline, or writes serialized into one. For the same reason, dev and production dbt targets must never point at the same database. :::tip Budget time after the port, not only for it. A direct translation of Redshift SQL runs on MotherDuck, but it may not run efficiently. See [Tune a migrated Redshift workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-redshift/tuning-a-migrated-redshift-workload/) for the workload patterns that need optimization. ::: ## Translate the SQL dialect Redshift SQL descends from PostgreSQL, and so does much of DuckDB's syntax, so most queries run unchanged. The differences below either fail loudly or, worse, run and return a different answer. | Redshift | In MotherDuck | |---|---| | Integer division | `7 / 2` returns `3` in Redshift and `3.5` in DuckDB, where `/` always returns a `DOUBLE`. Every ratio, bucket, and percentage built on integer division changes without an error. Use `//` where the model relies on truncation. | | `DISTKEY`, `SORTKEY`, `DISTSTYLE`, `ENCODE`, `BACKUP NO` | Delete them from the DDL. Put the sort key in the `ORDER BY` of the load instead, because row-group statistics do the job of zone maps. See [sort tables by common filter columns](/key-tasks/query-performance/#sort-tables-by-common-filter-columns). | | `PRIMARY KEY`, `UNIQUE`, and `FOREIGN KEY` | Redshift treats these as hints and doesn't enforce them, so its tables often hold rows that violate them. DuckDB enforces them, and the load fails with `PRIMARY KEY or UNIQUE constraint violation`. Drop the constraints from the DDL, and test for duplicates instead. | | `VARCHAR(n)` | Redshift counts `n` in bytes, so multi-byte UTF-8 text fits fewer characters than the declaration suggests. DuckDB ignores the length entirely and never truncates. Declare plain `VARCHAR`. | | Trailing spaces in comparisons | Redshift ignores trailing blanks when it compares `CHAR` and `VARCHAR` values, so `'a ' = 'a'` is true. In DuckDB it's false, and joins on padded keys silently drop rows. `trim` the keys during the load. | | `TIMESTAMPTZ` columns in an `UNLOAD` | Parquet unloads keep the timestamp value but drop the timezone, so the column arrives as a plain `TIMESTAMP` holding UTC. Cast it back during the load with `ts AT TIME ZONE 'UTC'`. | | `IDENTITY(seed, step)` | A sequence and a default: `CREATE SEQUENCE order_id_seq;` then `order_id BIGINT DEFAULT nextval('order_id_seq')`. Start the sequence above the highest key you've loaded. | | `GETDATE()`, `SYSDATE` | `current_timestamp` or `now()`. Redshift sessions default to UTC. DuckDB evaluates these in the client's `TimeZone` setting, so the same model can return a different date from a laptop than from a scheduled job. Pass the timezone explicitly in the models. | | `CONVERT_TIMEZONE('UTC', 'Europe/Amsterdam', ts)` | `(ts AT TIME ZONE 'UTC') AT TIME ZONE 'Europe/Amsterdam'`. `TIMESTAMP` and `TIMESTAMPTZ` mean the same thing in both engines, so the column types carry over. | | `DATEADD(day, 1, d)` | `d + INTERVAL 1 DAY`. On a `DATE` input DuckDB returns a `TIMESTAMP`, so cast back to `DATE` where the model expects one. | | `DATEDIFF(day, start, end)` | `date_diff('day', start, end)`. Same argument order, and both count boundaries crossed, so only the quotes around the part change. | | `NVL`, `NVL2`, `DECODE` | `coalesce` and `CASE`. DuckDB has no `NVL`. Its `decode` is a different function that converts a `BLOB` to text, so the error message doesn't point at the real problem. | | `LISTAGG(x, ',') WITHIN GROUP (ORDER BY y)` | `string_agg(x, ',' ORDER BY y)`. DuckDB has `listagg` but rejects the `WITHIN GROUP` form. | | `APPROXIMATE COUNT(DISTINCT x)` | `approx_count_distinct(x)`. | | `SELECT TOP 10` | `SELECT ... LIMIT 10`. | | `SIMILAR TO` | DuckDB treats the pattern as a regular expression, so `%` and `_` are literal characters. `'abc' SIMILAR TO 'a%'` is true in Redshift and false in DuckDB, with no error. Rewrite as `LIKE` or `regexp_full_match`. | | `REGEXP_SUBSTR`, `CHARINDEX` | `regexp_extract` and `strpos`. | | `JSON_EXTRACT_PATH_TEXT(j, 'a', 'b')` | `json_extract_string(j, '$.a.b')`. DuckDB's `json_extract_path_text` takes a single path, so the multi-key form fails. | | `SUPER` and PartiQL | `SUPER` unloads to Parquet as JSON. Cast it to `JSON`, or to a `STRUCT` or `LIST` when the shape is fixed. PartiQL arrays are 0-based and DuckDB lists are 1-based, so `items[0]` becomes `items[1]`. DuckDB returns `NULL` for `items[0]` rather than an error. | | `GEOMETRY`, `GEOGRAPHY` | `GEOMETRY` from the [`spatial` extension](/concepts/duckdb-extensions/). `GEOMETRY` can't be unloaded to Parquet, so unload it as text or CSV and parse it on load. | | `VARBYTE` | `BLOB`. Like `GEOMETRY`, it unloads only to text or CSV. | | `HLLSKETCH` | No equivalent, and it can't be unloaded to Parquet. Recompute the distinct counts from the base rows with `approx_count_distinct`. | | `CREATE VIEW ... WITH NO SCHEMA BINDING` | Remove the clause. DuckDB doesn't accept it, and views re-resolve their tables at query time anyway. | ## Translate the Redshift-specific features | Redshift | In MotherDuck | |---|---| | Spectrum external tables | Read the S3 files in place with `read_parquet`, or attach the AWS Glue Data Catalog for Iceberg tables. See [read Spectrum tables in place](/integrations/databases/amazon-redshift/#read-spectrum-tables-in-place). | | Federated queries to RDS and Aurora | The PostgreSQL and MySQL extensions, from a DuckDB client or a [Flight](/cookbook/flight-postgres-ingest/). See [loading data from PostgreSQL or MySQL](/key-tasks/loading-data-into-motherduck/loading-data-from-postgres/). | | Materialized views | No equivalent. Build a dbt table or incremental model and refresh it on a schedule. | | Scheduled queries | A [Flight](/concepts/flights/) on a cron schedule, or an external orchestrator for pipelines with cross-system dependencies. | | Stored procedures | DuckDB has no procedural SQL: no PL/pgSQL, loops, or dynamic SQL. Move the logic to Python in a [Flight](/key-tasks/flights/), which runs any PyPI package next to your data. | | SQL UDFs | [`CREATE MACRO`](/sql-reference/duckdb-sql-reference/duckdb-statements/create-macro/). Redshift SQL UDFs refer to arguments as `$1`, `$2`; macros use named parameters. | | Python UDFs and Lambda UDFs | No equivalent in SQL. Rewrite them in SQL where the logic allows, or run them as Python in a Flight. Redshift ended support for Python UDFs on June 30, 2026, so any still in your inventory are already broken. | | Redshift ML | No equivalent for training models in SQL. Train in a Flight. For LLM calls through Bedrock external models, use [`prompt()`](/sql-reference/motherduck-sql-reference/ai-functions/prompt/). | | Datashares | [Shares](/key-tasks/sharing-data/), which give read-only access to a database. A share reaches MotherDuck users, so a consumer who needs the data in their own Redshift cluster needs a file-based handoff instead. | | AWS Data Exchange datashares | No equivalent. Re-source the data from the provider directly. Inventory these during assessment: they're nobody's model, so they're easy to miss until something breaks. | | Automated and manual snapshots | [Database snapshots](/concepts/snapshots/) and [UNDROP](/concepts/data-recovery/) cover point-in-time restore. Retention is set by plan, and only the Business plan can change `snapshot_retention_days`, so check the window you land on matches your cluster's snapshot retention. | | Redshift Data API | The [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/) for applications that speak the Postgres wire protocol, or a DuckDB client library. | | Node types, WLM queues, concurrency scaling, and Serverless RPUs | Compute is per account, not per cluster. Size each service account's [Duckling](/about-motherduck/billing/duckling-sizes/) for the job it runs, and use [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) for concurrent dashboard traffic. | ### Governance features without an equivalent Four Redshift governance features have no MotherDuck counterpart: **row-level security policies**, **dynamic data masking**, **column-level privileges**, and **Lake Formation permissions** on Spectrum tables. List what you have with `SVV_RLS_POLICY` and `SVV_MASKING_POLICY`. Access in MotherDuck is granted per Share rather than per object. [Table-level security](/key-tasks/sharing-data/table-level-security/) narrows a Share to named tables and views with an include pattern, on the Business and Enterprise plans, so the usual replacement for a row or column policy is a shape change rather than a setting: publish a Share per audience from a source that already excludes what that audience shouldn't see, or maintain a masked copy of the source database and share that. [Environment management](/key-tasks/data-warehousing/environment-management/) describes the masked-copy pattern for regulated data. Row-level security and column-level permissions on Shares are on our roadmap, so check where they've got to before you design around their absence. :::warning Surface this during assessment, not at cutover. Where masking or row-level security policies back a compliance commitment, the replacement is a data-modelling change with a review attached, and that's weeks of lead time rather than a configuration change on the day. ::: ## Validate against Redshift before you cut over Run the aggregate comparison and anti-join from [Validate before you cut over](/key-tasks/data-warehousing/migrating-your-data-warehouse/#validate-before-you-cut-over). Three Redshift-specific differences cause most mismatches: - Integer division that truncated in Redshift now returns fractions, which shifts every sum and bucket built on it. - `COUNT(*)` and `COUNT(DISTINCT key)` disagree on a table Redshift declared with a primary key. Redshift never enforced the key, so the duplicates were already there. Decide whether the model should keep them before you call it a mismatch. - A `GETDATE()` that ran in UTC on Redshift now runs in the client's timezone, or a `TIMESTAMPTZ` column lost its timezone in the `UNLOAD` and was read as local time. Either lands rows on a different date near midnight. Check these and the source watermark on both sides before you go looking for a logic bug. ## Cut over and decommission Follow the [cutover steps](/key-tasks/data-warehousing/migrating-your-data-warehouse/#cut-over-and-decommission). On the Redshift side: - Freeze the workload by disabling the scheduled queries, auto-copy jobs, and stored procedure schedules that write to it. - On a provisioned cluster, pause the cluster rather than deleting it. Compute billing stops, storage keeps billing, and you can resume if something surfaces. Serverless bills compute only while queries run, so lower the workgroup's maximum capacity once nothing should be running against it. - Take a final manual snapshot before you delete the cluster or workgroup. Delete only after the parallel window closes and the query history you've been copying shows nothing reading the tables. ## Rethink what Redshift shaped Once a workload runs on MotherDuck, some of its shape was dictated by Redshift rather than by the problem: - Tables denormalized, or duplicated with a second distribution key, to avoid redistributing rows during a join. - `VACUUM`, `ANALYZE`, and deep-copy jobs that kept sort order and statistics fresh. - WLM queue design that kept dashboards and ETL from starving each other. Separate service accounts with their own Ducklings do this without a queue. - Cold data moved to Spectrum to save cluster storage. MotherDuck reads S3 directly, so the hot-or-cold decision can wait until a query proves it matters. Hold these until after the workload is validated. Changing the logic and the platform in the same step means a mismatch has two possible causes. ## Related - [Tune a migrated Redshift workload](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-redshift/tuning-a-migrated-redshift-workload/) for what to fix once the SQL runs - [Amazon Redshift integration](/integrations/databases/amazon-redshift/) for the `UNLOAD` mechanics - [Ingest partitioned S3 Parquet on a schedule](/cookbook/flight-scheduled-s3-ingest/) - [Migrate your data warehouse](/key-tasks/data-warehousing/migrating-your-data-warehouse/) for the source-independent process - [Environment management](/key-tasks/data-warehousing/environment-management/) --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fdata-warehousing%2Fmigrating-your-data-warehouse%2Fmigrating-from-redshift%2F&page_title=Migrate%20from%20Redshift&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.