Data Quality: Validation, Integration, and Traceability

35 min

Merge/Join and Data Relationships

Learning objective: use one-to-one, one-to-many, many-to-one joins; detect many-to-many explosions.

Before merging two tables, the most important question is not how to write the code, but what relationship the join is expected to have. Getting the relationship wrong still runs, but the row count and meaning of the result will be completely off.

master = pd.read_csv("DS102_Company_Master.csv")

# site_id alone repeats across companies, so it must be paired with company_id to be a unique site
joined = site_long.merge(master, on="company_id", how="left", validate="m:1")
joined.shape[0] == site_long.shape[0]  # a correct 1:N/N:1 merge should not change the row count

• Before merging, ask: should this be 1:1, 1:N, or N:1?
• Use merge(..., validate="1:1" / "1:m" / "m:1") in code so pandas checks the relationship for you and errors when it does not hold
• Always check the row count after merging and compare it against the expected count; not erroring is not enough
• If the row count is much higher than expected, suspect a non-unique join key before using the result

Key takeaway: Confirm the relationship before merging, and always check the row count afterward; not erroring is not the same as being correct.

30 min

Units, Definitions, and Comparability

Learning objective: normalize units only with documented conversion rules; distinguish same-name columns with different definitions.

The same column name in two datasets does not mean they can be merged or compared directly. Different units (tonnes vs. kilograms) and different definitions (revenue including vs. excluding tax) are the two most common, and most easily overlooked, problems.

# Suppose another source records waste in kilograms
waste_kg_source["waste_tonnes"] = waste_kg_source["waste_kg"] / 1000
waste_kg_source["original_unit"] = "kg"  # keep the original unit for later traceability

• Before combining sources, list out each source's definition and unit for the same-named column
• Unit conversion should only use a documented, checkable conversion rule, never an assumption made by feel
• Keep a column for the original unit or original definition, so it can be traced and checked later
• If the two sources' definitions cannot be confirmed as consistent, present them separately rather than forcing a merge

Key takeaway: A shared column name does not mean it can be merged directly; both units and definitions must be checked before comparability holds.

40 min

Validation Rules and Data Contracts

Learning objective: implement range, allowed-values, uniqueness, referential-integrity, and schema checks.

Cleaned data with no explicit validation rules can quietly break the next time it is refreshed. Writing the expected rules as executable checks is a simple data contract: data that fails should be caught, not silently accepted.

checks = {
    "unique_company_year_key": clean["company_year_key"].is_unique,
    "employees_non_negative": bool((clean["employees"] >= 0).all()),
    "sector_allowed": master["sector"].isin(["Manufacturing", "Food & Beverage", "Retail", "Logistics"]).all(),
    "site_company_id_exists": site_long["company_id"].isin(master["company_id"]).all()
}
checks

• Test every rule once against known-bad practice data first, to confirm it really returns False
• A rule's message should state which column and which condition failed, not just return True/False
• Range checks should cover both the lower and upper bound, not just one side
• An allowed-values list should stay aligned with the data dictionary's categories, and get updated whenever a category is added

Key takeaway: Write expected rules as executable checks, so data that fails them gets caught, not silently accepted.

30 min

Data Dictionary, Lineage, and Cleaning Log

Learning objective: document meaning, provenance, transformations, assumptions, unresolved issues, and owner.

A data dictionary explains what each column means; a cleaning log explains what was actually done to the data. Used together, they let someone else, or your future self, reproduce and check your judgment instead of just trusting the final numbers.

cleaning_log.append({
    "issue": "revenue_m_ntd stored as text with a thousands comma",
    "evidence": "column dtype was object before this step",
    "decision": "stripped commas and converted to float",
    "reason": "the underlying value is numeric; comma is formatting only",
    "residual_uncertainty": "assumes every comma is a thousands separator"
})

Key takeaway: A data dictionary explains what a column means; a cleaning log explains what was actually done; together they let someone reproduce your judgment.

45 min

Independent Lab: Build a Trustworthy Clean Dataset

Learning objective: independently complete the inspect, clean, reshape, integrate, validate, document loop, producing clean data, a validation summary, and an auditable cleaning report.

• Go to the DS 102 Practice page
• PreviewDownload the practice data (with planted quality problems)
• Download the Independent Lab starter notebook
• PreviewDownload the data dictionary (Traditional Chinese)
• PreviewDownload the data dictionary (English)

1. 1Load and inspect
   Load the practice data and check its shape, dtypes, missing values and duplicate rows.
2. 2Clean and log
   Handle every problem you find, and for each one write an Issue, Evidence, Decision, Reason and Residual uncertainty in the cleaning log.
3. 3Build the candidate key
   Build company_year_key and confirm there are no duplicates after cleaning.
4. 4Write validation checks
   Write at least three validation checks: one uniqueness check, one range or allowed-values check, and at least one check of your own choosing.
5. 5Assemble the outputs
   Produce the cleaned data, a validation summary, and the complete cleaning log.
6. 6State a limitation
   Write one sentence about what this cleaned dataset still cannot answer.
7. 7Log any AI assistance
   If AI was used, write down what it helped with and what you verified yourself.

• A notebook that runs top to bottom from a fresh kernel
• A cleaning log recording at least four handling decisions
• At least three validation checks and their results
• A validation summary (row count, uniqueness, number of missing values, etc.)
• At least one limitation or unresolved issue
• A record of AI assistance (if used)

• Confirm company_year_key truly has no duplicates after cleaning
• Confirm at least one validation rule has actually failed on deliberately broken data
• Confirm every decision in the cleaning log includes a Reason, not just a Decision

Key takeaway: A trustworthy clean dataset needs a reproducible cleaning process and validation rules that can actually fail, not just clean-looking numbers.