Migrate from Legacy to Databricks with Spark Declarative Pipelines and Unity Catalog

Summary

84.51 degrees migrated Kroger's clinical targeting pipelines from a decade-old on-premise stack of Linux servers, Oracle, and Azure Data Factory to Databricks Spark Declarative Pipelines, eliminating SLA misses that had previously reached up to 10 days per month.
The new architecture uses Auto Loader, Unity Catalog, and Databricks Asset Bundles to process more than 40 tables and millions of daily rows through a bronze-silver-gold medallion structure with CI/CD integration via GitHub Actions.
The migration achieved a 2.5x cost reduction and 2x faster pipeline execution, with SLAs now met consistently and advanced patterns like SCD Type 2, looping, and schema evolution handled natively in Spark Declarative Pipelines.

Migrate from Legacy to Databricks with Spark Declarative Pipelines and Unity Catalog

84.51 degrees, a retail insights company powering Kroger's clinical targeting pipelines, migrated from legacy on-premise systems spanning Linux servers, Oracle, and Azure Data Factory. Their architecture created severe bottlenecks: poor observability, cascading failures across multiple hops, and consistent SLA misses up to 10 days per month.
Learn how 84.51 replaced legacy ETL with Databricks Spark Declarative Pipelines, Unity Catalog, Databricks Asset Bundles, and Auto Loader to process 40+ tables and millions of daily rows. This case study covers SCD Type 2 dimension handling, identity column workarounds with external tables, real-world looping patterns, schema evolution, and CI/CD integration with GitHub Actions. Results: 2.5x cost reduction, 2x faster execution, and SLAs met consistently.
🤝

Chapters

FAQs

What legacy systems did 84.51 degrees replace when migrating to Databricks?

The legacy architecture had been in place for about a decade and included source files landing on Linux servers, an old-school SCD process for inbound ingestion, stored procedures for business logic, and Oracle as the data storage layer with Automate as the ETL tool. Cascading failures across multiple hops caused consistent SLA misses.

What is Spark Declarative Pipelines and how did 84.51 use it?

Spark Declarative Pipelines is a Databricks framework for defining data pipelines declaratively. 84.51 used it to replace their legacy ETL, handling complex patterns including SCD Type 2 dimension management, looping constructs, and schema evolution across more than 40 tables processing millions of daily rows.

What business process do Kroger's clinical targeting pipelines support?

The clinical targeting pipelines inform Kroger pharmacies and the Kroger call center about opportunities to engage patients who are at risk or eligible for clinical programs. The mission is to improve patient health by identifying and contacting patients who can benefit from pharmacy outreach.

How did 84.51 use Databricks Asset Bundles for infrastructure as code?

Databricks Asset Bundles allowed 84.51 to define their pipeline infrastructure as code and integrate it with GitHub Actions for CI/CD automation. This replaced manual deployment processes and enabled consistent, repeatable deployments across environments.

Full transcript

