Skip to main content

Mastering Change Data Capture With DLT

Summary

  • Apply Changes API simplification: LakeFlow's Apply Changes handles CDC complexity automatically—managing global order, out-of-order events, and stateful transformations without manual stream processing.
  • Square's data mesh implementation: Square uses Apply Changes to build CDC pipelines for transactional systems, enabling scalable data sharing across silver and gold layers.
  • SCD type support with CDC: Apply Changes supports Type 1 and Type 2 slowly changing dimensions, snapshot-based CDC, and continuous change feeds from message buses.

Mastering Change Data Capture With DLT

Watch: Mastering Change Data Capture With DLT
Talk by Jacob Gollub, Software Engineer, Square ; Ray Zhu, Director, Product Management, Databricks
Transactional systems are a common source of data for analytics, and Change Data Capture (CDC) offers an efficient way to extract only what’s changed. However, ingesting CDC data into an analytics system comes with challenges, such as handling out-of-order events or maintaining global order across multiple streams. These issues often require complex, stateful stream processing logic. this video will explore how DLT simplifies CDC ingestion using the Apply Changes function. With Apply Changes, global ordering across multiple change feeds is handled automatically — there is no need to manually manage state or understand advanced streaming concepts like watermarks. It supports both snapshot-based inputs from cloud storage and continuous change feeds from systems like message buses, reducing complexity for common streaming use cases.
Here’s more to explore:
Production ready data pipelines for analytics and AI: https://www.databricks.com/solutions/data-engineering
The Big Book of Data Engineering: https://www.databricks.com/resources/ebook/big-book-data-engineering-2nd-edition
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

What challenges does Apply Changes solve for CDC pipelines?

Apply Changes automatically manages global ordering across multiple change feeds, handles out-of-order events, and implements stateful transformations—eliminating complex manual stream processing.

How does Square use CDC in its data architecture?

Square leverages Apply Changes to ingest transactional changes from systems like loans and credit platforms, building cleansed silver-layer datasets that feed downstream analytics and ML.

What is change data capture (CDC) and why do teams use it?

Change data capture (CDC) extracts and processes only the data that has changed in operational systems, enabling organizations to keep data lakes synchronized with source systems in real time without full table scans. Teams use CDC to maintain fresh copies of operational databases in the lakehouse, enable real-time reporting without impacting source systems, and power event-driven architectures where business logic responds immediately to data changes. CDC is essential for scenarios requiring low-latency data freshness combined with the flexibility of keeping source and analytics systems decoupled.

What are alternatives to Debezium for change data capture?

Delta Live Tables (DLT) Apply Changes provides a declarative approach to CDC that is simpler and more scalable than alternatives like Debezium for teams already using Databricks. While Debezium is a powerful standalone CDC platform supporting many databases, DLT Apply Changes integrates natively with Delta Lake and Unity Catalog, providing better integration with the Databricks ecosystem and lower operational overhead. For teams committed to Databricks, DLT's declarative CDC approach reduces pipeline development time and maintenance burden compared to Debezium or custom Spark-based solutions.

Full transcript

0:00 Introduction to LakeFlow

All right. Uh, let's get started. Um, good afternoon everyone. Um, thanks for coming to this talk. Uh, I know it's 400 pm after long day. I know it takes a lot to show up at, uh, 4 pm talk. So, I appreciate it. And hopefully once you guys are done with, uh, this talk, you can go get your beers and happy hours. Um, so. I'm from the product team. I work on data engineering products for data bricks. And today with me I have and uh they are here uh which they're going to share their use case about using um change data feeds capabilities on top of um uh declarative pipelines hot off the press.
The product used to be called uh DT delta live tables and today we announced um that we're going to after a bunch of improvements it's next generation we're going to rename it into lakeflow declarative pipelines. There will be more announcement to come tomorrow during the keynote. Fully backwards compatible. Um no change to the code required. You're going to get a benefit of the new capabilities. Um so leg flow declarative pipelines.
Here's a quick agenda. I'll give a very like brief two to three minutes overview of declarative pipelines why we built it how that becomes the underpinninging uh component of the whole lakeflow data and drink products for data bricks. Then I'll dive into um change data capture and u I'll give an overview of the functionality but actually I'll I'll sort of um unveil the init initial inspiration of why we built that feature some of the complexities and challenges we ran into and how we sort of solve these problems in this specific feature what we call apply changes.

