Selectively Overwrite Data With Delta Lake’s Dynamic Insert Overwrite

Summary

• Dynamic Insert Overwrite: Delta Lake's fine-grained update feature eliminates the need for full-table rewrites by selectively overwriting specific rows.
• ETL optimization: This capability is essential for efficient data transformations, DBT incremental models, and Slowly Changing Dimension Type 2 implementations.
• Performance and cost: Using Dynamic Insert Overwrite on Serverless SQL Warehouses reduces computational overhead and improves query performance in production workloads.

Selectively Overwrite Data With Delta Lake’s Dynamic Insert Overwrite

Talk by Bart Samwel, Principal Software Engineer, Databricks ; Thang Long Vu, Software Engineer, Databricks
Dynamic Insert Overwrite is an important Delta Lake feature that allows fine-grained updates by selectively overwriting specific rows, eliminating the need for full-table rewrites. For examples, this capability is essential for: DBT-Databricks' incremental models/workloads, enabling efficient data transformations by processing only new or updated records ETL Slowly Changing Dimension (SCD) Type 2 In this lightning talk, we will: Introduce Dynamic Insert Overwrite: Understand its functionality and how it works Explore key use cases: Learn how it optimizes performance and reduces costs Share best practices: Discover practical tips for leveraging this feature on Databricks, including on the cutting-edge Serverless SQL Warehouses
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

• 0:00 Introduction and speakers
• 1:18 ETL overwrite challenges
• 3:20 Requirement criteria
• 4:21 Evaluating current approaches
• 7:11 Introducing replace using and on
• 9:15 Why merge falls short
• 12:52 Compatibility and features
• 14:09 Availability and summary

FAQs

What is Dynamic Insert Overwrite in Delta Lake?

Dynamic Insert Overwrite is a Delta Lake feature that allows fine-grained updates by selectively overwriting specific rows instead of rewriting entire tables. This eliminates the performance and cost overhead of full-table rewrites while maintaining data accuracy and consistency.

When should you use Dynamic Insert Overwrite?

This feature is ideal for DBT incremental models that only process new or updated records, Slowly Changing Dimension Type 2 implementations where historical records must be preserved, and any ETL workflows that need to update specific partitions or rows rather than full tables.

How does this improve performance on Serverless SQL Warehouses?

By eliminating full-table rewrites, Dynamic Insert Overwrite reduces computational overhead and query execution time. This is especially valuable on Serverless SQL Warehouses where reducing compute consumption directly improves performance and reduces costs.

What are the best practices for using Dynamic Insert Overwrite?

This video explores practical tips for leveraging Dynamic Insert Overwrite on Databricks including when to use it versus alternatives like MERGE, how to structure data for optimal performance, and strategies for incremental data workloads.

Full transcript

0:00 Introduction and speakers

Hi everyone. Uh welcome to the session. I'm super glad that so many of you turn up. Uh it's really nice to see you all. Um so I'm long. I'm a software engineer at based in the Amsterdam office. Uh I work in data uh and in Delta. Lately I have been working on the dynamic inquire feature. uh and in the past I've also worked on performance optimization projects and also projects on the open source delta repo such as delta connect that came came out in delta 4.
0 And today I'm with my co-presenter and my good colleague. Uh hi Um I work with Long also based based out of Amsterdam uh software engineer for data bricks and I'm uh I'm also working on Delta. Um uh we did a lot of work in the Amsterdam uh team on various delta features including deletion vectors which you might know about and um yeah so this is long show so I'm going to give the stage to him and uh I'll be back later for some uh for some additional content.
Thank you. Thank you. Uh so let's talk about the reason why we're here today. Uh so a common ETL ETL scenario is the need to atomically override the latest date partition. So let's say that you are the head of data engineering at a retail company uh managing like tons of sales data and in order to do that on the on the left you have a downstream table called history uh store sales uh that is partitioned by date. So you can basically contain the sales record uh FO every day and on the other and on the other side you have an upstream table called uh latest updated sales that contains latest sales data coming in and some of them could be late arriving as well.

1:18 ETL overwrite challenges

