Building Production-Grade SQL ETL on the Lakehouse

Summary

This video demonstrates how teams can build production-grade SQL ETL pipelines on Databricks Lakehouse using a declarative, SQL-first approach with Lakeflow Spark Declarative Pipelines, replacing brittle imperative pipelines and manual control tables with a simpler, more reliable paradigm.
Key capabilities showcased include Auto Loader for bronze-layer ingestion with automatic schema evolution, SCD Type 2 with auto-CDC that reduces implementation from roughly 150 lines to 10, incremental replace-where for eliminating merge overhead, and materialized views for the gold reporting layer.
The unified approach applies governance, quality controls, and AI-assisted authoring consistently across the pipeline, reducing operational overhead while delivering reliable, analytics-ready datasets at scale to BI, analytics, and AI workloads.

Building Production-Grade SQL ETL on the Lakehouse

ETL pipelines are often the most brittle part of the analytics stack — spread across tools, written in multiple languages, and hard to operate at scale. this video shows how teams build production-grade SQL ETL on Databricks Lakehouse using a unified, SQL-first approach. We’ll cover how to ingest, transform, and manage data using declarative pipelines, apply governance and quality controls consistently, author and optimize with AI, and deliver reliable datasets directly to analytics and downstream workloads. The focus is on simplifying architecture, reducing operational overhead, and enabling faster iteration without sacrificing correctness or scale.
Talk By: Mengfei Ren, Engineering Manager, Databricks ; Shanelle Roman, Senior Product Manager, Databricks ;

Chapters

FAQs

What is the declarative ETL paradigm and how does Lakeflow SDP implement it?

Declarative ETL means defining what data transformations should produce rather than specifying step-by-step imperative instructions for how to execute them, making pipelines more readable and easier to maintain. Lakeflow Spark Declarative Pipelines on Databricks lets teams write SQL-first definitions for streaming tables and materialized views that the engine automatically optimizes and executes. This shift eliminates the brittle control tables and manual orchestration logic common in traditional ETL approaches.

How much does auto-CDC simplify SCD Type 2 implementation on Databricks?

This video demonstrates that implementing SCD Type 2 with Lakeflow's auto-CDC feature reduces the required code from approximately 150 lines to just 10 lines of SQL. The engine automatically tracks change data capture events and maintains the full history needed for a slowly changing dimension without requiring custom logic. This dramatically lowers the barrier for teams that need historical tracking but lack deep ETL engineering expertise.

What role do materialized views play in a Databricks SQL ETL pipeline?

Materialized views in Databricks automatically precompute and cache query results so that downstream analytics and BI workloads get fast, consistent access to reporting-layer data without re-running expensive transformations on every query. They are refreshed incrementally by the Lakeflow engine, making them suitable for production reporting scenarios where both freshness and performance matter. This video positions materialized views at the gold layer of a medallion architecture serving analytics and AI use cases.

How does Auto Loader handle schema evolution during ingestion?

Auto Loader on Databricks automatically detects and accommodates new columns or changed data types as they appear in incoming files, without requiring manual pipeline changes or downtime. It incrementally processes only new data from cloud storage, making it efficient for the bronze ingestion layer of a medallion architecture. The combination of Auto Loader and declarative pipelines means teams can ingest evolving data sources reliably without writing custom schema migration code.

Full transcript

