Demo: SQL Server connector from Lakeflow Connect

Summary

• SQL Server connectivity: Lakeflow Connect's SQL Server connector enables retailers to build efficient incremental pipelines for analyzing customer purchase behavior from SQL Server.
• Business insights: Retailers can analyze IoT data, sales transactions, inventory, and supply chain patterns while combining multiple customer interaction channels.
• Unified governance: Incremental data pipelines maintain full integration with Databricks' governance, orchestration, and observability features from source to insight.

Demo: SQL Server connector from Lakeflow Connect

In this demo, we will explain how a retailer uses the SQL Server connector from Lakeflow Connect to understand customer purchase behavior. We will also use Databricks Apps to build a user-facing application to do the analytics. Then, we will look at how the retailer gets data, analyzes their customer order data, and combines it with customers’ interactions across the retailer's various channels for a holistic view.

Chapters

• 0:00 Introduction to LakeFlow
• 1:04 Use case and prerequisites
• 2:37 Configuring ingestion pipeline
• 7:25 Data transformation and workflow
• 9:19 Analytics and dashboarding
• 11:00 Conclusion and resources

FAQs

Why is analyzing customer purchase behavior important for retailers?

Understanding purchase patterns helps retailers personalize marketing, optimize inventory placement, anticipate customer needs, refine pricing strategies, and identify loyal versus at-risk customers for targeted retention.

How does Lakeflow Connect handle SQL Server data efficiently?

The connector uses change tracking or change data capture (CDC) depending on SQL Server version and configuration, enabling efficient incremental data pipelines instead of full data extracts.

What data sources can retailers combine for better insights?

Retailers can analyze customer order data and combine it with customer interactions across various channels (online, in-store, mobile) to get a holistic view of customer behavior.

How does governance work with SQL Server connector pipelines?

Lakeflow Connect integrates incremental pipelines with full Databricks Data and AI platform governance, orchestration, and observability from source to final insight.

Full transcript

0:00 Introduction to LakeFlow

Hello, I'm a solutions architect here at datab bricks. We recently launched LakeFlow Connect to provide simple built-in data connectors for popular databases, enterprise applications, and file sources. Data teams can now use these connectors to build efficient incremental pipelines at scale with easy setup and maintenance and full integration with the datab bricks data intelligence platform and experience the a unified orchestration observability and governance.
The new SQL Server connector helps businesses easily access and derive insights directly from SQL Server all within the databix platform. You can easily unlock many high value use cases such as analyzing IoT data, sales transactions, inventory, supply chain, etc. Today we are going to focus on one of the following use cases. It's customer purchase behavior analysis. Customer purchase behavior is crucial in the retail industry.

1:04 Use case and prerequisites

It helps businesses understand what, when, why, and how consumers buy products. By analyzing these patterns, retailers can personalize marketing and offers. Optimize inventory and product placement to match the demand. Improve customer experience by anticipating needs. Enhance pricing and promotion strategies based on buying trends and identify loyal versus churn risk customers for targeted retention efforts. Ultimately, understanding purchase behavior drives smarter decisions, better customer satisfaction, and higher profitability.
In this demo, we will see how a retailer uses the SQL server connector to understand customer purchase behavior. We will use the capability of data bricks apps to build a userfacing application as you can see here to do the analytics. We'll look at how the retailer gets data, analyzes their customer order data, and combines it with customers interactions across retailers various channels for a holistic view. Before we begin, we have to ensure that the following prerequisites are met.
Your databicks workspace has to be enabled for Unity catalog. Serverless compute should be enabled for jobs and pipelines. Appropriate permissions should be granted for creating and using connections within data bricks. Before we create the injection pipeline, first we need to configure the SQL server database to allow databicks to ingest data efficiently. This involves two key steps. One, enable change tracking or change data capture depending on how your SQL server version and table configurations are.

2:37 Configuring ingestion pipeline

