The Delta table API: a folder of files that behaves like a table

Before anything else: a table here is a folder, and the folder is just files

Start with the physical situation, because every confusing thing later comes from people describing the logical situation as though the physical one did not exist.

You have data. Say a list of orders. Each order is a row, meaning one complete record about one thing, written as a line of values that belong together. Each thing you record about that order is a column, meaning one named field that every row has: an order number, a customer, a country, an amount. Rows and columns together make a table, which in the ordinary sense of the word is a rectangle of values with named columns.

Now, where does that rectangle physically live? In the systems this piece is about, it does not live inside a running program that owns it. It lives in a folder on cheap storage, the same kind of folder you have on a laptop, usually rented from a cloud provider. Inside that folder are data files, each one holding some of the rows. Not all of them. Some.

The figure below is that folder. Each rectangle is one file, and the number written on it is how many rows are inside that file, because a file's row count is the only thing about it that matters for now. Click a file to see which orders it holds.

That is the whole physical setup, and it is worth sitting with for a moment because two things follow from it immediately.

First, reading the table means reading several files and stacking their rows. There is no other step. Nobody consults an index, nobody asks a server. A program opens the files it has been told to open, and the rows it gets are the table.

Second, and this is the uncomfortable one: nothing in that folder says which files are part of the table. The folder is a folder. If somebody drops a stray file in, it is now apparently part of your data. If a program crashes halfway through writing, whatever it managed to write is sitting there looking exactly like a real file. The folder has no opinion.

So the folder gives you storage and nothing else. To see why that is not enough, it helps to watch it fail at something completely ordinary.

Watch a plain folder fail at one ordinary job

Here is the job: today's orders have arrived, and you want to add them to the table. In the physical picture, that means writing a new file into the folder. The word for that is a write, meaning any operation that changes what is stored.

Adding one file sounds safe. But writing a file is not instantaneous: a big file takes time, and during that time the file exists and is incomplete. Meanwhile somebody else, perhaps a dashboard refreshing on a timer, is a reader, meaning any program opening those files to get the rows out. It has no idea a write is in progress, because nothing tells it.

In the figure, the writer's progress is drawn as a file that fills up over time, and the reader is drawn as an eye that opens at a moment you choose. Press the two buttons in either order and see what the reader gets.

Read during the write and you get a number that was never true: yesterday's rows plus part of today's. Nothing failed. No error was raised. The dashboard shows a figure, somebody screenshots it for a meeting, and the number is simply wrong, in a way that is impossible to detect afterwards because five minutes later the file finished and everything looks fine.

The property missing here has a name worth learning now, because it is the whole point of the machinery that follows. An operation is atomic when, from the point of view of anybody looking, it has either completely happened or not happened at all, with no observable in-between. The write above is the opposite: its in-between is fully visible, and readers land in it.

Now try the second job, which is worse. Change one row: order 1043 was recorded with the wrong amount. The rows live inside a file, and a file cannot be edited in place on this kind of storage. To change one row you must write a new version of the whole file and get rid of the old one, and between those two acts, the folder contains either both copies or neither.

That contents page is the entire idea. It has a name, it is a real file on disk, and everything else in this piece is either writing one or reading one.

The log is a numbered list of which files count, and it is written last

Inside the folder, alongside the data files, sits a subfolder named _delta_log. It contains small numbered files: 00000000000000000000.json, then ...001.json, and so on. Together they are the transaction log, meaning an append-only record of every change ever made to the table, where append-only means new entries are added at the end and existing entries are never edited.

Each numbered file records one commit, meaning one completed change, described as a list of data files added and data files removed. That is genuinely most of what is in there. Not the rows. Just: these files joined the table, these files left it.

The state of the table after commit number n is called version n. Version 0 is the table after the first commit, version 1 after the second, and so on. A folder with a log like this, read through the log rather than directly, is a Delta table.

Here is the order of events that makes it work, and it is the one detail to hold onto. The data file is written first, completely, while nobody is looking at it, because nothing in the log mentions it yet. Only when the file is finished does the writer append a log entry naming it. Readers consult the log, so the file becomes part of the table at the instant that one small entry appears, and not a moment before.

