Spreadsheets and delimited files remain important boundaries between business systems. Their flexibility makes them useful, but also allows small formatting changes to alter a dataset without an obvious application error.
This design turns recurring file deliveries into a controlled analytical process. It covers delivery evidence, explicit schemas, validation and reconciliation as a basis for discussing file integration with Covorg. Specific formats and transfer methods require assessment against the source and operating requirements.
Document what constitutes a complete delivery
Agree the file format, encoding, delimiter, column definitions, reporting period and delivery schedule. Specify whether each file replaces a complete dataset, represents a period snapshot or contains incremental records. A filename alone is rarely sufficient to establish these semantics.
A delivery manifest can record the source, period, schema version, expected row count and file digest. The digest helps identify an unchanged file delivered again; it does not prove that the business content is correct. Keep the original delivery reference so a corrected file can be associated with its predecessor.
Parse according to explicit field rules
Treat identifiers as identifiers, even when they contain only digits. Automatic type inference can remove leading zeroes or reinterpret dates according to an unintended locale. Define decimal conventions, date formats, empty value handling and accepted enumerations as part of the dataset contract.
| Field | Example rule | Failure to detect |
|---|---|---|
| Customer identifier | String with a documented format | Leading zeroes lost during conversion |
| Posting date | One agreed date representation | Month and day interpreted differently |
| Amount | Declared decimal and currency convention | Thousands separator read as decimal |
| Status | Approved set of business values | New state silently excluded |
| Record identity | Unique within the delivery scope | Repeated records inflate totals |
Validate structure and business meaning separately
Schema validation can verify required fields and types. Business validation checks relationships such as a known account mapping, an eligible reporting period or reconciled amounts. Passing the first does not imply passing the second.
The JSON Schema below defines a normalised record after file parsing. The required keyword makes the listed properties mandatory. Additional fields are rejected here deliberately; a different contract might permit them while routing a schema change for review.
{
"type": "object",
"properties": {
"recordId": { "type": "string", "minLength": 1 },
"amount": { "type": "number" },
"currency": { "type": "string", "minLength": 3, "maxLength": 3 }
},
"required": ["recordId", "amount", "currency"],
"additionalProperties": false
}Reconcile before replacing an approved dataset
Compare accepted, rejected and total source records, then evaluate the applicable control totals. Decide whether an issue blocks the entire delivery or permits a clearly marked partial result. A file that parses successfully but contains no expected records should trigger review.
Publish using a recoverable boundary so consumers do not observe a partially replaced dataset. Retain the input reference, validation outcome and transformation version. When a corrected delivery arrives, define whether it replaces an earlier period or adds a separately identified adjustment, and rerun affected checks.
File delivery and publication
- 01
Register
Record the source, file identity, expected period and schema version.
- 02
Parse
Apply explicit encoding, field types and format conventions.
- 03
Validate
Check structure, business rules and delivery control totals.
- 04
Publish
Release an approved dataset with its processing and source references.
Design considerations
- Distinguish a repeated file, a corrected replacement and a new incremental delivery.
- Define an explicit policy for unexpected columns and empty files.
- Keep original deliveries and diagnostic records under appropriate access and retention controls.
Sources
The following sources explain the underlying technical patterns.
