Use data clustering in Fabric Data Warehouse (Preview)

Applies to: ✅ SQL analytics endpoint and Warehouse in Microsoft Fabric

Important

This feature is in preview.

Data clustering in Fabric Data Warehouse organizes data for faster query performance and reduced compute usage. This tutorial walks through the steps to create tables with data clustering, from creating clustered tables to checking their effectiveness.

Prerequisites

  • A Microsoft Fabric tenant account with an active subscription.
  • Make sure you have a Microsoft Fabric enabled Workspace: Create a workspace.
  • Make sure you have already created a Warehouse. To create a new Warehouse, refer to Create a Warehouse in Microsoft Fabric.
  • Basic understanding of T-SQL and querying data.

Import sample data

This tutorial uses the NY Taxi sample data set. To import the NY Taxi data into your Warehouse. Use the Load Sample data to Data Warehouse tutorial tutorial.

Create a table with data clustering

For this tutorial, we need two copies of the NYTaxi table: the regular copy of the table as imported from the tutorial, and a copy that uses data clustering. Use the following command to create a new table using CREATE TABLE AS SELECT (CTAS), based on the original NYTaxi table:

CREATE TABLE nyctlc_With_DataClustering 
WITH (CLUSTER BY (lpepPickupDatetime)) 
AS SELECT * FROM nyctlc

Note

The example assumes the table name given to the NY Taxi dataset in the Load Sample data to Data Warehouse tutorial. If you used a different name for your table, adjust the command to replace nyctlc with your table name.

This command creates an exact copy of the original NYTaxi table, but with data clustering on the lpepPickupDatetime column. Next, we use this column for querying.

Query data

Run a query on the NYTaxi table, and repeat the exact same query on the NYTaxi_With_DataClustering table for comparison.

Note

For this analysis, it's beneficial to look at the cold cache performance of both runs – that is, without using the caching features of Fabric Data Warehouse. Therefore, run each query exactly once before you look at the results in Query Insights.

We use a query that is often repeated in the Warehouse. This query computes the average fare amount by year between the dates 2008-12-31 and 2014-06-30:

SELECT
    YEAR(lpepPickupDatetime), 
    AVG(fareAmount) as [Average Fare]
FROM 
    NYTaxi
WHERE 
    lpepPickupDatetime BETWEEN '2008-12-31' AND '2014-06-30'
GROUP BY 
    YEAR(lpepPickupDatetime)
ORDER BY 
    YEAR(lpepPickupDatetime) DESC
OPTION (LABEL = 'Regular');

Note

The label option used in this query is useful when we compare the query details of the Regular table against the one that uses data clustering later using Query Insights views.

Next, we repeat the exact same query, but on the version of the table that uses data clustering:

SELECT 
    YEAR(lpepPickupDatetime), 
    AVG(fareAmount) as [Average Fare]
FROM 
    NYTaxi_With_DataClustering
WHERE 
    lpepPickupDatetime BETWEEN '2008-12-31' AND '2014-06-30'
GROUP BY 
    YEAR(lpepPickupDatetime)
ORDER BY 
    YEAR(lpepPickupDatetime) DESC
OPTION (LABEL = 'Clustered');

The second query uses the label Clustered to allow us to identify this query later with Query Insights.

Check the effectiveness of data clustering

After setting up clustering, you can assess its effectiveness using Query Insights. Query Insights in Fabric Data Warehouse captures historical query execution data and aggregates it into actionable insights, such as identifying long-running or frequently executed queries.

In this case, we use Query Insights to compare difference in data scanned between the regular and the clustered cases.

Use the following query:

SELECT 
    label, 
    submit_time, 
    row_count,
    total_elapsed_time_ms, 
    allocated_cpu_time_ms, 
    result_cache_hit, 
    data_scanned_disk_mb, 
    data_scanned_memory_mb, 
    data_scanned_remote_storage_mb, 
    command 
FROM 
    queryinsights.exec_requests_history 
WHERE 
    command LIKE '%NYTaxi%' 
    AND label IN ('Regular','Clustered')
ORDER BY 
    submit_time DESC;

