# Tune a migrated Snowflake workload > Fix the query and dbt patterns that run on MotherDuck but run badly after a direct port from Snowflake, from JSON parsing to join fan-out to thread concurrency. A direct port of Snowflake SQL runs on MotherDuck. It doesn't run *optimally*. Snowflake absorbs a lot of inefficiency by reaching for a bigger warehouse, and that inefficiency can become visible as memory pressure and disk spills when you try to run it on a smaller Duckling. 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 Snowflake. See [Migrate from Snowflake](/key-tasks/data-warehousing/migrating-from-snowflake/). - [`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. ## JSON and semi-structured data This is the single biggest cause of out-of-memory errors in Snowflake migrations, and it comes from a storage difference that isn't visible in the SQL. Snowflake stores `VARIANT` data column-shredded internally, so parsing at query time is cheap and teams happily leave raw `VARIANT` columns in staging views. DuckDB stores JSON as text. Every parse does real work, and a staging *view* makes every downstream model and every dbt test redo that work from scratch. - **Parse once, store typed columns.** If a staging view runs `json_extract_string(content, '$.x')`, materialize it as a table with typed columns instead, using `STRUCT` or `LIST` for anything nested. This single change usually does more than everything else on this page combined. - **Use `UNNEST` on a typed column, not `LATERAL` over raw JSON.** Expanding raw JSON documents builds in-memory hash tables keyed on the whole document, which eat memory and spill badly. Unnesting a typed `LIST` or `STRUCT` column avoids the hash tables entirely. - **Filters on JSON columns can't skip data.** DuckDB normally skips row groups that can't match a filter, using per-group statistics. It can't do that when the filter is on a JSON column, so the scan reads everything. Pull any column you filter on out into its own typed field. - **Don't carry payloads you don't need.** Split high-volume message types into their own models. A general-purpose event or message-log model is frequently dominated, in both rows and bytes, by one payload type that one downstream model reads. ## Joins that explode DuckDB's optimizer is weaker than Snowflake's on joins whose condition isn't a simple equality, and it often misjudges how many rows one will produce. A single [Duckling](/concepts/hypertenancy/) also can't brute-force its way through a billion-row intermediate result the way a Large warehouse can. **Check key uniqueness on every join you port.** Joining to a one-to-many table just to filter rows silently duplicates those rows, which produces wrong revenue numbers rather than a crash. Use `EXISTS`, or join to a `SELECT DISTINCT` of the key: ```sql SELECT customer_id, order_date, count(*) FROM raw.orders GROUP BY ALL HAVING count(*) > 1 LIMIT 10; ``` **Replace calendar cross joins with the grain you actually need.** Crossing a daily calendar with every customer and reducing it afterwards with `DISTINCT` materializes the full product first. If the result is monthly, join to `SELECT DISTINCT month FROM calendar`, and join each customer once on a date-range condition. **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: ```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. In practice the `QUALIFY` version usually wasn't handling ties deterministically either. **Stop re-joining the same large table at every layer.** Pipelines that join a raw log again in each model, and then a reporting model that joins those results once per CTE, can spill terabytes. Add the columns you need to the lowest-level model so the layers above become plain selects, or do the join once in a `MATERIALIZED` CTE. **Filter and aggregate before joining back.** If every downstream `sum()` needs `status = 'paid'`, put that in the base CTE rather than the final `WHERE`. Aggregate child tables by key before joining them to the parent. **Check your `UNION`s.** `UNION` forces a full sort to deduplicate. Where the inputs are already disjoint, which is most of the time, `UNION ALL` is both correct and much cheaper. ## Timezone conversion in shared views `convert_timezone` is cheap in Snowflake and comparatively slow in DuckDB, where it's a per-row function call. In a shared view, every downstream query that scans the table pays that cost on every row before any filter applies, which can dominate the runtime of a query that ends up returning a few thousand rows. - If the stored timestamp already represents local midnight as UTC, `CAST(ts AS DATE)` gives the same answer for free. Confirm that holds for your data first. - Convert the filter instead of the column. Rewrite `WHERE date_trunc('day', ts_local) = :d` as a range on the UTC column: `ts_utc >= convert_timezone(tz, 'UTC', :d) AND ts_utc < convert_timezone(tz, 'UTC', :d + 1)`. That evaluates the conversion twice rather than once per row. - Where local-time columns are genuinely needed downstream, materialize them once instead of computing them in a view. ## dbt patterns worth auditing **Incremental models that aren't.** Models configured as incremental in Snowflake frequently rebuild in full anyway: no `is_incremental()` filter, or a CTE that looks like it filters and doesn't. Snowflake's compute hid it. Add a real filter on a last-loaded timestamp with a lookback window, and set `unique_key`. Don't assume the Snowflake config was right, either — copying it across unchanged can make models slower. **Append-only staging tables rebuilt from scratch.** Look for sources carrying a load-timestamp column such as `_sdc_batched_at` or `_fivetran_synced` that get rebuilt on every run. Making these incremental is usually the single biggest time saving available. **Window functions that silently do nothing.** `lag(x IGNORE NULLS) OVER (PARTITION BY id ORDER BY updated_at)` combined with `WHERE dbt_valid_to IS NULL` gives every partition exactly one row, because a dbt snapshot has one current row per key. The result is always `NULL`, and DuckDB still sorts every partition to produce it. Compute the window over full history in a CTE, then filter to current rows — and check with the model's owner what was meant, because the Snowflake output was probably wrong too. **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. **A plain `UNION ALL` model shouldn't spill.** If a model that only combines tables and selects columns spills heavily, set `preserve_insertion_order = false` for that build. The same setting speeds up Parquet loads from cloud storage. **`generate_surrogate_key` is fine.** The chain of `typeof()` cases in `dbt_utils` looks expensive but is evaluated at plan time, not per row. It benchmarks the same as a plain `md5()` concatenation. Spend your time elsewhere. ## 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 sizing one thing up. **dbt threads share one Duckling.** Every thread using the same token runs on the same Duckling and shares its memory. A Snowflake multi-cluster warehouse let you set eight threads and forget about it. 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.** BI tools running wide queries against reporting tables can hit the temporary disk spill limit. Give them their own token and Duckling, or use [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 Snowflake](/key-tasks/data-warehousing/migrating-from-snowflake/#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 Snowflake, make the comparison one you'd accept from someone else: - Name the Snowflake warehouse size you're comparing against. - Turn off `USE_CACHED_RESULT` on the Snowflake side. - Report cold and warm runs separately rather than averaging them. - Compare total cost across compute, storage, and seats. MotherDuck [prices storage per GB uncompressed](/about-motherduck/billing/pricing/) where Snowflake prices per compressed TB, so a single line item tells you very little on its own. ## Related - [Migrate from Snowflake](/key-tasks/data-warehousing/migrating-from-snowflake/) - [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-from-snowflake%2Ftuning-a-migrated-snowflake-workload%2F&page_title=Tune%20a%20migrated%20Snowflake%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.