← Back to site

Case study · Wipro · Agentic AI

From legacy SQL to safe, self-verifying Python.

An LLM-driven data-transformation pipeline that reads SQL stored procedures or plain-English rules, writes the pandas code, proves it is safe, runs it in isolation and checks its own answer — with zero hardcoded business logic.

Role
Lead Engineer, AI
Team
3 AI engineers
Engagement
9 months
Stack
Gemini 2.5 Pro · Vertex AI · Google Cloud · Python · pandas · JavaScript
7specialized AI agents
20K+data sources processed
~95%of manual work automated
100+internal users daily

The problem

Thousands of messy files. Hundreds of legacy rules.

Healthcare fee-schedule data arrived in every shape imaginable — Excel workbooks with junk rows above the header, scanned and digital PDFs, CSV and JSON exports, scraped tables. The business logic that cleaned it lived in legacy SQL stored procedures, and every new source meant hand-written, hand-tested code. It was slow, error-prone and hard to audit in a compliance-sensitive environment.

The goal: let an LLM write the transformation code — but never trust it blindly. Every line it produces is constrained, checked, isolated and verified before anyone relies on the result.

Three ways in, one pipeline

Drive it with code, words or examples.

🗄️

SQL mode

Hand it a stored procedure. The model rebuilds the same logic in pandas — DELETE / UPDATE / INSERT, CASE expressions, WHILE loops, temp tables and window functions — matching SQL rounding semantics exactly.

💬

Instruction mode

Describe the rules in plain English. The same contracts, safety checks and verification apply, so a business analyst gets the same guarantees as a SQL author.

🔁

Reverse mode

Give it an input file and the expected output. One agent infers the rules (as SQL and English, with a self-rated confidence); a separate agent turns those rules into code — a human-reviewable checkpoint in between.

Architecture

Eight stages, from file to proof.

Each stage feeds the next. The model only ever writes code inside stage 3 — everything around it exists to constrain, check, contain and verify that code.

  1. 1Load & detect
  2. 2Read & map
  3. 3Translate
  4. 4Safety check
  5. 5Save codelog
  6. 6Isolated run & repair
  7. 7Verify
  8. 8Outputs

LLM step Guardrail Deterministic

1

Load & detect

Finds the right worksheet and header row automatically — no need to know which tab holds the data or how many junk rows sit on top. PDFs and scans go to the Extract agent, which uses Gemini's vision to pull tables out page by page.

xlsx · csv · jsondigital & scanned PDFauto sheet + header
2

Read source, profile & map columns

Profiles the data (types, samples, nulls, ranges) and maps every identifier in the rules to a real column: exact match first, then fuzzy. Every non-exact match and every column the code would create is listed in the run report, and low-confidence matches are flagged for review instead of being silently accepted.

schema profileconfidence-scored mappingno silent guesses
3

Translate with Gemini 2.5 Pro

The prompt combines the rules, the data profile and the column map. File contents are wrapped as clearly delimited, untrusted data — instructions hidden in a cell are never followed. The model must answer with a strict JSON contract: a description, step-by-step breakdown and one apply(df) function.

{
  "description": "Normalise prices and flag missing codes",
  "steps": [{
    "label": "Clean currency values",
    "sql_excerpt": "REPLACE(price, '$', '')",
    "explanation": "Strip currency symbols before casting",
    "code_lines": "df['price'] = ..."
  }],
  "code": "def apply(df): ..."
}
4

Static safety check

Before anything runs, the code's syntax tree is checked against an allowlist — not just a blocklist. Anything outside it is rejected and sent back to the model with the reason. Details below ↓

AST allowlistno importsno I/O methodssize limits
5

Save the codelog first

The exact generated code and its steps are written to disk before execution, so there is an audit trail even when a run fails.

6

Isolated run & bounded self-repair

The function runs in a separate, locked-down worker with no network, a read-only filesystem and CPU, memory and time limits. If it fails, the Repair agent receives the code, the traceback and the history of earlier attempts. Every fix goes back through the safety check. Retries are capped, and the loop stops early if the same error repeats — no burning tokens on a fix that isn't converging.

run(apply, df)  — in an isolated worker
  ├─ success → keep result
  └─ error   → code + traceback + attempt history → Repair agent
               → safety check → run again
               (stop on success, repeated error, or attempt cap)
7

Verify the answer

A result is only trusted once it is checked: against a reference file when one exists, and with reference-free checks when it doesn't. Details below ↓

8

Outputs & audit trail

Every run leaves a complete, reproducible record — the data, the code, the reasoning, the proof and the cost.

result.csvTransformed data, in the file's own naming style
codelog.pyThe exact Python the model wrote — a real, importable file
steps.csvSource → plain English → code, step by step (opens cleanly in Excel)
validation.jsonPass/fail, per-column results, coverage and sample mismatches
run_metrics.jsonModel version, tokens, latency, retries and column-mapping report

The seven agents

