File Ingestion
The question arrives as 'a payment processor drops a CSV of settlements into a bucket every night. How would you load it?' Candidates who have only loaded files on a laptop describe reading the file and inserting the rows. Candidates who have loaded files in production describe everything that goes wrong between the processor's upload and your table, and that gap is exactly what this question measures.
Is the file complete?
Where a partial read happens depends on where the file lands. On an SFTP server or a shared file system, a 400 MB file is visible with 120 MB written, and a loader that starts then reads a truncated file and exits cleanly; the defense there is either a temporary name that the sender renames on completion, or a check that the file's size and modification time haven't changed across 2 polls a minute apart. A cloud bucket lists an object only once its upload completes, so the risk moves up a level: a delivery split across several files can be half-landed, or a file can arrive whole yet truncated because the export behind it failed. There the sender writes a marker last (a _SUCCESS file or a manifest listing names and row counts) and the loader waits for it. The manifest's row count is the only check that catches a complete file missing rows.
The ledger
Every production file loader keeps a table of what it has loaded: 1 row per file, written before the load starts and updated when it ends. The loader consults it before touching a new arrival.
| file_name | size_bytes | row_count | status | loaded_at |
|---|---|---|---|---|
| settlements_2026-10-16.csv | 18,442,019 | 91,207 | loaded | 2026-10-17 03:12 |
| settlements_2026-10-17.csv | 19,011,882 | 94,660 | loaded | 2026-10-18 03:09 |
| settlements_2026-10-18.csv | 7,320,114 | 36,118 | loaded | 2026-10-19 03:11 |
| settlements_2026-10-18.csv | 19,280,540 | 95,402 | pending |
The 4th row is the interesting one. The October 18 file was loaded at 03:11 with 36,118 rows, under half the usual count, and a file with the same name but 95,402 rows has now appeared. The processor re-delivered a corrected file. The right behavior is to delete every row tagged with that file name and reload, which is only possible if each loaded row carries the name of the file it came from. The size drop should also have raised an alert at 03:11, since a daily file that falls 60 percent deserves one even if no replacement ever arrives.
INSERT INTO raw_settlements
SELECT
settlement_id,
merchant_id,
amount_cents,
settled_on,
'settlements_2026-10-18.csv' AS source_file,
current_timestamp AS loaded_at
FROM staged_settlementsInside the file
Questions to settle before the first load
- Header row: present or absent, and does the loader trust it or a fixed column list?
- Delimiter and quoting: a merchant name containing a comma must be quoted, and the loader must honor the quotes.
- Encoding: UTF-8 is assumed until a name with an accent turns into garbage.
- Dates: 10/11/2026 is October 11 in one country and November 10 in another.
- Empty values: an empty string, the word NULL and a missing field can mean 3 different things.
- Numbers: 1,234.50 and 1.234,50 are the same amount written in 2 locales.
Where file loads break
| Break | Symptom | Property that prevents it |
|---|---|---|
| Loader reads a half-uploaded file | Row count far below normal, no error | Wait for a marker or manifest before reading |
| Same file delivered twice | Every settlement counted twice | Ledger by file name, and rows tagged with source_file |
| File never arrives | Yesterday missing from reports in silence | An expected-by time with an alert when nothing has landed |
| Sender adds a column in the middle | Amounts land in the date column | Load by header name, and reject a file whose headers changed |
| Retry after a crash mid-load | Partial rows plus a full reload | Load into staging, then move into the target in 1 step |
- Do
- Tag every row with its source file and load time
- Alert on a late file as well as a failed one
- Compare row counts to the manifest and to the last 7 days
- Keep the original file for 30 days or longer
- Avoid
- Read a file the moment it appears in the bucket
- Trust column position over column name
- Delete the raw file once the load succeeds
- Treat a smaller-than-usual file as a quiet day
The scored version
'I would wait for the processor's manifest, compare its row count to the file, and check a ledger of loaded files so a re-delivery replaces rather than appends. Rows load into a staging table with the source file name and load time on each row, then move to the target in 1 step. Headers are matched by name, and a changed header fails the load with a message. An alert fires at 04:00 if no file has arrived.' That tells the interviewer the file is being treated as an unreliable input from an external party, which is the production stance this question exists to find.
About this lesson
This section is part of the How Data Moves: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.