Microsoft Fabric Updates Blog

As data becomes more immediate, the gap between when an event occurs and when you must use its insights is getting smaller. Fabric Eventstreams enables users to ingest, transform, extract insights and route streaming data where it’s needed. In response to the growing demand for SQL-based transformations, we have introduced a new SQL operator feature — enabling you to apply SQL logic directly to your live data streams.

Why the SQL Operator Matters?

The SQL Operator brings the power of SQL to real-time data transformation. It enables low-code, real-time processing using familiar SQL syntax, complementing built-in no-code operators, and running on the proven Azure Stream Analytics runtime.

Implementing logic upstream minimizes post-processing latency and ensures that downstream pipelines remain streamlined. The SQL operator enables the development of complex data processing logic within Eventstream, facilitating the creation of advanced transformation rules at an early stage using the familiar SQL language. For a step-by-step guild on how to add SQL operator to your Eventstream topology, please refer to From Clicks to Code: SQL Operator under Fabric Eventstream (Preview).

With a rich collection of built-in functions, the Eventstream SQL operator simplifies most everyday data transformation and processing needs. For details on all supported SQL functions, refer to the Built-in Functions documentation. The table below gives an overview of the different data transformation functions provided by the Eventstream SQL operator. In this blog post, we will explore examples of SQL based analysis and transformation within Eventstream using some sample scenario.

Scenario

To illustrate how SQL operators support real-time data processing in Eventstream, let’s consider a practical example. Imagine an e-commerce platform where every time an order is placed, an event is generated and sent to Eventstream through a custom endpoint for real-time order analytics.

Objectives: In this scenario, we’ll demonstrate how to use the Eventstream SQL operator to accomplish these real-time data processing tasks.

1. Calculate per-minute sales totals per city and report high spikes in order of value.
2. Detect Bot Attacks in Real Time. You want to identify possible bot-like order bursts — situations where a single customer places more than 10 orders within 2 minutes.
3. Detect outlier orders — say, any order that’s more than 1.5 times the city’s 5-minute rolling average.

_{Figure1: Eventstream ingests order-created events from various regions and processes the data in real time to deliver target outcomes.}

Create an Eventstream and configure real-time ingestion with a custom endpoint. Next, in edit mode after default stream, Eventstream suggest you to either transform or add destinations. From this drop-down menu, select the SQL Code option to add data processing logic using SQL expressions. Notice that Eventstream also provides a variety of built-in no code transformations for building processing rules. You can also build powerful transformation logic using no code options. In this blog post, we will focus on the SQL-based data transformation capabilities.

Figure 2: Eventstream edit topology showing a custom endpoint as source, default stream, and SQL code option for event transformation.

Select SQL node in the topology and click Edit query button to enter the code editor experience.

Figure 3: Illustrates a SQL node included in the topology, featuring an edit query button on the right panel that opens the query editor view.

Figure 4: Query editor view of SQL operator

Inside the query editor we can analyze the incoming data, test SQL data transformation expressions and build transformation logic to complete the objectives that we have defined above. Use familiar T-SQL projections and filter expressions to analyze the incoming stream.

Let’s review our objectives to understand what data processing logic we need to define.

1. Calculate per-minute sales totals per city and report high spikes in order value.

The aim is to compute per-minute sales totals for each city and identify unusual spikes in order values. Sales totals should include the total number of orders, the total order amount, and the average order amount. To achieve this, we will add the SQL operator node to Eventstream and implement aggregation logic that calculates the required sales metrics for every city. Next, configure an Eventhouse destination named “citySalesAgg” to send these aggregated results for visualization in Power BI and Real-Time Dashboard.

SELECT
System.Timestamp AS WindowEnd,
city,
COUNT(orderId) AS OrderCount,
SUM(orderAmount) AS TotalRevenue,
AVG(orderAmount) AS AvgOrderValue
INTO
citySalesAgg
FROM
[ECommerceExample-stream]
GROUP BY
 city,
TumblingWindow(minute, 1)

Figure 5: Test results for the query in example1 are displayed in the SQL query editor, allowing for analysis and editing as necessary.

Under test results, notice that there are bigger precision values for AvgOrderValue and TotalRevenue. Let’s modify the query and round off these values.

This seems fine, but what happens if there are repeated events? Let’s analyze this example.

The query added the same event 4 times to calculate total revenue. Let’s modify the query to fix this problem.

WITH Temp AS (
    SELECT orderAmount,orderID,city
    FROM [ECommerceExample-stream]
    GROUP BY orderAmount,orderID,city, System.Timestamp()
)
SELECT
    System.Timestamp AS WindowEnd,
    city,
    COUNT(orderId) AS OrderCount,
    ROUND(SUM(orderAmount),2) AS TotalRevenue,
    ROUND(AVG(orderAmount),2) AS AvgOrderValue
INTO
    citySalesAgg
FROM
    Temp
GROUP BY
    city,
    TumblingWindow(minute, 1)

