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

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

FormatWhere it comes fromWatch for
Delimited text (CSV, TSV, pipe)Most exports and partner feedsDelimiters inside values, quoting, encodings, header changes
Fixed widthMainframes and banksA layout document you must have; one shifted column corrupts every field after it
ExcelFinance, planning, hand-made dataMerged cells, hidden sheets, formulas, manual edits, many tables on one sheet
JSON and XMLApp exports, APIs dumped to files, B2B messagesNesting, optional fields, very large single documents
Parquet and AvroOther data platforms and modern exportsSchema inside the file: good, but changes still need rules
Compressed and archived (gzip, zip)Large feedsA single gzip can't be split, so one huge file is read by one machine
Documents (PDF, images)Invoices, forms, statementsNeed extraction (OCR or a model) before they are data; check the extraction

How files arrive

DeliveryWhat it means for us
Source pushes to our SFTP or storageWe control the landing place; need a done marker
We pull from their SFTP or storageWe must know when a file is complete; listing big folders is slow
Object storage drop with eventsFile-arrival events can start the load (no schedule guessing)
Email attachments or shared drivesFragile and hard to secure; replace with SFTP or storage when you can

Agree it first

What a file contract holds

PartWhat it saysExample
Name patternSource, dataset, business date and sequence number in the namesales_eu_2026-11-09_003.csv
Full or deltaWhether the file holds everything or only changes, shown in the name…_full_… or …_delta_…
FormatDelimiter, quote and escape characters, header row, line endingscomma, double quote, header yes
EncodingCharacter set, and whether there is a byte-order markUTF-8, no BOM
ValuesDate and number formats, decimal separator, how nulls are writtenISO dates, '.' decimals, empty = null
Done markerA small file or trailer row saying the file is complete, with row count and checksum…_003.done with rows=120400
DeliveryWhere, when, and how late is too lateby 02:00 UTC daily
CorrectionsHow a fix is sent: a full resend, a correction file, or never edited in placeresend whole day with next sequence
RetentionHow long the source keeps files, so we can ask again30 days

In production

Common file problems, and the fix

ProblemWhat you seeFix
File read while still being writtenA day's total 40% low, no errorLoad only after the done marker, or after the size stops changing
Same file delivered twiceDoubled revenue for one dayKeep a register of file names and checksums; skip a file already loaded
Files out of order or missing in the sequenceTuesday loaded before Monday; history wrongProcess by sequence number; hold the queue and alert on a gap
Late fileGold runs on yesterday's dataFreshness alert; downstream waits for the file or runs with a visible flag
Columns added, removed or reorderedValues land in the wrong columnsMatch columns by header name, not position; detect schema changes; quarantine the file
Delimiter or line break inside valuesRows split or mergedA strict parser with quoting; count rejected rows; reject the file over a threshold
Wrong encodingNames with accents turn into garbageEncoding stated in the contract and checked on arrival
Ambiguous dates (03/04/2026)March or April, depending on the readerISO dates in the contract; reject ambiguous ones
Empty fileIs 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 fileOne machine reads 50 GB for hoursAsk for split or splittable files (several parts, or Parquet)
Thousands of tiny filesSlow listing and slow loadsAsk for batching; compact after landing
Hand-edited ExcelA renamed tab or merged cell breaks the loadA 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 chaseWhy we askWhat breaks in production if nobody asksDo this (not this)
FL · Filesfor sources that send files
FL1Is 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.
FL2What 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.
FL3How 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.
FL4How 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.
FL5Can 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.
FL6What 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.
FL7How 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.
FL8How 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.
FL9Where 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.
FL10What 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.
FL11Do 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.
FL12For 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?

OptionVerdict
On the done marker or a file-arrival eventPursue · Pursue. No half-written files, no guessing the time.
On a schedule, with a size-stable checkPursue when · Pursue only when the sender can't produce a marker.
On a schedule, whatever is in the folderDrop · Drop. Partial and duplicate loads.

How do we read columns?

OptionVerdict
By header name, with schema-change detectionPursue · Pursue. Reordered or added columns don't corrupt data.
By positionDrop · Drop for delimited files. Fixed width is the exception: there the layout document is the contract.

What happens to a bad file?

OptionVerdict
Quarantine the whole file with a reason, and tell the senderPursue · Pursue. Nothing half-loaded; the fix comes from the source.
Load the good rows, drop the bad onesPursue 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.