Last month, I inherited a reporting system that was perpetually six hours behind reality. The ETL jobs ran overnight, which meant the operations team spent their mornings looking at yesterday's problems. When I suggested we move to real-time processing, the response was immediate: "How fast can you get it running?"

Turns out, faster than I expected. Microsoft's Azure Stream Analytics paired with SQL Server gave us a pipeline that processes events as they arrive and lands them in a familiar database structure. No Kafka clusters to manage, no complex stream processing frameworks to learn—just a SQL-like query language and Azure's infrastructure handling the scaling.

Here's how I built it, including the configuration details and gotchas I wish someone had told me upfront.

What We're Building

The pipeline has four main components:

• Event Hub receives the raw event stream. Think of it as a high-throughput message queue built for millions of events per second.
• Stream Analytics processes events in flight using SQL-like queries. It can filter, aggregate, join with reference data, and detect patterns across time windows.
• SQL Server (or Azure SQL Database) stores the processed results. Once data lands here, your existing BI tools and applications can query it normally.
• Power BI visualizes the data in near-real-time dashboards.

For this walkthrough, I'll use a transaction monitoring scenario: we want to detect users making an unusual number of transactions in a short time window and alert when thresholds are exceeded.

Setting Up Azure Event Hub

Event Hub is where your data enters the pipeline. You'll need an Event Hub namespace first (the container), then a hub within it (the actual stream).

In the Azure portal, create an Event Hub namespace. The key configuration decisions you need to make are:

• Pricing tier: Standard gives you multiple consumer groups and longer retention. Basic works for proof-of-concept, but you'll outgrow it quickly.
• Throughput Units (or Processing Units in Premium): Start with 2. Each unit gives you 1 MB/sec ingress. You can enable auto-inflate to scale up automatically when traffic spikes. Once the namespace is created, add an Event Hub inside it. Name it something descriptive like transaction-events.
• Partition count: I started with 4 partitions. Partitions are independent ordered sequences—more partitions mean more parallelism but also more complexity in consumer ordering. Four is a good middle ground for moderate traffic. You can't change this later without creating a new hub, so err on the high side if you expect growth.
• Message retention: Set to 1 day initially. This determines how long Event Hub keeps events if a downstream consumer falls behind. In production, I bumped this to 7 days after we had a Stream Analytics job fail over a weekend and needed to replay events. Under "Shared access policies," create a policy with Send and Listen permissions. Copy the connection string—you'll need it for both producing events and configuring Stream Analytics.

Sending Events to the Hub

Before we can process a stream, we need a stream. Here's a C# console app that simulates transaction events:

using Azure.Messaging.EventHubs;
using Azure.Messaging.EventHubs.Producer;
using System.Text.Json;

var connectionString = "Endpoint=sb://your-namespace.servicebus.windows.net/;SharedAccessKeyName=...";
var eventHubName = "transaction-events";

await using var producer = new EventHubProducerClient(connectionString, eventHubName);

Console.WriteLine("Sending transactions. Press Ctrl+C to stop.");

var random = new Random();
var userIds = Enumerable.Range(1, 20).Select(i => $"user{i:D3}").ToArray();

while (true)
{
    // Create a batch of events
    using var eventBatch = await producer.CreateBatchAsync();
    
    for (int i = 0; i < 10; i++)
    {
        var transaction = new
        {
            UserId = userIds[random.Next(userIds.Length)],
            TransactionId = Guid.NewGuid().ToString(),
            Amount = random.Next(10, 500),
            MerchantCategory = new[] { "retail", "dining", "travel", "entertainment" }[random.Next(4)],
            EventTime = DateTime.UtcNow.ToString("o") // ISO 8601 format
        };

        var eventData = new EventData(JsonSerializer.Serialize(transaction));
        
        if (!eventBatch.TryAdd(eventData))
        {
            // Batch is full, send it
            await producer.SendAsync(eventBatch);
            break;
        }
    }

    await producer.SendAsync(eventBatch);
    await Task.Delay(100); // ~100 events/second
}

A few notes on this producer:

• EventTime as ISO 8601 string: Stream Analytics will parse this as a datetime. I initially used Unix timestamps and spent an hour figuring out why my windows were off by decades.
• User distribution: We're cycling through 20 users with random transaction frequencies. This lets us test threshold detection without waiting hours for natural patterns.
• Batching: The EventHubProducerClient batches events automatically for efficiency. In production, you'd tune batch size based on your latency requirements.

Run this, and you'll see events flowing into Event Hub. You can verify in the Azure portal under the hub's "Metrics" section—watch the "Incoming Messages" graph.

Configuring Stream Analytics

Create a new Stream Analytics job in Azure. The configuration has three main sections: inputs, outputs, and the query itself.