Advance the write halfway and read: the reader still gets yesterday's count, exactly and correctly, because the half-written file is not named in any log entry and so is not part of the table. Advance to the commit and read again: the number jumps in one go. There is no moment at which a reader sees a partial answer. The write has become atomic, and nothing about the storage changed to achieve it. What changed is that the definition of the table moved from "whatever is in the folder" to "whatever the log says".

Once you see that, the rest of the design follows almost mechanically. Updating a row means writing a new data file with the corrected row, then committing one entry that says: add the new file, remove the old one. Both halves land together or neither does. Deleting rows is the same shape. So is everything else.

All of this is a description of files on disk. What you actually type is a different question, and that is where the word API starts to matter.

An API is the set of calls you are allowed to make, and Delta gives you two

An API, short for application programming interface, is the list of operations a piece of software offers to programs that use it, along with what each one expects and returns. It is the menu. It is not the kitchen.

You will meet the Delta table API in two forms, and confusion between them costs beginners a lot of time, so here they are side by side.

The first form is SQL, where you write a statement as text and the engine works out what to do. The second is the programmatic API, where your code obtains an object representing the table and then calls methods on it. A method is just a function that belongs to an object, written after a dot: table.delete(...). Both forms reach the same files and the same log. Neither is more real than the other.

Two more words before the code, both of which appear in every example you will ever read. A path is the address of the folder, written as a string, like /lake/orders. And a DataFrame is the in-memory representation of a set of rows and columns that your program is currently working with: the rectangle, loaded, sitting in the program, ready to be filtered or counted or written somewhere. A DataFrame is not the table. It is a copy of some rows from it, held in memory.

The programmatic route needs one more idea, and it is the piece of vocabulary that makes most Delta documentation readable. Before calling methods on a table, your program needs an object standing for it. That object is a handle: a value in your program that refers to something outside your program, holding the address and knowing how to reach it, rather than holding a copy of the contents.

You obtain one like this, and the name of the call says exactly what it does, which is get me a handle for the Delta table living at this path:

Note what dt is not. It is not the rows: creating it reads a little metadata and no data. It is not a snapshot frozen in time either; ask it for rows again after somebody else commits, and you get the newer state. It is a way of addressing the table, which is why almost every operation in the rest of this piece starts by getting one.

With a handle in hand, the simplest thing to do with a table is put rows into it.

Writing rows: two modes, and the one that quietly deletes everything

Writing is done through the DataFrame, not the handle. That surprises people, and the reason is simple once said: you are asking a set of rows to save itself somewhere, so the rows are the sensible starting point. The call chain reads left to right as a sentence.

The mode is the part to slow down on, because it decides what happens to rows that are already in the table, and the two common values do opposite things.

append means add these rows and leave everything else alone. On disk: one new data file, one commit entry adding it, nothing removed. overwrite means this DataFrame is now the entire contents of the table. On disk: new data files, and a commit entry that removes every file that was live before.

In the figure, the folder is drawn as before, and each commit is drawn as a row in the ledger showing what it added and what it removed, because add and remove are the only two things a commit ever contains. Run the same DataFrame in both modes and compare the ledger.

An overwrite with the wrong DataFrame is the classic way to lose a table, and it is worth being precise about what "lose" means here, because it is less bad than it sounds. The old data files are removed from the table, meaning the newest commit no longer lists them. They are still sitting in the folder. The table at the previous version still names them and can still be read. Recovery is possible, for a while, and there is a call for it later in this piece.

One more term arrives with writing, unavoidably. The schema of a table is its column names and the type of each one: order_id is text, amount is a decimal number, and so on. Delta records the schema in the log and checks every write against it. Try to append rows with a column the table does not have, or with text where numbers belong, and the write fails rather than landing silently.

That refusal is a feature, and it takes people by surprise the first time. The alternative, which plain file folders offer, is that mismatched data lands happily and is discovered months later by somebody whose report is producing nonsense. If the new column really is wanted, you say so explicitly:

Adding rows is the easy half. The interesting calls are the ones that change rows already in the table, and to follow those you need to see what they do to files.

Changing one row rewrites a whole file, and that explains almost everything

