Multi-Statement Transactions: How to Improve Data Consistency and Performance

Summary

ACID compliance for lakehouse: Multi-statement transactions bring atomicity, consistency, isolation, and durability (ACID) guarantees from traditional databases to modern lakehouse architectures on Databricks.
Real-world use cases: Enable complex patterns like multi-table updates, deduplication pipelines, and audit logging that require all-or-nothing execution semantics to maintain data consistency.
Performance and reliability: Databricks optimizes concurrency and performance through techniques like optimistic locking while ensuring transactional guarantees, enabling faster innovation without sacrificing data integrity.

Multi-Statement Transactions: How to Improve Data Consistency and Performance

Talk by Franco Patano, Principal Solutions Architect, Databricks
Multi-statement transactions bring the atomicity and reliability of traditional databases to modern data warehousing on the lakehouse. In this video, we’ll explore real-world patterns enabled by multi-statement transactions — including multi-table updates, deduplication pipelines and audit logging — and show how Databricks ensures atomicity and consistency across complex workflows. We’ll also dive into demos and share tips to getting started and migrations with this feature in Databricks SQL.
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

Chapters

FAQs

What are multi-statement transactions?

Multi-statement transactions allow multiple SQL statements to execute as an atomic unit. Either all statements succeed and are committed together, or all are rolled back—ensuring the database moves from one consistent state to another.

What are real-world use cases?

Common use cases include multi-table updates where changes must stay synchronized, deduplication pipelines that remove duplicates across tables, and audit logging where original data and audit records must be captured atomically.

What is delta lake?

Delta Lake is an open-source storage framework built on data lakes that adds ACID transactions, making it possible to move from one consistent database state to another reliably. This video shows Delta Lake uses optimistic concurrency control (retry if commit fails) instead of pessimistic locking, enabling higher throughput while guaranteeing snapshot isolation for readers—solving traditional data warehouse problems like dirty reads and deadlocks.

What are ACID transactions on a data lake?

This video explains that multi-statement transactions on Databricks' lakehouse provide ACID guarantees: atomicity (all statements succeed or all roll back), consistency (moving between valid database states), isolation (readers see consistent snapshots), and durability (changes persist). Delta Lake's transaction log records every change, enabling reliable data warehousing patterns on cloud object storage.

Is Delta Lake proprietary or locked to Databricks?

Delta Lake is open-source and not locked to Databricks, enabling multiple platforms to build on the format. This video shows how multi-statement transactions on Databricks SQL leverage Delta's open transaction protocol, while the format itself is maintained by the Linux Foundation, ensuring community-driven development and broader ecosystem adoption.

Full transcript

`0:00` Introduction and logistics

There's a couple things I have to say before we get started. I don't know if everybody's got the things on. I have to present these uh these statements from our legal department to let you know these are all forward-looking statements. I will guarantee that everything that I'm talking about today, we are going to deliver because our customers have been demanding it. But still, they make us include this legal ease. So, please try to read it.
Complete your surveys after this event. We all know how we did by your surveys. If I can do better, I would love to hear it. Let us know how we can do better. So when you come back next year, uh it's even better experience than it was this year. And this is how you do it. Who am I?. I am a principal solutions architect at Data Bricks. Uh, I grew I live in the Chicagoland area. Anyone from Chicago, you know, gets upset when I say I'm from Chicago.

`0:50` Speaker background

I'm really from Bowling Brook, but nobody knows what Bowling Brook is. So, I'm from the suburbs. Uh, you could talk to me about data warehousing with data brick SQL. I spent over uh 15 years in industry building global data warehouse and BI solutions across various industries, banking, education, and commercial real estate. And then when I figured out what data bricks was doing with Delta Lake in 2018, I said, "Well, this is the future."
And I joined the company. Uh, you can also talk to me about ETL benchmarking. I spend an inordinate amount of time at Data Bricks making sure that what our engineers are doing actually benefits our customers and that means doing a lot of benchmarking with my friends at Data Bricks. I also like time travel, fiction, non-fiction, and the stuff that's in Delta Lake. Uh, so you can talk to me about any of those things if you'd like.

`1:58` Understanding transactions

