Common metrics for evaluating data quality post-ETL include completeness, accuracy, consistency, validity, timeliness, and uniqueness. These metrics help developers identify issues like missing data, incorrect values, or formatting errors that could impact downstream systems or analytics. By systematically measuring these aspects, teams can ensure the transformed data meets expectations and is reliable for use.
The first set of metrics focuses on completeness and accuracy. Completeness ensures all expected data is present after ETL. For example, if a source table has 10,000 rows, the target system should also have 10,000 rows (excluding intentional filtering). Null values in critical fields, like missing customer IDs, indicate gaps. Accuracy verifies that data matches source values or business logic. A checksum comparison between source and target datasets can detect mismatches. For instance, if an ETL process aggregates sales data, the total revenue in the target system should match the source’s sum. Sampling specific records for manual validation—like checking if a user’s address was correctly transformed—is another practical approach.
Next, consistency and validity ensure data adheres to expected formats and rules. Consistency checks verify uniformity across systems. For example, if one dataset uses “US” for country codes and another uses “USA,” this inconsistency must be resolved. Validity ensures data conforms to predefined rules, such as email formats (e.g., user@domain.com) or numeric ranges (e.g., age values above 0). Tools like regular expressions or schema validation (e.g., enforcing DATE types) automate these checks. Referential integrity—like ensuring foreign keys in a orders table map to valid customer IDs—is another critical consistency metric. Invalid or orphaned records can break downstream joins or reports.
Finally, timeliness and uniqueness address data freshness and duplicates. Timeliness measures whether data is updated within expected timeframes. For example, if an hourly ETL job runs 15 minutes late, downstream dashboards might display stale data. Tracking job execution times and latency between source and target systems helps identify bottlenecks. Uniqueness ensures no duplicate records exist where they shouldn’t. A primary key violation, such as two employees sharing the same ID, is a clear uniqueness failure. Tools like SQL GROUP BY queries or deduplication scripts can detect duplicates. Together, these metrics help maintain trust in the data pipeline and reduce errors in applications relying on the transformed data.