Post cover image

August 30, 2026

Building a Data-Focused Software Factory

A decade ago a pipeline was a GUI-configured ETL job, a scheduled script on a box someone had root access to, and a wiki page describing…

By Adrian Lee Xinhan

11 min read

A decade ago a pipeline was a GUI-configured ETL job, a scheduled script on a box someone had root access to, and a wiki page describing roughly what it did. There was nothing for an agent to grab hold of — no repository, no test suite, no compiler, no CI.

That is no longer what a pipeline is. Transformations are dbt models. Orchestration is Python DAG definitions. Ingestion is declarative config. Extraction is Spark code. All of it lives in version control, arrives as a diff, runs through CI, and gets reviewed like any other software change — with the same disciplines attached: linting, unit tests, code review, staged environments, semantic versioning.

That shift is the precondition for everything in this post. Because the artifacts are code sitting in repositories with linters, compilers, and test frameworks, we can do the one thing that makes autonomy tolerable: check the agent's work mechanically before it lands. You cannot gate a drag-and-drop GUI mapping behind a pytest assertion. You can absolutely gate a dbt model.

So the software-factory idea — treating agent output as an industrial process with quality gates rather than a chat transcript you eyeball — transfers to data engineering precisely because data engineering adopted software engineering's practices first.

That's the bet we set out to test.

Thus, we built a Proof of Concept where an AI agent autonomously detects schema drift in upstream sources, generates the code changes across a modern Data Engineering stack (dbt, Airflow, Spark, Snowflake), validates every artifact with deterministic test harnesses that act as a "ground-truth oracle," and monitors the resulting pipeline until it succeeds or fails — retrying intelligently and escalating when it can't. The core bet: gate the AI behind determinism. LLMs hallucinate; test harnesses don't. Every byte the LLM generates has to pass a machine-checkable gate before it's allowed to touch real infrastructure.

Note: implementation details in this article are generalised. Naming, schema fields, and layer conventions are illustrative rather than drawn from any specific environment.

The Problem: Schema Drift Is a Death-by-a-Thousand-Cuts

A typical ingestion layer pulls from heterogeneous sources — document stores, relational databases, and flat-file drops — into a medallion architecture (landing → raw → curated) running on Airflow, dbt, and Spark against a cloud warehouse.

Source schemas are never stable. Columns get added. Data types change. Columns get deprecated. A single upstream schema change can cascade through the ingestion SQL template, the ingestion config, the transformation config, the dbt model, and sometimes several orchestration definitions and an extraction job.

Handled by hand, each drift means: read the metadata diff, work out the blast radius, edit files across multiple repositories, push, trigger the pipeline, then babysit logs until it either succeeds or surfaces a subtle failure — then debug, regenerate, and retry.

We asked a hard question: can we automate this end-to-end?

The Design

We built a three-layer agentic system as shown below

None

1. The Engineering Agent — the "builder." It reads schema metadata from a catalog API (such as Openmetadata), classifies what kind of change occurred, and generates the corresponding artifacts. The classification matters because the blast radius differs sharply by change class:

None

Two things make this taxonomy useful rather than academic.

  • First, risk is not uniform — an additive change is low risk, a type change is the dangerous one, and a removal needs deliberate deprecation rather than deletion.
  • Second, the number of repositories touched grows with the class, so knowing the class up front tells the agent how much context it actually needs to load. A new extraction job is only required in the rarer case where an entirely new kind of source system is introduced, not merely a new namespace within an existing one.

2. The Quality Agent — the "guardian." After new tables land, it keeps data-quality declarations in sync with schema reality. Critically, it writes into the existing DQ framework (validation config plus dbt test definitions) rather than building a parallel one — so generated rules automatically participate in the existing quarantine tables, severity classification, and alerting with zero new infrastructure.

3. The Orchestrator — the "manager." It sequences everything, owns the retry loop (capped at two retries), decides when quality checks run, launches a monitoring listener to watch the pipeline chain, and escalates to a human when it can't recover.

4. Persistent Memory and Provenance An agent that can't remember what it did last time can't detect what changed since last time. Persistence is what makes "check this table" a meaningful instruction rather than a request for a full re-derivation. Each agent keeps its own state file, one key per table. Inside that key sit three separate concerns:

