We built a data warehouse with an AI agent. The hard part wasn't the SQL

Kitchen Warehouse is an Australian retailer. Everything the business does, meaning orders, invoices, refunds, purchase orders, receipts, stock movements, transfers, payments and journals, is recorded in one ERP. Reporting sat on top of a legacy stack of dashboards fed by queries that had accumulated business logic over years. The logic worked. It also lived in DAX measures and stored procedures that nobody could confidently explain end to end.

We wanted a proper warehouse: raw ERP data landed in BigQuery, transformed with dbt, exposed as a star schema that reporting tools read directly. Standard medallion architecture, nothing exotic.

The unusual part is how we built it. We used Claude Code as a full collaborator, writing models, writing tests, reviewing pull requests, debugging failed builds and auditing its own conventions. Three months in, the warehouse has 145 models, 189 custom tests, 4 snapshots and 11 conformed dimensions across 10 Gold datasets.

The reason for doing it at all was narrower than the technology. Answers lived behind query expertise: getting a number out of the ERP meant either writing the query yourself or waiting for someone who could. We wanted the wider team to ask a question in plain language and get an accurate, sourced answer.

What is actually hard about building a data warehouse with an AI agent?#

Getting an agent to produce SQL is easy and was never the problem. The hard part is that a competent-looking model in the wrong layer, with the wrong partition key, missing a delete filter, silently costs money or corrupts a number a buyer uses to place a purchase order.

The work was not prompting. The work was building an environment where the wrong answer is hard to produce and impossible to merge. Four things made that work, in this order.

How do you decide the architecture before an agent touches it?#

Decide it with humans, against real source data, and commit the decision in writing before the first model is generated. An agent will build a bad architecture beautifully and very fast. Architecture is the one thing you cannot delegate to something that has never seen your business.

The first thing we did not do was ask for a warehouse. We spent the first stretch on a narrower question: what does this business actually measure?

We interrogated the ERP directly. Rather than working from documentation, we pulled a live inventory of every raw table the ingestion pipeline had landed, with columns, types, sizes, partition metadata and population rates. That inventory is committed and refreshed on a schedule, so it reflects what is in the warehouse today rather than what someone believed a quarter ago. Over it sits a column-level field catalogue, roughly 8,500 fields, so that when a model renames a source column there is a document saying what that column meant in the source system.

We ran a transaction-type census. Every transaction type in active use, mapped to the business event it records. This produced the finding that shaped the whole Gold layer: the ERP captures eight distinct business processes, not one. Order capture, revenue, fulfilment, returns, procurement, inventory, cash and general ledger. Our original sketch had three fact tables and covered about 35 per cent of transaction volume. Eight processes, properly mapped, covers all of it.

We reverse-engineered the legacy reports. This was the highest-value work and it is the part most teams skip. The old revenue measure summed three transaction types. The correct definition at this business is four: invoices, cash sales, credit memos and cash refunds. Cash refunds had been omitted, which understated returns by roughly half a million dollars a year, and mattered most in exactly the channels leadership cared about. We also found that returns follow two structurally different paths depending on how the sale was paid, which means a single returns fact table would have been wrong.

None of those three findings could have come from a prompt. They came from querying the source, reading the legacy logic, and asking finance and merchandising what they meant by a word.

Then we chose the shape and wrote down why. Star schema, not one big table. One fact per business process, per Kimball. Eight processes became nine domain marts organised by business process, never by source system, plus one shared mart holding the conformed dimensions.

We also decided the dbt Gold layer is the semantic layer. Revenue is defined once, in one model. Every consumer reads that model and gets the same number.

All of this went into a single architecture document that ranks above every other document in the repo. When a later file disagrees with it, that file is wrong. That sounds bureaucratic until you have four humans and an agent making decisions in parallel, at which point an explicit authority order is the cheapest conflict-resolution mechanism available.

How do you make an agent follow the architecture every time?#

Stop treating context as something you type and start treating it as something you build, version and enforce. The repo becomes the context. Not once, in a good session, but on the two hundredth model, on a Friday, in someone else's hands.

