Data Prep Guide: Fixing Data Quality Issues

This guide explains the data quality issues present and how to fix them.

DIM_Customer

Full Name

• Issue: Name text contains excessive whitespace (tabs, trailing spaces, and double spaces when combined from first/last names).
• Fix:
  • Trim leading/trailing whitespace.
  • Collapse/normalize internal whitespace (e.g., multiple spaces to a single space).
  • If reconstructing from first/last names, join with a single space and trim.

DateFirstPurchase

• Issue: This should be a date, but values are stored as integer UNIX timestamps (seconds).
• Fix:
  • Convert the integer seconds into a proper calendar Date.
  • Since the source had no timezone, treat the epoch seconds as naive and cast the resulting datetime to Date (no time-of-day).
  • Store as Date consistently across the dataset.

CustomerKey

• Issue: Key values include an unexpected trailing character and are stored as text instead of a numeric identifier.
• Fix:
  • Remove the trailing character and restore the original data type (integer if that was the source of truth).
  • Validate keys are numeric and consistent; re-cast to the correct type.

UselessColumn

• Issue: Column carries a constant placeholder value and has no analytical use.
• Fix:
  • Drop the column.

UnwantedFlag

• Issue: A boolean flag indicates records with placeholder or test location values.
• Fix:
  • Use this flag to filter out the unwanted rows if appropriate.
  • Alternatively, standardize legitimate city names and remove placeholder values.

Gender

• Issue: Gender values have inconsistent casing and representation.
• Fix:
  • Standardize case to a chosen format (e.g., uppercase or title case).
  • Optionally map to categorical values (e.g., F/M) with a consistent dictionary.

Customer City

• Issue: Location data is combined in a single field (e.g., "City, State"), causing ambiguity.
• Fix:
  • Split the combined field back into city and state components.
  • Trim each part and validate against known values.
  • Keep normalized city/state fields separately.

First Name / Last Name

• Issue: Name components contain inconsistent whitespace when combined.
• Fix:
  • Trim each component and recombine with a single space.
  • Ensure names don’t contain internal excessive whitespace.

DIM_Products

Product Status

• Issue: Status values include non-canonical variants and trailing whitespace.
• Fix:
  • Normalize values to the canonical set (e.g., map "curr" and "curr " back to "Current").
  • Trim whitespace and standardize casing.

product name

• Issue: Column naming is inconsistent with the standard convention (case and spacing).
• Fix:
  • Rename column back to the standard name (e.g., "Product Name").
  • Apply a consistent column naming convention across the table.

Product Description

• Issue: The value is null (missing). -Fix:
  • Replace null descriptions with the string "NO DESCRIPTION".

Product Color

• Issue: Color information is oversimplified into coarse categories; original values may be lost.
• Fix (mitigation):
  • If original color values are unavailable, treat this as non-recoverable and document the limitation.
  • Optionally re-map from an external reference or business catalog if a reliable source of truth exists.

Duplicates

• Issue: Duplicate product records are present.
• Fix:
  • Identify and remove duplicate rows using a suitable key or full-row comparison.
  • Choose whether to keep the first occurrence or apply a rule for selection.

FactInternetSale

SalesAmount

• Issue: Amounts are negative but should be positive.
• Fix:
  • Multiply by -1 again or take absolute values to restore positive amounts.
  • Validate against expected totals or sample records.

OrderDateKey

• Issue: Dates are stored as keys/strings rather than proper date types.
• Fix:
  • Parse the key/string into a proper calendar Date.
  • Support both formats (YYYY-MM-DD and integer YYYYMMDD) and cast the result to Date.