{
  "relational/orders": {
    "last_applied_class": "additive",
    "last_applied_version": 1.1,
    "last_applied_at": "2026–08–04T00:00:00Z",
    "downstream_impact": {
      "raw_table": "<db>.<raw_schema>.<table>",
      "curated_table": "<db>.<curated_schema>.<table>",
      "model": "models/curated/<…>.sql",
      "orchestration": "<pipeline_id>",
      "affected_columns_by_class": {
        "additive": [
          "XX",
          "YY"
        ]
      }
    },
    "commit_history": [
      {
        "class": "additive",
        "version": 1.1,
        "repos": {
          "transform": {
            "hash": "4810daf",
            "message": "feat: add columns"
          }
        },
        "risk": "LOW",
        "columns_changed": [
          "XX",
          "YY"
        ]
      }
    ]
  }
}

The cursor is the only part the runtime needs last_applied_* is the cursor. It is the minimal contract — the one thing that has to persist for autonomy to work, because the change classes form a sequential chain.

With the cursor, a bare check

resolves to a specific pair of schema snapshots to diff. Without it, "what changed since last time" has no referent. Strip every other field and auto-detect still works.

The new-entity and new-namespace classes sit outside the chain and are independent of the cursor.

Commit_history is the log — append-only, and last_applied_version is effectively a pointer into it. Each run advances the cursor and appends an entry.

We leveraged on Snowflake's Cortex Code to perform the Snowflake specific tasks as well as the rest of the data engineering pipelines.

The Oracle: The Part That Makes This Safe

Here's the pattern we have built. We do not trust the LLM to know our own conventions. Those live as deterministic Python test harnesses — a "contracts" oracle that encodes:

  • The exact set of allowed test types and validation rule types
  • Repository directory conventions and file-naming rules
  • The structural CTE pattern every transformation model must follow
  • DDL operations and metadata column requirements
  • Naming correctness against the actual repositories

Before any generated artifact is pushed, the agents run a validation sub-skill — which executes these pytest harnesses against the actual working tree the agent just edited, not against a mock. This is the difference between "the agent says it looks right" and "the machine verified it is right."

None

The harnesses degrade gracefully — if a repo clone or live infrastructure isn't present, they skip with a message rather than fail. That keeps the check honest: a green run means the infrastructure was there and the artifacts passed.

The Oracle in Practice: What the Harness Scripts Actually Do

"Deterministic harness" sounds abstract. Here's what it concretely looks like. We wrote a set of small Python modules that encode the facts an LLM must never be allowed to guess. The snippets below are illustrative of what we built.

1. Drift classification — pure Python, zero LLM

Given previous and current schema snapshots from the catalog, this returns the change class, a risk rating, and the exact column changes:

# classify.py - deterministic, no LLM in the hot path
@dataclass
class DriftResult:
    change_class: str
    risk: str
    added: list[str] = []
    removed: list[str] = []
    type_changes: list[tuple[str, str, str]] = []  # (name, old_type, new_type)

def classify_drift(prev_schema: dict, curr_schema: dict) -> DriftResult:
    prev_cols = {c["name"]: c for c in prev_schema["columns"]}
    curr_cols = {c["name"]: c for c in curr_schema["columns"]}
    added   = sorted(set(curr_cols) - set(prev_cols))
    removed = sorted(set(prev_cols) - set(curr_cols))
    type_changes = sorted(
        (n, prev_cols[n]["dataType"], curr_cols[n]["dataType"])
        for n in set(prev_cols) & set(curr_cols)
        if prev_cols[n]["dataType"] != curr_cols[n]["dataType"]
    )
    # Priority matters: a type change outranks an addition, because it is
    # the change most likely to corrupt downstream data silently.
    if type_changes:          return DriftResult("type_change", "MEDIUM-HIGH", added, removed, type_changes)
    if added and not removed: return DriftResult("additive",    "LOW",         added, removed, type_changes)
    if removed:               return DriftResult("removal",     "HIGH",        added, removed, type_changes)
    return DriftResult("no_change", "NONE", added, removed, type_changes)

The agent delegates to this instead of reasoning through the diff in prose — faster, and it can't hallucinate a change class. Note the explicit priority ordering: when a table both gains a column and changes a type, the type change wins, because that's the one that quietly produces wrong numbers.

2. The contracts oracle — the single source of truth