Enable either change tracking or CDC. This allows database to detect and process data changes incrementally. We recommend doing change tracking as it has less performance impact on the source database. The second step is creating a dedicated database user. The database user has the necessary permissions to read the data and monitor changes. It's recommended to create SQL server users specifically for data bricks injection granting only the necessary read and execute permissions.
To create the injection pipeline, one first needs to make sure that the data connection to a SQL server is already created as I'm showing here in the databicks portal. The connections can be uh seen in catalog catalog explorer and connections and we have already set up a demo connection to begin our injection. We will now see how you can easily set up an injection pipeline directed to SQL server using databics connect simple UI.
This is the database console. You have to click on data injection and you can see all the available sources from where you can ingest the data. You can see SQL server here along with other connectors available. You can click on this and a simple UI will prompt you to perform the steps. Enter the details and you will be able to set up the connector in few minutes. I have already set up a pipeline and uh the connection configuration.
We will walk through this. The first step is creating an injection gateway for source databases with a name and target catalog for staging injection data as well as the target data and there's a schema also mentioned here. Next step is creating an injection pipeline where you can see um there is a pipeline name the destination catalog and the connection that we were seeing earlier as the source connection. Next step is uh getting the source details the source SQL server database that we are getting the data from.
But we are going to ingest this database and any of the tables we can pick and choose but we are going to get the entire database ingested. The final step is to define the destination and where the data source data would land. We can also look at the settings and we can see that we have a uh option to schedule this job. You can put it as uh the required frequency and also add notifications to the email recipients you would want on success and failure.
Let's save and close. Once the pipeline is created and executed, after a successful execution, the pipeline status would look something like this where you can clearly see the name of all the ingested data sets. what type of data sets they are, the duration to ingest the data sets and other details around the ingestion like written records, upserted records, deleted records. You will also get a full visibility of the event log um with all types of information, warnings, errors.
This takes benefit of all the outstanding features that lakeflow declarative pipeline offers. And this also provides you a graph view to monitor, track and get a view of all the ingested data sets. Once the data is ingested, the data will be available in the databicks catalog. You can browse it through the catalog explorer. Go to the necessary target database and you will see all the tables and data sets that were ingested.

7:25 Data transformation and workflow

Since now all the customer order data is ingested, we can work on transforming the data. We can combine this data with other existing tables in the databix platform to gain further insights such as demographic data about the customers and their interactions with retailer on their apps. And all of this can be ingested from other sources as well. We transform the data and create a aggregate data set using a simple pipeline which is shown here.
It's a simple lake for declarative pipeline where we are getting different types of data that we gather from the SQL server database and we are doing a business transformation to create a final data set that we will use for further analytics. We can also create a databix job or a workflow tying both the pipelines together. This is the injection task. The injection pipeline that we saw earlier that gets the data into databicks from SQL server and a task that refers to the pipeline that is used for transforming the data and creating a final data set.
We can create a workflow integrating both of them. This will help us monitor the entire pipeline end to end. Now the data is ingested and transformed and ready for analytics. Data insights can be analyzed in a dashboard or genie space all within data bricks. Databicks offers wonderful way of doing analysis and getting insights of data. Using these two simple features as part of our AIBI offering, we can leverage the capabilities of database genie spaces to conduct a thorough analysis of the provided and generated data sets.

9:19 Analytics and dashboarding

The primary objective of this analysis is to gain comprehensive understanding of customer purchasing behavior. As you can see, you can write simple prompts to get insights and also a quick visual to get more better understanding of the data here in Genie spaces. Upon completion of the analysis, we can design and develop an interactive dashboard that effectively visualizes the key insights and trends pertaining to the customer p purchase patterns which can be seen here where simple click and uh prompting creates a beautiful EIBI dashboard that can give you more insights of the data after the analysis is done through Genie spaces.
And finally, we create a databicks app that refers to the generated data set to give us the app that we showed in the beginning, which gives you a much more interactive way to interact with the data and give you beautiful visuals to understand the data and its insights. To wrap up, you have now seen the new lakeflow connect for a SQL server in action. A built-in connector for ingesting data directly from the data from SQL server into data bricks.

11:00 Conclusion and resources

Data teams can now easily build efficient incremental pipelines at scale with a simple UI or API with easy setup and maintenance. It's important to note that all of these steps can be completed within the UI. But if you prefer coding instead of clicking, there is also an API that does the exact same thing and it integrates with the database asset bundles. Businesses can now derive rich insights of by unifying all their data AI assets on the database data intelligence platform.
Everyone can unlock high value use cases such as predicting customer churn as well as many others including IoT analysis, supply chain, inventory and many more. Running personalized customer analytics and identify cross-ell and upsell opportunities as well. To get started with LakeFlow Connect, go to databicks. com and get more details.

Learn more about the Databricks Data and AI platform.