Storage Layers & File Formats

So far the shop pipeline built tables. This chapter asks a new question: how are those tables stored as files on disk?

The answer decides how much data a query must read. Less data read means a faster and cheaper query.

What you already know

• Modeling: orders is a fact table. One row is one order.
• Loads: MERGE updates a row that exists and inserts a row that does not.
• Data quality: checks and staging tables keep bad rows out of the published table.
• CDC: a change feed sends many small changes, all day.
• Batch and streaming: a micro-batch job writes small pieces often.

What you will learn

1. Three storage layers: raw, cleaned and serving.
2. Why reading less data matters.
3. Row storage and column storage.
4. How to choose a file format.
5. Partitions, and the small-files problem.
6. What is inside a Parquet file.
7. Why a plain folder of files is not a table.
8. Table formats, and how they change one row.

We use the shop. Orders 110 and 111 are the made-up rows from the schema chapter.

A storage layer is one place where data sits, at one step of cleaning. The pipeline moves data from one layer to the next. Each step makes the data safer to use.

Three layers are common. Many teams call them bronze, silver and gold. Together, this is called the medallion architecture.

In this chapter we use plain names: raw, cleaned and serving.

Here is what each layer holds for the shop:

This query builds the serving layer from the cleaned layer. It adds up the paid orders for each day:

SELECT order_date,
       SUM(amount) AS revenue
FROM   cleaned_orders
WHERE  status = 'paid'
GROUP  BY order_date
ORDER  BY order_date;

Nobody edits the raw layer. If a cleaning rule has a bug, you fix the rule and rebuild from raw.

A scan is when the engine reads data from disk to answer a query. A byte is the unit we use to count stored data. The more bytes a query reads, the longer it takes.

Some services also charge by the bytes scanned. Google BigQuery (on its pay-per-query plan) and Amazon Athena both do this. Reading less data costs less money.

The shop table is tiny, so the cost is tiny. A real orders table can hold billions of rows. Then every wasted byte is paid for again and again.

Storage design gives you three ways to read less:

1. Read only the columns the query needs. The next section shows how.
2. Skip whole folders that cannot match. This is partitioning.
3. Skip parts of a file that cannot match. This uses the statistics inside a Parquet file.

The rest of the chapter takes these three ideas one by one. Then it shows how table formats keep the files safe to change.

A disk stores data as one long line of values. The file must choose an order for them. There are two main choices.

Row storage writes one whole row, then the next row. Column storage writes all values of one column, then all values of the next column.

Here are the six orders of the shop, written both ways:

Now run a report query. It needs only the amount column:

SELECT SUM(amount) AS total
FROM   orders;

The table has 6 rows and 5 columns, so 30 values. This is how many values each layout must read for that query:

Real files are read in blocks, so this count is a simple picture. The idea is true: a column file skips the columns you do not name.

Add WHERE status = 'paid' and the query needs two columns. Column storage then reads 12 of 30 values. The sum becomes 160.50.

Column storage has a second gain. A column holds one kind of value, and values repeat: paid, paid, refunded.

To compress is to store the same data in fewer bytes. Repeated values compress well.

Row storage still wins for some work. Showing order 103, or writing one new order, touches one whole row. That is why the app database that makes the orders uses row storage.

A file format is the set of rules for how data is written inside a file. Four formats are common in pipelines: CSV, JSON, Parquet and Avro.

CSV and JSON are text formats. You can read them in any text editor. Here are orders 101 and 105 in both:

-- CSV: one line per row
order_id,customer_id,status,amount,order_date
101,1,paid,40.00,2026-01-03
105,3,pending,,2026-01-20

-- JSON: one object per line
{"order_id": 101, "customer_id": 1, "status": "paid", "amount": 40.00, "order_date": "2026-01-03"}
{"order_id": 105, "customer_id": 3, "status": "pending", "amount": null, "order_date": "2026-01-20"}

Parquet and Avro are binary formats. Binary means the bytes are written for programs, not for people. A text editor cannot show them.

Both store the schema inside the file, with real types.

Parquet stores columns. Avro stores rows, one after another, and keeps its schema in the file header.

This table helps you choose:

A common plan: keep raw data as JSON or Avro, the way it arrives. Store cleaned and serving tables as Parquet, which is how analysts read them.

Partitioning splits a table into folders by the value of one column. The folder name holds the value. We partition orders by order_date.

This is the shop table as folders. Each folder holds one file:

orders/
  order_date=2026-01-03/part-0.parquet    -- order 101
  order_date=2026-01-10/part-0.parquet    -- order 102
  order_date=2026-01-11/part-0.parquet    -- order 103
  order_date=2026-01-15/part-0.parquet    -- order 104
  order_date=2026-01-20/part-0.parquet    -- order 105
  order_date=2026-01-21/part-0.parquet    -- order 106
  order_date=2026-01-22/part-0.parquet    -- orders 110 and 111

Now filter on the partition column. The engine reads the folder names and skips the folders that cannot match. This is called partition pruning:

SELECT order_id, amount
FROM   orders
WHERE  order_date >= '2026-01-20'
ORDER  BY order_id;

