The Bit Bucket

Machine learning plays an increasingly important role in real-time analytics. Instead of only using models in batch workflows, many systems now apply them directly to streaming data - allowing predictions, classifications, or anomaly detection as events occur.

In this model, each incoming event - such as a transaction, sensor reading, or social media post - can be scored in-flight by a machine learning model. For example, a transaction can be classified as potentially fraudulent, an IoT signal can be checked for signs of equipment failure, or a social post can be analyzed for sentiment.

2026-09-22

In an earlier post , I discussed the design of EAV (Entity Attribute Value) tables, and looked at why they get used. I’d like to spend a few moments now looking at the pros and cons of these designs.

Let’s use the same table as the last time as an example:

Pros

The main positive that’s typically described is that the schema is flexible. By this, the developers usually mean I don’t have to change the database schema (or worse, have someone else change it) when my needs change.

2026-09-21

Update policies in KQL (and thus in Fabric) are a mechanism to automatically transform ingested data as it arrives.

Rather than separately ingesting raw data and then running background jobs to clean, route, or enrich it, an update policy allows you to declare a transformation query that runs whenever new data lands in a source table.

The transformation query writes its output into a target table. The source and target must exist in the same database. The schema (column types and order) of the transformation output must match what the target table expects.

2026-09-20

If you’ve been working with databases for any length of time, you will have come across implementations of Entity-Attribute-Value (EAV) tables (or non-tables as some of my friends would call them).

Instead of storing details of an entity as a standard relational table, rows are stored for each attribute.

For example, let’s create a table of people:

USE tempdb;
GO

DROP TABLE IF EXISTS dbo.StaffMembers;
GO

CREATE TABLE dbo.StaffMembers
(
    StaffMemberID int NOT NULL
        CONSTRAINT PK_dbo_StaffMembers PRIMARY KEY,
    FullName nvarchar(100) NOT NULL,
    HairColor nvarchar(20) NULL,
    StartDate date NULL,
    LoyaltyPoints int NULL
);
GO

INSERT dbo.StaffMembers
(
    StaffMemberID, FullName, HairColor, StartDate, LoyaltyPoints
)
VALUES
(1, N'Fred Nurk', N'Blonde', '20250705', 3),
(2, N'Siew Yu Hock', N'Black', '20250709', 2),
(3, N'Abhishek Newma', N'Brown', '20250802', 2);
GO

When we query it, all is as expected:

2026-09-19

KQL offers a wide range of specialized functions designed to handle advanced analytical tasks — particularly useful in real-time and large-scale telemetry environments.

Some of the most important belong to three broad groups:

• Anomaly and pattern detection: Functions like series_decompose_anomalies() and series_outliers() identify unusual trends or values within time series data. These are often used in monitoring, fraud detection, or IoT scenarios where unexpected changes may signal issues.
• Time series and multivalue operations: Functions such as make-series and mv-expand let you group data into time bins and expand array-like columns for further analysis. These help with trend visualization, forecasting, and interval-based reporting.
• Geospatial and temporal enrichment: The geo_* family (e.g., geo_distance_2points(), geo_point_to_h3cell()) allows spatial analysis, such as tracking asset locations or mapping activity by region.

2026-09-18

Many database developers (and other developers) seem to regard the endless use of abbreviations as some badge of honor. Don’t be one of these people.

Avoid abbreviations almost all the time.

I’ve written before about my dislike for the EOMONTH T-SQL statement. Given the same version introduced names like DATETIMEOFFSETFROMPARTS, surely we didn’t have to save 3 characters and could have had ENDOFMONTH. (I heard it was in fact named this way to match the Excel function. I hope that’s not true. Matching something from another language that was created a long time ago isn’t the right answer here).

2026-09-17

In KQL, the union operator can combine results from multiple tables, even when those tables follow a naming pattern. This is particularly useful in environments where data is split across many similar tables — for example, logs_2024_01, logs_2024_02, and so on.

You can use wildcards with the union operator to simplify these queries. Instead of listing each table individually, you can write:

union withsource=TableName logs_*

This automatically brings together all tables whose names begin with logs_.

2026-09-16

When designing databases, one question that comes up all the time is how large columns should be.

Numbers

For numbers, the answer is always big enough but not too big. This week I’ve been working at a site where the client numbers were stored in int columns. Given the clients are Australians and the Australian Bureau of Statistics Population Clock says there are just under 25 million of us, an integer seems a pretty safe bet, given it can store positive numbers up over two billion. It’s hard to imagine that number being exceeded, but I’ve seen people deciding that it needs to be a bigint. I doubt that. Even if we count all our furry friends, we aren’t going to get to that requirement.

2026-09-15

In Fabric, KQL supports querying across multiple databases or even across different clusters.

This feature is especially valuable when data is distributed across teams, regions, or functional domains — for example, separate clusters for telemetry, transactions, and logs.

A cross-database query allows you to access tables in more than one KQL database within the same cluster. This is often used when analytical data and operational data are stored separately but need to be correlated — such as linking user activity logs with transaction histories.

2026-09-14

Time to read

I was on a flight from Houston to Washington via Dallas a while back. We seemed to sit forever on the tarmac in Dallas, in fact just over 1.5 hours from leaving gate to taking off. It was amazingly silly. We were 18th in the takeoff queue and the airport required the planes to keep turning their engines off after moving each time. The captain (who was clearly fed up) warned us that if he had to stop and restart the engines one more time, we’d have had to return to the terminal for more fuel. And we hadn’t even left Houston.

2026-09-13