1:09 Pipeline development challenges

Um then you know uh the most important part which is I want to have uh and to come on stage to talk about block how they are using uh this particular feature in their use cases and workloads square and block you guys are as bad as naming things as we are. Uh so first of all flow declarative pipelines uh why we built it what was the original you know inspiration. So maybe before I get started, quick show of hands. How many of you guys how many of you guys already use sort of delta live table not declarative pipelines uh today?

2:02 Data engineering challenges

Thank you. And how many of you guys already use the apply changes feature uh with DT? So a handful. Oh, just great. Okay, thanks. Put your hands down. So for all the data engineering teams, the data engineers who are doing uh production data pipelines, what you really want to focus on is this transformation logic of how you want to ingest and process your data. But the reality is that there's a lot of other complexities around that core business logic you have to deal with.
So often times you have to deal with different product stacks. If you use batch processing, you need a batch stack. If you do streaming, you need a streaming stack. You have different downstream teams. data science want to have structured data sorry unstructured data with a data lake you have your business BI workloads you want to structure data there's just a lot of complexities to integrate with and to deal with then on top of that you know data is moving towards more sort of like software uh dev best practice so you have to think about how do you version control but not only your pipeline code but also how do you version control your data sets and how do you manage your deployment infrastruure structure.
Then of course like there's governance and there's data quality. You want to make sure that the data is ready for consumption downstream. You want to make sure the right person have the right access to the data. You want to make sure your downstream teams has a catalog to discover all of these data. Then even more complex like you know for all of you data engineers it's not always a happy case. So you know every week every month something goes wrong there's late arrival some data was wrong the business has changed requirement on this particular transformation and definition you have to go back in time clean up data rerun the entire process all that replan rewind and dependency management gets very complicated.
So the original vision of the clever pipeline is why don't we take care of all of these surrounding complexities which has nothing to do with your core transformation logic so that you as a data team you can focus on deliver the value of defining this unique business transformation logic. So sort of initially why we built declarative pipelines, it's an abstraction layer sitting on top of the spark and structure streaming engine unifies batch and uh streaming in one stack abstracts away all of these surrounding complexities that you don't need to deal with a product.
Now just a bit of a recap we've renamed that into uh lakeflow declarative pipelines. Uh it's all branded under data bricks lakeflow which is a unified data engineering platform sitting alongside connect for simple ingestion declarative pipelines for all of the ETL workloads. We also have jobs a you know fully featured allpurpose orchestrator. Um so these three pieces uh become the lakeflow portfolio and uh the clever pipelines been in the market for the past two and a half three years.
So we have you know many many customers are using that in production. If you look at this, you know, slide, you can look at, you know, virtually across all different industry segments. Companies of different sizes are finding a lot of value from declarative pipelines, running production grade pipelines without having to worry about all of these surrounding operational complexities. So that's a quick overview. Let's dive into sort of uh change data capture.
Um quick overview, what is change data fees? Why do people want to do change data capture? So a lot of the times for you guys you have your upstream systems the applications that's where the data is gener being generated then you need to ingest and move that data into the analytical store for analytics data science machine learning so these upstream are typically transactional databases right like uh you know we announced um lakebased this morning but similarly you have Postgress Oracle SQL server MySQL these sort of transactional databases you want to have efficient replication of these transactional data into your analytical store for downstream data science, machine learning and analytical workloads, right?

5:29 Change data capture overview

And typically you have it's a two-step process. One is there's an extraction process to get the data from the OOLOTP databases which is not the focus of this talk. For those of you guys who are interested in this space, we have LakeFlow connect which includes the extraction piece from these transactional databases. Then typically once you've done extraction you either throw them as pocket files into cloud storage systems or you for more real-time use cases you will stream the change feeds u directly into a message bus system like uh you know kafka kinesis pubsub azure event hub.
So the focus of this talk is once you've got all of these change fees extracted, how do you apply these changes efficiently and correctly into the analytical store uh for downstream uh systems to use that? So this is a simple example of a change feeds. It's got a unique ID. It'll give you all of these different changes in a particular column. It'll tell you the type of operations. Is that a insert? Is that a delete? Is that an update?