This query fetches details from the exec_requests_history view. For more information, see queryinsights.exec_requests_history (Transact-SQL).

The query filters the results the following ways:

  • Fetches only rows that contain the NYTaxi text in the command name (as was used in the test queries)
  • Fetches only rows where the label value was either regular or clustered

Note

It might take a few minutes for your query details to become available in Query Insights. If your Query Insights query returns no results, try again after a few minutes.

Running this query, we observe the following results:

Table comparing query execution metrics for two labels: Clustered and Regular. The Regular query used more resources.

Both queries have a row count of 6 and similar submit times. The Clustered query shows total_elapsed_time_ms of 1794, allocated_cpu_time_ms of 1676, and data_scanned_remote_storage_mb of 77.519. The Regular query shows total_elapsed_time_ms of 2651, allocated_cpu_time_ms of 2600, and data_scanned_remote_storage_mb of 177.700. These numbers demonstrate that even though both queries returned the same results, the Clustered version used approximately 36% less CPU time than the Regular version and scanned approximately 56% less data on disk. No cache was used in either query run. These are significant results to help reduce query execution time and consumption usage, and make the lpepPickupDatetime column a strong candidate for data clustering.

Note

This is a small table, with approximately 76 million rows and 2GB of data volume. Even though this query returns only six rows on its aggregation (one for each year in the range), it scans approximately 8.3 million rows in the date range provided before results are aggregated. Actual production data with larger data volumes can provide more significant results. Your results might vary based on the capacity size, cached results, or concurrency during the queries.

Choose clustering columns from your workload

For production tables, use observed query patterns instead of guessing which columns to cluster. The operations capability of the sqldw-cli skill analyzes Query Insights history, ranks recurring query patterns by remote data scanned, and identifies columns used in WHERE predicates.

Before you start, install Skills for Fabric, make sure the warehouse has recent query activity, and confirm that you have the Contributor workspace role or higher. Then, open GitHub Copilot CLI and use a prompt like this one:

Use the sqldw-cli skill to recommend clustering columns for
<workspace-name>/<warehouse-name> based on the last seven days of workload.
Rank candidates by total remote data scanned, consider columns used in WHERE
predicates, and explain each column's cardinality and data type suitability.
Use read-only diagnostics.

The skill identifies the query patterns with the greatest scan impact, extracts the tables and filter columns from those queries, and ranks the clustering candidates. Review the recommendations with these guidelines:

  • Prefer columns that repeatedly filter large tables and use mid-to-high cardinality values, such as dates or identifiers.
  • Favor columns used in selective range or equality predicates in the WHERE clause.
  • Don't select columns only because they appear in equality join conditions. These conditions don't benefit from data clustering.
  • Use no more than four clustering columns, and don't add more columns than the workload requires.

For example, consider an e-commerce warehouse where Sales.SalesOrder contains 1.5 billion rows and Sales.OrderLine contains 6 billion rows. After analyzing recurring queries, the skill might return these recommendations:

Table of clustering recommendations. SalesOrder uses OrderDate based on 428 query runs and 38 terabytes of remote data scanned. OrderLine uses ShipDate based on 612 query runs and 52 terabytes scanned.

The date columns are strong candidates because they filter the largest tables, support common range predicates, and provide more file-skipping opportunities than low-cardinality columns such as OrderStatus or SalesRegion.

The sqldw-cli operations capability is read-only. It recommends columns but doesn't create or replace tables. After you review the recommendation, use CTAS to create a clustered copy of the table:

CREATE TABLE Sales.SalesOrder_clustered
WITH (CLUSTER BY (OrderDate))
AS
SELECT * FROM Sales.SalesOrder;

Compare the clustered and nonclustered workloads to verify the effect. After you validate the clustered table, rename the original table and then rename the clustered table to the original name:

EXEC sp_rename 'Sales.SalesOrder', 'SalesOrder_old';
EXEC sp_rename 'Sales.SalesOrder_clustered', 'SalesOrder';

The original table remains available as Sales.SalesOrder_old for rollback. Verify dependent workloads and the new table before you remove the original table. When you no longer need the rollback copy, drop it:

DROP TABLE Sales.SalesOrder_old;

Related content

  • Last updated on