# Convert a dbt Mart to an Incremental Model

Yes. An agent turns a full-refresh dbt mart into an incremental model with a unique key and a lookback window, keeps its tests passing and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/convert-to-incremental-model/dbt/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads the model and everything that depends on it. In a dbt project like `dbt-labs/jaffle-shop`, `dbt_project.yml` names the project `jaffle_shop` at version 3.0.0, requires dbt 2.0.0 or later, materializes `staging` as views and `marts` as tables, and enables the `seeds/jaffle-data` CSVs only when `load_source_data` is true. `models/marts/orders.sql` builds from `stg_orders` and `order_items`: an `order_items_summary` CTE sums `supply_cost` and `product_price` and counts food and drink items per order, `compute_booleans` left-joins it onto the orders, and `customer_order_count` adds `row_number() over (partition by customer_id order by ordered_at asc) as customer_order_number`. `models/staging/stg_orders.sql` truncates `ordered_at` to the day with `dbt.date_trunc` and converts cents with the `cents_to_dollars` macro from `macros/`, which dispatches per adapter. `models/staging/__sources.yml` sets `loaded_at_field: ordered_at` on `raw_orders`.

`models/marts/orders.yml` is the contract. It has two `dbt_utils.expression_is_true` tests, `order_items_subtotal = subtotal` and `order_total = subtotal + tax_paid`, `not_null` and `unique` on `order_id`, a `relationships` test from `customer_id` to `stg_customers`, a semantic model with `ordered_at` at day granularity, a `customer_order_number` dimension that the `new_customer_orders` metric filters on, a saved query, and a unit test, `test_order_items_compute_to_bools_correctly`, that feeds rows into `order_items` and `stg_orders` and expects the boolean columns. `models/marts/customers.sql` aggregates `orders` per customer, so a wrong incremental row shows up downstream as a wrong `count_lifetime_orders`.

The agent sets `materialized='incremental'` and `unique_key='order_id'` in the model's `config`, leaves the strategy to the adapter default so Snowflake, BigQuery and Postgres each use what they support, and adds `on_schema_change='append_new_columns'`. The `orders` CTE gets an `{% if is_incremental() %}` filter on `ordered_at` from the max in `{{ this }}` minus a lookback window held in a project `var` with a default, so late orders are re-merged rather than missed. `customer_order_number` is the real work: a row number over a customer's whole history cannot be computed from a window of new rows, so the agent reads each customer's existing count from `{{ this }}` in the incremental branch and offsets the new rows' `row_number()` by it, and writes that reasoning into a comment and the pull request. The unit test keeps its expectations and gains a second case that sets `overrides` on `is_incremental` to true with `this` rows given, so the offset branch is tested without a warehouse.

Proof needs a warehouse, and `.github/workflows/ci.yml` only triggers dbt Cloud jobs for Snowflake, BigQuery and Postgres through `scripts/dbt_cloud_run_job.py`, so the agent starts Postgres in its sandbox, writes a `profiles.yml` for it, installs `dbt-postgres` and the `requirements.txt` tooling, runs `dbt deps` for `dbt_utils` 1.4.1 and `audit_helper`, seeds with `dbt seed --vars '{"load_source_data": true}'`, then `dbt build --select +orders+` for the first full build, `dbt build --select orders+` again to exercise the incremental branch, and `dbt build --select orders --full-refresh` to show the two agree on row count and on `customer_order_number`, using an `audit_helper.compare_relations` query. `sqlfluff` runs over the file with the project's `.sqlfluff` rules: lowercase keywords, explicit aliases, an 80-column limit and a 4-space indent.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Convert the orders mart in this dbt project to an incremental model. Read dbt_project.yml, packages.yml, models/marts/orders.sql, models/marts/orders.yml, models/marts/customers.sql, models/staging/stg_orders.sql, models/staging/__sources.yml, macros/, .sqlfluff, Taskfile.yml and .github/workflows/ci.yml first. Set materialized incremental with unique_key order_id and on_schema_change append_new_columns, add an is_incremental filter on ordered_at with a lookback window from a project var, and handle customer_order_number by offsetting new rows with each customer's existing count from this. Keep every test, the semantic model and the saved query working, and add a unit test case that overrides is_incremental to cover the incremental branch. Start Postgres in the sandbox, write a profile for it, install dbt-postgres, run dbt deps, seed with load_source_data true, run dbt build for a full build, run it again to exercise the incremental path, run a full refresh and compare the two with audit_helper, run sqlfluff on the file, and open a pull request that explains the window, the offset and the comparison result.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a pull request that changes `models/marts/orders.sql` and `models/marts/orders.yml`, adds the lookback `var` with a default to `dbt_project.yml`, and nothing else. The description explains the window and why it exists, how `customer_order_number` stays correct across runs, and quotes the row counts and the `compare_relations` result from the incremental and full-refresh builds.

