Skip to main content

Multi-Format, Multi-Table, Multi-Statement Transactions on Unity Catalog

Summary

  • Multi-Statement Transactions: Databricks introduces unified transactions across Delta Lake and Iceberg with Unity Catalog, enabling atomic updates on multiple tables.
  • Data Consistency: Multi-statement transactions (MSTs) solve coordination challenges in data pipelines by ensuring transactions either fully succeed or roll back, eliminating manual workarounds.
  • Enterprise Lakehouse: This video demonstrates how advanced transaction capabilities power reliable, complex ETL and data operations at scale on the Databricks Data and AI platform.

Multi-Format, Multi-Table, Multi-Statement Transactions on Unity Catalog

Watch: Multi-Format, Multi-Table, Multi-Statement Transactions on Unity Catalog
Talk by Michelle Leon, Staff Product Manager, Databricks ; Prakhar Jain, Staff Software Engineer, Databricks
Get a first look at multi-statement transactions in Databricks. In this video, we will dive into their capabilities, exploring how multi-statement transactions enable atomic updates across multiple tables in your data pipelines, ensuring data consistency and integrity for complex operations. We will also share how we are enabling unified transactions across Delta Lake and Iceberg with Unity Catalog — powering our vision for an open and interoperable lakehouse.
Here's more to explore:
Databricks named a leader in the 2024 Gartner® Magic Quadrant™for Cloud DBMS: https://www.databricks.com/resources/analyst-paper/databricks-named-leader-by-gartner
An open, unified approach to your data, BI and AI workloads: https://www.databricks.com/product/databricks-sql
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

What are multi-statement transactions in Databricks?

This video explains multi-statement transactions as a capability allowing atomic updates across multiple tables in single transactions. The feature ensures data consistency and integrity by either committing all changes or rolling back everything if any step fails, eliminating manual reconciliation and foreign key issues.

Why do data pipelines need multi-statement transactions?

This video illustrates problems like manual rollbacks, batching workarounds, and inconsistent data views. Multi-statement transactions solve these by allowing developers to wrap multiple table updates in begin/end blocks, ensuring all operations succeed together or all fail cleanly.

How do open table formats enable analytics to read single copies of data?

This video explains that open formats like Delta Lake and Iceberg combined with transactions in Unity Catalog enable both transactional updates and concurrent reads on single datasets. Using snapshot isolation and optimistic concurrency control, the system ensures analytics queries see consistent data while pipelines execute complex operations atomically. This approach eliminates maintaining separate copies.

What is the best open table format?

This video demonstrates that Delta Lake and Iceberg are both powerful formats with Databricks' transactions supporting both equally in Unity Catalog. The choice depends on specific workload requirements rather than platform limitations, as the Databricks Data and AI platform provides robust guarantees across both. Teams should evaluate workload patterns and ecosystem needs.

Full transcript

0:00 Introduction to MSTs

All right, welcome everyone. I'm a principal engineer here at Data Bricks and I will be presenting about multitable multiformat transactions along with my colleague Carr who is one of the main engineers who worked on this project. And so let's go ahead and get started. All right. So, this is me. I'm actually a remote employee. I've been logging in remotely since pre-COVID. Um, transactions are my thing since forever.
And I also help work on the Delta kernel if people have interests on that side of things. Uh, Picar is also an expert in databases, also an expert in all things performance. And so, if Delta is running slow, he often ends up in the loop. And let's talk about what's up on our uh presentation today. So, first of all, a quick explainer of what multi-statement transactions are, just in case uh people don't have that background yet.
Also, what do multi-statement transactions mean in a lakehouse setting? Then we'll have a awesome demo from Perkar and we'll dive in under the hood on some of the details of what we had to accomplish and then our usual wrap-up. Okay, so first of all, why multi-statement transactions? Uh there's various the short version is coordination, right? Getting things to hang together nicely. But if you've ever updated multiple tables at multiple steps and then had to reason about what happens if you know the second to last step fails or if you've ever had to manually roll back a change after a pipeline went bad or if you've had to use like batching and timestamping and other things to try to figure out what was installed when. audit logs, shadow tables, you know, customers calling and complaining that they got an inconsistent view.

1:19 Use cases and benefits

