Skip to main content

Lakeflow Connect: The Game-Changer for Complex Event-Driven Architectures

Summary

  • Event to direct extraction: Delaware transitioned from complex custom event architecture to Lakeflow Connect, which extracts data directly from sources to avoid data loss.
  • Data quality: Lakeflow eliminates manual SCD2 logic by automatically creating slowly-changing-dimension tables, reducing errors and improving data quality.
  • Simplified provisioning: Lakeflow Connect enables more efficient and intelligent data provisioning with less complexity than previous event-driven patterns.

Lakeflow Connect: The Game-Changer for Complex Event-Driven Architectures

Watch: Lakeflow Connect: The Game-Changer for Complex Event-Driven Architectures
Talk by Giancarlo Costa, Cloud Solutions Architect, European Food Safety Authority ; Jeroen De Clercq, Business Analyst, delaware ; Tim Bal, Senior Consultant, delaware
In 2020, Delaware implemented a state-of-the-art, event-driven architecture for EFSA, enabling a highly decoupled system landscape, presented at the Data&AI Summit 2021. By centrally brokering events in near real-time, consumer applications react instantly to events from producer applications as they occur. Event producers are decoupled from consumers via a publisher/subscriber mechanism. Over the past years, we noticed some drawbacks. The processing of these custom events, primarily aimed for process integration weren’t covering all edge cases, the data quality was not always optimal due to missing events and we needed to create a complex logic for SCD2 tables. Lakeflow Connect allows us to extract the data directly from the source without the complex architecture in between, avoiding data loss and thus, data quality issues, and with some simple adjustments, an SCD2 table is created automatically. Lakeflow Connect allows us to create more efficient and intelligent data provisioning.
Here’s more to explore:
Production ready data pipelines for analytics and AI: https://www.databricks.com/solutions/data-engineering
The Big Book of Data Engineering: https://www.databricks.com/resources/ebook/big-book-data-engineering-2nd-edition
See all the product announcements from Data + AI Summit: https://www.databricks.com/events/dataaisummit-2025-announcements

Chapters

FAQs

Why did Delaware move away from event-driven architecture?

While event-driven architecture provided decoupling, it created complexity for custom event processing, data quality issues from missing events, and complex SCD2 table logic that Lakeflow eliminates.

How does Lakeflow Connect improve data quality?

Lakeflow extracts data directly from sources without complex intermediate layers, avoiding data loss and quality issues while automatically creating SCD2 tables without manual logic.

What are the benefits of direct source extraction?

Direct extraction reduces architecture complexity, improves data quality by avoiding gaps, and accelerates time-to-value by eliminating custom event processing and SCD2 table engineering.

Can Lakeflow handle real-time data integration?

Yes, Lakeflow provides near-real-time event brokering and data integration while simplifying the architecture compared to custom event-driven approaches.

Full transcript

0:00 Introduction to EFSA and architecture

Good morning all and thank you for being here. Today we'll be speaking about uh the implementation of lakeflow conet at ESA. EPSA is the European food safety authority in Europe. This is the agenda of the session. I will give you an introduction and few words about my organization and then I will give the floor to my colleagues from Delaware a company helping my organization in the implementation our partner and they will go into the technical details of the topic.
So I'm John Carlo Costa from. I'm cloud solution architect there and uh I will I'm here with team and heron from Delaware as I already said few figures about FSA. FSA was founded in 20 2002 and uh we are about 600 staff and we focus on scientific risk assessment uh in the in in Europe and we do this through uh the help of uh several hundreds of scientific expert that are collaborating with us for the production of the scientific opinion that we use as output for our assessments.
We we run several meetings and produced several tons of opinions since the foundation. Um we are part of the network of agency in EU focusing on one health like the chemical agency in Finland, the the ECDC agency on human diseases in Sweden, uh the environmental agency in Denmark and the medicine one in in um in in uh Holland. As I said, we focus on risk assessment which is producing independent scientific advice and force the communication among the European institution promoting the scientific operation.
But we do not do risk management. Risk management it is in in the end of the commission of the parliament and of the me national authorities in the member states. How do we work? basically the commission, the parliament, the member states or even FSA itself in case we see the urgency of studying a topic. Uh we get a mandate okay and we execute the mandate with the help of a panel of expert and working scientific working group to produce our scientific assessment.
How do we what do we use? We use for sure uh people as I said we use science all the all the available scientific literature we use protocols and more and more we use data and AI because we are an evidence-based organization in uh basically in 2019 we started migration of our architecture to the cloud and uh we decided to adopt several cloud platform like uh Appion um tools from the commission, Salesforce. Okay. and we integrated them in Azure using uh real time almost real time even integration for process integration and uh for a while we continue that way but we discovered that I mean relying only on events makes then the data and analytics job of the data science not really solid and uh this is why when we heard about lakeflow connect we really started experimented with it because we wanted to have on our let's say integration cloud which is as a asure reliable data for our data scientist basically now today we speak about lakeflow connect for Salesforce which is I believe the one of the first one which went uh in in in March and uh in Salesforce basically we um have all our processes that are engaging the external stakeholders and this is an example of the open public consultation that we run in Europe with the citizen when we have a new topic we are working on it.
This is to also to increase the transparency of our of our work and the source data are brought now to our integration cloud for data analyst using liflow connect. I will now give the floor to we and he will start going into the technical details. Thank you. Okay. I'd like to walk you through how we built FS new central data platform from the ground up using the Medallion architecture. When we kicked off this project in 2020, our biggest advantage was starting with a blank slate.