7:03 SCD types and data types

They're very important for change feeds because you know ordering is super important for change feed. It tells you the order of these changes being happening. It's critical for you to get the correct replication of the data downstream. So there's also a sequence number for many many cases. It's just a time stamp of when that happened um in the upstream OLTP database. And in change data fees capture there are two very popular sort of representation of how you want to replicate the data.
One is called std type one the other one is called type two. Type one is very straightforward. Essentially the result of std type one replication is whatever is in the in the target table reflects whatever is in the source table. So in this case, was in Seattle at sequence number one and then later on it's being updated into San Francisco at sequence number three. So the end result just shows the latest status of where is.
So that's type one. Type two is a bit more involved. So in certain cases for your downstream application, not only you want to know the latest status of what's the upstream table, but you want to capture all the changes along the way. So type two will be represented in such a way that you can see that from you know sequence one to three addis was in Seattle but at sequence three and onward is you know changes into San Francisco.
So it captures that entire change history for your downstream use cases. Now this is apply changes. It's an API as a part of the declarative lakeflow pipelines to apply these extract change feeds whether from a message bus or whether from a cloud storage system into the target table. You can do that in SD type one or type two. So there are a few things you can configuration configure that number one is a source where's your change fees whether it's pocket files or a message in Kinesis Kafka.

8:46 Apply changes API functionality

You'll give a key. So you have to tell us which column is your key. That's what we use to identify the particular rows to be updated downstream in the target table. Sequence by is super important column for you to tell us the order of the changes. Then ignore now which is a super important feature that's very hard to get right which is sometimes the change fees coming in without all the complete values for each one of these columns.
So you just tell maybe in the previous example you update the location of into San Francisco but the change fees might not have the name information in these cases in instead of sort of updating into null value you might want to grab the latest valid value from the previous update. So it's a very important and powerful feature that's very hard to get right and uh you can also specify delete and truncate event. So we will figure out how to delete the row or drop the entire table in the target.
You can do type one, type two and you can select a subset of the columns to be replicated. And in the case of type two, you can select a subset of the columns to track for the history. So it's a very simple API. This is a SQL example. These are the configurations you can do on it. We also have a Python interface for that. So that's number one. You have the change feeds. You apply the change feeds into downstream target table.
Then we have a second API called apply change from snapshot because so many times some upstream databases don't have change fees or you're not doing change fees capturing the way then people do a hourly or daily dump of a snapshot from upstream database tables. So this is the second API very similar but the difference is that the source is not a change feeds it's a snapshot of your upstream databases. So you just keep applying a sequence of snapshots.

10:35 Snapshot-based CDC

This API will figure out between these different snapshots what has been changed and similarly apply these changes into the downstream target tables. So the configurations are very similar to apply changes. The only difference is that some of these configurations will not be valid in the case of a snapshot. For example, um there won't be now there won't be deletes because you are giving us all these different snapshots.
We are figuring out the changes um along the way. So this is the interesting piece which I think you know might be of interest to you guys you know especially for many of you guys are not using apply changes today. So before there was apply changes you know CDC change feeds app you know is inherently a streaming use case. So what people were doing is they're using structure streaming engine and the merging to statement to do change data capture.

11:28 Handling pipeline pitfalls

