Library · Data Practice OS

Code conversion

Reference guide · Published · Source reviewed 2026-11-06

The answer: let the free vendor converters do the bulk, have engineers use LLM assistance for the rest, and land the output as dbt models in the Medallion architecture layout, not as a pile of converted stored procedures. Nothing converted is accepted until it passes the rule tests from Extracting business rules and reconciliation.

The conversion stack

Layer	Snowflake target	Databricks target
Bulk SQL and procedure conversion	SnowConvert AI	Lakebridge transpilers
Ingestion (replacing simple ETL loads)	Snowflake Openflow, connectors, Change data capture	Lakeflow (Connect, Declarative Pipelines, Jobs) Connect
Transformation	dbt models, Dynamic Tables for incremental	dbt models, Lakeflow Declarative Pipelines
Orchestration	Tasks or external orchestrator running dbt	Lakeflow Jobs running dbt
Residual hard cases	Engineer plus LLM, with tests	Engineer plus LLM, with tests

Using dbt as the target keeps transformation logic portable across both platforms (ADR-003: dbt as the transformation standard). That matters while the platform choice is still open.

Converting ETL: replace, do not translate

Most SSIS and Informatica packages are 60-80% "copy table A to B with light cleansing". Don't convert those. Replace them with managed ingestion into bronze and a dbt staging model. Only the packages with real logic (lookups, conditional splits, derived calculations) need their logic converted, and that logic should already exist as rule cards.

Using LLMs well

LLM-assisted conversion is effective for the residue the tools cannot handle: dynamic SQL, cursor loops, vendor-specific functions, and procedural code rewritten as set-based SQL.

Rules for the practice:

Tests before conversion. The rule cards and their dbt tests exist first, so the engineer and the model have a target.
Small units. Convert one procedure or model at a time, with its inputs, outputs and the relevant rule cards in context.
Set-based rewrite. Ask for idiomatic dbt SQL, not a line-by-line port of a cursor loop.
Human review, always. The engineer owns the pull request. The model is a fast first draft.
Approved route only. Use a sanctioned model access route (AI gateway and model access). Legacy code can embed connection strings and sample data, so never paste it into public tools.

Prompt skeleton (per unit):
- Context: source dialect, target (dbt on Snowflake|Databricks), naming standards
- Input: procedure text, input/output table DDL, rule cards REG-FEE-011..019
- Ask: one dbt model per output table, set-based, incremental where the source has updated_at;
       list every assumption; map each rule ID to the line implementing it
- Output is then run against unit tests + a reconciliation sample

Notes by source type

CA Gen / DB2. There is no SQL to convert for the business logic. Rebuild from rule cards in dbt, and replicate DB2 tables with CDC into bronze.
SQL Server / Oracle. Converters handle DDL, views and most procedures. Watch for implicit type conversion, NULL and empty-string handling (Oracle treats '' as NULL), collation and case sensitivity, date arithmetic, and MERGE semantics.
Hadoop / Spark. PySpark moves to Databricks largely intact. For Snowflake, use Snowpark or rewrite as SQL. Check UDFs and Hive SerDes.

Worked example

Wave one covers the registrations mart: 120 procedures and 45 SSIS packages. Thirty SSIS packages are simple loads and get replaced by CDC ingestion. The converter handles 98 procedures automatically, and engineers review the output and refactor it into 64 dbt models (several procedures collapse into one model). The remaining 22 procedures, mostly cursor-based fee calculations, are rewritten with LLM assistance against 95 rule-card tests. Two weeks of Parallel run reconciliation surfaces one rounding difference, traced to a MONEY to NUMBER(18,2) mapping, which is fixed with a test added.

Pitfalls

Accepting converter output as done. It compiles. That does not mean it is correct.
Preserving procedural style. Converted cursor loops are slow and expensive on consumption platforms. Refactor to set-based SQL.
Converting dead code. Retire first (Migration assessment).
Losing lineage. Map every legacy data object (tables, files) to its new one in a tracking table, and use that to plan decommissioning. Trace logic through rule ids instead: legacy code -> rule <- model. Never map a legacy program directly to a model, because one program carries many rules and one model implements parts of many programs (draft practice, Tracing rules from legacy code to modern code).