Use data clustering in Fabric Data Warehouse

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

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

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.

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');

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

Running this query, we observe the following results:

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.

Related content

• Data Clustering in Fabric Data Warehouse
• Query insights in Fabric Data Warehouse
• Monitor Fabric Data Warehouse
• Use query labels in Fabric Data Warehouse