And so to ensure that your analytics is always up to date you need to regularly override the latest date partition um with fresh data from the u latest updated sales table. So let's walk through an example of this uh overwriting process. So you can see uh red means that the data is outdated and green means that uh the data is a new data coming in. So we we already have outdated data for date 9th and 10th uh because on date 9th we have five spoons instead of four spoons and on date uh 10th we have three forks and four knives instead of two forks.
So we need to update uh date 9th and 10th. And so we made the update uh and then once again we refresh the latest updated sales stable and we see that once again we now have latest data for date 9th. Uh so we found out that on day 10 we have two knives and two picss instead of instead of three knives instead of four knives. So perhaps someone made a mistake entering the data previously. So and we also have six scoop on date 11th.
So we need to replace all the existing entries for date 10th again and also to add the entries for date 11th. So uh hopefully you all get the idea by now. And so uh based on the example that we just show looks like um we are presented with the following three challenges for our solution. Uh number one is that our solution should only touch the latest uh change date partition instead of the whole table because the history store sales table would be quite big.

3:20 Requirement criteria

Uh the second thing is that the solution should automatically detect the latest change date partition. And the third thing is that the solution should be simple and intuitive for everyone to use not just PhD and data bricks. And so based on these three challenges our solution needs to satisfy the following three properties. It should be efficient in terms of cost and performance. It should be dynamic meaning that it can automally detect solid change date partition and also it should be easy to use.
Uh so let's check uh let's do like a round of check to see if there's are any existing approaches on data bricks that can potentially satisfy the three mentioned solution requirements. So approach number one some of you might know already is dynamic partition overgride that was introduced in spark a few years ago. Um so the solution is dynamic but it's not really easy to use. So this spark config at the top uh spark. sql. source. partition overgride mode if you set it to static then it overrides the whole table.

4:21 Evaluating current approaches

But if you set it to dynamic then it only touches the latest change date partition. Um so if you set the wrong config value then it basically results in a completely different behavior and it can corrupt your table. Um and also like the spark config that attach that controls this behavior is attached to the spark session instead of the SQL statement itself and it can be far away from the actual statement. So this makes it easy to forget uh about this uh config even though it's such an important config and also the solution doesn't show the partitioning keys.
So you cannot easily see from the get-go uh what the solution is actually doing. And another limitation that we will touch upon at a later part of the presentation is that this solution only works for the full set of tables partition key neither liquid uh normal keys. Second approach is replaceware. Uh that some of you might know already that was introduced in databicks a couple years ago. Uh it's easy to use. Uh it's efficient.
Replace where consists of a delete and insert and we optimize it by paralyzing the delete and insert. Uh and also we ensure the source query is only read once even though they are two even though they are delete and insert. Uh but however the solution is not dynamic. you need to hardcode the date to overgride. So you need to constantly update the graph filter to only override the latest change date partition. So this broccoli make it uh unusable and also the predicate cannot reference the source query columns.
What I mean by the source query is that the select star from latest updated sales. So this means that you cannot um add a subquery in the well filter to automatically fetch the latest change date partition. Um, and last but not least, you can use transaction. It's dynamic, but as you can already see, it's like quite complicated and it's not so easy to use. Um, and it's not so efficient because uh you you can notice that the source data here is read multiple times by the delete and by the insert.
Um, so here's a summary of the three approaches with regards to the three uh requirements. And as you can see, none of them satisfied all the requirements. But is this the end? Can we do better? Can we have a solution that uh overcome this restriction? So I'm very very excited to announce to you replace using. Uh replace using is simple. It has no knobs. It's dynamic. Uh it's flexible. You can customize the matching logic by specifying any list of columns, not just a list of partition columns.

7:11 Introducing replace using and on

And uh it's also efficient. Uh replace using also had a delete and insert like replace where and we also paralyze the delete and insert and also we ensure the source query uh is only read once. But one caveat about replaceing is that it's not no safe because it follows the default SQL equality like join using. So what do I mean by not no safe? So this means that if you have a node date in your history store sales table then new data with a node date won't override the node partition and instead it would append data to the no partition always and so let's say that you want to treat null like any other values then we have a complement solution called replace on.
So with replace on it's quite similar to replace using but instead of specifying a list of columns you specify a boolean matching condition like join on. Um and as you can see this matching logic is more flexible than replace using. So here you can use like the spaceship or the SQL no quality to handle nodes basically. The only downside is that uh this syntax is slightly more complex than replace using. Here's also the connection between replace using and replace on.
Uh when you specify uh a list of columns in replace using, this is equivalent to specifying a list of equality condition for each column and then you connect this condition by a logical end uh in replace. Um here's also the Scala and Python data frame APIs uh that you can use and as you can see it's quite similar to the SQL version. uh you specify a list of columns for replace using and you specify the boolean matching condition for place on and so some of you might wonder why don't we use merge in this case uh merge is very well known by everyone and it's the workhorse of ETHL on data bricks and so for this part I would hand it over to our merch expert uh take it away I want that.

