Partitioned Tables in Azure Synapse

How partitioning works in Synapse

In a Synapse dedicated SQL pool, data is distributed across compute nodes (based on the table’s distribution strategy). Within each node, data is further divided into partitions — physical segments of data determined by the value of the partition column. When your query includes a WHERE clause that filters on the partition column, the engine reads only the partitions that could contain matching rows and skips the rest. This is called partition elimination.

For a table with 1 TB of data partitioned by month over 3 years (36 partitions), a query filtering on a single month scans roughly 28 GB instead of 1 TB. That is a 97% reduction in I/O.

The interaction between distribution and partitioning is important to understand. If you have 60 compute nodes and 36 monthly partitions, each node holds 36 partition segments. The total number of partition segments across the whole pool is 60 × 36 = 2,160. Each segment is a separate rowgroup in the clustered columnstore index.

Choosing the right partition key

The partition key should be the column you most commonly filter on in WHERE clauses and the one that creates large, roughly equal-sized segments. In practice, this almost always means a date column for fact tables.

Creating a partitioned table

In Synapse dedicated SQL pools, partitioning is defined with a RANGE RIGHT or RANGE LEFT boundary on a date or integer column. Most date-based partitions use RANGE RIGHT, which means a boundary value belongs to the partition on its right (i.e., a boundary of ‘2025-01-01’ starts a partition that includes January 1st).

-- Create a fact table partitioned by month using a DATE column
CREATE TABLE dbo.FactOrders
(
    order_id        BIGINT          NOT NULL,
    customer_id     INT             NOT NULL,
    product_id      INT             NOT NULL,
    order_date      DATE            NOT NULL,
    quantity        INT             NOT NULL,
    unit_price      DECIMAL(10, 2)  NOT NULL,
    order_total     DECIMAL(18, 2)  NOT NULL,
    region          NVARCHAR(50)    NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(customer_id),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION
    (
        order_date RANGE RIGHT FOR VALUES
        (
            '2023-01-01', '2023-02-01', '2023-03-01', '2023-04-01',
            '2023-05-01', '2023-06-01', '2023-07-01', '2023-08-01',
            '2023-09-01', '2023-10-01', '2023-11-01', '2023-12-01',
            '2024-01-01', '2024-02-01', '2024-03-01', '2024-04-01',
            '2024-05-01', '2024-06-01', '2024-07-01', '2024-08-01',
            '2024-09-01', '2024-10-01', '2024-11-01', '2024-12-01',
            '2025-01-01', '2025-02-01', '2025-03-01', '2025-04-01',
            '2025-05-01', '2025-06-01', '2025-07-01', '2025-08-01',
            '2025-09-01', '2025-10-01', '2025-11-01', '2025-12-01'
        )
    )
);

This creates one partition per calendar month from January 2023 through December 2025 (36 partitions) plus two implicit boundary partitions (one for dates before 2023-01-01 and one for dates after 2025-12-31). You should always add future boundary dates before they arrive — adding boundaries to an existing table with data is possible but requires a SPLIT RANGE operation.

The partition switch pattern for fast loads

Partition switching is the fastest way to load large amounts of data into a partitioned production table. Instead of running INSERT statements row by row, you load data into an identical staging table, then swap the partition in a single metadata operation — near-instantaneous regardless of data volume.

-- Step 1: Create a staging table with the SAME structure as the production table
-- but using ROUND_ROBIN for fast loading
CREATE TABLE dbo.stg_FactOrders_202501
(
    order_id        BIGINT          NOT NULL,
    customer_id     INT             NOT NULL,
    product_id      INT             NOT NULL,
    order_date      DATE            NOT NULL,
    quantity        INT             NOT NULL,
    unit_price      DECIMAL(10, 2)  NOT NULL,
    order_total     DECIMAL(18, 2)  NOT NULL,
    region          NVARCHAR(50)    NOT NULL
)
WITH
(
    DISTRIBUTION = HASH(customer_id),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION
    (
        order_date RANGE RIGHT FOR VALUES ('2025-01-01', '2025-02-01')
    )
);

-- Step 2: Load January 2025 data into the staging table
COPY INTO dbo.stg_FactOrders_202501
FROM 'https://mydatalake.dfs.core.windows.net/silver/orders/year=2025/month=01/*.parquet'
WITH (FILE_TYPE = 'PARQUET', CREDENTIAL = (IDENTITY = 'Managed Identity'));

-- Step 3: Switch the staging partition into the production table
-- This is a metadata-only operation — no data movement
ALTER TABLE dbo.stg_FactOrders_202501
    SWITCH PARTITION 2 TO dbo.FactOrders PARTITION 25;
-- Partition 25 of FactOrders corresponds to 2025-01-01 (count from the boundary list)

-- Step 4: Drop the now-empty staging table
DROP TABLE dbo.stg_FactOrders_202501;

The SWITCH operation is atomic — it either succeeds completely or does nothing. Users querying the production table see the old data up until the switch completes, then see the new data immediately. There is no window where partial data is visible.

Verifying partition elimination

You can confirm that the query engine is actually eliminating partitions by looking at the estimated execution plan or using the built-in partition metadata DMVs:

-- Check partition row counts and sizes for a table
SELECT
    p.partition_number,
    p.rows,
    SUM(a.total_pages) * 8 / 1024.0 AS size_mb
FROM
    sys.tables t
    JOIN sys.indexes i ON t.object_id = i.object_id AND i.type IN (0, 1)
    JOIN sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE
    t.name = 'FactOrders'
GROUP BY
    p.partition_number, p.rows
ORDER BY
    p.partition_number;

A query like SELECT COUNT(*) FROM dbo.FactOrders WHERE order_date BETWEEN ‘2025-01-01’ AND ‘2025-01-31’ should show in the execution plan that only partition 25 is accessed, not all 38. If you see all partitions being scanned, the query optimiser is not eliminating — usually because the filter column is being implicitly converted (e.g., comparing a DATE column to a VARCHAR constant).

Repartitioning an existing table with CTAS

If you inherited a table with poor partitioning (or no partitioning), use CTAS (CREATE TABLE AS SELECT) to rebuild it with a better scheme. This avoids locking the original table for long operations:

-- Create a new table with the desired partitioning from an existing unpartitioned table
CREATE TABLE dbo.FactOrders_Partitioned
WITH
(
    DISTRIBUTION = HASH(customer_id),
    CLUSTERED COLUMNSTORE INDEX,
    PARTITION
    (
        order_date RANGE RIGHT FOR VALUES
        (
            '2023-01-01', '2024-01-01', '2025-01-01', '2026-01-01'
        )
    )
)
AS
SELECT * FROM dbo.FactOrders_Old;

-- After validating, rename tables to swap them
RENAME OBJECT dbo.FactOrders_Old TO FactOrders_Archive;
RENAME OBJECT dbo.FactOrders_Partitioned TO FactOrders;

CTAS reads the source and writes the destination in parallel across all compute nodes. For a 500 GB table on a DW500c pool, this typically takes 10–20 minutes.