Managing Delta Updates in Immutable Columnar Formats

In this lesson, you will learn how to handle data updates in read-optimized, immutable columnar storage without sacrificing performance.

The Challenge of Immutability

In previous lessons, we discussed how columnar formats (like Parquet or ORC) offer high analytical efficiency through compression and projection. However, these formats are immutable—once a file is written, you cannot simply change a single value inside it. Because the data is stored in blocks optimized for high-throughput scans, modifying one row would theoretically require rewriting the entire file, which is prohibitively expensive for large datasets.

The Delta File Strategy

To solve this, we use a strategy known as Merge-on-Read. Instead of modifying the base file, we store incoming updates in small, separate files called delta files.

When a query is executed, the storage engine performs the following steps:

1. It reads the large, immutable base file.
2. It simultaneously reads the small delta files containing the changes.
3. It merges these two streams at query time, prioritizing the information in the delta files.

Conceptual Illustration

Imagine you have a base file users.parquet containing million-row datasets. If you update a user's address, you create a tiny delta_001.parquet file:

-- Base Table (users.parquet)
| ID | Name  | City    |
| 1  | Alice | NY      |

-- Delta File (delta_001.parquet)
| ID | City    |
| 1  | LA      |

When your query engine performs a scan, it identifies the change for ID 1 in the delta file and overrides the value found in the base file. The system essentially treats the final result as Base + Delta = Current State.

Efficiency Through Compaction

While Merge-on-Read is excellent for write throughput, having too many delta files can slow down analytical queries. To maintain performance, we periodically perform compaction. This process merges the base files and delta files into a new, single, immutable columnar file, clearing the backlog of updates and ensuring that subsequent reads remain fast.

Key Takeaway: Since immutable columnar formats cannot be updated in-place, we utilize delta files to track changes and merge them at query time, periodically compacting the data to maintain optimal read performance.