Databricks SQL: ETL Migration Strategy with Franco Patano (Part 2)

0:04Hey folks, in the last episode I did interview Franco Patano and we end up the episode with a very interesting question about which language to use because now you know that you can develop ETL using Python or SQL and maybe Franco will enlighten enlighten us regarding which technology to choose.

0:26>> You know it's the it's the unfortunate answer. It depends um you know it depends on a lot of things but just to kind of give a little bit of the why of the what on Databricks you can do data processing all kinds of ways originally like a lot of people think I have to know Scala or need to know Scala Spark that was true like 10 years ago it's no

0:49longer the case I think I said before that it's about 50/50 Python and SQL total aggregate executions on Databricks so most of the world is using Python and SQL and you can see this on any popular programming charts. Python and SQL are like the top two and it's like that throughout the world and so people always ask like well I have data engineering is typically done in Python

1:12modern data engineering you have the data engineering from like 10 years ago where it was Scola Spark or Hadoop map produce type of stuff and I would say the spiritual successor to those data engineers is kind of like pi python or pispark and kind of about five years ago you saw this rise of the pythonic distributed data engineer and spark kind

1:34of uh doing Python on spark became more prevalent I would say over the past 5 years and originally I think I said previously SQL on on spark was just a query language before delta was introduced uh I don't think you could do DML in spark SQL I don't think it was supported 99% sure I could be wrong but

1:56there was no such thing as doing you know insert into or merge into uh in SQL um because you wrote data with a data frame back then. Then when delta came out and all of a sudden we could do DML uh on a data lake with transactions. The whole thing was in data warehousing in SQL languages you you expected some type of isolation. In order to do data

2:19warehousing you have to have acid compliance atomic consistent isolated and durable. So glad I remembered those those words but I'm pretty sure that's what it stands for. There was no way to do that on a data lake before Delta and then iceberg and then you know all the rest. There was no way to do that. You couldn't do it. You couldn't do typical SQL stuff. And then Delta brought the DML to the data lake and then kind of

2:42all the other data lake formats followed suit. So now all of a sudden people introduced the language the syntax in SQL in Spark SQL to do insert into merge into and update. I think those commands came with delta around 2019 2018 2018 2019 and so now you could do DML did spark SQL didn't support uh at the time it didn't support like the long

3:06tale of warehousing features that the industry took 20 years to build from like the 1980s to um the 2000s. Uh and what we've spent at Databricks now is the past five years is building all of that stuff that you would expect out of a warehouse and we talked about the last the last kind of group of features that

3:30we're working on now transactions tables scripting it's all kind of rolling out right now. So now and then you know this year we've been working with lakeflow which used to be called delta pipelines back in the day uh when it originally came out was originally pipelines and then it was declar then it was delta live tables and then it was what did we

3:54change it to delta live tables then it was lake we wanted I think for a brief moment in time wanted to call it just DT and then we rebranded into lakeflow pi lakeflow declarative pipelines and now it's spark declarative pipeline like holy moly that whole paradigm kind of is completely different way I call this the future I

4:17had talked I think Jose and I did a chat earlier this year when I when I said this is the future like declaratively building data pipelines is the future not everybody's doing it right it's super minimal that people are but it's like the newest branch of data engineering and like why like what's the big difference? Uh

4:39it's an unfortunate story. Very long story short, SQL by default is a declarative language. The programmatic extensions that vendors built on top of SQL over the past 2030 years like PL/SQL, TSQL, I don't know what Teroda calls BTech,

5:02something along those lines. They're all proprietary programmatic extensions to the SQL to the declarative SQL language to make SQL imperative. Meaning I have to tell you exactly where my data is, what I'm going to do to it, and where I'm going to put it. And it was like for anyone paying attention, it was like what are you doing? Why are why are we treating a

5:27declarative language imperative? It actually with set processing that SQL is, it creates a ton of issues. Like do you know what cursors are? Birthed this whole thing called cursors where in a set you have to figure out how to iterate it row by row. Anyone who knows anything about how set theory works. No, it's not what you do. Why are we doing

