A glossary for people who work with data files
Every term below is one I have had to explain to somebody, usually while looking at a file that had gone wrong. They are grouped rather than alphabetized, because the groupings are where the meaning is: encoding problems cluster together, and so do columnar-format terms.
Where a term has a tool behind it on this site, the definition ends with a link to it. There is also a separate product glossary covering the parts of the ExploreMyData interface, which is a different vocabulary from this one.
The format itself
CSV
Comma-separated values: a plain text file where each line is a record and fields are separated by a comma. It has no types, no schema and no metadata, which is both why it is universal and why every other entry in this glossary exists. The extension is a convention, not a guarantee about the separator. Tool: Open a CSV →
DSV
Delimiter-separated values, the honest general name for the family. A file separated by semicolons, tabs or pipes is a DSV, and calling it a CSV is common usage rather than precision. Use the term when you need to be clear that the separator is a parameter. Tool: Change the delimiter →
TSV
Tab-separated values. The safest common choice for machine-to-machine transfer, because a tab character almost never appears inside a real data value, so quoting is rarely needed. Its weakness is invisibility: a tab and a run of spaces look identical, so hand-editing is dangerous. Tool: Convert TSV to CSV →
Delimiter
The character that separates one field from the next. Comma in most English locales, semicolon across continental Europe because the comma is taken by the decimal separator, tab in scientific tooling, pipe in banking and telecom exports. Tool: The delimiter guide →
Header row
The first line of a file when it names the columns rather than carrying data. Nothing in the format marks it as a header, so every parser has to guess or be told. A file whose first row happens to be all text is usually read as headered; one whose first row contains numbers usually is not. Tool: Check the header →
Record
One logical row of data. It is not the same as one physical line, because a quoted field may contain a line break. This distinction is why counting newlines is not the same as counting rows, and why splitting a file by line number can cut a record in half.
Field
One value within a record: the intersection of a row and a column. In a typed system a field has a declared type; in CSV it is always text, and any type it appears to have was inferred by whatever read it.
Quoting
Wrapping a field in double quotes so it can safely contain the delimiter, a quote character, or a line break. A field must be quoted if it contains any of those three and may be quoted otherwise. Many exporters quote every field, which is valid and slightly larger. Tool: Validate quoting →
Escaping
Representing a quote character inside a quoted field. In CSV the rule is to double it, so a value of he said hi with quotes around hi is written with two quote characters at each spot. Backslash escaping is a JSON and shell convention, not a CSV one, and assuming it is a frequent source of broken parsing. Tool: The quoting guide →
RFC 4180
The 2005 memo that describes the common CSV form: comma delimited, CRLF line breaks, optional double quoting, doubled quotes for escaping. It is informational rather than a standard, and real files deviate from it constantly, but it is the shared reference point when two parsers disagree.
Delimiter collision
When the separator character also appears inside a value, as in a city field holding Berlin, Germany. Quoting is the standard defence. Choosing a delimiter that does not occur in your data, like a tab or a pipe, is the other. Tool: Re-delimit a file →
Embedded newline
A line break inside a quoted field, which RFC 4180 explicitly permits. It is legal, common in comment and address columns, and the reason a file can have five physical lines and two records. Tools that split by line will destroy these rows. Tool: See parsed records →
Line endings
The bytes that mark the end of a line. Windows writes carriage return plus line feed, hex 0D 0A. Unix and modern macOS write line feed alone. Classic Mac OS wrote carriage return alone. Mixing them within a file produces blank rows, or a file that reads as one enormous line. Tool: The line endings guide →
CRLF
Carriage return followed by line feed, the Windows line ending and the one RFC 4180 specifies. Doubling it, usually by writing an explicit CRLF into a stream that already translates newlines, produces the classic blank row between every row of data.
Character encoding
The mapping from bytes to characters. A file is bytes; an encoding is the agreement about what they mean. When the writer and reader disagree you get garbled text or lost characters, and the file itself contains no reliable declaration of which encoding was used. Tool: The encoding guide →
UTF-8
The variable-width Unicode encoding that everything should use. ASCII characters take one byte, so plain English text is byte-identical to ASCII, while accented and non-Latin characters take two to four. It is the correct default for any new file.
Windows-1252
The single-byte encoding Windows used as its Western European default for decades, and a superset of Latin-1. Almost every legacy export that is not UTF-8 is this. Every one of its 256 byte values maps to a character, so decoding as Windows-1252 can never fail, which is exactly why bad guesses go undetected.
BOM
Byte order mark: the three bytes EF BB BF at the start of a UTF-8 file. UTF-8 has no byte order to mark, so the sequence is purely a signal that the file is UTF-8. Excel on Windows uses it to auto-detect; many other parsers do not strip it and it ends up glued to the first column name. Tool: Detect a BOM →
Mojibake
Text that is unreadable because it was decoded with the wrong encoding. The signature is a Latin letter followed by punctuation where an accented character belongs. The good news is that no data was lost: the bytes are intact and re-reading with the right encoding restores everything. Tool: Repair mojibake →
Replacement character
U+FFFD, drawn as a black diamond with a question mark. A decoder produced it when it hit bytes it could not map. Unlike mojibake this is destructive: the original bytes are gone and only a fresh copy of the file will recover them.
Dialect
The full set of parameters that describe how one particular CSV file is written: delimiter, quote character, escape convention, line ending, whether there is a header, and how nulls are represented. Two files can both be valid CSV and share no dialect at all. Tool: Set parse options →
Sniffing
Automatic dialect detection. A sniffer samples the head of the file, tries each candidate delimiter, and picks the one producing the most consistent field count. It works well and fails predictably: on files with a prose preamble, on genuinely ragged data, and when the sample is unrepresentative. Tool: See the detected dialect →
Fixed-width
A layout with no delimiter at all: each field occupies a fixed number of character positions, padded with spaces. Common in mainframe and banking extracts. Parsing requires a column specification, because the file carries no clue about where the boundaries are. Tool: Fixed-width to CSV →
Ragged rows
Rows whose field count differs from the header's. Almost always a symptom of broken quoting or an unescaped delimiter. Some parsers throw, some pad the short rows with nulls, and some silently shift every value one column left, which is the dangerous one. Tool: Find ragged rows →
sep= line
A Microsoft convention where a first line reading sep=; tells Excel the separator regardless of regional settings. Excel consumes the line. Most other parsers treat it as a one-column data row, so it helps in exactly one place and causes confusion everywhere else.
Types and values
Type inference
Deciding what a text value means by looking at it. It is why 02134 becomes 2134 and why a part code like MAR1 becomes a date. Inference is unavoidable when reading a format with no schema, and the fix is always to declare the type rather than to argue with the guess. Tool: Convert types →
Schema
The declared structure of a table: the column names, their types, and any constraints. CSV has no place to put one, which is why the same schema gets re-declared in every script that reads the file. Parquet, Avro and databases store it with the data. Tool: Profile the columns →
Null versus empty
In a database, NULL means unknown and an empty string means a known value of zero length. CSV cannot express the difference: both are written as nothing between two delimiters. If the distinction carries meaning in your data, CSV is the wrong transport. Tool: Fill missing values →
Cardinality
The number of distinct values in a column. Low cardinality columns, like a status with five possible values, compress dramatically in columnar formats and make good grouping keys. High cardinality columns, like a UUID, do neither. Tool: Count distinct values →
Precision and scale
For a decimal number, precision is the total count of significant digits and scale is how many of them sit after the decimal point. A DECIMAL(10,2) holds amounts up to eight digits with two decimal places, exactly, which is what money needs and what a float cannot promise.
Safe integer
The largest whole number a double precision float can represent exactly, which is 2 to the 53rd minus 1, a little over nine quadrillion. Above it, integers start rounding. Excel, JavaScript and JSON all inherit this limit, which is why long identifiers must be strings. Tool: The precision guide →
Scientific notation
The 1.23E+15 display for a large or small number. In a spreadsheet it can mean two different things: a formatting choice that hides digits, or a value that has already been rounded to fifteen significant digits. Widening the column tells you which. Tool: Keep long IDs intact →
Leading zeros
The zeros at the front of an identifier, as in a New England zip code or a padded SKU. They exist only in the text form, so any tool that reads the value as a number discards them. The result still looks like a plausible value, which is what makes it dangerous. Tool: The leading zeros guide →
ISO 8601
The date and time format that starts with the largest unit: 2026-04-03 for a date, 2026-04-03T14:30:00Z for a UTC timestamp. It is unambiguous in every locale, it sorts correctly as plain text, and every modern parser reads it without configuration. Tool: The dates guide →
Locale
The regional settings that decide a machine's list separator, decimal separator and short date order. Two people opening the same CSV in different locales get different columns and different dates, which is why a file that works in one office breaks in another.
Decimal separator
The character between the whole and fractional parts of a number. A period in English locales, a comma across most of continental Europe and South America. A semicolon-delimited file usually carries comma decimals too, and both settings have to be handled together. Tool: Set the decimal separator →
Thousands separator
The grouping character inside a large number, as in 1,234,567. It is presentation, not data, and it is the most common reason a numeric column arrives as text that will not sum. Tool: Convert to a number →
Serial date
How a spreadsheet stores a date internally: a count of days since an epoch, with time as a fraction of a day. Excel's epoch is 30 December 1899, offset by one to accommodate its treatment of 1900 as a leap year, which it was not.
Epoch
The zero point of a time representation. Unix time counts seconds since 1 January 1970 UTC. Spreadsheet serials count days since 1899. A timestamp column of ten-digit integers is almost always Unix seconds; thirteen digits is milliseconds. Tool: Convert an epoch column →
Boolean representation
There is no boolean in CSV, so a true or false value arrives as one of true, TRUE, T, Y, Yes, 1 or an empty cell, often several of them in the same column. Normalizing them is usually the first cleaning step on any survey or CRM export. Tool: See every distinct value →
Columnar and binary formats
Columnar
A storage layout that keeps all values of one column together, rather than keeping all values of one row together. It makes reading three columns out of forty cheap, and it makes compression far more effective, because similar values sit next to each other.
Parquet
Apache Parquet: the standard columnar file format for analytics. It stores column types, compresses each column independently, and records per-block statistics that let a query engine skip data it does not need. Typically three to seven times smaller than the same data as CSV. Tool: CSV to Parquet →
Arrow
Apache Arrow: a columnar layout for data in memory, designed so that different tools can share buffers without copying or converting. Parquet is the disk format, Arrow is the memory format, and most readers move data from one to the other. Tool: Open an Arrow file →
Feather
A file format that writes Arrow buffers directly to disk. It is very fast to read and write and does not compress as well as Parquet, which makes it a good intermediate format and a poor archive format. Tool: Open a Feather file →
Avro
A row-oriented binary format from the Hadoop world that stores its schema in the file header as JSON. Good for streaming and message payloads where whole records are read at a time; less suited to analytics, where columnar layouts win. Tool: Avro to CSV →
Row group
A horizontal slice of a Parquet file, typically tens or hundreds of thousands of rows, holding a chunk of each column plus minimum and maximum statistics. Those statistics are what allow whole slices to be skipped without decompression.
Dictionary encoding
Replacing repeated values with small integer codes plus a lookup table. A region column with five distinct values across two million rows stores five strings and two million small integers, which is where most of Parquet's compression advantage comes from.
Run-length encoding
Storing a repeated value once with a count rather than repeating it. Extremely effective on sorted or naturally clustered columns, and one reason that sorting a file before writing it as Parquet can shrink it further.
Predicate pushdown
Pushing a filter down into the reader so that data which cannot match is never decompressed. A date filter against a Parquet file written in date order can skip most of the file. CSV has no equivalent, because there is nothing to push down into.
Column pruning
Reading only the columns a query actually names. In a columnar format the unread columns are never touched; in a row format every byte has to be scanned regardless. On a wide table this is the difference between seconds and minutes.
Compression codec
The algorithm used to compress a data block. Snappy is fast and moderate, Zstd is slower to write and noticeably smaller, Gzip is widely supported and slowest. Snappy is the usual default for Parquet and Zstd is the usual answer when file size matters. Tool: Choose a codec →
JSONL
JSON Lines, also called NDJSON: one complete JSON object per line, with no wrapping array and no commas between records. It streams, it appends, and a corrupt line only costs you that line. It is the right choice for logs and for machine learning training data. Tool: JSONL to CSV →
Flattening
Turning nested JSON into columns by joining the path with a separator, so a value at customer.address.city becomes a column of that name. It works cleanly for objects, which have fixed named keys, and not at all for arrays, which do not. Tool: JSON to CSV →
SQLite file
A single-file relational database. It holds multiple tables with real types and indexes, and it is a genuinely good way to ship a dataset that has more than one table in it. Reading one requires a library rather than a text editor. Tool: SQLite to CSV →
DuckDB file
The on-disk database format used by DuckDB. Like SQLite it holds many tables in one file, but it is columnar and built for analytics rather than transactions. Attaching one and copying tables out is the usual way to read it. Tool: Open a DuckDB file →
Shaping data
Grain
What one row of a table represents: one order, one order line, one customer per month. Almost every wrong number in a report comes from an operation that changed the grain without anybody noticing, most often a join or an explode.
Explode
Turning one row containing an array into one row per element, repeating the parent fields. It is how you make array contents analyzable, and it changes the grain, so counting rows afterwards counts elements rather than parents. Tool: Unnest an array →
Pivot
Turning distinct values of one column into columns of their own, with an aggregate at each intersection. Months across the top, regions down the side, revenue in the cells. It makes a table readable by a person and harder for a machine to process. Tool: Pivot a CSV →
Unpivot
The inverse: collapsing many value columns into a key column and a value column. Wide becomes long. It is the fix for a file with ten thousand columns, and the usual first step before charting data that arrived in a presentation layout. Tool: Unpivot a table →
Join
Combining two tables by matching values in a key column. Inner keeps only matching rows, left keeps every row of the left table, full keeps everything from both, and cross pairs every row with every row. Choosing the wrong one is how row counts silently multiply. Tool: Join two files →
Union
Stacking two tables with the same shape on top of each other. Unlike a join it adds rows rather than columns, and unlike a join it requires the schemas to agree. Combining twelve monthly exports into a year is a union. Tool: Append files →
Deduplication
Removing rows that repeat. Exact deduplication compares whole rows or a chosen key. The harder case is near-duplicates, where ACME Corp and acme corp. are the same customer written twice. Tool: Remove duplicate rows →
Levenshtein distance
The number of single-character insertions, deletions or substitutions needed to turn one string into another. It is the standard measure behind fuzzy matching: a distance of one or two between two company names usually means a typo rather than two companies. Tool: Find similar values →
Forward fill
Carrying the last non-empty value down into the blanks below it. It is the repair for a file exported from merged spreadsheet cells, where a category name appears once and the rows beneath it look empty but are not. Tool: Fill missing values →
Normalization
Making values that mean the same thing look the same: trimming whitespace, unifying case, mapping Y and Yes and TRUE onto one value. Unglamorous, and usually the step that determines whether a join finds its matches. Tool: Clean text values →
Analysis vocabulary
Aggregate
A function that reduces many rows to one value: count, sum, average, minimum, maximum. Paired with GROUP BY it turns a four million row file into a twelve row answer, which is usually what somebody actually wanted. Tool: Run an aggregate →
Window function
A calculation over a set of rows related to the current row, without collapsing them. Running totals, rank within a group, and the difference from the previous row are all window functions, and all of them are painful without one. Tool: Add a window function →
Rolling window
A window that moves with the row, such as a seven-day moving average. It smooths noise in a time series and it is the standard way to make a daily metric readable without throwing away the daily grain. Tool: Add a rolling window →
Percentile
The value below which a given share of the data falls. The 95th percentile of response time is the value that 95 percent of requests came in under, and it says far more about user experience than the mean does. Tool: See percentiles →
Median
The 50th percentile: the middle value when the data is sorted. It is resistant to outliers in a way the mean is not, which is why salary and house price are almost always reported as medians. Tool: Profile a column →
IQR
The interquartile range: the 75th percentile minus the 25th. It measures spread using only the middle half of the data, so a few extreme values do not distort it. It is the basis of the standard box plot and of one common outlier rule. Tool: Compute the IQR →
Outlier
A value far from the rest of the distribution. Two common rules: more than 1.5 times the IQR outside the quartiles, or more than three standard deviations from the mean. Neither says the value is wrong, only that it deserves a look. Tool: Find outliers →
Z-score
How many standard deviations a value sits from the mean. It makes values from different columns comparable, and it is the usual outlier test for data that is roughly normally distributed. It is a poor test for skewed data, where the IQR rule behaves better. Tool: Compute a z-score →
Correlation
A measure between minus one and one of how strongly two numeric columns move together. It says nothing about cause, and it only detects linear relationships, so a strong curved relationship can show a correlation near zero. Tool: Correlate columns →
Distribution
The shape of the values in a column: where they cluster, how far they spread, whether there are two peaks. Looking at the distribution before computing anything catches more data quality problems than any single test. Tool: See a distribution →
Histogram
A chart of a distribution, with the value range split into bins and a bar for the count in each. The bin width changes the story, which is why a histogram with too few bins can hide exactly the structure you were looking for. Tool: Build a histogram →
Variance analysis
Comparing two versions of the same measure, usually a baseline and a current period, and explaining the difference by breaking it down across a category. The useful output is not the total change but the ranked list of what drove it. Tool: The variance guide →
Engines and runtime
SQL
The query language for tabular data. Its value here is that it is declarative: you describe the result you want and the engine decides how to produce it, which is why the same SELECT works on a thousand rows and on ten million. Tool: Run SQL on a file →
DuckDB
An in-process analytical database, columnar and vectorized, designed to run inside another program rather than as a server. It reads CSV, Parquet, JSON and Arrow natively, and it is the engine underneath everything on this site. Tool: Query with DuckDB →
WebAssembly
A portable binary instruction format that browsers execute at close to native speed. It is what makes a real database engine possible inside a tab, and it is why a file can be analyzed without ever being uploaded anywhere. Tool: See it running →
View versus table
A view is a stored query that runs every time it is read; a table holds materialized rows. Views keep a chain of transformations cheap and always current. Materializing partway through a long chain trades memory for speed.
Virtual file
A buffer registered with a database engine under a filename, so that SQL can read it as though it were on disk. It is how a browser-based engine reads a file the user dropped, with no filesystem involved at any point.
Worker thread
A background thread in a browser, separate from the one that draws the page. Parsing a large file on a worker is what keeps the interface responsive and the progress bar moving instead of freezing the tab.
Geospatial and other
WKT
Well-known text: a plain text way of writing geometry, as in POINT(13.4 52.5) or POLYGON with a list of coordinates. It travels fine inside a CSV column, which makes it the usual way geometry survives a spreadsheet round trip. Tool: Inspect a WKT column →
GeoJSON
A JSON format for geographic features, pairing a geometry with a properties object. Converting it to a table means flattening the properties and keeping the geometry as text, usually WKT, in one column. Tool: GeoJSON to CSV →
Checksum
A short value computed from a file's bytes, used to prove that two copies are identical. Recording one alongside an export is the cheapest way to settle an argument about whether a file changed in transit.
Idempotent
An operation that gives the same result whether it runs once or five times. Cleaning steps should be idempotent, because pipelines get re-run, and a trim that is safe to repeat is very different from an append that is not. Tool: Build a rebuildable pipeline →