So I'm going to be talking to you today about multi-statement transactions or multi-table transactions. Uh, so to start us all off like what's a transaction in database or data warehouse parliament, right? Uh, when we say transactions, we usually hear another term acid compliance. What's acid? This isn't the stuff that they took in the 60s still today to trip out. It's actually something a data warehousing parliament called uh atomic, consistent, isolated, and durable.
This means that when you make a change in a database, it all happens or it doesn't and it sticks. That's basically the short of it. Essentially what it allows us to do is go from one consistent state to another consistent state with a bunch of different operations in the middle. Typically in databases and data warehouses uh this is how they write data. We call it DML or data manipulation language. So if you were doing insert, updates, deletes, merges, uh upserts, this is how you would do it typically is you would do it transactionally on a data warehouse.

`3:14` Role of multi-statement transactions

So what about multi-statement or multi-table transactions? Well, first before we dive in, a little bit of uh of kind of like the state of the of the industry or where Delta Lake is. Delta since its beginning has always had single table transactions. Actually, the way that Delta implemented it, every DML is a transaction and you don't have to declare begin, end, and create transactional like state. going to explain a little bit more about it as we go on, but essentially this solved a lot of the common data warehousing issues that users had to contend with.
Primarily dirty reads. I was an analyst for many years before I started my work in it fixing all the stuff I thought they broke when I was an analyst. But dirty reads were the bane of my existence. I never understood like how they could leave the database in an inconsistent state. uh but that was a problem. Deadlocks and the throughput issues that would happen with complex dependent uh warehousike workflows. It was never a great experience trying to recover from failures uh trying to handle all these things.
So Delta actually solved a lot of this complexity. single trainable transactions. Actually, when I was doing research, the bottom there was written by AI. You can tell because they use em dashes. I don't know, but most of us don't use those things, but AI does. And the reason is because it's like a pause in a sentence. But essentially, single table transactions according to all the research that I found consists of 70 to 80% of the markets ETL.
So a majority of transactions are single table. Delta already supported this. It wasn't really a need for us to create it because we found that a lot of people were misusing transactions. I actually have a customer now that is testing out multi-statement transactions. And through digging into the code, we figured out you these don't need to be multi-statement transactions. Just the consultants that created this 20 years ago decided to do it this way.
So a vast majority of the market doesn't actually need multi-statement or multi-table transactions but they do exist while less frequently but less frequent and super specialized these use cases do exist in the wild and this is kind of the lay of the land. So, Delta always had super simple transactions. We just didn't have multi-table or multi-statement. But why? Who cares? What's the what's the difference? Like what what is different with Delta?

`5:56` Delta Lake versus traditional SQL

Well, Delta implemented uh a much different protocol than these traditional data warehouses or database. It did a lot. Typically when you think of transactions in a database or a data warehouse, you think uh I have to lock the table because it's pessimistic control. It you have to lock the table, do all your things and then unlock the table and then nobody can you nobody can update that table while it's locked. Delta on the other hand does optimistic concurrency control which means it's going to try to commit the transaction and if it fails it'll retry.
It's optimistic instead of pessimistic. It's also in order to guarantee in order to fully comply with acid compliance. It also has snapshot isolation on the reader. That means readers always read consistent state. There's never a dirty read on delta, which solved two of the biggest problems in data warehousing. Locking contention and dirty reads. So, we solved all that. uh and also boosted throughput since we're not locking and waiting because if you think about how much of your warehouse is waiting because it's locked and it has to wait until the lock gets released your throughput is severely decreased.
Our customers found that they could get faster and cheaper and much better mechanics by leveraging this and you know most of transactions are single table so they didn't really need multi-statement transactions. Those are the big differences between what you would call uh traditional SQL databases and Delta. The one thing that users love about Delta is before you never had to do begin, end, and commit transaction just by doing insert, update, delete or merge it, Delta automatically created as a transaction.
So it actually made engineers lives simpler. Uh they didn't have to reason about all of these locking mechanisms and transactions. So that's kind of you know why we built what we built and how we got here. Why now? Well, our early adopters saw the benefits of ha of all of the things we were building in Delta optimistic concurrency control snapshot isolation and for the one or two times they needed multi-statement transactions they created great workarounds.

`8:17` Migration and cloud-native design

