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

Alejandro Silva Mendez — Wed, 09 Sep 2026 19:01:21 +0000

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.

Fail-closed Python automation: dry runs, single-use approvals, and safe retries

Alejandro Silva Mendez — Wed, 09 Sep 2026 18:51:51 +0000

When an automation can create or update records, a successful script is not enough. The workflow should make unsafe states difficult to reach and make every decision auditable.

This article presents a small pattern I use for isolated automation assessments:

1. Make dry-run the default

A dry run should validate inputs, compute the intended payload, and produce a machine-readable outcome without performing a live write. A caller must opt in to live mode explicitly.

2. Approve the exact payload

The approval should bind to a canonical JSON representation of the payload. If any field changes after approval, the signature must no longer verify. This avoids approving one action and executing another.

3. Make the approval single-use

Store a nonce or approval identifier in a transactional table and consume it atomically. A retry with the same approval then returns a clear duplicate outcome instead of repeating the write.

4. Reserve work across workers

A unique reservation key plus a short database transaction prevents two workers from executing the same logical operation concurrently. The reservation is shared across processes, not kept only in memory.

5. Keep an append-only audit

Write sanitized JSON lines for validation, approval, reservation, execution, duplicate detection, and failures. Never put secrets or full customer payloads in the audit. Include a correlation ID and enough metadata to reproduce the decision.

6. Test the failure paths

The important tests are not only the happy path: verify that dry-run never writes, a modified payload fails approval, a nonce cannot be reused, and two worker instances share the same reservation.

I built a small synthetic reference implementation with these properties here: https://github.com/AlejandroSilvaMendez/fail-closed-python-assessment

For focused Playwright, API, or Python reliability work, I also offer small fixed-price scopes: https://alejandrosilvamendez.github.io/qa-automation-services/

AI coding tools helped draft the example, and I reviewed the design, tests, and disclosure personally.

DEV Community: Alejandro Silva Mendez