How to validate date partitions in ELT: step by step

João Barros 18 de September de 2026 4 min read

This tutorial shows how to validate date partitions in ELT to ensure consistency, avoid gaps and improve query performance. Validating date partitions is useful to detect load failures, corrupted files and retention issues before they affect reports or downstream pipelines.

Prerequisites

Access to an environment with file/Delta support (for example: Data Lake with Delta Lake or parquet) and SQL/Notebooks.
Tools to run SQL or Python (for example: Databricks, Synapse, or another environment with Spark/SQL).
Basic knowledge of ELT, date partitioning (date, year/month/day) and SQL/Python commands.

Step 1: Identify the partition scheme

Understand how the data is partitioned: by date, year/month/day or by another convention. This allows building specific checks (gaps, invalid formats, metadata).

# Exemplo SQL para ver estrutura de partição (Delta ou Hive metastore)  DESCRIBE DETAIL nome_da_tabela;  -- ou  SHOW PARTITIONS nome_da_tabela LIMIT 100;

Step 2: List expected partitions

Generate the list of dates that should exist in the period of interest. This helps detect gaps (missing partitions) in the load window.

# Exemplo Python (Spark) para gerar datas esperadas entre start_date e end_date  from pyspark.sql.functions import sequence, to_date, explode, lit  start = '2026-09-01'  end = '2026-09-10'  dates_df = spark.sql(f"SELECT explode(sequence(to_date('{start}'), to_date('{end}'), interval 1 day)) as dt")  dates_df.show()

Step 3: Get actual partitions

Extract the partitions actually present in the storage/metastore. For Delta/Hive, we can query the partitions or infer them from file paths.

# Exemplo SQL para obter partições reais (tabela particionada por dt)  SELECT DISTINCT dt FROM nome_da_tabela ORDER BY dt;  -- Ou listar ficheiros no diretório de data: /data/tabela/dt=YYYY-MM-DD/

Step 4: Compare expected vs actual and report gaps

Join the expected and actual dates to identify missing days. Report results for alerts or backfill triggers.

# Exemplo Spark SQL juntando datas esperadas e reais  dates_df.createOrReplaceTempView('expected_dates')  spark.sql("""
SELECT e.dt as date_expected, r.dt as date_present
FROM expected_dates e
LEFT JOIN (SELECT DISTINCT dt FROM nome_da_tabela) r
ON e.dt = r.dt
WHERE r.dt IS NULL
ORDER BY e.dt
""").show()

Step 5: Check file integrity per partition

Detect corrupted files or files with different schema that cause read errors. We can try to read each partition and capture exceptions; also validate number of files and minimum size.

# Exemplo Python para validar leitura por partição e contar ficheiros  from pyspark.sql.utils import AnalysisException    partitions = [r['dt'] for r in spark.sql("SELECT DISTINCT dt FROM nome_da_tabela").collect()]  bad_partitions = []  for p in partitions:      path = f"/data/nome_da_tabela/dt={p}"      try:          df = spark.read.format('delta').load(path)  # ou parquet          cnt = df.count()          if cnt == 0:              bad_partitions.append((p, 'empty'))      except Exception as e:          bad_partitions.append((p, str(e)))    print('Partições com problemas:', bad_partitions)

Step 6: Validate metadata and statistics per partition

Check key columns (e.g.: id, timestamp) for null values, unexpected types or outliers per partition. Calculating basic statistics helps detect regressions.

# Exemplo SQL para checar nulos e contar por partição  SELECT dt,         COUNT(*) as rows,         SUM(CASE WHEN id IS NULL THEN 1 ELSE 0 END) as id_nulls,         MIN(event_time) as min_time,         MAX(event_time) as max_time  FROM nome_da_tabela  GROUP BY dt  ORDER BY dt;

Verify the outcome

Confirm that:

There are no missing dates in the analyzed window (empty result from Step 4).
The bad_partitions list from Step 5 is empty or only contains explainable cases.
Partition statistics (Step 6) are within expected limits (rows, id_nulls, min/max ok).

Conclusion

Validating date partitions in ELT prevents load failures and performance degradation. Next steps: automate these checks as part of the pipeline (scheduled jobs) and integrate alerts/rollback for partitions with failures. Tip: start by monitoring a short window (7-14 days) to reduce false positives and tune thresholds.