How to Profile a Data File Before You Build Anything On It

The report was wrong. Not because the query was wrong, but because the customer_id column was not unique, and the join multiplied every row by three. Ten minutes of profiling at the start would have shown that.

The short answer

Profile a file by computing statistics over every column, not by scrolling through rows. In DataTray, open Data Explorer → Columns: one pass over the file produces, for every column, the inferred type and semantic role, null count and rate, distinct count, min/max/mean/median, quartiles, shortest and longest value, the top ten values with frequencies, and a distribution sparkline. Cross-column, it reports candidate primary keys and correlations between numerics.

What a profile actually tells you

Four things, in rough order of how often they save somebody a day:

Is this column what its name says? A column called id that is 94% distinct is not an identifier, it is an identifier with a problem. A column called notes where 12% of values match a phone pattern is a privacy question nobody has asked yet.

Can I join on it? Distinct count over row count at exactly 1.0 means unique and non-null. Anything less and a join will fan out. DataTray reports candidate keys directly, including combinations of columns that are unique together when no single column is.

How much is missing, and where? A null rate is fine on its own; a null rate concentrated in recent rows means something upstream stopped populating a field.

What does the distribution look like? A sparkline catches things a summary statistic hides — a bimodal spread, a spike at zero, dates clustered on the first of the month because something is defaulting.

Type is inferred, not trusted

Headers lie and file formats forget. A CSV has no types at all, so everything in it is text until something decides otherwise, and that decision is usually made badly.

Profiling infers the type from the values, then goes further and infers the semantic role — email, phone, date, currency, postcode, country, identifier, category, free text. That second layer is what makes the profile actionable, because "this is a VARCHAR" tells you nothing and "this holds email addresses, and 38% of them do not parse" tells you what to do next.

Outliers, listed rather than removed

Outliers are flagged both by interquartile range and by z-score, per numeric column, and they are listed, never removed. An outlier is sometimes bad data and sometimes the single most interesting row in the file; a tool that quietly drops them is making a decision that is not its to make.

Each finding carries an affected-row count and a "show me these rows" action, so you go from a statistic to the actual records in one click and back again.

Ragged rows and the things structure hides

Some problems are not in a column at all. Rows with more fields than the header, duplicate column names, columns that are entirely empty or entirely one value — these are structural, they are common in exports, and they will break a load silently.

A profile that reports the rejected-row count tells you immediately whether the file parsed cleanly. If 1,200 rows failed to parse, you want to know that before you draw conclusions from the ones that did.

Do it on arrival, not on suspicion

Profiling is cheapest when the file lands and most expensive when somebody has already built something on it. It takes one pass and no configuration, and the output — a report and a data dictionary — is exactly what you would otherwise have to write by hand later.

Size should not decide whether you profile

The largest files are both the hardest to inspect by eye and the most consequential to get wrong, so the profiler has to work at any size. Because DataTray computes the whole profile as a query against the file on disk, a multi-gigabyte CSV and a folder of Parquet parts profile the same way a small file does.

---

Every other free way to do this asks you to upload the file first. Profiling a file you have not yet decided to trust is a strange moment to hand it to a stranger.