They're like ah you know what we did doesn't really matter. uh there are like five different types of workarounds that our customers created uh that they could get by without it. So they were like eh you don't really need to do it. But a lot of times when we're doing migrations from these old school data warehouses you have to contort your workflow a little bit in order to fit it into uh to data bricks land. Uh so now instead of like retrofitting or rebuilding to fit data bricks, data bricks meets your migrations where you are and it enables what the market lovingly calls lift and shift.
I hate lift and shift. If you read up on what I I write about in blogs, uh I have a blog if you want to check it out after this. I write a lot of the content for our data bricks SQL SME. Uh and I lead the SQL SME internally in data bricks. I hate lift and shifts and I really don't like store procedures. I actually wrote a whole blog about why store procedures are the devil in the modern data cloud on the modern cloud data era.
The primary reason is that store procedures were created because you took the logic from the ETL tool and you brought it to where the data and compute were because data and uh compute and data were tethered on the same machines on prem on these data warehouses. it made sense to take the logic from Informatica and bring it over to the SQL warehouse in a procedure and execute it because that's where the data was. You didn't have to move the data around.
But in the cloud, we separated storage and compute. So now there really is no reason to encapsulate in store procedures. That's a legacy thing. But that doesn't mean we're not going to build it. Our customers keep asking for them. We're going to build it. But I just want to come out and say I'm totally against store procedures. uh not from any mechanical point of view but from a design point of view. I don't think people design them properly and I think there's 20 years of bad practices in the industry that need to be addressed.
But if you want to know more about it, you can check out some of the stuff that I write. So what are some common use cases of multi-statement transactions or multi-table transactions? The most classic one is I have multiple statements I need to execute. And the number one thing that I see here is what we lovingly call in industry kill and fill. I need to delete the data that I'm going to be ingesting or I need to truncate my table and then I'm going to insert my new data into that table.

`10:35` Use cases for multi-statement transactions

And in order for the transaction to commit, both of these things should happen. Why? Because if I truncate or delete the data, I can't leave it like that, right? I have to restore it back to where it was before or my users are not going to get their data. Angry users, angry times for me. I don't want that to happen. So here's that first one. I will tell you that if you have this use case, pointer just not working. But if you have this one, you should seriously reconsider redesigning that because that you shouldn't be doing delete and truncate and then insert on data bricks or on the cloud in general.
The reason why is because the object store is immutable. These transactions are very very costly at scale and you don't need to do them. There's actually better ways to go about doing it. I would love to talk with you more about that. Number one, if you have that use case, I think you should consider refactoring your code. Most companies will find, most users find it's faster and cheaper a cloudnative way versus doing it that way.
But again, customers want to do what they want to do, we're enabling any of these different practices. You can go ahead and build them. What is great here is if a customer just wants to lift and shift and they don't want to refactor any of their code and they don't care if they're going to be spending lots of money and not getting a whole lot of value. But the second use case which I think is is valuable that is required that this is the one thing that I think if you have this use case multi-statement or multi-table transactions are for you is when you have multiple tables most lovingly in dimensional modeling you have to load the fact table then the dim table consistently or else you're going to get missed joins in when a a user queries that data and the last thing you want is in production for a join to fail because you're loading a fact table and you didn't load the dimension table yet.
So you have to load they both have to be consistent. This is the true use case uh for multi-table transactions. This is the most common one. You can't have multi-statement that's not a delete and insert. Sometimes you have to uh do a bunch of operations serially to a table like you have to update data, insert data, update data, insert data. uh I see this commonly in like finance where there's very very complex iterative logic that happens and you can't do setbased logic right so that's why that's the only use case I consider for one uh if you have some type of complex iterative logic that you can't use SQL for because SQL is a setbased language u other otherwise I see people using Python for that one because Python you could do setbas so let's talk a little bit about the transactions on the lakehouse.

`13:47` Optimistic concurrency and performance