Order 1043 has the wrong amount. In a database of the kind that runs as a server, you would tell it to change that row and it would edit the row where it sits. Here you cannot, and the reason is the storage: files of this kind are written once and are not editable in the middle. To change one row inside a file, the only physical option is to produce a new file containing all of that file's rows with the one row altered, and stop counting the old file as part of the table.

That approach has a name, copy-on-write, meaning the system copies the affected unit and writes the change into the copy, rather than modifying the original. The unit being copied is the file, which is why a one-row change can rewrite a file holding a million rows.

The call itself is short. You call it on the handle, and you give it a predicate, meaning a condition written as text that says which rows you mean, in the same style you would write after the word WHERE in a query.

In the figure, each file is drawn as before with its row count, and the rows inside the file you are changing are drawn individually so you can see which one moved. Files the engine did not need to touch stay pale. The one it rewrote is drawn twice: the old version faded and marked removed, the new one solid and marked added, because that pair is exactly what the commit contains.

Press the third button and the cost becomes obvious: an update touching rows in every file rewrites every file. The rows changed might be a hundred, and the rows rewritten a hundred thousand, because rewriting is done in whole files.

This single fact explains a run of behaviours that otherwise look arbitrary. It explains why a targeted update on a well-organised table is fast and the same update on a badly organised one is slow: the difference is how many files contain a matching row. It explains why these systems reward writing predicates that line up with how the data is laid out. And it explains why deleting a small number of rows can produce a surprisingly large amount of new storage.

Which leaves the question that decides whether any of this is quick or ruinous. When you ask for the German orders, how does the engine know which files hold German orders? On the evidence so far it does not, and it does the only thing it can.

Partitioning writes the filter into the folder names

Ask the folder for the German orders. You have three files and nothing else. Which one do you open?

All three. You have no choice. Nothing about the name part-0000 tells you whether there is a German order inside it, so the only way to find out is to open it and look. Three files is fine. Thirty thousand files, which is an ordinary size for a table that has been appended to every hour for a year, is not fine at all, and no amount of clever querying helps, because the cost was fixed the moment the rows were written.

The fix is almost insultingly simple: put the answer in the folder name.

That is partitioning. You nominate one or more columns as partition columns, and the writer sorts every row into a subfolder named for its value in those columns. No row is duplicated and no index is built. The value simply moves out of the file and into the path.

The payoff arrives at read time. Asked for Germany, the engine lists the directory names, sees that two of the three cannot possibly contain a German row, and discards them without opening a single file. That step has a name, partition pruning, and it is the difference between a query that reads three files and one that reads thirty thousand.

You choose the layout once, at write time, and it is part of the table from then on.

Those last two lines are the whole lesson, and the figure below is built to make the difference impossible to miss. Change the layout, change the question, and watch which files the engine actually has to open.

Now try the layout by country with the question month = '2024-03', because that combination is where most real disappointment lives. The drawers are labelled by country and the question is about March. The labels say nothing about months, so not one drawer can be ruled out, and the engine opens all of them. Partitioning bought exactly nothing.

Which points at the obvious temptation: partition by more columns, prune more questions. The third layout does that, and it works, and the counter shows the cost. Twelve orders now sit in six folders, so the average file holds two rows. Scale that up and it is the same shape of problem either way: partition by customer and a table with fifty thousand customers gets fifty thousand folders, most holding a file of a few kilobytes. The engine now spends its time listing directories and opening tiny files, and the pruning it gained is dwarfed by the overhead it bought. The number of distinct values in a column is its cardinality, and partitioning on a high-cardinality column is the single most common way to make one of these tables slow.

So the rule is not "partition more", it is "partition by the thing you filter on, and only while the folders stay big". A commonly cited target is at least about a gigabyte of data per partition. Country works. Order date by month often works. Order id never works.

One reassurance, because the picture so far is bleaker than reality. Even with no partitioning at all, Delta records the minimum and maximum value of each column in every file, in the log. A query for order 1043 can therefore skip files whose recorded range does not contain 1043, without any folder structure at all. That is data skipping, it is on by default, and it is why the honest advice for a modern table is to partition sparingly, or not at all, and let the statistics do the work.

