How to audit a CSV before it breaks a dashboard, import, or customer report

Start with fitness for purpose, not a universal score

Data quality is not one permanent property of a file. A list can be complete enough to answer a weekly capacity question and still be unusable for invoicing. A customer field can be optional in an internal operations view and mandatory in a tax record. The first question is therefore not “is this CSV clean?” It is “what decision, import, report, or action must this file safely support?”

Write the intended use in one sentence. Then name the failure you cannot accept. For a dashboard, an unacceptable failure might be a total that does not reconcile to the source. For a CRM import, it might be overwriting a different contact because the key is not unique. For a customer report, it might be mixing currencies or time zones without disclosure. Those failures become the priority quality rules.

The UK Government Data Quality Framework describes six useful dimensions: completeness, uniqueness, consistency, timeliness, validity, and accuracy. A browser checker can measure parts of the first five. Accuracy, whether the value matches reality, normally requires comparison with an authoritative source or human evidence. A valid-looking date can still be the wrong date. A complete amount column can still contain false amounts.

A structural score is a triage device. It is not a certificate that the facts are correct.

Freeze and describe the source before touching it

Make the raw export immutable in your working process. Copy it into a dated source folder, but do not open it in an application that may silently reformat dates, identifiers, scientific notation, or leading zeroes and then save over the original. Every transformation should operate on a copy or derived table.

Create a short source register. Record the system, account or workspace, export method, person or automation that produced it, export timestamp, period covered, timezone, currency, filters, row count, and file name. If the platform can revise historical data, record when the source was last refreshed. For a sensitive export, also record the authorized purpose, storage location, access rule, and deletion date.

A hash is useful when the file crosses several hands because it lets you show that the reviewed source and the delivered source are the same bytes. It does not prove the file is truthful; it proves the file did not change unnoticed.

• Keep the raw file unchanged
• Use an explicit source register
• Record period, filters, timezone, and currency
• Give derived files new names
• Never put credentials or unnecessary personal data into the audit record

Write a small input contract

An input contract turns assumptions into checks. It does not need a formal schema language. A table with field name, purpose, required status, expected type, accepted values, key rule, and example is enough for most recurring exports. The contract should distinguish between a required column and a required value. A column may need to exist while blanks are legitimate for some rows.

Choose a business key only when the source defines one. An order number, transaction ID, or ticket ID may be unique; a customer name almost never is. When the natural key uses more than one field, write the composite explicitly, such as account ID plus reporting month. If no stable key exists, do not invent certainty by selecting the least-bad column.

For categorical fields, record the controlled vocabulary and the treatment of unknown values. For dates, specify timezone and accepted format. For amounts, state currency, decimal convention, sign convention, and whether zero differs from blank. These definitions prevent a tool from making a technically convenient but commercially wrong correction.

Run structural checks in a deliberate order

Begin with parsing and shape. Confirm the delimiter, encoding, header row, unique header names, stable column count, and absence of unexpected blank rows. A malformed quoted field can shift every later cell while the file still appears superficially readable. If shape is broken, stop before interpreting individual columns.

Next profile each field: filled count, blank percentage, unique count, dominant type, type exceptions, minimum and maximum where meaningful, and a few representative values. Review outer whitespace and invisible characters when identifiers or categories are involved. Test exact duplicate rows separately from duplicate business keys. An exact repeat may be an accidental double export; a repeated key with different values may be a legitimate revision or a more serious conflict.

Then apply the input contract. Required fields, value vocabularies, key rules, date bounds, numerical bounds, and cross-field consistency belong here. Keep generic signals separate from rule failures. A statistically unusual amount is a signal. An amount outside a contractual maximum may be a rule failure. Both need context before deletion or replacement.

• Parse and shape
• Column profile
• Exact duplicates
• Business-key duplicates
• Declared schema rules
• Cross-field rules
• Reconciliation totals
• Sensitive-data and delivery review

Do not let cleaning destroy evidence

A good issue register contains severity, rule, field or row, observed value, expected condition, likely impact, proposed disposition, owner, and status. Severity should reflect the intended use. A duplicate order ID can be high severity for revenue reporting and low severity in a raw event log where repeats are expected.

Apply mechanical corrections only when the rule is unambiguous: trimming outer whitespace in a derived file, standardizing an approved case-insensitive category, or removing a confirmed exact duplicate. Keep a change log. For ambiguous values, preserve the source value and route the issue to an owner. Never turn blank into zero because it makes a formula work; zero asserts a fact that blank does not.

After correction, run the audit again. Compare source and clean row counts, removed-record counts, null counts, key uniqueness, and important totals. A cleaning pass that fixes one rule can violate another. Reconciliation is the point where a tidy-looking output earns the right to feed the next system.

The safe pattern is raw source → issue register → approved correction rule → derived clean file → rerun → reconciliation.

Reconcile to something independent

Structural checks cannot tell you whether the export contains every record it should. Reconcile against an independently visible control: the source platform’s period total, a transaction count, a balance, a prior approved extract, or another authoritative report. Record both values and the reason for any difference.

Expect legitimate differences. Timezone boundaries, late-arriving events, refunds, exchange rates, platform attribution revisions, and filters can all create variance. The control is not “numbers must always match.” The control is “the variance is zero or quantified, explained, and approved for this use.”

When the source itself can change, record the as-of time and decide whether the report will restate prior periods. Silent restatement makes later comparison impossible to explain. A short metadata note can prevent hours of confusion during the next close.

Package the handoff so the next person can trust it

The delivery should include the untouched source reference, derived clean file, schema or field dictionary, issue register, change log, reconciliation record, and a short limitations note. File names need a date or version, not labels such as final-final-v2. If the data contains sensitive information, do not place unrestricted copies into convenient shared folders merely to make the handoff easy.

State what was checked and what was not. A useful limitation might say that format, required values, key uniqueness, and period totals were reviewed, while customer identity and underlying transaction accuracy were not independently verified. This is more credible than a large “quality score” with no account of its boundaries.

For recurring work, save the rules and compare results over time. A sudden change in row count, field list, missingness, or category distribution can reveal source-system changes before they reach a customer-facing report. That is where a one-time cleanup becomes an operating control.

• Source context
• Quality rules
• Issue register
• Approved change log
• Reconciliation
• Known limits
• Reviewer and date
• Next review trigger