Pushing the Limits of What Your Warehouse Can Do Using Python and Databricks

Summary

• Python User-Defined Functions: Create custom functions in Python to extend SQL warehouse capabilities beyond the 700 built-in functions, enabling complex logic at scale.
• PyPI Dependencies: Bring external Python libraries and custom dependencies into your SQL warehouse, whether as wheels or secure cloud service invocations, without sacrificing performance.
• Governed Reusability: Store UDFs in Unity Catalog for collaborative sharing, managing execution privileges, and reusing complex logic across teams while maintaining governance.

Pushing the Limits of What Your Warehouse Can Do Using Python and Databricks

Talk by Jakob Mund, Staff Product Manager, Databricks
SQL warehouses in Databricks can run more than just SQL. Join this video to learn how to get more out of your SQL warehouses and any tools built on top of it by leveraging Python. After attending this video, you will be familiar with Python user-defined functions and how to bring in custom dependencies from PyPi, as a custom wheel or even securely invoke cloud services with performance at scale.
Here's more to explore:
The best data warehouse is a lakehouse: https://www.databricks.com/product/databricks-sql
How to migrate legacy data warehouses: https://www.databricks.com/resources/ebook/migrate-your-legacy-data-warehouse-databricks
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

FAQs

Why would you use Python UDFs in a SQL warehouse?

SQL alone can be limiting for complex logic. Python UDFs enable you to express sophisticated transformations, call external APIs, and integrate with cloud services—all while maintaining the performance and governance of SQL warehouses.

How do you bring external Python libraries into SQL warehouses?

This video covers multiple approaches: importing from PyPI, uploading custom wheels, and securely invoking cloud services from within Python functions. All methods enable you to leverage existing Python libraries without leaving your warehouse.

How are UDFs governed and shared across teams?

UDFs can be stored in Unity Catalog, where teams can discover them, share them securely, and grant execution privileges to colleagues. This promotes code reuse and maintains governance over who can execute each function.

What compute environments can call Python UDFs?

Python UDFs can be called from all C-enabled compute in Databricks: SQL warehouses, all-purpose clusters, Jobs, and Delta Live Table pipelines. This ensures your complex logic is accessible wherever it's needed.

Full transcript

