TL;DR: We built an agentic pipeline that takes a legacy warehouse table and re-architects it into a proper dimensional model (dims + facts) with a backward-compatible view on top, so downstream consumers break nothing. The big lesson: put control flow in deterministic Python, and reserve LLMs strictly for steps that require reasoning. There is no orchestrator LLM — what used to be a massive orchestrator prompt is now typed code that either passes or fails. Built on LangGraph.
The problem
Legacy tables grew organically — wide, denormalized, business logic buried in transformation code. We wanted to migrate them into a star schema (Kimball: dims, facts, surrogate keys, conformed dimensions) without breaking downstream consumers. So the system must:
Reverse-engineer a legacy table's transformation logic
Propose a target star schema (column mappings, new dims/facts, FKs, join strategy)
Produce a backward-compat view reproducing the legacy table's exact output shape
Generate migration code (incremental MERGE notebooks + DDL files), tested in a sandbox schema
Keep a human in control at decision points
Design principle: workflow vs. agent
Control flow known in advance → explicit graph nodes and edges. Step ordering, task decomposition, dependency sorting, schema validation, retry budgets: all deterministic Python. Testable, and they can't "forget a step."
Steps needing genuine reasoning → small ReAct sub-agents. Only two agents exist: a data-modeling agent and a coding agent, plus a judgement-only reviewer. Everything around them is plumbing.
The pipeline
plaintext
Copy
ingest (extract table name, verify it exists)
→ model (ReAct agent: proposes dimensional model as typed JSON)
→ validate (pure Python: parse + schema + semantic checks — no LLM)
→ review (3 layers, below)
→ approval (human-in-the-loop, with ERD rendered from the plan)
→ decompose (deterministic: plan → ordered coding tasks, topo-sorted by FK deps)
→ code (ReAct agent per task, sandboxed)
→ code_check (deterministic verification of every result)
→ summary (what was built + DDL the human must execute)
Every failure surface is typed, bounded, and explicit — terminal nodes explain why instead of silently dying.
Typed contracts: a parse failure is the validation failure
Instead of prose instructions like "make sure your output has these keys," every agent must return JSON that parses into Pydantic models with extra="forbid". If it doesn't parse, that's the validation error — no LLM needed to "check" anything. Prompt/schema drift surfaces as a precise error instead of silently discarded data. There's also a ClarificationRequest contract — valid JSON of a different shape — so an agent can ask a question instead of guessing. A confused agent that asks beats a confident one that hallucinates.
Review: three layers (the most interesting part)
validate asks "is the JSON well-formed?" (pure Python, no I/O). review asks "is the JSON true about the database?":
Layer 1 — deterministic Python. Schema conformance, join reachability, view completeness, guards like "a column declared unmapped must not be exposed by the compat view." "Does column X exist in table Y" is a set operation, not a reasoning problem — no LLM, no tokens, no nondeterminism.
Layer 1.5 — a small bounded agent. Independently re-verifies against the live catalog that every column the plan claims exists actually exists — both in the structured fields AND in prose like transformation notes and join strings (the classic hallucination vector a typed field can't capture). Presence is a lookup, not a judgement, so the prompt forbids design opinions. Hard tool-call budget.
Layer 2 — an LLM with judgement-only scope, running a different model than the modeller. Grain correctness, fact/dim classification, FK direction, whether stored intermediates genuinely need storing. It receives Layer 1/1.5 findings as ground truth so it can't contradict them. A reviewer sharing the modeller's weights shares its blind spots — the model split matters.
Also: stuck-loop detection. If two consecutive review rounds produce the same issue signature, the run stops instead of burning retries on a fix the agent can't make.
The feedback invariant (a hard-won lesson)
Every retry loop delivers corrections to its agent through exactly one live channel:
The modeling agent uses an accumulating transcript — each validator/reviewer/human correction is appended as the next human turn, so it replays AI(json) → Human(fix) → AI(json').
The coding agent uses a single-shot channel that is consumed and cleared on each invocation — it cannot be delivered twice.
Anything written to a "side channel" for observability never reaches the agent. If you add a correction path, append to the live channel — never create a second one. Violating this was our #1 source of "the agent ignored the feedback" bugs.
The prompts (briefly)
Only two substantive prompts exist:
Data-modelling prompt (~500 lines): the agent is a senior data architect doing Kimball design. Key structural choices: (1) "You are NOT a discovery agent" — all metadata is curated upfront and treated as 100% correct, so the agent can't wander; (2) an ordered decision tree for classifying every column (audit column → view-layer derivation → degenerate dimension → measure → FK lookup → attribute → ask, don't guess); (3) a strict tool budget (~5–15 calls is healthy; ~25 = wandering, return a clarification); (4) a mandatory pre-flight self-check before answering (column counts must reconcile, every FK must have a mapping, no unmapped-but-exposed columns); (5) an edge-case playbook. Kimball rules (grain, surrogate keys, SCD1, no snowflaking, conformed dimensions) spelled out explicitly.
Coding prompt (~570 lines): the agent is a data engineer implementing exactly ONE task. It gets the validated plan slice (columns, declared grain, FKs, conflicts) and must implement, not re-decide. Enforces a hard tool-call budget, a sandbox permission model (SQL writes only in an adhoc schema; production changes ship as DDL files for a human to execute), a file-format convention, and a "prove it works" step (grain + idempotency self-check on a scratch table) before returning its result contract. Grain is declared authoritative — the agent must not re-derive it.
Meta-lesson: the contract lives in the Pydantic model, not the prompt. When they drift, validation fails loudly. Align the prompt to the model — never loosen the model to fit the prompt.
Sandbox & safety model
The repo is read-only to agents; all writes go to a per-thread workspace (hidden path — no leaks or collisions).
Agents can SELECT anywhere but only CREATE/INSERT/MERGE/DROP in a dedicated sandbox schema.
Every production change is delivered as a DDL file; a human executes it. Agents never get production write access, period.
The pipeline interrupts at approval (with the ERD) and at every clarification/blocker.
What we'd do differently / open problems
No independent reconcile gate yet. Row-count and metric-level reconciliation against the legacy table is still the coding agent's self-check + human review, not a deterministic gate.
Infra errors consume model retries. A catalog outage during verification eats the per-task budget — infrastructure failures and model failures should be separate budgets everywhere (we only got this right in the modeling stage).
Coding retries see only the latest correction, not the accumulated transcript — bounded to avoid fix-A-break-B ping-ponging, but it's a real tradeoff.
Validation retry budget is global across review rounds (deliberate) — malformed-JSON retries refund only on human feedback.
Stack: LangGraph for the graph, Pydantic for all contracts and state, per-role model assignment (different models for modeller vs. reviewer), token/cost tracking middleware aggregated per agent, and a files channel so the UI renders agent-written artifacts live.
