Loading Data: INSERT, Formats, and What Actually Happens

The problem an INSERT has to solve

MergeTree — the table engine nearly every ClickHouse table you'll encounter is built on — wants its data sorted and laid out efficiently on disk, so that scans and range lookups are fast; that's the whole promise of the engine. But data doesn't arrive pre-sorted and pre-compacted. It arrives constantly, in small, messy increments, from applications that have no idea what order the table wants its rows in.

Something has to reconcile those two facts: "reads want one big, sorted, tidy structure" and "writes show up as small, unsorted dribbles, all the time." Re-sorting and rewriting the entire table on every single INSERT would make writes catastrophically slow as the table grew — the exact opposite of what a database needs to do. So MergeTree takes a different approach: instead of merging new data into one giant structure immediately, every INSERT gets its own small, self-contained, already-sorted-internally file, written once and never modified again. A separate background process reconciles those files into fewer, larger ones later, on its own schedule, purely as later housekeeping — not something a query has to wait for.

That small, self-contained, immutable file is called a part. Understanding INSERT means understanding how a part gets made.

What happens, in order

When ClickHouse receives an INSERT statement, it does the following, in this order, before returning control to the client:

1. Parse and decode. ClickHouse reads the incoming data according to whatever format it was sent in (more on formats below) and decodes it into an in-memory block of columns — the same columnar shape the table will eventually store it in.
2. Sort the block. The rows in that block are sorted according to the table's ORDER BY expression. This happens on every INSERT, to every batch, regardless of what order the rows arrived in. (Why ORDER BY is the thing being sorted by, and what it costs to get this wrong, is its own lesson later in the course — for now, just note that sorting happens here, per batch.)
3. Write a new part. The sorted block is written to a brand-new directory on disk — one column file per column, plus an index file — with a name unique to that INSERT. Nothing existing is modified. This directory is the part.
4. Register and expose it. As soon as the part's files are written, it's immediately visible to any query that runs against the table. There is no waiting period, and no synchronous merge step blocking the INSERT from returning.

That's the entire lifecycle for one INSERT. Nothing else happens synchronously. The part just sits there, on disk, as one of potentially many, until a background merge process — running independently, on its own schedule — later decides to combine it with other parts into something larger. That merge is an optimization for future reads, not a requirement for the data to be correct or queryable right now.

Formats: how rows travel, not how they're stored

Every INSERT specifies, explicitly or by default, a format — the scheme ClickHouse uses to decode the incoming bytes into rows and columns. The format only describes step 1 above, the parsing step. It has no bearing on steps 2 through 4: every format gets sorted the same way, by the same ORDER BY expression, and lands in the same kind of column-file part on disk. Two INSERTs carrying identical rows, one sent as CSV and one sent as JSON, produce parts that are indistinguishable from each other once written — same columns, same sort order, same file layout.

ClickHouse supports dozens of formats, but four come up constantly:

INSERT INTO events (user_id, event_type, event_time) VALUES
    (1001, 'page_view', '2024-01-15 10:22:31'),
    (1002, 'click',     '2024-01-15 10:22:33');

INSERT INTO events FORMAT CSV
1001,page_view,"2024-01-15 10:22:31"
1002,click,"2024-01-15 10:22:33"

CSV is the obvious choice when data is already sitting in spreadsheets or exports from other systems that speak CSV natively.

INSERT INTO events FORMAT JSONEachRow
{"user_id": 1001, "event_type": "page_view", "event_time": "2024-01-15 10:22:31"}
{"user_id": 1002, "event_type": "click", "event_time": "2024-01-15 10:22:33"}

JSONEachRow is the natural fit for application code that already produces one JSON object per event — no CSV-escaping logic required, and no wrapping array that would force the sender to buffer the whole payload before it knows where the array closes.

INSERT INTO events
SELECT * FROM file('historical_events.parquet', 'Parquet');

Parquet is a columnar file format used widely across the data-lake and analytics-tooling world (Spark, pandas, and most warehouse export tools all read and write it). Loading Parquet directly — instead of exporting it to CSV first and re-parsing text — skips a wasteful round trip through an untyped intermediate format: Parquet already carries per-column types and a columnar physical layout, which is close to what MergeTree wants anyway.

Native is different in kind from the other three: it's ClickHouse's own binary wire format, the one clickhouse-client uses by default when talking to a server, and the fastest option precisely because there's no text parsing or per-row object overhead — the bytes on the wire are already close to the columnar block ClickHouse will sort and write. It's also the format ClickHouse itself uses for interaction between servers, not just between a client and a server. You won't typically hand-write Native data; it shows up as a --format Native flag or as the format two ClickHouse processes use to exchange data with each other efficiently.

The takeaway: pick a format based on what's convenient for whatever is producing the data. It changes nothing about how the table stores or sorts the result.