So I'll give a very simple example going back to type one. we have these three change events. I want to apply downstream so that I know the you know the latest location of and. So to begin with it's you know it's very straightforward. This is a structure streaming job. Um you know the way you implement that is that as soon as you receive the change feeds from the streaming source you're going to write a logic you're going to look up the particular key uh downstream in the target table.
If you find a match you're going to update that row. If you don't find a match you're going to insert that row. So that's fairly straightforward but actually there's a lot of problems and edge cases this this part of logic sort of doesn't handle. Number one is that for you know for each of the data processing batch and binaries you might have you know more than one row of the same key arriving at the same time. So in this case there are two rows you need to do to update the particular ID for ID number one which is a key.
So you first of all you have to figure out okay in this case which one do I apply. So that's kind of straightforward. You just do a group by and max by the time stamp or the sequence number within the batch before you apply the changes to the downstream target. So that adds a little bit more complexity to your code but you know still fairly straightforward not a big deal. So we'll do that. So but that adds you know uh a few lines of code to your implementation in streaming.
Then the second one which is out of order I sort of teed it up a little bit for change feeds as you go through the intermediate layer of the cloud storage or a message bus it almost always will happen where things arrive out of the order. So in this particular case for example there was an update events for at time five and time three but the updates at time five could arrive before the updates for time three. So if you just blindly do the merge statement, you'll first update location to Los Angeles and then to San Francisco.
So the end state will be San Francisco. But in this particular case, the end state upstream is actually Los Angeles. So you have to handle the out of order. So how do we do that? We actually uh store a time stamp as we insert these updates into downstream. So every time we try to update we will compare what we have in the current change feeds to the last time stamp that's been updated in the table so that we'll figure out and reordering this stuff.
So in this case it'll correctly update to the latest location as opposed to the arriving order. So you know that adds a bit more complexity. You have to compare the time stamp or the sequence number with the target before you do the changes. So now 22 lines of code then there's deletes. So you know apart from insert update there's delete. So when you receive a delete event you need to know that how do I update the downstream to delete the row.

14:29 Managing deletes and zombies

So this is very straightforward just one line of code. But here's the real challenge which is when you combine deletion events with out of order arrival things becomes quite complicated. So in this particular case, for example, at time five, there's an update to change location into Los Angeles. Then at time six, there's a deletion events to delete that entire row for. But things could arrive out of the order. So at you, you first receive the deletion event.
So you're going to delete the row in the target table. So there's no longer ID1. There's no longer. Then after that you receive a record saying I need to update at its location into Los Angeles. So but there's there's no row to be updated downstream. That row no longer exist. So what do you do? You have two options. One is um let me just insert that row. But in this case you can tell that if you insert the row that becomes a zombie data because that row should have been deleted at time six.
So it won't be the correct results. Then option two is that you know that ID no long exists in the target table. Let me just drop it. In this case, drop it would have been the right implementation, the correct results. But what if actually there's another insert events that's arriving late that at time you know time stamp let's say number 5. 5 that hasn't arrived yet that actually will eventually put at its location at Los Angeles.
So if I just simply drop the data, I might have data loss. So it's one way or the other, you're going to get the wrong results. So what do we do with that? So the way we've solved this is that instead of doing deletion right away, we actually perform a soft delete uh in the target table and continue to track that record. So that later on when we have insert or update events showing up late arrival, we will compare with the time stamp on the deletion events to produce the correct results.
But then things gets a bit more complicated because you have the soft delete. Then on the query side you want to filter out these soft deleted events. So we actually create a view on top of the underlying table to give you the you know point in time correct results uh from the target of apply changes. Then we build a crown job to periodically clean up these datas um as time goes on. So then things becomes you know much more complicated with this uh you know out of order for deletion events and also update events.

17:09 Complex CDC scenarios

Then all of a sudden, you know, you have a lot more code and we have you have to create a view on top of it to filter out this the data that's soft deleted you're tracking for order and at the same time you need a crown job to clean up these data periodically and in reality this is the simplest case that I just described type one if you consider ignore now feature where not only you need to track the order of a particular row you actually have to track the order of a particular column because whenever you receive receive a null value for a particular column, you have to go look up the previous value record.
So the ordering system becomes infinitely more complex when we do that. And STCD type 2, it'll just, you know, it's way more way too more complicated. I just no way we can cover that um in this particular talk. So the way we build apply changes behind the scene, it's more than 2,000 lines of code actually. It's a simple API function that I just showed you guys and more than 10,000 lines of code to check out for all of these edge cases.
So essentially for everything I just described basically we turned you know a few thousand lines of code you know quite a lot more a testing code into a 10 lines of sort of declarative function for you to apply all of these changes behind the scenes. We're handling all of these ordering, tombstone, viewing, cleanup jobs under the hood for you. So, that's a quick overview. With that, I want to, you know, welcome and to talk about how Square's been using this functionality um at their use cases and company. , thank you.

