Rebuilding a Finance Warehouse's History with an LLM Conversion Agent

‍The problem.  A decade of SQL Server procedures that wiped and rebuilt their target tables every night. No history, and a bill that grew with table size rather than with change.

What we built. An LLM conversion agent that rewrote each procedure as an SCD Type 2 merge, ten procedures at a time, together with a validation framework that checked each result against the source warehouse before any of it was shipped.

The result.  370-plus procedures converted, cloud costs down 75%, data delivery SLAs up 85%, and point-in-time reporting the business never had before.

Customer Story

Our client, a renewable energy business, came to V4C with a SQL Server estate that had grown for about ten years. Several hundred stored procedures fed the dimension tables behind their finance reporting, sourced from Dynamics 365, and those tables fed period close, management reporting, and audit response.

It worked. It had worked for years.

Two things broke it. Finance and audit kept asking questions the warehouse couldn't answer, the sort that started with “What did this look like in March?” And the nightly processing had quietly become the largest line on the platform bill.

Neither problem would have gone away by moving the same procedures to a faster engine, as both were baked into how the pipelines worked.

Problem Statement

Every stored procedure did the same thing: truncate the target table, then reload it in full from source.

That was a defensible pattern. It was easy to reason about, and it was self-healing, because each run rebuilt everything from scratch, but it also destroyed the previous state, and that was where both problems came from.

There was no history since the rows from last night were deleted, and the cost increased in proportion to the total table size rather than the amount that actually changed, because every row was rewritten whether it needed to or not.

Solution

V4C set the target as SCD Type 2 dimensions fed by incremental loads. Instead of overwriting a row when something changed, the load closed the old version off with an end date and inserted a new one. The table grew through time, which made it possible to ask what any record looked like on any date.

That was not a translation. It was a different design, and the source code did not hold enough information to produce it. An SCD Type 2 dimension has to know how to recognize the same entity across runs, which columns matter enough to justify a new version, and how to tell what has changed since the last load. A truncate-and-reload procedure needs to know none of that. That information was not buried somewhere in the code. It had never been written down.

The part that turned out to matter most was: We only had to work that out once.

We settled the pattern with the client's data team in a day of discussion: how keys were formed, which attributes were tracked, how change was detected, and what happened to deletes. Following that, each of the 370 procedures reflected a consistent pattern. The challenge lay in the design, which required deep thought. The real effort came from implementing it 370 times.

That was precisely the kind of problem that should be passed on to an agent, on the condition that the output could be proven to be correct. Our team first examined three different options. Manual rewriting wouldn't have met the delivery deadline, and if several engineers worked on it in parallel, they would have ended up with multiple versions of the same pattern. Transpilers faithfully translate syntax, but that wasn't of any use in this case since a transpiler would have changed 'truncate-and-reload' into 'truncate-and-reload'. If you directly prompted the model, it produced plausible SQL, but there was no way of checking the cases that ran without error and yet returned the wrong figures. In a finance dataset, that kind of failure was the one that mattered.

The Conversion Agent - Technical Detail

Every stored procedure passed through the same five stages, and ten procedures moved through the pipeline at once.

1. Retrieval through tool functions: Rather than pasting schemas and dependency graphs into the prompt, we gave the agent functions to fetch a table schema, resolve an object's dependencies, and read source definitions when it needed them. The context stayed small, output quality did not drift as the estate grew more complex, and every retrieval was logged. When something came out wrong, we could see exactly what the model had looked at.

2. The pattern contract: We agreed on the contract regarding the pattern. Before we began generating the tables, we clearly specified each table's key, the attributes it would track, how changes would be detected, and the rules for deletion. The agent suggested possible candidate keys by examining the joins, indexes, and grouping clauses, but it never chose one. Since a wrong key results in a dimension having a historically inaccurate record without any error being detected, the choice of key remained with our engineers.

3. Generation: On the basis of the contract, the agent produced the complete implementation of the MERGE INTO statement, including the proper handling of effective dates, current-row flags, surrogate keys, and the sequence of first closing, then inserting, which is required by Type 2 versioning.

4. Validation. Each candidate was placed in a sandbox and then given to our validation framework, which checked the candidate against the source warehouse, and no merging took place unless there was a match.

5. Retry, then escalate. If a mismatch occurs, the failing diff is sent back to the model, informing it which columns were in disagreement and the values on both sides; if a certain number of attempts have been made, the procedure is then passed on to a V4C engineer.

