ETLintermediate
Data Quality & Cleansing in the ETL Pipeline
Explain where and how data quality checks and cleansing rules are enforced in an ETL pipeline before data reaches BI reporting layers.
A dashboard showing an obviously wrong number is reported to the BI team, whatever caused it. Intercepting bad data before it reaches a report is the job.
It's like an airport security checkpoint with multiple stages: one scanner catches obviously prohibited items (structural checks), a secondary manual review catches subtler cases requiring judgment (business rules), and anything flagged goes into a visible, logged holding area for review — nothing questionable just quietly disappears without a trace.
Key Concepts
1
Check in layers, matching the pipeline.
staging layer structural: types, mandatory fields present,
row counts against the source
transform layer business rules: valid codes, ranges,
referential integrity, duplicates
warehouse layer reconciliation: totals against the source,
trend checks against yesterday2
Each layer catches a different kind of wrong, which is why one gate is not enough. A file that parsed
correctly can still contain a negative quantity, and a load that passed every row check can still be
missing a day's data.
3
What to do with a bad row is a design decision.
reject route it to an error table, continue with the rest
default substitute a value, and record that you did
fail stop the load4
For a customer feed, rejecting and continuing is usually right — a handful of bad rows should not stop the
warehouse. For a financial reconciliation, failing is right, because a partially loaded ledger is worse than
no load.
5
Stating that the answer differs by feed is the answer.
6
Reject tables must be read. A load that has been routing five hundred rows a night into a reject table
for six months is losing data continuously, and it reports success every time.
7
That makes reject volume something to alert on, not just to record.
8
Trend checks catch what row-level rules cannot.
yesterday: 1.2 million rows, revenue 4.8m
today: 1.2 million rows, revenue 0.4m9
Every row is valid. Something upstream changed, and no row-level rule would notice. A comparison against
recent history is the only check that would.
10
The unknown member. A fact whose dimension lookup fails needs somewhere to go. Assigning it to an
"Unknown" dimension row keeps the fact — so the total is right — and makes the problem visible in reports,
which is better than dropping it silently and better than failing the load.
11
And the principle. Fix data at the source where you can. Cleansing in the ETL means the warehouse is
right and the source system is still wrong, so every other consumer of that source has the same problem and
somebody is cleansing it again.
12
What the interviewer is probing.1. "Where should quality rules be enforced?" Probing: the design. Stalls: "At the end."
Moves up: as early as possible, with rejects captured rather than dropped — a rule applied only in
reporting means bad data is already stored.
13
2. "What do you do with a failing row?" Probing: the engineering. Stalls: "Drop it." *Moves
up:* route it to an error store with the rule that failed, so it is visible, countable and
correctable — silently dropping breaks reconciliation.
14
3. "What is the difference between cleansing and correcting at source?" Probing: the
judgement. Stalls: "Nothing." Moves up: cleansing in ETL treats the symptom forever; feeding
errors back to the source system is what actually reduces them.
15
4. "What should be measured?" Probing: the maturity. Stalls: "Nothing." Moves up: reject
rates per rule over time — a rising rate is an upstream change, and a zero rate often means the rule
stopped running.