Update and delete each do one job. The call most people come to Delta for does three at once, and it is worth walking through slowly.

Merge: bring me today's file and I will work out what changed

Real feeds do not arrive sorted into things to add and things to change. A supplier sends today's file, and inside it are some orders you have never seen, some you have seen with corrections applied, and possibly some marked cancelled. Doing that with the calls so far means three passes and some awkward bookkeeping between them.

Merge is one call that does all three. Two words come with it and they are used constantly in the documentation. The target is the existing table being changed. The source is the incoming rows you are merging in. You supply a condition saying how a source row is recognised as being about the same thing as a target row, and then a clause for each case.

Follow one merge through, one clause at a time.

Written out, the call mirrors the walk-through exactly. Read it as a sentence: for this target, merge that source, matching on this condition, and here is what to do in each case.

Two details on that snippet, both of which trip people up. alias gives each side a short name so the condition can say which side it means, which is why you see t. and s. prefixes everywhere in merge examples. And nothing happens until execute(): the calls before it are describing the operation, not performing it.

Each clause can also carry its own condition, which is how you express the rule that stops replays from doing damage:

Without it, re-running an old file overwrites newer corrections with stale values. With it, a merge can be run twice, or out of order, and the result is the same, which matters because pipelines are re-run constantly.

Every operation so far has produced a new version and left the old files sitting in the folder. That accumulated history is not waste. It is a feature you can read.

Every past version is still readable, because its files are still named

Here is the dial for this piece, and it is the capability that most surprises people coming from ordinary databases.

A commit records which files were added and removed. Work through the log from the start and stop at commit 3, and you have the exact list of files that made up the table at that moment. Read those files and you have the table as it was. That list is a snapshot, meaning the complete contents of the table at one version, and reading one is called time travel.

It costs nothing to keep, because nothing was deleted to create the new version. The old files were unlisted, not removed.

In the figure, drag the version slider along the log. The top strip is the log with the selected commit marked. The middle is the folder, where files live at the selected version are drawn solid and files not in that version are drawn faded, so you can watch the same physical folder produce different tables. The bottom is the rows a reader gets. Everything is computed from the commits above it.

Drag to version 1 and the row corrected at version 2 shows its old amount again. Drag to version 2 and the rows deleted at version 3 are back. This is not a backup being restored: it is the ordinary read path, pointed at an earlier list.

You ask for it with one option, by version number or by wall-clock time:

Three uses justify the whole mechanism. Reproducing a number somebody is querying, by reading the table as it stood when the report ran. Comparing two versions to see what a job actually changed, by reading both and subtracting. And undoing a mistake, which has its own call rather than requiring you to copy data around:

Restore is worth one extra sentence, because its behaviour is a small lesson in how the whole system thinks. It does not rewind the table by erasing versions 5 and 6. It appends a new commit whose file list happens to match version 4's. History is never rewritten, only extended, so the record of the mistake and of the recovery both remain.

Which raises a question you may have been holding since the log first appeared. If nothing is ever deleted, does this folder simply grow forever?

Housekeeping: two calls, one of which is the only way to lose data

The answer is yes, it grows, and there are two calls that deal with it. They are usually mentioned in the same breath and they do completely different things, so it is worth keeping them apart from the start.

The first problem is not size, it is count. A pipeline appending a small batch every few minutes produces a small file every few minutes. Reading the table then means opening thousands of files, and opening a file has a fixed cost regardless of how little is in it, so a table holding a modest amount of data can become slow to read purely because of how it is spread out. That is the small files problem.

OPTIMIZE fixes it by reading many small files and writing their rows out as fewer large ones. Nothing about the data changes. In log terms it is one more commit: many files removed, a few added.

The second problem is the files nobody needs any more: the ones removed by earlier commits, still sitting in the folder. VACUUM deletes them from storage, and it is the only operation in this piece that actually destroys anything.

It will not delete files that any recent version still needs, and the meaning of recent is a setting called the retention period, which is the age a discarded file must reach before vacuum is allowed to remove it. The default is seven days.

