The first stored procedure I gave the AI was about 600 lines of T-SQL that built our customer dimension. It had cursors, three temp tables and a comment at the top: "do not touch, ask the previous team." I never learned which previous team.
Fifteen seconds later I had a PySpark version. It ran first time and the row counts matched the old output, so I felt pretty clever for a day and a half. Then I spent two days finding four places where it was wrong. Nothing had failed. The new code simply behaved a little differently from the old procedure, and most of this post is about that kind of difference.
What we were moving
Ten years of SQL Server. SSIS packages loaded the data, stored procedures transformed it, and SQL Server Agent ran everything on a schedule somebody set up in 2015. The target was Databricks on Azure: Delta tables, Spark SQL, PySpark, and Lakeflow Jobs (the new name for Workflows) for orchestration.
Nobody wanted to pay for a redesign, so the plan was boring on purpose. Pick one pipeline, translate the code, keep the logic. I used Databricks Assistant for small pieces and a general chat model when the code was longer than a screen.
I didn't measure anything, so there are no before-and-after numbers here, only impressions and what I'd tell a colleague who's about to start.
The easy part
Most of the T-SQL to Spark SQL conversion is a long list of small swaps, and the AI did them faster than I would. ISNULL(a, b) becomes coalesce(a, b). Spark has an isnull() too, but it takes one argument and returns a boolean, so the name is a trap (ifnull and nvl take two arguments, like the T-SQL one). TOP becomes LIMIT, temp tables become views or CTEs, and square brackets turn into backticks or just go away.
DATEADD mostly survives, since Databricks has dateadd(unit, value, expr) with the same argument order. There are two catches. The unit has to be written out, so T-SQL shorthand like dd or mm won't parse, and the three-argument form returns a TIMESTAMP, so anything that used to be a plain date needs a cast back. For simple transformations I read the diff, ran it and moved on, which saved a lot of time.
Explaining old code worked better than I expected. I pasted in procedures of 800 lines, asked for a step-by-step walkthrough, and the answers were right often enough to be useful. I built an inventory from them before changing anything: what each procedure reads, what it writes, and a guess at why it exists. I checked every summary against the code, but checking is a lot cheaper than reading cold.
Scaffolding was the other win. Delta DDL, job definitions, config files, and the reconciliation notebooks that compare old and new output with row counts, per-column aggregates and row-level hashes. I hate writing those by hand and the model didn't mind at all.
Where it went wrong without telling me
Our database used a case-insensitive collation, so 'ABC' joined to 'abc' and nobody had thought about it for years. SQL Server also ignores trailing spaces when it compares strings with =, so 'foo' equals 'foo '. Spark compares bytes. By default neither of those joins matches.
You can get the old behavior back in Databricks:
SELECT 'ABC' = 'abc' COLLATE UTF8_LCASE; -- true
SELECT 'ABC' = 'abc ' COLLATE UTF8_LCASE_RTRIM; -- true
UTF8_LCASE gives you case-insensitive comparison from runtime 16.1. The RTRIM modifier arrived in 16.2 and works with UTF8_LCASE as well as the locale collations, so UTF8_LCASE_RTRIM covers both problems. SELECT name FROM collations() shows what your runtime supports. You can also set the collation on the column in CREATE TABLE and skip repeating it in every join. The default is still binary comparison.
Pattern matching is the catch. LIKE, ILIKE, RLIKE and the regexp_* functions are documented for UTF8_BINARY and UTF8_LCASE only, and on locale collations such as UNICODE_CI they throw an error. The RTRIM variants aren't on that list either, so test your pattern matching on those columns before you trust it.
The AI translated every join faithfully, and the ones that relied on the old behavior started losing matches. Our dimension lookups are left joins against a table with one row per key. When a key stops matching, the row count doesn't change and the looked-up columns just come back null. That's how the customer dimension passed the count check and was still wrong.
Afterwards I asked the AI which SQL Server behaviors might differ on Spark. It gave me a good list: collation, trailing spaces, datetime precision and a few more. So it knew all of that and said nothing while translating my code.
Datetime precision was a smaller version of the same problem. SQL Server's datetime rounds to increments of about three milliseconds (.000, .003, .007), while Spark timestamps go down to microseconds. Values the new code computed at full precision had digits the old columns could never hold, so row-level hashes differed on rows that looked identical on screen. The reconciliation notebooks flagged this one immediately.
The MERGE at least failed loudly. One procedure ran an UPDATE joined to a table where several source rows could match the same target row, and SQL Server picks one of them arbitrarily and says nothing. The AI rewrote it as a MERGE into a Delta table, and Delta refused to run it because multiple source rows were trying to update the same target row. That error did us a favor. It exposed a data problem that had sat in production for years behind a system that never complained.
What the model had no way of knowing
It only knows what you paste in. So, it couldn't know that procedure A has to finish before procedure B starts, because that ordering lived in the Agent schedule as a forty-minute gap someone left on purpose. I broke it by migrating file by file. When I wrote the schedule down and started moving whole pipelines together, it mostly stopped.
The style of the generated code was annoying rather than dangerous. Left alone, the AI wrote long DataFrame chains with caching and repartitioning I hadn't asked for. My team reads SQL fluently and PySpark slowly, and a 15-line Spark SQL statement is much easier to review than a 40-line chain that does the same job. Cursor logic sometimes came back as a Python UDF, which works, but is extremely slow.
It also made-up function names and options now and then. A made-up function fails straight away with an error. A made-up option can be worse, because Spark often accepts reader options and config keys it doesn't recognize without complaint.
Testing took over
Before this project, translation was the slow part and testing got whatever time was left. With the AI, translation took minutes and testing became most of the work. We ran the old and new pipelines side by side for a couple of weeks and compared outputs every night. The notebooks wrote mismatching keys to a table, and each morning I went through that list until it was empty. Most of the mismatches were collation and whitespace. A few were real bugs in the legacy code. One or two were mine, and I'm still a bit embarrassed about those.
Some of the old behavior was deliberate, and the reason had been lost, in someone's head, in an old email, or nowhere at all. The AI can write the code, but it can't tell you whether the odd filter on line 212 is a bug or the one thing keeping finance's numbers right.
It feels fast in the first weeks because code keeps appearing, and the cost of testing only shows up later, often after someone has already told their manager the migration is mostly done.
What I'd do differently
Start with the inventory and make it wider than tables: the schedule, the dependencies, who owns what. I did mine halfway through and wished I'd begun with it.
Migrate whole pipelines instead of single files and keep the old version running next to the new one until the numbers agree.
Give the model the known traps along with each procedure. After the second collation bug I started pasting in a short list of SQL Server versus Spark differences, and the translations improved noticeably. Style works the same way. A few lines saying prefer Spark SQL over DataFrame chains, no caching unless asked, and no UDFs for anything that can be done set-based got rid of most of the over-engineering.
Anything that touches money needs a person. For the finance logic I read every line and did the reconciliation by hand, and only afterwards, sometimes, asked the AI to review my work.
I'd do it again, and I wouldn't start another migration without an AI assistant, but I'd plan much more time for testing.
If you are planning a move from SQL Server or SSIS to Databricks and want the inventory, the reconciliation and the testing planned before the first procedure is translated, this is the kind of migration we run with clients. Talk to our team about what it would look like for your pipelines.
Sources
- Azure Databricks docs - Collation (UTF8_LCASE, RTRIM modifier, supported operations)
- Azure Databricks docs - dateadd function
- Azure Databricks docs - Upsert into a Delta Lake table using merge
- Azure Databricks docs - Lakeflow Jobs
- Microsoft Learn - datetime (Transact-SQL): accuracy rounded to .000, .003 or .007 seconds
