Modernizing Investment Analytics with Spark Declarative Pipelines and Serverless Compute

Summary

Northern Trust replaced a 30-year monolithic analytics platform built on mainframes and Oracle databases with a modern cloud architecture on Databricks using Spark Declarative Pipelines, Delta Lake, Unity Catalog, and serverless compute.
The new platform processes data through source-bespoke medallion streams unified into a diamond layer, with SQL and Python-first design that democratizes data engineering and isolates pipeline components to reduce regression risk.
The modernization delivered a 60% improvement in processing speed and a 40% cost reduction through intelligent pipeline refactoring, auto-scaling serverless compute, and features such as liquid clustering and automatic data quality checks.

Modernizing Investment Analytics with Spark Declarative Pipelines and Serverless Compute

Northern Trust replaced a 30-year monolithic analytics platform (mainframe, Oracle, legacy logic) with a modern cloud architecture using Spark Declarative Pipelines, Delta Lake, Unity Catalog, and serverless compute. The legacy system had high regression cycles, rigid architecture, and slow processing. The new platform brings source-bespoke medallion streams unified into a diamond layer, feeds an OLAP analytics engine, and delivers results through web portals, batch reporting, and APIs.
Discover how SQL and Python-first design democratizes data engineering, auto-scaling serverless compute eliminates manual resource planning, liquid clustering and auto-optimization remove tech debt, and data quality checks trigger automatically on table changes. Northern Trust achieved 60% speed improvement and 40% cost reduction through intelligent pipeline refactoring.

Chapters

FAQs

Why did Northern Trust decide to replace its 30-year legacy analytics platform?

The legacy platform was built on a monolithic architecture with mainframe ingestion, Oracle storage, and bundled logic that created long regression cycles whenever a change was made, making it difficult to extend or staff. Modernizing on Databricks gave Northern Trust a modular, cloud-native architecture that scales to meet growing client demand for faster, more direct data delivery.

What is the diamond layer architecture Northern Trust uses?

Northern Trust's new architecture processes data through source-bespoke medallion streams — each business system has its own bronze, silver, and gold layers — that then converge into a unified diamond layer. This diamond layer feeds the OLAP analytics engine and delivers results through web portals, batch reporting, and APIs.

How do Spark Declarative Pipelines improve data engineering at Northern Trust?

Spark Declarative Pipelines allow engineers to write transformations in SQL and Python rather than requiring deep Spark expertise, lowering the barrier for data engineers and analysts to contribute pipeline development. Event-driven data quality checks trigger automatically on table changes, and liquid clustering with auto-optimization in Unity Catalog removes the need for manual tech debt management.

What cost and performance outcomes did Northern Trust achieve with Databricks?

Northern Trust achieved a 40% cost reduction and 60% improvement in processing speed through intelligent pipeline refactoring and migrating from legacy compute to serverless on Databricks. Auto-scaling serverless compute also eliminated manual resource planning, freeing the team to focus on delivering data to institutional clients earlier and faster.

Full transcript

