How to validate data lineage in ELT: step by step

Validating data lineage in ELT means proving where the data came from, which transformations it underwent and where it ended up. This helps investigate errors, meet audits and ensure trust in analyses.

Prerequisites

• An ELT environment with SQL support (e.g., Databricks, Azure Synapse, Snowflake).
• A source (raw) table and a target (trusted/silver) table in the format you prefer (Delta, Parquet, or SQL table).
• Permission to create metadata tables/files and run queries.
• Basic SQL knowledge and code versioning.

Step 1: Define the lineage elements we want to capture

Before coding, choose which metadata will be recorded: source (name, schema, timestamp), transformation (query or job id), affected columns and checksum or load hash. This makes validation practical and limited to the essentials.

Step 2: Create a lineage metadata table

Create a simple table to record each ELT run. Include id, source, target, query_hash, row_count, col_checksums and run_timestamp. Example in SQL that works on many engines:

CREATE TABLE IF NOT EXISTS lineage_metadata (
  run_id STRING PRIMARY KEY,
  source_name STRING,
  target_name STRING,
  query_hash STRING,
  row_count BIGINT,
  col_checksums STRING,
  run_timestamp TIMESTAMP
);

Step 3: Generate an identifier and hash of the transformation

Before applying the ELT transformation, generate a run_id and a hash of the query to precisely identify the logic applied. The hash helps detect regressions when the query changes.

-- Pseudocode SQL/psuedoscript
-- run_id: UUID or timestamp + jobname
SET run_id = 'run_' || CURRENT_TIMESTAMP();
-- query_text: the SQL that will perform the transformation
SET query_text = 'INSERT INTO trusted SELECT id, name, amount*1.0 FROM raw WHERE dt = ''2026-01-01''';
-- query_hash: use the engine's hash function (MD5/SHA1/sha2)
SET query_hash = MD5(query_text);

Step 4: Calculate checksums of relevant columns in the source

Compute aggregated values that represent the content of columns (for example md5(concat(col1,col2))). This allows validating that the same data arrived at the target after predictable transformations.

-- Example: calculate column checksum in the source
SELECT
  COUNT(*) AS row_count,
  MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cols_checksum
FROM raw
WHERE dt = '2026-01-01';

Step 5: Run the ELT transformation and record metrics

Execute the transformation (INSERT/CREATE TABLE AS SELECT) and immediately record in lineage_metadata the run_id, sources, target, query_hash, row_count and col_checksums calculated before or after the load.

-- 1) Transformation
INSERT INTO trusted
SELECT id, name, amount*1.0 AS amount
FROM raw
WHERE dt = '2026-01-01';

-- 2) Measure target
SELECT COUNT(*) AS row_count_target,
       MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cols_checksum_target
FROM trusted
WHERE dt = '2026-01-01';

-- 3) Insert metadata
INSERT INTO lineage_metadata (run_id, source_name, target_name, query_hash, row_count, col_checksums, run_timestamp)
VALUES (run_id, 'raw', 'trusted', query_hash, :row_count_target, :cols_checksum_target, CURRENT_TIMESTAMP());

Step 6: Compare checksums and row_count to validate lineage

Run queries that compare the checksum and row_count of the source with those recorded in the target. If they diverge, investigate: expected transformation (e.g., aggregation) may justify differences; otherwise, there is an issue.

-- Compare source vs target
WITH src AS (
  SELECT COUNT(*) AS rc, MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cs
  FROM raw WHERE dt = '2026-01-01'
), tgt AS (
  SELECT COUNT(*) AS rc, MD5(CONCAT(COALESCE(id,''), COALESCE(name,''))) AS cs
  FROM trusted WHERE dt = '2026-01-01'
)
SELECT
  src.rc AS src_rows, tgt.rc AS tgt_rows,
  src.cs AS src_checksum, tgt.cs AS tgt_checksum
FROM src CROSS JOIN tgt;

Check the result

Confirm that row_count and col_checksums are equal or that the differences are explained by the transformation logic (e.g., aggregation, filter). Also check the lineage_metadata for each run_id: it should contain query_hash and timestamp. In case of mismatch, fix the query or reprocess the load.

Conclusion

Validating data lineage in ELT gives you traceability and confidence for diagnostics and audits. Next steps: automate logging with scripts/Notebooks, integrate with CI/CD and store query_text alongside the query_hash. Tip: add a job_version field to link lineage to the transformation code; do you already have that in your process?