Spec-Lock-Diff

English · Português (pt-BR)

A framework for dbt development using AI agents. The goal is to reduce the main risks that arise when an agent writes SQL:

The framework boils down to three phases:

• Spec — The human defines, in structured detail, what the dbt model should do before any code is written.
• Lock — Deterministic restrictions. Cost, access, and behavior limits live in the infrastructure (warehouse, CI, permissions), not in text instructions to the agent.
• Diff — After the agent finishes, the human checks and reviews numbers (differences between production and the new version), not code.

A working reference implementation of the gates lives in tools/: three commands in one Python package, no network and no warehouse.

Want to see it before you read all this? examples/quickstart is a dbt project the gates pass on — two marts, their specs, their pre-registrations and their diffs. No dbt, no warehouse and no credentials needed:

pip install "pyyaml" "jsonschema>=4"
python tools/slp.py check --project-dir examples/quickstart

Adoption is a ladder, not a cliff: check and gate are twenty-six of the thirty-five rules and need no warehouse at all. The install section has the five rungs, each green on its own.

---

Table of Contents

0. Roles — who does what
1. Manifesto — 3 principles
2. Building the lock — 5 mandatory controls
3. The development process (routine) — 5 stages
4. Routines — three step-by-steps
5. References — where these ideas come from

Seven words this document uses before it defines them, so you can read straight through:

---

0. Roles — who does what

This framework defines four roles.

---

1. Manifesto — 3 principles

Why the framework is being built. All rules derive from them.

---

2. Building the lock — 5 mandatory controls

You are not writing rules for the agent to obey — you are building an environment in which the rules cannot be broken. Once these five controls are in place, the agent can be released inside them and left to work alone, because it cannot spend money it was not given, read data it was not shown, or merge code no one read. This way we can reduce the human work and effort of reviewing SQL models line by line.

Who executes: Platform. When: One time only, before the first PR with an agent.

[!IMPORTANT] Don't turn an agent loose on the repository before these five are in place. They are what make everything after them enforceable instead of advisory.

They are not a prerequisite for running the gates. check and gate — twenty-six of the thirty-five rules in tools/ — need no warehouse, no identity and no spending cap, and are worth having on a repository no agent has touched yet. Adoption is a ladder; this section is its fourth rung.

---

Control 1: Create a dedicated identity for the agent

What it is: The agent must have its own separate identity in the warehouse and in git, with restricted permissions.

Why it exists: If the agent uses a human's credentials, it inherits all of that human's permissions. If it runs as admin, it can do anything. A separate identity with minimal permissions limits what the agent can do.

How to implement:

In the warehouse (Snowflake, BigQuery or Databricks):

• Create a role called agent_ci (or equivalent name).
• Create a user associated with that role.
• This user will have the permissions defined in controls 2, 3, and 4.

In git (GitHub, GitLab etc.):

• Create a bot user for the agent.
• This user cannot approve PRs.
• This user cannot merge.
• This user cannot push directly to main.

Branch protection on main (all mandatory):

• PR mandatory for any change.
• CODEOWNERS review mandatory.
• Approvals automatically dismissed on each new push (so the agent cannot "pass" an old approval after changing the code).
• No bypass for anyone — including admins.
• Mandatory status checks: CI (stage D) and Diff (stage E) of the per-PR flow.

On every branch (a ruleset that targets *, or the equivalent):

• Force-push blocked. The anti-fraud gate (Control 5B) walks the commits of the pull request to see when the spec and the pre-registration were first written and how often they changed. A rewritten history — commit --amend, a rebase, a squash — is a history with none of that in it, and nothing the gate can read tells it so. An agent that cannot rewrite the branch cannot erase the evidence; an agent that can, can.

---

Control 2: Restricted data access

What it is: The agent only sees what it needs to see, and never sees sensitive data.

Why it exists: An LLM that accesses raw data can leak personal information (CPF, email, address) in code, tests, PR comments, or even in the conversation log with the model provider.

How to implement:

Masking of sensitive columns:

• In the .yml of each dbt model, every sensitive column must have meta: {sensitive: true}, or an analogous mechanism.
• Masking is applied automatically by the agent_ci role when querying these columns.
• Implementation by platform:
  • Snowflake: use the dbt-snow-mask package.
  • BigQuery: use policy tags.
  • Databricks: use column masks.

---

Control 3: Spending caps

What it is: Financial limits that automatically shut down the agent when reached.

Why it exists: An agent can generate expensive queries in a loop (accidental cross joins, repeated full scans, infinite loops).

How to implement:

Warehouse costs:

• Snowflake: Resource monitor with FREQUENCY = DAILY and action SUSPEND_IMMEDIATE. The daily quota should be: (monthly quota ÷ 22 business days). When reached, the warehouse is shut down immediately.
• BigQuery: Daily quota of scanned bytes in the agent's CI project, and maximum_bytes_billed in the agent's profiles.yml, so that one query above the cap fails instead of billing.
• Databricks: Databricks' budget system only sends alerts (doesn't shut down). So create a job that runs every hour, queries the day's accumulated consumption, and shuts down the agent's SQL warehouse if it's above the cap.

Timeout per query:

• Configure STATEMENT_TIMEOUT_IN_SECONDS on the agent's user and warehouse. If a query takes longer than the timeout, it is cancelled automatically.

---

Control 4: Aggregate statistics instead of access to real records

What it is: Instead of allowing the agent to query real rows of data, provide it with a pre-computed statistical summary of each model.

Why it exists: If the agent runs SELECT * FROM customers, it sees names, emails, CPFs — real data. Even with masking, the less the agent sees, the better. A statistical profile gives the agent enough information to write correct SQL, without exposing any individual data.

How to implement:

Create a weekly job that:

1. Runs with the agent_ci role.
2. For each dbt model, generates a file in docs/profile/<model_name>.yml.
3. Each file contains, per column:
   • Total row count.
   • Percentage of nulls.
   • Cardinality (number of distinct values).
   • Top 20 values only in columns marked with meta: {categorical: true} in the model's .yml. Columns without this tag do not display individual values.
4. The profile does not contain: minimum values, maximum values, data samples, row examples.

# docs/profile/fct_orders.yml — regenerated weekly, read by the agent
order_id:       {rows: 1284003, nulls: 0.0%, distinct: 1284003}
customer_id:    {rows: 1284003, nulls: 0.0%, distinct: 84120}
status:         {rows: 1284003, nulls: 0.0%, distinct: 6,
                 top: [shipped, delivered, cancelled, ...]}   # categorical: true
customer_email: {rows: 1284003, nulls: 1.2%, distinct: 83904}
# no minimums, no maximums, no samples, no example rows

When the agent needs to understand the structure of data, it consults docs/profile/ first. To draft a spec (Stage A) it may also run aggregate-only queries over staging and marts, as the agent_ci role, under the masking of Control 2 and the spending cap of Control 3: metadata first (on BigQuery, INFORMATION_SCHEMA row counts and table bytes cost nothing), then count(*) against count(distinct ...) or APPROX_COUNT_DISTINCT to test a grain, null rates, sums of numeric columns as metric candidates, and the distinct values of columns marked categorical: true as edge candidates, each over one recent partition and each preceded by a dry run. It never runs a query that returns rows.

---

Control 5: Protected paths and anti-fraud gates

What it is: Certain files and directories must be protected so that only humans can modify them. Additionally, a CI script must detect if the agent tried to weaken tests or bypass protections.

Why it exists: An agent can, without ill intent, remove a failing test, change the expected result of a test to make it pass, or change a security config. These changes make the CI green, but hide bugs. Humans need to control the files that define the rules of the game.

How to implement:

Part A — CODEOWNERS (git requires human approval for these paths):

Part B — Anti-fraud gate:

A script that runs in CI on the pull requests the bot opens — the opener of a pull request is an identity the platform authenticates, unlike the author of a commit, which is text — and judges every commit in them, whoever wrote it. On a pull request a human opens it runs and is advisory: CODEOWNERS (Part A) judges those. It is the only custom script that the framework requires. It analyzes the diffs and blocks the PR if it finds any of these situations:

The reference implementation of this gate is tools/slp.py: slp gate, next to slp check for Stage A and slp compare for Stage E.

Optional (extra layer of protection): If the agent supports hooks before executing tools (e.g., PreToolUse in Claude Code), configure a hook that refuses writing to protected paths on the spot — even before the commit.

---

3. The development process (routine) — 5 stages

---

Stage A: Spec (Author)

What it is: The Author (human) writes a declarative specification in the model's .yml, inside the meta.spec block. The spec defines what the model should do — not how.

Where it lives: In the dbt model's .yml file, inside meta.spec.

When it is mandatory: In all models within models/marts/**. Models in staging or intermediate can have a spec, but it is not mandatory.

Spec fields (6 base fields + 3 additional for critical models):

meta:
  spec:
    # --- 6 mandatory fields for every model in marts/ ---

    grain: "one row per order per day"
    # What each row represents. This is the most important definition of the model.
    # Example: "one row per customer" or "one row per transaction per product".

    primary_key: [order_id, date_day]
    # The columns that together uniquely identify a row.
    # The agent will generate a uniqueness test for this combination.

    tier: critical  # Possible values: "critical" or "standard"
    # "critical" = model that feeds business decisions, financial reports
    #             or executive dashboards. Requires 3 extra fields (below)
    #             and approval from a Partner.
    # "standard" = everything else.

    metrics:
      gross_revenue: "sum of order_total before discounts and taxes"
    # Each metric the model calculates, with a natural language definition.
    # The agent will use these definitions to write the SQL.
    # The diff (stage E) will compare the values of these metrics between
    # production and the new version.

    known_edges:
      - "status='cancelled' → row excluded"
      - "value in cents → divide by 100"
      - "timestamp in UTC → convert to America/Sao_Paulo"
    # Special cases the Author already knows exist.
    # EACH edge becomes a unit test with synthetic fixture.
    # The edge should describe the EXPECTED result, not the implementation.
    # Good example: "status='cancelled' → row excluded"
    # Bad example: "use WHERE status != 'cancelled'"
    # One line each, free text: what is given, an arrow, what the model does
    # with it. Nothing validates the words; a machine checks only that every
    # edge has a unit test naming it (Stage C, Rule 2).

    sensitive_columns: [customer_email]
    # List of columns containing personal data.
    # Control 2 masking will be applied to these columns.

    # --- 3 additional fields, mandatory ONLY for tier: critical ---

    reconciliation_query: analyses/reconciliation_fct_orders.sql
    # Path to a SQL query that compares the model result with an
    # external source of truth (another system, closing spreadsheet, etc.).
    # This query runs in stage E with full data.

    reconciliation_tolerance: "0.1%"
    # The maximum acceptable difference between the model and the source of truth.
    # If the difference is greater than this, the PR is blocked.

    external_validation: "gross_revenue 2025-12 = R$ 14,203,118.40 in accounting closing"
    # A concrete number from outside the warehouse that serves as an anchor.
    # This exists because the spec can also be wrong.
    # If the spec is wrong, all tests will pass (they test the spec),
    # but the final result will diverge from the real number.
    # External validation catches that case.

Important rules about the spec:

1. The agent can draft an initial version of the spec from the statistical profile (Control 4). But the 6 fields must be read and approved by the human before any line of code is written.

2. The spec can also be wrong. An error in the spec is invisible to all automated gates (because the tests verify the spec, not reality). That's exactly why the external_validation field exists: it anchors the model to a number that comes from outside the warehouse.

---

Stage B: Pre-registration (Agent)

What it is: Before writing any code, the agent declares which numerical changes it expects to happen. This is done in a pre_registration block in the model's .yml.

Why it exists: Without pre-registration, the agent sees the diff numbers and then invents a justification. Pre-registration reverses this order: the agent commits to intervals before seeing the results. If the numbers fall outside the interval, the PR is automatically blocked — the agent cannot "adjust" its prediction later.

[!IMPORTANT] The pre-registration is immutable from the moment stage D (CI) begins. If the agent changes the pre-registration after CI has run, the CI is re-executed from scratch and a change counter is incremented in the PR (visible to the Author in review).

Pre-registration format:

pre_registration:
  type: data_change
  # Possible values:
  #   "data_change" — the change must alter numerical results.
  #   "refactoring" — the change must NOT alter any result.
  #                   If type is "refactoring", every delta MUST be 0.
  #                   Any numerical difference blocks the PR.

  reason: "include status='partially_shipped', previously excluded incorrectly"
  # One-sentence explanation of why the numbers will change.
  # The Author will read this in review and assess whether the interval makes sense
  # given the declared reason.

  row_delta: {min: 0, max: 12000}
  # How many more (or fewer) rows the model will have compared to production.
  # RULE: every interval must have min AND max. Open interval
  # (e.g., {min: 0} without max) is invalid and rejected by CI.

  removed_pks: {max: 0}
  # How many primary keys (rows identified by the spec's PK)
  # exist in production but not in the new version.
  # max: 0 means "no row should disappear".

  altered_columns: [gross_revenue, order_count]
  # Exact list of columns whose values will change.
  # If in the diff a column NOT in this list shows a difference,
  # the PR is blocked. This prevents accidental changes in columns
  # the agent didn't intend to alter.

  metrics:
    gross_revenue: {delta_pct: {min: 0.0, max: 0.8}}
    # For each metric in the spec, the expected percentage range of variation.
    # Example: gross_revenue should increase between 0% and 0.8%.
    # If the actual variation is -1% or +2%, the PR is blocked.
    #
    # A model that does not exist in production has no percentage to predict.
    # Declare the value itself, inside the diff's window, written around the
    # number in external_validation:
    #   gross_revenue: {value: {min: 14000000, max: 14400000}}
    # A metric declares one of the two, never both. row_delta is then the row
    # count itself, and altered_columns is empty.

When it is mandatory: For every model whose code the PR changes. Stage C cannot start without it, and stage E has nothing to compare against without it — a model that reaches the diff with no pre-registration is not a model that fails the comparison, it is a model nobody compared. Deleting the prediction must not be cheaper than missing it.

Whose it is: A pre-registration belongs to one pull request. It is written on the branch, for the change that branch makes. One that is identical to what main already has is the previous change's prediction — made against another production, for another reason — not this one's, and it counts as absent: the agent replaces it, it does not inherit it. After the merge it stays in the .yml as the record of what was predicted, until the next change to that model replaces it.

Validation: The pre-registration is validated by JSON Schema in CI (stage D). If the format is wrong, fields are missing, or intervals are open, CI fails.

---

Stage C: Code (Agent)

What it is: The agent writes the SQL code, tests, and everything needed to implement the spec. It follows 8 rules, documented in the AGENTS.md file (which is protected by Control 5 — only humans can modify it).

The 8 agent rules:

Each rule below must have an infrastructure mechanism that enforces it. The text rule exists only for the agent to understand the intention; the mechanism exists so that the rule works even if the agent ignores it.

---

Stage D: Automatic CI (on every push)

What it is: A CI pipeline that runs automatically every time the agent pushes to the PR branch. Must complete in less than 15 minutes.

What runs (in this order):

# 1. Static validations (pre-commit hooks)
pre-commit run --all-files

Pre-commit runs:

• JSON Schema: validates that the spec, the sensitive field, the pre-registration, and mandatory tests exist and are in the correct format.
• Gitleaks: detects leaked secrets, including custom rules for email and CPF.
• Anti-fraud gate: the Control 5B script runs on the bot's commits.

# 2. Build with sample
dbt build --select state:modified+ --defer --state ./prod-artifacts --sample "30 days"

The build includes:

• Fusion in static_analysis: baseline — detects non-existent columns and wrong types before running any query (static SQL analysis).
• Unit tests generated from the spec's edges.
• Mutation check on every marts model whose SQL the PR changed: the model's SQL is mutated in a fixed, deterministic list of ways (a comparison flipped, a where predicate dropped, a sum turned into a max, a join type changed, a coalesce removed, a literal altered), and its unit tests must fail on every mutant. It runs through unit tests only, so it scans nothing: every input is mocked, the compiled query reads no table, and one dbt test invocation covers every mutant of a model. A surviving mutant blocks the PR unless a human has listed it as equivalent in tests/mutation_equivalents.yml, under the protected tests/ and read from the branch the PR targets. A changed model with no unit test blocks: it has nothing that could tell it from a wrong one.
• Uniqueness test of the spec's primary_key.
• Minimum count test — the threshold is adjusted proportionally to the sample window (e.g., if the sample is 30 days and the table has 365 days, the minimum threshold is 30/365 of the full threshold).
• Contracts on marts models (ensure columns and types are correct).
• dbt-project-evaluator — detects structural problems in the project.

Why run hooks in CI if they already run locally: Because git commit --no-verify skips all local hooks. If someone (or the agent) uses that flag, the hooks don't run. CI ensures that validation happens anyway.

---

Stage E: Diff + human review (once per PR)

What it is: A full dbt build (without sample) followed by a numerical diff between the new version and current production. Runs when the PR is marked as ready-for-review, and again on every push after that — Control 1 dismisses an approval on push, and a diff of code that has since changed is worth the same. While the PR is a draft it does not run, which is why the agent opens the PR as a draft and marks it ready when stage C is done.

The diff is produced by automation, deterministically — the same build, the same closed event_time window, the same comparison, every time. Neither a human nor the agent composes it ad hoc, and neither one gets to choose which numbers appear. The human's job at this stage must be only to read the diff.

What runs (in this order):

Step 1 — Build with full data

The build runs in a separate schema called ci_pr_<n>_full:

# For standard models: builds only the modified model
dbt build --select state:modified --defer --state ./prod-artifacts

# For critical models: builds the modified model AND all models that depend on it (downstream), using the "+" operator
dbt build --select state:modified+ --defer --state ./prod-artifacts

Why critical models use state:modified+ (with the +): Without the +, downstream models would be built on top of production intermediate data (via --defer), not on the modified version. The diff would show differences only in the modified model, not in the marts that consume it. With the +, the entire downstream chain is rebuilt, and the diff captures the full effect of the change.

Step 2 — Aggregate data diff

Using Recce or dbt-audit-helper in summary mode (never in mode that shows individual rows of sensitive columns):

• The diff is calculated over a closed event_time window, identical on both sides (production and new version). This is essential: if production has data up to yesterday and the new version has data up to today, the "today" rows would appear as false differences.
• The diff publishes: row count, removed PKs, columns with altered values, and the value of each metric defined in the spec.
• For a model production does not have there is no delta to publish: the diff publishes each metric's value itself, in the window, and compares it with the value interval the pre-registration declared (stage B).

Step 3 — Comparison with the pre-registration

Each diff number is automatically compared with the intervals declared in the pre-registration (stage B). The PR is blocked if any of these conditions is true:

• A number is outside the declared interval (e.g., row delta is 15,000, but the pre-registration said max: 12000).
• A column shows a difference but is not in the pre-registration's altered_columns list.
• A metric pre-registered by value lands outside its interval — or a model production does not have was pre-registered by percentage, when there is no production number to take a percentage of.
• The type is refactoring but some delta is not zero.

Step 4 — Reconciliation (critical models only)

For models with tier: critical, the reconciliation query (reconciliation_query) runs on full data and compares the result with the declared tolerance (reconciliation_tolerance). If the difference is greater than the tolerance, the PR is blocked.

[!CAUTION] This is the only gate capable of detecting the case where the AI incorrectly assumed the meaning of a column. If the agent thinks order_total is gross but it's actually net, the unit tests pass (they test what the spec says), but the reconciliation against the accounting system fails.

Step 5 — Human review: three readings

The Author (and the Partner, if the model is critical) reads exactly three things.

Under the three questions, CI prints one line per edge of the spec: the unit test that names it, and how many rows it is given and expects, so the third reading starts from that list rather than from the yml. The mutation check's survivors, if any, are printed next to it.

Approval rules:

• Standard model: the Author approves.
• Critical model: a Partner (≠ Author) approves. CODEOWNERS enforces this.
• Macros, incremental models, and models/semantic/: always go through human approval, regardless of tier. CODEOWNERS enforces.

---

4. Routines — three step-by-steps

Everything above says what each stage owes. This section says what a person does on a Tuesday, in order, in the three situations that happen every week.

A new model

1. The Author asks for a draft. The agent reads docs/profile/, runs the aggregate-only queries of Control 4, and proposes the six fields of the spec (for a critical model, the reconciliation query, its tolerance and the external anchor too). Nothing in this step is a gate; it exists so that the Author writes as little as possible.
2. The Author reads and approves the six fields, corrects what is wrong, and the spec reaches main before any code. The recommended path: the agent pushes the draft on a branch and the human opens that pull request (spec only; for a critical model, the reconciliation query too), which CODEOWNERS decides. A spec may also be born on the agent's own branch, in a commit of its own before any code; the gate then tells the Author which commit to read, and the spec does not change again on that branch.
3. The agent pre-registers (Stage B) and works in the loop of Rule 5: one change, check, gate, the unit tests, commit or revert. One unit test per edge, naming it; every input mocked.
4. dbt build once, the pull request opened as a draft and marked ready when Stage C is done. Stage D and Stage E run.
5. The Author reads the three questions of Stage E, the edge list and the mutation check; a Partner approves a critical model.

A model that already has a spec, whose business rule changed

The spec is the human's, and the gate blocks any change to it on the agent's branch. So a business-rule change reaches an existing model in two pull requests, in this order:

1. The Author asks for a draft of the change. The agent reads the current spec and the profile, and proposes: the edges that change and the edges that go, the new metric definitions, the tier if it changes, and for a critical model the new reconciliation query, tolerance and external anchor. It also lists the tests that encode the old rule: the unit tests whose config.meta.edge names an edge that is going away, and the data tests the new rule contradicts (an accepted_values list, a relationships).
2. One spec pull request, opened by a human. It changes meta.spec, removes or rewrites the obsolete unit tests and data tests, and changes the reconciliation query. The agent may push the branch; the human opens the pull request, because the gate is required on the pull requests the agent identity opens and advisory on a human's, and every one of its rules about specs, tests and reconciliations fires here by design. CODEOWNERS decides it, and the advisory gate output is the list of what changed.
3. Merge the spec pull request first. main now carries the new spec and no test that contradicts it.
4. The agent's pull request, exactly as for a new model: a fresh pre-registration (type: data_change, a reason that names the business rule, intervals the Author can judge), the code, one unit test per new edge, the loop, draft until Stage C is done, then ready for review.
5. Stage E reads the three questions against the new spec; for a critical model the reconciliation runs against the new external anchor.

A spec, a reconciliation and the tests that encode an old rule change in a pull request a human opens, before the agent starts. The agent may push that branch; it does not open that pull request.

The agent's loop

tcr.sh "message" runs check, gate and the unit tests. Green: the change is committed. Red: the working tree goes back to the last commit and a strike is counted; a green step resets the count; the fifth consecutive strike stops the agent with a message that says to ask a human. The unit tests read no table (every input is mocked), so the loop costs nothing in the warehouse however many times it runs; the build runs once, in CI. Nothing the agent can do inside the loop weakens a test: gate is inside it, and Rule 3 says the code is what changes.

---

5. References — where these ideas come from

None of these is about dbt or agents. The framework is what they become when pointed at both.

---

License

MIT © Matheus Miloski. Contributions are welcome — see CONTRIBUTING.md.