So let's start um with a quiz. Um what do you think how many built-in functions does data brick SQL have? And I want you to make up your mind about it like generally try to make up your mind about the number that what you think is is the answer here. So built-in is anything like uh a JSON parse function and crypt function things like that we have date parse function things like that. So this is just a part of I looked at the dogs just a part of a collage that I did on uh the function available.
So the answer here is we have nearly 700 built-in functions in data. Um raise your hand if you were close to that. Who thought that it's less? Okay, who thought that it's more? Who wants more? Okay, good news for you. Um, because you can have more. Of course, you can do your own. You can uh create userdefined functions in SQL. All you have to do is uh issue a create function statement and then you can uh mix and m mix and match all the built-in functions together with your userdefined functions and create uh new functions built on top of this and build more and more and more complex logic um using userdefined functions.
Uh and when you do so what is happening is those functions will be stored in unity catalog and you can manage and govern those using unity catalog. So you can grant others the uh uh the privilege to execute those functions. Um but if you don't do they can't do it. Uh but if now this being part of unity catalog uh you can browse for this you will see it in the catalog explorer. You can search for it. It will show up. So it's a great way to collaborate and share functionality using unity catalog.
Um and this being written in SQL is great because it's fully transparent to the optimizer. So you can save time and cost when you execute it. Um and you can call it from all your C enabled compute. So you can call the those UDFs uh from your uh allp purpose cluster from your jobs uh from data life table pipelines uh or from your SQL warehouse. So all of this seems pretty great and I have to admit it is but why are we still here?
Well, there's one question is but what if but what if SQL is not enough? Uh and I'll tell you a couple of reasons why we think secret is great but sometimes not enough. Uh if there are more, please let us know. All right, first one. I'd argue that it can be difficult to express complex logic using SQL alone. Uh and let's start with a very simple example. On the left, you have a table that contains some JSON data that has PII in it.
So there is an email field and there's a phone field. You want to redact that. How would you do that? Well, you can of course do that in SQL alone uh using one of the built-in functions for regular expressions. However, not everyone is super familiar with regular expressions and the uh the group of people who actually love them might be even slower. So, uh you have to be you have to know them and know how to use them which can be complex in itself.
I think everyone who's written complex enough reg knows about that. The other thing is it's also a bit more brittle. Let's say that your email field uh gets substituted by a complex structure or maybe there's some wide space that you have an account for. Maybe there is escape characters. All of this won't work but it's rare. So you have to tweak tweak and tweak and tweak this until it works. Now this is how the thing would look like in sorry this is how the thing would look like in a Python UDF.
So you do the same thing as for a SQL UDF. you create function but you specify this keyword language Python and then you can use Python code uh and execute that from your SQL warehouse. So in this case you import like the JSON library and you do do you tell the code which which keys to look for and then it gets replaced by uh by the redacted uh string. Uh arguably easy to write, easy to test, easy to read, easy to share.
Um and it works as a SQL udf. It works everywhere on all SQL enabled comput. Another example, let's say that you want that you have a couple of input dates in very different formats, but you want to normalize this to one particular way to how to want to store your data. Uh you can do this in SQL, but it gets bit ugly. There are a lot of tririccast uh chaining that you have to do. You can do this in Python now. It's much easier.
Uh you use the datetime parser. You have uh loops. you have conditional and dynamic branching and you have exception handling. All of this helps you to much easier and simpler um write your UDFs uh especially if you want to have some advanced logic. All right, so this is one of the arguments where you might shift from SQL to Python because it's faster and more robust to develop. But are there other reasons as well? Yes, there are.
Um, one of the particular things that's super exciting about Python is that there is a base library based a number of libraries out there that you can use for Python. And this is one of the main motivations why it's so easy and quick to develop in Python. Uh, and we brought this to UDFs as well. So what you can do is for Unity catalog enabled UDF, you can specify custom dependencies. So you don't have to rely on whatever you we provide you, but you can bring your own.
And this is true for both public pi packages. So if you want to build on what the Python community has built, you can use pi um dependencies. But if you have custom code that you don't want to share with others, just within your organization, you can upload that to either cloud storage or unity catalog volume at the wheel and put this here as well. All right. So all I have to do, this is a simple example. It's a spell checker with that function that corrects a word.
Um, and I'm using the PI spell check library for this, uh, which you see highlighted here. And then all I do is I just write the Python code for that one. It will correct, uh, the happening mistake here, uh, and fix it. All right. Um, speaking of Python libraries, there's one new feature that we have as well that's super uh, helpful for that. Uh, that is the use of the batch execute execution mode for those UDFs. So you can improve the performance of your UC Python UDFs by using batch execution.
What this means is that instead of writing a function that does a rowby rowby row update of your um of your data, you can write a function that receives a whole batch of data, processes all this data and expects that you process a batch that you return a batch of the same size back to Spark. So this is a great way to improve the performance um especially if you have big like a a heavy initialization call in your library that doesn't depend on the actual data but you're reusing that.
So in this example again for the spell checker uh what is happening is I am now uh declaring my function to be of parameter style pandas which means that it will receive a pendas series as an input. uh and I'm also specifying a handler function now which is called correct word words which is a function that will return a panda series as well and this is the function that you see below um and the first step here is I am initializing I'm in instantiating the spellch checker library once and then for all the words that I'm receiving my batch I am just applying and calling that library to correct word after word after word so when this everything that is above this uh for loop gets executed once per batch only and only what is in the batch loop uh in the for loop will be executed for all your data pro.
Uh so this is a great way to do it if you have heavy initializations. It's also a great way to do uh if you're calling external services and this is the last thing that I want to mention here in terms of where SQL might not be enough. If you're calling arbitrary cloud services the UCUDFs are a great way to do so. So what do I mean by this? Let's say you have a SQL query where you call a a batch using Python UDF and that UDF should invoke a cloud service.
Uh let's say you're in AWS, you want to call like an external lambda function or you want to um access AWS secrets manager. So you can use the Python UC uh the batch UC Python UDF that I just uh told you about plus a feature that we call UC service credentials that you can attach to UDF. And if you're not familiar with the UC service credential, that is essentially a UC object or UC version of a cloud identity like think of it like an IM role on AWS or a managed identity on Azure.
Uh and what that UDF will use then if you attach it the service credential to it, it will use that service credential or that role to authenticate with the cloud service. All right, let's uh give this an example. uh let's say you have a lambda function that is doing hashing some values for you and you want to integrate that with data bricks. So what you do is you bring in um a credential it's called service credential and I make it a default.
Making a default just makes it easier to reference and use in the code. Uh and then in the code I'm using BH3 which is the official AWS SDK for accessing cloud services on Azure. This would be the Azure SDK. um and I'm starting a port three session to lambda and because I declared my credential to be the default service credential it will use that credential that IM ro to authenticate this UDF um to the lambda function and now um what I'm doing afterwards in a for loop I am constructing a request to that lambda function uh constru more specifically constructing a payload for that udf uh once for all the rows for a batch of send that thing over to lambda um so that I'm not invoking a lambda by row by row but also using a batch approach.
So this way you can uh achieve secure authentication because it will use shortlived credentials as you do with IM with IM roles um or manual entities. uh you can have very fine grade access control because all the um UC governance rules still apply and you have a high throughput because what you're doing is you're sending over batches to data to lambda and this is happening in parallel because the UDF is executed in spark or data pro SQL warehouse in parallel so you get parallel calls with each batch of data so you can still achieve high throughput for external cloud functions all right um so yeah there's a couple of reasons that I think or we think um is a good way of thinking where you could extend um your warehouse um using Python.
Now there might be things that uh we still haven't covered here and we want to learn about those. So please tell us if you want other languages for UDFs uh other than SQL and Python. Are you interested in Scar, Java, Go, you name it. If you have some good use cases for other languages, please let us know. Uh and it's super interesting for us. Um, do you need do you have use cases where you need to access workspace files or UC files from within the UCUDF?
Do you have a library that depends on some configuration file, some external file uh uh on a file system, please let us know as well. Um, if you already developed a couple of Python UDFs or started on the journey and you had to debug one of those or you wanted to profile the performance of it, please let us know about how your experience was. Uh, and in general, if there are things where you want to extend your SQL warehouse or just code that you want to run in your SQL warehouse that's not there today, please reach out to us.
We're super interested about those use cases. All right. Uh, now what we what we talked so far is why all of this is useful. Um, but let's touch a bit on what the what Unity catalog brings to the table and what are good practices uh around using pipes. Uh first what do we get from having a Python UDF in Unity catalog? Well first of all you can reuse and share that across teams. So you only have to register it once in unity catalog.
This UD this UDF becomes permanent in UD catalog and then you can if you want to but you don't have to you can grant others the permission to access it. So you essentially grant uh execute on that procedure to another user uh and if another user now then wants to call that unit catalog will check if that user actually has a permission and only then execute it. So you get this reuse for yourself and also the sharing characteristic with others.
Uh but not that's not um because the UDF lives under the three-level namespace you get a couple of things for free. uh if your if your catalog is bound to a specific workspace that will apply to your UDF naturally. So if you have a catalog there live a couple of UDFs live in this catalog you bound that to say only your development workspace then all UDF will also be bound to that workspace. So you get all this those things for free and if you don't want your users to create UDF you just don't give them the create function privilege and then they can't be.
So you can essentially control who can and who cannot create videos, right? Um and now once we have those in Unity catalog um they become discoverable for you and your team. So they will show up in a catalog explorer. Your tables will show up, your volumes will show up and so will your functions and when you create them you can see them in a catalog. You can just drag and drop them in. You can global search for them.
So the search bar on top of data bricks if you type in a name function or any other metadata you can find your UDFs. So you have cross team reuse and sharing you have full governance and you can discover this at scale. What else? Uh let's talk about compute and where you can leverage those UDS. First you can and I think this is maybe the most obvious part you can use those in SQL queries. So you can now this is an example where you select from table where redact uh blah blah the redact function is udf so you can use it here in udf just as any other building function there's no distinction um but it's not only a SQL thing you can use the same UDF across all UC enabled compute you can use it from the dataf frame API as well so in pispark you could essentially refer to by the same name here uh captured in an expression uh function uh and call that function as well.
So it's not limited to SQL warehouse only but you can of course use it in data frame API and Python as well. And now the thing that is used like asked for a lot is by uh UDF being a part of Unity catalog we can now refer from other unity catalog objects to that UDF. Um that is true you can have a a Python UDF let's say in a UCSQL udf but maybe most importantly you can use it in views. So you can create a view that within a view definition references a UDF.
So let's say that you want to the redaction logic that we uh talked about in the beginning. Maybe you want to create a view that automatically redacts all the information. You essentially create a view and you pre uh you have the redact in the select part of the view to apply it to like a color masking function. All right. Um so all of this in itself is super powerful. Um, let's talk about a couple of best practices how to use them.
Well, first and I think this was the point that I wanted to make in the beginning is if there is a built-in function or SQL UDF, our recommendation is to go with the SQL version. Why is that? Because the Python UDF is a black box to the optimizer whereas a SQL UDF is just SQL and SQL is something the optimize understands. So expect the best performance and the most efficient execution using SQL. If there are other reasons we talked about this if that's not the case uh Python DFR are a good choice.
Um they are in Unity catalog you want to share them maybe you want to reuse them um treat it like an asset this way. So this means give it a self expert not a name. Uh take some time to maybe add a description text to it. This is great for discoverability. If you describe what your function does, someone searches for what what the person wants to do. If there is a UDF for it, great. Maybe have a convention of if you're providing a couple of UDFs to a whole team, maybe add a text so other can easily discover this as an asset for the team.
Make it discoverable and think about the reuse of those UDFs. Um if UDF requires an extensive setup step like a library in initialization or calls external services consider to use the batch execution mode. So this is really good if you have something very very heavy and you want to amortize the cost for this heavy operation over um a whole batch of data. This really makes a difference. you can get like if you get a batch of data of thousand rows, you can essentially thousandx the performance of your stuff.
Um, and this is more often uh possible. All right. Um, if you have long lived UDFs or widely shared UDF, meaning you have a UDF super important for your organization, you want to keep that for a long time and you want to share that with the whole team or maybe even a couple of teams. Um the thing here that you want to avoid is that someone leaves the company or that user account that created is not long with the company on a different team or does not have the permission to access any of the resources that the UDF uses.
It's best to give that UDF ownership to a service principle account and think of it like the one pro the providing account for UDFs that you want to do. So this way you can uh you can ensure that over time UVF will be kept it will be still usable no matter what happens to the original user counter. Um yeah I think this is this concludes the talk. Um overall I hope you learned a lot about SQL udfs but also about maybe some use cases of what you can do uh uh with beyond SQL and Python UDFs.
Uh and again if there's anything that's super interesting for you that we can't do today please let us know. I will piece for Thank you.

Learn more about the Databricks Data and AI platform.