When the first statement is executed, the duplicate records are combined into one as the fields in the Groupby clause are all the same. Therefore, it removes the duplicates. SQL editor enables you to develop and test the processing logic incrementally with ease and lets to analyze the input data with test results for you to handle such cases while designing the processing logic.

2. Detecting Bot Attacks in Real-time.

The next objective is to detect potential bot-like order bursts, defined as instances where an individual customer submits more than 10 orders within a two-minute window.

To address this, we will implement a sliding window aggregate query that quantifies the number of orders placed by each customer within short time frames. In real time-streaming scenarios, performing operations on the data contained in temporal windows is a common pattern.

Next in our SQL editor, we will use this SQL query to highlight suspicious customer behavior and configure a curated stream (derived stream) containing only these flagged activities. Specifically, we will choose the Derived Stream output destination and assign it the name “Suspicious orders.”

Let’s use the following code snippet and select the Test query button to analyze the result. After validating the result, select Save to save and exit the code editor.

SELECT
    System.Timestamp AS WindowEnd,
    customerId,
    COUNT(orderId) AS OrdersPerCustomer
INTO
    SuspiciousOrders
FROM
    [ECommerceExample-stream]
GROUP BY
    customerId,
    SlidingWindow(minute, 2) -- 2-minute window
HAVING
    COUNT(orderId) >=10

Figure 6: Test results for the query in example2 are displayed in the SQL query editor, allowing for analysis and editing as necessary.

3. Detecting outlier orders

In this instance, any order that’s more than 1.5 times the city’s 5-minute rolling average. Let’s add another SQL operator and use the sql query from the code snippet. This query continuously compares new orders against their city’s rolling average — flagging anomalies in near-real time. Add Activator destination to this sql node to set alerts when we detect these anomalies.

    WITH CityAverages AS (
    SELECT
        System.Timestamp AS windowEnd,
        city,
        AVG(orderAmount) AS RollingAvg
    FROM
          [ECommerceExample-stream] TIMESTAMP BY eventTime
    GROUP BY
        city,
        SlidingWindow(minute, 5)
)
SELECT
    o.orderId,
    o.city,
    o.orderAmount,
    a.RollingAvg,
    a.windowEnd,
    CASE
        WHEN o.orderAmount > a.RollingAvg * 1.5 THEN 'Anomaly'
        ELSE 'Normal'
    END AS OrderStatus
INTO
    Activator
FROM
      [ECommerceExample-stream] o TIMESTAMP BY eventTime
JOIN
    CityAverages a
ON
    o.city = a.city
AND
    DATEDIFF(second, o, a) BETWEEN 0 AND 60

Figure 7: Test results for the query in example3 are displayed in the SQL query editor, allowing for analysis and editing as necessary.

The query used in this scenario is a multiset query where we are calculating a rolling (sliding) average order amount per city using a sliding window. Comparing each incoming order to that average. Then flagging orders that meet anomaly criteria using CASE statements. Then we join the incoming event (orderStream o) with the current rolling averages (CityAverages a) by city. As shown in the figure above, under the test result section, our query is successfully detecting the anomalies in the order of events.

We have now achieved all three objectives using the power of SQL operator available withing Fabric Eventstream. We can now click Publish on the top right corner, as shown in the figure, to deploy this real-time data pipeline and process live traffic.

Figure 8: Eventstream receives data via a custom endpoint, processes it using an SQL operator, and then writes the results to various destinations.

Summary

As demonstrated in these scenarios, adding SQL node and centralizing all business logic using SQL syntax provides significant advantages:

1. Advanced real-time data processing logic can be implemented within Eventstream, consolidating all logic in one SQL node. This greatly enhances the development experience by incrementally building complex logic and testing the result.
2. Issues can be debugged more efficiently, and outputs are validated with ease. As requirements change frequently, extending the logic, testing results, and adjusting data destinations in the pipeline becomes straightforward.

With the introduction of robust SQL support in Eventstream, data engineers and analysts are now able to move far beyond basic ingestion, filtering, or type of conversion. Eventstream is evolving into a comprehensive stream analytics platform, enabling advanced real-time solutions to be deployed directly within Fabric.

Users can aggregate, join, enrich, and analyze streaming data utilizing expressive SQL queries, which facilitates powerful capabilities such as anomaly detection, real-time metrics, and automated business actions. This marks a new era of possibilities for developing intelligent and responsive real-time data pipelines with Eventstream—delivering both flexibility and productivity through SQL. Give the SQL operator in Eventstream a try today and let us know what you think!

Get help with SQL in Fabric Eventstream

To learn more about language support please refer our MS learn page. Azure Stream Analytics has consistently employed the same SQL language semantics for many years. Users benefit from extensive community support, with a variety of solutions and patterns developed over time by stream analytics practitioners. The same knowledge base is now available for the Fabric Eventstream users to use and design real-time data processing pipelines in Fabric Real-Time Intelligence.

Need help or want to suggest an improvement?

Reach out to us on Real-Time Intelligence Forum: Get Help with Real-Time Intelligence – Microsoft Fabric Community 

Request or upvote a suggestion on Fabric Ideas RTI: Fabric Ideas – Microsoft Fabric Community