Pruning works only on the partition column. A filter on customer_id cannot skip a folder, because the folders are by date. The engine must open all seven.

Do not make files too small. The small-files problem is a table with very many tiny files.

Every file costs a request to open, and some extra bytes to read before any row.

Tiny files have two common causes. First, a job writes too often. A micro-batch job that writes a new file every minute makes 24 x 60 = 1,440 files in one day.

Second, a partition column has too many values. Partition by customer_id, and you get one folder for each customer.

Three habits keep partitions healthy:

• Partition by a column that queries filter on, such as the date.
• Choose a column with few distinct values. Use the date, not customer_id.
• Compact on a schedule. Compaction rewrites many small files into fewer large ones. Many guides suggest files of about 128 MB or more.

A Parquet file groups its rows. A row group is a block of rows, often thousands to millions. Inside a row group, each column is stored on its own, as a column chunk.

At the end of the file is the footer. The footer holds the schema.

For each column of each row group, it also holds the smallest value (min) and the largest value (max). It holds the number of NULLs too.

Picture one file with orders 101 to 106 in two row groups of three rows. Real row groups are much bigger. This is the footer for the amount column:

Now run a query for large orders:

SELECT order_id, amount
FROM   orders
WHERE  amount > 70;

The engine sends the WHERE filter down to the file reader. This is called predicate pushdown.

The reader then uses the footer to skip row groups. This is called data skipping.

An engine has three ways to skip data:

1. Partition pruning skips folders.
2. Footer statistics skip row groups inside the files that are left.
3. Column pruning reads only the columns the query names.

A data lake is a large store of files in cheap object storage, such as Amazon S3. Object storage keeps whole files. It cannot change a few bytes inside a file.

A plain folder of files has no table rules. Four things go wrong:

1. No all-or-nothing write. A reader can see a job that is half finished.
2. No list of files. The folder is the table, so old files and new files look the same.
3. No safe change. To change one row, you rewrite a whole file. Readers can see the change half done.
4. No history. After you replace a file, the old version is gone.

Here is problem 1 in the shop. A job rewrites the folder for 2026-01-22.

It writes the new file first, and deletes the old file after. A reader runs in between:

SELECT SUM(amount) AS revenue
FROM   orders
WHERE  order_date = '2026-01-22'
  AND  status = 'paid';

A table format adds a log to the folder. The log lists which files make up the table. Delta Lake, Apache Iceberg and Apache Hudi are the three common ones.

Readers read the log, not the folder. A writer saves its new files first.

Then it adds one entry to the log. The table changes only when that entry exists.

Here is a simplified log for the shop. To keep it short, we ignore the date folders.

Each commit is one entry:

The log gives you three things:

• Atomic commits. Atomic means all or nothing. In version 2, the remove and the add happen in one entry. A reader sees version 1 or version 2, never both files b and c.
• MERGE and UPDATE. The engine rewrites the files that hold the changed rows, then commits. The next section shows how.
• Time travel. Old files stay for a while, so you can read an old version of the table.

These two queries read the table at version 1 and at version 2. The syntax is Delta Lake. Iceberg uses a snapshot ID in place of the version number:

-- Version 1: before the refund
SELECT SUM(amount) AS revenue
FROM   orders VERSION AS OF 1
WHERE  order_date = '2026-01-22' AND status = 'paid';

-- Version 2: the current table
SELECT SUM(amount) AS revenue
FROM   orders
WHERE  order_date = '2026-01-22' AND status = 'paid';

The formats differ in where they came from and where they are strong. This table is a rough guide:

All three usually keep the data in Parquet files. They also record the schema in the log, so a column change is one more entry. The schema chapter showed how each format handles a column change.

The choice often follows your engine and cloud, so check what your tools support.

A Parquet file cannot be edited. So how does MERGE change one row? A table format has two ways.

A refund arrives for order 110. This MERGE applies it. The orders are in file b from the last section.

Look again at version 2 of the log. This MERGE made it:

MERGE INTO orders AS t
USING updates AS s
  ON t.order_id = s.order_id
WHEN MATCHED THEN
  UPDATE SET status = s.status;

Copy-on-write rewrites the whole file that holds order 110. The engine writes a new file c with both orders, and commits. This is what the log showed.

Merge-on-read does not rewrite file b. It writes a small marker, “row 110 in file b is deleted”, and a small file with the new row 110. Readers combine them when they run a query.

Here is the trade-off:

Compaction rewrites the small files and markers into clean files. Copy-on-write is the usual default. Check your engine, because some platforms turn on merge-on-read for new tables.

A CDC feed sends changes all day, so merge-on-read often fits it better.

Five points make a complete answer:

1. Parquet stores columns. A SUM(amount) reads one column, not five.
2. Parquet has types and a footer with min and max. Engines use it to skip row groups.
3. Partition by the column that queries filter on, such as order_date. Avoid many tiny files.
4. Delta and Iceberg add a log: atomic commits, MERGE and time travel. A plain folder can count orders twice: 78.00, not 39.00.
5. Name the costs: merge-on-read needs compaction, and old files need cleanup.

Build each scenario on the pipeline canvas with the chapter closed. The review tells you which requirement your design missed.