Most data quality problems that analysts discover in reports and dashboards didn't originate in the warehouse — they originated upstream, in the ETL process. By the time a data quality failure reaches a dashboard, it's typically been transformed, aggregated, and distributed into dozens of downstream calculations. The failure is cheap to fix at the ETL layer; expensive to fix in the warehouse.
Where Quality Fails at Each ETL Stage
At Extraction
Extraction pulls data from source systems. Quality failures here include:
Incomplete extraction: The query or API call returns fewer records than expected — because of a time filter that missed records near the boundary, an API limit that was hit, or a connection timeout that truncated the pull.
Stale extraction: Data pulled from a cache or snapshot that's hours old rather than the current source. The extraction "succeeded," but the data reflects an old state.
Schema changes: The source system changed its output schema (a renamed column, a new column, a changed data type) since the ETL was last configured. The extraction succeeds but maps data to wrong columns.
Sohovi profiles your datasets for quality issues in minutes — see what's broken before it breaks your pipeline — try Sohovi free.
Encoding issues: Source data contains special characters that aren't handled correctly by the extraction configuration, producing garbled values.
At Transformation
Transformation applies business logic to extracted data. Quality failures here include:
Wrong join logic: A join between tables that drops records that should be kept, or duplicates records that should appear once. NULL-producing outer joins that should be inner joins.
Sohovi automatically finds every duplicate in your dataset — including near-matches — and shows you exactly which rows are affected.
Type casting errors: Casting a string to an integer that contains non-numeric characters — produces NULL or error for affected records.
Business rule errors: The transformation applies the wrong formula or uses the wrong filter. Revenue calculated as gross when the spec called for net. Customer count that includes internal test accounts that should be filtered.
Null propagation: A null value in an upstream field propagates through calculations, producing null in fields that should have values.
Sohovi profiles every column in your dataset for completeness and flags the exact rows where values are missing — free to try.
At Loading
Loading writes transformed data to the destination. Quality failures here include:
Duplicate records from non-idempotent loads: A load job that runs twice (due to a retry after failure) creates duplicate records if it's not idempotent.
Constraint violations: Records that violate unique constraints or not-null constraints in the destination fail silently or cause the load to abort partway through.
Wrong partition or table: Data loaded to the wrong partition or wrong table — often a subtle error in the load configuration that produces no error but puts data in the wrong place.
Frequently Asked Questions
Q: What is the most common ETL data quality failure? Schema drift at extraction — a source system changes its output format without notifying downstream consumers. The ETL job succeeds (no error) but maps data to wrong columns or produces NULLs where values should exist.
Q: How do you add data quality checks to an ETL pipeline? Add validation steps at key boundaries: (1) after extraction, validate schema against expected, check record counts, check null rates on critical fields; (2) after transformation, check business rules, expected value distributions, referential integrity; (3) after loading, reconcile totals against source, verify counts.
Q: What is an idempotent ETL process and why does it matter for data quality? An idempotent ETL can be run multiple times and produce the same result as running it once. Non-idempotent ETLs create duplicates when run more than once — common when a job fails and is retried. Designing ETL with upsert logic (rather than pure insert) is the most common approach to idempotency.
Q: What is a data reconciliation check in ETL? A reconciliation check compares the total records or aggregate values in the destination to the expected totals from the source. "Source has 50,423 records for this date range; destination loaded 50,423 records" confirms a clean load. Significant discrepancies indicate loading failures.
Q: How does null propagation work in SQL transformations? In SQL, any arithmetic or concatenation operation involving a NULL returns NULL. If you join two tables and the join condition produces a NULL in a key field, downstream calculations that use that field will produce NULL. Handling NULLs explicitly with COALESCE or NULLIF is essential for preventing null propagation quality failures.
Q: What is a dead letter queue in an ETL context? A dead letter queue holds records that failed validation during loading — records that would have violated a constraint or failed a business rule check. Instead of being silently dropped, they're stored for investigation and potential reprocessing.
Q: How does incremental vs. full load affect data quality? Full loads replace all destination data with the current source data — simpler but slower and prone to overwriting corrections made in the destination. Incremental loads update only changed records — faster but more complex, with additional quality risks if change detection logic misses records.
Q: What is the role of dbt in ETL data quality? dbt (Data Build Tool) handles the Transform step in an ELT pattern. It includes built-in test functionality that runs quality checks on transformed data: uniqueness tests on primary keys, not-null tests on required fields, accepted-value tests for categorical fields, and referential integrity tests.
Q: How do you monitor ETL pipeline health for data quality? Set up alerts on: job completion status, record counts per run (alert on significant deviations from expected), freshness (how recently was the destination updated?), and key quality metrics (null rates, duplicate rates for critical tables).
Q: What is the most important data quality check to add to an ETL pipeline? Volume monitoring — comparing the count of records extracted/loaded to the expected count. A sudden drop from 50,000 records to 500 is immediately visible with count monitoring, whereas it might not be noticed in downstream reports for days.
Data quality problems born in ETL are multiplied downstream. Adding quality gates at each stage — extraction, transformation, loading — is what separates pipelines that silently corrupt your warehouse from pipelines you can trust.