For Connecting to Event Hub, Add an input to the job. Here are a few things to configure:

• Input alias: Transactions (this is how you'll reference it in queries)
• Source type: Event Hub
• Subscription and Event Hub: Point to your namespace and hub
• Consumer group: Use $Default for now. In production, create a dedicated consumer group so Stream Analytics doesn't interfere with other consumers.
• Event serialization format: JSON
• Encoding: UTF-8

The critical setting is the Event serialization format. Choose JSON and expand the settings:

Timestamp: Select "EventTime" from the dropdown. This tells Stream Analytics which field in your JSON represents when the event actually occurred, not when it arrived at Event Hub. This distinction matters for out-of-order events.

If you don't set this, Stream Analytics uses arrival time, and your time-based aggregations will be skewed by network delays.

The Stream Analytics Query

This is where the magic happens. Stream Analytics uses a SQL-like language with time-aware extensions. Let's start with a simple query and build up. This query will detect High-Frequency Users:

SELECT
    UserId,
    COUNT(*) AS TransactionCount,
    SUM(Amount) AS TotalAmount,
    System.Timestamp() AS WindowEnd
INTO
    HighFrequencyAlerts
FROM
    Transactions TIMESTAMP BY CAST(EventTime AS datetime)
GROUP BY
    UserId, 
    TumblingWindow(minute, 5)
HAVING
    COUNT(*) > 10
```

Let me explain each piece:

• **TIMESTAMP BY CAST(EventTime AS datetime)**: This overrides the event time at the query level. I'm casting because EventTime comes in as a string. You could also handle this in the input configuration, but I prefer keeping timestamp logic visible in the query.
• **TumblingWindow(minute, 5)**: This creates non-overlapping 5-minute buckets. Every event belongs to exactly one window. Visualize it like this:

Events: |---|---|---|-|-|--------|---|--|
Window 1: [12:00:00 ------- 12:04:59]
Window 2: [12:05:00 ------- 12:09:59]
Window 3: [12:10:00 ------- 12:14:59]

When a window closes (at 12:04:59, for example), Stream Analytics emits one row per UserId summarizing that 5-minute period.

• System.Timestamp(): Returns the window end time. This is the timestamp assigned to the output row.
• HAVING COUNT(*) > 10: Only emit rows for users who exceeded 10 transactions in the window. This keeps the output focused on anomalies.
• INTO HighFrequencyAlerts: This is the output destination. We'll configure this next.

Save the query. You can test it using the "Test query" feature in the portal—upload a JSON file with sample events and see what outputs.

The next query is for oOverlapping Windows for Trend Analysis. Sometimes you want a "sliding window" effect. For instance, to show "transactions in the last 10 minutes" updated every minute:

SELECT
    UserId,
    COUNT(*) AS TransactionCount,
    SUM(Amount) AS TotalSpent,
    AVG(Amount) AS AvgTransactionAmount,
    System.Timestamp() AS WindowEnd
INTO
    UserSpendingTrends
FROM
    Transactions TIMESTAMP BY CAST(EventTime AS datetime)
GROUP BY
    UserId,
    HoppingWindow(minute, 10, 1)
```

**HoppingWindow(minute, 10, 1)**: The first parameter is window size (10 minutes), the second is hop size (1 minute). This creates overlapping windows:
```
Window 1: [12:00 ------------ 12:10]
Window 2:   [12:01 ------------ 12:11]
Window 3:     [12:02 ------------ 12:12]

Each event appears in multiple windows. Use this when you want smooth, continuous updates rather than discrete jumps every N minutes.

The tradeoff: more output rows (one per hop interval per user) and slightly higher processing cost.

The third query lets us join with Reference Data. You can enrich streams with static or slowly-changing data. For example, flag transactions from high-risk merchant categories. First, upload a reference file (CSV or JSON) to Blob Storage:

[
  {"MerchantCategory": "gambling", "RiskLevel": "high"},
  {"MerchantCategory": "crypto", "RiskLevel": "high"},
  {"MerchantCategory": "retail", "RiskLevel": "low"},
  {"MerchantCategory": "dining", "RiskLevel": "low"}
]

Add a second input to your Stream Analytics job, type "Blob storage / ADLS Gen2," point to this file. Set the path pattern and serialization format.

Now join in your query:

SELECT
    t.UserId,
    t.TransactionId,
    t.Amount,
    t.MerchantCategory,
    r.RiskLevel,
    t.EventTime
INTO
    RiskFlaggedTransactions
FROM
    Transactions t TIMESTAMP BY CAST(t.EventTime AS datetime)
INNER JOIN
    MerchantRiskReference r
ON
    t.MerchantCategory = r.MerchantCategory
WHERE
    r.RiskLevel = 'high'

This outputs only transactions in high-risk categories. The reference data is cached in memory and refreshed periodically (configure refresh interval in the input settings).

Output: Landing in SQL Server

Create an Azure SQL Database (or use an existing SQL Server instance). In your database, create the target table:

CREATE TABLE dbo.HighFrequencyAlerts (
    UserId NVARCHAR(50) NOT NULL,
    TransactionCount INT NOT NULL,
    TotalAmount DECIMAL(18,2) NOT NULL,
    WindowEnd DATETIME2 NOT NULL,
    DetectedAt DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
    INDEX IX_WindowEnd_UserId CLUSTERED (WindowEnd DESC, UserId)
);

SELECT
    *,
    System.Timestamp() AS ProcessedAt
INTO
    DebugBlob
FROM
    Transactions TIMESTAMP BY CAST(EventTime AS datetime)

Configure the output to write to Blob Storage with JSON line-delimited format. Now you have a paper trail of every event and can manually verify your aggregations.

Connecting Power BI

Once data is in SQL Server, Power BI can visualize it. The trick is using DirectQuery mode (not Import) so the dashboard refreshes automatically. In Power BI Desktop:

1. Get Data ? Azure SQL Database
2. Enter your server and database
3. Choose DirectQuery (not Import mode)
4. Write a query that fetches recent alerts:

SELECT 
    UserId,
    TransactionCount,
    TotalAmount,
    WindowEnd
FROM dbo.HighFrequencyAlerts
WHERE WindowEnd >= DATEADD(minute, -30, GETUTCDATE())
ORDER BY WindowEnd DESC

Create a table visual with these columns. Then go to File ? Options ? Report settings and set the refresh rate to 1 second (minimum). Now your dashboard updates in near-real-time as Stream Analytics writes rows.

A critical gotcha: DirectQuery means every visual refresh hits SQL Server. If you have 10 visuals refreshing every second, that's 10 queries/second. Keep queries simple, indexes tight, and consider pre-aggregating data in SQL if performance becomes an issue.

For complex dashboards, I created a second Stream Analytics query that pre-aggregates at multiple levels (per user, per hour, per day) and writes to separate SQL tables. Power BI queries those pre-aggregated tables instead of hitting the raw alerts.

Troubleshooting from Experience

If a query shows no output, check that:

• The job is running (not stopped or failed)
• Events are arriving at Event Hub (check Metrics)
• Your TIMESTAMP BY field is correctly formatted
• Windows have actually closed (tumbling windows don't emit until the window ends)

If the output is delayed by several minutes, this is usually the watermark delay growing. Scale up SUs or simplify your query.

If you find duplicate rows in SQL. either you have multiple Stream Analytics jobs writing to the same table, or you scaled up SUs and didn't account for parallel writes. Add a unique constraint if you need deduplication, but be aware of potential deadlocks.

For SQL connection timeouts, Stream Analytics retries failed writes, but if SQL is consistently slow, events back up in Event Hub. Scale up your SQL tier or optimize your indexes.

If you have the wrong time zone in results, remember that the System.Timestamp() returns UTC. If you need local time in SQL, do the conversion there with AT TIME ZONE.

When to Use This Architecture

This setup shines when:

• You need sub-minute latency from event to dashboard
• Your processing logic fits SQL-like queries (filtering, aggregating, joining)
• You already have SQL Server expertise and BI tools that expect relational data
• You want managed infrastructure without maintaining Kafka or Spark clusters

It's not ideal for:

• Complex machine learning in the stream (use Azure ML or Databricks for that)
• Ultra-high throughput (millions of events per second—look at Kafka + Spark)
• Stateful processing that requires precise guarantees (Stream Analytics is at-least-once delivery)

For us, the sweet spot was fraud detection and operational monitoring. We detect anomalies in seconds, land alerts in SQL where the fraud team already works, and push real-time dashboards to Power BI. The entire stack is managed, scales automatically, and integrates with our existing tooling.

Final Thoughts

Real-time analytics used to mean building and maintaining complex infrastructure. Azure Stream Analytics collapses that complexity into a managed service with a SQL-like interface. The learning curve is measured in hours, not weeks.

The key is understanding the fundamentals: partitions for parallelism, windowing for time-based aggregations, and the tradeoff between latency and accuracy when handling late events. Once those click, you're just writing queries.

Start small. Ingest one event stream, write one simple query, output to SQL. Get that working, then add complexity. The pipeline I described here took me three days to build and test, including the mistakes and dead ends. Your mileage will vary based on your data volume, but the path is straightforward.

The hardest part isn't the technology—it's deciding what questions to answer in real-time. Not everything needs sub-second latency. But when you find the use cases that do, this architecture delivers without the overhead of a dedicated stream processing team.