From Four Sync Paths to One: Migrating PostgreSQL to Databricks Lakebase

Summary

Afresh migrated from a self-managed PostgreSQL instance with six separate sync mechanisms to Databricks Lakebase, a fully managed serverless PostgreSQL engine, consolidating all data sync paths into a single dbt-based bidirectional approach.
Custom dbt materializations called synced_table and reverse_refresh enable both lakehouse-to-Lakebase and Lakebase-to-lakehouse syncing with minimal code, reducing the time to add a new operational data model from 3–5 days to 15 minutes.
Beyond velocity gains, the migration delivered modular reliability, simplified governance through Unity Catalog, and AI-readiness benefits by enabling agents to access data end-to-end via the Databricks SQL MCP.

From Four Sync Paths to One: Migrating PostgreSQL to Databricks Lakebase

Afresh forecasts and optimizes produce orders for fresh food retail, serving over 10,000 store departments at leading retailers like Albertsons. The challenge: synchronizing data between a self-managed PostgreSQL database and the Databricks Lakehouse, which had accumulated four different sync mechanisms, significant boilerplate, and operational friction. this video details how Afresh migrated to Databricks Lakebase, a fully managed, serverless PostgreSQL engine natively integrated with the Lakehouse.
Learn how Afresh consolidated six previous sync paths into one unified dbt-based approach, eliminating locking, scaling bottlenecks, and VPN complexity. Discover the custom dbt materializations (synced_table and reverse_refresh) that enable bidirectional syncing with minimal code, reducing time to add new operational data models from 3 to 5 days down to 15 minutes. Explore reliability gains from modular refresh, governance simplification through Unity Catalog, and the unexpected AI-readiness benefits that enable agents to access data end-to-end with the Databricks SQL MCP.

Chapters

FAQs

What is Databricks Lakebase and how does it differ from self-managed PostgreSQL?

Lakebase is a fully managed, serverless PostgreSQL engine natively integrated with the Databricks Lakehouse. Unlike a self-managed PostgreSQL instance, it eliminates operational overhead like capacity management, VPN configuration, and connection scaling, while providing native integration with Unity Catalog and the rest of the Databricks platform.

How do Afresh's custom dbt materializations work for bidirectional syncing?

Afresh created two custom dbt materializations: synced_table, which syncs data from the lakehouse into Lakebase for operational serving, and reverse_refresh, which syncs data from Lakebase back into the lakehouse for analytics. Both materializations encapsulate the sync logic so engineers add new operational models with minimal boilerplate code.

What problems did sync sprawl cause for Afresh as the company scaled?

Sync sprawl meant Afresh had accumulated six different mechanisms for moving data between its production database and lakehouse, each created through a series of individually reasonable decisions. This created significant boilerplate, operational friction, and inconsistency that became harder to maintain as the company scaled to more enterprise customers.

How does the Lakebase migration make Afresh's system AI-ready?

Because Lakebase is natively integrated with the Databricks platform, AI agents can now access both operational and analytical data end-to-end using the Databricks SQL MCP. This integration enables agentic workflows that span the full data lifecycle without requiring separate connectors or cross-system credentials.

Full transcript