[00:08] Welcome everybody. I'm glad you're here. Uh, so how everyone is doing today? Uh, I hope everyone had a good time last night. And really glad that you made this morning after the hangover or whatever may it be. So, migrating is always hard.
[00:25] Like be it dragging yourself from your bed to this room or moving places or moving your data. So, we are here to share our journey of migration from on-premise to Databricks using some of the tools like Lake Flow Spark
[00:42] declarative pipelines, Unity Catalog, and Dabs. We have named it from legacy bottlenecks to high-performance platforms. Databricks makes it possible.
[00:59] I am Piyush Malik, a senior data engineer from 84.51. My co-presenter Alex, I'll let him introduce when he starts on his part. We are from 84.51. If someone doesn't know what it 84.51 is, it's a the data science insights media
[01:16] company. It is it acts as a like wholly organized wholly subsidized organization under Kroger. And uh, so you can say that uh, 84.51 acts as an analytical backbone of Kroger
[01:32] and its CPG partners. And Kroger needs no introduction. It's one of the largest supermarket chains in the US. So, whoever knows about Kroger also knows about the Kroger pharmacy and we are here to talk about its business use case. So, one of the
[01:50] main business use case of Kroger pharmacy is the clinical targeting. So, it's the solution of the clinical expertise. It's programmed into the targeting signs. On regular basis, we inform Kroger pharmacies and the
[02:06] Kroger call center about various opportunities for the patients. Its mission is to improve patients' health um on a regular basis and then we try to contact each and every patient and who
[02:21] are at risk or who can be the most valuable patients. Some of the examples of the signs includes like patients eligible for clinical opportunities, patients who are at risk or the patients who are likely to engage
[02:37] with our program. Now, coming to our legacy architecture. So, this is the legacy architecture that existed for about a decade now. Um so, going into the detail,
[02:53] we have the source files coming from the upstream, mostly from Kroger and some of the third-party vendors. It used to land in the Linux server and then we have the old-school SCD process, route to inbound ingestion happening,
[03:09] and then we have the stored procedures and business logic. We have the final tables landed in the fact tables and SCD type two dimension tables and all got stored into Oracle as the data storage. The ETL tool we used
[03:25] for that is Automate. It's similar to Informatica, if you would know. So, looking back couple of years, talking about 2021 or '22, early '22, our company decided to move to cloud.
[03:41] So, we at that time had the exposure to ADF and only notebooks, Databricks notebooks at that time. And our our team and our users were also keen to experiment on cloud.
[03:57] So, as a quick and dirty way, we figured out a way just copy the data from Oracle to ADLS using data bricks. No ETL or migration involved here, just the copy of the data.
[04:15] So, we get to use the data. We also got to use the data and our downstream users also had the opportunity to use cloud data. But, we had some bottlenecks here.
[04:30] Even though we were migrating the data from Oracle to ADF, Oracle to Azure via ADF, we had some bottlenecks. We had like poor monitoring system, multiple failure
[04:47] points because there were so many hops underlying hops there. We had debugging complexity and scalability issues as well because the chunk of the majority of the ETL flow was done in on-prem.
[05:03] And we didn't have much of a technical assistance at that time. So, what was the result? Increasing cost, increasing execution time, and the most important, we were missing out on SLAs. Like it was 9 or 10 days in a month that we were
[05:20] missing out on SLAs. So, again looking back at the legacy architecture, this this had to be modified. Something had to be re-architectured. Something had to go. Some new had to come in.
[05:40] So, we removed the Linux server, removed Oracle, removed ADF. There's nothing wrong with ADF, but we wanted to remove the number of hops. We were already utilizing Databricks notebooks underlying ADF. So, we thought, why not
[05:56] use all the tools available in Databricks and make it uniform across our platform and our users as well. So, thinking about the modern architecture, the source remains the
[06:11] same, but we replaced the later part of our system with some Databricks tools, and I'd like Alex to speak more about that.
[06:27] So, hi everyone. I'm Alex. I am also a data engineer at 84.51. Um I hope that at the end of this conference, you already know what most of these tools are, but just in case, I'm going to give a quick crash course. Um so, Lake Flow Jobs and Spark Declarative Pipelines is up first.
[06:44] Lake Flow Spark Declarative Pipelines or SDPs are a declarative framework for creating batch and streaming pipelines. You can either use SQL or Python, and SDPs use the same data frame API as Spark and Structured Streaming.
[06:59] Some key capabilities are incremental data ingestion, batch and streaming transformations, and real-time stream processing. I included a little code snippet. It's very simple, but it's a very real way to use SDPs. There you can define the name
[07:15] of the table and the transformation logic. Notice that all you need to do is define the what, so where you get your data from and what you want to do to it, and you don't have to worry about how. Uh so, that means the orchestration, the error handling, etc. That's all done
[07:33] automatically. That's the power of Spark Declarative Pipelines. One way to load data is the auto loader. The auto is a way to incrementally ingest data files as they arrive in cloud storage. So, some
[07:49] sample sources would be Amazon S3, Azure Data Lake Storage, Azure Blob Storage, uh Google Cloud, or Unity Catalog volumes. And auto loader keeps track of ingestion progress using checkpoints to
[08:05] ensure that every file is only processed once. So, to use the auto loader, you would use spark.datareadstream and specify the format as cloud files. As you can see, a variety of formats are
[08:23] supported. Um another feature of auto loader is the path blob filter, which behaves similarly to a regular expression where uh based on the pattern you define, it will grab certain file names. Um so, in this example, we have
[08:40] star.csv. Can everyone hear me properly? Okay, I'll Should I wait? All right, okay. I'll keep going. Um so, star.csv will match every file name that ends in .csv in your file path. And
[08:57] this is useful for when you're trying to read from a folder with multiple file names or file formats. So, in that example there, we have that .xlsx files as well, but we only grab the CSV files. So, this is a pretty common use case.
[09:15] As for SDPs combined with auto loaders, it's as simple as including the auto loader definition within the table definition. This way, we can use Spark declarative pipelines to perform ETL logic incrementally on files in cloud cloud
[09:32] storage, which is a common use case that we use as well. Now, Lakehouse Jobs is workflow automation within Databricks. It provides orchestration for a variety of tasks. Uh so, task could be notebooks, queries,
[09:47] pipelines, etc. Um it also has additional functionality such as control logic where you can set certain tasks to wait for other tasks to run before running. Um you can also schedule jobs to run once a day, once a week. Um these
[10:04] are just some examples of at this additional customizations you can make for Lakehouse Jobs. Now, on to Unity Catalog. Unity Catalog is a unified governance layer in Databricks. And for our purposes, we were really only interested
[10:22] in the catalog, schema, and table namespace. Uh this is where our data would end up. So, catalog is the overall um you would organize it based on business unit. And then schema is a bit more specific based on projects or teams. And then table, it's pretty
[10:38] self-explanatory. It's your structured data. Uh volumes are unstructured data. Uh views, functions, self-explanatory. Um Unity Catalog also provides additional powerful tools such as data lineage,
[10:54] data discoverability, um access control. We'll show some examples later of that. And another concept that's pretty neat with Unity Catalog is managed versus external assets. So, what this means is for
[11:09] managed tables, Unity Catalog manages both the governance metadata as well as the underlying file storage. So, that would be like where you store your actual data files. So, Azure Data Lake Storage. And for managed tables, when you delete it from Unity Catalog, not
[11:26] only does it get rid of the metadata, it also goes into the file storage and deletes that as well. As for external tables, Unity Catalog only manages the metadata. So, when you delete it from Unity Catalog, you might not see it in Unity Unity Catalog
[11:42] anymore, but if you go into Azure, it's still there. So, that requires the extra step for you to um delete that data. Now, on to DABS, which is declarative automation bundles.
[12:00] Oops. Declarative automation bundles is an infrastructure as code approach for managing Databricks projects. It's a collection of YAML files that define the configuration, infrastructure, or resources such as Lakeflow jobs or Lakeflow pipelines um in your project.
[12:17] Uh bundles also allow you to define different targets targets, which means you can define uh different settings for different environments. So, your typical development, testing, stage, prod. You can customize each target so that
[12:33] um you're deploying to different environments. And the center component of the bundle is the Databricks YAML file, which I'll show an example here. Um so, at the top of the file, you would define your bundle name, and then you can list your resources. So, in this
[12:49] case, we have a test job that runs a test notebook. And then you have the targets. So, dev local is a specific one that I'll talk about in the next slide, and then you have dev, stage, test, prod. And within each uh target, you can see that you can like
[13:05] redefine uh base parameters such as the environment variable. Um so, you would use different targets based on where you're deploying your bundle to. So, as I mentioned before, dev local is a special pretty useful case here. Uh if you look there in the left arrow, you
[13:23] have the development mode selected. So, what this means is that when you deploy your bundle, your username gets tagged to your resource. And this is really useful for when multiple developers are working on the same project.
[13:38] In the past, we didn't have dabs and we would only have a single dev resource to work on. So, each developer would be like, "Hey, is anyone working on this?" If someone was, you'd have to wait for them to like finish their changes and then you
[13:53] put your changes in because you'd be overriding that that resource. But this way, each developer has their own resource that they can independently test their changes on. Now, to use dabs, you can use the command line interface. The procedure is
[14:09] pretty straightforward if you're just testing. So, you have to log in, validate, and deploy. Obviously, you have to authenticate with the workspace. So, you have you're able to deploy your resources. Bundle validate,
[14:24] assuming you have all your files ready, it'll check all your file definitions and make sure that your bundle is formatted correctly without actually deploying it to the workspace. And then you have bundle deploy, which deploys your bundle to your workspace.
[14:40] An additional command here that I didn't put here is called bundle destroy. So, when you're developing and you're done all your changes and you think that it's fair to remove all your pipelines and all the test data that you created, you can run the bundle destroy command,
[14:57] which will just remove your footprint from the workspace. And going back to manage tables, this is useful as well. So, when you destroy a bundle, it will not only destroy the pipelines, but it will also remove the data that was
[15:13] created by those pipelines. So, when you say you have the test data, but you don't need it anymore, when you run the bundle destroy, you get rid of that storage and free it up as well. So, that concludes the quick quick crash course on the Databricks tools that we
[15:30] use. Going to pass it back to Pew to go over the migration. Thank you, Alex. So, now we have a fair understanding of the tools that we have used in our migration project. Let's go back
[15:46] to the legacy architecture. So, we already went through this. We removed Linux server, removed Oracle, removed ADF. Then, we replaced it with Lake Flow jobs and Spark
[16:02] declarative pipelines and Unity Catalog. And then, this is the modern architecture that looks like So, the source file remains the same. The source remains the same, and then comes the data landing and the ingestion
[16:18] part, where we used ADLS Gen2 as the data storage, and leveraged streaming tables for bronze layers, where we have shown before how the raw to inbound ingestion happens. And there comes the silver layer.
[16:34] We use materialized views for putting all our business logic there, and then the gold layer, which has all the SCD type two tables. These are, by the way, external tables. We'll explain later why we chose external tables, even though we had the option of
[16:51] managed tables, as well. So, once we have the source files data load landing and ingestion done, these goes to the downstream applications. One of the use case I have already explained before is
[17:07] the clinical targeting. That is the most important use case for the Kroger pharmacy, but we have other use cases, as well, like vaccination and Kroger health automated segmentation and third-party vendor messaging. And for all these we used orchestration
[17:23] tools like Lake Flow jobs and STPs. And for internal audit, we have chosen to use managed Delta tables and Unity catalog for the data governance.
[17:39] This is how our sample Lake Flow job diagram looks like. So you have at the initial start um we have the initial kickoff. So here we have used the used the managed tables to register the kickoff so that we don't
[17:55] do a duplicate run of each job. Um and then comes the streaming tables and validation where we do the raw to inbound ingestion. After that, it's the materialized view at the bottom if you see, where um the
[18:10] business logic resides and in parallel executes the schema check. Just to like keep a track if all the schema that we are receiving from the source is the same or is there any deviation.
[18:27] We'll have a deep dive on that later as well. Once that's done, then we move on to the SCD tables, fact and dimension tables, and then when it executes, we have a registered run like if if the job got failed or if
[18:44] it succeeded, we'll have a tracking of those as well. So this is just a basic flow of how the job looks like. It can get more complex depending on number of tables and how we are ingesting. So this is like if you if you consider it's like
[19:00] one-on-one mapping, one table coming one file to one table to one SCD. Um and then we can use loops in in if the number of tables increase. This is a like screenshot from our
[19:17] actual job, how it looks like. Route to inbound validation and then uh first we have the registered run route to inbound validation and then the number of layers, how it can get complex as we go in further and how the
[19:33] dependency increases. And once everything is done, we have the pipeline success and failure. Depending on that, it gets registered. So, if it has the failed status, for example, it failed, it won't let you run the next job. So, you have
[19:48] to manually update it or you have to update the job or you have to correct it before you do the job run for the next day.
[20:05] Now, we'll have a deep dive of Lake Flow STPs and see how the register repetitive logic looks like. Over to you, Alex. So, can everyone see those tables names? No. No, you can't because there are so many tables there. Um this is a specific use case that we ran into where we had
[20:22] many different folders and many different files to read from you but for different pharmacy data. But the ETL logic was all the same where we would be reading from a not comma-separated but it was like a vertical bar character delimited
[20:39] file where uh we were just reading it from the files. And the question is, did we really define every single one of these tables one by one over and over again? The answer is no. Um you can use looping logic with Spark declarative pipelines.
[20:57] You can define a mapping first uh of each table's input file name, schema, and file format, and then in your Spark declarative pipeline definition, you can specify those parameters for each in a for loop.
[21:14] But, notice that the these two code snippets, while it looks like they're performing the same logic, are not the same. In the left one, we define the parameters outside of the table definition, but in the right one, we define them explicitly within the table function.
[21:30] Now, I don't know exactly how it works under the hood, but the left one does not work properly. What happens is when you run your whole pipeline, it only grabs the last value that it sees, but if you explicitly define those parameters within the table definition,
[21:47] it correctly loops through every table and runs the Spark declarative pipeline. Now, we also use Auto Loader to ingest files from Delta Lake from our data lake storage, and I just wanted to highlight
[22:02] an additional glob pattern, uh specifically So, the curly braces, it kind of behaves like a SQL if in statement, where if your value matches any of the values within the curly brackets, it will match the pattern. Um
[22:18] so, this was useful for our weekly jobs, where files would be created every day, but they would have the um their own dates tied to them. So, in this case, we want to get June 17th and June 18th.
[22:33] And those file names would match, where June 17th and June 18th would match, but not June 19th. And I also wanted to highlight the star character. So, technically, this means zero or more characters, and sometimes
[22:49] our file names would contain the date then additional characters, such as timestamps, but with the star character, it will make sure that uh the date.csv file will get grabbed, but also the date blah blah blah.csv will get grabbed as well.
[23:09] Now, Autoloader also has a schema evolution mode where it you can kind of customize how your stream deals with new columns or data type changes, and there are five options here. I'll go through each of them each of them quickly. Add new columns will fail your stream at first, then update the schema, and then
[23:27] the next time you run that stream, it'll it'll work properly. Add new columns with type widening is essentially the same as add new columns, but for certain types of changes such as integer changing to float, it will modify that column called type widening,
[23:43] and then it'll work properly in the future. But, there are some unsupported data type changes such as integer to string that will not get widened. What will happen is that it gets added to a rescued data column. Now, what's a
[23:58] rescued data column? That brings us to the next option, the rescue option. So, your stream will not fail, it'll carry on as usual, but if it sees new columns or new data types, those columns get added to a JSON string. You can see the example in there. It's
[24:16] like a curly brace new column two new column three. So, it's kind of up to you to look at that column and decide what you want to do there. And then you have fail on new columns. This fails your stream until you fix your schema or fix the file cuz some
[24:33] sometimes it's the file that's a problem and you don't want to actually update your schema. And then the last option is do nothing. New columns are ignored. Carries on as usual. Now, one limitation that we ran into was
[24:49] identity columns, and this is also the reason that we did not use, I think it's called Auto CDC, where we're implementing SCD type two changes in our tables. Um unfortunately, this is not possible yet with streaming or incremental
[25:06] processing in Spark declarative pipelines, but there is a workaround and that's to use external data tables. Uh so you can create an external table where you specify the deep generated by default column and allow the
[25:21] delta.feature.allowColumnDefaults property. So this changes our process a little. So rather than using a single Spark declarative pipeline to run to transform all our data and load it, we
[25:37] have a Spark declarative pipeline to load the data and then the create table statement and then we have a separate script that runs SCD type two merges um into our final tables. And this way
[25:52] you get those auto-generated default columns. Now, everything that comes out of our ETL process now automatically shows up in Unity Catalog. There's no need for that Azure Data Factory copy from Oracle
[26:07] to Unity Catalog. Um and a neat feature of Unity Catalog is the Catalog Explorer. So as you might remember, we have I don't know, over 50 tables to look through, but um you can always look up your tables and it makes it really easy
[26:23] to navigate. An additional feature of Unity Catalog is access control. So you can kind of specify the permissions of your team, your service principles, your admins all in Unity Catalog. There's also data
[26:38] lineage where you can see all the different notebooks, uh processes, or queries that access those tables if you're trying to place the blame on someone. Um we also use declarative automation bundles throughout this project. Uh so,
[26:53] this is another snippet. You've seen this before, but um our daily ETL is pretty ex- pretty long and there's a lot of different dependencies. Um but declarative automation bundles has made that um made managing that very easy.
[27:09] So, here's a little snippet of our databricks.yaml file where, as you can see, you can specify the permissions, you can specify the notebook paths. You can also specify the the control logic where um in the bottom the routes inbound task does not run until the
[27:26] status and validation step completes. So, this this way, by defining everything in the in a single file, ensures that configurations are consistent across deployments because your all your developers aren't uh accessing the workspace UI to define
[27:42] these. Um so, you're just eliminating more room for error. And Piu and I also used the development mode here. Uh so, we were working on the same project and we need to test our own changes. So, as you can see, we get our
[27:58] own weekly job with our username attached to it. We're able to um play around with them without clashing with each other. And also with GitHub actions, um our CI/CD process was um
[28:14] made a lot easier where changes to our files in GitHub would be automatically deployed to Databricks on uh merges to main. Now, that kind of concludes all the specific use cases we did. Um and we
[28:29] completed the migration. Um Piu, how did the migration go? Okay. So, everything done. Job is in production. It was running fine for couple of days, weeks as well. And then we asked our users uh
[28:46] to ingest our data and see how it works on their system. So, do you think that it ran all fine and everything went smoothly? It did not. So, I talked about one of the use cases
[29:01] earlier, clinical targeting. So, that was one of our users, downstream users, who who tested our data on their system, and for some reason the data was not matching. There was a big spike in their numbers.
[29:17] So, all of our development team were just wondering what could go wrong. We did so much of unit testing, and everything was working fine. What happened? So, now Genie comes to the rescue here. I know, like by now you have all heard
[29:34] many things about Genie, and then we have so much more on Genie to explore, but here in this case we like Genie helped a lot in figuring out what happened. So, it was like one of the columns
[29:50] where the data type mismatch happened. So, how the data was flowing from like inbound to the like from source to target, it did not meet the expectations, and that's why we were having this issue. So,
[30:06] if you see, this is the screenshot of one of the test cases, or the questions that we asked Genie, and it gave the answers. It's not the exact output of what we got as a result. I didn't know at that time that we were presenting, so I would have taken that screenshot, but
[30:21] it gives uh good way of presenting that what we were expecting. So, what changes we had made deliberately, and how the output looks like. So, I'd like to say, like whenever
[30:36] you're migrating your project, and you have your downstream users, ask them to test the data on so that there are no surprises at the end. So, in this case, our users waited for a couple of days to test on our data because they had some prior commitments, but early
[30:54] testing is good for the detection of these kind of errors. So, now talking about the final impact, we have seen a huge impact in the reduction of the execution time. So, this is the data taken from like one
[31:09] week of execution. So, the time has drastically decreased like around 2x amount of time it has decreased. And also talking about the cost, we see a drop of about 2.5 times
[31:28] in the like 2.5 times reduction in cost in a month. So, that's really good. But, the main thing that we would like to share is the SLA comparisons between on-prem and Databricks job run. So, if you see how
[31:44] we have divided There are three categories. So, the job runs early in the morning. So, that's in the green category. And then we have the yellow category and top That's a red For some reason, it's not showing up as red here, but that's like
[32:00] late at night. So, at the left you can see the job starts in the morning and it takes around the whole day to finish because there are so many hops. It starts in Automate, then goes into Oracle, then ADF. And there are so many
[32:15] hops, something might go wrong. And then we have to fix it and like the overall process takes It takes around like 4:00 p.m. to 5:00 p.m. of our time to get executed. In this case, in Databricks, the job
[32:30] starts early on and then it finishes within our green SLA category. So, why is it So, for our users, we always try to deliver fresh data on time. So, if we consume so much of time in execution of
[32:47] the job, and something happens at night, the users don't get the fresh data, and Proguard loses out on money. So, being on time is very important for us. The But, and with this, we conclude our presentation. That's all we had to
[33:03] share, and that was our migration story. Thank you for listening to it. And I hope you enjoy the last day of the summit. 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.