Reproducible CSV catalog QA with Python: validation, traceability, and safe exports

#python #testing #automation #data

Small catalog files often fail in production for boring reasons: a header changes case, a price arrives as text, a duplicate SKU slips through, or a spreadsheet is saved with a different encoding. A repeatable QA pass catches those failures before the file reaches an ecommerce or ERP import.

This post shows a conservative pattern for validating product CSVs without inventing missing facts.

1. Preserve the input and define the contract

Start by treating the source file as evidence. Keep the original column names and compute a hash before transforming anything. The internal contract can stay small:

sku (required, stable identifier)
title (required)
description (optional)
category (optional)
price (optional, preserved exactly unless a rule says otherwise)

A mapping file makes the transformation reviewable instead of hiding assumptions in code:

version: 1
columns:
  product_code: sku
  name: title
  long_copy: description
  product_type: category

If a required field cannot be mapped with confidence, stop and report it. Guessing a SKU or category creates a more expensive error later.

2. Normalize only deterministic details

Whitespace and encoding are safe places to start. Keep the operation explicit and idempotent:

from pathlib import Path
import csv, hashlib

def sha256(path: Path) -> str:
    digest = hashlib.sha256()
    with path.open('rb') as f:
        for block in iter(lambda: f.read(1024 * 1024), b""):
            digest.update(block)
    return digest.hexdigest()

def read_rows(path: Path):
    with path.open('r', encoding='utf-8-sig', newline='') as f:
        reader = csv.DictReader(f)
        if not reader.fieldnames:
            raise ValueError('missing header')
        for row in reader:
            yield {key: (value.strip() if isinstance(value, str) else value)
                   for key, value in row.items()}

Do not silently merge ambiguous duplicates. Return them in an errors file with the row numbers and the conflicting values so a client can decide.

3. Validate keys and values

A useful report distinguishes errors from warnings:

from collections import Counter

def validate(rows):
    errors, warnings = [], []
    skus = [r.get('sku', '') for r in rows]
    for index, sku in enumerate(skus, start=2):
        if not sku:
            errors.append({'row': index, 'code': 'missing_sku'})
    for sku, count in Counter(skus).items():
        if sku and count > 1:
            errors.append({'sku': sku, 'code': 'duplicate_sku', 'count': count})
    for index, row in enumerate(rows, start=2):
        if row.get('price') and row['price'].strip().startswith('-'):
            warnings.append({'row': index, 'code': 'negative_price_review'})
    return errors, warnings

The validator should also record input and output row counts, preserved key counts, the mapping version, and hashes. Those checks make reruns comparable and expose accidental data loss.

4. Produce a handoff package

A practical delivery has four pieces:

The normalized CSV.
An HTML or Markdown report with counts, rules, and unresolved errors.
A machine-readable errors file for follow-up.
A change log that links each output row to its source row.

That package is more useful than a cleaned file alone because the recipient can explain what changed and reproduce the result. It also gives a QA engineer a clear place to add regression tests when the source format evolves.

5. Keep automation fail-closed

Run the same checks in CI and reject the export when required columns disappear, duplicate keys increase, or the output count does not match the accepted input set. A dry-run mode should print the proposed mapping and counts without writing an external file. When an approval is required, bind it to the exact input hash, mapping version, and destination so a retry cannot send a different payload.

I keep a small reference implementation and test fixtures here: https://github.com/AlejandroSilvaMendez/fail-closed-python-assessment

For a short, fixed-scope review of a real catalog, I offer a smoke check, a Playwright/API review, or automation hardening with a written report: https://alejandrosilvamendez.github.io/qa-automation-services/

The goal is simple: make the transformation deterministic, make uncertainty visible, and leave the recipient with evidence they can rerun.

DEV Community