The repo now carries a deliberate context stack: an operating model describing how any agent works here and the hard boundaries it must not cross; a project context file carrying layer contracts, naming rules, the revenue definition, the partition doctrine and a growing section of hard-won gotchas; 18 tool runbooks; 16 reference documents; 8 personas describing the humans affected by a change; 24 skills; 8 subagents; and 15 slash commands.

The distinction between those last three matters and took a while to get right. A skill is the how. A subagent is the who. A slash command is the when. Confusing them produces a repo full of documents nobody invokes.

The most useful thing we learned about writing skills#

Every good skill in our repo has a section called Common rationalisations. Two columns: the excuse for skipping a step, and why the excuse is wrong.

This is the difference between a document that describes a process and one that survives contact with an agent under time pressure. Models are very good at constructing a locally reasonable justification for a shortcut. If you pre-empt the specific justification in writing, the shortcut stops being available.

The gate we would tell everyone else to copy#

Work moves through a fixed sequence, each stage with an owner and an artefact.

01spec02validate-spec03plan04build05test06review07pull request08ship

Write the specification

What is being built, the contract it must satisfy, and why now. Options considered and alternatives rejected are recorded here, which is what makes the spec worth reading a year later.

Owner

Engineer, with the agent drafting

Artefact

A versioned spec file

The command names are not ours. The spec, plan, build, test, review and ship sequence comes from Addy Osmani's agent-skills, which packages senior engineering workflows as skills and slash commands for coding agents. We took the lifecycle shape from there and added the one gate our own data problems demanded.

validate-spec is the stage we added later. We shipped a spec for a large revenue fact table, moved to planning, started building and then hit three blocking problems that only appeared when someone queried production. A column the spec depended on was 45 per cent null. An assumed enum had values nobody had listed. A foreign key we intended to test had orphans.

So we inserted a mandatory gate between specification and planning whose only job is to check the spec against reality. It enumerates every data assumption the spec makes, meaning column existence, population rate, enum values, foreign-key integrity, grain, scan cost and sign conventions, and runs a verifying query for each. It has explicit block conditions: any referenced column missing, any non-optional column under 5 per cent populated, any unmapped enum value, any orphaned key, any scan-cost estimate off by more than 5x. The evidence is embedded in the spec, so a year later it shows the decision and the data it rested on.

Specifications drift from reality, and an agent cannot see the drift because it is reasoning from your document rather than from your data.

A convention that relies on remembering is not a convention, so the workflow is enforced in three independent places: the context file the agent loads every session, a pre-filled checklist in the pull-request template, and the reviewer subagent, which fails any pull request modifying model SQL without a spec link. Any one can be forgotten. All three being forgotten is unlikely.

The by-product is 151 specification documents, which is now the most valuable asset in the repo: the durable record of why the warehouse is shaped the way it is, in a form both humans and agents read.

How do you give an AI agent access to production data safely?#

Give it eyes, not hands. Scope read-only at the credential layer rather than by instruction, allowlist the command line, attribute every query to a named human and handle personal data at ingestion so it is absent from the warehouse rather than present and filtered.

An agent reasoning about a warehouse it cannot see will invent column names. That is not a character flaw, it is the predictable result of asking someone to describe a room with the lights off. Our posture: read wide, write narrow, attribute everything.

Structured reads over MCP. We wired read-only servers for warehouse schema and metadata in both dev and production, for the transformation platform's job and run history, and for the ERP. Each is scoped read-only at the credential layer, not merely by convention, so a bug in a server cannot escalate into a write.

Command line behind an allowlist. The repo commits an explicit allow list and deny list. Reads, dry runs and cost-capped queries are allowed. Anything mutating shared state is denied: copy, remove, create, load, extract, force push, hard reset, run-operation, because a macro can do anything including dropping a table, full refresh, because it can rescan terabytes, and every command targeting the production profile.

The detail I am most pleased with: raw ad-hoc query is deliberately not on the allow list. Not because we forbid queries, but because we want a prompt every time. A cost-attributed wrapper is allowlisted instead. The friction is the feature. It steers every query into the labelled path without blocking real work.

Per-user service accounts, not one shared robot. Each engineer gets their own dev and production identities, so the audit log shows which person the agent was acting for. Authentication is by impersonation rather than downloaded key files: nothing sensitive sits on a laptop, tokens expire in an hour, revocation is instant.

