SAS to Databricks: Real Migration Costs & LLM Hybrid Approach

0:04hey folks today I have with me Mike Perlo CTU of Alchemist hi Mike hi hello everyone hi how you doing all good all good I'm very pleased to meet you uh please can you introduce yourself to the viewers yeah and I I'll be a bit boring and I'll use a presentation for that if

0:26you don't mind and and for for the rest of the talks so I'll just share the screen so yeah I'm I'm Mike peof I'm a former date engineer I was doing date engineering I was a date architect I was a consultant I even had like 100 people working for me at some point but basically I was doing data right and analytics and and everything when it was still called Data

0:48warehousing so before the lake house I guess and now I'm uh at some point I figure out that I can help people like me or the customers to kind improve efficiency when they uh essentially migrate systems and work with data engineering so I I built a tool and still building with the team uh that is helping to convert specifically in our

1:11case we're focusing on sus which is one of the Legacy systems but generally a conversion and analysis tool for code that's that's that's me yeah and I checked your uh your resume or like your LinkedIn page and I was amazed like resume is so so big we did so many things so I understand like uh how you were able to let's say to uh

1:34build Alchemist and by the way can you tell us more about Alchemist yeah I so this talk uh in general today I wanted to address two things that are it's not related to Alchemist itself it it's related to migrations and the things we're working with and I hope it will be helpful to anyone so it doesn't matter which

1:56technology I mean obviously migrating to data braks I expect but because that's the technology but it doesn't matter where you're coming from right uh and Alchemist I I will just I my experience is coming from specifically Alchemist and the customers uh that we're working with right on migrations and uh you'll see I'll show Alchemist is just one of the tools that people use to automate

2:19portions of migrations and make it well feasible cheaper uh and more efficient basically so it's it is a code converter I I will briefly um uh show what Alchemist does but mostly this is a general discussion and also a discussion of well where the llms uh what's what's their role in in this whole process

2:41obviously so we all know about LS and we can just ignore them right of course and I'm I'm curious to know do you migrate specifically from cloud uh to data brakes or you also Target the UN premise uh migrations so so uh so I'm I'm actually part of a Consulting like a small

3:03consult still a consulting company is we just I'm like a startup within a company and I'm I'm strictly focused on the software portion that is specifically related to the SS code migration which is usually on premise uh because they only recently started doing uh cloud like Cloud versions of it right so I'm focused on the old you know like Legacy

3:27migrations Legacy migrations what I will be saying is probably relevant to Cloud migrations as well so for example if you you want to migrate red shift to datab brakes you would probably have at least some of the same issues and some of the same questions and you would also probably use similar tools to Alchemist uh to do the migration and you would

3:50probably use the AI in the same fashion as as well and can you walk us uh uh through the process that you follow in order to uh have a successful migration like what are the steps we need to follow uh sure uh so in general well usually the migration and and I will

4:12kind of touch upon that as well I wanted to touch upon that but in general migration is you need to assess right what you migrate and uh it's actually very important is when you migrating Legacy stuff it's usually huge right and this is I will be talking about the cost you see and uh it's very important to understand what to migrate and remove

4:35stuff you don't want to migrate because that's this one of the ways how to reduce the costs H so this is the first thing you do then a very important thing is to phase it out so trying to migrate if you can try to migrate in phases is much better way to get to success and so you you you have to plan and then within each like as you walking

5:00through the phases you build up some knowledge about how you migrate you build up some let's say if you're using models AI models you may be fine-tuning them to know your code better or if you're using tools like Alchemist or other tools you building configurations for this tools uh and you basically scale out and you speed up with your migration so the third step is basically

5:23well the scale out portion right where you do a bigger bigger bigger phases and quicker and more and more efficient so roughly three steps assess plan do a first phase or a couple of simple phases and then scale out yeah that that that makes sense and I'm really curious to see I'm I'll be waiting for the for the slides how you able to add the Gen to this migration uh

5:48process yeah so if you don't mind I actually wanted to touch two topics an AI kind of II is a second topic so wait for it but basically I I wanted to first touch one thing that unfor unfortunately most people kind of Miss on is the cost and why it still May and sometimes the people who do realize the full cost of

6:11migration why it's still worth it and then how gen and tools like Alchemist or other static analysis tools actually help to bring down the cost to some manageable levels right so and regarding the costs right uh I have this you know my kids are in elementary school so they they get like problems of the day like that so I just use the analogy so uh

6:34like you know imagine you have you like a big Enterprise right and you have 20 and sometimes it could be 50 analysts developers whoever is working on your data infrastructure and they've been doing it for 10 years and you have a huge system you have reports and etls and whatnot right and and they worked on it for for 10 years right and you also paying let's say for some Legacy system