5:17 Data platform and medallion architecture

We weren't tied to an existing data warehouse or legacy platform. We agreed with each of our data producers on some things up front. The format they sent the data to us, how often they sent the data to us and the path they use to send it. On the other side, our consumers were the open FSA portal and uh a suit of downstream APIs. Their needs defined our performance and data quality targets. To ingest data, we supported both batch loads and near realtime streams.
We used Azure event hubs for streaming but quickly discovered that it became more microbatch processing. And next to this, we also used Azure data factory for orchestrating the datab bricks jobs. Once the data arrived, it landed in our uh landing uh zone from and from there we applied incremental cleansing and conformance in the middle layer. Then it was called the canonical data uh model. Before producing our final tables, we then called the views analytics ready business curated data sets and finally those tables were published to a SQL database that powers the open FSA portal and the API services.
So the medallion structure we are now using everywhere at ESA follows the raw prepare surf instead of bronze, silver, gold. But what's in a name? Let me walk you through how FSA's raw prepare surf pipeline works using a simple Lego analogy. First, imagine every data producer handing us boxes of blue Lego blocks in our row zone. And we keep those blocks uh sorted by source just like shelving separate bins for source one, source two, so on.
And this gives us a clear untouched record of exactly what arrived from which provider. Next comes a prepare phase where we start organizing the pieces more meaningfully. Uh Legos can be sorted by colors, size or at ESA the function and we merge and structure the data across providers uh more around the dosiers the questions or organizations and this is where the raw pieces take shape into coherent sets that share a common context.
Finally in the surf layer we transform those organized sets into polished sculptures ready for use. Think of a completed Lego model with every block in place delivered to our data consumers where it's a dashboard, an API, the open FSA portal, and they get a fully assembled product they can trust and build on. Now, let's zoom in on how this looks in practice. We start at the left with our third party systems. We have Appan, ESFC, Oracle, EKA, Salesforce each sending data into our integration layer through API management.
As each payload arrives, we first do an envelope check to confirm its well-formed JSON. Valid message are then forwarded to a payload check where we verify if they adhere to our agreed schema and business rules. And once the me message passes both checks, it lands in the event up for downstream processing and it's also uh stored in an archive uh on a data storage. Not every source uses real-time APIs. For example, Oracle HM exports uh comes as CSV dumps at uh an adjustment we made when we encountered reli reliability issues with the live feed.
Regardless of how data arrives, our data bricks pipelines pick it up transform according to the prepare logic and then load the final results into serving layers. In short, producers feed data envelope payload validation, event injection or CSV import, data bricks processing and then to the final layers. Over time we ran into bottlenecks with event hub. its retention window uh gave some issues and it kept messages at one megabyte and some events and those became larger and larger 10 megabytes and we needed to find another way to work with it.
We started first with B 64 encoding but yeah it became a hassle and then datab bricks released autoloader and we saw an opportunity to simplify and harden our pipeline since every incoming payload was already being written to our storage account for archiving. Switching to autoloader required almost no rework and we simply stopped forwarding data to the event up after the payload check and the instead autoloader picked it up from the storage account.
Today our flow is even leaner. Data rise envelope payload validation filance in the storage account autoloader picks it up and processes it. By removing event up from the path we reduced some points of failure and improved end to end reliability. We noticed during the process that our Lego analogy didn't cover all tracks we were walking. So, let me shift gears a bit and share a new analogy. We're using a restaurant. Imagine our data producers are the ingredient suppliers and their raw ingredients arrive in our store room where we sort and organize everything by type, by freshness.