Personal data is handled at the boundary. Personal fields are blocked or hashed at ingestion, so names, emails and phone numbers are absent from the warehouse rather than present and filtered. There is no query, permission mistake or wildcard that can expose what was never loaded. SELECT * is banned in the Gold layer and enforced by a commit hook, precisely because a wildcard is how a newly-added personal column silently reaches a dashboard.

Error handling is the real product. When a query returns something unexpected, the agent has to explain why rather than fail quietly. We built explicit error paths for every tool, because an agent that found nothing and an agent that failed to look are indistinguishable to whoever reads the output.

The architecture scales for a duller reason than it sounds: each new capability is a new server rather than a change to an existing one. Purchase order recommendations and anomaly detection in sales patterns are the next two, and neither requires touching what already works.

The safety of an agent with live access is a function of what its credentials permit, not what its instructions say. Scope at the credential layer. Use the instruction layer to route work through the paths you want to be able to audit.

How do you stop an agent's mistakes reaching production?#

Build a ladder of gates where each tier catches a class the tier below cannot, and make every gate fail loudly. A gate that passes silently when it did not actually run is worse than no gate, because it manufactures confidence.

Everything runs against a dev project first, mirrored from production with zero-copy clones so dev has real data at real volume for effectively no storage cost. Then five tiers:

01Commit hooks~5 sec

24 hooks across five sources, running before the commit is written. Standard hygiene and secret detection, SQL linting through a templater that performs a real project compile so what passes locally passes in the pipeline, then eight hooks encoding our own rules.

Catches

• Secrets and credentials about to be committed
• Surrogate keys not built with the standard macro
• Wildcards in the Gold layer, which is how a new personal column reaches a dashboard
• Facts that fail to declare partition, clustering and filter-requirement config

02Parse and manifest gates~1 min

A dependency install and a parse, catching broken references and invalid YAML without touching the warehouse. Then four Python gates that read the compiled project manifest and fail fast on things a data test structurally cannot catch.

Catches

• Models flagged as needing unit tests that have none
• Duplicate unit-test names, which collide only under one selection pattern
• Models tagged for the daily build whose upstreams are not, building a child without its parent
• Column descriptions over the warehouse's 1,024-character limit, which fail the build rather than a test

03Slim CI on changed models3 to 5 min

Build only what changed and everything downstream, defer the rest to the last successful production manifest, and short-circuit every table creation to zero rows. This validates the real SQL, the real DDL and the real tests through the actual query engine while scanning essentially no data.

Catches

• SQL that compiles but fails against the real engine
• Tests that pass in isolation and fail in the graph
• Contract violations between layers
• Collisions between concurrent pull requests, each of which gets its own scratch dataset

04Platform CI with real dataLonger

The transformation platform runs its own validation against a scratch dataset at real volume. This is where tests that depend on actual values live, rather than on structure.

Catches

• Assertions about distributions, ranges and row counts
• Reconciliation against live source data
• Anything a zero-row build cannot exercise

05Production build6x daily

The scheduled production run, which additionally executes the governance tests that need production metadata to be meaningful.

Catches

• A fact claiming it is small enough to skip partitioning when the storage metadata says otherwise
• Snapshots and snapshot-reading tests, excluded earlier on cost grounds
• Drift between the committed pipeline specification and the live configuration

Tier 2 is worth dwelling on because it shows where these gates come from. A column description exceeding the warehouse's 1,024-character limit fails the build rather than a test, and is invisible to parse. It failed a slow pipeline stage twice before anyone understood why. It is now caught in a minute by twenty lines of Python reading a JSON file.

Automated review that actually blocks#

Every pull request touching models, macros, tests, seeds, snapshots or project configuration triggers a purpose-built reviewer subagent. Not a generic code review: 13 axes calibrated to this warehouse, reading the architecture guide, the field catalogue and the live source inventory in a documented authority order.

Three details make it useful rather than decorative.

The verdict is a gate. Originally the review posted a comment and the check went green regardless. Then a pull request came through with zero code-correctness problems but a genuine process failure flagged as critical, and the check sat green and would have allowed the merge. Now the workflow parses the verdict and fails on request-changes. An advisory reviewer is one people learn to skim.

