Convert CSV to Parquet

Everything a CSV knows about itself is a header row, and a header row is a list of names. It cannot say that `amount` is a decimal, that `is_active` is a boolean, or that `customer_id` should never be treated as arithmetic. Every tool that reads the file has to work it out again, and every tool works it out slightly differently, which is why the same extract loads three ways in three places.

A Parquet file ends with a footer holding the schema, the number of rows, and per-column metadata. A query engine reads the footer first, which is a few kilobytes at the end of the file, and can tell you the column names and types before touching any data. That is a structural property rather than a convenience: it is what makes the format safe to hand to somebody who was not there when it was written.

Three things happen to each column on the way in and they compound. The values are written in their binary form rather than as decimal text, so a number that took eleven characters in the CSV takes four bytes. Each column is then compressed on its own with Snappy, which is the default codec here. And a column whose values repeat is dictionary-encoded: the distinct values are written once into a dictionary page, and the column itself becomes a list of small integer references into it.

The dictionary is applied per column and only where it pays. The writer samples the first thousand values and uses a dictionary when at most half of them are distinct, which catches the columns you would expect — country, status, product code, currency — and skips a column of unique free text where a dictionary would be larger than the data. That is why the saving on an operational extract is enormous and the saving on a table of distinct sentences is modest, and it is worth knowing which of those your file is before predicting the result.

The rows are written in groups rather than as one block, between one thousand and one hundred thousand rows each, with pages inside them capped at a megabyte. Each column chunk within a group carries statistics, and those statistics are what turn a scan into a skip.

The effect is the one people mean when they say Parquet is faster. A query filtering on a date range reads the footer, sees that a row group holds values entirely outside the range, and never reads that group at all. A query naming two columns out of forty reads two columns’ worth of bytes rather than every row. Neither of those is available on a CSV under any circumstances, because a CSV has to be read from the beginning to find out where anything is.

The type is inferred from the values rather than declared, since the source has nothing to declare. Each column is read in full: all booleans becomes BOOLEAN, all whole numbers inside the 32-bit range becomes INT32, numbers that are not all integers or not all small enough become DOUBLE, and anything else becomes a string. Nulls do not participate, so a numeric column with gaps stays numeric.

One stray value makes the entire column text, and that is deliberate rather than a limitation. Taking the type from the first row would write a column that begins with numbers as an integer column and turn every later non-numeric value into a null, producing a file that loads without complaint and has quietly deleted rows. A string column is visible, castable in one expression, and never loses anything. If a column arrives as text and you expected numbers, querying the values that fail to cast will show you a handful of rows with a footnote, a total or the word "none" in them — each of which is a true fact about the extract.

The conversion reads the CSV with type inference, so `007` is the number 7 by the time the writer is choosing a column type, and the resulting INT32 column is a faithful record of a value that was already wrong. Parquet gets the blame for this regularly and does not deserve it: the loss happens at the text-parsing step, which is the same step every other CSV reader performs.

The columns at risk are the ones where arithmetic would be meaningless — postcodes, part numbers, phone numbers, account references, anything zero-padded. If the extract is under your control, export those columns quoted or with a prefix and they arrive as strings intact. If it is not, check the column in the output before the file is written anywhere durable, because the whole point of moving to Parquet is that the file stops being re-derived.

On a table of fifty thousand rows and six columns — an identifier, a product code, a city, a quantity, a price and a flag — the Parquet output measured 301 KB. The same data written as tab-separated text was about 1.8 MB, and a CSV of it differs from that only by which byte separates the fields and by the quotes around values containing commas.

That is roughly a sixfold reduction, and it is a reasonable expectation for operational data with repeated codes and categories. A file dominated by unique free text will do considerably worse, because neither the dictionary nor the binary encoding has much to work with there. Both figures assume no compression on the CSV; a gzipped CSV closes part of the gap, at the cost of being unqueryable until it is decompressed in full.

No setup is required. DuckDB reads the file directly in a FROM clause, pandas reads it in a single call, and Spark treats it as a native table format. The file begins and ends with the four bytes PAR1, which is how each of them recognises it before reading the footer.

The first thing to look at after loading is the inferred schema rather than the first ten rows. A column you expected to be numeric that arrived as text is telling you something true about the CSV, and it is much cheaper to learn it now than after the file has been joined to three others and copied into a warehouse. Casting on the way into a table is one expression per column; casting blindly is how the rows you were protected from losing get lost anyway.

The whole table is held in memory: the CSV is parsed into rows, the rows are transposed into columns, and the columns are written. Tens of megabytes converts without ceremony and hundreds is where a tab begins to strain, which is a real ceiling and not a plan tier — there is no upload and nothing to pay for.

For a genuinely large extract the right instrument is DuckDB, which will read the CSV from disk and write Parquet in one statement without ever holding the file. Saying that plainly is more useful than letting a two-gigabyte conversion fail two-thirds of the way through. This page is for the file that fits, which is most of them, and for the case where installing something is not an option.

Both halves run on this page: the CSV parser and the Parquet writer are libraries loaded on demand, and no request carries the file anywhere. A CSV heading for a data platform is very often the least redacted artefact in the whole pipeline — the raw dump before the joins and the masking — and uploading it to a converter is precisely the step a data protection policy exists to prevent.

It also removes a tier from the conversation. There is no queue, no maximum row count and no account, so the only question is whether the file fits in memory, which you can answer by looking at it.

Parquet is a poor destination for anything a person has to read. It is binary, it is not editable, and a colleague without the right tooling cannot open it at all — the registry records its support as patchy for exactly that reason. If the file is going to a human being rather than an engine, send the CSV or a spreadsheet.

It is also the wrong target for a load that happens once and is then thrown away: the writing cost buys nothing if the file is never queried again, and a bulk loader reading delimited text directly will be faster end to end. Choose Parquet when the same data will be scanned repeatedly, filtered, joined and kept — which is exactly when the typed columns and the skipped row groups start paying for themselves.