LakeShift: Oracle to Snowflake, Proven

Migrate legacy Oracle PL/SQL to Snowflake and prove it. Nemotron agents on Nebius translate, self-fix using cited docs, and verify every row matches.

Comment

Inspiration

I've spent 10+ years as a cloud and data architect, across Accenture, Cognizant and now Revantage, and I've seen the same thing on every Oracle migration. Translating the code is the easy part. Proving it still gives the same answers takes months of manual checking, and tiny differences still slip through and quietly break reports. One example: Oracle treats an empty string '' as NULL, and Snowflake doesn't. I wanted a tool that doesn't just translate the code, but proves the result.

What it does

LakeShift migrates legacy Oracle PL/SQL to Snowflake SQL and proves every object row by row.

  • Planner splits the legacy code into objects and works out the order they depend on each other.
  • Translators convert each object in parallel, using NVIDIA Nemotron on Nebius Token Factory.
  • Verifier runs the Oracle and Snowflake versions on the same data and compares every row. Only an exact match passes. It also tests "day 2" changes, such as customer updates and deletes.
  • Researcher uses Tavily to search only the official Oracle and Snowflake docs, so every fix comes with cited sources.
  • Model ladder: each object starts on Nemotron Nano. If the proof fails, it escalates to Super, then Ultra, so the big model is used only for the hard parts.
  • Honest results: if an object still can't be proven, LakeShift marks it FAIL with the docs it checked, so a person reviews one job instead of all of them. Nothing unproven is passed off as correct.
  • Privacy: customer text values are masked before anything is sent to a model.

It handles real legacy patterns, not just simple procedures: cursor loops, MERGE upserts, window functions, and hierarchical CONNECT BY queries.

Results

Metric Result
Objects proven 6 / 6 PASS
Rows compared 5,685
Run time 327 s
Tokens 65,441
Cost per full run about $0.064
Same run on Ultra only about $0.196 (3.1× more)

From a full serverless run. Run times vary with load (about 5–8 minutes on the hosted app). On harder runs, an object that can't be proven is flagged for review instead of passed.

The ladder cost is the sum of tokens times price for each model:

$$ \text{cost} = \sum_{m \in {\text{Nano, Super, Ultra}}} \left( \text{tokens}^{in}_m \cdot p^{in}_m + \text{tokens}^{out}_m \cdot p^{out}_m \right) $$

How we built it

  • Python multi-agent pipeline, run with python -m lakeshift run.
  • Nebius Token Factory (OpenAI-compatible API) with Nemotron 3 Nano, Super and Ultra.
  • Serverless and dedicated inference: the public app runs on serverless. A dedicated Nano endpoint benchmarked at about 300 vs 124 tokens/sec (about 8 vs 12 seconds per object), which pays off at hundreds or thousands of objects.
  • Prompt caching: the static system prompt goes first, so repeated calls share a cached prefix.
  • Reasoning effort per model: low for Nano first drafts, medium for Super, high for Ultra repairs.
  • Structured output: an optional JSON mode for SQL replies.
  • Oracle Free in Docker on a Google Cloud VM as the source of truth, and Snowflake as the target.
  • Tavily for doc research, restricted to the official Oracle and Snowflake docs.
  • Streamlit demo app with a live progress bar and activity feed, scorecard, side-by-side code view, cost breakdown and a peek into both databases.
  • Reliability: a 150-second timeout per model call with retries, one live run at a time, runs that keep going if the page is refreshed, a daily run cap and a judge access code.

Security

For the demo, the Oracle database runs on a small cloud VM so judges can try it live, protected by an access code and a daily run cap. In production, for banks, insurers and other regulated companies, the database never leaves the company's network: a worker inside the network pulls migration jobs, customer values are masked before any model call, and every change comes with a row-level proof and cited sources that an auditor can review.

Challenges we ran into

  • Sorting looked right but wasn't. IDs loaded into Snowflake as text, so ties sorted differently and the answer key itself was scrambled. Casting them to NUMBER fixed it, and it taught me to verify the verifier.
  • Masking hid the bug. My privacy mask turned '' into a placeholder, which hid exactly the Oracle empty-string trap the model needed to see. Now empty strings stay visible while real values are still masked.
  • Reasoning models think out loud. Nemotron sometimes wrote old drafts of the SQL inside its reasoning. I built a cleaner that strips the reasoning and keeps only the final SQL.
  • Two runs on one database. Two live runs at the same time rebuilt the same Oracle tables and broke each other. I added a run lock with automatic cleanup of stale locks, so only one runs at a time.
  • Slow replies stalled runs. One slow model call could hang a run for 10 minutes. Now each call has a timeout and retries, and a failed draft goes straight to the fixer instead of stopping the run.
  • Dialect traps: recursive CTE type mismatches, NUMTODSINTERVAL (unsupported in Snowflake), and the '' vs NULL difference.

What I learned

  • A small model gets most of the work right when it gets a clear "your proof failed, here's why" signal. Escalating beats starting big.
  • Row-level proof is what makes automated migration trustworthy enough for production.
  • Citing the official docs makes every fix explainable to a reviewer.
  • Saying "I couldn't prove this one" is a feature, not a failure.

What's next

  • A "bring the code to the data" production mode: a worker inside the customer network pulls jobs, so the database is never exposed.
  • More sources: Teradata, SQL Server and Informatica.
  • Databricks as a second target.
  • A migration sign-off report generated from the proofs.

Built With

Share this project:

Updates

Submission history