It fails loud when it produces nothing. A review that was expected and silently posted nothing used to look exactly like a clean approval. Absence of a finding and absence of a review are different states and must not render identically.

It abstains honestly in one case. A pull request editing the review workflow itself cannot run the reviewer, because the token exchange requires the workflow file to match the default branch. That is a deliberate security control, not a bug: it stops a pull request rewriting the reviewer to exfiltrate a token. So the gate abstains with an explicit notice that a human must review manually. It does not silently pass.

We do not fully trust it, and that is designed in. It is a gate rather than an oracle, running alongside four mechanical checks that need no judgement. Each of its 13 axes traces to a named source, so a challenged axis has a citation behind it rather than an opinion. We also had to build a separate discipline for responding to reviews, because a suggestion applied verbatim once introduced a defect. The rule now: reproduce the claim, check it against the authority, ground any volume claim in real data, then follow it, resolve it better, or set it aside with the reasoning recorded. A confident reviewer can be confidently wrong, and 'the reviewer said so' does not survive a post-mortem.

A human still approves every merge.

Isn't this over-engineered for a four-person team?#

Month one looked like no progress on a burndown chart. Almost no models shipped, all environment. Two things justify it, and the second is the one that convinced me.

The failure being guarded against is not a broken build. It is a wrong number nobody notices for a quarter, off which a retail buyer places purchase orders. And we have the counterfactual: the failures that cost us most were the classes where a gate did not yet exist. One configuration mismatch silently deleted a month of a fact table. A vendor-side change took the pipeline down for about a day. In both cases the fix was a gate, and building it first would have been strictly cheaper.

We hold the opposite discipline too, because a gate that costs more than it saves gets routed around. There is a written rule that a commit-hook chain longer than about 15 seconds is one developers will skip, so a new hook has to earn its place: add it if the failure happened twice, or if it cost more than 30 minutes the first time. Otherwise document it and move on.

What went wrong?#

Plenty, and it is catalogued. We recorded 20 distinct pipeline failure classes with root cause and the control that now catches each one. Four of the patterns transfer well beyond this warehouse.

The second one is why this section exists. The build succeeded and the data was gone: tens of thousands of order rows and over a million line rows. No amount of care prevents a destructive interaction between two individually correct choices. Only a check that understands the interaction does.

The first is worse than a wasted round trip. Everyone had been reading 'lint passed' as complete when it meant 'passed on the subset we could render'. Silent partial coverage manufactures confidence, which is more dangerous than no coverage, because no coverage at least prompts caution.

The third is the highest-value control type in the whole system, and the one I would build first somewhere else: make an external change that used to arrive as a confusing symptom arrive instead as a named, actionable failure.

One more, because it is the failure most people expect to hear about. Our first attempt at inventory forecasting produced confident and incorrect projections, because the model had no access to seasonal adjustment data and nothing in the output signalled the gap. We now state what the model lacks as explicitly as what it holds, and that line is part of every specification.

What is still broken in our own setup?#

Four things, found by auditing our own gates rather than by an incident, which is the only comfortable way to find them.

Eight of our 12 governance tests carry no tag for the scheduled production build. They are correct tests that run only when a pull request happens to select them, which is not where slow drift gets caught.

A cleanup workflow was green and dead. It reported success while doing nothing, and thousands of orphaned dev datasets accumulated behind it. A green check is not evidence of a working check. Verify the effect, not the exit code.

Warning-severity tests bury new signal. We have many times more warn-severity tests than tests with a growth threshold, so a warning that re-emits the same expected count every build teaches everyone to skip the category. Where a count can never legitimately reach zero, pin the accepted level and warn only on growth.

Our documented branch-protection rule had drifted from the live configuration. The same lesson as the pipeline-configuration guard: configuration outside version control drifts silently, and something has to check it on a schedule.

Publishing that list is the point rather than a caveat to it. Auditing your own gates and finding them wanting is not a failure of the approach. It is the approach.

What is the one idea that transfers?#

Encode the rule where it will be enforced, not where it will be read. A rule in a document is a hope. The same rule as a commit hook, a pipeline gate, a graph-walking test and a reviewer axis is a property of the system.