18:41 Square business context

All right. Hello. Like, thanks, Ray. Um before we get started, just want to do a show of hands. How many people have heard of Square? Uh I'm glad we're in SF, so that makes a lot of sense. So, um most of you probably know Square as your neighbor neighborhood POS system. We're in coffee shops. We're in bars. We're in restaurants. Um but what many of you um don't know is actually Square provides some of the best banking products for small businesses out there.
Um, we provide credit cards, uh, lines of credit, loans, uh, checking, savings accounts, and I'll be giving two examples of, um, how we're using those banking products, as well as some of the things that Ry talked about, uh, in our in our day-to-day work. Um, so I'll I'll be providing some, uh, business context, uh, behind some of the use cases, tell you a little bit more about the team, and then I'm going to pass it to my engineering colleague, who's actually going to uh, talk about the juicy technical details behind the scenes.
So next slide. Um so a little bit more about our team. Um we're the financial data team. Uh essentially we have our primary customers are those that use banking data within square. So these are our other product managers that we work with. These are data scientist folks as well as ML engineers who use our data build underwriting models build prediction models and so on. Um at a very high level um internally our goals are create a single source of truth for our banking data so that we can disperse that banking data across square across all block uh so that we have a unified source of truth.
On the other side externally we make it easier for product teams to ser better serve our customers. So this is providing some of the best financial products best offers to our customers there is and very competitive on the market. Um now let's go to two examples. Um the first one I want to talk about is Square Loans. So many of you guys might have heard this. We're previously known as Square Capital. Uh but essentially we take a look at your OnSquare processing data and we provide working capital.

20:40 Loans and credit products

Um working capital is the lifeblood of small businesses out there. Um the genesis behind this product is really to help our businesses grow, help them make payroll, help them expand, and really help them succeed while they're on the Square platform. Um so we ran into an issue uh where um essentially to continue to ramp up this product to continue to make it better and more competitive for our sellers. Um we needed to gain more insights into their businesses.
Um as you may you guys may know uh many of the sellers uh they don't just work on Square. They might have a e-commerce store that they're on Shopify with. They might do B2B processing. They might do um you know something on the side. So just because we see your data on square doesn't mean that we know your entire business and as a on the flip side as a small business owner um you like to think of your business as a whole right it's not just a different commerce platforms that you're processing on but rather it's an entirety of your business so that's really the genesis of this problem and we had to we knew that we had to incorporate external revenue to our underwriting models so is going to talk a little bit more about how we're using plaid to do that as well as using data bricks Um but essentially it gives us a more holistic view into their entire business that what the seller cares about.
In addition to that uh some sellers aren't eligible to borrow from Square at all today. Uh many of them have given us feedback that the uh loans offer process the underwriting process feels a lot like a black box. They're not getting insights into why they are eligible or they're not eligible for a loan. And therefore we wanted to expand the eligibility of Square loans so that it gives more the sellers more control in their process.
They can link their external bank data to give us more information and more insights into their uh into their businesses and in turn we will provide some of the most competitive financial products out there. So that's really the genesis behind square loans. Um this will tie into a lot of themes behind um dduplicating data as well as incorporating external revenue. Something else that I want you guys to keep in mind is this is a a loan offer or a underwriting for a loan is really a decision at a single point in time.
Once we have made that decision, we can't go back and retract it. So therefore, the more data that we have, the more accurate it is, it'll help us provide the best offer out there. On the second example I wanted to talk a little bit more about is credit card underwriting as well as line management. Uh so this is one of another uh financial product that Square offers to our um our uh business customers. This is not unlike the credit cards that you and I have in our pockets today.
Um you know we provide them with an initial sizing uh and then but the I think the more important thing is unlike a loan a credit card line or a credit line of credit is something that you can pro retroactively adjust over time. So as we see more of your business volume, as you as we see your repayment behavior, we can adjust that credit line either smaller or larger over time. Um, and then this we'll talk about the some of the products that Ray had discussed in the previous slides, but essentially we wanted to very we know that different borrowers and different sellers have various needs.
Therefore, we wanted to adapt our products to the best of our abilities to provide for those needs. In addition to that, um, some sellers aren't super comfortable taking out loans. So, this is usually something that gets a seller comfortable. Everyone, you know, pretty much all use credit cards. Uh, and then they also have different day-to-day spending needs that varies from a loan to a different credit card. And therefore, um, it really helps us use this as an on-ramp to some of our other financial products, as well as continue to use this to gain a more holistic view of the seller's business.
So with that, you know, just wanted to provide a little bit more business context behind what will share more in details. Uh but I'll let him take over the rest. All right. Thanks. Um before I get started, I just want to give a reminder that for the next slides, DT is synonymous with LakeFlow declarative. We just heard about the rebranding along with all of you today at keynotes. So, uh, yep, the we're referring to that when we say DT.