The transcript holds the Postgres start, `dbt deps`, the seed, each `dbt build` with its test results, the full-refresh comparison and the `sqlfluff` output, so a reviewer sees the incremental branch run against real rows rather than only compiled. `GET /o/{org}/p/{project}/sessions/{id}/changes` returns the diff of the two model files.

Before merging, a reviewer checks that the lookback window is wide enough for the latest order the sources can deliver, that the offset reads `{{ this }}` once rather than per row, that the unit test's incremental case has `this` rows that make the offset non-zero, and that the semantic model's `customer_order_number` dimension still filters the `new_customer_orders` metric correctly. A follow-up message to the same session converts `order_items` the same way, and the sandbox still has Postgres and the seeds loaded.

## Run it on every mart that outgrows its nightly rebuild

An analytics engineer hands the model off from the terminal: one `POST /o/{org}/sessions` with the repository in `resources`, this prompt with the model name, and a `request_id` for the model so a retried request returns the run already started. Close the laptop; read the pull request from the console or from `GET /o/{org}/sessions/{id}` when it is done.

A team with several marts to convert sends one request per model, each in its own sandbox with its own Postgres, and a `webhook_url` on the key posts `run.completed` with a `session_url` for each pull request as it lands.

## Why run it on Rush?

An incremental conversion is only believable after two consecutive builds and a full-refresh comparison, which on a laptop means a warehouse connection, seeds, three builds and an audit query, with the dbt Cloud jobs only reachable through CI. In a Rush sandbox the agent runs Postgres and dbt itself, does all three builds and the comparison, and keeps each one in the record.

The hard part of this job is the window function that depends on history, and the pull request has to explain it rather than hide it. The transcript shows the offset logic being tested with the unit test's override and against real rows, so a reviewer can check the reasoning instead of re-deriving it. Several models convert at once, each in its own sandbox.

## Frequently asked questions

### Which incremental strategy does it use?

The adapter default, so Snowflake and BigQuery merge on `order_id` and Postgres uses delete and insert, matching the three dbt Cloud jobs in CI. Say `incremental_strategy` in the prompt to pin one, and the agent sets it in the model config.

### How does customer_order_number stay right when only new orders are processed?

The incremental branch reads each customer's current order count from `{{ this }}` and adds it to the `row_number()` of the new rows for that customer. A late order inside the lookback window is re-merged on `order_id`, and the pull request notes that an order arriving outside the window needs a full refresh to renumber.

### Will my existing tests and semantic model still work?

Yes. The columns do not change, so the `expression_is_true`, `unique`, `not_null` and `relationships` tests, the semantic model, the metrics and the saved query are untouched. The existing unit test keeps passing and a new case covers the incremental branch.

### I do not use Postgres. Is the proof still valid?

The SQL stays adapter-agnostic, with `dbt.date_trunc` and the dispatched `cents_to_dollars` macro as in the project today, and the dbt Cloud CI jobs run the pull request on Snowflake, BigQuery and Postgres. The sandbox run on Postgres shows the logic; CI shows it on your warehouse.

## More dbt jobs

- [Document dbt Models and Find Data Quality Gaps](https://getrush.ai/run/dbt-model-docs/dbt/)
- [Publish dbt Docs to GitHub Pages](https://getrush.ai/run/publish-dbt-docs/dbt/)
- [Review Pull Requests on a dbt Project](https://getrush.ai/run/review-pull-requests/dbt/)

## More jobs like this: Hand off a long task

- [Dockerize an Actix Web App](https://getrush.ai/run/dockerize-app/actix/)
- [Dockerize an Angular App](https://getrush.ai/run/dockerize-app/angular/)
- [Dockerize an Axum API With SQLx and Postgres](https://getrush.ai/run/dockerize-app/axum/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