This module defines the invariants every artifact must satisfy. The most important: a set of pipeline-managed metadata columns that no change class is ever allowed to drop, rename, or retype. These are the columns the platform itself depends on for lineage, deduplication, and incremental logic — if an agent touches them, the pipeline breaks in ways that are hard to trace.

# contracts.py - the "sacred" invariants (illustrative names)
# Pipeline-managed metadata columns: NEVER dropped, renamed, or retyped.
METADATA_COLUMNS = frozenset({
    "RECORD_KEY",           # surrogate key
    "RAW_PAYLOAD",          # original source document
    "SOURCE_PRIMARY_KEY",
    "SOURCE_MODIFIED_AT",
    "SOURCE_ROW_HASH",      # change detection
    "EXTRACTED_AT",
    "SOURCE_SYSTEM",
    "SOURCE_OBJECT",
    "SOURCE_FILE_PATH",
    "LOADED_AT",
    "SNAPSHOT_DATE",
})

3. Pattern checks — asserting the model matches house style, not generic dbt

Rather than reading code, these are assertions over the generated SQL. Every transformation model must carry the required config() block, the correct unique_key, the expected CTE structure, and a final select from the deduplication stage:

# model_checks.py - structural pattern enforcement
REQUIRED_CONFIG = {"materialized": "incremental", "incremental_strategy": "merge"}
DEDUP_CTE = "deduplicated_batch"   # house-style CTE name

def check_config_block(model_sql: str) -> list[str]:
    m = re.search(r"\{\{\s*config\((.*?)\)\s*\}\}", model_sql, re.IGNORECASE | re.DOTALL)
    if not m:
        return ["missing required config() block"]
    block = m.group(1)
    violations = []
    for key, expected in REQUIRED_CONFIG.items():
        if not re.search(rf"{key}\s*=\s*['\"]{re.escape(expected)}['\"]", block, re.IGNORECASE):
            violations.append(f"config() missing/incorrect {key}='{expected}'")
    return violations

def check_dedup_stage(model_sql: str) -> list[str]:
    if not re.search(rf"\b{DEDUP_CTE}\b", model_sql, re.IGNORECASE):
        return [f"model must build a '{DEDUP_CTE}' CTE"]
    return []

A subtle but important detail: the oracle is transcribed from what the repositories actually do, not from the idealised template in the documentation. Those two drift apart over time, and when they do, the repository is the truth. The harness also carries an explicit waiver list of pre-existing violations, so a new run isn't blamed for drift it didn't introduce.

4. Grounding — the agent does not guess where things live

Repository locations, the target branch, and the catalog endpoint are resolved by code with explicit fallbacks — skip loudly, never silently pass:

# repos.py - resolve real repo clones, or skip loudly
REPO_DIRS = {"transform": "analytics-transform", "orchestrate": "analytics-orchestration"}
REPOS_ROOT = Path(os.environ.get("REPOS_ROOT", default_root))
BRANCH = os.environ.get("REPO_BRANCH", "development")

def repo_available(kind: str) -> tuple[bool, str]:
    path = REPOS_ROOT / REPO_DIRS[kind]
    if not path.is_dir() or not (path / ".git").exists():
        return False, f"{kind} repo not found at {path} - clone it or set REPOS_ROOT"
    return True, "ok"

5. Wiring it into the agent lifecycle

Validation is a first-class step between generation and push. The validation skill maps a change class to an exact pytest selection — it never "eyeballs" code:

# validation skill: change class -> pytest selection
selections = {
    "additive | type_change | removal": 'pytest tests/test_column_drift.py -k "<class> and <source>"',
    "new_entity":                       'pytest tests/test_new_entity.py -k "<source>"',
    "new_namespace":                    'pytest tests/test_new_namespace.py -k "<source>"',
}

The loop is now: classify in Python → generate in the LLM → validate with pytest against the real working tree → push.

Closing the Loop: Watching the Pipeline, Not Just the Push

Generating correct-looking code isn't the same as shipping a working pipeline. So the orchestrator launches a background monitoring listener that watches the dataset-triggered pipeline chain and closes the feedback loop.

The listener doesn't just report pass/fail — it classifies failures, because the right response differs completely:

  • Transient infrastructure — regenerate identically and let the new run trigger
  • Schema or code error — feed the error back to the engineering agent as context so it regenerates the affected artifact with the failure in mind
  • Configuration error — do not retry; escalate immediately, since retrying a misconfigured watcher just burns another long timeout