6:56you're paying for a license in Hardware I don't know maybe 50,000 k a year so the question how much should you budget for a migration so most of our customers actually ask this question uh usually before we uh join into the fry I think datab braks is usually the same so the typical the typical thinking is well the good project is the one that has Roi in

7:19one year and by Roi think people are thinking okay we're going to datab briak is well much cheaper right it's going to be much more efficient so we will just reduce the cost of the license and Hardware right and so the project has to be I don't know at maximum $500,000 because that's how much we going to reduce the costs in a not not 500 but something like that in a year so

7:41unfortunately this is not like this is not true uh in reality if you would count how much it was it cost you to build this system in 10 years with 20 people even if you have very cheap employees right it's at least and I calculated the numbers you can do it just itself it's like at least $10 million maybe $20 million so

8:04unfortunately no matter how good the automation or whatever there's just so much knowledge in in it that it the honest price is 3 million and if you accepting that the price is lower you basically setting yourself up for change requests in the future so you you like someone from or you're not you're not actually talking about the full migration something like that so it's

8:26important to understand the true cost and at this Point many Enterprises will say oh wait a second why would I even migrate it's like it's ridiculous right it's just I mean it's a six-year TCO for for the current system and you should bother because the way I think about Legacy migrations is that it's it's it's essentially a

8:48ticking bomb it may not like you may not have a disaster today maybe not even tomorrow but you will it's it's essentially the same issue and it's actually very connected to security right so you have all the data breaches right now there was like a major one I think two weeks ago last week last week we all heard about it like there are a lot of breaches and uh

9:11they also happen in new systems obviously but but the reason they happen is because you have a system with a lot of code no one looked into it there's a lot of security issues so you by doing a migration not just changing the platform and reducing cost or improving performance that's not you also removing the technical dat you will actually

9:34probably remove some Legacy parts of your code that is not used right you renew the knowledge the knowledge right so you your analyst will remember what they're doing right and you will be able to on board and better educate the new people who are joining so it's very important to renew your system and this will fix security issues and uh again

9:57you may not do migration today you may think okay that's too expensive I'm going to postpone it for whatever but eventually the business will break down eventually and I I've been there your analyst who did a very big ATL job uh that they executed manually will let's say change the company or retire for example and suddenly like all the hell

10:19broke loose and and it's it's too late already and you obviously reduce you I mean if you count on fiveyear Horizon or six year Horizon usually you would reduce your TCO as well so so again migrations are unfortunately still I mean at least at this level of llm llms and everything are still kind of expensive and not only because of

10:41conversion but other processes but they still worth it right I mean I in my opinion you don't have a choice you just have to prioritize which migration if you probably have multiple in your Enterprise which of them to tackle first and this where the AI portion comes in so I know you like this is what

11:03you've been waiting for right exactly so so like so will J solve all the issues so so the answer is I mean honestly I'm the believer that probably J will replace everything So eventually yes but eventually is we can all speculate when but

11:27definitely not today it is part a solution and I I will illustrate this so what I did is and this is public so anyone can open it I used the one of the frontier models and I gave it a this is slightly up but it's a real code from a real customer from SAS and you don't have to understand it but basically it's a more kind of more elaborate it's not

11:49very complicated but not basic SQL for example and I gave the code and it's poorly commented the names of the columns are kind of not very descriptive and the result this is completely incorrect so the way the model converted it is entirely incorrect but but bear with me if you use a more descriptive like some

12:12comments more descriptive comments so if your analyst for example developers are doing kind of being more explicit it suddenly gives you a correct conversion more Contex that makes sense yeah it kind if you give it a context and what's important and it will feed into my what what is the actually the real solution is if you annotate and give

12:37explanations of what each kind of piece of code is doing and bear with me this can be done automatically so this the first what I just show this subject to your to how your users are coding and they can code in different ways but you can automatically annotate and explain to AI what the thing like what every piece of the code is doing and when I when you

13:01annotate this code and run it again it produces the correct result so AI is definitely helpful and getting much better even this uh like year ago uh none of the frontier models could convert sus at all and now they can and so they're getting better right but essentially just using a is not enough

13:26it's important and in a big and also in a big migration you want to make sure that you establish the same the code are the same the patterns are the same right so you want to have like uh you don't want to have random code like different code you want this to be you know like all the code looks the same so you have to combine you have to combine AIS with tools uh like Alchemist and here's like

