The Write Way: Building Bi-Directional Data Pipelines in the Lakehouse

Summary

Traditional lakehouses are designed for reading, not writing back, forcing business users into workarounds like Excel or external databases that create data silos, break lineage, and produce multiple conflicting versions of datasets.
This video demonstrates a full writeback loop where Unity Catalog syncs to PostgreSQL via Lakebase for operational speed, a Dash-based Databricks App provides an intuitive editing interface, and Lakeflow jobs sync changes back to the lakehouse every five minutes.
The architecture supports form-based data entry, table editing, and data quality workflows within a Bronze-Silver-Gold medallion structure, with history tables providing a complete audit trail of all edits.

The Write Way: Building Bi-Directional Data Pipelines in the Lakehouse

Ever wondered how to bridge the gap between analytical data and operational workflows without breaking your lakehouse architecture? What if business users could edit data directly in a web app while maintaining a single source of truth? Traditional lakehouses excel at analytics but struggle with user-driven updates, often creating silos or complex workarounds. There’s a better way—in this video we’ll cover a full writeback loop where lakehouse (Unity Catalog) syncs to PostgreSQL (Lakebase) for operational speed, a Databricks app offers an intuitive editing interface and Lakeflow jobs sync changes back every 5 minutes. The lakehouse stays the source of truth while users get flexibility. We’ll explore scenarios including form-based data entry, table editing and data quality workflows, all built with Dash and Databricks Asset Bundles. By the end, you'll have a clear blueprint for building bi-directional data flows that empower users without sacrificing governance or reliability.
Talk By: Falek Miah, Chief Architect, Advancing Analytics ;

Chapters

FAQs

How can business users edit data in a Databricks lakehouse?

This video shows a writeback architecture where a Dash-based Databricks App gives users an intuitive editing interface for form-based entry, table editing, and data quality workflows. Changes made in the app sync back to the lakehouse through Lakeflow jobs running every five minutes, keeping the lakehouse as the single source of truth.

What is the role of Lakebase in a writeback architecture?

Lakebase is a PostgreSQL-compatible relational database on Databricks that serves as the operational layer for data requiring fast read/write speed. In this writeback pattern, Unity Catalog syncs data to Lakebase, the app writes user edits there, and Lakeflow jobs propagate changes back to the lakehouse.

How do you maintain data lineage with bi-directional pipelines?

The architecture maintains lineage by keeping the Bronze-Silver-Gold medallion structure intact, with history tables providing a full audit trail of all user edits. Lakeflow's visual DAG provides visibility into pipeline execution and sync operations across systems.

What deployment approach is used for the writeback pipeline?

Databricks Asset Bundles (DABs) package the entire solution — including the Databricks App, Lakeflow jobs, and Unity Catalog objects — for repeatable, governed deployment. This avoids the need for custom infrastructure while enabling near-real-time synchronization between the app and the lakehouse.

Full transcript