5:49that? That's crazy. What what uh what are we calling it now? Declarative pipelines. What declarative pipelines does is it brings declarative back to SQL and also to Python. You know, Python, we're just going to let that sit there for a second. DLT has Python. If you're a Python data engineer, you should definitely leverage declarative pipelines. It makes a whole lot more

6:11sense to somebody coming from Python for sure because Python is an imperative language by default. You have to tell it what to do. And when you're bringing declarative stuff and making Python simpler, Python people love DT or declarative pipeline. It makes your code testable, it makes sense. It makes it easier. The SQL people on the other hand are like, what are you doing? Because it's you're turning around this concept

6:34on its head twice where we went from declarative to imperative to declarative. We're making the imperative part of the declarative SQL declarative again. And for a lot of folks, this is mental gymnastics that's very hard to reason about. like um so I think that's like the big gap that we're seeing now is you can do ETL on Databricks with straight oldfashioned Python you can

6:56plug it into to jobs you can do SQL on uh the serverless SQL warehouse or you can do on generic compute or you can do it on declarative pipelines and the API just differs a little slightly to drive people insane and then you kind of have like automated tooling now that's coming out you now you have lakeflow designer and kind of like gooey based things and

7:18it's like well which way what am I supposed to do? you have a you have a new customer coming to Databricks and like a lot of our essays are are kind of helping these customers out and they're like well which do I choose and this you know in part I think is a lot of the complexity with Databricks it's the choice you have you really have to choose but why why did we do this why are all these teams kind of internally at data bicks creating products that

7:42kind of overlap they really overlap now like DT and some of the like store procedures like well do I use a store procedure or do I use uh declared pipelines to build my thing. And this is like one of the questions I ask our product teams all the time like what are we doing? The way I advise customers is like listen you can't you're migrating a lot of times it's like why it depends. I'll kind of like give you the

8:04battlefield. A lot of times customers come to us and they're migrating. They're not in the cloud. They're on premises and they need to migrate to the cloud. They need to sunset legacy systems and they need to keep the lights on while they're doing this. The last thing that I want to do to a customer like that is introduce, hey, we're going to retrain your whole team on how to do declarative on top of migrating to the cloud, on top of sunsetting your

8:26systems. We're going to retrain them and oh, by the way, we're going to redo all your data models and we're going to completely refactor all of your code. It's way too much change management all at once. Like, I don't advise it. It's way too much change all at once. If something breaks, you have no way to track down who caused it, where did it happen, and what part of the refactoring did it go wrong. I think you should that most customers should take a phased

8:48approach. So if you're migrating, don't bite off more than you can chew. You should definitely check out what the capacity of your team is and don't change too much at once. A lot of customers the options that you have right in front of you when you're let's say you're you have say we have three customers. One is not in the cloud and they're migrating to the cloud. One is in the cloud but they're on let's say cloudnative services or another

9:11competitor service and they don't like it and they want to come to data bicks. And then let's just say it's a brand new net new green field. We're going to fresh build everything. The customers who's migrating, you have three options. Uh you're going to migrate to the cloud. You can lift and shift and you can pray that your costs aren't going to explode. So you're going to for that type of customer, I would suggest and you're

9:33upgrading your your enterprise data warehouse, you should definitely leverage, you know, store procedures and the way you did things if you want to do a lift and shift. I'm not I want to clarify something. I don't believe in lift and shifts. I'm just saying these are your options. I think lift and shift is the most risky of all of them just because the primary factor is a lot of your code for organizations that are

9:55migrating a lot of that code that was developed was developed by probably consultants over the past 10 20 years and to say it's probably not efficient is you know being very delicate about it. And when you lift and shift all those sins of the past or the tech debt into the cloud, it gets really expensive. But I don't want to belver that point. That's just an option. You can lift and

10:17shift for those customers. Store procedures, SQL scripting, if you're coming from a SQL warehouse, it's the native way to do things on Databricks. That's a great option. Now, if you want to refactor your code, that's when customers start looking at declarative pipelines. A way to really scale it out is by we have a project called DT meta which allows you to if you scrub the metadata from your legacy systems. You