One agent per job — never one prompt for everything.

Each agent has a single responsibility and its own prompt. Splitting the work keeps every prompt focused, which made outputs more accurate and failures easier to diagnose.

Extract

Pulls tables from digital and scanned PDFs with Gemini vision.

SQL translator

Rebuilds stored-procedure logic as one pandas function.

English translator

Turns plain-English rules into the same function contract.

Rule inference

Infers rules from input + expected output, with a confidence rating.

Rules-to-code

A fresh call that converts inferred rules into code.

Step explainer

Splits the function into steps whose combined result must equal the whole — checked automatically.

Repair

Fixes code that failed at runtime, using the traceback and attempt history.

Prompt engineering

Prompts as contracts, not conversations.

Strict role, narrow task

"Your only task is to translate." The model is a translator, never a decision-maker — no editorialising, no added features.

Structured output, enforced twice

JSON-only answers required by the prompt and by the API's JSON mode, then parsed and validated before use.

Reproducible by design

Temperature 0, a pinned model version and a cache keyed on the prompt hash — the same input gives the same code, and every run records what produced it.

Show, don't hope

A few-shot cookbook of SQL → pandas idioms: COALESCE → fillna, ROW_NUMBER() → groupby().cumcount(), CASE → np.where.

Bugs become rules

Failures found in validation were encoded back into the prompt — e.g. SQL rounds half-up while Python rounds half-to-even (2.675 → 2.68 vs 2.67), so the prompt mandates a decimal-based rounding helper.

Prompt mirrors the guardrails

The forbidden-operations list in the prompt matches the safety checker exactly, so the model rarely writes code that gets rejected — fewer retries, lower cost.

Trust & safety

AI-written code, run with defence in depth.

Static checks catch most problems early and cheaply. Isolation contains whatever static analysis misses — like file or network access hidden inside an allowed library call. Neither layer is trusted on its own.

Layer 1

Static allowlist

  • Exactly one def apply(df) (plus small helpers)
  • Pre-loaded tools only: pandas, NumPy, re, json, math
  • Any import statement
  • Names or attributes starting with _ (blocks __class__-style escapes)
  • File or network I/O through libraries: read_*, to_csv, to_parquet, NumPy load / save / fromfile
  • eval, exec, getattr, DataFrame.query, string-format tricks
  • Code over the size limit
Layer 2

Isolated execution

  • Separate sandboxed worker per run
  • No network access
  • Read-only filesystem
  • CPU, memory and wall-clock limits — no infinite loops or memory bombs
  • Data in, DataFrame out in a plain format — results are never unpickled
Layer 3

Audit & accountability

  • Codelog saved before execution
  • Every rejection and retry logged with its reason
  • Model version, tokens and latency per run
  • Data contents treated as untrusted input end to end

Verification

Generation is easy. Proof is the product.

🔑

Reference comparison

With a known-good file, rows are aligned on an auto-detected key (composite keys supported), values normalised (1001.0 → 1001, dates, whitespace) and compared with a numeric tolerance. Result: PASS/FAIL per column, coverage (matched / missing / extra) and sample mismatches.

📐

Invariant checks

No reference? The run still checks what must hold: expected row counts, unique keys, null rates and column totals within bounds. Silent data loss gets caught even without a gold file.

🪞

Shadow SQL run

In SQL mode, the original procedure is transpiled to an embedded SQL engine, run on a sample and diffed against the Python result — an independent second opinion on every translation.

From demo to production

Prove it fast, then ship it properly.

  1. 1 · Prototype & approval

    Streamlit demo

    The backend was first wrapped in a Streamlit app for stakeholder reviews: pick files, press run, and inspect the pipeline log, the source side-by-side with the generated code and its steps, the validation metrics and every artifact. Reviewers could see exactly how each result was produced before signing off.

  2. 2 · Production

    Google Cloud + JavaScript frontend

    Once approved, the same backend shipped on Google Cloud behind a purpose-built JavaScript frontend — the version 100+ internal users rely on every day. Sheet and header detection still need zero configuration.

Impact

Shipped, used, recognised.

  • ~95% of a previously manual workflow automated
  • 20,000+ data sources and millions of files processed
  • 100+ internal users relying on it daily, with downstream healthcare applications reaching millions of people
  • Wipro AI Impact Award and AI Collaboration Award

Lessons

What I'd tell the next team.

  1. Treat prompts as code. Version them, test them, and turn every production bug into a rule.
  2. A blocklist is not a sandbox. Allowlist what code may do, then contain it anyway — and try to break your own sandbox before someone else does.
  3. Fuzzy is fine, silent isn't. Every guess the system makes should show up in a report a human can read.
  4. Bound every loop. Self-repair is powerful, but only with caps, history and an exit when it stops converging.
  5. Verification is the product. The model writes the code; the pipeline earns the trust.

Building something like this?

I lead teams that take agentic AI from prototype to production.

Get in touchBack to the site