The importance of running ten stored procedures at a time exceeded our expectations. Since conversion largely involved waiting - either for responses from the model or for sandbox queries - throughput was due to concurrency rather than to any one procedure running more quickly.

The design was based on three factors. Since the pattern contract was established by a V4C engineer, the agent never had to guess a business key. The validation framework was positioned between each candidate and the source warehouse, and it was because of this that the output could be regarded as trustworthy rather than just plausible. Instead of re-sending the prompt, the retry included the failing diff, so each attempt began with evidence that the previous one had not had. Ten of these loops were carried out simultaneously.

Method Comparison

Table 1. Only the agent approach delivered the redesign, held one pattern across the estate, and caught output that ran cleanly and returned the wrong answer.

Challenges and Mitigations

Proving the migrated warehouse matched the source. This was the real problem, and it was bigger than any individual procedure. Two warehouses, different engines, different dialects, hundreds of tables, and no practical way to eyeball whether the numbers agreed. Reading generated SQL told us nothing useful, because the failures that mattered produced valid SQL.

The V4C team therefore developed a validation framework and carried out automation of it. For each table, it compared the row counts, the number of null values for each column, the column-level checksums and the numeric totals for both the source and the target, and instead of giving a pass or a fail, it reported the columns that were in disagreement. This framework then became the checkpoint in the conversion loop and remained useful afterward as a regression check.

Dialect differences that raised no error. SQL Server uses a case-insensitive collation while Spark's string comparison does not. In the source, a predicate that matched produced fewer rows in the target without any failure and in a way that appeared completely reasonable. Decimal precision in division and timezone handling diverged between the two engines in the same silent way: no error, plausible output, wrong figures.  These issues could not be detected by reading the code, which was the reason we advocated for validating the data. It was only by means of a checksum comparison that the discrepancy was discovered without having to know which semantic gap had caused it.

The first load and every load after it were different problems. The initial run had no previous versions to close off, whereas each of the later runs did. If they had been treated as a single problem, then either duplicated history would have resulted, or the opening rows would have been missing. We therefore maintained separate backfill and steady-state processes and checked idempotency in the straightforward way of running it twice and then asserting that no changes had taken place the second time.

Results

V4C converted all 370+ procedures to SCD Type 2 dimensions on a metadata-driven Medallion architecture, with idempotent pipelines throughout. Re-running a load now reproduces the same result instead of duplicating history.

Substituting full reloads with incremental processing reduced the total cost of ownership in the high-volume finance modules by 75% since the processing now scales according to the amount of change rather than the size of the table. The data delivery SLAs also improved by 85% for the same reason. We have also combined batch and streaming ingestion using Auto Loader and Lakehouse Federation, and transferred the releases to Declarative Automation Bundles in order to achieve a fully automated deployment cycle.

The capability the business did not have before is point-in-time reporting for close and audit. It is now a property of the model rather than something reconstructed by hand each time somebody asks.

Key Takeaway

The agent did not just give us speed; instead, it brought about uniformity. If several hundred procedures had been rewritten by a team, there would have been several hundred slightly different SCD implementations, each of which could be defended on its own but all of which would have been impossible to maintain together. Our agent, on the other hand, applied a single pattern throughout the entire estate without variation, and one pattern is simply a matter of documenting, debugging and changing.

The main point is what an LLM is incapable of doing: if a modernization involves a redesign rather than a port, then the target architecture is not contained in the source code, and so no model will be able to discover it. V4C decided on the pattern first, used the agent to apply it without making any variations, and then examined each result against the source code since reviewing generated code will not pick up the kinds of failures that matter.

How v4c.ai Can Help

v4c.ai is a Databricks services partner with over 850 certifications, more than 500 practitioners, and over 200 enterprise clients. Most warehouse modernizations aren't ports. The target design needs decisions the source code never recorded, and that's where programs stall - or worse, ship quietly wrong numbers that nobody catches for a quarter.

We settle the pattern with your team, then apply it across the estate with conversion agents and a validation framework that proves every table matches the source before it goes live. The approach doesn't change shape at 30 procedures or 3,000. Only the runtime does.

Planning a move off SQL Server, Oracle, or Teradata, or already mid-migration with reconciliation blocking sign-off? Contact v4c.ai to schedule a migration architecture review.

‍