Easy Button Auto CDC: Dimensional Data Marts With Spark Declarative Pipelines

Summary

84.51° processes 8 million sales transactions and 80 million purchased items per day and uses Lakeflow Spark Declarative Pipelines with Auto CDC to handle inserts, updates, and deletes across fact and dimension tables without writing complex merge logic.
Auto CDC provides built-in support for slowly changing dimension types 1 and 2, and the Auto CDC From Snapshot capability infers change sequences from full source snapshots when the upstream system does not provide a change data feed.
The video demonstrates concrete code patterns for fact tables using Kafka event streams and dimension tables using Delta Sharing, with specific production guidance on handling nulls, deletions, and out-of-order data arrivals safely.

Easy Button Auto CDC: Dimensional Data Marts With Spark Declarative Pipelines

At 84.51°, processing 8 million sales transactions and 80 million purchased items per day requires dimensional data modeling that scales. Their legacy ETL proved unworkable; they needed a simpler, more reliable approach without hundreds of lines of merge logic.
Learn how to use Lakeflow Spark Declarative Pipelines and Auto CDC to handle conditional inserts, updates, and deletes across fact and dimension tables. this video walks through streaming reads with readStream for continuous data, batch reads for dimensional data, slowly changing dimension types 1 and 2, deletion behavior in different contexts, handling nulls carefully, and avoiding unintentional data corruption. You'll see real code examples for fact tables using Kafka event streams and dimension tables using Delta sharing, with practical guidance for production environments handling millions of rows daily.
🤝

Chapters

FAQs

What is Auto CDC in Spark Declarative Pipelines and why is it useful?

Auto CDC is a declarative framework within Lakeflow Spark Declarative Pipelines on the Databricks Data and AI platform that automatically handles change data capture without requiring engineers to write complex merge statements or manage out-of-sequence arrival logic. It also provides built-in support for slowly changing dimension types 1 and 2, eliminating hundreds of lines of custom SQL.

What is the difference between Auto CDC and Auto CDC From Snapshot?

Standard Auto CDC processes a stream of change events with explicit insert, update, and delete indicators delivered directly from the source system. Auto CDC From Snapshot is used when the source does not provide a change feed — it compares successive full snapshots and infers what changed, making CDC accessible even when the upstream application lacks native CDC capabilities.

How does 84.51° use Auto CDC for fact tables versus dimension tables?

For fact tables, 84.51° uses streaming reads from Kafka event streams with Auto CDC to continuously capture transaction events. For dimension tables, they use batch reads with Auto CDC From Snapshot, pulling from Delta Sharing sources and inferring changes by comparing full table scans — a pattern well suited to source systems that publish periodic snapshots.

What are the risks of handling nulls incorrectly in Auto CDC pipelines?

Nulls in Auto CDC flows can cause unintentional data corruption because a null value in an update record may be interpreted as an intentional write of null rather than as a missing field with no change intended. This video covers specific patterns for distinguishing meaningful nulls from absent fields to prevent silent data corruption in production dimensional tables.

Full transcript