10:44 Limitations of event-driven patterns

And next, our data chefs take over, transforming those ingredients in two distinct offerings. A buffet style walking dinner. Here, consumers like PowerBI. Uh, users can browse an array of prepared components and serve themselves whatever they need. Flexible on demand access to pieces they want. Plated dishes. The other one for services like OpenSA. We deliver fully completed dishes. Complete polished data sets ready to consume without any additional assembly.
This restaurant model highlights how we go from raw inputs to both modular building blocks and finished meals, ensuring every data consumer get exactly the experience they need. But even though our event-driven architecture ticked a lot of boxes, like said, we began to uncover its limitations in practice. First, unanticipated edge cases uh kept popping up. Scenarios our initial flows didn't capture, forcing us to revisit and rework pipelines on every side just to plug the gaps.
At the same time, we saw data drift, network hiccups, misalignments meant some events simply never arrived, leaving holes in our event stream. And while we built a sophisticated logic to handle the SEDD2 uh data, it wasn't foolproof. Data producers sometimes strayed from our naming conventions. So keys in events weren't always consistent. In short, missing edge cases coverage, transmission gaps, and inconious scheas highlighted the need for a more resilient approach and that was fulfilled by LakeFlow Connect which my colleague will explain.

12:42 Transitioning to LakeFlow Connect

Thank you. In the new architecture, we implemented Leglow Connect to ingest Salesforce data as part of our transition away from a complex event-driven architecture. For ESA, it was mainly important that the data is reliable so that it can also be used for analytics. It's also important that the history of the data is preserved and more specific in a SD2 format. SEDD2 is a strategy to manage changes in data over time.
Each record has a start date and end date to specify from when to when the data is fed. When the data is updated, a new record is inserted and the old one is preserved. In the old record, the end date receives the time stamp of change, which is the same value as a start date of the new record. In the new record, the end date is not yet specified and this also specifies that the data is still valid and this will allow us to build a complete history of the data over time.
Salesforce only offers limited support for history tracking. So the new solution has to handle this constraint. For the new solution, we compared three possible solutions. The first one is LakeFlow connect. This is a connector offered by data bricks and it's built on the DT framework. So it's actually a pre-built DT pipeline. The second possible solution is Salesforce bulk API with Azure data factory. This is also a connector and uses a snapshot approach.
Lastly, we have the old solution which is Salesforce event change data capture in which we use the Azure event hub and a event hub connector to transform the events to tables on the delta lake. As you can see, LFlow connect is the best fit for ESSA. It supports incremental load of the data which allows for a minimal achievable latency of only a couple of minutes. It's also easy to implement and easy to maintain which also makes it a robust solution for the future.
SD2 is not yet supported out of the box, but it's easy to implement because layflow connect has as output streaming tables. So you can so you can use the DT framework to build a DT pipeline for streaming t uh for streaming processing so that you can add SD2 to the tables. In the first run, Layflow Connect will ingest all the data and as from the second run, it will use a reference column to check which data has already been ingested and it sees which data has been changed and will only ingest those.
For example, a reference column can be the last modified date or a system mod stamp. LakeFlow Connect is also completely serverless, so you don't have to manage a complex infrastructure by yourself. It's also multiloud supported and natively integrates with Unity Catalog, which allows for governance. Lakeflow Connect exists for Salesforce, but also for other data sources, for example, SQL Server, which is also on the road map to be implemented at ESA.
Lakeflow connect situates itself in the ingest layer and more specific it's of the type streaming. For the implementation of lakeflow connect we used three key components namely datab bricks as the central data platform in which we use lakeflow connect as connector to ingest the salesforce data and also to do the data manipulations for example adding the SC2 logic. Secondly, we used Microsoft Azure as a cloud platform for the infrastructure hosting.

16:15 Implementation and technical workflow

And lastly, we also used Terraform to deploy the resources. This allows us to create a cloud agnostic solution to be more futurep proof. In databicks, we have built a workflow that consists of three stages. The first stage is that we ingest the Salesforce data with LakeFlow connect, which is a pre-built DT pipeline. So it's a pipeline task. Secondly, we use the streaming tables to apply through a DT pipeline uh in which we use streaming processing to add the SC2 logic.
And lastly, we can also serve the data dome stream. So we are now going to show an example of uh how you can implement LFlow connect and data bricks. So in databicks we created three new resources namely two pipelines and one datab bricks job. The first pipeline is a pipeline that will be automatically created once you configure lakeflow connect through the lakeflow connect UI. Secondly we also have the pipeline to apply the SD2 logic.
Lastly we have the datab bricks job and we use both pipelines. The data bricks job is completely serverless and scheduled to run once per day. When we go to the first task of the pipeline, we have Layflow connect to ingest the Salesforce data. We can go to the LFlow connect UI and there you can specify the Salesforce connection that you want to use. Based on the Salesforce connection, you can see which objects you can ingest and you can select those that you want.