24:55 Data mesh architecture

And getting started here, um, I wanted to start with a data mesh architecture diagram. So, if you're not familiar, a data mesh is really a set of practices for owning, publishing, and consuming data throughout an organization. And on the top left you can see we have producer microservices publishing data to Kafka topics. From there the data is persisted into S3 and we then consume the data in DT pipelines that are organized using a medallion architecture where you roughly have different layers corresponding to their own tiers of data quality.
In practice, this looks like the bronze layer containing raw unprocessed data, silver clean dduplicated data, and the gold layer aggregated or joined data. Oops. Um, so I'm going to be walking you through two use cases, two real life use cases. They will both directly correspond to each of the product slides that presented to you all and they live in the silver and gold layers of this data flow. So, uh the question I'm going to be trying to answer for these next few slides is how does data bricks DT pipelines specifically using the apply changes API help us implement this architecture?

26:37 Silver layer transactions

And the first use case I'm going to walk you through is called our silver layer bank transactions pipeline. And the problem statement is given a stream of bronze transaction data with many duplicates. We need to create a DT pipeline that exposes a dduplicated view of each merchants's bank transaction history. And at the bottom of the slide, you can see that's a snippet of our transaction event schema. It contains a bunch of identifier fields as well as not pictured just would be additional fields for attributes on the underlying transaction such as the amount or a description of the transaction.
Um, and I'm going to walk you through some slides just to illustrate what it looks like to dduplicate this data. So, we source the data from Plaid, which is an external banking API provider. They offer a product called the asset report from which we are able to pull merchants bank statements. And we've got our product teams performing this pulling an upfront 2 years uh asset pull and then on a monthly cadence they're pulling the last 45 days of assets.
And as you can see from that diagram, this necessarily creates a lot of overlapping regions. And that's by design. We want to make sure we don't miss any data, but as a result, we have to account for it and make sure we we dduplicate it before exposing it to our downstream consumers. And one other characteristic is that the transactions themselves are mutable. It's possible for an individual transaction to change over time as it's referenced in one asset report to a later asset report.
And it's mostly because of that characteristic of the transactions being mutable that we decided to dduplicate by retaining the earliest version of each transaction. So um just going back and forth one more time to illustrate that we're basically just clipping the transactions uh the front of each subsequent asset report because we already have collected the transactions from the previous report. And uh this is just one other example of how you can have transactions overlapping with one another.
In this case, a merchant linked their bank accounts uh linked the same bank accounts twice through different product flows at square. And we have multiple two-year asset pools that are mostly overlapping with one another. So in this case, we want to do the same thing. We want to just clip the majority of transactions from the beginning of that second two-year poll. So, we're able to implement this in a pretty straightforward manner using apply changes.
This is the DAG for our DT pipeline. The first two views are performing basic filtering and transformations before we call apply changes in the final node. We're using sedd type one with transaction ID as our unique key and we're sequencing by updated timestamp times negative1. That's a trick you can use with sequence by that uh normally if you were to sequence by the updated time stamp on its own. You would update the transactions in place using the apply changes API uh as the time stamp field increments.
But by using the negative time stamp value, we're just ignoring any updates that take place after the initial version of each transaction. So yes, once again, uh using apply changes makes this straightforward, which is great. I want to highlight this is kind of an atypical problem that we ran into. It's not that common that you're publishing up to two years of transaction data into the past and that that transaction data can change over time, but we're able to do it just in several views and an apply changes call.
And by using that trick where we sequence by the negative timestamp, we're able to prevent data drift and any other similar types of issues during back testing. And we've got this pipeline running in production. and it's processing a little over 20 million records per week. And one final benefit is that backfills are super easy. We can just point all our historical data into the same pipeline and by virtue of apply changes handling out of order records uh we don't have to do anything else.

