Spend the first twenty minutes on the bytes
Before any parser touches the customer export, open the raw bytes. You are looking for the encoding, a byte-order mark, the delimiter, whether quoted fields contain line breaks, and whether there are header rows, footer totals or blank separators that a spreadsheet user added. An export that went through a spreadsheet on the way to you often carries its scars: identifiers with leading zeros stripped, long numbers turned into scientific notation, and dates rewritten in a local format.
Then read every column as a string. This is the step that protects everything after it, and it is covered in its own section below.
import pandas as pd
raw = pd.read_csv("export.csv", dtype=str, keep_default_na=False,
encoding="utf-8-sig")
profile = pd.DataFrame({
"non_empty": (raw != "").sum(),
"distinct": raw.nunique(),
"sample": pd.Series({c: raw.loc[raw[c] != "", c].head(3).tolist()
for c in raw.columns}),
})
print(len(raw), "rows")
print(profile)
Profile, then infer the schema from evidence
The profile gives you, per column, how often it is filled, how many distinct values it has, and what a few values look like. From that you infer types as claims you can test. A column whose distinct count equals the row count is a candidate key. A column with five values covering nearly every row is a category. A date column where every value has a first part of twelve or less is ambiguous between day-first and month-first, and you need one value above twelve, or the customer, to settle it.
Decide the grain at this point too. Ask what one row is: an order, an order line, or a snapshot of an order on a given day. If the candidate key repeats, the grain is not what you assumed, and every total you would have built on it is wrong. The deeper techniques for this, including testing joins rather than assuming them, are on schema discovery with no documentation.
Show progress at the end of the first hour
At around the one-hour mark, send the customer a short message, not a product. List what you found and ask them to confirm the decisions you cannot make alone.
Export received: 48,210 rows, 23 columns.
What I need from you before I build further:
1. order_ref repeats on 1,904 rows. Is a row an order line, not an order?
2. Dates are ambiguous in 71% of rows. Are they day-first (UK format)?
3. 6% of rows have no region. Leave them out, or report them as "Unknown"?
Next update in one hour, with a loadable table.
Those numbers are illustrative, but the shape is the point. Three precise questions get answered in minutes. A finished dashboard built on the wrong grain gets discovered at the end, when nothing can be fixed.
Data validation: accepted and rejected rows, with reasons
Write the data validation rules down as code: required fields present, dates parse in the agreed format, amounts are numeric, the key is unique at the agreed grain. Every row ends up in exactly one of two places. Accepted rows go on to the load. Rejected rows go to a file with the original values untouched and a column naming the rule that failed.
The validation report is a one-line reconciliation that must always balance: rows received equals rows accepted plus rows rejected. When it balances, the customer can trust the totals. When it does not, you have a bug in your own code, and you have found it before they did. If one rule rejects a large share, take that back as a question, because a fifth of rows failing a rule is usually a business process nobody mentioned. Segmenting missing values covers how to read that.
Make the load idempotent
The customer will send a corrected export. Plan for it from the start. Load into a staging table keyed on the natural key you confirmed, and upsert, so running the same file twice produces the same table.
INSERT INTO staging.order_lines (order_ref, line_no, amount, order_date, load_id)
SELECT order_ref, line_no, amount, order_date, :load_id
FROM incoming_order_lines
ON CONFLICT (order_ref, line_no)
DO UPDATE SET amount = EXCLUDED.amount,
order_date = EXCLUDED.order_date,
load_id = EXCLUDED.load_id;
The load_id records which file each row came from, so you can answer "is this figure from the old export or the new one". If the corrected file is a full replacement rather than a delta, truncating staging and reloading inside one transaction is simpler and just as safe. What matters is that a rerun is a normal operation, not an emergency.
Letting the parser guess types destroys evidence
The single decision that separates a strong answer is refusing type inference when you first read a customer export. A parser that guesses will silently turn the identifier 00412 into the number 412, read 03/04/2026 as whichever month its locale prefers, and turn the literal text NA, which may be a real region code, into a null. None of these raise an error. All of them change the data before you have looked at it, and you cannot tell afterwards which values were changed.
Reading everything as text keeps the original evidence. You then cast each column on purpose, with a rule you can show the customer, and any value that fails the cast lands in the rejected file instead of disappearing. A candidate who says this unprompted has usually been burned by it.
What usable means at hour three
At the end, the customer should have a staging table they can query, a rejected-rows file they can fix at source, a validation report whose numbers balance, and a short note listing the assumptions they confirmed. A chart is optional. A table they trust, and a clear account of what was left out and why, is the deliverable.
In three hours, the most useful thing you build is the evidence: a profile, a report that reconciles to the source file, and a load you can rerun when the file changes.
© 2026 Preptima. Originally published at preptima.com (opens in a new tab).