All jobs

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 RushOpens 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 with this body:

{
  "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.

The use case this job follows, with its automation and trigger: Hand off a long task.

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

More jobs like this: Hand off a long task

Keep reading