13:51I'll just briefly explain what the tools like Alchemist do this is from my Alchemist picture but it applies to anything so basically Bally this you have tools that are like just like compilers that compile you know like programmers WR in Python data Engineers WR in Python and it executes on machines on on databas clusters there is a compiler behind it uh the compiler reads

14:14the code it there's no llm it's just an algorithm that reads the code understands it and translate it to machine so machine can run it so it's essentially the same technology it reads the code but instead of translating this code into machine like into actually executing it it can translate it to other code so for example we read SAS

14:37code and we can translate it to bpark code that does the same thing and we can also and this is important analyze the code and this we can present the code as data so we can say here's what the columns of this table are we can just guess from from the code here are the data types here's what the St statements

15:00do so we have all this knowledge that the AI is lacking is lacking so the optimal uh architecture uh that we are going to is basically to combine the power of two so you take a tool that is statically converting the code whatever you have right so you have Oracle there are tools for that if you have I don't know you

15:23have informatic ET there tools for that and U and you convert everything that the tool can convert automatically using the tool because it's stable it will give you the like the same code all the time it will be you can set up usually some configurations so you'll get what you want and then it annotates the remaining code that it can

15:46convert and feds it into let's say like in databas right you can run any model you want you can fine-tune the model on your code it fits it into that code and this whole framework is the optimal in terms of efficiency today so until llms are more smarter than all the like the humans like in in a broader sense

16:08and they can do the task end to end this is the way and you don't have to believe me I I I also left I don't know you you can like probably add this link slack team did a awesome very long it's a kind of long read it's a technical technical article where they were migrating from uh one Java testing library to another Java test testing library and they

16:30actually implemented this from scratch and they have some the case study includes like how many hours they estimated it will take manually uh what challenges they had with llms and how they address them but basically there are cases where people successfully use that and this is how our teams are using alchem like our users and clients use that right so they use Alchemist for everything that's can be automatically

16:54converted and they feed the metadata that Alchemist provides into well llms if the customer allows it or at least into some Frameworks uh that well that they set up um yeah so this is what I suggest and uh and this is the state-ofthe-art optimal approach right now um it may be different in a year we

17:17like the speed of innovation right now is very high but that's basically it and um I've seen that you're pointing out to uh m to Lama Cod Lama like like how many models have you tried and have you noticed any any difference in regarding the

17:39performance or yeah I I I can so so first of all it heavily depends on the kind of Legacy system you have uh so some systems like for example if you're migrating from postr there's a lot of Open Source Code in there and they are um or my myle for example right so there's actually very good tools to

18:02automatically convert the SQL code without any use of llms already and then llms know the language really well there are still uh there are SQL specific like for example ddb they introduced their du DB fine tuned model for SQL conversion uh also obviously the SQL

18:24itself is very simple but usually in the big systems there's procedural languages right so in in in Oracle you have pale SQL for example in po you have PG SQL and all of that stuff so this is trickier because then it it's harder to convert but again the more open uh and open sourced I guess the system the better the outof thee boox models will

18:47be so SAS is closed so it's you it's almost impossible to find like you have some code open source right there's some stuff on the internet but uh for example SAS is very specific it has non-typical struct like it has language that doesn't have analog right in the in the modern world so it would struggle more and it

19:10would make even more sense for you to find tune the model so if you going through migration and you have things that people re Rewritten themselves you can fine-tune it right uh same goes to other like systems right even for example we've been working with teradata code there are open source SQL converters so you would expect that maybe terad dat is handled but

19:33apparently this open- Source converters do not handle some of the common stuff that we've seen in real life exercises so because teradata is inherently enterprise system there's not so much open code for uh just typical Frontier models to learn so it really depends and it does make sense to if you scaling it

19:55out you would probably start with a smaller model right you may train be X or you can use whatever I know code llama and you can fine tune it for I would say I was really surprised I I actually tried it before this uh uh recording uh the recent Frontier models on SAS I was actually surprised they got much better I don't know how they did that I honestly don't understand where they got the some understanding of

20:19sus but in some cases you will need to defer to I guess Frontier models um and I don't know I mean I can mention which one but I guess everyone knows there's like there's what two three of Frontier models so yeah I hope I answer the question yeah yeah that's um I really love the like the process and how how it was

20:42developed it it can give you more confidence on when it comes to migrating the code of course not everything can be streamlined you will see it to some tweaking but if you can upload some task that it still that it's still let's say can help the this process and make it maybe let's say

21:04cheaper you no it is I mean without that so remember I was I said about the costs yeah so if you when I said like the costs of migration are proportional to the cost of building the whole thing right so if you build it for 10 years it will be proportional if you're doing it literally manually if you just like sit down with a bunch of developers who would just re like look at the old code

