# Tune a migrated BigQuery workload > Fix the query and dbt patterns that run on MotherDuck but run badly after a direct port from BigQuery, from nested GA4 fields to sharded tables to dbt threads. A direct port of GoogleSQL runs on MotherDuck. It doesn't run *optimally*. BigQuery spreads a query across as many slots as it needs, so inefficient SQL costs bytes and slot time rather than failing. On a single Duckling the same inefficiency 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 BigQuery. See [Migrate from BigQuery](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/). - [`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. ## Optimize for time, not bytes BigQuery's on-demand pricing bills bytes scanned, so BigQuery SQL is written to scan less: partition filters on every query, narrow column lists, sharded tables. MotherDuck bills Duckling time. A query that reads a whole table and finishes in two seconds is cheap, and a query that reads one partition but builds a huge intermediate join is not. Keep the habits that also save time, such as selecting only the columns you need. Stop spending effort on the ones that only saved bytes, and look instead for the patterns below, which BigQuery's slot pool hid. ## Nested and repeated fields This is the single biggest cause of slow models in BigQuery migrations, and the GA4 export is where you'll meet it first. GA4 stores event parameters as a repeated record, `event_params ARRAY>>`, and the standard BigQuery idiom pulls each parameter out with its own correlated subquery: ```sql -- One correlated subquery per parameter, per row SELECT event_date, user_pseudo_id, (SELECT p.value.string_value FROM unnest(event_params) AS u(p) WHERE p.key = 'page_location') AS page_location, (SELECT p.value.int_value FROM unnest(event_params) AS u(p) WHERE p.key = 'ga_session_id') AS ga_session_id FROM raw.main.ga4_events WHERE event_name = 'page_view'; ``` This runs in DuckDB, but every subquery unnests the full list again, and a staging view makes every downstream model and every dbt test repeat the work. - **Flatten once, store typed columns.** Materialize the parameters you use as a table, and point the downstream models at it. `list_filter` reads a parameter from the list without a subquery: ```sql CREATE OR REPLACE TABLE analytics.main.page_views AS SELECT event_date, user_pseudo_id, list_filter(event_params, lambda p: p.key = 'page_location')[1].value.string_value AS page_location, list_filter(event_params, lambda p: p.key = 'ga_session_id')[1].value.int_value AS ga_session_id FROM raw.main.ga4_events WHERE event_name = 'page_view' ORDER BY event_date; ``` This single change usually does more than everything else on this page combined. - **Don't carry payloads you don't need.** Split high-volume event names 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. - **Parse JSON strings once, too.** Columns of JSON text read with `JSON_EXTRACT_SCALAR` in BigQuery become `json_extract_string` calls in DuckDB, and DuckDB parses the text on every read. Filters on JSON can't skip row groups either, so the scan reads everything. Extract the fields you use, and every field you filter on, into typed columns. ## Sharded tables and partition filters A BigQuery workload often reads daily shards with `FROM events_* WHERE _TABLE_SUFFIX BETWEEN ...`, or relies on `_PARTITIONDATE` filters for pruning. Neither survives the move as a pruning mechanism. - **Consolidate shards into one table, sorted by date.** DuckDB keeps min/max statistics per row group, so a table loaded in date order skips the row groups a date filter can't match. That's the same pruning partitioning gave you, without one table per day. - **Replace pseudo-columns with real ones.** A model that filters on `_PARTITIONTIME` needs that value as a stored column, and the sort order on insert should follow the column you filter on most. - **Drop the defensive partition filters that only limited cost.** A `WHERE event_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY)` that existed to stop a full scan from being billed can go, unless the model's logic needs it. Keep the ones that make incremental models incremental. ## Joins that explode BigQuery shuffles a large join across hundreds or thousands of slots. 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.** Joining to a one-to-many table 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 the `ARRAY_AGG` deduplication idiom.** BigQuery code often keeps the latest row per key with `ARRAY_AGG(t ORDER BY updated_at DESC LIMIT 1)[OFFSET(0)]`. DuckDB doesn't accept `LIMIT` inside an aggregate. Use `arg_max`, which keeps one row per key without collecting the rest into a list: ```sql -- Latest row per customer, as a struct SELECT customer_id, arg_max(c, updated_at) AS latest FROM raw.customers AS c GROUP BY customer_id; -- Or keep the columns flat SELECT * FROM raw.customers QUALIFY row_number() OVER (PARTITION BY customer_id ORDER BY updated_at DESC) = 1; ``` **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. **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.** GoogleSQL makes you write `UNION DISTINCT` or `UNION ALL` explicitly, but a translator that maps `UNION DISTINCT` to `UNION` keeps a full deduplication. Where the inputs are already disjoint, `UNION ALL` is both correct and much cheaper. ## Timezone conversion in shared views BigQuery code converts timezones inline, with `DATETIME(ts, 'Europe/Amsterdam')` or `DATE(ts, tz)`, 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 **`insert_overwrite` has no direct equivalent.** dbt-bigquery's usual incremental strategy replaces whole partitions. dbt-duckdb supports `append`, `delete+insert`, `merge`, and `microbatch`. To replace whole days, use `delete+insert` with the date column as `unique_key`: every date in the new batch is deleted and rewritten. ```sql {{ config( materialized='incremental', incremental_strategy='delete+insert', unique_key='event_date' ) }} SELECT * FROM {{ ref('stg_events') }} {% if is_incremental() %} WHERE event_date >= (SELECT max(event_date) - INTERVAL 3 DAY FROM {{ this }}) {% endif %} ORDER BY event_date ``` **Remove BigQuery-only configs.** `partition_by`, `cluster_by`, `require_partition_filter`, `partition_expiration_days`, and `labels` do nothing on MotherDuck. Delete them so nobody assumes they still apply, and put the sort order in the model's SQL instead. **Incremental models that aren't.** Models configured as incremental frequently rebuild in full anyway: no `is_incremental()` filter, or a filter that BigQuery's partition pruning 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, nested-field unnesting included. Materialize heavily tested staging models as tables. ## Concurrency and sizing Compute in MotherDuck is [per account](/concepts/hypertenancy/), not per slot pool, so concurrency problems are solved by splitting across accounts rather than by buying more slots. **dbt threads share one Duckling.** Every thread using the same token runs on the same Duckling and shares its memory. BigQuery projects routinely run dbt with 16 or more threads, because the slot pool absorbs them. 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.** Dashboards that ran on BI Engine or the shared slot pool can hit the temporary disk spill limit when they run wide queries against reporting tables. 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 BigQuery](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/#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 BigQuery, make the comparison one you'd accept from someone else: - Name the pricing model you're comparing against: on demand, or an edition with its reservation size. - Turn off cached results on the BigQuery side, with `--nouse_cache` in the `bq` CLI or the query settings in the console. - Report cold and warm runs separately rather than averaging them. - Compare total cost across compute, storage, and seats. BigQuery bills storage on logical or physical bytes depending on each dataset's storage billing model, so check which one applies before you compare storage lines. ## Related - [Migrate from BigQuery](/key-tasks/data-warehousing/migrating-your-data-warehouse/migrating-from-bigquery/) - [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-bigquery%2Ftuning-a-migrated-bigquery-workload%2F&page_title=Tune%20a%20migrated%20BigQuery%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.