CSV Is Not a Database, but Sometimes It Is the Interface You Get

A column named price looks reassuring until the next file uses a different decimal separator, includes a currency symbol or silently changes whether tax is included. The parser may accept every row. The catalogue can still be wrong.

CSV is convenient because many systems can exchange it. That convenience does not give it a complete data model. A file rarely explains identity, units, update semantics or what an empty cell is supposed to mean. Those agreements usually live somewhere outside the rows, sometimes only in the habits of the people exchanging them.

In catalogue work, the useful response is to build a boundary around the file. Preserve what arrived, interpret it through an explicit contract and prevent unresolved ambiguity from spreading into the systems that depend on it.

Treat the file as input, not as truth.

Separate syntax from the supplier contract

RFC 4180 documents a common CSV format, including quoting and the handling of commas and line breaks inside fields. It is an Informational RFC, not a guarantee that every exporter follows one uniform standard. Real files can use different delimiters, encodings and conventions.

That means two agreements are needed. One describes how to read the bytes into fields. The other describes what those fields mean. A parser can solve the first problem while leaving the second entirely open.

For a supplier feed, the contract might specify the identifier column, encoding, delimiter, currency, units and whether the file is a complete snapshot or a partial update. It should also say how missing values affect existing records. An empty description might mean “leave unchanged,” “clear the field” or “unknown.” Choosing silently is a data mutation disguised as parsing.

A representative sample helps establish the contract, but it should include awkward cases. Product descriptions containing line breaks and identifiers with leading zeroes are more informative than a file containing only simple rows.

Put the pipeline where it can be inspected

A useful conceptual flow is:

raw file
   ↓
schema detection
   ↓
normalization
   ↓
validation
   ├─ rejected-row report → review
   ↓ accepted records
canonical dataset
   ↓
channel exports

The branch matters. A rejected record should not become canonical merely because a report was generated. Depending on the operation, the policy may reject the entire batch or admit independent valid records while quarantining the rest. That choice should be explicit.

Each stage needs a narrow responsibility. Schema detection identifies whether the file matches a supported contract. Normalization converts agreed representations. Validation decides whether the resulting records satisfy the rules needed by the next stage.

Keeping these responsibilities distinct makes failures easier to explain. If the header changed, report a schema mismatch. If a value cannot be interpreted, report the field and original value. If a product lacks required identity, reject the record for that reason instead of allowing a downstream API to discover it later.

Detect changes without guessing their meaning

A renamed column can look harmless. Sometimes it is. Sometimes it indicates a different export version with changed semantics. Schema detection should compare the observed fields with a known mapping, including required columns and unexpected duplicates.

Automatic delimiter detection can be useful as a diagnostic, but it should not become permission to accept any plausible interpretation. Python’s CSV documentation describes dialect configuration and the Sniffer helper, including the heuristic nature of header detection. In a recurring pipeline, an agreed dialect is a stronger starting point than repeated guessing.

Encoding deserves the same treatment. If the workflow expects UTF-8 and encounters undecodable bytes, replacing them silently can damage identifiers or descriptions. Preserve the file and report the mismatch. If another encoding is legitimately supported, select it through a documented source rule.

The safest reaction to an unfamiliar schema is often to stop before mutation. A temporary import delay is easier to explain than a large set of updates based on a guessed mapping.

Normalize conservatively

Normalization creates a consistent internal representation, but consistency should not erase meaning. Identifiers should remain strings when leading zeroes or formatting are significant. Units should be converted only when the source unit is known. Decimal notation should be interpreted according to the feed contract.

A hypothetical weight field illustrates the risk. The value 1,250 could represent one and a quarter units or one thousand two hundred and fifty units under different conventions. A global punctuation replacement cannot determine which meaning the supplier intended.

Record the original value alongside the normalized value when it is useful for review. A correction report can then show what changed and which rule applied. This is especially helpful when a person questions an output that is syntactically valid but surprising.

Normalization should remove representational differences, not unresolved questions.

If the source does not establish whether a price includes tax, converting it into a decimal type does not resolve that uncertainty.

Give the canonical model a separate job

The canonical dataset should describe the product independently of any one supplier file or sales channel. It may include stable internal identity, explicit units, source references and validated relationships between products and variants.

That does not mean inventing a universal schema large enough to represent every future product. Start with the distinctions the current catalogue actually needs and extend them deliberately. A catch-all text field can preserve unknown material without pretending that it has been understood.

This separation prevents a marketplace-specific field name from becoming the definition of the product everywhere. It also makes it easier to add another channel without repeatedly reinterpreting the original supplier file.

The product-data article discusses identity and ownership at the catalogue level. The CSV boundary is where those decisions first become executable rules.

Reject usefully and publish deliberately

A rejected-row report should include enough context to act: source row reference, identifier when available, field, failed rule and whether any downstream change occurred. Avoid dumping unnecessary commercial or personal data into logs simply because it is available.

Some failures belong at batch level. If the identifier column is missing, generating an error for every row adds noise. Explain the contract failure once and prevent the batch from entering the update stage.

For accepted records, compare the proposed canonical result with existing state before export. Distinguish additions, meaningful changes and unchanged rows. Large or unexpected changes can require review under explicit rules appropriate to the operation.

Publication is a separate boundary. Producing a valid export file does not prove that the destination accepted it. Keep the destination’s response and reconcile it with the intended changes. The same discipline appears in API integration design.

A practical acceptance example is a batch containing one valid update and one ambiguous identifier. State in advance whether the valid update may proceed independently or whether consistency requires rejecting the batch. Then verify both the output and the report. The test should make the chosen policy visible to someone who has never seen the original implementation.

Also distinguish a report from an audit trail. A human-readable summary helps immediate review; a structured record of source, rules and accepted changes supports later reconstruction. They can be generated from the same batch result without becoming separate accounts of what happened.

Make tomorrow’s file an ordinary event

A pipeline is only useful if it can handle repeated exchange. Preserve source versions, mapping versions and batch outcomes so that a changed result can be traced. Rerunning the same input should not create duplicate products or reapply an unintended operation.

A missing product in a later file needs an explicit policy. If the feed is a complete snapshot, absence may carry meaning. If it is a partial update, absence may mean nothing. Deleting records based on the wrong assumption can be more damaging than a visibly malformed row.

Keep a small set of regression fixtures covering the important boundary cases: quoted line breaks, empty identifiers, changed columns, ambiguous numbers and repeat imports. These tests should protect real rules rather than mirror the current parser implementation.

The durable interface is the contract around the file. CSV may remain the format you receive for years. A clear contract, a reviewable pipeline and a useful rejection path let that practical compromise coexist with reliable internal data.

Sources

1. RFC 4180: Common format and MIME type for CSV files — checked 2026-10-11
2. Python documentation: CSV file reading and writing — checked 2026-10-11