An import can complete without exceptions and return roughly the expected number of rows while changing customer identifier 00042 into 42 or valid code NA into a missing value. CSV organizes text records; business types and null meanings need separate agreements. The order-file example is constructed, not a production experiment. Parameter discussion is scoped to the cited Python 3.13 and pandas 2.2.3 documentation.
Define an input contract instead of trusting the extension
For ongoing interfaces, the discussion of semantic contracts and versioned data migration addresses field changes. CSV ingestion needs an earlier check: has the data already changed on its way into the first table? Automatic inference can lose source information even when the target schema is unchanged. Test parsability and business interpretability separately.
A minimal contract specifies encoding, delimiter, quoting and escaping, header presence, column names and order, empty-record policy, business types and missing markers. It also defines keys, duplicate handling, whether delivery is a snapshot or an increment, and its cutoff scope. Neither operating-system defaults nor a colleague's spreadsheet settings constitute an interface agreement.
RFC 4180 describes common comma, quote, embedded-newline and quote-escaping conventions, but it is Informational; not every CSV producer follows one dialect.[1] Declare and test semicolons, alternative line endings or special escaping when used. Automatic sniffing can suggest candidate formats, but should not silently replace an approved contract.
Retain a controlled source file and content digest with provenance, contract version and receipt time. Access and retention should reflect actual sensitivity. The source supports investigation and replay; do not attach it indiscriminately to public logs. Logs can identify the file and record position for authorized investigation.
Preserve strings before evidence-based conversion
An identifier does not become a number because it contains only digits. Whether 00042 and 42 represent the same customer is a business decision. Long numeric-looking identifiers should not pass through floating point on their way back to text. If an upstream export already removed zeros or approximated a value, ingestion cannot reconstruct the missing facts; return to the authoritative source.
Use a raw-string staging layer followed by an explicitly typed layer. Retain source-file identity, logical record ordinal, original value and conversion status so authorized staff can trace a target value to its input. Parsed strings preserve field contents; source bytes preserve evidence about encoding and quoting. They are distinct forms of evidence.
Python csv.reader normally returns strings, while its special numeric-conversion option changes that behavior. Configure encoding explicitly and follow the documented newline='' file-opening convention.[2] This supports record-by-record validation, but the parser cannot know which field is a customer identifier, an amount or a business date.
With pandas, make the first reading stage explicitly string-based and disable default missing-token recognition. The documentation says keep_default_na=False without na_values prevents strings being interpreted as NaN; a dtype choice does not replace the null policy.[3] Therefore dtype=str alone is not a demonstration of text fidelity.
Distinguish empty text, unknown values and valid codes
The constructed interface has five columns: order_id, region, amount, note and optional_code. order_id is mandatory and preserves leading zeros. NA is a valid region value by this example's business agreement, not a claim about a geographic coding standard. amount is a two-decimal monetary string, note permits commas and newlines, and an empty optional_code means not supplied.
One record contains 00042, NA, 12.30, a note containing both a comma and a newline, and an empty string. Staging should still contain five fields. The identifier has five characters, the region retains two letters, the amount retains the text 12.30, and the newline remains inside the note. Proper CSV serialization must quote and escape the note as required. Splitting on commas or physical lines is not record parsing.
If the business distinguishes explicitly empty from unknown at source, use distinguishable representations, such as a separate status column or an agreed missing marker with an escaping policy. Once both states have been exported as an identical empty field, consumers cannot infer their original difference. Using quoted versus unquoted empty fields for that distinction requires verified support on both sides, not an assumption.
Either retain all strings in staging and map missing values per column, or use explicit field-specific na_values with evidence that valid codes survive. Replacing NA, NULL or 0 across the entire table can destroy column-specific meaning. A missing mandatory key may block or quarantine a record; an absent optional note may be accepted under the contract. They should not share one generic failure policy.
Validate boundaries, headers and encoding independently
A valid note can span physical lines, so logs should retain logical record ordinals and relevant physical positions. Counting newline characters as orders is incorrect. Parsing arbitrary byte chunks independently may start inside a quoted field. Large-file processing needs parser-recognized record boundaries instead of simple newline-based parallel splitting.
Validate headers before converting records to dictionaries or tables: names must be unique, unexpected empty columns absent, and order and case consistent with the contract. Automatic trimming can merge distinct names; automatic duplicate-column renaming can hide source errors. Approved aliases need explicit versioned mappings, not an unannounced choice of the first or last matching column.
Check every parsed record's field count against the contract, covering both excess and missing fields. pandas documents on_bad_lines in terms of too many fields, and both warn and skip discard the affected records.[3] Thus on_bad_lines='error' is not a complete structural validator. Making a job green with skip cannot justify a claim of complete ingestion.
Decode strictly according to the contract. Illegal bytes should preserve file identity and stop the affected import, rather than be ignored or replaced as routine repair. If a UTF-8 BOM is allowed, handle it explicitly at the decoding boundary and verify that no invisible character remains in the header. Resolve a mismatch between declared and actual encoding with the producer; do not cycle through guesses until one avoids an exception.
If damaged quoting makes subsequent record boundaries unreliable, quarantine the affected file or a demonstrably independent block. Do not claim that only one row was lost. Mark error counts unknown when they cannot be established. Single-record business quarantine is appropriate only when record boundaries remain trustworthy.
Create a replayable publication boundary between staging and production
Use five stages: receive and establish file completeness; decode and parse strictly; validate structure and stage original values; apply explicit business conversions; and publish the accepted version. Record starting, completed and failed states for each stage. Receipt is not completed ingestion. Use a completion marker or equivalent delivery protocol rather than treating an upload still in progress as a final file.
Define conversion per column. Convert monetary decimal text into a target with explicit precision, checking range and fractional places. Parse dates with an explicit format: 03/04/2026 must not be guessed as March 4 or April 3. Unknown enumerations must not silently become normal states. Trimming, case normalization and rounding require versioned rules and retained before-and-after differences.
A business quarantine should record the failed field, reason, rule version and source-record reference. If partial success is permitted, expose completeness status and quarantine counts with the output. Do not let consumers mistake an incomplete order set for a full snapshot. Snapshot-dependent reporting can block the batch; low-risk independent records may be delivered under an approved partial policy. This is a business tradeoff, not a parser default.
Replay identity should include source scope, original content digest and transformation-contract version, rather than filename alone. Retrying identical content under identical rules should return the existing result or converge on the same commit without adding contributions twice. Changed rules need an explicit processing version and replacement relationship, not silent reuse of old output.
Expose consistent production results through transactions, versioned table cutover or an equivalent mechanism. After interruption, preserve staging and stage evidence, then inspect whether a commit occurred before continuing. A file digest identifies identical bytes, not duplicate business orders across different files. Cross-file deduplication still needs business keys and snapshot-versus-increment semantics.
Accept against counterexamples, not just row counts
The first test group checks information retention: leading zeros, long numeric-looking identifiers, valid NA, empty strings, commas and quotes in notes, and embedded newlines. Predefine field counts, original text and typed expectations. Inspect stored values, not only displayed cells, whose formatting may conceal changes.
The second group checks structural rejection: duplicate headers, missing or additional columns, short and long records, unclosed quotes and illegal encoding. Expected outcomes include no incorrect production version, a traceable reason and a safe recovery stage, not merely an exception. Whether one record can be isolated depends on whether its boundaries remain reliable.
The third group checks business semantics: empty mandatory keys, out-of-range amounts, ambiguous dates, unknown enumerations, duplicate keys and conflicting records. Equal row counts can hide one loss and one duplicate. Compare key sets, duplicate counts and relevant field-level values. Aggregate totals are supplementary and cannot replace record reconciliation.
The fourth group checks operational recovery: incomplete receipt, interruption during conversion, a committed result with a lost acknowledgement, redelivery of identical files, different content under the same filename, and replay after rule upgrades. Each commit needs an explicit identity, without duplicate contributions or old-rule output presented as newly validated output.
A proposed receipt records source-file identity, contract and parser versions, determinable parsed-record counts, accepted and quarantined counts, duplicate dispositions, production commit identity and unverified items. These are acceptance methods to implement, not a claim that production tests were performed. Reliable ingestion means every accepted value retains explainable provenance and meaning, rather than merely a reader that raised no exception.