18:19 Implementation demo and SCD2 logic

You can also specify the destination that you want to use for the tables in Unity catalog. Lastly, you also have the option to specify if you want to receive any notifications if the pipeline succeeds or fails. And you can also uh add the schedule to the pipeline. LakeFlow connect underlying creates a view that contains the incremental load of the changed data. Optionally, it also creates a second view. If we have on sales for site formula fields in the table, then it will also take a full snapshot of those.
Then both views will be combined through a join and the output will be a streaming table. You can also see this uh visualized. In the second task, we will apply the SD2 logic to the streaming tables. When we go to the settings of the DT pipeline, you can see that it's also completely serverless and it and that it has a triggered pipeline mode because it will start when the first pipeline of LFlow connect is finished.
We also defined a source notebook in which we have the source code for the DT logic. When we go to the source code, we can see that we will create a list with all the tables that we have in the landing zone and we will iterate over over the tables. For each table, we create a DT view a DT view to have uh the incremental changes of the data and then we will write uh those away to a streaming table. We also use a reference column for example the last modified date to define uh based on which um the SD2 has to be built up.
Lastly, you can also add a third task to serve the tables downstream. Now we are also going to give an example in which you can see the SC2 uh logic in datab bricks. So for example, ESA has an account that is called Delaware and they want to update Delaware to Delaware consulting to make it more specific. So they go to Salesforce and they update the account name and then when the datab bricks job is triggered and when it has completed the changes will all also be reflected in datab bricks.
So you can see a new record is inserted with the updated account name and the old record is preserved. The old record hit its end date is a time stamp of change and in the new record it the start date has the same value. You can also see in the new record that the end date is not yet defined because the data is still valid. Lastly, we also used Terraform to deploy the resources in datab bricks. Terraform is a tool uh that is used for infrastructure as code that you can use to create and manage resources through APIs.

21:40 Infrastructure as code and deployment

When you when you work with Terraform, those APIs are abstracted away. Typically, a Terraform workflow consists of three stages. First, you have the Terapform right in which you define the infrastructure in a configuration file. Secondly, we have the terraform plan in which we compare the new infrastructure to the current one and then you can see if you want to apply those changes if you agree with the terapform plan.
So if you agree with the changes that will be made to your infrastructure, you can also do the terapform apply in which the infrastructure will be provisioned. At ESA, we already used Terraform to manage the infrastructure. So we only had to create three new resources, namely the two pipelines, the lakeflow connect pipeline and the SC2 pipeline and also the datab bricks job. So we are now going to give an example of how you can implement Lakeflow connect through Terraform.
So first we have the Terapform right stage. We have two configuration files. So one configuration file for the pipelines. There we have the layflow connect pipeline and the SC2 pipeline. And we also have the jobs configuration file in which we have the datab bricks job that consumes both pipelines. First we go to the pipeline configuration file. On this slide you can see the lakeflow connect pipeline reflected as a terapform resource.
You can also define the Salesforce connection like we do in the lakeflow connect UI also the destination in the catalog and the objects that you want to ingest. Secondly, we also have the SD2 pipeline and you can also see the path reflected to the source code notebook with the ST2 logic. Lastly, we have the jobs configuration file in which we have the new databicks job. You can also see in the tasks in the first two that the lakeflow connect pipeline and the SC2 pipeline are reflected.
You can also define the schedule that you want to use for the pipeline and for example also if you want to receive any notifications if a datab bricks job succeeds or fails. Secondly, we have the terform plan at ESA. We use Azure DevOps pipelines to deploy resources to deploy the workflows across the different environments. We also integrate Terraform in those pipelines. In this example, you can see the Terapform plan.
The Terapform the Terapform plan shows that a new pipeline will be created. In this example, it's the Lakeflow Connect pipeline. If we agree with the terraform plan, so if we agree with the changes that will be made to the infrastructure, we can also do the terapform apply. Terraform apply will provision the infrastructure. So the resources will be created in datab bricks. So this was our presentation. Thank you for coming to our session.

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.