31:28 Gold layer credit cards

So this takes us to our second example which is gold layer credit cards. And here the problem statement was given two streams of data, we needed to create a DT pipeline that exposes an up-to-date view of each credit card. And our first stream of data is credit card created events. These are emitted each time a credit card account is opened. They contain a number of identifier fields as well as attributes on the account at the time of opening.
And our second stream of data is credit card update events which are just emitted anytime an attribute changes on the underlying account. So there those are grouped into several types of updates. We have account status, line size, and account terms updates. And I think this is a really great example to share because this is more of a canonical use case. you have some entity whose state you're tracking over time and you have a stream of events that correspond to any change in state of that entity over time.
So this is a great use case for apply changes as well. And to implement this with apply changes, we can do that in three steps. The first of which is transforming credit card update events so they match the credit card created event schema. And for this example, we have a line size update event. We're basically just pulling that line size amount out to a top level field and then setting a bunch of values to null in the middle.
All the that's why owner token, product type, and credit offer ID are all null. Uh we're just setting all the other remaining fields on the created events as null. And we're able to do this because we're going to ignore null updates. Uh I'll get into a little more detail on that just to illustrate it in a few slides. And then our second step is just going to be appending the streams together. So we've got a single stream of credit card created and transformed credit card update events that all share the same schema.
And once we've done that, we can just call apply changes as well on that appended stream with account ID as our unique key. and we are sequencing by event time milliseconds which is just when the event occurred and we are ignoring null updates. So when we do all those together with apply changes were able to output the desired table and I just wanted to walk through a few slides to illustrate what the ignore null updates portion looks like.
For this example we have a single credit card created and update event. The created event sets all the attributes on the account at the time of opening like APR, line size, etc. And that update is just overwriting the line size from 10,000 to 15,000. All the other pretty much all the other fields on that event are set to null. So with ignore null updates, as you'd expect, we just ignore all those null fields and overwrite the line size to 15,000.
And this can just extend to any number of events. Uh this second example is just overriding APR from 1,00 to 1,200 basis points. So yeah, once again using DT apply uh pipeline with apply changes makes this pretty straightforward and it's really the ignore null updates which is doing a lot of the heavy lifting here by handling out of order records and determining which fields to set. I just wanted to share an example of what an alternative would look like without DT pipelines and apply changes.
So this is a Spark SQL query using merge into. And it's got a fair amount of the same logic as the DT pipeline we just constructed. It's unpacking the nested update fields and setting those as top level fields with a series of coales statements. And this works for the most part, but as soon as you run into out of order messages, you're going to have issues as mentioned earlier. And if for any reason an upstream uh producer is publishing messages out of order or you have to perform a backfill, then you're kind of out of luck with this approach.
You're going to need to reprocess the entire data set in the correct order. And one other downside of this approach is that it doesn't scale in the best way with respect to schema evolution. So in the case that you are adding any new account attributes as fields to your credit card created or updated events, you're going to have to update this query as well, which is just going to add a bit of overhead and is the kind of thing where you could easily imagine someone forgetting to update the query every time the schema changes.

35:58 CDC implementation benefits

So to recap, apply changes speeds up our development process and reduces maintenance overhead. We could see that was the case for two very different examples in the silver layer transactions and gold layer credit card pipelines. All of this is in service of us uh my team being able to put high-quality data sets into the hands of data science, ML and product team customers. And there are also benefits uh we didn't touch upon in these examples from Ray's portion of the talk such as out of order deletes or sedd type 2 that you could potentially benefit from depending on your use case.
So we definitely encourage you to check out DT pipelines with apply changes. Um yep we're going to bring it back to questions.

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.