Uh, needing to do kind of the drop and swap idea of, you know, reload a whole copy of the table into a temp table, modify that, and then drop the original and rename the temp across. All of these things are made a little bit easier when you have transactions. So, let's walk through some examples, right? We have a major ISV construction industry. they are trying to just do inserts across a bunch of tables and they don't want their foreign key constraints to be completely messed up and without the MSTs you know you're trying to do like batching and views and dealing with audits after the fact to clean up inconsistencies with multi-statement transactions you just do begin end do all the tables in between and boom it works another customer right manufacturer they need to update a whole bunch of tables again they have constraints across cost tables they wrote and in particular they don't want dangling foreign keys right you you need these constraints to hold without MSTs they had to do all this logic to like go find orphaned rows and you know reinforce the m the foreign key constraints after something went wrong with transactions you just update the tables and at the end of the transaction everything either works or it doesn't all right so in short right atomic updates roll back capability you have one transaction block that's all or nothing and you have a simpler codebase right you're not having to constantly reason about every possible combination of errors that might arise all right and with that I will hand over to Par who will talk a little bit more about what a transaction looks like in a lakehouse rather than kind of your old school data warehouse thanks uh so now I'm going to talk about uh our approach of building multi-statement transactions on the lake house.

3:40 Lakehouse transaction architecture

So a lakehouse is made of three main components. The first one is the open table formats. Each lakehouse stores its data in open table formats like delta and iceberg. The second important is uh like the cloud object stores. All the data in the lakehouse is stored in uh open object stores like S3, ABFS, GCP. And finally a lakehouse uses an open catalog for its govern governance. Example Unity catalog or IRC. So in order to build multi-statement transactions on the lakehouse we need to make sure that we can do multiple uh like we can update multiple lakehouse tables atomically.
But in order to do that we need ways to publish multiple files in the cloud object stores atomically. For example we want to publish n number of files in S3 atomically. None of the cloud object stores today provide such APIs and that's where the catalog comes in. So we need to teach our cataloges like unity catalog on how to uh update multiple tables atomically. So it needs to act as a central commit coordinator. So it can like publish changes to multiple uh tables at once and thus provide us transactional capability.
This is why building multi-statement transactions on data on like leak house is very difficult. So uh we took the learnings from traditional data warehouses and uh built transactions on the lakehouse with best-in-class semantics. So our lakehouse transactions at datab bricks provides strong isolation level. It provides uh high concurrency and it also provides best-in-class performance using our rowle concurrency and all this is built on top of open table formats.
So let's do a deep dive into each of these. First we'll start with uh consistency. So we provide a snapshot isolation as our consistency model. Under snapshot isolation you see a consistent view of all the tables within a transaction. So it implicitly means you get guarantees like repeatable reads or read your own rights. How many people here are familiar with these terms? Okay. Uh almost half. Let's try to uh understand these terms.
So a repeatable read means that a transaction sees the same data again and again. It sees the same version of the table again and again. For example, here you have a longunning transaction. It's trying to read a table one, it gets the result 1 2 3. Now if someone concurrently writes to this table and inserts more data, this transaction will still see the same 1 2 3 when it do any subsequent reads. This is a very important guarantee and this makes sure that your applications do not suffer from problems like phantom reads.
Next we'll go to read your own rights. So under this guarantee a transaction see its own changes that it has made earlier although they are not committed yet. So for example here we have the same example. The table one has initially 1 2 3. Now you want to write more data into this table under this transaction. We wrote rows 11 12 and 13. Someone else also can write concurrently from some some other user from some other notebook.
But if you try to read this data again from this transaction, you will see your old consistent view plus the additional changes that you have made. So you see 1 2 3 which was your old consistent view and 11 12 13 because this is what you inserted. This is called read your own rights guarantee and it's an important guarantee which avoids non-monotonic reads otherwise your application logic becomes more difficult in order to handle it.
Okay. So this is about consistency. Our lakehouse transaction provides repeatable reads and read your own rights guarantee together with the snapshot isolation. Let's move to concurrency. So we will start with the pessimistic concurrency control first which is a popular approach used in many traditional data warehouses. So under pessimistic concurrency a transaction tries to acquire lock on tables that it is trying to write so that no one else can touch those tables when it's like writing to those.

