Search⌘ K
AI Features
Home
Courses
Forward Deployed Engineer for AI Systems

Working with Messy Data

Explore how to profile real inputs before cleaning them, and how normalization differs from inventing missing information. Understand how to prevent join inflation, preserve conflicts, and account for every source row in a disposition ledger, preparing you to deliver totals a customer can trust.

We'll cover the following...

The command completes without errors and outputs energy totals by region. However, one reading is counted twice, a site is assigned to two regions, and several source rows are excluded during data cleaning without explanation. The report is generated, but its results cannot be fully verified against the source data.

For this separate facilities-export assignment, report accepted energy totals by region and provide an auditable record of every excluded or unresolved source row. Treat this as a separate assignment from the dispatch project.

Why totals go wrong

Start with the actual files. In sites.csv, the columns are site_id,region, and the four data rows are:

  1. 001,North

  2. 002,West

  3. 002,East

  4. 003,South

In energy_readings.csv, the columns are reading_id,site_id,reading_date,amount,unit. The eight data rows, in order, are:

  1. R1, 001 ,2026-09-01,1.5,kWh

  2. R1,001,2026-09-01,1500,Wh

  3. R2,002,2026-09-01,2,kWh

  4. R3,009,2026-09-01,300,Wh

  5. R4,003,03/04/2026,1,kWh

  6. R5,003,2026-09-01,,Wh

  7. R6,003,2026-09-01,500,Wh

  8. R7,001,2026-09-01,250,kW

In reading row 1, the identifier 001 contains leading and trailing whitespace in the source field. Row 6 contains a blank energy amount. In this example, source_row is the one-based index of each data row, excluding the header.

The export contract allows trimming identifier whitespace but requires preserving leading zeros, using ISO-formatted dates, and accepting only nonnegative energy values expressed in Wh or kWh. The contract does not specify which of the two region assignments for site 002 takes precedence.

A cleaned dataset may still be incorrect if processing infers missing dates, replaces blank amounts with zero, or silently removes unmatched rows. These operations alter the source data's meaning rather than simply standardizing its format.

Profiling the source data

Data profiling involves examining source data to identify its structure, distributions, and quality issues before defining transformation rules. For small datasets, inspect every row alongside summary statistics. A single row cannot establish that an entire column contains valid data.

The dataset profile shows the following:

  • Sites: Four site records contain three distinct IDs and no missing fields. Site 002 appears twice with conflicting region assignments: West and ...