How Erasmus Migrated T-SQL to dbt on Databricks: AI-Assisted Migration with Lakebridge

Transcript

1. AI-ASSISTED MIGRATION WITH LAKEBRIDGE Ellen van Bergen Murat Can Çakır 9 september 2026
2. INTRODUCTION Ellen van Bergen Murat Can Çakır Lead Platform Engineer Erasmus MC Senior Data Engineer Connecting data, AI, and infrastructure to empower research, innovation, and healthcare Xebia - Data Helping organizations across industries build data platforms on the cutting edge
3. AGENDA 01 The challenge of migrating to the cloud 02 Deep dive Lakebridge 03 Demo 04 Learnings 05 Q&A
4. 01 THE CHALLENGE The challenge of migrating to the cloud
5. CAN AI MIGRATE A DECADE OF SQL? +10 years Hundreds Databricks + dbt of SQL Server logic of views, procedures and functions where we want to go Could AI do the migration? That's what we set out to learn.
6. On premise THE LEGACY DATAPLATFORM 24 66 sources staged — the biggest: EPD HiX FHIR resources · 591 mappings in the DWH [[Foto]] 30+ ~15 active Power BI dashboards — some with 100+ users data engineers in our department And research runs on it too 292 publications · 8 data science use cases · 100+ research datasets
7. Target Cloud Platform THE ERASMUS DATAPLATFORM
8. Context WHY THIS IS PAINFUL TODAY ▪ Manual rewrites don't scale — a single domain has hundreds of views, procedures and functions ▪ Dialect gaps everywhere — TOP, ISNULL, GETDATE, temp tables, procedural logic — no 1:1 Spark SQL equivalent ▪ Architecture shift, not just syntax — procedural warehouse logic must become declarative, layered dbt models ▪ Inconsistent & error-prone — every engineer translates differently; reviews are slow; regressions slip through ▪ Repeated per client / per domain — the same grind starts over on each new schema
9. THE MIGRATION ESTIMATE How long would this take by hand? ~4 h 883 Mappings (across 66 FHIR resources) and publications to rebuild × per mapping by hand — translate, review, test* 1+ year ~3,500 h ≈ ≈ 440 person-days of engineering ≈ alongside daily work for our team Too long. And that is before repointing 30+ Power BI dashboards, republishing research datasets and revalidating 8 data science use cases. So we looked at AI. * ~4 h per mapping is our own average estimate — includes translation, review and testing.
10. HOW DID WE START? ▪ We started by testing AI SQL Server Lakebridge dbt Engineer review Production exported .sql LLM transpile models + tests validate + harden deployed Small experiment first: a handful of objects through the pipeline, engineers judging every line.
11. AI TRANSLATES; ENGINEERS REVIEW What AI did What engineers did – Translation – Business logic validation – Boilerplate – Performance tuning – Initial model creation – Data quality checks – Edge cases
12. 02 DEEP DIVE LAKEBRIDGE The accelerator, Lakebridge Switch, and the architecture
13. Overview WHAT THE ACCELERATOR IS A repeatable, hub-led pipeline that uses an LLM to auto-translate Legacy SQL into dbt SQL — at batch scale, entirely inside Databricks. Removes the grind Reusable as a product Not a silver bullet Automates repetitive SQL rewriting with an LLM transpiler (Switch) Drop in a client's SQL, tune one prompt, run LLM output is a DRAFT, not a guarantee Classifies & routes objects into dbt structure automatically Each batch isolated by a HUB_NAME Translates code, not data / grants / schemas Ships a jaffle_shop sample to try end-to-end Human review + testing stay on your side
14. The Engine HOW LAKEBRIDGE SWITCH WORKS ▪ An LLM-powered SQL transpiler, open source from Databricks Labs ▪ 40+ source dialects — not just T-SQL ▪ One file in, one file out — each translated on its own ▪ Runs as a job inside your own workspace ▪ The prompt is the product Supported sources: MSSQL · Oracle · Snowflake · Teradata · Synapse · Redshift · BigQuery · DB2 · Netezza · Postgres · Hive
15. Architecture THE END-TO-END PIPELINE INSIDE THE DATABRICKS WORKSPACE SOURCE NB 01 NB 02 OUTPUT SQL Server UC Volume Switch job Shared dir dbt project export .sql (SSMS) input + prompt LLM transpile transpiled .sql bronze/silver/gold Foundation Model API Claude · pay-per-token HUB REVIEW compare vs source SQL · dbt compile / sqlfluff lint / dbt test · domain sign-off → Pull Request NB 03
16. Roles TWO AUDIENCES — HUBS DO THE WORK HUB — the main user PLATFORM — enables it once • Domain / data engineering team • Prepare the workspace once • Export source SQL, run the batch → 02 → 03 • Install Lakebridge + Switch — as a Service Principal • Create UC schema + volume • Review & harden the dbt output • Open the PR • Frequency — notebooks 01 — EVERY batch • Hand hubs a ready Switch job • Frequency — ONCE per workspace
17. 03 THE HUB WORKFLOW Three notebooks you run per batch — input prep, transpile, map to dbt
18. Hub workflow THREE NOTEBOOKS, ONE CONFIG Edit config/config.yml once per batch — every notebook reads it via %run ./utils/helpers. 01 input_prep 02 run_switch 03 map_to_dbt • Classify SQL • Find the Switch job • Route transpiled SQL — views / procs / functions • Trigger with params • Upload to UC Volume • Stage prompt + config Volume — input/output, model, config — onto the • Poll every 30s inspect output — bronze/silver/gold, macros, ops • target: metadata + prefix — print run URL, — heuristics • DRY_RUN / OVERWRITE flags
19. Configuration THE FILES YOU ACTUALLY EDIT config/config.yml — edit once per batch Hub name + email, UC catalog/schema/volume, foundation model, source dialect, optional path overrides. helpers.py expands ${volume_root} / ${catalog} / … and exposes derived paths to every notebook. config/prompt_mssql_to_dbt.yml — the LLM prompt Layer rules, naming, source()/ref() mapping, dialect translation, few-shot examples, target: metadata. 80% of your tuning time lives here — this is how you adapt to a new client's schema. config/switch_config.yml — Switch run settings Foundation model, concurrency, token threshold, target_type: file, output_extension: .sql, prompt pointer. Self-contained: Switch loads only this file (notebook 01 renders a per-hub copy onto the Volume).
20. Notebook 1 INPUT PREPARATION ▪ Drop the exported .sql files into one folder ▪ Classify them: views, procedures, functions ▪ Stage everything on a Unity Catalog Volume ▪ The prompt travels with the batch Gotcha Copy is idempotent — a re-run shows files as "skipped (already exist)". REPO_DIR must match your workspace folder name.
21. Notebook 2 RUN SWITCH ▪ Trigger the shared Switch job from a notebook ▪ Each batch runs with its own config ▪ Watch it live — the run URL is printed ▪ Output lands in a shared folder, ready to map
22. Notebook 3 MAP TO DBT ▪ Each translated file lands in the right dbt layer ▪ Bronze, silver, gold: decided by the prompt ▪ Dry-run first: see the plan before anything moves ▪ What comes out is a starting point, not a release ▪ You review, test and harden before it becomes a Pull Request
23. 04 DEMO jaffle_shop end to end — run notebooks 01 → 02 → 03
24. WHAT WE LEARNED 1 AI is much better at migration than we expected 2 AI still makes predictable mistakes — and we can keep improving it 3 The bottleneck moved from coding to validation
25. Q&A