# Tune a migrated Redshift workload > Fix the query and dbt patterns that run on MotherDuck but run badly after a direct port from Redshift, from distribution-key joins to staging-table upserts to dbt threads. A direct port of Redshift SQL runs on MotherDuck. It doesn't run *optimally*. Redshift spreads a query across the slices of every node in the cluster, and its distribution keys put matching join rows on the same slice before the query starts. On a single Duckling there are no slices to spread across, so a join that Redshift's layout made cheap shows up as memory pressure and disk spills. This page covers the patterns that cause it, in rough order of how much damage they do. Work through it after the SQL translates and the output validates, not during the port. Changing logic and platform in the same step means a wrong number has two possible causes. :::info Prerequisites - A workload already running on MotherDuck, with output you've checked against Redshift. See [Migrate from Redshift](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-redshift/). - [`EXPLAIN ANALYZE`](/key-tasks/query-performance/#using-explain-analyze-for-runtime-metrics) output for the models you're investigating, or [`QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) rows for them. ::: ## Start by checking it's the query's fault A model that fails with an out-of-memory error *within seconds, having spilled nothing to disk* was almost certainly starved by another job running at the same time. Re-run it on its own before you rewrite anything. Rewriting a query that was never the problem is the most common wasted day in a migration. If it does spill and then fail, it's the query. Keep reading. ## Sort keys become sort order on insert Redshift skips blocks with zone maps: min and max values per 1 MB block, ordered by the table's sort key. DuckDB keeps the same min and max statistics per row group, so the mechanism carries over. The declaration doesn't. There's no `SORTKEY` clause, so the order has to come from the `ORDER BY` of whatever writes the table. - **Put the sort key in the load.** Write each table with `ORDER BY` on the column its queries filter on most, usually the first column of the old compound sort key. See [sort tables by common filter columns](/key-tasks/query-performance/#sort-tables-by-common-filter-columns). - **Pick one column for an interleaved sort key.** DuckDB has no interleaved order. Sort by the column with the most selective common filter, and check the others with `EXPLAIN ANALYZE` before you add anything. - **Re-sort what you append to.** `VACUUM` re-sorted the unsorted region of a Redshift table in the background. Nothing re-sorts a DuckDB table after the fact, so a table that receives out-of-order appends gradually loses its pruning. Rebuild it periodically with `CREATE OR REPLACE TABLE ... AS SELECT * FROM ... ORDER BY ...`, or make sure each append arrives in order. Delete the `VACUUM`, `ANALYZE`, and deep-copy jobs once the tables they maintained have moved. They have no counterpart to run. ## Joins that explode Redshift's distribution keys co-locate the rows a join needs, and `DISTSTYLE ALL` copies small dimension tables to every node. A single [Duckling](/concepts/hypertenancy/) can't brute-force its way through a billion-row intermediate result the same way, and DuckDB's optimizer often misjudges how many rows a join whose condition isn't a simple equality will produce. **Check key uniqueness on every join you port.** Redshift doesn't enforce primary key or unique constraints, so a table declared with a primary key can still hold duplicate keys, and many do. Joining to it to look up one row silently duplicates rows, which produces wrong revenue numbers rather than a crash. Check before you trust the declaration: ```sql SELECT customer_id, order_date, count(*) FROM raw.orders GROUP BY ALL HAVING count(*) > 1 LIMIT 10; ``` Where the duplicates are real, deduplicate once in a staging model with `QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1`, and join to that. Use `EXISTS` where the join only filters rows. **Replace self-joins for rolling windows with window functions.** A self-join on `customer_id` filtered to a 14-day lookback builds every possible pair of rows and then throws most of them away. Redshift absorbed this when `customer_id` was the distribution key. DuckDB doesn't: ```sql -- Builds every pair, then filters SELECT a.customer_id, a.d, sum(b.amount) AS amount_14d FROM orders a JOIN orders b ON b.customer_id = a.customer_id AND b.d BETWEEN a.d - 14 AND a.d GROUP BY ALL; -- Same answer, one pass SELECT customer_id, d, sum(amount) OVER ( PARTITION BY customer_id ORDER BY d RANGE BETWEEN INTERVAL 14 DAY PRECEDING AND CURRENT ROW ) AS amount_14d FROM orders; ``` **Use `ASOF JOIN` for latest-match lookups.** A `LEFT JOIN ... AND b.ts >= a.ts` followed by `QUALIFY row_number() = 1` multiplies rows before discarding the duplicates. `ASOF LEFT JOIN` gets there without the intermediate blowup: ```sql SELECT e.event_id, e.ts, p.price FROM events e ASOF LEFT JOIN prices p ON p.symbol = e.symbol AND p.ts >= e.ts; ``` The two handle ties differently, so check the result. **Filter and aggregate before joining back.** Aggregate child tables by key before joining them to the parent, and put filters every downstream aggregate needs in the base CTE rather than the final `WHERE`. Where several layers join the same large table again, add the columns to the lowest-level model once, or do the join in a `MATERIALIZED` CTE. **Check your `UNION`s.** `UNION` deduplicates, which costs a full hash of every row. Where the inputs are already disjoint, `UNION ALL` is both correct and much cheaper. ## Staging-table upserts Redshift code written before `MERGE` arrived upserts in a stored procedure: create a temp table, `COPY` or `INSERT` into it, `DELETE` the matching rows from the target, `INSERT` the new ones, and wrap it all in a transaction. It ports to DuckDB as-is, but it writes the batch twice and deletes rows the next statement puts back. Use `MERGE INTO`: ```sql MERGE INTO analytics.main.customers AS t USING staging.main.customers_batch AS s ON t.customer_id = s.customer_id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *; ``` In dbt, the same pattern is an incremental model with `incremental_strategy='merge'`. ## SUPER and JSON `SUPER` columns arrive as JSON after a Parquet `UNLOAD`. PartiQL navigation like `payload.items[0].sku` becomes a `json_extract_string` call, and DuckDB parses the text on every read. Filters on JSON can't skip row groups either, so the scan reads everything. - **Flatten once, store typed columns.** Extract the fields you use, and every field you filter on, into a table with typed columns, and point the downstream models at it. - **Unnest arrays once.** A PartiQL `FROM t, t.payload.items AS i` becomes `unnest`. Do it in one staging model rather than in every model that needs the items. - **Don't carry payloads you don't need.** Split high-volume event types into their own models. A general-purpose events model is frequently dominated, in both rows and bytes, by one event type that one downstream model reads. ## Timezone conversion in shared views Redshift code converts timezones inline with `CONVERT_TIMEZONE`, often in a shared view. In DuckDB the conversion is a per-row function call, and in a view every downstream query pays it on every row before any filter applies. - Convert the filter instead of the column. Rewrite a filter on a local-date expression as a range on the stored `TIMESTAMPTZ` column, computed from the local day's boundaries once. - Where local-time columns are genuinely needed downstream, materialize them once instead of computing them in a view. ## dbt patterns worth auditing **Remove Redshift-only configs.** `dist`, `sort`, `sort_type`, `bind`, `backup`, and `auto_refresh` do nothing on MotherDuck. Delete them so nobody assumes they still apply, and put the sort order in the model's SQL instead. **Incremental strategies carry over.** dbt-redshift and dbt-duckdb both support `append`, `delete+insert`, `merge`, and `microbatch`, so incremental models keep their strategy. Add an `ORDER BY` on the sort column to the model's SQL, because the old `sort` config did that job. **Incremental models that aren't.** Models configured as incremental frequently rebuild in full anyway: no `is_incremental()` filter, or a filter that Redshift's zone maps made cheap enough that nobody noticed. Add a real filter on a load timestamp with a lookback window. Sources that carry a load-timestamp column, such as `_fivetran_synced` or `_sdc_batched_at`, and get rebuilt on every run are usually the single biggest time saving available. **Tests on views re-run the view.** Each dbt test against a view re-executes its logic, JSON parsing included. Materialize heavily tested staging models as tables. ## Concurrency and sizing Compute in MotherDuck is [per account](/concepts/hypertenancy/), not per cluster, so concurrency problems are solved by splitting across accounts rather than by adding WLM queues, concurrency scaling, or RPUs. **dbt threads share one Duckling.** Every thread using the same token runs on the same Duckling and shares its memory. On Redshift, WLM queued the threads beyond its slot count and concurrency scaling picked up the overflow. Here the symptoms are a small model taking minutes because it started alongside a heavy one, a model failing with `cancelled by a newer transaction step` seconds after a sibling ran out of memory, and retries failing identically. Run fewer threads, give the heavy models their own service account and therefore their own Duckling, or size a dedicated Duckling up for them. **Size the BI Duckling separately.** WLM queues often existed to keep dashboards away from the ETL. The MotherDuck equivalent is a separate token and Duckling for the BI tool, or [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/), sized independently of whatever runs dbt. **Keep one writer per database.** Covered in [Migrate from Redshift](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-redshift/#three-habits-to-unlearn), and it's the constraint most often behind a write that fails under orchestration. ## Benchmark it honestly When you compare against Redshift, make the comparison one you'd accept from someone else: - Name the configuration you're comparing against: node type and count for a provisioned cluster, or base and maximum RPUs for Serverless. - Turn off the result cache on the Redshift side with `SET enable_result_cache_for_session TO off`. - Report cold and warm runs separately rather than averaging them. - Say whether concurrency scaling ran during the test, because it changes both latency and cost. - Compare total cost across compute, storage, and seats, including reserved-node commitments. ## Related - [Migrate from Redshift](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-redshift/) - [Query performance](/key-tasks/query-performance/) - [Loading data best practices](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/) - [Scaling patterns](/concepts/scaling-patterns/) --- ## 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%2Ftuning-a-migrated-redshift-workload%2F&page_title=Tune%20a%20migrated%20Redshift%20workload&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.