# Optimize SQL Queries and/or complex workloads like dbt Projects on MotherDuck
> Harness to diagnose and optimize queries and/or complex workloads like dbt Projects with isolated experiments, profiles, correctness checks, and auditable measurements on MotherDuck.
[View source on GitHub](https://github.com/motherduckdb/motherduck-cookbook/tree/main/query-optimization)
Category: getting-started
Features: shares
Tags: dbt, migrate

AI assistant prompt:

```text
I want to optimize SQL queries or a dbt project on MotherDuck. Help me adapt this recipe to my own data and use case, using it as a guide: https://motherduck.com/docs/cookbook/query-optimization
```

Harness to diagnose and optimize queries and/or complex workloads like dbt Projects with
isolated experiments, profiles, correctness checks, and auditable measurements on
MotherDuck.

## How it works

For one slow read-only query, use [lightweight diagnosis](https://github.com/motherduckdb/motherduck-cookbook/blob/main/query-optimization/methodology.md#single-slow-query)
with the user's existing token: no new accounts, clones, or campaign harness.
Use the full methodology for dbt, write-query, or broader optimization campaigns.
Generate [profiles/history](https://github.com/motherduckdb/motherduck-cookbook/blob/main/query-optimization/motherduck-measurement.md), then consult
[DuckDB patterns](https://github.com/motherduckdb/motherduck-cookbook/blob/main/query-optimization/duckdb-patterns.md) for observed bottlenecks. No existing
profiles or internal tooling required.

The [helpers](https://github.com/motherduckdb/motherduck-cookbook/tree/main/query-optimization/scripts/) capture SQL profiles and paired timings, not resource
provisioning or campaign orchestration. Adapt an isolated runner for dbt jobs.

## Questions to answer

Discover what the project provides; clarify only missing inputs. Single-query
diagnosis needs SQL/parameters, database access, the existing token, and execution
conditions; management credentials and compute-slot limits are campaign-only.

1. Goal: query, query group, or dbt project.
2. Workload: SQL and parameters, or project and exact dbt invocation, selectors,
   exclusions, variables, and target. Confirm dbt focus: incremental runs, full
   refreshes, initial builds/backfills, or multiple modes. Never assume full refresh.
3. Credentials: secret references with source-data access. Campaigns also need
   service-account/Duckling management capability; verify permissions and grants.
4. Sources: databases/shares; comparisons need stable inputs. Campaigns pin a snapshot.
5. Campaign compute envelope: maximum service accounts, per-size limits, allowed query/job
   Duckling sizes, and starting dbt threads. Size and threads remain tunable.

For campaigns, suggest smaller query-exploration slots and representative job-sized slots;
increase within the envelope if memory pressure makes exploration unrepresentative.

## Caveats

- `explain analyze` executes SQL. Lightweight diagnosis is read-only; writes
  and dbt require disposable targets and campaign credentials.
- Helpers accept one `SELECT`, optionally as CTAS; these do not measure a full
  dbt job or its actual incremental writes.
- Profile for diagnosis; measure timing without profiling. Follow methodology
  for warm-up, comparable batches, validation, and opt-in repetitions.
- `--setup` reruns per process; prepare persistent experiment inputs separately.

## What you'll adjust

| Helper option | Meaning |
| --- | --- |
| `--database` | Existing database for read-only diagnosis; experiment clone for campaigns. |
| `--sql` / `--baseline` / `--candidate` | SQL files, including compiled model SELECTs. |
| `--mode` | `explain` (default), `profiling`, or `timing`. |
| `--materialize` | Include CTAS into a unique disposable `main.__mdopt_*` table. |
| `--keep-result` / `--keep-results` | Retain CTAS outputs for validation; persistent databases only; track cleanup. |
| `--conditions` | Adapt [conditions.json](https://github.com/motherduckdb/motherduck-cookbook/blob/main/query-optimization/templates/conditions.json); required for MotherDuck. |
| `--inputs-read-at` | Verified full-input read timestamp; omit if unknown to trigger warm-up. |
| `--output` | New artifact directory; existing runs are never overwritten. |
| `--token-env` | Experiment token variable; default `MOTHERDUCK_EXPERIMENT_TOKEN`. |

## Run it

Use `uv` to reuse/recreate the customer's Python, DuckDB, dbt, and adapter
environment; record versions. No recipe dependency pins or original dependency
edits/upgrades. Helpers require Python 3.10+; adapt them for older runtimes.
Commands assume the prepared environment and this recipe's working directory.

For a single-query diagnosis, use [direct profiling](https://github.com/motherduckdb/motherduck-cookbook/blob/main/query-optimization/motherduck-measurement.md#capture-profiles)
with the existing user token. Optional helpers accept `--token-env MOTHERDUCK_TOKEN`;
MotherDuck helper runs still require `--conditions` and perform warm-up as configured.

For campaigns, provision the isolated slot/clone, prepare conditions, and expose its token
through the environment. Profile the customer's SQL, then measure one pair:

```bash
uv run python scripts/profile.py \
  --database md:opt_baseline --sql optimization/experiment-001/baseline.sql \
  --conditions optimization/conditions.json \
  --output optimization/experiment-001/profile

uv run python scripts/benchmark.py \
  --database md:opt_baseline \
  --baseline optimization/experiment-001/baseline.sql \
  --candidate optimization/experiment-001/candidate.sql \
  --conditions optimization/conditions.json \
  --output optimization/experiment-001/pair
```

Use `--mode profiling` for connection profiles; add `--materialize` to include
table-model writes. Never use it with a read scaling token.

## Security

Campaigns separate provisioning, experiment, and observer credentials; lightweight
diagnosis reuses the user's token without changing its permissions. Keep secrets
out of SQL, artifacts, logs, and diffs. Clone only approved sources through restricted
shares; clean only campaign-owned resources. Review SQL/profile artifacts for
sensitive names and literals before sharing.

## Learn more

- [Experiment checkpoint](https://github.com/motherduckdb/motherduck-cookbook/blob/main/query-optimization/templates/experiment.json) and [report](https://github.com/motherduckdb/motherduck-cookbook/blob/main/query-optimization/templates/report.md).
- [Read-scaling benchmark](/cookbook/motherduck-performance-benchmark/) for read-only load tests.


---

## 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=%2Fcookbook%2Fquery-optimization%2F&page_title=Optimize%20SQL%20Queries%20and%2For%20complex%20workloads%20like%20dbt%20Projects%20on%20MotherDuck&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.