7:48 Optimistic concurrency control

Let's take an example here. So you have a catalog and you have a transaction one. Your transaction is writing trying to write to table one here. So it contacts the catalog and acquires a lock. Then it tries to write to table two. It again contacts the catalog and grabs a lock. Now suppose another transaction transaction transaction two comes in here and it also wants to touch table one. Since it's writing to table one, it will also ask the catalog for a lock but catalog will reject the request because the lock has already been acquired.
In such a scenario, this transaction two will get blocked and it has to wait for transaction one to release the lock in order to succeed in order to proceed. So transaction two goes into a waiting state. Now transaction one will finish its work and eventually it will commit. As part of commit the lock will be released and now the transaction two can acquire these locks. So it gets the lock now and now it can proceed making the changes.
This is called pessimistic concurrency. And uh although this locking me mechanism ensures correctness but it comes with a big trade-off which is reduced concurrency and this is especially problematic in cloud environment where you have multiple jobs that are trying to like modify the same set of tables at the same time and they are working on different partitions. So they are not going to conflict but still they are acquiring locks and not allowing other transactions or other ETL pipelines to proceed concurrently.
This is why uh we moved away from pessimistic concurrency control and uh we adopted a more scalable and crowd-friendly approach for building transactions in the lakehouse at data bricks. Uh it's optimistic concurrency control and uh we'll talk about it next. So under optimistic concurrency control multiple transactions can operate parallelly without interfering with each other. There is no explicit locking required under optimistic concurrency.
So transactions assumes that there is no one else running in the system and they will make their own changes and eventually they will try to resolve the conflicts during the commit time. So whoever commits first wins. Whoever comes later they have to rebase against the other transaction. Uh this is like a git rebase. So whoever merges to master uh like branch first they win. Then the second uh one has to do a rebase and then merge the changes.
So we use role level concurrency using our native photon engine for detecting these conflicts which make sure that the conflict checking is performant as well as we do it at role level which makes sure that we don't fail the transactions unless there are logical conflicts in them. So optimistic concurrency basically gives us better performance and high concurrency. Let's see an same example with optimistic concurrency.
So uh here we can see we have two tables T1 and T2. They're at version 44 and 64. So the transaction one starts. Note that there are no locks involved. Now transaction 2 is modifying both table one and table two and persisting its changes to the catalog. Transaction two also starts and persists its changes to the catalog. So both can proceed concurrently without waiting on each other. Now when this heavy part is done, transaction one will eventually try to commit the changes.
When it commits, it moves the table from version 44 and 64 to 45 and 65 uh respectively. This is the time when commit has really happened and transaction one has finished. Now the transaction two here is going to try doing a commit. As part of commit, it will try to change the table version in the catalog and it will realize that someone else has already modified the table version. So it's going to do a rebase now on top of the changes that has been made concurrently.
Once it do the rebase, it's going to reattempt doing a commit and once the commit succeeds uh the transaction two is also over. So we can see that how optimistic concurrency allows all the transaction to proceed concurrently and how they can use conflict detection only at the commit time to resolve conflicts. So we get high throughput and high concurrency with this. Okay. So let's wrap up this section by contrasting how multi-statement transactions work on traditional data warehouses versus our approach of building them on the lakehouse at datab bricks.
So uh most of the traditional data warehouses using uses locking which causes slowdowns and deadlocks while we use optimistic concurrency which means like your workloads can proceed in parallel and you can get high throughput. Second most of the traditional data warehouses uses core screen conflict detection like table level or partition level. We use uh role level concurrency at like row level which allows very fewer conflicts.
Next is most of the traditional data warehouses work on top of their own native table formats with lakehouse transactions you could use open table formats like delta work. So you're not logged in basically. Uh most of the traditional data warehouses uses their own catalogs to do uh the transaction management. Uh at databicks we use open catalogs like unity catalog and IRC for building these transactional capabilities.
And finally, most of the traditional data warehouses provide weaker isolation level guarantees like read committed. But on lakehouse, we provide strong guarantees like snapshot isolation, repeatable reads, and read your own rights, which we saw earlier, which makes the application logic very easy. Okay. So we'll start with u isolation level demo. So here you can see we have a table called product and we are trying to read this table within a transaction.

14:00 Isolation and WAP demo