21:27and try to rewrite it the cost will be can easily be the exact same amount as the whole system I mean honestly there's no way you can convert in time or at at cost so you have to use automation I'm just saying that this is the most effective way how you can reduce the like this is you can go better than that and the testing is very important uh and

21:51actually most of the time in migration is spent not on the conversion of the code this is not this the most of the time goes into testing and fixing the discrepancies because the code may look correct and may even execute but it will give you different results on real data and some and you need to run on full data and it usually takes time and so so

22:15having tests especially autogenerated test is as crucial as you know using automation for code conversion and again static analysis tools unlike AI llms can help you generate the tests because again llms will not give you consistent tests right you can rely on and and you have to rely on some

22:37classical analysis of the language to do that and by the way I've been I read the blog post I think this has been released maybe two years ago on the four steps to migrate SAS data wehous uh lake house I think that was in September 2022 and I've seen like the step

22:59similar but now we have let's say the help of the Gen or the fine tuning to yes Absolut because because this post was written I think I I I I maybe I was one of the authors I don't remember you were yeah yeah you it was written be I think it was written before CH GPT announcement so I I were we were using

23:21for development I was using co-pilot which actually came before uh chpt so I knew about the stuff but I I we haven't we all haven't seen yet uh how effective it is you know in general sense so yeah this was as I said two years passed and the recommendation is slightly different

23:43I would say uh uh maybe in I don't know in five years uh we'll get a super intelligence and I mean all of this will be mood we just okay here's Sam Alman built a trillion dollar Data Center and it it just it just doesn't or maybe qu Computing who knows yeah yeah yeah yeah I I I I don't know I don't know so it's honestly I can say I

24:07I'm I'm not trying to predict future but I can tell you right now that's that's the way to go and uh before closing is there any advice you want to give to anyone or any team uh who who will go through this process well one advice is be honest with yourself when you do at least in

24:29internal estimates and if you well if you understand it's a lot go face by face start small do like a small project you'll see the value yourself you will prove the value for the general uh organization you will learn stuff right so do not be discouraged right uh and the second advice is obviously keep Pace

24:52with uh the how geni is U kind of evolving because again this make change and third is definitely don't overlook the testing right so if someone tells you that we're going to convert your code for whatever A Penny uh and they don't and it doesn't

25:15include any testing it like basically you're only looking at 20% of your costs potentially so make sure that you when you think about the costs for you you think about how you're going to test on on your data that's very important and I think I forgot but I want to ask you to show us uh what

25:37Alchemist looks like ah I mean yeah I I I didn't want like I didn't want to break so so Alchemist honestly it's it's really it's really boring if I would show the kind of the conversion piece but like as I said we have two things one is analyzing the data and another one is converting so this is how our dashboards look this is basically data based on the code uh in case of sus

26:00right so it and it does all kinds of fancy stuff it understands what kind of code and classifies it it it gives you some complexity so it usually the way people use that is they start by understanding their environment as I said and figuring out what they need to address right so and how complex the task is right how much code do we have you know that kind of stuff and and

26:24there's also a conversion I I honestly I didn't prepare I can show like maybe converting some code but essentially it's a CLI tool that will just spit out the converted code uh into notebooks um it has some fancy features because we I am a former consultant and basically building a tool for uh Consultants mostly and customers uh we're trying to

26:47build a tool that is convenient to use in the context of migration right so it has some features not just converting code but giving you I don't know if you want Source uh giving you some information and helping you to feed that into llms when it just can't convert you're basically filling the Gap that you faced when you were a consultant yeah we were actually were

27:09using other tools we didn't like the other tools for our purposes we thought it it didn't make our migrations efficient so I just sat down and I I've been a developer like my background is in development so I'm like oh whatever I just I will I will call the thing myself so so yes uh we're trying to make a consultant or an internal engineer work

27:32easier right to make migrations as feasible as cheap and as efficient as as is possible basically and and by the way for those who are watching the uh the recording we're going to be sharing in the description of uh the video all the links that uh that Mike was uh was showing and I would like really thank

27:56you for uh for your time it was a very insightful uh discussion I thanks to you I know more and more about uh the process and how it can be done and any any any closing on your side like no thanks youf I I really appreciate the opportunity I know I'm talking a lot I maybe this's going to be a

28:19lengthier uh video on but I hope people will enjoy it feel free to reach out to us or even maybe you want to ask I can recommend tools for other whatever languages I I know at least a couple uh so always happy to talk thank you thank you Michael thank sorry thank you Mike yeah no no it's it's fine yeah

28:43and that's important I have the right te right on me so y very right wish you a good day yeah you too you too byebye