The Bit Bucket

Anomaly detection is a key use case for real-time analytics — it’s about automatically identifying when something unusual or unexpected happens in your data.

In most systems, normal behavior forms a fairly predictable pattern. For example, transactions follow a steady daily rhythm, CPU usage fluctuates within a typical range, and sensor readings stay within expected bounds.

Anomalies are the data points that break from those patterns — a sudden spike in temperature, a large payment outside normal limits, or a sudden burst of failed login attempts.

2026-06-24

Another Fabric Down Under podcast is out the door.

Andrew is the Founder & CEO at Blue Badge Insights, and writes about Data & Analytics. He does independent analyst work with Gigaom.

Andrew is a fellow long-term MVP and member of the Microsoft Regional Director program and he’s co-chair of SQL Server Live! and Artificial Intelligence Live!

In the podcast, we discussed semantic models and ontologies, from the beginning to the current state of Fabric IQ. Andrew also shared interesting details from the competitive (data-related) industry landscape.

2026-06-23

KQL provides a comprehensive library of functions that make it incredibly flexible for analytics. You’ll find functions across several categories — from simple text operations to advanced time-series analysis.

First, there’s a wide range of built-in functions. These include string functions like substring(), tolower(), and replace(); mathematical functions such as round(), sqrt(), and abs(); and datetime functions like now(), ago(), and datetime_diff(). These make it easy to clean, transform, and standardize your data right inside your query.

2026-06-22

For general index maintenance, our friend Old Hallengren has an awesome solution for most people: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

We started to have customers using clustered columnstore indexes from SQL Server 2014, and they needed to be treated differently. At the time, I checked Ola’s latest scripts to see what happens with columnstore indexes.

It appeared that the code ignored nonclustered columnstore indexes (ie: index type of 6), which made sense as we would potentially need to rebuild them whenever the data changes, and at the time, the table was read-only. So that made lots of sense. Fortunately, that limitation is now gone.

2026-06-21

When you start working with real telemetry data, you’ll quickly notice that not everything fits neatly into tables and columns. Much of it is semi-structured — think JSON payloads, arrays, and nested fields.

KQL provides a set of advanced operators that make handling this kind of data easy and powerful.

Operator	Purpose / Description
parse, parse_json	Extract fields from strings, logs, or JSON content
mv-expand	Expand or flatten arrays into individual rows
top-nested	Find top results within grouped categories
union	Combine results from multiple tables or streams
(Advanced tools)	Ideal for semi-structured or nested data

The parse and parse_json operators let you extract values directly from text or JSON fields. For example, if your logs contain a JSON blob, you can pull out fields like deviceId or errorCode as first-class columns you can filter and aggregate on.

2026-06-20

When I attend events like TechEd/Build/Ignite, like many people I usually find the networking time more valuable than the session time. There is a pretty tight limit on the number of sessions you can attend, no matter how hard you try. So I often watch the sessions later when I can.

At one of the earliest TechEd Australia events that I attended, they gave us DVDs of the TechEd USA sessions. That was great because that TechEd had around 250 sessions, and there were over 150 that I would have loved to attend. Clearly that wasn’t possible.

2026-06-19

Like SQL, KQL supports joins that let you combine data from multiple tables. This is a powerful way to bring together different data sources — for example, matching real-time events with reference or lookup data to give them more context.

The join operator works much like you’d expect, combining rows based on a matching key. KQL supports several join types, including inner, leftouter, rightouter, and fullouter.

Here’s a simple example:

2026-06-18

I’ve ended up with a number of Microsoft figurines over the years. It all started with Nine Guy:

Then it went on to the whole Source Force team:

It was interesting to see how they evolved over the years. Here are the SQL Server ones:

SQL Server 2005 had a demure little lady that was all Red. She morphed into an orange version by SQL Server 2008 (shown in the header with me at TechEd USA) in 2008. Later she took on a Neo look from the Matrix. More recently, there was a guy, the Query Controller.

2026-06-17

When you’re dealing with real-time or high-volume event data, one of the first challenges is scale — there’s simply too much information to interpret at the individual event level. That’s where aggregations come in. Aggregations are the process of summarizing large numbers of rows into meaningful metrics that humans can easily interpret.

For example, if you’re collecting IoT sensor readings from thousands of devices, it doesn’t make sense to inspect each data point individually. Instead, you might calculate the average temperature per minute, the total number of readings received, or the maximum pressure recorded in the last hour. Those aggregated values turn a flood of raw data into something you can analyze and act on.

2026-06-16

I recently wrote about how SQL Server related tooling has been pushing us to use constraints like primary keys, check constraints, and unique constraints without naming them ourselves. But a similar issue applies to DEFAULT constraints.

With DEFAULT constraints, the issues are slightly different.

The first issue with them is the same as with the other constraints. They can cause havoc with deployment and schema-comparison tooling unless you find and use options to ignore any system-generated names.

2026-06-15