How Harness Transforms AI Coding for Data Warehousing into an End-to-End Pipeline

Introduction

The author explains that a simple accuracy figure such as "AI writes SQL with 90% correctness" is misleading for data‑warehouse (DW) production. An internal experiment with five engineers showed that while syntax correctness stayed high, the pass‑rate for strict business constraints dropped from 90% to 8.6% and finally to 0% when additional checks (PK uniqueness, no SELECT *, partition naming, DQC validation, change‑log writing) were required.

Four Core Pain Points in DW AI‑Assisted Development

Cross‑layer semantic drift : Business definitions (e.g., "active user") change meaning across ODS, DWD, DWS layers, and LLMs lose these long‑range constraints.

Metric‑sensitive details : Small syntax errors (LEFT JOIN vs INNER JOIN, date bounds) can break downstream reports, and LLMs cannot guarantee correctness every time.

Costly rollback : A single error may require terabytes of data to be re‑processed across dozens of downstream tables.

SLA hard constraints : Production wide tables must be ready by 4 am; a failed SQL that crashes at T+1 defeats any efficiency gain.

Why Copilot‑Style Tools Fall Short

Copilot improves typing speed but does not address the above constraints, which are fundamentally engineering problems rather than coding‑speed problems.

Harness: A Seven‑Layer Engineering Framework

Treat the LLM as a creative but forgetful engineer; use a deterministic framework (Master + SKILL + HITL + state + anti‑pattern library + self‑check) to make it production‑grade for DW.

Layer Overview

L1 – Master Orchestrator : Detects LLM drift and loads a persistent state file before each session.

L2 – SKILL Registry : Each SKILL is a Markdown file describing trigger keywords, capability boundaries, dependencies, execution steps, required artifacts, and known pitfalls.

L3 – MCP Tool Bus : Provides safe, sandboxed access to external services (SQL execution, TAPD, Feishu, Tableau).

L4 – Human‑in‑the‑Loop Gates (5 gates) : Inserted at points where decision cost is low but correction cost is high.

L5 – Persistent State (_state.json) : Single source of truth across sessions; re‑loaded at each orchestration step.

L6 – Anti‑Pattern Library : Stores concrete failure cases (AP‑001 … AP‑024) with phenomenon, root cause, and mitigation.

L7 – Mandatory Self‑Check : Evidence‑based Q&A that forces the LLM to provide proof for each deliverable.

Key Design Details

1. SKILL Registry vs IDE Hooks

IDE hooks can only enforce syntactic rules after code is generated and provide opaque rejections. The SKILL registry acts as a pre‑execution soft gate, describing why a rule exists and allowing the LLM to incorporate the constraint during generation. Example SKILL (dw‑dqc‑validation) includes trigger keywords, capability limits, required MCPs, six DQC SQL templates, and a list of negative cases.

{
  "req_id": "Indonesia log table sync",
  "phase": "M3_done",
  "feishu_enabled": true,
  "impacted_cols": ["business_type", "third_party_scene_code"],
  "unchanged_cols_fp": "crc32_sum=8843718233",
  "hitl_gates_passed": ["S1","S5","M1","M3"],
  "hitl_gates_pending": ["T1"],
  "current_master_skill_version": "v1.6",
  "anti_pattern_hits": ["feishu_changelog_skipped"],
  "last_updated": "20260601T17:42:00+08:00"
}

2. Persistent State Machine

Because a DW task typically spans 4‑6 chat sessions, the previous TODO list in the LLM context is lost. The framework writes a JSON state file and a file‑tree of artifacts. The Master reads this file at the start of each session, reconstructing context without relying on chat history.

3. Human‑in‑the‑Loop (HITL) Gates

Five gates (G1‑G5) are placed where the marginal benefit of human review outweighs the cost of correction. Each gate presents structured evidence questions (e.g., "Do all DQC rules pass?", "Is the CRC32 fingerprint identical between dev and prod?") and requires explicit confirmation before proceeding.

4. Anti‑Pattern Library

Concrete failure cases (e.g., AP‑007: wrong enum meaning, AP‑013: INSERT * after column addition, AP‑019: missing Feishu changelog) are injected into the LLM context whenever a related SKILL runs, reducing repeat error rates from 47% to 6%.

5. Answer‑with‑Evidence

At the final T1 delivery stage, the LLM must answer five mandatory questions with verifiable artifacts (DQC SQL IDs, CRC32 sums, Feishu documentRevisionId, rollback script path, downstream impact list). This eliminates the "done" illusion where the model claims completion without evidence.

End‑to‑End Case Study: Indonesia Three‑Party Log Table

A real project added two new metrics to a 1,800‑row wide table with 500+ existing columns, requiring historical back‑fill from 2026‑05‑25. The total wall‑clock time was 47 minutes (AI 33 min, human HITL 14 min) compared to the previous 2‑person‑day effort. Key observations:

AI work (S1‑T1) averaged 2‑5 minutes per stage.

Human review (G1‑G5) took 14 minutes, mainly at DDL/Dependency review (G2) and code‑blueprint validation (G3).

Zero‑side‑effect guarantee was proved by a CRC32 fingerprint test that compared dev and prod partitions field‑by‑field; all 500+ existing columns matched exactly.

ROI and Quality Metrics

Per‑stage time reduction ranged from 87% to 95% compared with manual effort. Overall, a typical demand that used to require ~14 hours of engineering time was completed in ~47 minutes, a 94% reduction (≈25× speedup). Quality indicators improved dramatically: zero‑side‑effect achievement rose from 38% (pre‑Harness) to 96% in v1.6, and the "virtual done" reports dropped from 2‑3 per project to near zero after the Answer‑with‑Evidence addition.

Design Evolution and Lessons Learned

Four major design pivots were documented:

Removed automatic AI‑only deployment because responsibility must stay with humans (G5 gate).

Stopped letting product owners converse directly with the LLM; ambiguous requirements caused silent failures.

Abandoned multi‑agent racing; coordination overhead outweighed any quality gain.

Iteratively refined SKILL loading logic from eager loading (v1.0) to stage‑plus‑trigger‑semantic loading (v1.3), ensuring the Master never guesses.

Future Plans

Refactor the monolithic Master into multiple sub‑agents (verifier, reporter) for better isolation.

Build a visual Harness console to replace raw JSON state files, showing progress, gate status, and artifact previews.

Generalize the framework to adjacent high‑sensitivity domains (financial risk, marketing metrics) by reusing the Master‑SKILL architecture.

The author concludes that Harness is not an end point but a living system that continuously evolves as new failure modes are discovered, turning AI‑assisted coding from a novelty into a reliable production capability.