The consequence lands directly on the version slider from a moment ago, and it is the sentence people wish they had read earlier: the retention period is the real limit on how far back you can time travel. The log may still describe version 2, but if vacuum has removed the files version 2 named, reading it fails. Set retention against how far back somebody might genuinely need to reproduce a number, not against the storage bill.

In the figure, the folder is drawn with each file in one of three states, which are worth reading off before you press anything: solid means live in the current version, faded means removed from the current version but still needed by some older version you could still read, and outlined means removed and old enough that vacuum may delete it. The horizon marker shows how far back time travel currently works.

There is one more thing the log offers before we get to what all of this cannot do, and it is free, because it is simply the log read as a report.

Every commit records what kind of operation made it, the parameters it was given, and how many rows it touched. Nobody had to switch that on. It is a by-product of the log existing, and it answers the question that plain file folders make unanswerable: who changed this table, when, with what, and how much did it affect.

Two writers at once, and the error message that means it worked

Everything so far assumed one writer. Real systems have several: a nightly load, a correction job, somebody running a backfill by hand.

The rule for how simultaneous changes are handled is called concurrency control, and Delta uses the optimistic kind, meaning writers do not reserve the table in advance. Each one reads the current version, does its work, and then tries to commit. The commit is where the check happens: if somebody else committed in the meantime, and their change overlaps with yours, your commit is rejected.

The rejection is the mechanism working. Nothing was corrupted, and no half-merged state was written. Your job raises an error saying the table was modified concurrently, and the correct response is usually to run it again, since it will then read the new version and redo its work on top.

In the figure, two writers are drawn as two lanes, each showing which version it read and what it wants to commit. Press the buttons to advance either one, and see which commits succeed.

Two appends to different parts of a table generally both succeed, because they add files and remove nothing, so there is nothing to disagree about. Two merges aimed at the same rows will conflict, and that is correct, since one of them was computed against a table that no longer exists.

The practical guidance follows directly: arrange for writers not to overlap where you can, and where you cannot, let the loser retry rather than catching the error and continuing. A caught conflict that is then ignored is how a job reports success while having done half its work.

That is the mechanism in full. What remains is being clear about the shape of problem it is for, which is narrower than the enthusiasm around it sometimes suggests.

What this is not: a database for your application

Delta tables suit an analytical workload, meaning work that reads a great many rows at a time to compute summaries, and writes in batches. They suit it very well: large scans, columns rather than whole rows, and periodic bulk changes.

They do not suit a transactional workload, meaning the pattern behind an ordinary application, where thousands of small operations a second each touch one row and expect an answer in milliseconds. Copy-on-write is the reason: a one-row change rewrites a file. Serving a shopping basket from a Delta table would be a design error, and no amount of tuning fixes it, because the mismatch is architectural.

Three more limits deserve stating plainly rather than being discovered.

The storage underneath has its own rules. These tables usually live on object storage, meaning cloud services that store whole files addressed by name, rather than a filesystem with directories in the traditional sense. Listing files there is slower than you expect, renames are not always cheap, and the guarantees vary by provider. Most of the awkward operational corners in real deployments trace back to that layer rather than to Delta.

Small frequent writes are the expensive pattern. Every commit is a file plus a log entry. Committing every few seconds produces a table that needs constant compaction to stay readable. Batching writes is not a micro-optimisation here, it is how the design expects to be used.

Deleting for privacy requests is more work than a delete. The rows leave the current version, and they remain in older versions until vacuum passes the retention horizon. Any credible erasure process has to account for that, and for copies in other tables downstream.

The figure lays out four workloads against what each one needs, so the fit and the mismatch are visible together rather than asserted.

Which is a reasonable place to stop, with the whole mechanism in view: a folder, a log, a handful of calls that write one and append to the other, and a set of consequences that all trace back to those two facts.

Every call in this API does one of two things. It writes data files while nobody is looking, or it appends a line to a log saying which files count now. Reading, updating, merging, time travel and recovery are all consequences of those two moves.

Now try it yourself

A table you can operate. Issue calls in any order and watch both halves respond: the folder on the left, the log on the right, the rows a reader would get underneath. Nothing here is scripted, so you can put the table into states this piece never described.