9:15 Why merge falls short

So yeah, this is a question that I got a number of times when we were talking to people about this replace using, replace where, replace on. Why not use merge? Because merge is intended to take in new data from a data source and just replace the existing rows that you have, right? It's supposed to be able to do this. Now, if you actually go and look at what merge does, then it turns out that it can't. And I just want to convince you of this by actually showing you what happens if you try to do this.
So here's attempt number one that you could do. So let's say that you just merge by date. So we have the same example as as in the introduction. So you do uh merge on uh the and match on the date column and just a simple upsert criterion. So when matched matched then update when not matched then you insert the row. Super simple upsert. And now what you can see is that it does match all the rows for the for the corresponding dates.
But what goes wrong is this thing at the bottom. You see the two arrows from the uh right table pointing at one row at the left table. The problem is that merge actually the way that it works is you actually give uh an instruction for how to modify each row in your target table and that instruction has to be unambiguous and it's based on the row that matches it from your source table. In this case, there's actually two rows from your source table matching that row.
So, uh doesn't work because you have an ambiguous instruction. you're not going to be able to say update this row with both those rows. So this doesn't work. So let's try the other obvious thing. So let's see if we can do this by taking a unique key and actually using that for the upsert. Well, what happens if we do that? Okay, it's great. So the knife at the bottom, it doesn't have a match because there is no knife in the target table.
So yes, it will be inserted. the spoon and the fork, they have matching keys in the target table, so they will be updated. Great. But actually, there's that row on the left. What's going to happen with that? That row is also on date the 10th. It doesn't have a corresponding row in your source table. So, who's going to delete that row? We were supposed to replace the entire partition. We have new data for the entire partition of June 10th, but nothing in this merge is going to match that.
So nothing is going to delete that row. So this attempt once again failed. So this is why merge cannot do it. All of the obvious ways to do it just don't do it. And the reason is fundamentally that merge operates on a per row basis. And we're really thinking of a per partition or per groups of rows basis uh for this new feature. So um let's move on to something else. So as long alluded to before the old solution that we had the uh with the configuration the dynamic partition overwrite required you to replace data just by your partition keys.

12:52 Compatibility and features

But we've been telling people that partition uh partitioning hive style partitioning is a thing of the past and you should really be using liquid clustering because it's more flexible. Uh you could change it whenever you want. It it doesn't have problems with bad file sizes etc. All sorts of advantages, better concurrency, all sorts of reasons why you want that. But the old solution didn't work well. Replace using will work with your liquid cluster tables.
And to make it better actually you can specify arbitrary keys. So it doesn't matter which keys you use. You can just replace using date if your partition key is by customer ID or by whatever your uh your table is partitioned or clustered by. It doesn't matter. Your replace using works. And with replace on, you can also do even more advanced matching conditions to figure out which rows need to be deleted before you insert your new data.
So with that, I'm sure all of you are wondering when can we start using this. Actually, this feature is one of the most requested features by our customers. So we we've been uh getting requests from this like every couple of weeks uh for unfortunately too long but you won't have to wait much longer because replace using is actually available in public preview in data bricks runtime 60. 3. So it's already available with the one restriction that it actually requires you to just use exactly your table's partitioning keys.

14:09 Availability and summary

So it's essentially like your old config based dynamic partition overwrite but with the new syntax. In DBR 17. 1 we have replace on syntax. So the more advanced syntax that allows you to do everything and the full support for replace using will be available in DBR7. 2 17. 2. So that's in a couple of months. I don't know I don't remember the exact date but it's um very soon now. So that brings us to the end of this talk.
Thank you for listening. Um so in summary we have this replace using efficient dynamic easy to use works everywhere. Replace on for advanced matching logic and if you have nulls. So uh please try it out when it's available and uh give us your feedback. Uh we'd love to hear from you. Um and uh with that um long come back on. So um that's it for today. So we invite you to come uh uh come to the stage and ask questions if you have any.

Learn more about the Databricks Data and AI platform.