Reverse-Engineer a Legacy Stored Procedure Before You Rewrite It

The discipline is treating every extracted "business rule" as a claim to be checked against the code and the data, not as documentation.

When to reach for this

Use this before rewriting or migrating any stored procedure too large or too old to have a living owner. It is equally useful when an incident forces you to understand a procedure fast, or when scoping a migration and you need a defensible inventory of embedded logic.

Triggers:

• •
  A migration backlog item that starts with "convert proc X" and no documentation.
• •
  A procedure whose author left; changes are made by superstition.
• •
  An estimate needed for modernization work — logic complexity drives the number.

The workflow

1. 1.

   Extract the complete source plus its neighborhood. Pull the procedure DDL, the DDL of every table it reads or writes, and any procs it calls. Incomplete context produces confident, wrong summaries — the model fills gaps with plausibility.

2. 2.

   First pass — structure, not meaning.

   Copy

   Here is a legacy T-SQL stored procedure (1,100 lines) plus DDL for the
   tables it touches. Do not summarize business meaning yet.
   
   Produce:
   1. Control-flow outline: sections, loops/cursors, branches, and what
      each writes to (table or temp table).
   2. Full dependency list: tables read, tables written, procs called,
      with line numbers.
   3. Every hard-coded literal (dates, IDs, magic numbers, statuses)
      with line number and surrounding context.
   4. Anything unreachable, or reachable only under conditions you can
      identify (dead branches, commented-out logic still referenced).
   
   [paste procedure + DDL]
   

   The line numbers matter: they make every claim spot-checkable.

3. 3.

   Verify the skeleton before going deeper. Spot-check five claims against the source — two dependencies, two literals, one branch condition. If any is wrong, fix the context (missing DDL? truncated paste?) and regenerate; a wrong skeleton poisons everything downstream.

4. 4.

   Second pass — business-rule extraction.

   Copy

   Using the verified outline, extract every business rule as:
   RULE-nn | plain-English statement | line numbers | inputs | outputs |
   confidence (high/medium/low) | what would break downstream if this
   rule were dropped.
   Flag rules that contradict each other or that the branch structure
   makes unreachable. Do not merge similar-looking rules — list them
   separately with the difference noted.
   

5. 5.

   Interrogate the low-confidence rules with data. For each rule the model marked medium/low, write a query that would show the rule operating. Rules the data contradicts get flagged for a human decision — the code may be wrong, dormant, or the summary may be.

   Copy

   -- RULE-07 claims: orders in status 'H' older than 90 days are
   -- excluded from settlement. Does the output actually reflect that?
   SELECT COUNT(*) AS should_be_zero
   FROM settlement_output s
   JOIN orders o ON o.order_id = s.order_id
   WHERE o.status = 'H'
     AND o.order_date < CURRENT_DATE - INTERVAL 90 DAY;
   

6. 6.

   Produce the rewrite contract. Ask for a consolidated spec — rules table, dependency map, literals-to-parameterize list, open questions — and have the procedure's stakeholders review the open questions. This document, not the old code, is what the new dbt models get built against; every RULE-nn becomes a test in the rewrite.

Verify before you trust it

• •
  Dependency list cross-checked against the database's own metadata (information_schema.routines / sys catalog views for referencing objects) — the model's list and the catalog's list must reconcile.
• •
  Five random rule citations opened at the cited line numbers; the code says what the rule says.
• •
  Every low-confidence rule has a confirming or refuting query executed against production-like data.
• •
  Row-count sanity: run the legacy procedure on a fixed input set, count outputs per branch (where loggable); the rule table must account for every excluded row.
• •
  Contradictions and dead branches reviewed by a human who can decide "bug, feature, or fossil."

Pitfalls

• Plausible invented logic. Given incomplete context, the model describes what such a procedure typically does, not what this one does. Complete DDL plus line-number citations are the countermeasure.
• Cursor semantics glossed over. Row-by-row logic with intra-loop state (running totals, last-row comparisons) gets summarized as if it were a set operation, losing order-dependence that changes results.
• Temp-table lifecycle confusion. Which version of #stage a later section reads, after conditional rebuilds, is exactly the detail summaries flatten. Trace one temp table by hand as a spot check.
• Dormant rules treated as dead. A branch that data has not exercised in two years may still be legally required next quarter-end. "Unreachable in the code" and "unused in the data" are different claims — label them separately.
• One-pass extraction. Asking for structure and meaning in one prompt yields shallow coverage of both. The two-pass split is what makes 1,000-line procs tractable.

Tools

• Claude (long context)fits the whole procedure plus DDL in one context; the two-pass workflow above is built for it.
• SnowConvert AI assessment reportsautomated inventory and complexity scoring across hundreds of procs; use it to pick which procedures deserve this playbook's depth.
• Databricks Lakebridge (analyzer phase)similar codebase-level profiling for migration scoping on the Databricks path; open source.

Client-safe variant

Legacy procedure code on client engagements is client IP, and MSAs usually prohibit pasting it into public tools. Run the identical two-pass workflow through Bedrock or Azure OpenAI provisioned in the client's tenancy, or a long-context local model via Ollama for smaller procedures. Where even tenancy-approved AI is unavailable, genericize first: a scripted rename of schema, table, and column identifiers to neutral tokens (kept in a local mapping) preserves the logic the analysis needs while removing client vocabulary. All verification queries run inside the client warehouse, and the rewrite contract is reviewed under the client's document controls.