[00:08] Thank you guys for coming to this talk. Uh to start we wanted to do a show of hands. How many of you have more than one way of moving oper data from your operational database into your production datab or lakehouse? Okay, we have some brave hands back there. You're in good company. We had
[00:24] four ways of moving data between our production database and our lakehouse. And this talk is about how we got to one. I'm Mary Keenan. I'm a staff software engineer at Aresh. I co-led this migration with my colleague Aaron Leonard Zang who couldn't be here today. So I'll be giving both sides of the
[00:40] story. We're going to talk about how we moved from managed post a self-managed Postgress instance to a managed lakebased instance. How we unified our operational and analytical data in Unity catalog and how we consolidated onto DBT au mechanism in both directions. We'll
[00:57] talk about the motivation, the architecture, how DBT works for us, and uh the rollout process we'd recommend. There will be a lot of time for questions at the end, so please write them down as we go. To start, some context on Aresh. Aresh
[01:13] is a forecasting and ordering software for fresh food retail. Grocerers use us every day to decide how much produce, deli, meat, seafood, and bakery they should order for their stores. We serve more than 10,000 store departments at leading retailers
[01:29] including Albertson's. Fresh is a tough industry to be in. If you if a store orders too much, then they have food go to waste in their back room. And if they order too little, then the shelves are empty and consumers are not happy. Uh so mistakes compound over time. Overordering leads to food waste. Under
[01:46] orderering hurts customer sentiment and they both hurt the grocerers's bottom line. Um and our goal is to help them optimize their orders. So that doesn't happen. Every day we ingest data. We do various data transformations, cleaning, entity resolution. We create ML forecasts and
[02:04] then we sync that data, those recommendations into our operational database to serve to the stores. And that operational data layer is how we turn forecasts into orders. And it's really important that we be reliable uh both to us, our customers, and to you as
[02:20] consumers. So why did we change it? The old stack worked and it got us here. But as we scaled to more enterprise customers, it became the right thing to invest for three reasons. One, we had sync sprawl. Nobody starts wanting four
[02:35] different ways to uh sync data. Happens one reasonable decision at a time. Um but the cost showed up whenever an engineer needed to add a new sync because there were a lot of different patterns they could follow. uh but no pattern that was really like a first tier wellsupported and well doumented
[02:52] path. The second pain point was how we required a lot of boilerplate for each new sync model. Adding an operational data model meant changes across multiple code bases that you didn't own. It became a multi-team undertaking instead of a simple task and as a result people
[03:09] didn't want to build an operational data very much at all. And third, the database itself. Self-managed Postgress at enterprise scale means a lot of time on contention, instance sizing, locking, uh, upgrades, and Aresh is not an infrastructure
[03:25] company. So any time that we're spending on our infra is time that we're not spending on our actual product. So our goal was to address these three pain points while maintaining our reliability.
[03:41] Um, and this is just an overview of our architecture in the before world. We uh have our govern delta lake tables. That's where all of our pipeline outputs live in Unity catalog. And then we had six ways of moving data back and forth. Four in just one direction. Um, we had
[03:56] our self-managed Postgress instance which was behind a VPN and on a different uh set of permissions. And so basically this architecture just shows a lot of friction. Friction moving data back and forth friction accessing the data or trying to compare inputs and outputs. Uh a lot of hassle.
[04:18] As we decided which uh the solution that we wanted to take to address these three pain points, we developed three guiding principles. We wanted to coales around one govern catalog. So we wanted our analytical and operational data to be discoverable, permissioned and queryable in Unity catalog.
[04:34] Um like I mentioned in the old world, our data uh was split between two different uh services with different governance and lineage um and permissions. So we wanted to simplify that and nobody including our security team was going to
[04:49] be sad to see the VPN go. We also wanted to consolidate onto one dev interface. We're a big DBT shop. So we use DBT for our main ETL. We use it for our data quality checks, RDB refresh transforms, even DBT Python, which is maybe a little
[05:07] bit edgy. Um, so we wanted to consolidate on DBT as much as possible so we didn't have to learn and maintain a second flavor of tooling. And then we wanted one deploy path. Similarly to DBT, uh we wanted to only
[05:22] have one flavor of tooling that we had to use. We didn't want to have to manage deploys across multiple different uh deployment mechanisms. And we also didn't want to set up pipelines that were special um where it's like, oh, if you're adding a certain type of transform, you need to do a certain type of thing. We wanted it to just match the
[05:38] rest of our architecture. So, one catalog, one interface, and one path. And what we landed on was Lakebase. Lakebase is data bricks managed Postgress. It's serverless. It has compute separated from storage which
[05:54] removes the locking and sizing problems we had before. Um it syncs natively with Delta Lake. It's registered in Unity catalog like everything else. And that got us to this architecture which is a lot simpler, a lot less friction. You can see on the top right or top left we have Delta
[06:11] Lake. Again, that's where our uh ML outputs are being written. We have a DBT materialization that is syncing that into our operational database lakebase each night. Um and then we serve that data to stores using our product API. So
[06:27] we are providing them recommendations, inventory, promotions, etc. As stores use our product, they write data back into the operational database. So the orders they placed um any adjustments they make to the displays or inventory.
[06:42] And then every night we sync that data back into Delta Lake again using DBT. So the big box Unity catalog one set of lineage and permissions and access model and the line kind of moving data from one side of Unity catalog to the other
[06:58] is just DBT in both directions. Um we're going to talk a little bit more now about the actual DBT materializations we're using for syncing. uh because this is a little bit of an unconventional choice. Uh so if
[07:14] you're familiar with dbt, this looks like a pretty standard vanilla dbt model. We have four lines of config. The first one specifies the materialization we're using. Synced table is not a materialization you'll find in the dbt data bricks package. It's a custom materialization that we've written. And
[07:29] under the hood, it's doing some uh somewhat heavy logic for us using the Unity catalog connection object. And so it's hitting REST APIs to create the table and then to trigger updates to the synced table every time we run this
[07:45] model. But the abstraction is super simple and so that's why we chose to take this approach. We're able to have a really simple wrapper around that logic and it makes it really easy for people to add new models. So they specify the custom materialization. The alias that's how
[08:01] we're going to materialize the table in Delta Lake and the sync table name is the target table where we'll materialize it in lakebase. We also specify primary key columns. Those are required for lakebased tables. And if no primary key column exists on the table then we can manufacture one in
[08:17] the materialization. Um yes. So all pretty abstracted away. We also have some flags for dev local developments that allow us to disable the syncing behavior if you just want to make sure that that like shadow table is uh materializing correctly and has the
[08:34] right shape and the right contents. And overall it's about 10020 lines behind the hood. Um it's not nothing. It's probably our most complicated materialization, but it's so much simpler than what we had before.
[08:50] And then reverse refresh is pretty much the exact same thing in the other direction. Um so previously we had four mechanisms for doing this. We had application SQL plugins in a legacy service which were blocking language upgrades for the entire service. We had event- driven messaging jobs um EL
[09:06] pipelines using lowercase DLT not Delta live tables um which had long cold start uh times that made them kind of prohibitively slow to use and we had protein custom scripts which were not really maintainable or scalable.
[09:21] And now it's just this DBT model. So if you know how to do DBT, which we generally do because we use DBT for most of our code, then you know how to do both refresh and reverse refresh. We're not even using a custom materialization for this one. We use the incremental materialization which is available in
[09:37] the DBT data bricks uh package. Um similar kind of uh vanilla DBT specify some parameters. Um we do some logic to basically merge in our data and create I think like an SD2 table every night. Uh
[09:53] but pretty simple and really easy for people to contribute more uh data. And one uh key thing to call out as well is different from the approaches we were taking before. We've basically taken out the movement part of reverse refreshing.
[10:10] So we're no longer moving and transforming the data. We're really just transforming it uh because it all lives in Unity Catalog. So it's just like a select star. Um there's not any like staging it in memory or as parquet files. It's just selecting it directly using Unity catalog.
[10:30] Cool. As a result, um it's so much easier for engineers to add new models in both directions. So adding new operational data or building off of existing operational data. Previously, it would take about 3 to 5 days for engineers to add um a table in either direction. So defining the transform
[10:46] maybe that was like 15 minutes generally pretty simple. You had to do a table migration to create the target table versus now dbt manages the table creation and general management for us. You had to configure the sync and whatever existing job you were dealing with and you had to coordinate at least
[11:02] two or three deploys um especially for that legacy service we had that had the language upgrade issues. there was some funky stuff around like release branches for that and then you would inevitably have to debug and iterate whatever you had built because it probably didn't just work the first time. So 3 to 5 days
[11:20] versus now we still have to do that initial define the transform that 15 minutes and then that's roughly it. Uh 30 to 60 minutes to get that reviewed. Um we have a co-orker named Andy who is our go-to fast reviewer. So if he's online maybe that's 10 minutes but
[11:36] otherwise a little bit longer. deploy is automated. Um, since there's really only one file going out, you don't have to make sure that it's going out the exact same time as something else. And so, if you're not in a hurry, you can just let our automated staging and production deploys roll it out for you. And then
[11:52] the monitoring is built in for all of our lakebased tables or reverse refresh tables. So, that's how we built it. Um, in terms of how we rolled it out, um, our product backs daily store ordering. you rely on
[12:07] our grocerers for food and they rely on us to order you that food. Uh so it's pretty important that we are have uptime and then don't have any uh critical incidents. So when we were approaching launch, one of the things we did was do a formal premortem. We got together key stakeholders from our ops team,
[12:24] engineering teams and we worked backwards of okay something has gone wrong. What do we think it was and why? We developed a playbook for, you know, if these different things happen, these are the messaging we want to send out. These are like our roll back thresholds, the owners. And I do think it's
[12:40] important to do that kind of uh pre-planning in a really low stress environment versus in the middle of like a 6 a.m. incident when everybody is really wired. We also created schema diff dashboards. I think it's pretty common to create like row count dashboards when you're
[12:56] doing any kind of database uh or table migration. But the thing that caught us a little bit by surprise Lakebase was actually we needed to be doing type level checks because going from manage tables to or going from tables that we like managed ourselves to tables that were automatically created by Lakebase.
[13:12] Um we were no longer directly defining the types. And so when we did do some initial product testing that was something that caught us a little bit by surprise some of the type changes that we were experiencing. Um so that's an important thing to make sure that you're checking ahead of time. And then we did
[13:28] a a lot of load testing. Performance was definitely our biggest concern during roll out. Um rollout itself, we started on staging. We had like a really detailed plan. We had our ops people go through and that's how we found those like typing issues.
[13:43] Then we started with a pretty lowrisk customer doing our first cut over and we did the rest of our customers over about a week. uh we wanted to see how the database did as we added progressive load because again performance was our biggest concern. And so we wanted to see if we were seeing degraded performance
[13:58] as we added customers before we got to our biggest one. And then of course stage two readiness. Um, this one kind of speaks for itself, but making sure that you're on call is ready to deal with the new types of issues, um, and has, you know, break glass and other types of runbooks
[14:14] documented. In terms of what this foundation unlocked, um, I'm going to leave the biggest one for last, but we'll start with reliability. Uh, so previously we were refreshing our data all or nothing. So we were doing all of our, um, delta
[14:32] tables, we would stage them, and then we refresh them. And if there had been any upstream failures or if any of the pipelines failed, then we would fail the whole batch. This was kind of like a legacy assumption for relational integrity that we needed this that we had to do all or nothing because god
[14:47] forbid you do one table but not another and then now there's something funky in the database. Lakebase doesn't really support that out of the box. And so when we were evaluating if lakebase would be a good fit for us, we had to test the assumption of do we really need these coordinated swaps? And it turned out
[15:03] that we really don't we're generally actually very robust to kind of uh small inconsistencies in the data and we had like went through all the different like scenarios of this table before this table. Um and it actually turns out to be pretty harmless. So we moved to a more modular refresh where we're doing
[15:19] every table independently and as a result we're a lot more robust. Um if one table has a failure or like a data quality check um doesn't pass then we can still refresh the rest of the tables. uh we can refresh data as soon as it's available instead of waiting for
[15:34] everything to finish. And so we're able to serve uh more our data quicker and more reliably to customers. So reliability was a win. Uh velocity as we've talked about. Uh it's much easier now for analytic and product teams to add their own um synced tables and to
[15:52] build off of operational data um using dbt without needing to get multiple teams involved um or you know coordinate multiple deploys. um governance uh similarly analytics team it's easier for them to query operational data um just generally for
[16:09] like our ops users there's now like a single tool they have to learn SQL editor and data bricks and then they can access all of our data end to end um one call out here is that we still do reverse refresh we still have a formal syncing job um because even though
[16:25] anybody could query lakeface we don't actually want like heavy recurring loads against it uh because it is our production database and so any usage on it is competing with actual users. So for any kind of like really like sophisticated usage we still want to be working off of those um reverse refresh
[16:42] tables which are read optimized and living in Delta Lake. But for ad hoc query usage anybody can now use uh just any SQL editor tool to do that. Um and then platform investment shifts. So uh we were able to get rid of a lot
[16:57] of stuff a lot of um our legacy DB instances the VPN custom code um and generally we were able to start investing more in our product and less in our infra especially in this like AI age um anyone can build anything now um and so the question becomes less can we
[17:14] and more should we and in this case we were like let's just let data bricks do it um and we'll focus on the things that actually set our product apart and so we're able to do that a little bit better now and then the big one um operability uh what's good for people is good for
[17:29] agents. I will admit that a year ago when we were planning to do this project we weren't thinking of AI readiness uh but this ended up being a really good investment for that. We're now able to use the data bricks SQL MCP to access our data end to end. We have lineage end to end um and it's made it really low
[17:47] friction and high value to have um you know triage agents diagnosing issues. Um, it's easier to answer data questions using chat bots. So, uh, it's been a kind of funny just in time AI readiness investment that we hadn't done for the
[18:02] AI but are now benefiting from. And then in terms of other things that this has unlocked, um, so we're currently migrating onto this reverse refresh pattern. We're nearly done with that. We have one remaining operational
[18:19] database that we plan to migrate to Lakebase next quarter. And at that point, we can really kill the VPN. Um, and then we're excited for ephemeral lakebased instances. That's something if you've ever had to share a testing environment uh with a bunch of other engineers or coordinate who's using the
[18:35] database right now. Um, then this is a pain point you would resonate with. Um, so ephemeral lakebased instances will be a big win for development and also for AI assisted development to give them like a safe place to really go crazy. Um and generally we're looking forward
[18:50] to other improvements coming in the pipeline um from data bricks. Things like autoscale to zero. That's something that we're opted in to receive in I think like a week or two. Uh our usage is really spiky. We have a lot of orders in the morning and then not much usage in the afternoon and evening. So for us
[19:06] to be able to scale to zero will have big uh cost savings. Um and generally being on a modern managed platform these new lakebased features they land for us by default. So we could have built ephemeral instances or we could have built autoscaling to zero for oursel and our self-managed
[19:22] postgress instance um but we didn't have to and now we will just like receive these things as they come out without any additional investment on our side and so that is the reason why you still want to choose probably some some managed platforms instead of doing everything yourself
[19:42] cool I talked a little bit fast so we're at the Q&A portion um if people have any questions I'm happy to answer them. Uh I also have like another slide talking about six recommendations. Um or happy to let people go.

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.