Getting Started with Data Clustering in Microsoft Fabric Data Warehouse Preview

Data clustering is one of the most powerful performance features added to Microsoft Fabric Data Warehouse. It organizes your data physically in storage so that rows with similar values stay close together — and that drives two big wins: dramatically faster queries and lower Capacity Unit consumption on large datasets.

If you are a data engineer or analytics professional working with Fabric, understanding how to use data clustering will make your analytics workloads faster and cheaper. This article walks through how it works, when to use it, the CLUSTER BY syntax with examples, and practical workshop-ready scripts you can run today.

How Data Clustering Works

At its core, data clustering changes how rows are stored on disk during ingestion. Instead of landing rows in arbitrary order, Fabric uses a space-filling curve algorithm to organize rows so that similar values in your chosen clustering columns end up physically close together in storage.

This is not just a sort order like a traditional clustered index. It is a physical layout change that preserves data locality across multiple dimensions simultaneously — something a simple B-tree index cannot do. The clustering metadata is embedded in the storage manifest during ingestion, allowing the warehouse engine to make intelligent decisions about which files to access during user queries.

The result is reduced I/O, lower CPU, and lower Capacity Unit consumption — especially on large fact tables where scanning everything would be expensive.

When to Use Data Clustering

Not every table benefits equally from clustering. Here are the scenarios where it delivers the biggest gains:

CLUSTER BY Syntax and Examples

Data clustering is defined at table creation time using the CLUSTER BY clause. It cannot be added to an existing table after creation.

CREATE TABLE with Clustering

-- Create a table clustered on CustomerID and SaleDate
-- Rows with similar CustomerID and SaleDate values will be stored close together
CREATE TABLE Sales
(
    SaleID     INT,
    CustomerID INT,
    SaleDate   DATE,
    Amount     DECIMAL(10, 2)
)
WITH (CLUSTER BY (CustomerID, SaleDate));

CREATE TABLE AS SELECT (CTAS) with Clustering

-- Create a clustered copy of an existing table
-- Useful for testing clustering impact without dropping the original
CREATE TABLE SalesClustered
WITH (CLUSTER BY (SaleDate))
AS
SELECT * FROM Sales;

Key Rules for CLUSTER BY

• You can specify between one and four clustering columns
• Clustering must be defined at creation time — it cannot be added to an existing table
• The order of columns in CLUSTER BY does not affect storage layout — the space-filling curve algorithm handles multi-dimensional co-location regardless of column order
• Clustering adds ingestion overhead because data must be organized during load — batch your inserts for best results
• For best ingestion efficiency, aim for at least one million rows per batch

Supported Data Types

Only certain column types can be used in CLUSTER BY. Note that while VARCHAR(MAX) and VARBINARY(MAX) are now supported for storage in Fabric Data Warehouse (since November 2025), they remain unsupported as clustering columns.

How to Inspect Existing Clustering

Use system views to see which tables have clustering defined and which columns are used:

-- View all clustering definitions in the warehouse
SELECT
    t.name                          AS table_name,
    c.name                          AS column_name,
    ic.data_clustering_ordinal      AS clustering_ordinal
FROM sys.tables t
JOIN sys.columns c
    ON t.object_id = c.object_id
JOIN sys.index_columns ic
    ON c.object_id = ic.object_id
    AND c.column_id = ic.column_id
WHERE ic.data_clustering_ordinal > 0
ORDER BY t.name, ic.data_clustering_ordinal;

This returns every table that has clustering defined, the column(s) used, and their ordinal position in the CLUSTER BY definition.

Workshop: Measure the Difference

This workshop creates a clustered and non-clustered version of the same table and measures the query performance difference using Query Insights.

-- Create a clustered copy keyed on pickup datetime
CREATE TABLE nyTaxi_clustered
WITH (CLUSTER BY (lpepPickupDatetime))
AS
SELECT * FROM nyTaxi;

-- Query against the non-clustered table
SELECT
    YEAR(lpepPickupDatetime)   AS pickup_year,
    AVG(fareAmount)            AS avg_fare
FROM nyTaxi
WHERE lpepPickupDatetime BETWEEN '2008-12-31' AND '2014-06-30'
GROUP BY YEAR(lpepPickupDatetime)
OPTION (LABEL = 'No Clustering');

-- Query against the clustered table — same logic, same filter
SELECT
    YEAR(lpepPickupDatetime)   AS pickup_year,
    AVG(fareAmount)            AS avg_fare
FROM nyTaxi_clustered
WHERE lpepPickupDatetime BETWEEN '2008-12-31' AND '2014-06-30'
GROUP BY YEAR(lpepPickupDatetime)
OPTION (LABEL = 'With Clustering');

-- Query Insights T-SQL view for recent execution history
SELECT
    distributed_statement_id,
    label,
    status,
    data_scanned_remote_storage_mb,
    total_elapsed_time_ms,
    command
FROM queryinsights.exec_requests_history
WHERE label IN ('No Clustering', 'With Clustering')
ORDER BY submit_time DESC;

-- Check that your key queries filter on the clustering column
-- If your most common query looks like this, clustering on SaleDate is appropriate:
SELECT * FROM Sales WHERE SaleDate BETWEEN '2025-01-01' AND '2025-03-31';

-- If your most common query looks like this, clustering on SaleDate is NOT helpful:
SELECT * FROM Sales WHERE CustomerName = 'Acme Corp';
-- Consider clustering on CustomerID (higher cardinality) instead

Data Compaction and Clustering

Fabric Data Warehouse runs a background data compaction service that reorganizes small files into larger, more efficient ones. This process works alongside clustering and is important to understand when designing ingestion patterns.

Best Practices

• Choose clustering columns based on real WHERE predicates from your actual query workload — not theoretical ones
• Prefer date, datetime2, and integer ID columns — high cardinality and commonly used in range filters
• Do not add more clustering columns than necessary — one or two well-chosen columns usually outperform four poorly chosen ones
• Batch ingestion with at least one million rows per load — small batches reduce the effectiveness of the clustering algorithm
• Use Query Insights to measure before and after — validate the improvement is real before moving to production
• Plan for the lack of ALTER support — if you choose the wrong clustering columns, you must recreate the table using CTAS
• For string columns, remember only the first 32 characters are used in statistics — very long string values with similar prefixes may not cluster as effectively as you expect
• This feature is currently in Preview — test thoroughly in non-production before deploying to critical workloads

Final Thoughts

Data clustering in Microsoft Fabric Data Warehouse is a practical and powerful optimization for analytic workloads. By colocating similar rows physically in storage using a multi-dimensional space-filling curve, the query engine can skip entire files that do not match a filter predicate — turning expensive full-table scans into efficient targeted reads.

The gains are most dramatic on large fact tables with selective date range or ID-based filters — exactly the queries that power dashboards and reports. Lower scanned data also means lower Capacity Unit consumption, which translates directly to lower cost.

References

• Microsoft Learn – Data Clustering in Fabric Data Warehouse
• Microsoft Learn – Tutorial: Use Data Clustering in Fabric
• Microsoft Learn – Query Insights Overview
• Microsoft Learn – Performance Guidelines in Fabric Data Warehouse
• Microsoft Fabric Blog – Announcing Data Clustering (Preview)
• Microsoft Fabric Blog – November 2025 Feature Summary