What are common data quality issues encountered in ETL workflows?

Common data quality issues in ETL workflows often stem from inconsistencies, errors, or gaps in source data, which can disrupt downstream processes. These problems typically arise during extraction, transformation, or loading phases and require careful handling to ensure reliable outcomes. Below are three key categories of issues developers encounter, along with practical examples.

First, data completeness and consistency are frequent challenges. Missing values, null entries, or incomplete records can lead to gaps in analysis. For example, a customer address field might be empty in a sales database, making shipping impossible. Inconsistent formats, such as dates stored as “MM/DD/YYYY” in one system and “YYYY-MM-DD” in another, complicate transformations. Similarly, a numeric “product ID” arriving as a string with leading zeros (e.g., “00123”) might fail to match integer IDs in another table. Schema mismatches, like a column expected to be a integer arriving as a string, can also break workflows unless explicitly handled.

Second, data accuracy and duplication are critical concerns. Invalid entries, such as a phone number containing letters, can render fields unusable. Business rules might be violated—for instance, a discount percentage exceeding 100% in a pricing feed. Duplicate records, such as a customer appearing twice due to merge errors or overlapping source systems, skew reporting. For example, merging CRM and billing data might create two entries for “John Doe” (e.g., “john.doe@company.com” vs. “jdoe@company.com”), requiring deduplication logic. Without checks, these issues propagate into analytics, leading to incorrect aggregations or insights.

Third, timeliness and data integrity issues affect reliability. Outdated data, like stale product prices from a delayed source, can cause financial discrepancies if loaded into a reporting system. Data integrity problems, such as foreign key mismatches (e.g., an order referencing a nonexistent customer ID), break referential constraints. For example, loading sales transactions without validating customer IDs against a master list could orphan records. Constraints like “age must be non-negative” might also be violated if raw data contains invalid values (e.g., "-30" for age). These issues often surface during loading and require validation rules to enforce consistency.

Developers address these challenges by implementing validation checks, standardization rules, and deduplication logic during transformations. Proactive monitoring and testing at each ETL stage help mitigate risks, ensuring data remains trustworthy for downstream applications.