MBA Knowledge Library · Business Analytics
Data Quality and Data Cleaning: A Practical Business Workflow
A correct formula can produce a misleading answer when inputs are incomplete, duplicated, or defined inconsistently. Cleaning should improve reliability while preserving a traceable explanation of important changes.
← Business Analytics hub · Business library
See how reliable inputs support decisions in Descriptive, Predictive, and Prescriptive Analytics.
What is data quality?
Data quality is fitness for a defined use. A record may be sufficient for counting inquiries but inadequate for calculating collected revenue. Define the question, unit of analysis, reporting boundary, and required fields before treating “clean” as a universal label.
| Dimension | Check | Example |
|---|---|---|
| Accuracy | Does the value match source evidence? | An amount agrees to the issued invoice. |
| Completeness | Are required records and fields present? | Each invoice has an entity and date. |
| Consistency | Do representations use the same definitions? | Systems use a shared customer ID. |
| Validity | Does the value follow the permitted rule? | A status belongs to the agreed list. |
| Uniqueness | Is each defined event represented appropriately? | One invoice is not counted twice. |
| Timeliness | Is information current enough? | Receipts are included through the stated cutoff. |
Validity does not prove accuracy. A correctly formatted date can belong to the wrong month. Completeness depends on purpose: an optional phone field may not matter to invoice aging while a missing due date does.
Define the grain before removing duplicates
The grain is what one row represents: an invoice, invoice line, payment, customer, or account-period balance. Several rows with one invoice number can be legitimate lines. Different entities can reuse invoice numbers. A duplicate-removal button cannot establish the correct identity rule.
A candidate invoice-header key might combine entity, customer ID, and invoice number. Validate it with the source owner. Preserve credit notes, reversals, and corrections according to the reporting purpose rather than deleting inconvenient records.
A repeatable cleaning workflow
- Preserve raw data: retain source, extraction time, filters, and original format.
- Profile: inspect counts, types, missing values, distinct values, ranges, and totals.
- Define rules: document keys, units, dates, statuses, and inclusion criteria.
- Transform: standardize through explicit mappings and reproducible steps.
- Quarantine exceptions: separate records needing evidence rather than guessing.
- Reconcile: explain count and amount changes from source to output.
- Review: disclose limitations and obtain the appropriate release review.
Separate raw, working, and output layers. A spreadsheet, Power Query workflow, or script can help; a reviewer should still understand and reproduce the result. Automation is useful only when its rule is appropriate.
Worked example: an invoice export
This fictional six-row invoice-header export uses one currency. Source-document review confirms that the two Cedar A-101 rows represent the same invoice, not separate lines.
| Row | Entity | Invoice | Amount | Issue |
|---|---|---|---|---|
| 1 | Cedar | A-101 | $1,000 | Original header. |
| 2 | Cedar | A-101 | $1,000 | Confirmed duplicate export row. |
| 3 | Cedar | A-102 | $750 | Valid header. |
| 4 | Cedar | A-103 | $500 | Customer name has surrounding spaces. |
| 5 | Maple | A-101 | $900 | Valid: different entity. |
| 6 | Cedar | A-104 | $850 | Date 03/04/2026 has an unconfirmed format. |
The raw total is $5,000. Removing the confirmed $1,000 duplicate leaves five distinct headers totaling $4,000. Trimming a display-name space does not change the amount. The $850 invoice remains in the distinct-header population, but its period is unresolved.
For an analysis requiring reliable month assignment, quarantine the ambiguous-date row until source evidence establishes March 4 or April 3. Four confirmed-date headers total $3,150, with a separate $850 exception. Do not present $3,150 as the complete sales population.
Handle common issues deliberately
| Issue | Sensible treatment | Avoid |
|---|---|---|
| Missing value | Confirm evidence or flag an exception. | Replacing every blank with zero. |
| Label differences | Use an approved mapping and retain the original. | Combining categories with different meanings. |
| Identifiers | Preserve meaningful leading zeros and stable keys. | Converting every identifier to a number. |
| Outlier | Investigate source and business context. | Deleting a legitimate unusual event. |
| Mixed units | Convert with documented units, rates, and dates. | Adding different currencies as equivalent amounts. |
| Ambiguous dates | Confirm locale and business meaning. | Guessing because most values parse. |
Make quality measures explicit
One extra duplicate row out of six raw rows is a 16.7% extra-row rate. That differs from counting every row belonging to a duplicate group, which includes both copies. Name the definition and denominator.
One of five distinct headers lacks a confirmed date, a 20% unresolved-date rate. Its $850 represents 21.25% of the $4,000 amount. A small exception count can have a material effect. Review count, amount, and business consequence together.
Keep a transformation log
Document the rule, affected records, old and new values where appropriate, source evidence, responsible person, and review status. Distinguish standardization from correction of business facts. Keep the mapping-table version with the output so later changes do not silently alter historical results.
When excluding records, show how many, their relevant amounts, why they were excluded, and the remaining limitation. A clean-looking output should not hide a difficult population.
Prevent recurrence at the source
Repeated cleaning points to capture rules, integration behavior, ownership, or training. Consider entry validation, controlled categories, stable identifiers, and exception reports. Assign an owner who can resolve disputes and authorize appropriate corrections.
Monitor counts, totals, schemas, and quality measures after software or process changes. A successful refresh does not prove a field still means the same thing. Maintain the path from reported measures to source and transformation rules.
Common mistakes
- Overwriting the only raw copy.
- Removing duplicates before defining grain and keys.
- Silently excluding difficult records.
- Treating missing amounts as true zeroes.
- Cleaning until a desired conclusion appears.
- Reporting percentages without population and rule definitions.
Frequently asked questions
Can Excel support data cleaning?
Yes, for suitable datasets and controls. Use structured tables and reproducible steps where possible; consider scale, collaboration, and review needs.
Should every correction be automated?
No. Reliable transformations can be automated, while ambiguous or material exceptions may need source confirmation.
Is an outlier always wrong?
No. It might be a real event, a different population, or an error. Investigate first.
When is data clean enough?
When it is sufficiently reliable for the intended decision and remaining limitations are understood. Required quality depends on the consequence of error.
Your release checklist
Confirm source and cutoff, grain, keys, required fields, transformations, exceptions, reconciled totals, reviewer, and limitations. Explain what changed and why before anyone relies on the result.
Sources and example notes
- IBM: Data Quality Dimensions
- IBM: Data Quality Monitoring
- Government of Canada: Guidance on Data Quality
Examples, calculations, checklists, and practical recommendations are original educational illustrations, not research findings or universal benchmarks. Adapt decisions to actual evidence, policies, and obligations.