How to validate row counts in ELT: step by step

Validating row counts in ELT is a simple but crucial task to ensure that loads are complete and idempotent. This tutorial shows how to compare counts between source and destination, detect losses or duplicates, and automate checks with SQL and control tables.

Prerequisites

• Access to a data source (e.g.: SQL database, CSV) and a destination (e.g.: Delta, tables in a Data Warehouse).
• Permissions to read from the source and write/query the destination.
• Basic knowledge of SQL and building ELT pipelines.

Step 1: Understand the objective of the row count check

The objective is to verify that the number of records loaded into the destination matches the expected number from the source for a time window or for the entire table. This helps detect ingestion failures, missing partitions, or duplicates introduced by the ELT process.

Step 2: Define the granularity of the check

Choose whether the check will be for full load, by partition (e.g.: date), or by batch (e.g.: hour). For incremental loads, use a watermark column (e.g.: last_modified) or the date column. The granularity determines the SQL queries you will use.

Step 3: Create the count query on the source

Write a simple query on the source that returns the count by the chosen dimension. Example using SQL to count by event date:

SELECT event_date, COUNT(*) AS src_count
FROM events_source
WHERE event_date BETWEEN '2026-10-20' AND '2026-10-21'
GROUP BY event_date;

Step 4: Create the count query on the destination

On the destination table (for example a Delta table or a table in a Data Warehouse), run the same aggregation. You should use the same filtering and transformation rules applied in the ELT.

SELECT event_date, COUNT(*) AS dst_count
FROM events_dest
WHERE event_date BETWEEN '2026-10-20' AND '2026-10-21'
GROUP BY event_date;

Step 5: Compare the results and calculate the delta

Combine the two queries to obtain a comparison table with delta and percentage. This makes it easier to spot discrepancies by partition.

WITH src AS (
  SELECT event_date, COUNT(*) AS src_count
  FROM events_source
  WHERE event_date BETWEEN '2026-10-20' AND '2026-10-21'
  GROUP BY event_date
),
 dst AS (
  SELECT event_date, COUNT(*) AS dst_count
  FROM events_dest
  WHERE event_date BETWEEN '2026-10-20' AND '2026-10-21'
  GROUP BY event_date
)
SELECT
  COALESCE(src.event_date, dst.event_date) AS event_date,
  COALESCE(src.src_count, 0) AS src_count,
  COALESCE(dst.dst_count, 0) AS dst_count,
  COALESCE(dst.dst_count, 0) - COALESCE(src.src_count, 0) AS diff,
  CASE WHEN COALESCE(src.src_count,0) = 0 THEN NULL
       ELSE ROUND((COALESCE(dst.dst_count,0) - COALESCE(src.src_count,0)) * 100.0 / src.src_count,2)
  END AS pct_diff
FROM src
FULL OUTER JOIN dst USING (event_date);

Step 6: Automate the check in the ELT pipeline

Add a step in the ELT pipeline that runs the comparison query and produces a result saved to an audit table. If the difference is above a threshold (e.g.: 1%), mark the job as failed or send an alert.

-- Exemplo pseudo-SQL para inserir resultado de auditoria
INSERT INTO audit_row_counts (event_date, src_count, dst_count, diff, pct_diff, check_time)
SELECT event_date, src_count, dst_count, diff, pct_diff, CURRENT_TIMESTAMP
FROM comparison_query
WHERE COALESCE(pct_diff,0) > 1.0 OR COALESCE(diff,0) < 0;

Step 7: Handle common errors

Frequent errors include differences due to divergent filters, inconsistent time zones, transformations applied unevenly, or reordered loads causing duplicates. Check the transformations in the ELT, timestamp conversions, and whether deduplication is applied before counting.

Verify the result

Confirm that the audit_row_counts table has records for the tested window and that the diff/pct_diff fields are within the acceptable threshold. For testing, force a discrepancy (e.g.: manually load a record) and confirm that the alert is generated.

Conclusion

Validating row counts in ELT helps detect losses, duplicates, and transformation errors before they reach reports. Next steps: add sum checks (aggregated values) and unique key checks for more comprehensive coverage. Tip: start with simple partition checks and increase complexity as you gain confidence.