The Bit Bucket

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

Partitioning is one of the most effective ways to improve scalability and performance in event-driven systems. It works by dividing a single event stream into multiple independent subsets, or partitions, that can be processed in parallel.

Each partition operates independently, which allows multiple consumers or processing nodes to work simultaneously without interfering with one another. This parallelism increases throughput and helps maintain low latency even as data volumes grow.

The partition key determines how events are distributed among partitions. Common choices include device ID, user ID, or region, depending on the data model and access patterns. The key should be selected carefully:

2026-09-12

If you are aware of Betteridge’s law of headlines , you already know the answer, but let me explain.

There are a lot of odd myths that surround SQL Server. One of the more persistent ones is related to indexes on columns that hold bit values.

A SQL Server column that’s a bit can be in three states. It can have the value zero; it can have the value one; and it can be NULL (ie: have no value at all).

2026-09-11