[00:07] Hi everyone, thank you for coming. My name is Jake Stiger. I work for Northern Trust and lead our investment analytics platform for institutional clients. And I'm Russ Hoberg. I am a senior solutions architect for Northern Trust. And uh today what Jay is going to be Jake's going to be talking about some some great topics I think you guys are going to be interested in. Um, these
[00:25] guys have been fantastic and have really um, progressed through a journey that I think a lot of you can benefit from. Uh, and so I'm super excited to have Jake talk about this stuff. So today we're talking about modernizing investment analytics with data bricks.
[00:41] And really what we're talking about is how we've used Spark declarative pipelines to accelerate our go to market and lower our processing speeds to get our data to our clients in a faster and more timely manner. So, let's back up. We had a 30-year-old
[00:57] analytics platform that was built on a monolithic legacy architecture. Consume data from mainframe, store data in Oracle databases on prem, all the logic bundled together, hyper efficient, extremely good at what it does, but harder to extend, harder to
[01:14] staff, to keep uh running. And so we took a step back and we said, how can we modernize this so that we have a more modern framework running in the cloud that also lets us scale to bring new capabilities to our clients and be more
[01:30] competitive with our competitors in the industry. So as part of this, we had a couple key tenants. We needed to increase our capability of speed to market. that monolithic repo and platform that we built each time you make a change had a
[01:46] large regression cycle and installations were trickier because everything was bundled together. So you change one thing maybe it accidentally breaks another. Wanted to reduce data processing times. So as clients become more data hungry and wanting data delivered directly to them rather than
[02:02] only through portal experiences, they want them earlier and earlier so that they can process it in their internal systems and be able to get it into their either portfolio managers or executives hands as soon as possible. We needed to improve observability and
[02:18] supportability. So breaking it down into more micro architecture improves your transparency into where data is at any given time. If it gets stuck, how you can get it unstuck and be able to extend and revise each component of your architecture that uh without impacting
[02:36] the other pieces of it and then lastly bringing together common skill sets. So one of the historical issues that we've seen is our domain experts, our business teams, our data analysts and business analysts speak one
[02:52] language. They understand data. They understand business value but they don't necessarily understand coding languages whether it's .NET, Java, C++, you name it. That's not their expertise. And so when things go wrong, they want to jump in to help, but they don't understand
[03:07] how to review what's going on. So we looked to create a foundation to create faster feature delivery. Our target architecture was looking to improve data quality. So we built a exceptionbased data quality solution in
[03:23] data bricks improve governance transparency and self-service. And so this highle data flow walks through generally how our platform works. We have uh all the way on the left in the green our cloud data sources
[03:39] and our on-remise data sources. So accounting books of record, security master, asset data, characteristics, you name it. And then our on-remise data is our historical platform data. So the thing about investment analytics is history is expected to be immutable. So
[03:54] you're looking to bring that forward and not lose any of that already audited and governed data that was existing historically and look at it as a point in time going forward solution moving forward where you're layering on your new data feeds directly from the sources
[04:09] that were already feeding our legacy platform. We take all of that data, drop it into raw paret files with incremental data in them that we process through our Medallion architecture. So you'll see that our medallion architecture is
[04:25] broken down into a couple different streams. That's not indicative. We only we don't only have three streams, but it's showing that per source we built a medallion architecture that is source bespoke and then we unify those together into our diamond layer. Ideally, we'll
[04:41] look to shift this diamond layer back to gold eventually, but this gives us the flexibility to refactor each of these data source processing individually without impacting the other streams. so that we can move quickly and expediently until we're comfortable with our end
[04:57] state that we can start to normalize some of this earlier and earlier in our data processing volumes. Then outside of all of our uh bronze through diamond layer, you can see what sits outside of data bricks for us with our improcess calculation engine that serves our
[05:13] computed metrics to our clients in real time. in real time being we're not streaming in the 500 milliseconds but it's we store everything in our fact and dimension data model but we only compute metrics as and when they're needed. So we're not premputing every permutation
[05:30] of possible analytic that our clients would require. We're computing those as and when our rest APIs are hit and then our improcess engine will then drop that data. So our key components here are our final data shape, our dimensional data model that I just spoke about that is really
[05:46] the bread and butter of what makes our platform so great is the flexibility that it offers with our OLAP analytic engine sitting on top of that to slice and dice and do carveouts on the fly. Our ETL solution where we use Spark declarative pipelines for our transformation
[06:02] that is bringing our ETL down to a more common denominator that I'll talk about on the next slide. our analytics and access through that OLAP query engine and our experience layer. So, we're moving into a multi-channel experience layer where you have user interfaces
[06:19] that clients log in through web portals and access through uh our analytics portal. You have batch reporting which on a schedule or event-based will send out flat files whether via a data share or via a flat file that gets cut such as
[06:36] a text file or CSV. And then we have different data delivery channels such as API, data shares, data warehousing solutions, you name it. Why we're struggling to keep up. When we first started this journey, we were not
[06:51] on data bricks. We looked at a bunch of different ETL solutions to get data from source to our target of our data product that feeds our engine. But this still had the bifurcated experience and disjointed experience that we were trying to avoid where our analysts and
[07:08] our data domain experts worked in SQL or more commonly in things like Alterix which are lo which is a load code builder and then they're handing off their logic and solutions that they've already proven out to our data engineers who then write pispark and try and interpret how those things work when
[07:24] they're not experts in things like Alterix and building it and that causes a lot of churn. where there's misunderstandings. Maybe it's translated like for like, but the way you would implement it in a low code solution is not optimized for how you would want to build it in Spark and other uh dataf
[07:39] frame like solutions. Spark and Delta Lake not accessible, right? Uh how do you access the data? You're asked to do user acceptance testing. How do you confirm if the data matches requirements? Especially when you're talking about terabytes of data,
[07:55] it's large volumes of data is hard to comb through. How do you know if it's working, meeting your specs? And then compute and cost optimization. And this was a big one. When we build our notebooks, you're building multiple different steps in a notebook, which means you're guessing on compute for the
[08:13] entire notebook, which means that it might be perfectly sized for one job in the notebook, but overkill for another part of the notebook and maybe undersized for a third part. And so then there's a lot of overhead into how do you optimize building your notebooks? How many different compute clusters do you want to maintain? When you go
[08:29] through upgrades, how do you manage that? There's a whole set of comp complexity that comes with having to manage compute and cost optimization and there's trade-offs there.
[08:45] So, solutions and challenges. When we looked at uh Spark declarative pipelines, it was extremely uh appetizing for us and that's mostly because we can use SQL and native Python to write all of our logic. So that brings the skill set into a more
[09:02] common denominator where you can upskill teammates internally that maybe come from more legacy development backgrounds and aren't data engineers, but they do understand things like SQL because that's a common skill set across everything that we do. And then Python's
[09:17] easy to pick up since uh it's a more simple language to learn when you're coming from other backgrounds. We're able to use AutoC to do more event- based solutioning. So, as we're doing incremental processing, it was
[09:32] still typically based on timing triggers, but with AutoC, it'll just monitor those incremental files and adjust them as and when they're ready. And then serverless compute is a huge one. as we were able to unlock serverless compute at Northern and working with our enterprise teams to do
[09:49] that. That removes all of that compute and cost optimization for the most part except for a few key areas where in our lakeflow connectors they do still require serverless compute and but those are in a minimized specific purpose that we're able to manage rather than having
[10:04] to manage it across the fleet of pipelines that we have. And so now when we enable serverless compute there's other challenges and considerations that we'll talk through in the next slide but it reduces that complexity of what size workload is this how much data is coming
[10:20] through it to figure out what size compute do we need what version of spark are we on it removes all of that from consideration which allows our engineers and our analysts just to focus on business logic development and validation in the cycle rather than also having to have a third consideration.
[10:39] Then with data bricks unity catalog we got a lot of other capabilities as well that I want to call out even though we're mostly talking about spark declarative pipelines liquid clustering is huge but it's a massive accelerator for us where now data bricks based on our read and write operations mostly reads is optimizing
[10:57] how to cluster and partition our tables for us rather than us having to go through partitioning and repartitioning exercises maybe having outages changes as at times while we're repartitioning and the tables are turning through that and not available to our clients. Uniform tables are huge for data
[11:14] sharing. We're able to enable uniform tables on our uh diamond layer so that we can share with other platforms outside of data bricks seamlessly with readwrite operations going back and forth rather than having to incur data copy costs or expose it in other less
[11:31] performant means that degrade user experience. And then from a data governance and maintenance perspective, the auto optimization, autovacuuming removes a ton of tech debt from our platform where we had either built into the pipelines
[11:47] themselves, a vacuum and optimization component that does it before you write the data or separate pipelines that are doing that on a schedule. And so we're able to remove all of that unnecessary tech debt from our platform and just let Unity Catalog manage that on our behalf.
[12:04] So that leads us to optimal use cases, right? Firstly, data quality. We're able to use DQX and uh bring that forward into our platform, which means that our data quality pipelines are event driven based
[12:20] on when our tables of the source in our diamond layer are ready. We're able to build our data quality using simple SQL and YAML files to get get that built. And it's all running in parallel with our data processing capabilities rather
[12:37] than using a thirdparty tool that we have to manually trigger or in pipelines send out a sentinel to say hey now you can run it just automatically knows when data's changed in the tables that it care about and then it runs and outputs the exceptions that our operations team can then manage and our operations team
[12:54] can also because it's storing it in Unity catalog tables update the statuses of those exceptions to say whether the resolved false positives and the like to say okay we've actioned on these now we've audited that our data is good for our clients on a day week monthly basis
[13:11] to be certified for reporting autoloader and autoc drastically reduce our curation approach and moving forward we're also going to be looking at these new capabilities that data bicks has announced with the iceberg compatibility
[13:26] to see if we can even drastically reduce that to remove the parquet unloads to just looking at iceberg tables coming from all the different sources that support that. And then simplified ETL. Now we don't have to build massive orchestrators of our pipelines with notebook parameters and the like. We're
[13:43] just building the SQL that's needed with some simple parameters that get autotriggered based on the different types of events for triggering those pipelines. And so they're able to just listen to the source tables and auto auto build what they need rather than having to be told from a large
[13:58] orchestration unit, hey, now it's your turn to spin up a compute cluster and run. Pass it to Russ now to talk about some of the challenges and how the data bricks team has really partnered with us to address these challenges and considerations. Yeah, thanks Jake. Yeah, so there's
[14:15] certainly been um I'm going to give you a little bit of unvarnished truth. the account team with Northern Trust, you know, we were there to back them up and and get them moving. Truth be told, and I I don't even know if I've told Jake this yet, but um you know, we we watch our customer consumption a lot and and
[14:31] we do it, our account team does that specifically because we don't want them to have any big surprises when something crazy spikes. We want to get out in front of that and make sure that we can control those costs. So, out of nowhere, uh Jake's team's workspaces popped up and started consuming and we're like, "What is this? We don't even know what
[14:47] this is." And so we we we tracked them down. We figured out who who they were. And what we found was a very competent team that had a very uh deep and and technical skill set to to do these things, particularly, you know, a few key team members, their their lead architects and and Jake himself. Um so
[15:04] this brings an interesting um relationship, right? We want to be able to help them and and uh assist them as they go through some of the challenges, but it's on a sort of we'll meet you where you are type of type of basis. We don't want to force them to do things that they're not comfortable with and
[15:20] over time we just encourage them to do things like going to serverless and spark declarative pipelines and these things have assisted. That said there still are there's still a few little areas where I think because of the depth of their team they would like to see some additional observability. Um we're
[15:37] working on that with them. I know that um you know I think Dinatrace is something we're working on with them. they like to centralize all of their uh their reporting on the you know how things are running in Dinatrace. So we're helping them get that implemented. But what I really want to emphasize here
[15:53] is that um if you if you're if you're if you're when you're you know when you're operating in data bricks if you have the ability to bring a data bicks delivery solution architect on board I would encourage you to do that. uh our our data solution architect for Northern Trust, Ami Vora. She has been
[16:10] instrumental in helping them unblock uh when there was a specific um actually she worked with them directly on a few support cases. They actually helped us improve our product. uh she helped get them enabled in some areas that they needed to to get moving on and she helped clear a lot of blockers and
[16:26] provide a lot of uh sort of uh getting started type code to get these folks moved over from sort of like that DT type scenario into Spark declarative pipelines. So they can be an expensive resource but they pay for the themselves in spades. So I just wanted to plug the
[16:42] DSAs right there for a moment and and she just does a fabulous job for for Jake and and Jake's team. So, kind of getting to some of these other issues. We talked about the debugging. Um, we're we're working to actually add that observability layer to Spark declarative pipelines so that we can understand
[16:58] exactly when things uh have any kind of of issue. Um, yeah. So, they they they did have a problem with some of their queries involving a lot of tables that um prevented some of those I don't know if you guys are familiar with the enzyme capabilities where the materialized
[17:13] views have the ability to kind of update incrementally. a lot of time we were having to have some recomputee because of the complexity there and we we've we've actually made a lot of improvements in that area with their processes and yet uh there's still a little bit to go there as well. Um I'm
[17:30] not going to take Jake's thunder away. He's got a slide that talks about the benefits and the and the performance improvements that they're getting. But when they decided to go to more of a triggered approach and split SQL statements, that was really a a boon to some of their performance and and their
[17:45] uh and their u cost controls. Yeah, if I could just jump in there for a second. One of the big learnings we had when moving to DT and then to SDP actually since we started using data bricks before SDP was even a thing when it was contributed back to the spark
[18:02] community was we were still building our pipelines where our files even though you don't use notebooks anymore were built like notebooks so they had multiple operations in there and the by partnering with the data bricks team we actually learned that within spark declarative pipelines it prevers prefers
[18:17] you to have one operation per file and what happeneds s when you have one operation per file is it then can give you deep insights into the profiling of your query because it's only observing that one query. Then additionally with materialization if you have one materialization per pipeline it then
[18:34] will spin up the serverless compute specific to that materialization but if you daisy chain them in one spark declarative pipeline it can then have suboptimal performance compared to what you would expect because it's not self-scaling in the middle of that pipeline. is trying to predict before it
[18:49] spins up your serverless compute what size it is based on the pipeline observability and then it gets confused as to which one to pick. Yeah. And let me let me pick up right on that thread. So this was an important learning that I think that their team picked up which is serverless compute is
[19:06] it's a it's a learning resource. So when you run your pipelines up front the you know he talked about the planning it has to understand what needs to be done to run your pipeline and when some of their some of their more important pipelines were failing in the beginning it's because it takes a few runs for the for
[19:24] this for this infrastructure to learn what resources you need. So it would sometimes it would get hung in the planning phase or on complex flows and not have the resources. The temptation there is to stop the pipeline and you know try it again and do these things.
[19:40] You really just kind of need to take your hands off the steering wheel for just a minute and trust the system. It may fail a couple times but then it will learn what resources it needs and it will come come to the table with a bigger um you know serverless compute set next time. So that's an important thing I think that the team learned and
[19:56] it was it's actually a feature. it is absolutely designed to run that way, but you're you're you're you know the the initial reaction is to stop everything and make it quit. And what you're doing is you're not giving it the ability to finish uh what it started and and analyze the result. You're kind of interrupting it in the middle. So that's
[20:13] just an important point I wanted to bring up. Um, and then I think the last thing here, SQL develop pipelines. They're definitely an accelerator, but if you do want to do some unit testing, that does lend itself more to the pipeline side of things or sorry, the Python side of uh of the equation with
[20:29] the with the development. That said, I do think they ended up um I do think that they ended up doing uh quite a bit of of optimization on their own uh and and managing those unit tests. So Jake, thank you. I will say that you can write
[20:44] sharable functions in the SQL. So as our analysts and our domain experts P our logic when they hand it off to the engineering team to optimize for how much materialization really should exist and how to orchestrate these pipelines,
[21:00] they actually uh are also letting the engineers then figure out what's going to be reusable across different pipelines. and they're then refactoring those into reusable functions in SQL now to plug and play across different Spark declarative pipelines so that when we have to update that logic in the future,
[21:16] we're only updating it once. So where would we end up and how do we get here? Started with DT and then SDP with classic compute using raw delta lake tables. Now what we observed was there
[21:32] was a slight uptick in cost compared to managing Spark notebooks and compute clusters uh due to our learnings of how to use it and just generally some of our pipelines due to high availability and real-time availability of data as soon
[21:48] as we get it were running continuously which meant that our compute was always running and turnurning costs. But on the flip side, we were willing to incur that cost in the short term because it drastically reduced our speed to delivery. We were delivering
[22:03] pipelines fully robustly tested in uh one to two sprint cadence whereas before it was taking a lot longer to write a full endtoend pipeline. We moved from there to classic compute still but with managed catalog tables. What that did was it saved operating
[22:20] costs and running costs because those UC tables were then being managed, vacuumed, optimized with liquid clustering in Unity catalog on our behalf, which meant that we weren't scanning a ton of data outside of it and also managing our own vacuum and
[22:37] optimization jobs, which were actually running with 30-day history, but now with our UC catalog uh enterprise guidance, we vacuum on a 7-day cadence. We reduce uh engineering patterns by bringing
[22:52] things back into simple Python so that you don't have to learn the spark under the hood if you aren't don't have that background and didn't come from data engineering skill sets. And then we also like I said removed those pipelines to save a bit of cost.
[23:09] Then thirdly, where we are today. We're now using full serverless for everything possible using SDP, which immediately within the first week dropped our compute costs 40% beyond what they previously were. So that's in addition to the 20% savings from phase
[23:24] two of our delivery. We we achieved another 40% saving because those things that were continuously running are now either continuously running or triggered with serverless compute that only spins up as and when we need it and is not costing us those charges during idle windows.
[23:40] Additionally, we saw an incredible 60% speed to improvement by refactoring our pipeline so that our materialization happens individually for the serverless compute so that it optimizes the scale as it spins up in the planning phase specifically for that workload. And so now we're getting bespoke compute sizes
[23:58] for each time we need to materialize data that is catered towards that use case. This is our last slide. So I'm happy to take any questions if anybody has any.
[24:13] Happy to talk through any of the topics more.

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.