FIT5137 Chap.3 Data Quality and Cleaning
Data Quality and Cleaning
Data Quality and Cleaning treats cleaning as diagnosis rather than cosmetic rewriting. The workflow profiles data, states a precise defect, infers a plausible cause, applies the least destructive supported rule and validates both record counts and business measures. Duplicates, missing values, inconsistent codes, invalid ranges, broken relationships and untimely records require different evidence.
A repeated identifier can represent accidental duplication, event history or legitimate recurrence; a null can mean unknown, not applicable or unfinished. This chapter separates detection SQL from correction SQL and insists on before-and-after controls, rejected-row visibility and deterministic survivor rules.
It also explains why apparent improvements can be selection effects: deleting incomplete events can change the analysed population rather than improve performance. The assessment-ready output is an audit trail that shows the defect, chosen method, affected records, reconciliation and remaining uncertainty.
What this chapter covers
- 01
Profile completeness, validity, consistency and uniqueness
- 02
Define the defect at row, attribute or relationship level
- 03
Separate diagnostic queries from correction queries
- 04
Distinguish duplicate events from status history
- 05
Apply the least destructive supported rule
- 06
Preserve rejected and unresolved records
- 07
Reconcile row counts and business measures
- 08
Report sensitivity and remaining uncertainty
Worked example · free
Clean duplicated booking snapshots
- 2Profile identifier counts and inspect all columns for the repeated identifiers. Confirm that each pair is one event observed at two status times.
- 2Archive the two earlier open snapshots. The accepted event count is 52 minus 2, which equals 50 unique bookings.
- 1Retain the two completed distances. Their combined distance is 24 plus 31, which equals 55 kilometres.
- 1Reconcile 50 accepted rows plus 2 archived snapshots to the original 52 input rows.
- 2Check that the accepted distance total includes 55 kilometres from the repeated identifiers and compare the complete total with the operational control for the extraction window.
Key terms
- Data profiling
- Queries and summaries that reveal value patterns, ranges, missingness, duplicates and relationship defects.
- Duplicate event
- More than one stored row representing the same business occurrence under the chosen grain.
- Survivor rule
- A deterministic policy for selecting the retained row among competing representations of one event.
- Rejected row
- A record excluded from accepted analysis while remaining visible with its rejection reason.
- Referential integrity
- The condition that relationship keys point to valid referenced members under declared rules.
- Sensitivity check
- A comparison of results under plausible treatments of uncertain or excluded records.
Data Quality and Cleaning FAQ
Why is DISTINCT not a cleaning strategy?
DISTINCT removes identical projected rows, but it does not establish which records represent the same event or which version is valid. It can hide status history, discard meaningful differences or leave non-identical duplicates. Diagnose business identity and chronology before selecting a survivor.
Should every missing value be filled?
No. Missing can mean unknown, not applicable, not yet completed or genuinely absent. Imputation adds assumptions and can distort distributions. First classify the meaning, then use an authorised value, preserve a managed unknown, exclude with disclosure or perform sensitivity analysis as appropriate.
What belongs in a cleaning audit trail?
Record the defect definition, diagnostic query, chosen rule, affected-row count, before-and-after examples, accepted and rejected totals, business-measure reconciliation, execution context and unresolved exceptions. The trail should let another analyst repeat the result and challenge the assumption.
How can deletion improve a metric falsely?
If difficult, incomplete or failed events are more likely to have missing fields, deleting them changes the population. An improved average may reflect selection rather than better performance. Compare exclusion rates by status and report results for comparable populations.
Assessment move
Organise revision as defect cards. On each card, name the quality dimension, diagnostic query, possible causes, least destructive correction and validation. Include one case where the tempting fix is wrong, such as deleting null completion times or collapsing genuine repeated events. Rehearse the identity accepted plus rejected plus archived equals input. Then add a business measure that must also reconcile.
In assessment writing, show detection, correction and validation as separate artefacts and finish with the scope that the cleaned data can now support. Create paired examples for duplicates, missing values, codes and broken relationships. In the first example the obvious repair should be justified; in the second it should damage a legitimate distinction. Compare the evidence that separates those outcomes.
Practise deterministic survivor rules by adding tied timestamps and contradictory statuses, then decide when the record must be quarantined instead of forced through. For every deletion scenario, calculate the excluded share by a relevant status or category and ask whether the analytical population changed.
A complete revision note should include the unresolved cases, because knowing when not to clean is part of data-quality judgement. End with a short sensitivity table that recalculates the main result under two plausible treatments of uncertain rows. Explain whether the conclusion changes and why. This transforms uncertainty from a vague caveat into evidence about the stability of the analytical claim.
Store the alternative results beside their assumptions so later reviewers can reproduce the comparison without guessing which rows changed.
