How to partition Delta tables in ELT for fast queries

João Barros 17 de July de 2026 4 min read

This guide shows how to partition Delta tables in ELT to improve query performance and reduce read costs. Learning to choose the partition column, create the partitioned table and optimize files avoids issues like many small files and unbalanced partitions.

Prerequisites

Access to an environment that writes/executes SQL over Delta (e.g.: Databricks, Spark SQL).
Raw data already loaded into a table/staging area (e.g.: mydb.raw_events).
Permissions to create tables and run OPTIMIZE/VACUUM (if applicable).

Step 1: Choose the right partition column

Why: a poor choice creates partitions with too many or too few files and worsens performance. Best practices: prefer columns with moderate cardinality and frequent use in filters (WHERE). For temporal data, using dates (by day, month) is common in ELT.

-- Exemplos de colunas a avaliar
-- COUNT(*) grouped para ver cardinalidade
SELECT event_date, COUNT(*) AS cnt
FROM mydb.raw_events
GROUP BY event_date
ORDER BY event_date DESC
LIMIT 50;

Step 2: Create the partitioned Delta table

Explanation: create the table in Delta format and declare PARTITIONED BY. Here we do a CTAS (CREATE TABLE AS SELECT) to materialize event_date derived from a timestamp.

CREATE TABLE mydb.events_partitioned
USING delta
PARTITIONED BY (event_date)
AS
SELECT *, to_date(event_ts) AS event_date
FROM mydb.raw_events;

Step 3: Load incremental data into the partitioned table

Explanation: for ELT it is common to do transformations in the SELECT and insert. Before inserting, repartition the DataFrame/query by event_date to reduce small files. In Spark SQL/Databricks you can use INSERT INTO with a SELECT repartitioned via the spark API (example in PySpark) or SQL with a hint.

-- SQL simples (se o engine controlar escrita):
INSERT INTO mydb.events_partitioned
SELECT *, to_date(event_ts) AS event_date
FROM mydb.raw_events_incremental;

-- Exemplo PySpark para controlar número de ficheiros por partição:
df = spark.table('mydb.raw_events_incremental')
df = df.withColumn('event_date', to_date(col('event_ts')))
# Reparticionar por event_date para optimizar escrita por partição
df.repartition('event_date').write.format('delta').mode('append').saveAsTable('mydb.events_partitioned')

Step 4: Compact files and optimize (compaction)

Common problem: many small files per partition. Solution: compact using OPTIMIZE (Databricks) or write with coalesce before save. OPTIMIZE is useful in Delta to create larger files and reduce metadata overhead.

-- Exemplo Databricks: optimizar por tabela e, opcionalmente, ZORDER para colunas de consulta
OPTIMIZE mydb.events_partitioned
WHERE event_date = '2026-07-01';

-- Agregar manualmente com repartition/coalesce (antes de escrever nova carga)
df.repartition(1, 'event_date').write.format('delta').mode('overwrite').option('overwriteSchema','true').saveAsTable('mydb.events_partitioned')

Step 5: Manage partition evolution and null partitions

Common errors: inserts with event_date NULL create unexpected _delta_log directories or partitions with NULL value. Handle NULLs during transformation and, if changing granularity (day→month), create a new column and backfill.

-- Filtrar ou substituir NULLs
INSERT INTO mydb.events_partitioned
SELECT *, coalesce(to_date(event_ts), date('1970-01-01')) AS event_date
FROM mydb.raw_events_incremental;

-- Para mudar partição (ex.: day -> month) cria-se nova tabela e CTAS com new_month
CREATE TABLE mydb.events_by_month
USING delta
PARTITIONED BY (event_month)
AS SELECT *, date_format(event_date,'yyyy-MM') AS event_month FROM mydb.events_partitioned;

Verify the result

Run queries and metadata commands to confirm that partitions exist, files are compacted and queries use partition filters.

-- Ver partições existentes
SHOW PARTITIONS mydb.events_partitioned;

-- Ver detalhe da tabela Delta
DESCRIBE DETAIL mydb.events_partitioned;

-- Testar se o filtro usa apenas uma partição (EXPLAIN):
EXPLAIN SELECT COUNT(*) FROM mydb.events_partitioned WHERE event_date = '2026-07-01';

-- Verificar tamanho médio de ficheiros por partição (exemplo simplificado)
SELECT event_date, SUM(size) as total_bytes, SUM(file_count) as files
FROM (DESCRIBE HISTORY mydb.events_partitioned) -- se o engine expuser metadados
GROUP BY event_date ORDER BY event_date DESC LIMIT 20;

Conclusion

Partitioning Delta tables in ELT improves latency and reduces costs when done with a good choice of column, file control and maintenance (OPTIMIZE/VACUUM). Next steps: measure the impact with real queries, automate compaction and test changing granularity. Tip: start partitioning by day and monitor the file distribution — have questions about which granularity to use for your volume?