[00:10] Um my name is Shu. I am a solution architect at data bricks and thanks for coming to the session. I know you have lots of other options. So thanks for coming and today me and Scott is going to talk to you about easy button auto CDC and um I'll let Scott introduce
[00:27] himself when his part comes up. And we're going to talk about auto CDC which is an easy button for streaming data flows and batch data flows.
[00:46] Now what is auto CDC? I know at keynote you heard CDC represents continuous data corruption. That's not it. It's continuous data a change data capture. Yeah,
[01:06] CDC is auto CDC is automatic change data capture. It basically is a decorative framework so that you can capture your change data without reasoning with a complex merge statement and then complex logic writing hundreds of lines of SQL
[01:22] to sort out out of sequence data arrivals and then duplication and autoc also has built in a slow change dimension one as well as slow change dimension two support out of the
[01:37] So again you don't have to reason that in your merge statement. So slow changing dimension one and slow changing dimension two they are these are really not new to everybody but I just want to quickly capture slow change dimension one means I just want the
[01:54] latest and greatest and slow change in dimension two means I want a full state the full life cycle and a typical think about very very easiest thing to reason about when you shop right you add
[02:09] something to your cart and then you decide decided to put it back and empty your cart. So that is a lot of information that you wanted to work with. You don't want to just write it off as a slow change dimension one. But there are situations both slow change
[02:25] dimension one and slow change dimension two makes sense. And now what is autoc with snapshot? So as data engineer all of us have written change data capture and you find
[02:42] yourself in a situation you just at the mercy of your source system you don't have a perfectly change data sequence passed to you as the source not what you do so that's exactly what the autocd from snapshot implements it basically
[03:01] compare your snapshots and infer what our changes. So you don't have to put in like extensive application just to capture that change sequence. We're going to talk a lot more about how
[03:16] the snapshot infers the change sequence. But briefly here if it's in the source not in the target that means an insert. If it's in the source in the target and you have the key defined that means an
[03:32] update. So that's no big deal there. But then the interesting part is if it shows up in your target but is disappeared from your source, right? You can infer that as a delete. And we're going to talk more about the delete and but delete is usually the tricky case when
[03:49] you do your usual ingestion upserts normal right business as usual. Delete is what we pay attention to. So why we have a auto CDC? We already have a merge for your coding ninjas. I
[04:06] can write this right. AutoCDC is just a easy button for you implement things out of the box. It defines the your primary keys or your unique keys. So you don't have to worry about dduplication. It'll do that for you. It defines a
[04:22] sequence column which then sorting your records for you. So you can it handles late arrivals out of sequence for you out of the box. And then the thing about the slow change dimension one and two is essentially just like a parameter you
[04:38] define. So you don't have to even reason about it. You just need to say oh I want to sd1 or I want sedd2 decoratively. And we have a blog publishing out about autocdc versus the performance of merge.
[04:55] You'll see that in our blogs. Just quickly list out here because autocdc is implemented out of the box that we have a optimized implementation versus your handwritten merge statements so that your code actually runs very
[05:12] efficient and then you would have much less code and then much less storage when you needed to do the merge scanning the source scanning the target compare the difference and then also it That's why it have way better throughput as
[05:29] well as lower cost and then better SOAs. Okay, so we talked about all the upsets and normal business as usual. Let's take some deep dives into deletions and nos.
[05:45] These are I like to call it like overload the confusion. We get clarity how exactly deletion behave in the situations. So in a streaming context which is autocd deletion behavior and then batch
[06:01] context which is autocd from snapshot deletion behavior. We'll talk about these two and then we'll talk about the nulls. How do we update our results with nulls intentionally like we don't want to corrupt the data when the null is just
[06:17] unintentional. I have some source semi nulls I end up corrupting my target with nulls. So that's unintentional now update. So we'll talk about how we handle those
[06:34] autocd in a streaming context when deletion comes autocd API let you just say apply as delete. So this at itself is easy but the complex part is you may not have control if your source going to give you a tag in your data
[06:51] that says okay if this operation says delete apply as delete so that's how the auto CDC API works if you are able to tag it says okay this row has an operation that is a delete when your target comes auto CDC says apply as
[07:08] delete now the behavior of slow changing dimension one is it's a deletion. This record cease to exist as if nothing ever happened. And slow chain dimension two however it says I'm going to mark you as delete but mark when it delete
[07:25] happened. So give you the full context the autoc from snapshot the deletion behavior in a batch context. We mentioned a little bit about how the
[07:40] engine infers it's a delete because it shows up in your source snapshot but then this it shows up in your target snapshot but then it disappear from your source. So the API detect that as a deletion, it will then delete from your
[07:56] target based on if you want it to be a slow change dimension one, it deleted just the record doesn't exist anymore or you do the slow change dimension two markup when this record is deleted. So you have a full life cycle of that
[08:11] event. The behavior of std type one and std type two for deletion in streaming and batch context, they're the same. But then how the deletion is detected and inferred that's a difference.
[08:31] Okay. So for nulls I don't know how many of you had unintentional nos in your data ingestion pipelines. Now you can have a fine grain control of you wanted to tell the API your intention when I wanted to update when it's a null value and when the null value does not correct
[08:48] my target data. So there are two solutions to avoid this like ignore updates ignore null updates the all or nothing kind of scenario.
[09:05] The first solution is if you know exactly that you have a a some subset of columns that they will never take nulls. These are golden records only if there's some actual data come in that I'm going to keep these and then ignore any of the nulls. You can explicitly set it here. You can say
[09:21] ignore no updates on these columns. So this is one way if your use case happen to fit into you know exactly what columns that you want to do ignore the notes. And then the second solution is this one is tricky because it depends on
[09:38] your source system you can actually make a mark. It provides you a status column meaning for example if your pipeline you have many heterogeneous sources some source will send partial data some source send another subset of data they all know that I'm going to only send you
[09:55] column one to column two and the other sources I'm going to send you column three to column four they will mark it for you so that you know you can do rowby row if this is from this source I'm going to trust the status column so that any data from these column columns
[10:12] are updated from source one and any data from like another the stat status column markup coming from a different source. So that basically give you a multi-source rowbyrow kind of control what data that you wanted to um honor
[10:29] the nulls or avoiding unintentional nulls. Okay, so with that I'm going to pass it on to Scott. He's going to introduce himself and he's a real ninja for a Spark decorative pipeline and I've been working with him for like a few years. Leave it on that slide. You want me to go forward?
[10:45] I don't want to spoil my next slide. So just you want me to go forward? Nope. All right. Nice job. Sh. Thank you. Do you want your
[11:02] Okay, we're doing good on time. Our our our goal is to leave plenty of time at the end for questions. We're hoping to get some good conversation going at the end. So, I'll go through my part and then we'll do that. I do want to set some context. I'm going to show you a couple demos and I just want people to have some context of what it is I'm I'm
[11:18] trying to show because AutoCDC has many different features as you've seen. There's just, you know, all kinds of features and knobs to twist in ter and each of them has applicability in different scenarios. Some of those are applicable when you're reading change data. So if you're consuming change data
[11:35] from a source like a Delta Lake table that has change feed enabled and you're consuming change data, you can use AutoC with some of those change attributes to to handle uh updates in your target. That is not what I'm showing you. So I
[11:51] just want to be clear if you're looking at my demos and going, "Well, where's the change data?" It's not there. What we're showing is how you can use AutoC when you're just reading the source data itself. So you don't have those change records, you just have the data and AutoC will automatically derive what has
[12:10] changed by comparing the source to the target. So you can still get change data even though you're not reading a change feed. So that's the two examples I'm going to show you. I'm Scott. I'm a data engineer at 8451. 8451 is wholly owned by the Kroger
[12:27] Company, national grocery retailer. You may know them by other names. Fred Meyer, Dylan, Smiths, they have about 15 different banner names. They across the country. Uh so national grocery retailer. Uh anyone's who's familiar with
[12:42] dimensional data modeling, facts, dimensions, that sort of thing. Good. Because that's the context of what I'm going to talk about. I'm going to show you the first example is using AutoCDC in a fact table data flow. And then the second example is using AutoCDC in a
[12:58] dimension table load. Okay. So in the the first example, we're going to use a a a read stream statement. So the first example is going to be a fact
[13:13] table loaded with read stream. Second table dimension table loaded with just a regular batch read.
[13:28] So why use readstream? Because Scott loves read. That's why you should use readstream. So if you've used uh uh readstream at all, you're familiar with functionality. If not, let me describe it in a very simplistic way. So read simply keeps a checkpoint. So it
[13:45] knows what's the last data you read upstream. So you read some data, you write those rows, the checkpoint is advanced. You come back later, you read some new data, it's going to pick up at the checkpoint, it's going to write that data, advance the checkpoint. The beauty
[14:02] of that automatic checkpoint advance is it gives you guaranteed once delivery. The checkpoint's not advanced until the right succeeds. So if the right fails, checkpoint is not advanced. And the next time you call read stream, you get that original data from your last checkpoint.
[14:18] So you can reprocess it. So automatic reprocessing guaranteed once delivery. Any of you that have built batch data flows, which is probably all of you because we've been doing that for decades, you know the concerns of building a batch load incremental
[14:34] system. You're constantly worried about did did I get too many rows? Did I get not enough rows? Did I double load any rows? Did I miss something? When you use read stream spark structure streaming automatic checkpointing, all those edge cases go away. You don't code for them.
[14:50] you don't worry about them. Go to sleep and don't worry about it because it is built in guaranteed automatic. So use readstream. There's particular sets of data where readstream is particularly advantageous and that's in your typical fact data
[15:06] which I'll call continuous data. Continuous data are events that happen all day long like sales transactions, clicks, events, any kind of telemetry. That stuff that logically has no beginning of time. It has no end of time. It's just an unbounded
[15:22] continuous flow of data. So readstream works particularly well there. Now historically readstream was often referred to as an appendon operation because in its simplest form you would read some data, write the data, go read
[15:37] more data, write the data with by introducing autoCDC into your data flow. Now you can control how the data is written to the target. So you can conditionally do inserts, updates, and even deletes on the target table even though you're using a read stream
[15:53] mechanism. So here's the first example I'm going to show you. So uh Kroger customers buying groceries. Kroger does somewhere around 8 to 10 million sales transactions per day.
[16:10] These transactions are are streaming into Kroger's data center into a streaming platform all day long. They come from, you know, instore sales of shoppers buying stuff. You can order uh groceries for pickup at the store. They'll come out and, you know, load them in your trunk and check you out and
[16:27] you drive away. Or you can order groceries online for delivery to your home. Regardless how you get your groceries, a sale transaction is is generated by a point of sale interface and that sale is immediately streamed
[16:42] into into Kroger's data center. So those arrive in real time. On my team at 8451, we want to consume Kroger sales and put them in our data mart to do all the the sales analytics that our data scientists do. So this is
[16:59] uh we built this uh spark declarative pipeline. It's doing a read stream on that that Kafka topic via Azure event hubs for what it's worth um and pulling those sales into our data mart.
[17:15] So let's take a look at the code for this this data flow. One thing you're going to find about the code is it's declarative in nature as is Spark declarative pipelines and it is surprisingly simple as Shu mentioned. Even though reasoning about is this an insert or an update, is it out of
[17:31] sequence or not can be you know kind of complicated to think through the code to implement this is is surprisingly simple. You're going to see three pieces of code for each data flow. The first piece identifies the source data. So any query
[17:46] you can write be it a SQL query or a a pispark data frame either one it's just a definition of of a query against a source. So it's going to return a row set right rows and columns of data. This is our source definition. The key thing
[18:02] here is line four spark readadstream. That's important because it it when it determines which version of autocdc I'm I'm going to use. So this is a read stream. The data in this case is pretty simple. It's coming from Kafka. So you
[18:18] just have a key and a value. The key is the uh unique identifier for the transaction. So you can think of it like a transaction ID. It's just a gooid that the the point of sale interface generates to uniquely identify that transaction. And the value is just that big complex nested JSON blob that's got
[18:36] all the structure of the transaction in it. All the, you know, hundreds of attributes nested in there. The second piece is define the target table. Uh so you use this statement create streaming table. All I'm setting
[18:52] up here is the name of the table. I'm also specifying which schema cataloging schema it's going to be in. But it gives the table a name. And then I set any table level properties. I'm not defining the schema here as in the columns because that's what's coming from the source. That's going to determine the
[19:08] contents uh of the table. I'm just setting up kind of a name as a target. This is where you're going to write the data. And please ignore line three. This is some old code. I would not recommend you use partitioning. You should use liquid clustering instead. But this was an old
[19:25] snippet, so forgive me. So the third piece, this is the magic. So this is create autocd flow. This is what connects the source to the target and defines the rules for the merge. So just these three lines right here are
[19:42] what's determining the whole behavior of is this a change record or not. So it's going to take the key in this case the transaction ID look in the target table say does this key exist in the target table. If it does not it's going to insert it in the target table. If it
[19:59] does exist already it becomes a candidate for an update. I say a candidate because it's also going to evaluate the sequence by uh since in a streaming data flow you know reading multiple partitions from a topic you're not guaranteed
[20:14] events arrive necessarily in chronological order. So you have to account for the fact that it is possible you could have gotten this transaction you know version two of this transaction first and then later got version one and you wouldn't want to overwrite that in your table. Uh so so the sequence this
[20:32] time stamp in the sequence is coming from the transaction record. So it knows the timestamp of of that transaction. So that controls just making sure the target table always has the latest version of that transaction and you never overwrite it with an older
[20:48] version. And finally, if you're astute with all this dimensional stuff, you're you're going to look at that and say, "Scd type one, Scott, you told me we're loading a fact table. Why am I using slowly changing
[21:04] dimension type one on a fact table?" Well, well, well, what that really means is just a simple upsert, right? It just means conditionally do an insert or an update. So, so that pattern is just as applicable to a fact table as it is to a type one sedd. It just means it's a simple upsert. no history tracking and
[21:20] all that crazy stuff. All right, so that was the fact table streaming read. Now I'm going to show you how we use auto CDC in a in a batch read scenario.
[21:40] So why use batch? Didn't Scott just say how much streaming is so awesome and you should just always use streaming? Well, you should if you can, but you can't always use a streaming read. Some source systems just don't support a streaming read. So you have no option but to read the entire source. And there are cases
[21:56] where even if the source does support a streaming read, streaming gets very complicated when you have multiple inputs. So if I got to read, you know, three tables and then do some joins with a foreign key relationship. Trying to stream from three tables at once gets
[22:13] really complicated. You have to set watermarks and conditions on how long is each stream held in memory to make sure the join, you know, can find the record it needs to match to. And it gets ugly very quickly. So on a small set of data, you'd probably say it's not worth the
[22:29] effort, you know, just just just read the entire data set. So dimensional data is usually relatively small, uh slowly changing. So it's not time series data where you're getting, you know, on and on new new stuff. it's just a set of reference data or master data.
[22:46] In general, scanning the entire data source is usually not a problem. So that's the case here. An example I'm going to show you and that's why we decided to use a batch read. So our example I'm going to show you is we're loading our store
[23:02] dimension. So every sale is associated with a store either where you bought it or you picked it up or it was shipped out of. So So the store is an attribute of the sale. So, we build a store dimension to to link to the sale. Um, and I mentioned 8451 and Kroger. We're
[23:20] kind of two different companies, but same same parent, but we still operate in two different Azure tenants. We used to be a separate company. So, on the left, you see Kroger's Unity catalog. They have an Azure tenant. They have their Unity catalog 8451. We have our
[23:35] own Azure tenant and our Unity catalog. So, for us to share data back and forth, we use delta sharing. So, someone at Kroger is maintaining this list of locations, property locations, every physical property that Kroger owns, about 10,000 locations across the
[23:50] country. That's stores, distribution centers, fulfillment centers, office buildings, any property they own is in this master uh database that they maintain. They share that to us through delta sharing. So, when I look at it,
[24:06] what I see are these I think it's seven views. So that's what they have shared to us these seven views and they said there there's all the location information you go get it. So I got to join in a couple different passes several of these tables together. So you
[24:21] can see why immediately I said I don't want to read stream that. It's a view. You know it's it it it's not going to have the transactional consistency of a table trying to stream off of those. You know it's just it's too much to reason about and it's 10,000 rows. So just scan
[24:36] the source, bring it in, figure out what changed, and you're done. So again, uh, well, here's our data flow. We're going to do a batch read on those seven views. Uh, apply AutoC to
[24:53] detect changes. Write that into a type two slowly changing dimension as our as our target table. Again, three pieces of code. There's the source, the target, and the the merge mapping. So this is the source. Key
[25:08] thing here is on line seven, spark read, not read stream, spark read. So a full table scan of the source table, joining it with several of the other views in this little snippet and adding a few columns on there.
[25:25] So the second piece, define the target table. So I'm give it a name. I give it a few other properties that gives me a target to write to. And finally, create auto CDC from snapshot flow. So that's what's different here. When you use a read,
[25:42] you're in batch mode. You have to use autocd from snapshot flow. So shu on the earlier screen talked about autocd and how it compares snapshots. So it can be a little vague. What do you mean by snapshot? Well, when you look at
[25:58] this statement right here, the target table is a snapshot of the data. You might call that the previous snapshot, right? It's what's currently sitting in the table. I've read the source. I now have a new snapshot. So, I have the previous snapshot in the table. I have
[26:14] the current snapshot that I just read from the source. Snapshot meaning it's a full set of data. It's not, you know, a micro batch or a part of a stream. It's a full set of the data. So the comparison here is simply compare
[26:29] source to target and figure out what's different. So again, the way it's going to do that's just with the key. It's going to take the location ID. Do you exist in the target table? Yes or no. If you don't, it adds that row to the table. If it does exist, it's going to
[26:44] overwrite it, update it. In this case, there is no sequencing because we're using a full snapshot of the data. That location's only in there once. So I don't have to reason about you know part of a stream and did I read it before has it come through again doesn't matter
[27:01] whatever's in that snapshot is the latest version of that so it it's going to up it's going to overwrite the location in that case if it gets a match stored as type two sedd this time so I get all my history tracking and normal uh type two scd pattern uh then the
[27:18] other options just if you want to ignore some columns for updates so not really relevant Did I cover everything? Okay, so in summary, so Shu talked about auto
[27:35] CDC as a method for conditional insert, update, delete on a target table. Again, you can use it with change data or without change data. The options and features are a little different, but it it works on in either case. We talked about streaming data flows, batch data
[27:52] flows, when it's advantageous to use one versus the other. And then a key thing we pointed out is there's two different versions of autocd. When you're using readream, you use create autocd flow. When you're using read batch read, you use create autocd
[28:09] from snapshot flow. They function mostly the same, but there are a few kind of nuance differences in behavior. the way updates get sequenced are are handled slightly different. Uh delete behavior is slightly different between the two.
[28:24] And if you are doing type two sedds where you're setting a start at and end at for each row, the actual time stamp value will be a little different if you're using a batch read versus a stream read. Uh I'm not going to get into all those nitty-gritty details. It's all documented. I just want you to
[28:40] be aware that there are some differences. So, okay. I think that clock says we have about 11 minutes left. Is that right? So, I'd like to open it up for questions. We'd like to get some good
[28:55] discussion going. We do have a a microphone here. So, thanks everyone. We'll stick around for a little bit if you want to talk more.

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.