So let's run these commands. Yeah. So, the table has three rows. Now, let's move to a concurrent notebook. Suppose this is user two who trying to insert some data into this table. So, the user inserted two more rows. And now the number of rows in this table is actually five. 3 + 2. Now if user one tries to read this data again, they still see the old three rows. This is called repeatable reads. Now uh let's look at read your own rights.
So this user is trying to insert some data in the transaction. Now the transaction is still active. So nothing is committed so far. They inserted one row and if they read this table again, they should see the old three rows plus the one new row. These are the four rows. Once the changes are committed, these changes will be visible to everyone. And now this user, if it tries to read this table, it will see the rows from the concurrent user as well as the changes that it had made earlier because the changes have been published as part of the commit transaction step.
Yeah. So we just saw isolation level demo. Next we'll talk about a common pattern called write audit publish that could be achieved very easily with multi-statement transactions. So um in this setup we have four tables here. Two golden tables and two CDC tables which are getting the change data feed. Our golden tables are product and pricing. Our CDC tables are product changes and pricing changes. Our goal here is to update our golden tables atomically in a consistent man manner so that they don't diverge from each other and we need to do this daily.
So uh to do this we first start a transaction then we do the right step in the right audit publish. So we run two queries which are going to modify your source table like your golden tables using this uh product changes and pricing changes. So here we have added more data to this product and deleted some rows based on the changes that arrived and same thing we did for pricing. So this finishes our right step in the right audit publish.
Next we are going to do a audit here. So let's try to find are there any products which are missing any pricing info. If we have such products in the system that means there was a issue in this batch of data that came today and so we should roll back a transaction. If there are no issues then we will do a commit for the transaction. So our audit query here returned empty results. That means all the products are in good shape.
Every product has a pricing info. So we do commit the transaction. Okay. So this is how multi-statement transactions could be easily used for uh like the right audit publish use case. Yeah. Okay. Let's move to the uh next section. SQL scripting. This is the most exciting one. So we have also added this capability of multi-state open transactions into our scripting framework. SQL scripting is a new scripting framework based on SQL which allows you to use conditional statements, if else, while loops, for loops, local variables and all.

18:04 SQL scripting capabilities

And you can express very complicated logic very easily with SQL scripts. So consider the same old example here. So we want to update two golden tables but this time we have modified it slightly. What we have done is we also want some automatic recovery when the data is not consistent in the tables. So this is our SQL script. Begin atomic tells that hey everything in this script needs to be atomic. Then we declare some variables on the top here.
After the variables we run our merge queries which are going to update both the golden tables. So let's run this script. After the merge queries, we have the audit step. But note that we are noting the value of the audit into a local variable here. Bad price count. So how many products have bad price count? And based on the value of this variable, we are going to take an action. We are going to decide if total bad products are more than zero.
Let's do an recovery. Let's log all these bad products into a audit log. Then delete these bad products from these golden tables so that they disappear from our website. And so this this ensures that your golden tables remain consistent with each other and there are no issues in them. If there are no bad products, so as part of this else statement, we just log that there are no bad products and we end the SQL script.
Once the SQL script ends, the entire block will execute and all the changes will be published atomically. Okay, so we can see we run this script. It says one product have failed the validation. Let's look at our audit log. The audit log shows the product. Yeah. So we just saw how we can easily use this SQL scripting to do very complex uh like workloads where you can use your own if else statements, local variables, while loops to express very complex logic very easily.
Yeah. Um you could also use the SQL scripting in the streaming jobs. So one of the very popular pattern that customers use all the time is the for each batch. Here you have a streaming source and you are trying to process each batch and try to do something uh on top of that data frame that it generates. So you could take that incoming data frame and you could use it in your SQL script and get some atomic guarantees inside uh the transaction.

21:15 Technical implementation

