How to validate checksums in ELT to detect regressions

João Barros 06 de October de 2026 6 min read

This tutorial shows how to implement and validate checksums (hashes) in ELT to detect regressions and data corruptions during loads. Validating checksums helps ensure integrity, facilitates idempotent reprocessing and reduces surprises in production. I will explain the reason for each step and give concrete examples: for instance, how to manage a set of 100k–1M rows and which metrics to monitor (percentage of change, processing time).

Prerequisites

Environment with SQL (e.g.: Azure Synapse, Databricks SQL, PostgreSQL) or Spark/Databricks.
A source table with simple columns (strings/numbers) or Parquet/CSV files. Ideally start with a small table (~10k rows) to validate the process before scaling to 100k–1M.
Permission to create temporary tables and execute hash functions (MD5/SHA1/SHA256). SHA256 is recommended for collision resistance (256 bits).
Basic knowledge of SQL or PySpark. At minimum know how to run SELECT, JOIN and create simple tables.

Step 1: Choose the checksum strategy

Decide whether the checksum will be per row or per partition/file. The most common approach is to generate a hash per row based on the columns that represent the business key and the attributes to protect. For tables with 100k–1M rows, per-row calculation is easy to store (an additional column) and allows detecting which records changed.

Avoid including columns with ingestion timestamps if you want idempotency: if you include a field like ingestion_time each run will produce different checksums even without logical changes. Alternative: generate two checksums — row_checksum (business attributes) and ingest_checksum (includes metadata) — for diagnosis.

Step 2: Generate per-row checksum in SQL

Example in SQL using SHA256 concatenating columns in deterministic order. Includes COALESCE for NULL values and a consistent separator. Note: SHA256 produces 256 bits; the probability of collision is negligible for usual volumes (<10^9 rows).

-- Exemplo SQL (adaptar nome de tabela/colunas)
SELECT
  id,
  col1,
  col2,
  col3,
  LOWER(CONVERT(VARCHAR(64), HASHBYTES('SHA2_256',
      CONCAT(COALESCE(col1,''|'') , '||' , COALESCE(CAST(col2 AS VARCHAR),''), '||' , COALESCE(col3,''))
  ), 2)) AS row_checksum
FROM source_table;

In systems where HASHBYTES does not support large strings, serialize only the critical fields (e.g.: 5–10 columns) or use native functions like sha2 in Databricks. Test with a sample of 1k–10k rows to measure time: on modest clusters generating 100k SHA256 checksums usually takes between 10–60s.

Step 3: Generate per-row checksum in PySpark

Example in PySpark/Databricks using SHA2. Keep column order and handle NULLs explicitly. PySpark scales well: a cluster with 4 cores can process 1M rows in 1–3 minutes depending on transformation complexity.

# Exemplo PySpark
from pyspark.sql.functions import sha2, concat_ws, coalesce, lit, col

df = spark.table('source_table')
df_with_checksum = df.withColumn('row_checksum',
    sha2(concat_ws('||', coalesce(col('col1'), lit('')), coalesce(col('col2').cast('string'), lit('')), coalesce(col('col3'), lit(''))), 256)
)
df_with_checksum.createOrReplaceTempView('source_with_checksum')

Step 4: Store checksums and metadata

Create an audit table that stores key, checksum, source_run_id and source_timestamp. Also store the number of rows and a global hash per partition/file to diagnose corruptions in large files. For example, for 1M rows, an audit_checksums table with 1M records is trivial to store in a columnar format (Parquet/Delta).

CREATE TABLE audit_checksums (
    id STRING,
    row_checksum STRING,
    source_run_id STRING,
    source_timestamp TIMESTAMP
);

INSERT INTO audit_checksums
SELECT id, row_checksum, 'run_20261006_01', CURRENT_TIMESTAMP FROM source_with_checksum;

Example of a global hash per partition: aggregate the row_checksums ordered and compute a final hash. This detects file-level differences even if the row count is the same.

Step 5: Compare checksums between runs

To detect regressions perform a JOIN between the current table and the previous audit table: different checksum => change; missing in current => deletion; missing in previous => new row. Analyze percentages: for example, if >0.1% of rows appear as CHANGED in a daily process it may be acceptable in some scenarios, but if >1% it warrants investigation.

-- Exemplo SQL de comparação
WITH prev AS (SELECT id, row_checksum AS prev_checksum FROM audit_checksums WHERE source_run_id = 'run_prev'),
     curr AS (SELECT id, row_checksum AS curr_checksum FROM source_with_checksum)
SELECT
  COALESCE(curr.id, prev.id) AS id,
  CASE
    WHEN prev.id IS NULL THEN 'NEW'
    WHEN curr.id IS NULL THEN 'DELETED'
    WHEN prev.prev_checksum != curr.curr_checksum THEN 'CHANGED'
    ELSE 'UNCHANGED'
  END AS status
FROM prev FULL OUTER JOIN curr ON prev.id = curr.id;

Step 6: Handle false positives and normalization

Common errors: differences from column order, whitespace, different types or irrelevant columns. Normalize strings (trim, lower), sort collections before serializing and convert types to avoid false positives. For example, use LOWER(TRIM(col_name)) and convert decimals to a fixed-precision format before hashing.

-- Normalização simples em SQL
LOWER(TRIM(col_name))
-- Em PySpark usa trim(lower(col('col')))

Also define a threshold for automatic alerts: for example, if NEW + DELETED + CHANGED > 0.5% of daily rows, create an alert and launch a deep validation job.

Verify the result

Validate by running the comparison between two sample runs: introduce an intentional change in a row and verify the status appears as CHANGED; remove a row and verify DELETED; add a row and verify NEW. Also confirm that rows with only the ingestion timestamp different appear as UNCHANGED if that field is not included in the checksum. Record metrics: computation time, number of changes and relative percentage to build a history (e.g. daily average of 0.03% changes).

Conclusion

Implementing checksums in ELT is a simple and powerful technique to detect regressions, validate integrity and facilitate idempotent reprocessings. Practical next steps: automate the loading of audit logs, create alerts when the change percentage exceeds a threshold and generate checksums per partition to validate large files. Tip: start by protecting a small set of critical tables (3–5 tables) and iterate as you find false positives; within 2–4 weeks you will have a robust mechanism that reduces manual investigations and increases confidence in loads.