Just to summarize a few of the concepts I've talked to you about, data bricks uses optimistic concurrency control. Really, it's Delta Delta Lake uses optimistic concurrency control. Multiple transactions can happen simultaneously and they don't even have to know about each other. In data warehousing, concurrent writers was a very very hard thing to accomplish traditionally. and data bricks with delta these things just happen and they don't really need to know about each other.
The one thing we there um talk about that one later there's no explicit locking. There are no more deadlocks. If a transaction fails because a row it was updating needs to be updated again, it's actually just going to retry automatically. So no more deadlocks, no more contention, a lot faster system. any of these conflicts that happen, we do it at the row level. And nobody else in the cloud does this. And that's what I'm going to explain now.
Sorry, this thing isn't working right. All right, so we talked about concurrency control. There are two major ways that cloud vendors have implemented concurrency control. Optimistic and pessimistic. Pessimistic is locking. Optimistic is no locking. Right? So the only vendors out there that are doing optimistic concurrency control is us and BigQuery. And I think that's a a huge difference. I think uh uh BigQuery has done a couple things right.
The section level this this the next level is the isolation level. Uh, and this one's super important. Snapshot isolation is the most common way to do this in the cloud today. The modern way, uh, one of my favorite competitors likes to do it recommitted. I don't think we don't think that's the way to do it. Uh, snapshot isolation is the way of the future, especially with how we develop on object stores with delta and iceberg in these modern formats.
So, uh, higher of these levels, your database is more consistent, right? because you're always reading a snapshot that's consistent. You're not reading uh you're not having to like read only parts of the data that are committed. And then finally, this is where data brick sets itself apart from our competitors. Nobody is doing rowbased concurrency control. So what this means is that you can write concurrently at scale to a delta table with multi-table or multi-statement transactions and as long as your operations aren't touching the same row, everything just magically happens.
If for some odd reason two transactions are hitting the same row at the same time, it automatically resolves itself on a retry. So, one transaction will fail and then it'll just automatically retry. If for some odd reason it can't resolve, you're updating the same column twice in the same atomic moment in time, you're going to get uh an error error like it's going to error out because you can't do that. It's illegal.
Two transactions tried updating the d same data at the same time, the same row, the same column, right? So there is no other system out there today that gives you optimistic concurrency control with snapshot isolation with granularity down to the row level. Data bricks is the only vendor in the space in the cloud that is doing this today. So that's how we set oursel apart from our competitors with multi-statement transactions.
As a customer, what does this mean to you? Like who cares? Why would I care about how granular data bricks has done this? Right? It matters because we can get significantly more throughput with this specific recipe and the most common types of transactions that we see for ETL out there. So much so that we have customers that have told us it's like night and day the difference between some of your competitors your throughput the total amount of transaction that I can throughput.

`17:30` Benchmarking throughput

Uh a lot of our other vendors who's you used some of the less specific techniques, they have uh a queue. You can only do so many updates to a table. It can only queue up so many before it just starts failing transactions because it has to work through a queue of updates. And you can see clearly here that when you're updating a significant amount of data with a lot of users, data brick SQL outshines our competitors in this space.
So five times faster with stronger isolation. That's the value we're bringing to multi-statement transactions. And the reason why it took us a little bit longer to implement this is that we had to kind of build the dependent bricks beforehand like that rowle concurrency had to be put in before we could really outshine less the competitors in this space. So that's kind of why it took a little bit longer, but we wanted to deliver much much better results to our customers.

`19:04` Conclusion and closing remarks

So this is what that looks like. This is uh this is what new transactions on data bricks looks like. And it's in preview right now. I have customers testing this with stored procedures, multi-statement transactions, and SQL scripting. We're migrating data warehouses, lift, and shift straight into the lakehouse. And this is kind of what those looked like. If you've seen the SQL PSM standard for uh transactions, this basically follows that standard.
So we're we're following ANI compliant SQL. Your SQL code that you develop on data bricks is transportable anywhere that you want to go. So we're not locking you in with your data. Delta is an open- source format and we're not locking you in with the SQL code. So when you migrate to data bricks you don't have to worry if another vendor comes along all of this code that you built abides by the SQL standard for P and PSM.
So all of this code is generally consumable. There's no vendor lock in with additional value that we're building in to data bricks. All right that's the end of my talk today. I wanted to tell you all about multi-statement transactions, multi-table transactions and data bricks. Something that has been a long time coming. But I want to let you all know do not think that this is the hammer that will hit every nail. A lot of times I will tell you lift and shift migrations doing these patterns from onrem into the cloud it leads to a lot of heartache.
So really consider what you're doing. minimal viable refactoring for the cloud is what I believe in. I think you have to figure out what are the most cloudnative ways that you can adapt your code uh in order to get better efficiency in the cloud, but that doesn't mean we're not going to give you the tools to do it. You should consider how you're using the tools. Um I will be available for questions if you want to ask uh right outside here.
Thanks everybody. Have a great day.

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.