Atomic Transactions in Databricks: BEGIN ATOMIC & Multi-Table Commits

0:04Hey folks, today I have the pleasure to host for the third time Fabian Contaminas, specialist solution architect, who works at Databricks. Hi Fabian. Hi Youssef. Thanks for having me today with you. Always a pleasure to be there. And I think I no longer need to present him, and he will record a series of video with me. Today we are going to talk about two data warehousing

0:28concepts. So it's multi-statement transaction and multi-table transaction. So both uh features are almost the same. With multi-statement multi-statement transaction, what we want to do is running in one command multiple operation on any table and fail the whole block in case there is one hiccups. And I will I will show you that

0:50right after right after with some code. Multi-table transaction is for more complex logic behavior. So it's more familiar with what what uh you can could have been running within SQL Server with a begin transaction statement, which is a statement by itself. Then you can run any command you want, and you can at the end decide to either commit or either

1:13rollback the whole transaction. So actually, let me let me show you all these demo. And I think that's the reason since we need to manage how things are written, that's why it needs to go through the catalog based commit. Absolutely. Absolutely. This this is this is very important. So as you see on my screen, we have I will be creating two tables for you, and we have these new properties, which

1:37is named Delta feature catalog manage supported, that gives an information additional information on the table that the commit is actually managed by Unity Catalog. And this this is this is very important because if you if you can have multiple committers in your table, then we wouldn't be able to guarantee

2:00the veracity of the data with multi-statement transaction. So here, let me I'm I've just created two tables, MST table one, MST table two. Let me fill two values in both these tables. So it's easy. I I've for both the tables I've got an ID, which is identity, so it will be one because I I have only one values for both my table, just a timestamp and a value. Okay, so we

2:24have our two rows in this table. Let me just display what we have inserted. So exactly what we have here. So for table one, we have the value one, table two, we have the value two, and both have ID one. So let's say, you may have not noticed, I put a not null constraints on all my columns in my tables. And what I want to do here, I

2:46want to update both my table. For the table one, I want to set the value to the val column to 1.1, and for table two, I want to set the val to null, which is not permitted. So let me run this block of command. And I've got a not null constraint violated for the column, which was expected. But if I go back and look in both my tables, we'll

3:09see that actually, so table two didn't get updated, but table one did. So this statement succeed and this statement failed. And in a lot of scenarios, especially in data warehousing, you want to have you for data validation, for data quality, you want to have like tight logic. So you want to do first some operation on a table, another set of operation on the

3:32same table or another table, and so on and so on, but you want all those operation to be consistent. So this is this is where the multi-statement transaction is actionable. So we have this new keyword that you type just to the to the begin here. So when you do a begin atomic, and I will run again the same command. I've just put 1.2 here for

3:54the table one. So I'm still expecting the I'm still expecting the error. So here here it is. But when I look into the data, this time we have no change, not for table one, not for table two. Both statement are either committed or rolled back. So this is this is very good. But let's say you need to have more complex logic. I don't know, you

4:17want to add some some data in your table, and you need to you need to check that the sum of something is below certain threshold. So it's like a complex logic validation. So for this one, this is where we have the multi-table transaction features. So you In this one, it's separated command. Maybe I didn't emphasize enough, but

4:40here it's only one command. It's one block of command. This is leveraging SQL scripting. Here, this is natural command. So begin transaction is a is a new command in the in DB SQL. So I'm here I'm declaring and opening a new transaction. I can, for example, update my table one. So here I've put the number 1.3. I will update my table two with the number 2.3. This time I want

5:04this command to succeed. So I can check the value in both my tables. Okay, so 1.3, 2.3. This I'm happy. This is what I wanted. But let's say I'm implementing more logic and I'm not happy with the result, I can just rollback the transaction. And now if I check again my data in those two tables, I'm back to where I was before running this this two update operation. And yeah, this is it.

5:28This is this is a feature. Uh very easy to use, very very natural. And if you are coming from data warehousing or or more classical database background, it will it will feel super easy for you to adjust. And that's it for me, Youssef. I think that's pretty much so I was as Fabian mentioned, we have two new concepts. I mean, not new, but new to in to

5:52Databricks, multi-statement transactions, where you can make sure that either you fully commit everything or nothing. And then if you have a more complex logic, um for validation, then you need to go through multi table transactions. Perfect. And both of them rely on the catalog based commit, which is the feature that

6:15that Fabian enabled on at the table level. Perfect. Thank you so much, Fabian, and see you soon. Thanks, Youssef. Talk to you soon. In Lake Five data seas, Lake flow streams the flow. [music] Iceberg and Delta Lake, tables are glow. Unity Catalog governs Lake base OLTP strong. [music] Genie takes to SQL MOSAIC AND KO PROLONGS. Youssef and

6:38Quinn and co-host light the fire. Data and dreams scale higher and higher [music] next gen. Drop it.