01 / Data quality

Before you trust a CSV

A file can open successfully and still answer the wrong question. Start with its meaning, not its chart.

1. Write down what one row represents

Is it a person, an order, an event, or a daily total? A row count is not a customer count unless there is exactly one row per customer. Record the expected unit of observation before aggregating anything.

2. Inspect the structure

Confirm the encoding, delimiter, column names, and date format. Preserve identifiers as text when leading zeroes matter. A value such as 00127 may be a label, not the number 127.

Use a CSV parser rather than splitting each line on commas: a quoted field can legitimately contain a comma or a line break.

3. Check keys and coverage

• Which column, or combination of columns, should identify a row?
• Are repeated keys legitimate events or duplicated records?
• Does the extract cover the expected dates and categories?
• Are missing values concentrated in one source or period?

For the synthetic sample file, the intended key is one calendar day. A repeated date needs explanation; a missing date does not automatically mean zero records.

4. Reconcile a small summary

Compare the row count and a meaningful total with the source, if available. This compact Python example checks the date key and totals the supplied sample:

import csv
from datetime import date

with open("sample-data.csv", newline="", encoding="utf-8") as source:
    rows = list(csv.DictReader(source))

days = [date.fromisoformat(row["day"]) for row in rows]
assert len(days) == len(set(days)), "Repeated day"
total_records = sum(int(row["records"]) for row in rows)
print(len(rows), total_records)

The sample produces six rows and 8,650 records. Matching a total is useful, but it cannot prove that individual rows are correct.