How to create table partitions in a Data Warehouse: step by step

João Barros 01 de October de 2026 4 min read

This tutorial shows how to create and manage table partitions in a Data Warehouse to improve query performance, maintenance and incremental loading. It explains why partitions are used and guides you through a practical SQL example to partition a fact table by date.

Prerequisites

Basic SQL knowledge (SELECT, CREATE TABLE, INSERT).
A Data Warehouse engine that supports partitioning (e.g., Azure Synapse, SQL Server, Snowflake, BigQuery).
A fact table with a date column (e.g., order_date).

Step 1: Why partition a table in a Data Warehouse

Partitioning physically divides data according to a column (typically date). This reduces I/O for queries that filter by that column, speeds up maintenance (removing old data) and simplifies incremental loads. Common mistakes include partitioning by too many columns or by high-cardinality fields — choosing the right column is critical.

Step 2: Choose the partitioning strategy

Decide between range partitioning (by date range), list partitioning or hash partitioning. For temporal facts, range by day/month/year is the most common. Example: partitioning by month(start) allows dropping whole monthly partitions, useful for retention and archiving.

Step 3: Create a partitioned table (T-SQL example)

Here is a simple example in SQL Server / Azure Synapse that uses partition function and partition scheme to create a fact table partitioned by month on the order_date column.

-- Criar uma function de partição por mês (valores de corte: primeiro dia de cada mês)  CREATE PARTITION FUNCTION pf_OrderDate(datetime)  AS RANGE RIGHT FOR VALUES  ('2026-01-01','2026-02-01','2026-03-01','2026-04-01');    -- Criar um partition scheme que usa filegroups (exemplo simples com PRIMARY)  CREATE PARTITION SCHEME ps_OrderDate  AS PARTITION pf_OrderDate TO (PRIMARY, PRIMARY, PRIMARY, PRIMARY, PRIMARY);    -- Criar tabela de factos particionada  CREATE TABLE factOrders (    order_id BIGINT NOT NULL,    order_date DATETIME NOT NULL,    product_id INT,    quantity INT,    amount DECIMAL(18,2)  ) ON ps_OrderDate (order_date);

Step 4: Load data into partitions and incremental load

When loading, direct the load to the correct partitions. For incremental loads, insert only data for the target period. Example of loading one month:

-- Inserir dados apenas de abril 2026 (carga incremental)  INSERT INTO factOrders (order_id, order_date, product_id, quantity, amount)  SELECT order_id, order_date, product_id, quantity, amount  FROM stagingOrders  WHERE order_date >= '2026-04-01' AND order_date < '2026-05-01';

Step 5: Manage retention by removing old partitions

To efficiently remove old data, do a SWITCH OUT or MERGE of the partition and then DROP. Example: remove January 2026 data.

-- Criar tabela temporária com mesma estrutura e sem constraints  CREATE TABLE factOrders_Archive (    order_id BIGINT NOT NULL,    order_date DATETIME NOT NULL,    product_id INT,    quantity INT,    amount DECIMAL(18,2)  );    -- Mover (SWITCH) a partição de janeiro para a tabela de archive (exemplo conceptual)  ALTER TABLE factOrders SWITCH PARTITION 1 TO factOrders_Archive;    -- Depois pode truncar ou arquivar a tabela factOrders_Archive  TRUNCATE TABLE factOrders_Archive;

Step 6: Reorganize and maintain partitions

Run regular maintenance: rebuild/reorganize indexes by partition and update statistics to keep performance. Example of rebuild for a specific partition.

-- Rebuild do índice clustered na partição 2 (exemplo)  ALTER INDEX ALL ON factOrders REBUILD PARTITION = 2;

Verify the result

Confirm that partitions are being used and that date-filtered queries are faster. Check partition metadata and execution time:

-- Listar partições e row counts (SQL Server)  SELECT p.partition_number, p.rows, rng.value AS RangeBoundary  FROM sys.partitions p  JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id  JOIN sys.partition_range_values rng ON rng.function_id = OBJECT_ID('pf_OrderDate')  WHERE OBJECT_NAME(p.object_id) = 'factOrders';    -- Teste de desempenho: comparar tempos de query com e sem filtro por order_date  SET STATISTICS TIME ON;  SELECT SUM(amount) FROM factOrders WHERE order_date >= '2026-04-01' AND order_date < '2026-05-01';  SET STATISTICS TIME OFF;

Conclusion

Partitioning tables in a Data Warehouse dramatically improves temporal queries and simplifies retention. Next steps: test other strategies (hash, list), implement automatic partitioning and monitor execution plans. Tip: start with a monthly granularity and adjust according to query patterns — which periodicity makes the most sense for your reports?