10:40can use the metadata driven framework to push it all into declarative pipelines and it'll build out what we call like bronze to silver for you like automatically. And that's a way to kind of like modernize in the cloud. uh kind of an in between way between like fully remodernizing and lift and shift is what I like to call minimum minimal viable refactoring where you find the costliest

11:05tech debt in the stack that needs to be refactored and you rewrite just those pieces but you keep the core SQL logic in SQL like in the procedures but like a common an example of this in procedures on prem super common practice is kill and fill with your ETL you you truncate the table, you load the new table in and you make it active. Like super common in the cloud. This is ridiculously in

11:28expensive because it's multiple operations and you're paying every time you do the operation. In the cloud, we say just do create a replace table because it just stubs a new version of the delta table and then when we do delta cleanup, it'll clean up those old versions eventually. You don't have to worry about it. Um, and it's much cleaner and much more efficient to do it this way because you're not wasting cloud compute every time you go kill and

11:52fill a table. So like there are lowhanging fruit for minimal viable refactoring that I highly advise and you can mix mode that that's not full declarative pipelines rebuild but it's kind of like an in between in between lift and shift and in between rebuild which a lot of customers appreciate. Keeping the core logic in the original SQL syntax reduces the risk significantly. So that's like when you're a customer migrating from onrem.

12:15Then if you're a customer coming from another cloud product, typically if you did a lift and shift the first time you went to the cloud, now you know why you don't do a lift and shift because of what's happened, the situation you got yourself in, right? And now you're like, well, maybe a lift and shift wasn't such a great idea. And maybe the vendors we chose originally cloud native or kind of like their own data cloud type thing

12:37wasn't really the best decision. And now they're like, well, how how do I fix this? And a lot of times we're not we're not going to lift and shift from that solution, right? Because that's it's still got the sins of the past in it. In those situations, I would kind of advise let's kind of go more towards the minimum viable refactoring inefficiencies that they have in that previous stack that we can maybe pluck some lowhanging fruit for. Maybe we can

13:00uh make it more efficient. Sometimes our compute engine just makes it more efficient underneath the covers with like a couple weird tricks. But if you have capacity, go to declarative pipelines. uh to kind of do that. It a lot of times a lot of the efficiencies are in declarative pipelines and when you do that one-time debt repayment, it it kind of makes things run faster. If you're building brand new in the cloud,

13:22I'd highly advise taking the time to train upskill your team on these declarative frameworks. I I think you don't want to build legacy tech green field like that's just something I would advise if you have the capacity. Plus at this point building net new you can leverage AI to write most of the code and so you just have to tell it which to use. I would just use declared pipelines. So those are kind of like the

13:45the ways and what to use where just I would be super conscious of the risk and debt repayment technical debt repayment uh and how much you can afford to repay to keep moving forward. A lot of times if you take on too much debt repayment in the migration, it'll take too long to get business value and the business will get uh fatigued, migration fatigued and

14:08uh that's not going to help your case at all. Right? So it's kind of like a balance of how much change and debt can we logically repay in the migration process before fatigue sets in and people start complaining about the migration. It's a very delicate game. That's why it depends. There's not one clean clear answer. My favorite approach right now is to when we have legacy

14:31involved and a lot of companies have AI priorities. It's like we have to do AI. I have to do it this year and it's August and we don't have anything. Can you help me? I can't wait a year for a migration. In that situation, I highly advise what's great about Databricks is we give you tons of options. Right? That's I just talked about data ingestion. We have just talked about ETL. I even talk about data ingestion which we have connect and then there's

14:54uh federation so you don't need to move the data we don't need to wait for the migration how about we just federate to the system where where where it exists today the legacy system where it exists today a lot of times these legacy systems can't take AI AI is has uh too much uh uh uh it uses too much capacity on these legacy systems and a lot of