Yeah. Uh that's it for the demo. Now Ryan is going to explain like how MSTS work under the hood. Right. Back to you. Hello. Yeah, it's working. Okay, there we go. So, thank you, Picar. Um, let's go ahead and talk about what had to go into the data bricks product to make this happen. So, first and foremost, uh, there are multiple layers that have to interact here. And a very important point that's easy to miss is that transactions are primarily an engine and a catalog capability, but they do need support from the table formats in order to be able to do their work.
And so we're going to go through tables, cataloges, engines, and kind of break down what is needed at each level. So first of all, in the table format, in order to support repeatable reads, the table needs to support some kind of versioning or time travel, right? So like a random directory full of parquet files is going to be impossible to do repeatable reads on because every read is just going to return whatever's in that directory.
Something like a delta or an iceberg table where you do have these snapshot concepts can very easily provide these semantics. Uh similarly the table needs to be managed by a catalog so that we're not relying on atomicity of the file system as our source of you know commit coordination because there's no way like Par mentioned at the beginning of the talk you know none of the cloud stores give you a way to atomically put 10 different files and so that's the one we're going to dive into a little bit here.
Um so in delta uh which actually predates a lot of this catalog excitement that we have now uh there was no catalog assumption in the delta format right it's very file system based it relies on put if absent and a right to the table is considered to have succeeded as soon as the cloud store is successfully persists a numbered JSON file right so you just go from 0. json to 1. json JSON and so forth. As soon as the new file appears, it's committed.
It doesn't even matter if the client knows that their commit succeeded. Others can already start reading it through the cloud store. So, very simple. It's very reliable, but it makes it really hard to coordinate multiple table transactions because now you've got a whole bunch of puts to juggle. And there's another problem that we've discovered as we've brought Unity on board in data bricks. It's really hard to keep the catalog in sync with a table that's updated kind of around, you know, through the back door through the file system.
And so you get all of these things, you know, you have to sync and repair the metadata and you have to have hooks to like upload the catalog state so that the catalog doesn't, you know, catalog explorer isn't completely out of sync with the actual table state. And even within uh you know the data bricks and the delta spark ecosystem whatever schema comes back from the catalog we we have to just drop it on the floor and load the real schema from the table otherwise we could be working with stale data throughout query analysis that's really frustrating because you know the catalog gave us this nice schema but instead we have to go to S3 to load the schema and there's also you know as in a government's world there's a very real need to control what kinds of changes can be made.
Right? With a delt with a regular file system commit, you can change anything you want. You can replace the table, you can change it schema, you can change the partitioning. In a governance world, you know, I may have the right to insert new rows to this table, but I don't have the right to delete. Or I may have the right to modify any data I want, but I can't change the schema. Or I can't modify those special table properties that are used by some other system.
And there's no way catalog can enforce any of those requirements if the commit point is a successful put to S3. And so what we've done is developed a new approach to bring Delta into the catalog era where the catalog is in charge of ratifying commits rather than the cloud store. And the commits still end up in the cloud store, right? You can still consume them, but the catalog had first right of refusal on these commits.
And this gives us way better governance story, right? We can now actually block illegal operations according to the catalog's definition of illegal and not have to trust that the client did everything exactly perfect. It also gets rid of the split brain problem. So now the catalog is able to be, you know, confident that every schema change, every table property change is in fact visible in the catalog immediately. And this has landed uh just in the last couple of weeks as a new delta RFC.
So this is a feature that's in preview in Delta 4. 0 and in the coming months we'll be hardening that up and getting it finalized so that it becomes a part of the you know an official part of the Delta spec. Meanwhile though you can play around with it you just have to enable the preview feature on it. Okay, so to give an example of what this looks like, right? In in the traditional delta write, you're going to write out your data files to S3, right?
Bunch of parquet and those files land, they're safely there now, but they are not part of a snapshot. We have to actually tell the table metadata that those files exist. And so, traditionally, right, we would have just done a put if absent that said, yeah, this JSON file references the new a. paret paret file that's now part of the table state and that would have just been like three. json that brings the table from version two to version three.
Now instead we're writing to a uid named file. We're staging it and then we go to the catalog and say hey please associate that file with commit version 3 and the catalog has the right to accept that or to reject it. If it rejects it, well, then you have a useless UI name JSON file in S3 that'll get cleaned up eventually, but no harm done. Okay, so that's the that's the table story. And note that none of that said how the catalog should do what it does or how the engine should achieve the magic it achieves.
It just gives the building block that the engine and the catalog need. So next up, catalog. Right, we need the ability to do transactional table resolution as queries come into the system. They need to resolve within the context of my transaction, not somebody else's transaction. That would be a dirty read. And definitely not, you know, transactions that have already committed by somebody else concurrently. That would be a non-re repeatable read.
We don't like that, right? We want this nice isolation. And so, and then also read your own rights, right? Somebody needs to keep track of the rights that I've made so that when I read them back later, I can see them. And it's not a normal table, right? Because then everybody else would see it. That would be a dirty read. Again, that's bad. And so that's something the catalog needs to be able to provide. And we'll dive in here in a second.
Other capabilities, right? The catalog has to be able to coordinate commits across multiple tables. So now instead of just checking that, you know, commit three against table T is valid, we also need to check all the other tables that came with it. And it's all or nothing. And then and so that covers our data change, right? Our DML. And then the last one that is perhaps the trickiest is the catalog has the ability to participate in the transaction directly.
Right? Do I want to define new ales on that table that I just created or changed? Do I want to, you know, create drop tables in a transaction? That's not a within table operation. That's changing the catalog's name scheme. What if I rename a table? Right? All of those things. Now that the catalog's in the loop, the catalog can choose to allow catalog operations as part of the transaction. In an put to S3 world, that's just flatout impossible, right?
The catalog doesn't live in S3. There's no coordination mechanism. So, let's jump into transactional table resolution. What we want, you know, we have a proposed client transaction here and we're going to se, you know, we're going to read from a couple of tables. We're going to update one of the tables, read from those tables. Again, kind of a write audit publish pattern here and then eventually we commit. What we want to see is that you know when I read my tables they are snapshotted as of the latest version when I started my transaction.

