Data Quality & Cleansing in the ETL Pipeline

Data quality checks are best implemented in layers, mirroring a typical staging-to-warehouse ETL architecture: at the staging layer, basic structural validation catches type mismatches, unexpected nulls in required fields, and referential integrity violations (a fact row referencing a customer ID that doesn't exist in the dimension) before any transformation logic runs on top of bad data. At the transformation layer, business-rule validation catches semantically invalid data that's structurally fine but logically wrong (a negative quantity sold, a future-dated transaction, an order total that doesn't match the sum of its line items) — these rules are typically organization-specific and require actual business input to define correctly, not just generic technical checks.

Common cleansing operations include standardizing inconsistent categorical values ("NY", "N.Y.", "New York" all mapped to one canonical value), trimming/deduplicating whitespace and formatting inconsistencies, handling missing values via either a documented default, an exclusion, or an explicit "Unknown" dimension member (rather than silently dropping rows, which distorts totals), and deduplication logic for source systems that produce near-duplicate records.

A senior-level point interviewers listen for: rejected/quarantined rows need a visible home, not silent deletion — a robust ETL pipeline routes failing rows into a reject/error table (with the reason for rejection captured) that's itself monitored and reported on, so data quality issues are visible and actionable rather than invisibly vanishing from the warehouse with no trace, which is exactly the kind of gap that causes a business user to notice "missing" data months later with no explanation.