How to Generate a Data Dictionary From a File You Already Have

A data dictionary is one of those documents that everybody agrees should exist and nobody wants to write, because writing one means opening a forty-column file and describing all forty columns by hand.

The short answer

Most of a data dictionary can be derived from the file itself. Name, type, semantic role, null rate, distinct count, minimum and maximum, and sample values are all measurable — only the description needs a person. In DataTray, open Data Explorer → Columns and export the data dictionary: it produces one row per column with everything measurable already filled in and an editable description field for the rest. It exports as .xlsx for circulation and as a Frictionless Table Schema .json for anything that needs to read it programmatically.

What belongs in one

A dictionary that helps somebody is more than a list of column names and types:

That last row is the point. A generator's job is to remove the forty pieces of clerical work so you can spend your attention on the one thing that needs judgement.

Semantic role is what makes it useful

Knowing a column is VARCHAR tells you almost nothing. Knowing it holds email addresses tells you what it is for, what a valid value looks like, and that it probably needs handling under a privacy policy.

DataTray infers the role from the values rather than the header, which matters because headers lie — a column called id is frequently not unique, and a column called notes is frequently where somebody has been storing phone numbers.

The number that tells you it is a key

Distinct count over row count is the most informative ratio in a profile. At 1.0 the column is unique and is a candidate primary key. Near 1.0 it is nearly unique, which usually means it is meant to be a key and something has gone wrong. Very low, and it is a category.

DataTray reports candidate keys directly, including small combinations of columns that are unique together when no single column is. That is the fact people most often open a file to establish, and it is worth having in the dictionary rather than re-deriving each time.

Two output formats, one scan

.xlsx is what to circulate. Everybody can open it, and colleagues can fill in descriptions and comment on rows without any tooling.

Table Schema JSON is the open specification for describing tabular data — field names, types, constraints, and a primary key. It costs nothing extra to emit from the same scan, and it is what makes the dictionary useful to a validator or a pipeline rather than only to a reader. If you have never needed it, the .xlsx is the one you want.

Do it before the file becomes somebody else's problem

The right moment to generate a dictionary is when the file arrives, not when someone asks for one six months later. Two minutes of profiling at the point of receipt answers most of the questions that would otherwise become a thread.

It is also the cheapest possible review of whether a file is what its sender thinks it is. A column that is 60% null, a date range that starts in 1970, a supposedly unique identifier with duplicates — the dictionary surfaces all three before anything is built on top of them.

On files that will not open

A dictionary is most valuable for the files that are hardest to inspect, which are usually the large ones. Because DataTray reads the file on disk rather than loading it into memory, the size of the file is not what decides whether you can profile it — a multi-gigabyte CSV and a folder of Parquet parts both profile the same way.

---

Every other free way to do this asks you to upload the file first. If the file is worth documenting, it is usually worth not uploading.