Retrieved article excerpt
Open article · Retrieved 2026-09-17T13:22:30.064725+00:00
# Spec-Lock-Diff
**English** · [Português (pt-BR)](https://github.com/miloskimatheus/spec-lock-diff/blob/main/README.pt-br.md)
Spec · Lock · Diff
[built for dbt](https://camo.githubusercontent.com/3d59bfa3ae2ebd8ec96f769c04bf806addd24f116f6197631c12f451f807af27/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f6275696c74253230666f722d6462742d413334463245)
[warehouse: snowflake, bigquery, databricks](https://camo.githubusercontent.com/1a34a6921f0c353060b0a33e277d968817b178355022ffe9d128b57642bb5474/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f77617265686f7573652d736e6f77666c616b65253230254332254237253230626967717565727925323025433225423725323064617461627269636b732d343434643536)
[license MIT](https://github.com/miloskimatheus/spec-lock-diff/blob/main/LICENSE)
[PRs welcome](https://github.com/miloskimatheus/spec-lock-diff/blob/main/CONTRIBUTING.md)
[docs in EN and pt-BR](https://camo.githubusercontent.com/7d32496fdc02eec08cfafc3ff25e5706363975f09817e6063a39a09f398dd2ee/68747470733a2f2f696d672e736869656c64732e696f2f62616467652f646f63732d454e25323025433225423725323070742d2d42522d384135413042)
A framework for dbt development using AI agents. The goal is to reduce the main risks that arise when an agent writes SQL:
Three risks: wrong results that look right, leakage of sensitive data, unexpected financial costs
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/`](https://github.com/miloskimatheus/spec-lock-diff/blob/main/tools/README.md)**: three commands in one Python package, no network and no warehouse.
**Want to see it before you read all this?** [`examples/quickstart`](https://github.com/miloskimatheus/spec-lock-diff/blob/main/examples/quickstart/README.md) 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](https://github.com/miloskimatheus/spec-lock-diff/blob/main/tools/README.md#1-install) has the five rungs, each green on its own.
---
## Table of Contents
0. [Roles — who does what](https://github.com/miloskimatheus/spec-lock-diff/#0-roles--who-does-what)
1. [Manifesto — 3 principles](https://github.com/miloskimatheus/spec-lock-diff/#1-manifesto--3-principles)
2. [Building the lock — 5 mandatory controls](https://github.com/miloskimatheus/spec-lock-diff/#2-building-the-lock--5-mandatory-controls)
3. [The development process (routine) — 5 stages](https://github.com/miloskimatheus/spec-lock-diff/#3-the-development-process-routine--5-stages)
4. [Routines — three step-by-steps](https://github.com/miloskimatheus/spec-lock-diff/#4-routines--three-step-by-steps)
5. [References — where these ideas come from](https://github.com/miloskimatheus/spec-lock-diff/#5-references--where-these-ideas-come-from)
**Seven words this document uses before it defines them**, so you can read straight through:
| Word | In one line | Defined in |
| --- | --- | --- |
| **Spec** | What the model must do, written by a human into the model's yml before any code exists. Six mandatory fields. | [Stage A](https://github.com/miloskimatheus/spec-lock-diff/#3-the-development-process-routine--5-stages) |
| **Pre-registration** | The agent's numeric prediction — how many rows will move, how far each metric may drift — committed before it writes SQL and before it can see any result. The term is borrowed from clinical trials, and so is the reason. | [Stage B](https://github.com/miloskimatheus/spec-lock-diff/#3-the-development-process-routine--5-stages) |
| **Diff** | The measured difference between production and the pull request's build, read as numbers rather than rows. | [Stage E](https://github.com/miloskimatheus/spec-lock-diff/#3-the-development-process-routine--5-stages) |
| **Gate** | A deterministic check that blocks a pull request. Never an LLM: the same input gives the same verdict every time. | [Control 5](https://github.com/miloskimatheus/spec-lock-diff/#2-building-the-lock--5-mandatory-controls) |
| **Critical model** | One that feeds business decisions, financial reports or executive dashboards. It owes more than a standard model: a second reviewer, a reconciliation, a rebuild of everything downstream. | [Stage A](https://github.com/miloskimatheus/spec-lock-diff/#3-the-development-process-routine--5-stages) |
| **Reconciliation** | The model compared against something that is *not* the model — a closing spreadsheet, a source system — inside a tolerance the spec declares. | [Stage E](https://github.com/miloskimatheus/spec-lock-diff/#3-the-development-process-routine--5-stages) |
| **Protected path** | A file the agent may not touch, enforced by CODEOWNERS and a gate rule, because editing it would let the agent change the rules that judge it. | [Control 5](https://github.com/miloskimatheus/spec-lock-diff/#2-building-the-lock--5-mandatory-controls) |
---
## 0. Roles — who does what
This framework defines four roles.
A human writes the spec, the agent runs inside an enclosure built by the Platform, a human reads the diff
| Role | Who they are | What they do |
| --- | --- | --- |
| **Platform** | Infra/platform team | Configures the setup controls (section 2) one time. After that they only need to make sure it keeps working. |
| **Author** | A human on the team | Writes the model spec, triggers the agent and reads the diff. Is responsible for the PR. |
| **Partner** | Another human (≠ Author) | Must be called in to approve PRs of critical models. |
| **Agent** | The AI (LLM + tools) | Starts by writing the numerical pre-registration, then writes the code and tests. |
---
## 1. Manifesto — 3 principles
*Why* the framework is being built. All rules derive from them.
The three principles feed the framework: principle 1 shapes Spec and Diff, principle 2 shapes Lock, principle 3 shapes Diff
| # | Principle | Why it holds | What follows from it |
| --- | --- | --- | --- |
| **1** | **In SQL, a bug doesn't give an error** It returns a number that is plausible, and wrong. | Get a `JOIN` wrong in Python and the program breaks. Get it wrong in SQL and the query runs normally, returns `16,894,203.11`, reports `1 row · no error`, and never mentions the rows it duplicated. | The human **decides before**, by writing the spec, and **checks after**, by reading the numerical diff. Between those two moments the human does nothing — the agent works alone in the middle. |
| **2** | **Limits must be configured in the infrastructure** Not written down and hoped to work. | "Do not access sensitive data" in an `AGENTS.md` is an *instruction*, not a control — the agent can ignore it, forget it, or interpret it differently. `REVOKE USAGE ON SCHEMA raw` is a control. | Real control means **denied database permissions**, a **resource monitor** that shuts the warehouse down, a **branch protection** that prevents pushing to `main`. If the agent tries to violate, the system blocks — regardless of what the prompt says. |
| **3** | **Checks must be deterministic** The same inputs must always produce the same results. | LLMs are stochastic by nature, and that is fine while *generating* code — the same prompt yields three different joins. It is not fine while *judging* it. | Every verification gate — tests, diffs, reconciliations — is deterministic. An LLM is never the final judge of "is the code correct?". The judges are **automated tests, numerical diffs, and human eyes**. |
---
## 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/`](https://github.com/miloskimatheus/spec-lock-diff/blob/main/tools/README.md#1-install) — 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.
The five controls and what each one stops
---
### 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:**
Agent permission by data layer: no access to raw, masked read on staging and marts, no write to production, read and write in its own PR schema
| Data layer | Agent permission |
| --- | --- |
| `raw` (raw data) | **No access.** Not even `SELECT` or `DESCRIBE`. |
| Staging and production marts | **Read with masking.** Sensitive columns are masked (see below). |
| Production (write) | **Prohibited.** The agent's `profiles.yml` has no `prod` target. It cannot write to production even if it tries. |
| Working schema | **Read and write** in an exclusive schema: `ci_pr_<PR_number>`. Created when the PR opens, dropped automatically when the PR closes (merge or abandonment). |
Masking of sensi