How do you verify the integrity of data after ETL completion?

To verify data integrity after ETL completion, developers use a combination of automated checks, reconciliation processes, and manual validation. The goal is to ensure data accuracy, consistency, and completeness between the source and target systems. This involves comparing counts, validating transformations, and checking for anomalies that might indicate errors during extraction, transformation, or loading.

First, automated validation scripts are essential. These scripts can verify row counts between source and target datasets to confirm no data was lost or duplicated. For example, if a source table has 10,000 records, the target table should also have 10,000 after ETL. Checksums or hash values can be used to validate data content. By generating a hash (e.g., MD5 or SHA-256) of source data and comparing it to the hash of the transformed data, developers can detect unintended changes. Additionally, schema checks ensure columns, data types, and constraints (e.g., NOT NULL, unique keys) match expectations. Tools like Great Expectations or custom SQL queries can automate these validations and flag discrepancies.

Second, business rule validation ensures transformations align with requirements. For instance, if a column is supposed to contain uppercase values, a script can scan the target data for compliance. Aggregation tests (e.g., verifying sum of sales in source matches target) or date format checks (e.g., ensuring all timestamps are in UTC) are common. For complex transformations, sampling specific records helps verify logic. If a field combines first and last names, spot-checking a subset of records confirms the concatenation worked correctly. Unit tests for transformation logic, written during development, can also be rerun post-ETL to catch regressions.

Finally, reconciliation and monitoring tools provide ongoing assurance. Logging errors during the ETL process (e.g., failed rows due to invalid data) and reviewing those logs helps identify systemic issues. Tools like Apache Airflow or AWS Glue can track job success and failure rates. For critical datasets, running a secondary reconciliation process hours after ETL completion—such as comparing daily totals in the source system with the target—catches latency or duplication issues. Combining automated checks with periodic manual reviews (e.g., auditing 1% of records monthly) ensures long-term data reliability. This layered approach minimizes the risk of undetected data corruption.