[00:09] Hello Bricksters. Welcome back. So excited to have you all join us. Welcome to all of you in the room. If you don't know what I'm doing, my name is Allison Baker. I'm a senior solutions architect and I'm here to present our next live speakers and there's a camera in the
[00:21] back. This is the only room where all of our sessions are being streamed live to our over 70,000 at-home viewers. We're super excited to have you all here. I will also want to give a quick shout-out to Aryan Holly across the street. I've
[00:33] only been over here in Moscone West the entire time. I'm going to come over later and say hi. But you guys are killing it. Been enjoying all of your amazing content. So super excited to see you in a little bit. Before we jump in with the next set of speakers, let's
[00:45] take a look at our final two sessions that we're going to have on day two at Summit. So coming up next, we'll have our latest innovations in Genie code and the future of a genetic data work followed by agent ready data governance
[00:58] Databricks on Databricks. So I'd like to present our next two speakers and we're going to move a little bit to the left of our workflow. So we've talked a lot about the consumption side and the agents, but
[01:12] let's talk about ETL. Because for a lot of customers, for a lot of teams, that's the most brittle part of the workflow. And these next two presenters are going to help us figure that out. So up first, we have Chanel Raman. She's a senior product manager. She's responsible for
[01:25] materialized views, streaming tables, and auto CDC. And we also have Mengfei Ren, an engineering manager who owns the engine that makes it all run and she still has time to read the stack traces from what
[01:37] I see. So I'm really excited. They're both local to San Francisco. I know Chanel's going to party tonight. I think we might be able to get Mengfei with us. I know she's going to put the kids to bed, but she said she's going to come back. So who else is going to party with the Chainsmokers?
[01:50] Come
[02:02] name is Chanelle. Welcome to the Data and AI Summit. So, just a quick forward-looking statement. Everything here for legal purposes is informational and subject to change. And a brief reminder,
[02:16] feedback from customers like you allow us to make this event great. You'll receive a survey at the end of the session. If you could fill it out, you can open it up on the Databricks events app as well as on the attendee portal.
[02:31] So, for today's talk, we'll be walking you through how to build production grade SQL ETL on the lakehouse. My name is Chanelle. I'm a PM here at Databricks. I focus on the SQL ETL products, including materialized views, streaming tables, and declarative
[02:44] pipelines. And with me here presenting, I have Mengfei Ren. She's an engineering manager that makes it all work. So, we'll start by walking you through a typical customer journey. So, we'll be
[02:56] walking you through the customer journey of Wander Bricks and talking through how they were able to modernize their SQL ETL, bringing data from a legacy data warehouse onto the lakehouse, and uh
[03:08] modernize their data for more real-time customer insights. So, Wander Bricks is a travel booking platform. They have flights, hotels, packages across 40-plus markets. And to give you a sense of the scale of their
[03:21] data, every booking, cancellation, and price change lands in their warehouse. They have 40 million plus bookings ingested per year. All of that is run through 200-plus nightly SQL ETL jobs. And they've had
[03:34] about 12 years worth of booking data stored in their legacy on-prem warehouse. And so, we'll be walking you through their journey today.
[03:47] And so, what does the data team at Wander Bricks do? They have four main tasks that they try to do all in SQL. First, they need land this data into the warehouse. So, they want to ingest booking and customer data. What this looks like behind the scenes
[03:59] is they want to land raw files from both their booking system and their CRM into their warehouse. Second, they want to build out their silver layer and maintain customer profiles. So, in particular, Wonder Brick is really interested in
[04:11] understanding how customers change over time. So, when someone goes from a prospect to a closed one prospect, as well as um whether a customer location changes. And so, to do that, they apply profile
[04:23] changes over time, keeping the full SCD type two history. Then, as they start to do analysis, they want to enrich their uh bookings data with profiles to understand who's booking their flights and understanding
[04:36] like who are the most valuable customers. So, to do this, they're joining bookings to customers data in order to build out their gold level reporting data sets. And then finally, to make these this data useful for their end data
[04:49] consumers, they want to actually take this data set, empower executive reporting, and dashboards, as well as downstream models. So, in order to do so, they will aggregate the enriched data and serve it fast and fresh.
[05:02] So, today, all of this is written in handwritten SQL ETL on their legacy warehouse. And so, we'll be walking you through their journey. And so, taking a look at why they did it the way they did it today, um like many
[05:15] folks, they chose SQL-based ETL for a variety of reasons. The first is is allows for faster development. SQL is purpose-built for for transforming data. It allows for better performance on the lakehouse. And third, it allows for
[05:27] greater collaboration. SQL is a language that anyone from business analysts all the way through data engineers can both understand and write. So, ETL is able to be self-serve, rather than siloed.
[05:43] And on Databricks lakehouse, all of their legacy ETL runs natively. So, when Wonder Brick was moving over onto Databricks SQL, all of the data warehouse primitives that they had are all native to Databricks SQL. So, their migration starts as a lift and shift
[05:55] rather than quite requiring like a 10-year rewrite. And so, these are just some of the primitives that Databricks SQL now supports today. Anything from SQL scripting with variables, if while control flows, all the way to stored
[06:07] procedures, which now support both definer and invoker rights in GA. Some of the other primitives that are supported on Databricks SQL include temporary tables, multi-statement transactions, recursive CTEs, and
[06:20] collations. And if you're interested in learning more about this, I believe Ken Martin is doing a session specifically on the types of SQL primitives that Databricks SQL supports. So, after this migration landed on Databricks SQL, they basically
[06:32] translated everything as a lift and shift. And so, to take a look at the architecture that they built, they basically built a very classic medallion architecture on Databricks SQL, starting from bronze to silver to gold, then
[06:46] finally a platinum reporting layer. So, on the bronze side, they basically ingested this data landing raw booking and customer updates in the warehouse. So, they have booking and customer staging table.
[06:58] In the silver layer, they basically clean up this data and kind of clean up the formatting. And so, they create clean and maintain a customer profile dimension's history, keeping track of customer changes over time with their
[07:10] customer dimension's table. And then in gold, they then enrich that with the fact table. And so, they enrich their bookings with profiles into a domain data set called bookings enriched.
[07:22] And then finally, they serve it up into their BI serving layer, aggregating it for the executive reporting dashboard in a data set called top customers that's ready for any executive to take a look at.
[07:37] However, as the business grew, Wonderbricks ran into some problems. What they realized is that the business logic for actually transforming this data was relatively simple. However, they realized that they were spending a lot of time on the operational maintenance and the plumbing work. They
[07:50] ran into problems such as, how do you keep track of which files have already been processed? How do you handle late arriving updates? What happens if uh records arrive out of order? And how are they keeping track of the refresh windows of if the nightly
[08:03] load fails? Similarly, how do they recover if a nightly load fails? Typically, the approach is just to truncate and reload, which requires like rewriting the entire data set, which is pretty costly. And what they realized is that the
[08:15] problem was not actually just the transformational logic. Instead, it was the operational maintenance and how long it actually took for them to ingest from their source data all the way to powering the reporting data set, leading
[08:27] to like a really long time to insight, really affecting that their ability to drive effective business outcomes. They wanted to drive a lot more real-time time to insights.
[08:39] And so, um, they decided to shift from kind of an imperative paradigm to a declarative paradigm. So, on the left-hand side, you can see a very imperative style of programming, where basically the user will manage the operational logic. So, in this example
[08:51] of how the user is calculating the total, they will actually say, "Here is a variable, and this is exactly how you calculate the total." Where you say, like, "Total equals total plus value." Then update, line by line by line.
[09:04] However, in a declarative paradigm, you simply describe what is the result that you want, and then Databricks will manage it for you. Rather than describing line by line, you simply say, "Select sum of value from source."
[09:16] You simply describe the outcome. Databricks will manage the state, change processing, and refreshes. This allows you to have a lot more, um, reliable code and process data a lot faster.
[09:30] And so, we'll be walking you through how Wunderbricks was able to modernize their SQL ETL architecture to really deliver production-grade outcomes in a lot faster um timelines.
[09:42] So, starting with the ingestion layer, like many folks, they started with copy into. This allowed them to basically copy data from CSV files into a landing table. Um so, here you can have an example of like what a very classic ingestion um
[09:55] workflow looks like. But, there's actually a lot of manual work that's required for this. First, they had to keep track of the load timestamps to make sure that they were processing data incrementally. They needed to handle schema evolution by hand. So, if the CSV had a new
[10:08] column, the table would break and they would need to manually alter it. Similarly, they had to log ETL metrics to separate processing tables to keep track of what's actually been processed, did it fail, how long did it take.
[10:20] And third fourth, they had to manage session variables in order to keep track of state. All of it's doable, but also quite manual.
[10:32] The modernized way to do it is to use autoloader. So, autoloader will keep track of um the state for you. It's as simple as saying create a refresh table, and then you can actually schedule it natively in the SQL DDL to say schedule every 1 hour, and
[10:44] then you can say select from and then the directory from which you're reading from. Autoloader has automatic state management. This means that it will keep track of what are the files that have already been loaded and only update the only read the
[10:58] net new files. Similarly, it comes with automatic schema evolution. So, you can say when there is a new column or type changes, do you want to quarantine that data or do you want to just keep reading? And then third, it comes with managed
[11:11] observability metrics. So, you don't need to actually have a separate ETL process just for observability. Instead, it comes natively within the Databricks system.
[11:25] So, moving on to the silver layer uh and building the dimensions table. Wandering wants to keep track of customer profiles over time. In order to do so, it created a very classic three-stage process. They first started by copying it into staging. Then they keep track of only the new
[11:39] customers that have been ingested and the customers that have been updated, and they move that into like an integration view. Then finally, they take that updated data set and merge it into the final view. So, it becomes a three-step
[11:52] process with a lot of extra processing and a lot of extra time. And so, in a very classic data warehouse pattern, there's a lot of manual work that's required again. You have to again keep track of load timestamps and manage
[12:04] incremental state manually. You have to handle schema evolution and log ETL metrics. And the way you do this is you have session variables, temporary views, complex CTEs, and window functions.
[12:16] So, to take a look at more detail what this actually looks like, they started by basically pulling the data from the staging table into this integration view. And to do so, it's actually a lot of gnarly code. Um
[12:28] basically, Wonderbricks was building the version history themselves. They first had to find the existing data that's already in the dimensions table by basically finding the current version. Then they needed to have the newly changed rows on top.
[12:41] Then they need to dedupe it and find, okay, what are the customers that actually haven't changed since that not that much has happened with them. And then finally, they need to close the prior version to see, okay, this is the last time I've updated this data set.
[12:53] And so, in order to do so, they have to create two different views, four CTEs. It's very fragile. And once they do all this code, they then have to take that updated data set
[13:05] and merge it into their final table. So, all in all, it's about 150 lines of cold code on the positive side. Um all just for one dimensions table. And if you think about bringing creating a classic star schema, there may be other
[13:18] dimensions table that you're building. It's a lot of code to maintain and it's really fragile. And so, there's a lot of edge cases that Wonderbricks ran into. And so, we're excited to announce that we now support Auto CDC. This is a
[13:31] declarative API that's really meant to simplify the most basic and most difficult of data engineering tasks, building like slowly changing dimensions table. This is a popular API that's already available in Spark declarative
[13:44] pipelines today, but we're now excited to announce that in GA, it's now natively available directly in your lakehouse. So, you can run it from the SQL editor and from your notebooks to connect it to warehouse compute. So, it
[13:57] basically takes that process that was 150 lines and collapses it down to about 10 lines of code. You simply say create streaming table, flow Auto CDC from your source table, and then you specify the key on which
[14:10] you would like to merge on. So, in this case customer ID, sequence by and which column determines when rows are in order. So, whenever the customer was last updated, and then whether you want to store it as SCD type one, which means
[14:23] only keep track of the current state, or SCD type two, keep track of the full profile history. And so, the benefit of this declarative paradigm is it allows you to natively handle a lot of the stuff that was really difficult for Databricks to deal
[14:35] with previously. So, if there are multiple updates for a customer in a single batch, typically that requires a lot of extra work, it's natively handled with Auto CDC. Similarly, we have correctness guarantees for handling out-of-order
[14:47] processing. Deletes get automatically processed with a simple apply as delete when, then you specify how a delete is specified. So, no need to manually tombstone data. And then finally,
[14:59] doing SCD type one or SCD type two is simply a one-line change. And so, taking a look at the pain points that we walked through before, out-of-order events can now be specified with a simple sequence by column name.
[15:13] Deletes previously required hand-coded predicates. Now, you simply say apply as delete when. SCD type two history, you simply say stored as SCD type two or type one. No need to do that in our latest CTEs,
[15:26] merges, checksums, etc. And then finally, error recovery. Um rather than having to rerun the whole job or backfill data manually by like picking data through picking through data,
[15:38] instead Auto CDC comes with manual checkpointing. So, we handle the checkpointing and we'll automatically pick up where we left off or you can simply rewind and replay the data.
[15:57] And so, now going to the gold layer. Um so, now um that Wonderbricks has built their profiles data, then they wanted to think about like, "Okay, how can I enrich my bookings data with my profile information?" So, the very classic approach is to do like a batch
[16:09] overwrite, um whether it's insert overwrite or replace where. And this typically will basically rewrite everything within a given window. So, let's say like for the last 7 days, let me update my bookings orders.
[16:21] And so, the classic approach is to have a staging table with a manual watermark variable and an overwrite job, all wired up by hand. What this looks like is you have a create or replace table with bookings enriched and you keep track of all the
[16:34] date data that's been updated from bookings since the last time I processed this data. Then, I update my final data set by just adding everything in, overriding everything that was previously written
[16:47] since the last time I ran this job. And so, this will typically rewrite the entire matched window even if the data in the target table hasn't actually changed, which is both both slower and more expensive for you all.
[17:05] And so, we're excited to announce in beta that we now have a new primitive called incremental replace where. This allows you to basically smartly incrementalize and only refresh the rows that have changed while specifying the exact same information that you had
[17:17] before. Um you can basically say create a refresh bookings enriched. Again, you can natively schedule in SQL DDL to say schedule every one day, or you can schedule it through a job.
[17:29] And then you simply specify the window over which you would like to refresh this data. So replace where the last 7 days. Um you can say last 30 days, last hour, etc. And then the query that you would like to write into the table.
[17:41] What this does is it basically gives Wonderbricks a scheduled refresh natively in the DDL. It guarantees that you they'll never fully rewrite the table. You can scope over what the period over which you would like to overwrite.
[17:53] And it comes with the benefit of automatic incrementalization. So our underlying um AI optimizer enzyme will basically identify here are the rows in the source table that have actually changed and only update those rows in
[18:06] the target table, rather than just naively overwriting everything, allowing Wonderbricks to deliver a lot faster time to insights because we're not doing extra work that wasn't actually necessary.
[18:22] And it's more than just a faster refresh. Some of the benefits of incremental replace where is it allows Wonderbricks to first get a performance boost. It's both cheaper and faster than traditional replace where or batch overwrite workloads because enzyme will
[18:34] only process what's changed. It also provides a very a very um advanced way to do targeted corrections. You can basically correct specific rows or columns without having to rewrite an entire table, truncating
[18:47] and reloading, which can be pretty expensive. And third, it works like a normal table where it honors arbitrary DMLs. So if a data engineer wants to do any targeted corrections, they can do inserts, updates, and deletes directly on the
[18:59] table. And we it comes with the native backfill predicate. So rather than having to do backfills with truncates and reloads or manually, you can simply say refresh um streaming
[19:11] table where and then the the window over which you would like to backfill the data. So, you can update a table every 7 days, realize that something went wrong, and then say, "Replace where the last year's worth of data." Then keep
[19:23] processing on from there. And so, taking a look at what this actually looks like behind the scenes, here's what a traditional, um, batch
[19:35] override workload would look like. Um, the customer will basically rewrite everything on May 2nd and May 3rd, even if nothing's actually changed. However, with incremental replace where powered by enzyme, we will basically
[19:47] identify that actually the only piece of data that actually changed in the customer source table is May 2nd at 2:00 a.m. And we'll only operate uh, update that data. Everything else stays exactly the same.
[20:03] And so, the results speak for themselves. Um, incremental replace where is 3.4 times faster and 2.5 times cheaper than a traditional batch override workload on an incremental TPC-DI benchmark. So,
[20:16] this allows Wonder Bricks to really deliver customer results in a much faster way for a much cheaper cost.
[20:28] And so then, moving on to the uh, reporting layer. Now that Wonder Bricks has really built these domain data sets, they want to power these data sets to um, help their executives understand, "Who are my top customers? Where are the growth areas in both like Power BI
[20:40] dashboards as well as analytical reporting?" In order to do so, they wanted to build reporting data sets with either views or metric views. Um, and so, they basically took their booking enriched data and then built a
[20:52] reporting view called top customers. Um, this is basically a view that will rerun the whole query on every single dashboard load. And so, it comes with a couple of downsides. The
[21:04] first is, every single time that the customer queries the view or the metric view, it's recomputing that entire query, which gets pretty expensive. And as a result, cost will scale linearly with every single data consumer. So, if
[21:17] the executive is refor- refreshing the dashboard every single minute, every single minute we're recalculating that data. And there's no way to actually process only what's changed. And so, what Wonder Bricks did is they
[21:30] decided to materialize the reporting layer. And so, some of the benefits of this is that first, it allows them to accelerate their BI queries. This allows that basically what it does is we will precompute the results into a materialized view, which makes the
[21:43] dashboard dramatically faster than just querying the base tables. And so, you simply do like create or refresh materialized view, trigger on update, which means refresh as soon as the base tables have actually updated,
[21:55] and then the query that you had before. And this is supported both in materialized views in SQL, as well as metric views. So, metric views also support materializing that exact same thing, allowing you to deliver better
[22:07] performance. And not only does it just accelerate your BI queries, it also comes with the power of enzyme to incrementally refresh. So, our AI-powered optimizer will process only the changed data,
[22:19] avoiding expensive recomputes. And it's fully governed. So, all the customer needs to do is write the select, we will handle the refresh, and it's an object in Unity Catalog with all the lineage and access control that you
[22:31] would have with a normal view. And so, now coming to operational modernization. Now that Wonder Bricks has actually taken a look at how they can opera- uh modernize their ETL
[22:44] processes, they wanted to look at how they can actually uh manage their ETL workloads more scalably. How can they measure that they're not waking up at like 3:00 a.m. when there's a bug? Um and so,
[22:56] what's coming soon is that we're introducing a new primitive called a flow. Um this is basically a named staple query that's stored in Unity Catalog that runs repeatedly repeatedly to keep
[23:08] a table up-to-date. You can think of it as a very similar to like a function or a stored procedure. Um, there's a variety of different types of flows including append flows, auto CGC, and incremental replace where.
[23:20] And some of the benefits of this is because we keep track of the state, it's incremental by default. The customer doesn't need to handwrite incrementalization logic. Instead, we keep track of the offset and query state
[23:32] to make sure that we only process what's changed. No handwritten incremental logic. It also has managed checkpoints. So, it supports holistic table rewind and robust disaster recovery. So, you can
[23:44] kind of rewind to back to a day ago and then pick up where you left off. You can refresh these on a design schedule or on demand. So, you can say refresh this flow every single day or
[23:56] just run it as a one-time backfill and it will always pick up where it left off. And it comes with its own page in Unity Catalog with its definitions, schedule, last run, all in one place for you to govern. And so, it's one declarative ETL
[24:09] primitive that's supported on all Databricks surfaces. Notebooks, the SQL editor, as well as declarative pipelines, and serverless notebooks.
[24:21] And all of this writes to the tables that you already have. So, flow flows will target real Delta tables as well as managed Iceberg tables. So, it's not actually a separate pipeline. It'll write directly into your Unity Catalog tables. So, what this means is that it allows
[24:34] Databricks to modernize in place. Rather than having to recreate a table when they wanted to modernize, instead they can keep their existing tables, grants, and downstream consumers. All they needed to do is basically swap out the jobs and then continue updating the
[24:47] table from there. If anyone's familiar with declarative pipelines, streaming tables will basically just become normal Delta tables with all of the um existing grants, governance features, etc. And
[24:59] so, there'll no longer be like special case tables. It'll just be like a normal part of the Databricks ecosystem. And so, now to walk us through how this actually looks like, Mengfei will take us through a demo. >> Thank you, Chanel. Uh hi everyone. My
[25:12] name is Mengfei. I'm the engineering manager from Databricks. Chanel just walked you through uh how Wonder Brick modernized their ETL at a high level. So, what I like to do now is
[25:24] to make that journey a little bit more concrete by showing you one ETL pattern that they modernized. It is a single pattern in their entire pipeline.
[25:36] Which is to move their booking data from the source table into their bronze layer table. I believe it's a very common pattern. Probably most of you have running in
[25:48] production today. So, let's first take a look at how Wonder Brick originally built their um pipeline.
[26:03] All right. So, here in the catalog explorer, you can tell they have a booking source table. This is their source table and right now it had five rows of booking data. They have this booking stage table,
[26:17] which is their bronze layer table. So, remember the business requirement is fairly simple, right? All we need to do
[26:31] is to find all the newly arrived booking data and append them to the bronze layer table. But, you might also notice that we have a third table named ETL control table.
[26:47] Business never asked for it. It only exist because we need someone to remember where has been what has been processed so far. Right? So, every successful run need to
[27:00] update watermark, so the next run knows where to pick up. If we look at this table, we already have a initial state insert into it. So, you can see the last load
[27:13] timestamp for the data and the last job run timestamp here. Cool. So, now let's take a look at their Lexi SQL script.
[27:29] Um from the initial glance, you might probably and easily tell that is a fairly long script. If I scroll down, you might tell this is
[27:41] lines of code. Right? So, the business logic is very simple. The complexity all comes from the operational plumbing logic. So, if we zoom in, we need to pull the
[27:54] watermark from the control table. Right? We need to identify the processing range from the source table and then insert all the new data into our target table. And eventually, we need to update the
[28:08] watermark into the control table. And of course, in real life, you need orchestration job on top of the script, so the script can be run on a schedule
[28:20] basis. For the demo purposes, I didn't create a job and we will just show you by manually run this script.
[28:35] And it's just a quick recap, right? Remember our source table, we have five rows of booking data. Our bronze layer table, it's empty now. So, if we hit run,
[28:52] uh it finishes, let's go back to the table overview page. And if we look at the booking stage table, you can tell now five new rows got
[29:04] inserted into this table. And if we look at this ETL control table, you might tell our watermark got updated.
[29:16] So far, so good. Um however, one that breaks they doesn't they don't want you update or maintain this watermark. So they are actually wondering is that even possible for them
[29:30] just to focus on their business requirement while removing all the operational logic? And the answer is, of course, yes. So that's what I will show you the next.
[29:48] Cool. So um for any migration or modernization you want to do, the very first thing you should do is definitely to pause your job, right? So your legacy SQL script won't actually accidentally insert new
[30:00] uh arriving data to your bronze layer table. And for the demo purposes, let's simulate a case where I already have the insert command ready. Uh let's try to
[30:12] make right uh five new rows got inserted into our booking source table. And as we run, you can tell the booking source table now in total have 10 rows
[30:24] of data. And just to recap, right? Right now our booking stage table, because we paused the job, it remains five rows.
[30:40] So um after we paused the job, the next thing you need to do is you need to figure out what's the legacy um stop at, right? So, basically, we need to figure out where we need to pick up. So, the way you do that is
[30:52] um you need to go to the ETL control table to figure out the last run timestamp, and then run the describe history on your source table to identify which delta table version we should
[31:04] start up with. Um now, let me show you the model I see we tell. You can tell this is 14 lines of code compared to previously 58 lines of code.
[31:17] Right? So, all we do is we create a flow called insert table flow. We insert it into our bronze layer table. We want this flow to be scheduled on a daily basis, and all this flow does is to
[31:31] identify all the newly arrived booking data from the source table starting from is delta table version one.
[31:44] So, no control tables, no watermark you need to maintain. And of course, you also don't need to manually create a job to schedule it. Right? We simply express the business intent we want to execute. This is the
[31:56] beauty of declarity. Cool. So, now let me uh run the script to show you how it will be look like. Remember um
[32:08] our source table now has 10 rows, our bronze layer has five rows. Uh as it finishes, let's do the count on the table.
[32:22] If we count on the booking stage table, which is our bronze layer, and no surprise, it shows 10 rows now. Cool. Now, if we zoom out to look at the table overview uh page, you can tell our
[32:36] bronze layer table now this insert booking flow is attached to this table. If we click into it, it takes you to our flow overview page. This is the place you can tell the
[32:50] definition of the flow. It has actual information, for example, the flow type. And in this case, this flow is called append. And you can see how we schedule it. Of course, the modernization journey
[33:03] doesn't stop here, right? So, let's just zoom back um zoom out to quickly show you um the silver layer and golden layer, how the flow looks like. Right. So, this is auto CDC flow that
[33:15] Wunderbricks built um and they use it to maintain their customer dimension table. And they choose, you know, you can choose between SCD type one or type two.
[33:28] It has a flow type auto CDC. The same thing happened for their replace flow uh where flow, where they can keep their gold layer, the booking enrichment table, synchronized whenever they update
[33:41] their customer information. >>
[33:54] their business logic. All they did is to stop managing the operational overhead and hand it over to our platform. And before I wrap up this demo, um
[34:07] I want to mention one thing. Right. So, this entire demo, because we just migrate one single step in the pipeline, so we did everything, we built everything inside Databricks SQL. However, if you're considering maybe
[34:21] modernize a broader pipelines, lake flow uh Spark declarative pipeline is another option for you, which has the exact same declarative concept. Um Spark declarative pipeline, in short,
[34:34] SCD SDP. Uh, it provides additional capabilities such as pipeline orchestration, uh, graph, visualization like multi-file,
[34:46] multi-folder management, and data quality management in to help you to manage your entire pipeline life cycle. So, it doesn't matter if you choose to
[34:58] write it inside DBC code, sorry, data bricks code or inside SDP, the uh, your programming model remains the same. With that, I'll hand it back to Chanel.
[35:11] >> Thanks so much, Mantha. So, then to quickly recap, if there's three things you want to take home, the first is auto CDC dramatically simplifies the process of keeping your slowly changing dimensions model
[35:24] up-to-date or any place that you would use a merge. You can basically take 150 lines of SCD type two logic, very brittle, all the way down to 10 lines of SQL, fully GA.
[35:36] Incremental replace where is a much faster primitive for doing traditional batch overrides. So, anywhere where you're doing a batch override, we'd recommend that you give incremental replace where try. It's natively available inside your lake house today.
[35:49] Um, it allows you to basically update only a slice of data that's changed in the most optimized way possible. Then finally, all of this writes to your existing Delta tables. This allows you to modernize in place with the same
[36:01] Unity Catalog tables, grants, and consumers. All you need to do is swap out the job whether that rather than having to do a whole scale modernization process that requires recreating tables.
[36:13] And so, with that, I'll open it up for questions. Thank you so much.

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.