We learned to write rules at three or four altitudes at once: the human-readable explanation, the local hook that catches it in seconds, the pipeline gate that blocks the merge and the reviewer that explains why in the pull request with a copy-pasteable fix.

That is why the repo has 12 governance tests whose subject is the repo itself rather than the data. They check that every fact declares its partitioning, that snapshot readers filter correctly, that incremental lookbacks match partition granularity, that unpartitioned facts really are small. They are unit tests for our own conventions, and they are the reason a convention written in May still holds in August across four humans and an agent.

The second idea is smaller and more practical. Every failure that cost more than half an hour became a permanent artefact. We catalogued 20 distinct pipeline failure classes with root cause and what catches each one now. That table is the highest-value document in the repo for a new joiner, human or agent.

The most instructive failure: an incremental strategy that replaces whole partitions, combined with a lookback window finer than the partition grain. Each is fine alone. Together they overwrote a month's partition with a few days of rows. The build succeeded and the data was gone. No amount of care prevents a destructive interaction between two correct choices. Only a check that understands the interaction does.

What would we do differently?#

In order, and each of these cost real time.

1. Build the data-validation gate on day one, before the first Gold model rather than after the first expensive rebuild.
2. Unify on the authoritative lint templater from the start. The fast one is a trap: it is fast because it is incomplete, and the incompleteness is invisible.
3. Pin framework versions explicitly everywhere from the first commit. Treat alignment between the pipeline and production as a correctness property, because a mismatch corrupts change detection and makes an unrelated pull request rebuild the whole graph.
4. Tag governance tests into the scheduled build as they are written. A gate that runs only sometimes is a gate you will misremember as always.
5. Adopt nullable-first for every column addition. A required column added to an existing incremental fact broke every open pull request, including ones touching unrelated models.
6. Set a growth threshold instead of a bare warning from the first irreducible test. Retrofitting hundreds is worse than setting the pattern once.
7. Write 12 procedural skills rather than 24. Some of ours are reference documents wearing a procedure's frontmatter. A skill is a procedure, not a briefing.

What would you tell someone starting on Monday?#

1. Spend the first week on architecture, not on prompting. Query your source. Reverse-engineer your existing reports. Write down the decision and its authority rank.
2. Write the anti-rationalisation table. For every step you expect to be skipped, write the excuse and the rebuttal.
3. Put a data-validation gate between specification and planning. Specifications drift from reality and the agent cannot see it.
4. Scope at the credential layer. Read-only means the credential is read-only, not that the instructions say read-only.
5. Make the friction point the thing you want to audit. Not allowlisting raw queries produced better behaviour than any instruction about cost would have.
6. Automate review, then make the verdict block the merge.
7. Make every gate fail loudly. Absence of a finding and absence of a review must never look the same.
8. Encode every rule at more than one altitude. Document, hook, pipeline gate, reviewer.
9. Turn every expensive failure into an artefact. Cheap to write once, compounding thereafter.
10. Keep humans on the irreversible decisions. A person ruled on the revenue definition, chose the star schema, and signs off every production deployment. That is where accountability belongs.

What did three months actually produce?#

About three months, from an empty repository to a governed warehouse.

Warehouse: 145 models (61 staging, 12 intermediate, 72 marts), 4 snapshots, 18 macros, 13 seeds. Eleven conformed dimensions across ten Gold datasets, organised by nine business domains.

Testing: 189 custom test files on top of the standard generic tests, of which 12 are governance tests whose subject is the repo's own conventions.

Context: 24 skills, 15 slash commands, 8 subagents, 18 tool runbooks, 16 reference documents, 8 personas, 151 specification documents.

Process: 24 commit hooks, 7 pipeline workflows, 20 catalogued failure classes, five tiers of validation.

This is not a claim that Claude built the warehouse. A person decided the star schema, ruled on the revenue definition, signed off every production deployment and owns the failures. The claim is narrower and more useful: the quality of what an agent produces is set by the context you give it before it starts, and that context can be engineered, versioned and enforced like any other part of the system.

The warehouse is the deliverable. The environment we built around the agent is the thing we would rebuild first if we had to start again.

If you are doing something similar, there is more on this area under AI-augmented operations and composable architecture. I am always interested in comparing notes, so get in touch.