All jobs

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

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

The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.

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

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

Keep reading