Build real-time order notifications with Eventstream’s CDC connector
Change Data Capture (CDC) is a popular database feature for monitoring and recording all row-level changes to a table. It plays a significant role in handling transactional events, such as sales orders from an online store, and providing real-time status updates to customers. With Eventstream’s CDC connector, you can now achieve this seamlessly, capturing changes in your database in real time. You can then transform the changed data in Eventstream and route it to various destinations within Fabric for alerting and analysis.
What is change data capture?
Change data capture (CDC) provides historical change information for a table by capturing both the operations (insert, update, delete) and the changed data. It reads changes from the transaction log and places them in corresponding change tables. These change tables provide a historical view of the changes made over time to source tables.
What is Eventstream’s CDC connector?
Eventstream’s CDC connector allows you to capture changes in various types of databases in real time and transform them for notification and analysis in Fabric. After you enable the CDC feature in your database, you can simply add the corresponding database CDC connector to Eventstream, and it will start capturing the changes as soon as they occur. Here’s the list of Eventstream’s current CDC connectors:
- Azure Cosmos Database
- Azure Database for PostgreSQL
- Azure SQL Database
- MySQL Database
Build order notifications with Eventstream’s CDC connector
Let’s delve into a real-life example of how an online store use Eventstream’s CDC connector to enhance their order management system. Assume the online store uses Azure SQL Database for storing purchase orders. We’ll walkthrough the steps to add an Azure SQL CDC connector to Eventstream and process the changed data for alerting.
Here’s a sample of order table:
1. Enable CDC in Azure SQL Database
First, go to the Azure portal and select Query Editor. Execute the following SQL commands to enable CDC in the database:
SQL
-- Enable Database for CDC
EXEC sys.sp_cdc_enable_db;
-- Enable CDC for a table using a gating role option
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'orders',
@role_name = NULL
GO2. Add CDC connector for Azure SQL DB to Eventstream
Next, open an eventstream in Fabric. In the main editor, select Add external source and choose Azure SQL DB (CDC).
Enter the server address and specify the table for CDC. Then select Next to complete the configuration.
3. Process the Changed Data in Eventstream Edit Mode
When a change is made to the order table, go to Eventstream Edit mode and add a Managed Fields operation. The “payload” column in the CDC data contains “before” and “after” properties detailing the change in the source table. We’ll use the Managed Fields operation to extract the “after” property and get the “event_type” and “total_amount” fields. Next, add a Stream Destination to the eventstream for later consumption in Real-Time Hub. Finally, select Publish to commit the changes.
4. Set Up an Alert in Real-Time Hub
We want to be notified of large purchase orders (i.e., amounts greater than 1,000) to allow more time for preparation. Go to Real-Time Hub in Fabric, locate the DerivedStream, and select Set Alert. Set a condition to capture “total_amount” events greater than 1,000 and choose “Email” as the action. Select Create to complete the trigger.
Congratulations! You have successfully set up real-time notifications using Eventstream’s CDC connector to monitor online store orders. When a large purchase order is placed, you will receive a notification in your email, allowing you to promptly process the customer’s order.
To learn more about the Azure SQL CDC connector in Eventstream, visit here: Add Azure SQL Database CDC source to an eventstream – Microsoft Fabric | Microsoft Learn.