[00:07] Thank you for attending this session on how to bridge the gap between analytical data and operational workflows. So just before we start, who am I? I'm Fick. I'm a chief architect at Advanced Analytics who is a data and AI consultancy in the
[00:21] UK. Um and I've been in data and AI and analytics for nearly 20 odd years. Um and I've got extensive experience on building uh data engineering and analytical solutions uh and now AI
[00:35] solutions for customers all all around the world. Uh I'm a data bricks champion and I work closely with Microsoft as well um and other other partners. So as the session says, bridging the gap
[00:49] between analytical data and operational data can be a tricky activity. And why is it so hard? And then why does most teams end up coming up with a
[01:01] workaround? Well, the reality is our lakeouses never well never were built to write back into them. it was always there to be read and then but most of our business users
[01:15] want to be able to um edit that data, correct it, um maybe even approve that data and they come up with these workarounds, right? They either build an application for it, um take it to Excel
[01:29] because we all love Excel, right? Um and then things like external databases, access databases, maybe a SQL server somewhere
[01:43] And what happens with that is we start having these data silos, right? We get these multiple version of data sets. We get um a chaos of workarounds. No single
[01:56] version of truth. Uh we're breaking lineage. no longer have that lineage in our lakehouse and we create additional work for our data teams. The lakehouse was never really designed to say no to any of this stuff. It just
[02:10] wasn't around until now. All right. So now we have this whole suite of things that we can do in data bricks. So from unity catalog where we can govern the data lake base where we can um have a
[02:23] relational database for that operational speed that we need
[02:36] and even data bricks um asset bundles or declarative automation bundles now um to package it all up. Okay. So in this session I'm going to run through all of these and I'm going to go through a
[02:48] scenario where we've had to do this in the past. So just going through some of the kind of core components that we have. So if you haven't used data bricks before you need to catalog is that data and AI um
[03:02] solution for kind of bringing all of the assets and metadata that is um that is all going to be held in one location. And it gives us a lots of different
[03:15] things that we can do with that. Okay. So we can have a centralized access control, right? For our files, our models, our tables, it doesn't matter. We have a a one way to do everything. We
[03:27] can use service principles and guide them to which tables that they they have access to. We can discover the data. We can also use things like Genie on top of that. We can then build in data quality
[03:41] and profiling on those tables. We can see the end to-end lineage, see where the data is coming from, where it's going to. And then we got lakebase, which is what I want to talk about mainly is what lakebase allows us to do. So lakebase is
[03:56] that fully managed serverless post database. And it's there to kind of help us take the transactional data, apps, lookups, things that we can't traditionally do in
[04:08] an analytical warehouse and and build that into the process. So it's a managed process database, meaning we don't have to mess about with any patching, any cluster setups. It's
[04:21] all handled for us through serverless.
[04:34] thing that's great is this whole compute and storage separation right so traditionally when we have a post database we've always had this tied together storage and compute so
[04:47] therefore if you if you ever um scaled one you had to scale the other all right so you're paying the cost even though if you didn't need one and so latebase breaks that all right so it means that because of that separation
[04:59] we're able to increase our capacity, increase our our compute without impacting the storage means we basically only need to pay for that compute. And then we can also register it with
[05:12] Unity catalog. So therefore, we've got a centralized again centralized governance. We got the same governance model as we did for the rest of our lakehouse. We also have this autosync which is
[05:24] another great feature that basically means that within a simple one click we can synchronize and move our lakecast tables into postgress. So let's think about it as lakebas is
[05:38] our operational layer. So with lakebase comes a couple of different flavors. All right. So we've got the provisioned which is the old flavor. um it's no longer something that you can you can set up.
[05:52] And then we've got autoscaling which is the the newer one that came out earlier this year and it's the latest version is the default version and it's the one that you would spin up if you've got it.
[06:05] and these are some of them. So you can scale because of that separation you can scale your compute really well and you can scale it all to down to zero.
[06:17] >>
[06:29] great. So you're not always switching it on and leaving it. You got this feature of branching and we heard in the keynote about branching where you can utilize that for things like different agents, but also it's beyond that. Okay, so if
[06:42] you've used git, git branching is similar to that but for databases. So it's where we can where we can have safe development using similar data within seconds provision the our production data for our um for
[06:57] our developers to go and build a new application feature right we'll have multiple versions of that and then instant restore. So without impacting any of the data losses we can basically restore to a certain point in
[07:11] time and then we've got go back to sync syncing our tables. What we can do is there are different options to sync our tables our our Unity catalog tables to
[07:24] our lakebased and this all depends on our needs on our freshness and our requirements. So we've got the the three. So we got snapshot, trigger, and continuous. So snapshot, as
[07:37] it says, we take a a point in time picture of it. It doesn't change very often. It's cheap and it's cheerful. It's great. We got trigger. So that might happen after an ETL process or a
[07:49] known event, something that will trigger it. And that's a common one that we'd all be using. And then we've got continuous, which is near real time, right? So we will we'll do it every 1 minute, every 10 minutes or whatever we
[08:03] think near near real time means but that can be expensive. So be very careful with that one. Now whichever choice you you choose it it will automatically create uh a lakeflow job
[08:17] behind the scenes for you. All right. So when you're synchronizing those tables you will basically create a lake uh lakeflow job. So what is a lakeflow job? Electro job is a way that we can process
[08:30] our data, our workloads, our jobs. We can have multiple tasks there. We can schedule it. It can be part of another flow, another larger workflow. It's serverless.
[08:43] So again, we don't have to worry about these compute and any additional setups. It can handle complex workflows. So it can handle things like branching, if else rules, so looping,
[08:56] um, retries, dependencies, and we get this visual DAG as well to show us all of the components, all of the tasks, the dependencies, and then it has built-in monitoring and alerting.
[09:09] Um, we can also have this new lovely GA lakeflow designer which we can build an a no code um a no code pipeline. So we
[09:22] can by using genie we can tell it build me a pipeline to move X to Y and it will do it and it's great. And then we've got data bricks apps. So data bricks apps is basically going to
[09:35] allow us to build web apps natively inside data bricks. There's no additional servers. There's no additional um security or anything we need for that. No infrastructure costs. It's serverless again. So again, it's
[09:48] nice and easy to set up. Uh the apps support things like Python, Node.js, so fast APIs, React, Streamlit, and it natively connects to all our data
[10:01] bricks applications. So things like the lake base things like unity catalog SQL server and it has built-in security which is very similar to what we're already using. So, OOTH
[10:14] uh C uh service principles that we've already got defined and we can use that to build these um these UIs for our business teams and we can keep control
[10:27] of that as technical engineers. Where are we? And then we've got declarative automation bundles or otherwise known as data access bundles
[10:39] and I'll just call them bundles. um or dabs and this is basically an infrastructure code solution that for data bricks projects. So we can basically package everything up and we
[10:51] can deploy it and it gives us a heaps amount of kind of benefits. I've gone one extra there you are. Um so we can basically
[11:03] pro uh um we can define one definition and deploy it multiple times in our dev test prod. Right? Right? We can package it all up as one one set of um code that has all our source files, our resources,
[11:17] our configurations.
[11:30] And everything I've been talking about so far can be added into that uh bundle. So we can have our unity mechanical schemas. We can have our lake uh lakebased cataloges and databases. We can configure our uh our sync tables,
[11:43] our jobs and even how we want that app to be built. So that was just a bit of an background to all those tools and now I'm going to talk to you about an existing scenario I had recently. What had prompted me to to
[11:57] write this uh uh this workshop or this session. So I had a client and um they had various vendors and they would send them loads of data and some of that data
[12:09] would be really good. Some of it would be incomplete, some of it be incorrect, but the vendors wouldn't necessarily be able to change that. They didn't want to. All right? Whatever reasons there were. So that meant that data didn't work. No, that data would go to our
[12:23] downstream systems and our application, our reports would be wrong. um the business business would be making decisions on bad data. So it's not really an option. So we get some data
[12:35] stewards in, we get them to fix all the data quality, right? They look at what's come in, they build a custom application um and um they push that data back in and now they've got clean data and good
[12:47] data. Excellent. But they've created a problem here. They've now created this solution that doesn't fit with the rest of our application. So it might be an app services a new app services that they've
[12:59] used. Um so they need to figure out how to write that back or how we get lakehouse uh to talk to it and how do we talk it back or they've gone to a relational database um and that adds
[13:12] some additional complexity. our IT team now has to be able to manage that um and monitor it and that creates their them as a bottleneck
[13:24] or gives them additional work to do. So how could we build something like this in data bricks? Well, everything we've kind of run through we could use. So we can use our unity catalog to be our in in a medallion architecture right
[13:37] to be our single version of truth holds our historic data a version analytical data right and then we can push that data or those tables into lakebase.
[13:49] So now we have that operational layer. So now we've got fast table tables in there for fast read and writes.
[14:02] table at a time would take too long, right? It wouldn't be very efficient. We would have to go into every single table and do it. So, we'd build a pattern to do it, right? So, we would build a delta to uh Postgress pattern which is
[14:14] basically using the rest APIs or SDK to basically say, well, take me all these tables and and push them into into Postgress. That could be part of my end of my ETL process so I can create a job for it.
[14:27] And that's what we're going to do. And then once we've got the data in post on top of that we can put an app and we can build an app and we can browse we
[14:39] can validate we can make some changes basically let the users do what they need to in there and we can build that using things like fast APIs dash react whatever the business allows us to I'm
[14:51] going to use fast API because it's a great fit. It's Python and I know Python I don't know anything else. Um and then it will also use databicks orth um orth because that's what data bricks uses to
[15:04] login. So perfect I don't need to do any external hosting or separate login or authentication setups. Everything lives inside data bricks
[15:19] and then we go back to lakebase. So once we've made our amendments, our changes to our in our app, we can push that back into lakebase and lakebase will now hold that current data, right? The current state of the data. Now the problem here
[15:31] the different options is lakebase will hold the current state meaning it will be it won't have the history. It will just replace it and it will override what we've got. And I don't want to override it. I want to be reconciling it
[15:43] because I've got mistakes, I've got um errors or I've got missing data. I want to fix it. So I don't want it to necessarily override everything. So there's different patterns here where I can merge back into my analytical um
[15:57] layer and we'll go through a couple of them and how we can do it. So there's different approaches to it and this is just one method. I'm going to write back using uh another
[16:09] pattern another notebook where I'm going to go this time round from postgress to delta meaning I'm going to pick it up on a certain uh field on certain ids and
[16:21] I'm going to push that back and I will um I'll create that and then after after I've got it back into my lakehouse I will then continue with my gold pattern and basically use my new clean table
[16:34] that I've got called corrections Okay.
[16:48] declarative whatever it's called dabs. All right, I'm going to have to do a switch over.
[17:07] Okay. Right.
[17:20] okay. So, we got three pipelines. So, we're going to run three quickly. Okay. So, we've got um a lakecast one, Delta to Postgress, and Postgress to Delta. So, if we look at the first one, it's a simple pipeline where I'm going to go
[17:32] from my bronze, silver, um, gold, and it's going to in initially set up these these little cataloges for me with some tables in there. So, I've got my bronze table. I've got two tables there. I've
[17:44] got my silver table. And in my silver table, I've got a few extra ones, but we're just focusing on these top two here.
[17:58] And then I can query it. So I can see that I've got my three tables that I'm interested in. I've got a gold table as well, but that's not rel it's relating to a slight something different, but a different table. But we're going to focus on this sales one here. Right. So
[18:10] if we run it, and it might give me a second, and I am trying to do live demos, which is always fun.
[18:31] There you are. It's run. So, this is my um what did I say? Silver table. And we can see we have some nulls that we need to fix up. Right. So, that's our bad data is coming in in our silver table here.
[18:44] So, if we went to Postgress, so this is our Postgress and we can get to that through the little box here and we have different options here. So if we go to Postgress, we can see that we've got our
[18:56] uh dashboard here. We can go to branches and we can create those branches really nice and easily. We can just simply just go to create. We can specify how long we want that to um stay active for
[19:10] in our overview. We are able to see some of the branch details and in information monitoring. We have we can see that I've been using it this morning. We can see how much usage and CPUs we're using.
[19:27] We've got the editor where we can create a table. So if we went and then we got tables. So if we go to my database that I've created, should take a minute to we can see that I've got some sync tables. But before we go to that, we can simply
[19:41] create. So we've got options to creating this. This isn't working. So we've got a nice little simple example here that we can just go and run.
[19:55] Just going to create a nice little test table. We can explore it. It gives us our information there. We can go back to table and very quickly we can create a test table in there. And we can do that
[20:07] inside inside that UI because we've got this set up. If we go back to our uh catalog, wherever that was there, we can go and easily create a
[20:21] synchronized version of this as well. We can go to the table we want. We can go to um create this little thing. We can go to create and we can synchronize.
[20:34] And now we would to be able to create a synchronized table obviously we need Postgress to exist. So we need to create the Postgress table first. Um, so we'll just call it um test. Yeah, we got the
[20:48] where we want it to where we want it to go. So if we've got a different project, we give it the branch we want it to go, which the database we want it to go. Down here we can choose what the mode
[21:01] is. And then here we can either choose to create a new pipeline or use an existing. So, we can see that it's creating some existing ones because I've already synchronized these these two.
[21:14] if we hit create and we'll leave it running in the background and we'll see how long that takes if it Okay. And we can see that it's
[21:28] starting to sync. So now what it's doing is it's starting to create this sync nice table. So this little little but um no that's not it.
[21:42] It where is it? It's creating this synchronized table here. Right. And we can see it's synced. And if we go back to my pipelines
[21:56] just get rid of that. We can see that it's created one at the bottom because it gives its own weird gooey name. So we can see that it's created this pipeline on its own. So in the background whenever we synchronize it
[22:08] creates this for you. So as I mentioned we could do that but that's going to take us a long time. So we don't want to do that. What we will do is we'll create
[22:20] this delta to postgress um synchronization notebook and essentially we're using the APIs to do that. And so all we've we've got here is some some functions
[22:33] to get some of the parameters that we need. So I've defined the parameters that I need. And all that's going to do is I'm going to provide the parameters. I'm going to tell it the all the tables I want to do it. I mean we can obviously
[22:46] qu we can make that um call the um query the table and generate it itself but I've decided to specify it directly. And then in here I've got some functions. But essentially what I'm doing is I'm
[22:59] looking at the API to uh to get a sync table to view if it exists to create it to even delete it if I want to remove it. And at the end it will just gener
[23:12] loop through all of that and generate my tables one at a time. And so it's a bit more of a it's the pattern is then allows me to create hundreds at the same
[23:24] time. Right? And when I ran this, it took what 57 seconds back and when I ran it. And so that's what generated Oh, let's go back here. Um, and that's
[23:36] what generated this table here. And this table here is exactly the same as the one I have in my silver table. So if I go back to here.
[23:52] So these are my sync tables. That's my silver table. And if I query them,
[24:05] I'm just going to query this one if I can. If I query these two, they will be exactly the same.
[24:26] are there. So the next thing we can do in Postgress is we can also go to our Postgress if I can find it again. Yeah, we can go to the editor and we can even query tables in here and we can
[24:40] query something specific. So here I want to basically find anything that's got everything that's got nulls in it. So, all of these and I can run that and it'll give me my my records.
[24:52] If I wanted to, I could even do that in the table. I could filter it in here. So, I have the ability to do the filtering here. Um, I don't remember what number it was. So, I could type in
[25:04] the in here and I can filter it as well. So, we've got two different places where you're able to kind of query query that data. So, I've gone off and and built this
[25:16] app. So, I have a not little app to try and correct it. And that's what it looks like. I'm just going to refresh it to make sure it works. And I've got two things it's doing here.
[25:29] Right. So, I've got the data browser. So, I want to give it to my um my users that will be able to uh browse it. Um and then I've got sales corrections. So if I click on the sales corrections
[25:44] and edit it, I can see that everything I have in that query that I showed a minute ago inside 8base, it just shows that I've got things pending. So if I just click on that one, I just type in
[25:56] uh some numbers and we can see we got some calculations at the bottom. So where we've got calculations we want to force on our users, we can do that as well. we can say, well, this is what you need to this
[26:09] is how it it should happen. Um, I'm going to put 20% tax on. I can review those changes and make sure that the users validate that that that's the correct change. And once they've
[26:21] confirmed, it will go back and it'll update that record. So then if I go back to my if I go back to this now and I I run it
[26:33] again and I go across, we can see those records have been updated. So there's my 20% 100 values. So then if I go back to
[26:45] my sync tables that I ran a minute ago, these ones, right? Um and run them again.
[26:58] >> We can see the sync tables have also changed, but I've lost that. I've lost what the history was. So I've lost that it was zero. If I go up one, I can still see that what my original silver table is. That
[27:10] is still blank. Right. So what I'm going to do is I'm going to create a create another pattern to basically create me a corrections table. And very similar to what I did with the first pattern,
[27:23] but this time round, I'm I'm just creating and I'm writing back. And if I go back to the bottom, what we do is we do this merge. We're just merging it based on the on
[27:36] the on some of the ID and um and conditions. And we can see that that's created this corrections table for us. And then again we can go back and we can query that
[27:48] corrections table and it will add or update that corrections table for us. So therefore if we go back to this here we
[28:00] can see that we've got our corrections table. We still have our sync but we've got our correction table. >>
[28:14] can create that as a as an external location and then there's oh not external location ex uh where is oh
[28:27] um create as a catalog and then we can create it as a for um as a a lake base and then we can point where we want it and then we end up having a a copy of
[28:42] that in our catalog. All right. So, go back to this one.
[28:58] So, kind of start wrapping up. I don't actually know what time it is. Um
[29:10] so what's next for Lakebase? Now when I started building this last year, this is the problem I had last year. Now since since I kind of started doing this uh putting this demo together, Lakebase has changed four times and I've had to change my demo four times. Um and most
[29:24] recently this came out um change data feed
[29:36] in public preview but essentially it's doing something similar. All right. So it's basically going to
[29:48] going to hold it inside a history table. um similar to what I did with my corrections table. And then it's basically um going to run it and update it and keep it fresh. Right? Those
[30:00] history tables are going to capture some additional uh fields where it's going to tell you what the operations was, but you'll still need to create that golden pattern later, right? That history table will need to then merge with with
[30:13] something later on, right? It's still preview, but essentially that is where where it's moving. So the ultimate goal, the ultimate thing with late bas is it's ever changing, right? It's every month something new is coming out. All right,
[30:25] and that's that's where it's going. All right, it's going to keep improving. So some things to kind of watch out for
[30:38] The SDK hasn't been updated. They've only got it for the provisioned. Um so um just be flexible on that. Um the regions, they have more regions coming out, but again check what regions are
[30:50] available. And then branches are one directional, meaning you can have a child branch from your your production, but you can't go the other way. And you probably don't want to because your child or your your your dev branch is
[31:04] just meant to be a throw away because it's just a copy, right? But again, keep an eye on that and see what that happens. And if you need to push it back in, then it should go back to the flow. And then um you got things like uh
[31:20] other limitations that's worth looking at um and keep an eye on the limitation and using some of the addition new new uh new features. Unity catalog registration is optional
[31:32] but you should look to kind of do that as a mandatory thing and it would be good because then you start having that lineage on everything and there is no going from um going from
[31:44] uh post uh provisioned to autoscale. There's no kind of migration step. So you have to do that very manual. Um and then you've got things like um
[31:56] apps. there's a limitation on of the size of the app files.
[32:09] in the future. Um so make sure you have that conversation with whoever needs to pay the bill. And then apps are maturing. Okay, so they're getting better. So we've recently had custom u uh URLs come out and that'll help with
[32:22] that kind of built-on stuff. So, you know, we'll keep keep an eye on that. So, just to wrap up the last couple of things, um you know, we went through through these things
[32:35] you c you can achieve building that kind of lake um that kind of right back without breaking your lakehouse. Um there's no native um it's all native. So, you don't have to have any external
[32:47] solutions. Um and you can basically govern it all through Unity catalog. And then your big winners are your end users um and your IT teams along with that data quality and you keep that
[33:00] centralized information. So yeah, give it a go and try that. I think I'm out of time now hopefully. Is that right? Yeah. Thank you.

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.