Real-time Analytics: Streaming SQL Server Data to Fabric using Debezium

Introduction/What

This article draws a method to stream data in real-time from SQL Server to Fabric using Debezium, enabling real-time replication based on events, similar to Synapse Link.

Why

Why do we need real-time analytics? That's a question best answered by the customers who demand it. The objective is to meet this demand with little coding.

How

To achieve this, I explored various solutions and found a valuable approach shared by Alexander. He demonstrated how to bring SQL Server data to Azure Hub using Debezium and was kind enough to share his script, which I modified slightly for my purpose (Here).

SQL Server to Event Hub using Debezium

1. init.sql: This script prepares tables in CDC mode for real-time data streaming.
2. environment.ps1: This script sets up the necessary variables. Use the variables as per your requirements.
3. container 2_3.ps1: the script has all the commands for pulling a container and inserting the config into the REST endpoint. Al Tables in the "table.include.list" will move to the 'transmute' topic. I consume only one topic in KSQL *Important: I don't want to work with many many topics/ eventhubs.

Event-hub to KSQLDB

First Try: Event Hub to Event Stream

Unfortunately, the JSON format of Debezium proved incompatible, failing.

Second Try: Event Hub to KSQLDB

It was necessary to consider the input as text to read in KSQLDB. Below are the step-by-step instructions:

1.>Connect EventHub to KSQL DB: Follow the instructions provided here.

Event Hub namespaces: your link.

Event Hub: transmute.

Share Access Key Name: your Key Name.

Share Access Key: your key.

Data format: TXT

*Important: If I choose another option I will get an error.

2.>Create Materialized Views: Create materialized views for easy use (view this query in 4.query realtime.ksql).

//Stock
.create materialized-view 
with (backfill=true, effectiveDateTime=datetime(2019-01-01)) Stock1  
on table even5 
{
even5
| where payload_source.table == "Stock" and payload_op <> ""
| project this = iif(payload_op=="d", payload_before, payload_after),payload_op,payload_ts_ms
| summarize arg_max(payload_ts_ms, this.Price, payload_op) by tostring(this.Stock_Code) 
}

//Customer
.create materialized-view 
with (backfill=true, effectiveDateTime=datetime(2019-01-01)) Customer1  
on table even5 
{
even5
| where payload_source.table == "CUSTOMER" and payload_op <> ""
| project this = iif(payload_op=="d", payload_before, payload_after),payload_op,payload_ts_ms
| summarize arg_max(payload_ts_ms, this.Name, payload_op) by tostring(this.Customer_Code) 
}        

3:>Consume a Real-time Table in Power BI: Once the above steps are complete, you can consume a real-time table in Power BI by using materialized views. In this example below: Customer1, Stock1.

Conclusion

I can efficiently achieve real-time analytics with minimal coding with Fabric through Debezium.

[Thanks to Huy ??ng for the information in this post]