29:18 Engine and glass pane design

So that would be version 21 and version 16 respectively for these two tables. And so to do that we're going to actually put a glass pane in front of the catalog. Basically a proxy catalog. And whenever the transaction tries to resolve a table for read or write it's going to go through this glass pane. So when we finally resolve you know the table W and R they're going to resolve as of a specific version we've captured that in our glass pane and similarly when we update right we will capture those versions in the glass pane they did not go back to the main catalog where others would see them and they're there you know ready to go and later in the oh and meanwhile somebody else snuck in and committed to one of the tables now we're going to do our our publish our audit phase, right?
We're going to go back and read things. Well, I'm going to see the changes that I made to my table because the glass pane intercepted my table resolution and I do not see the changes that other party made to table R because they were not captured in my glass pane, right? It never actually my request didn't make it back to the real catalog. And so by doing this, we're able to put this isolation layer very cleanly, right?
First touch goes all the way through to the catalog. subsequent touches only go to the glass pane. And then when it's time to commit, we take that glass pane and we drain it out to the actual catalog. That's where a conflict checking and everything else happens. And all of those rights get collapsed down into a single atomic change that becomes visible to everybody else, right? We don't want people time traveling through the intermediate state of our transaction.
We want the all or nothing. So by doing this, we get a very clean abstraction for the client. we get a very you know solid story on how we're maintaining this isolation and it just works right this can be plugged in in front of any catalog okay and then the last step is the engine and this is in retrospect it's obvious that the engine would need to do a lot but it became very obvious as we implemented it just how much the engine has to do and the biggest thing turns out the hardest thing is that we just need to know whether we are in a transaction ction or not when a given statement runs.
Uh like in Spark for example, that's just plain impossible. It has no concept of that, right? It has sessions, but you can share sessions between threads, between users, you know, across notebook, like you can do whatever you want with a session. You can even have multiple sessions in the same thread and like juggle them in and out. It's a mess. So just even knowing, you know, definitively am I in a transaction? Yes or no, that's already a surprisingly hard problem. and other things, right?
We need to do that conflict resolution. We need to squash the changes down to an atomic single package that we present to the catalog so that we don't publish a whole chain of commits, right? We we don't want to time travel our intermediate state. So tracking right data bricks runtime just like spark there was no notion of am I in a transaction or not. So we had to figure out how to plum through that tracking so that every statement reliably knows I'm part of my transaction or no I'm something else unrelated.
Uh but it goes further right DBSQL serverless you run three statements you ran on three clusters potentially totally depends on the backing store and in fact those clusters come and go. So the cluster that ran the first statement isn't even there anymore when I try to commit. So now not only do we need to track statements across transactions within a cluster but we have to be able to track that across clusters reliably and that's a little tricky when you have you know a stateless serverless product.
Um so lot of plumbing work there to keep the you know keep the transaction state uh reliable even as clusters come and go. And then the last one which was also a a bit of a surprise but obvious in retrospect right the all the product surface that's interacting with transactions needs to know that it's interacting with transactions. So a simple example notebooks right super convenient feature that if your cluster just disappeared for a while when you try to run a statement the notebook will just launch the cluster and run the statement.
So easy so nice right very convenient. It's a bad thing if that happens when you were in the middle of a transaction and the cluster went down and you run the next statement and it just auto runs on a new cluster in a new transaction context. Right? I just broke atomistity. All those previous changes are gone but no indication that the new change is completely independent and now running just like any other statement.
So we actually had to teach notebooks that hey there was a transaction active. Make the user acknowledge that the transaction died and that the next statement is going to run in, you know, brand new context. So just little bits of polish all scattered through the system that need to be transaction aware and put that all together. Right? So we now have table formats that are able to participate in transactions. We have a catalog that knows how to manage transaction multi- table commits and broker catalog changes at the same time.
And we have an engine that knows whether it's in a transaction or not. All of that together means that we can now offer managed tables participating in transactions across data bricks. So where to from here right first thing is we got a lot more to build still right we don't yet support uh catalog changes in transactions that's coming we don't have all of the advanced like SQL scripting you know uh canned transaction capabilities so things like stored procedures the ability to work with temp tables right all of this still needs to come online and then fancy stuff right save points can you know if things go wrong at step 10.

