# Add Snapshots to a dbt Project

Yes. An agent adds dbt snapshots for the source tables whose rows change in place, with keys, strategies and tests in your project's style, and opens a pull request.

[Run this on Rush](https://cloud.getrush.ai/run/add-snapshots/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 project before deciding what to snapshot. In a dbt project like dbt-labs/jaffle-shop, `dbt_project.yml` already declares `snapshot-paths: ["snapshots"]` and requires dbt 2.0 or later, but no `snapshots` directory exists. `models/staging/__sources.yml` defines one source, `ecom`, in the `raw` schema with six tables: `raw_customers`, `raw_orders` (with `loaded_at_field: ordered_at`), `raw_items`, `raw_stores` (with `loaded_at_field: opened_at`), `raw_products` and `raw_supplies`. The staging models show which of these are facts and which are slowly changing: `stg_orders` and `stg_order_items` read event tables that only grow, while `stg_products` renames `sku` to `product_id` and converts `price` with the `cents_to_dollars` macro, `stg_supplies` builds `supply_uuid` from `id` and `sku` with `dbt_utils.generate_surrogate_key` and converts `cost`, and `stg_customers` carries only `id` and `name`. A price, a supply cost or a customer's name changes in place and the current models lose the old value; those three are the snapshot candidates, and the two event tables are not.

It then writes the snapshots in the project's style. `raw_products` gets a YAML-defined snapshot keyed on `sku` with the `check` strategy over the columns that can change, `name`, `type`, `price` and `description`, because the table has no updated-at column to drive a `timestamp` strategy. `raw_customers` gets the same treatment keyed on `id` over `name`. `raw_supplies` is keyed on `id` and `sku` together, the pair `stg_supplies` already hashes, so the agent keeps that key rather than inventing a new one. Each snapshot configures `hard_deletes` so a product that disappears from the source gets a closing row instead of staying open forever, and a `+schema: snapshots` entry under a new `snapshots:` block in `dbt_project.yml`, which `macros/generate_schema_name.sql` turns into `<target>_snapshots` in prod and the default schema everywhere else, the same rule the marts already follow.

Tests and documentation follow the pattern of `models/staging/stg_products.yml`: `not_null` on the key columns and a `dbt_utils.unique_combination_of_columns` over the key plus `dbt_valid_from`, with a description for each snapshot and each column. SQL is written to pass `.sqlfluff`: lowercase keywords, explicit aliases, four-space indentation and lines under 80 characters. The agent does not change `stg_products` or the marts, so every current model reads the current state exactly as before; it adds an `analyses/` example showing how to join `orders` to the product price in force at `ordered_at`, which is the question the snapshot exists to answer.

To prove the snapshots run, the agent needs a database. The project's `profile: default` points at a warehouse through a `profiles.yml` that `.gitignore` excludes, so the sandbox has none. The agent installs dbt with a local adapter, writes a throwaway profile outside the repository, runs `dbt deps` for `dbt_utils` 1.4.1 and `dbt-audit-helper` from `packages.yml`, loads the sample data with the command from the README, `dbt seed --full-refresh --vars '{"load_source_data": true}'`, then runs `dbt snapshot` once, changes one product's price in the seeded table, and runs it again to show the first row closed with `dbt_valid_to` set and a second row opened. `dbt test` then runs the new tests. If an adapter cannot be installed, the agent falls back to `dbt parse` and `dbt compile` and says so in the pull request rather than claiming a run.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Do the work the Linear issue describes: add snapshots to this dbt project for the source tables whose rows change in place. Read dbt_project.yml, packages.yml, models/staging/__sources.yml, every model under models/staging and models/marts with its YAML, macros/generate_schema_name.sql, macros/cents_to_dollars.sql, .sqlfluff, .pre-commit-config.yaml, README.md and the headers of the CSV files under seeds/jaffle-data first. Decide from the staging models which sources are slowly changing (products, supplies, customers) and which are append-only events (orders, items), and snapshot only the first group. Define each snapshot in YAML under snapshots/, keyed on the source's natural key (sku for products, id for customers, id plus sku for supplies, the pair stg_supplies already hashes), using the check strategy over the columns that change, with hard_deletes configured so removed rows get a closing record. Add a snapshots block to dbt_project.yml with +schema: snapshots, and tests and descriptions in the style of models/staging/stg_products.yml, including a dbt_utils.unique_combination_of_columns test over the key and dbt_valid_from. Do not change the staging or mart models. Add one analysis under analyses/ that joins orders to the product price in force at ordered_at. Prove it: install dbt with a local adapter, write a throwaway profiles.yml outside the repository, run dbt deps, seed with dbt seed --full-refresh --vars '{\"load_source_data\": true}', run dbt snapshot, change one seeded product price, run dbt snapshot again and dbt test, and include the row counts and the closed row in the pull request. If no adapter can be installed, run dbt parse and dbt compile and say so instead. Keep SQL within the .sqlfluff rules and open a pull request.",
  "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 with a new `snapshots/` directory holding one YAML file per snapshot, a `snapshots:` block in `dbt_project.yml`, a test and documentation file beside them, and one analysis under `analyses/`. No staging or mart model changes, so `dbt build` produces the same current-state tables as before and gains three history tables beside them. The description lists each snapshot with its key, strategy and checked columns, and explains why `raw_orders` and `raw_items` were left out.

The transcript of the run holds the `dbt deps`, `dbt seed`, both `dbt snapshot` runs and `dbt test` with their output, so a reviewer can see the second snapshot run close one row and open one, and the tests pass over the result. Where the agent could only parse and compile, the pull request says that in its first line instead.

A reviewer checks the keys against `__sources.yml`, that the `check_cols` list names every column the business edits and no timestamp column that would open a new row on every load, that the schema config follows `generate_schema_name`, and that the analysis joins on the validity window correctly before merging. A follow-up on the same session can add a snapshot for `raw_stores`, whose `tax_rate` also changes, using the same clone and profile.

## Run it on every Linear issue labelled agent

Add an automation to the Project with this prompt and a `linear` trigger on `linear.issue.labeled` with the label your data team uses for agent work. The trigger adds the labelled issue after the prompt, so an issue that says which sources to track, or which columns a finance analyst needs history for, steers the run without any edit to the prompt; a change made by an app integration never fires it, and `max_runs_per_hour` caps how many issues the automation picks up in an hour.

From your own server, send `POST /o/{org}/sessions` with the repository and the issue URL in `resources` so the issue text reaches the agent through your Linear connection, and a `request_id` that is a UUID v5 of the issue id so a retried call returns the run already started. Set a `webhook_url` on the key to receive `run.completed` with `data.session_url` when the pull request is open.

## Why run it on Rush?

Snapshots are the kind of change that looks finished when the YAML parses and is only known to work when `dbt snapshot` has run twice over a changed row. On a laptop that second run needs a warehouse connection most analysts do not have for a side branch; in a Rush sandbox the agent installs an adapter, seeds the project's own sample data and runs the full cycle in isolation, touching no production schema and no shared credentials.

The evidence is kept with the result: the seed, both snapshot runs and the test output are in the transcript the pull request links to. Several projects, or several issues against one project, run at once in separate sandboxes and arrive as separate pull requests, each with the row counts that show its snapshots behaving.

## Frequently asked questions

### Why not snapshot every source table?

Because `raw_orders` and `raw_items` are event tables that only gain rows, and their `loaded_at_field` settings in `__sources.yml` already serve freshness checks. A snapshot of them would double the storage for no history that the tables do not already hold. Products, supplies and customers change in place, which is the case snapshots exist for.

### Why the check strategy and not timestamp?

None of the three tables has an updated-at column: `raw_products.csv` has `sku, name, type, price, description` and `raw_customers.csv` has `id, name`. With no timestamp to compare, `check` over the columns that change is the only strategy that can detect an edit.

### Do the staging models or marts change?

No. `stg_products`, `stg_supplies`, `stg_customers` and every mart keep reading the current source rows, so dashboards built on them do not move. The snapshots are new relations in their own schema, and the analysis under `analyses/` shows how to read the price in force at order time from them.

### Which warehouse does the proof run against?

A local one the agent installs in its sandbox with a throwaway profile outside the repository, since `profiles.yml` is gitignored and the project's dbt Cloud environments are not reachable from a sandbox. The SQL stays portable through the `adapter.dispatch` macros the project already uses, such as `cents_to_dollars`, and the pull request says which adapter ran the check.

### What about the dbt Cloud deploy jobs?

The workflows under `.github/workflows` trigger dbt Cloud jobs on `main` for Snowflake, BigQuery and Postgres. A `dbt build` in those jobs now includes `dbt snapshot`, so the history tables start accumulating on the first production run after the merge; nothing in the workflows needs editing.

## More dbt jobs

- [Add Source Freshness Checks to a dbt Project](https://getrush.ai/run/add-source-freshness/dbt/)
- [Add SQLFluff Linting to a dbt Project](https://getrush.ai/run/add-sqlfluff-linting/dbt/)
- [Add Unit Tests to a dbt Project](https://getrush.ai/run/add-unit-tests/dbt/)

## More jobs like this: Turn a Linear label into a PR

- [Add Sobelow Security Scanning to a Phoenix App](https://getrush.ai/run/add-sobelow/elixir/)
- [Add Social Cards to an Eleventy Blog](https://getrush.ai/run/add-social-cards/eleventy/)
- [Add Social Cards to Sphinx Docs](https://getrush.ai/run/add-social-cards/sphinx/)

## 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/)
