Home › Learn › Data systems › Upstreams › Files
Data systems · upstreams, in depth · for data leaders, then builders
File sources: complete, once, in order
Every file arrives whole, once, in order and in the agreed shape, or it is held back with a reason.
What goes wrong with file-based sources, and what to agree before the first file: formats, naming, done markers, corrections, order, encoding, column changes, size and security.
For leaders
- File feeds are the most common source and the least controlled: whoever writes them can change them without telling us.
- Most problems are partial files, files sent twice or out of order, changed columns, wrong encodings and dates, and empty files that mean different things.
- Agree a file contract before the first delivery: name pattern, full or delta, format, done marker with row count, delivery time and how corrections are sent.
- Keep every file exactly as received, so any day can be replayed or withdrawn.
In plain words
Parcels at a loading dock
File feeds are parcels left at a loading dock. A good sender numbers each parcel, puts a packing list inside saying what should be there, and tells you when the last one is on the truck. A bad sender drops boxes whenever they like, sometimes twice, sometimes half-packed, and changes the box size without saying. Most file problems are solved by agreeing the packing list before the first delivery.
Kinds of files
File formats and what to watch
| Format | Where it comes from | Watch for |
|---|---|---|
| Delimited text (CSV, TSV, pipe) | Most exports and partner feeds | Delimiters inside values, quoting, encodings, header changes |
| Fixed width | Mainframes and banks | A layout document you must have; one shifted column corrupts every field after it |
| Excel | Finance, planning, hand-made data | Merged cells, hidden sheets, formulas, manual edits, many tables on one sheet |
| JSON and XML | App exports, APIs dumped to files, B2B messages | Nesting, optional fields, very large single documents |
| Parquet and Avro | Other data platforms and modern exports | Schema inside the file: good, but changes still need rules |
| Compressed and archived (gzip, zip) | Large feeds | A single gzip can't be split, so one huge file is read by one machine |
| Documents (PDF, images) | Invoices, forms, statements | Need extraction (OCR or a model) before they are data; check the extraction |
How files arrive
| Delivery | What it means for us |
|---|---|
| Source pushes to our SFTP or storage | We control the landing place; need a done marker |
| We pull from their SFTP or storage | We must know when a file is complete; listing big folders is slow |
| Object storage drop with events | File-arrival events can start the load (no schedule guessing) |
| Email attachments or shared drives | Fragile and hard to secure; replace with SFTP or storage when you can |
Agree it first
What a file contract holds
| Part | What it says | Example |
|---|---|---|
| Name pattern | Source, dataset, business date and sequence number in the name | sales_eu_2026-11-09_003.csv |
| Full or delta | Whether the file holds everything or only changes, shown in the name | …_full_… or …_delta_… |
| Format | Delimiter, quote and escape characters, header row, line endings | comma, double quote, header yes |
| Encoding | Character set, and whether there is a byte-order mark | UTF-8, no BOM |
| Values | Date and number formats, decimal separator, how nulls are written | ISO dates, '.' decimals, empty = null |
| Done marker | A small file or trailer row saying the file is complete, with row count and checksum | …_003.done with rows=120400 |
| Delivery | Where, when, and how late is too late | by 02:00 UTC daily |
| Corrections | How a fix is sent: a full resend, a correction file, or never edited in place | resend whole day with next sequence |
| Retention | How long the source keeps files, so we can ask again | 30 days |
In production
Common file problems, and the fix
| Problem | What you see | Fix |
|---|---|---|
| File read while still being written | A day's total 40% low, no error | Load only after the done marker, or after the size stops changing |
| Same file delivered twice | Doubled revenue for one day | Keep a register of file names and checksums; skip a file already loaded |
| Files out of order or missing in the sequence | Tuesday loaded before Monday; history wrong | Process by sequence number; hold the queue and alert on a gap |
| Late file | Gold runs on yesterday's data | Freshness alert; downstream waits for the file or runs with a visible flag |
| Columns added, removed or reordered | Values land in the wrong columns | Match columns by header name, not position; detect schema changes; quarantine the file |
| Delimiter or line break inside values | Rows split or merged | A strict parser with quoting; count rejected rows; reject the file over a threshold |
| Wrong encoding | Names with accents turn into garbage | Encoding stated in the contract and checked on arrival |
| Ambiguous dates (03/04/2026) | March or April, depending on the reader | ISO dates in the contract; reject ambiguous ones |
| Empty file | Is it 'no sales today' or 'the export broke'? | Agree what an empty file means; send an empty file with a done marker for 'no data' |
| One huge compressed file | One machine reads 50 GB for hours | Ask for split or splittable files (several parts, or Parquet) |
| Thousands of tiny files | Slow listing and slow loads | Ask for batching; compact after landing |
| Hand-edited Excel | A renamed tab or merged cell breaks the load | A locked template, or move the data to a form or table |
Landing keeps every file exactly as received, and bronze records which file each row came from, so any file can be replayed or withdrawn later.
The question list
Every question, why we ask, and what breaks if nobody does
| # | Question to chase | Why we ask | What breaks in production if nobody asks | Do this (not this) |
|---|---|---|---|---|
| FL · Filesfor sources that send files | ||||
| FL1 | Is each file a full snapshot or only the changes, and does the file name say which? | Full and delta files are loaded completely differently. | A delta file treated as full deletes everything it doesn't mention. | Full or delta in the name and the contract. Not: guessing from the size. |
| FL2 | What is the naming pattern: source, dataset, business date, sequence number? | The name is how we order, de-duplicate and find files. | Two files for the same day can't be put in order. | A fixed pattern with business date and sequence. Not: 'export.csv' overwritten daily. |
| FL3 | How do we know a file is complete: a done file, a manifest with row count and checksum, a trailer row? | A file being written looks like a file that is finished. | A half-written file loads and a day's total drops by 40%. | A done marker with row count and checksum, checked before loading. Not: loading whatever is in the folder. |
| FL4 | How are corrections sent: a full resend, a correction file, or edits to the old file? | Corrections decide whether we replace or add. | A corrected file is appended next to the original and the day is doubled. | Resend the whole business date with the next sequence number; we replace that date. Not: editing files in place. |
| FL5 | Can files arrive late, twice or out of order, and in which order must they be processed? | Order matters for history and for deltas. | Tuesday's delta loads before Monday's and the latest values are wrong. | Process by sequence; hold and alert on a gap; skip exact repeats. Not: process in arrival order. |
| FL6 | What is the exact format: delimiter, quoting, header, encoding, line endings, nulls, date and number formats? | Small format differences corrupt data silently. | Accented names become garbage, or 03/04 is read as the wrong month. | Write it in the file contract and check each file on arrival. Not: letting the parser guess. |
| FL7 | How are column changes announced, and are columns matched by name or by position? | Files change shape whenever the exporting system changes. | A new column in the middle shifts every value after it. | Match by header name, detect changes, quarantine files that break the contract. Not: positional reads of delimited files. |
| FL8 | How big are the files, how many a day, and how are they compressed? | Size and count decide parsing speed and cost. | One 50 GB gzip is read by one machine for hours; or a million tiny files take longer to list than to load. | Agree split or splittable files of sensible size. Not: whatever the export produces. |
| FL9 | Where are files delivered, who can write there, and how long does the source keep them? | The landing place is a security boundary and our replay window. | Someone else's process overwrites a file; or we need a resend and the source deleted it. | A dedicated landing place, write access for the sender only, retention on both sides. Not: a shared folder. |
| FL10 | What does an empty file mean, and what does no file mean? | 'No data today' and 'the export broke' must look different. | A broken export sends an empty file and today shows zero sales. | An empty file with a done marker means no data; a missing file is an alert. Not: treating both the same. |
| FL11 | Do files hold personal data, and are they encrypted in transit and at rest? | Files are easy to copy and forget. | Customer files sit unencrypted on an SFTP server for years. | Encrypted transfer and storage, short retention, masked copies for testing. Not: plain files kept forever. |
| FL12 | For Excel or hand-made files: is there a locked template, and who owns it? | Hand-made files change whenever someone edits them. | A renamed sheet breaks the monthly budget load on the day it's needed. | A locked template with validation, or move the data to a form. Not: parsing whatever arrives. |
Design decisions
The decisions to take
How do we start loading a file?
| Option | Verdict |
|---|---|
| On the done marker or a file-arrival event | Pursue · Pursue. No half-written files, no guessing the time. |
| On a schedule, with a size-stable check | Pursue when · Pursue only when the sender can't produce a marker. |
| On a schedule, whatever is in the folder | Drop · Drop. Partial and duplicate loads. |
How do we read columns?
| Option | Verdict |
|---|---|
| By header name, with schema-change detection | Pursue · Pursue. Reordered or added columns don't corrupt data. |
| By position | Drop · Drop for delimited files. Fixed width is the exception: there the layout document is the contract. |
What happens to a bad file?
| Option | Verdict |
|---|---|
| Quarantine the whole file with a reason, and tell the sender | Pursue · Pursue. Nothing half-loaded; the fix comes from the source. |
| Load the good rows, drop the bad ones | Pursue when · Pursue only with a rejected-row threshold and the rejects kept for review. |
FAQ
Questions people ask
What should a file-based data feed agree up front?
A file contract: the name pattern with business date and sequence, full or delta, the exact format and encoding, how values are written, a done marker with row count and checksum, the delivery time, how corrections are sent and how long files are kept.
How do you stop the same file being loaded twice?
Keep a register of every file loaded, with its name and checksum, and skip any file already in it. Bronze also records which file every row came from, so a bad file can be withdrawn.
Next
Where to go next
Other source types: APIs; databases are covered on the main Upstreams page.
Back up: Upstreams: know your sources, and the printable questionnaire.