35:01 Future work and summary

Do I really have to abort the entire transaction or can I just roll back the previous statement and try again with the thing that actually went wrong? Uh nested transactions, similar concept but even more advanced, right? We're exploring what we would want to do there in order to support use cases. So if you know of cases where rollbacks or nested transactions are important, we'd love to talk. And then a call to action, right?
All of this is the data bricks runtime. It's an engine capability. Somebody's got to teach Spark how to do transactions if we want truly open lakehouse transactions. And that's a big lift and we're working on it, but we need to work with the community to come up with, you know, what is a what's best for the lakehouse genre and not just what's best for data bricks, right? Like we need more voices in the room here. And this should potentially allow any open system to participate in these transactions once we've defined the semantics and built the functionality into Spark and other engines.
It's a lot of work. Trust us, we just did it. Um, and so we've already got the Delta RFC, right? Delta tables can participate in transactions. We've got Unity catalog that we're going to be publishing these APIs for multicomit. The Ice Progress catalog already had that, you know, from day one, right? They're a little ahead of the curve on this one. And yet none of the engines out there actually support it. Right? You can't run an iceberg transaction.
The best you can do is write audit publish which is entirely handled by icebergs branching and tagging internally. Have fun coordinating that across multiple tables. Good luck defining the semantics. And if the table had even a single commit while you were doing it, sorry, you're out of luck. They only support fast forwarding, right? because you need an engine that can do like the conflict checking and all this other work that you know the IRC backend can't do on behalf of an engine and so the hard part let's teach all these engines to do transactions right let's get out of the proprietary you know transactions are only for Oracle world and let's also realize that you know table formats and cataloges are actually the easy part of this question right we've got to get these engines on board all right so to summarize Right.
We have multi-statement transactions available in private preview. Uh all the things that provided you can sign up for and have access as well. It works across all our compute types. It works in the various user surfaces and you can read from any data source. Non-transactional data sources just don't have repeatable reads. Right? If you're reading from a Kafka Q, you get what you get. Um and you can write transactionally to all UC managed tables.
All right. All right. And then there's a few other related sessions that might be of interest, right? Uh later today talking about interoperable commute with compute sorry uh with unity and the open APIs and transaction multicomit will play a big role there. And also you know how do we bring open table formats and open transactions together right? What does it look like in a world where there's a bunch of formats and they all want to participate in transactions simultaneously?
Let's make sure that we keep that easy. And then yeah, if you grab this QR code, uh then you can reach out for the opportunity to do multi-table transactions in private preview starting today. And with that, I will end there.

Learn more about the Databricks Data and AI platform.

The information provided herein is for general informational purposes only and may not reflect the most current product capabilities or configurations.