15:16times legacy systems they're being redlined like they're already out of capacity and they just can't take it. So a lot of times what we do is we federate and then we materialize a cloud copy uh for Databricks and then we have AI use that b show the business that you can get value out of AI first. Let's federate to the data where it sits uh kind of prove the value out first. How

15:38about you treat migration as an optimization later after we've proven we did AI this year like we got to do AI in 25. Let's do it. Let's use federation. show the business we can get value out of AI and then when the business leaders come around and say well does it have to be this expensive no let's invest in an optimization and migrate our ingestion

16:00in our transformation over to lakeflow connect and lakeflow pipelines >> and now you have the business asking you to get it done right that that's I think a better position as like a technical leader for you to be in uh you want the business to come to you and be like we need to migrate great because we're getting so much value, but the cost is too high. We need to optimize our ROI.

16:23It's like great, we can do that. We need you to invest in this migration of our ingestion and our transformations into into modern, you know, Databricks. If if you've chosen us for that, I highly advise you do it. We do great with ETL kind of like we set world records. But it I highly advise that methodology using federation first prove out the value with AI and then kind of look at

16:46to optimize with the migration. I think uh the business will be a lot happier and if you have goals to do AI this year it it gets the job done you know and then it entices the business to reinvest in the platform because you don't want to say we'll give you AI but you have to wait a year and a half for our migration to finish who wants to do that >> yeah and federation does not only bring

17:08you like possibility to use your legacy uh data your legacy uh database in database but you also have the possibility to bring governance as Well, for like the past few years, like I'm talking about SQL scripting, store procedures, recursive cities. This is mainly to help you reduce the friction when when doing this migration. I

17:30remember like few years ago saying that data analyst we never do AI or this is something they won't be able to use. And I've noticed in the cup flight last couple months, we brought a lot of features that democratize access to AI to almost everyone from business users

17:52to data analysts. I won't be talking about data scientists and data engineers because they have those technical knowledge to leverage AI. But what what's your what's your what are your >> Yeah. Um I think Databricks is working overtime to bring AI to the to the every person. uh you have AI query and a bunch of uh AI query for like anything. It

18:14kind of lets you plug and play. And then there are a bunch of stock AI functions now as well as we just released in preview AI document parse. And I don't really like the name because it does uh PDF and images. You can read the text off of images which is fantastic. A lot of unstructured data exists in these formats. What I'm finding is all the rage now is these SQL analysts are using

18:39AI query or these other AI functions and AI document parse to find value out of unstructured data or to leverage AI right in their queries right in their analysis and they're they're just using a function and it's super easy and straightforward for them to use and I am also seeing right now a lot of times like companies get a batch of docs in like overnight or hourly um and then

19:02they have to do some type of processing on them. What I'm seeing is that they're leveraging lakeflow jobs with AI query and our serverless SQL warehouse to get super efficient document processing pipelines. Basically, serverless SQL warehouse spins up and spins down. It has all the functions and it can handle the throughput of these AI queries and you can batch process a ton of

19:26documents in a job like really really fast and efficiently. And then you can even do intelligence by wrapping AI query around AI document parse. You can actually build intelligence on top of unstructured data. Um, and I'm seeing this is like an emerging pattern across the industries is leveraging the document parsing with AI query to

19:49basically ask questions of documents live or in a job process unstructured data super fast. like you used to have to load a bunch of libraries and it was very complicated in SQL to do this. Now it's two functions that you have to to put together. So that's what I think we're bringing AI to the every analyst and even in Genie you can teach Genie to use these AI functions or a custom

20:12function. Uh and this is why I say we're going towards the every user can use a IML. It's not you don't even know how to use a function. Genie can just use the function in line and give you a forecast. And even in AIBI, you can do a forecast with a couple clicks. So I think we're bridging the gap between the every user and AI with function and

20:34these capabilities. It's fantastic. >> That's amazing. Thank you so much, Franco, for your for your insights. It's always a pleasure to have you. See you soon, my friend. >> Yousef, thank you for your time. Good luck out there. Thank you.