That third case is the one teams usually get wrong. A failure the agent cannot possibly fix by regenerating must exit the loop immediately rather than consume the retry budget.

Building MR templates

Monitoring closes the loop between the agent and the pipeline. But there's a second loop, and it's the one we underestimated: the loop between the agent and the human who has to live with its output.

An agent that pushes code has to answer a question that never comes up when a person does it: how does a reviewer verify work they didn't write?

Our answer was a generated review artifact — a merge-request description per repository. Four parts carried the weight:

  1. The upstream change, in diff notation. A table of columns marked + added, ~ modified, - removed, with type and nullability. The reviewer sees the cause, not just the consequence.
  2. The machine's own evidence, as checkboxes.
None

This is harness output surfaced into the review. The reviewer isn't asked to trust the agent — they're shown which deterministic gates it cleared. And critically these are reported, never claimed: the agent cannot tick a box the harness didn't return.

  1. An explicit impact section, answering the question a reviewer would otherwise have to derive themselves:
None

4. Cross-links to the sibling requests. One upstream schema change may touch three repositories, and the commits can't be atomic across them. Each description links the others. Without that, a reviewer sees one third of a change with no way to know the rest exists — which is the single most confusing thing about reviewing multi-repo automated output.

What We Learned

  1. LLMs train on generalities; your repository is a specific fact set. Move every convention, naming rule, and structural requirement into deterministic code. Treat the harness as the contract and the agent as an interpreter that must satisfy it.
  2. The cheapest token is the one never spent on an LLM. Drift classification, config parsing, and validation are all deterministic — delegate them to Python instead of reasoning about them. Faster, and it never hallucinates.
  3. Sequencing is architecture, not convention. How many times quality checks run, when the pipeline runs, and which agent retries on failure are decisions with real blast-radius consequences. Make the orchestrator own them explicitly: a quality failure must never be misattributed to the engineering agent, and a config failure must never trigger a pointless regenerate-and-retry loop.
  4. "Code is correct" and "pipeline works" are two different claims. Validation covers the first; a monitoring loop with failure classification covers the second. Both are required before you let an agent push to a shared branch.
  5. Skip loudly, never silently. Every harness check that cannot run reports why. A test suite that quietly passes when the infrastructure is absent is worse than no test suite, because it manufactures false confidence.

What's Next: Part 2 — Making the Factory Efficient

This isn't a finished playbook. We're still building, and a fair amount of what follows we learned by getting it wrong first.

This POC proved the loop works. But "it works" isn't the same as "it runs at the right cost." The industry conversation this year has shifted from "can agents do this?" to "can we scale agents without multiplying the bill?"

Part 2 applies that lens to this system. We're borrowing from Uber's public engineering playbook on running a software factory efficiently — their premise being that agent cost decomposes into an equation whose terms you can attack independently:

_> _cost ≈ users × sessions × turns × requests × tokens × price

You don't just negotiate a lower token price. You eliminate zero-value tokens — those burned on work the LLM shouldn't be doing at all. For this agentic factory, that maps to eight concrete improvements:

  1. Code-mode classify-and-route — classify drift with a Python CLI and return a routing card, instead of letting the LLM re-derive the diff in prose. Classification never touches an LLM token.
  2. A static context index — a pre-built map from each table to its lineage and expected artifacts, replacing repository-grepping with a single lookup. A lightweight stand-in for the large context graphs bigger shops maintain.
  3. Slim the orchestration prompt — today the orchestrator loads full agent skill files (over a thousand lines) on every run, even a single-column change. Replace that with a routing card that loads only what the change class needs.
  4. Change-class-gated reference loading — read only the pattern file the change class actually touches, not every pattern file every time.
  5. Outcome metrics, warehouse-native — duration, failure rate, and retries per change class as warehouse views, so "managed-agent unit economics" stop being a vibe and become a table.
  6. A papercut feedback loop — persist classified pipeline failures so the next skill iteration is driven by real failures rather than memory.
  7. Layered cost control and operator defaults — be explicit about which surface is managed versus interactive, and document context-compaction and reasoning-effort defaults that cut per-request tokens.
  8. An eval matrix — reuse the existing pytest harnesses as the benchmark, so any model or skill change is judged against the same bar before it ships.

The running theme: the cheapest token is the one never sent to an LLM. Stay tuned on this journey.

Recogniton: Muhammad Ardiyan and Reza Widodo from Snowflake