A versioned table keeps one current row per key and every accepted version beside it. Writes that arrive late or out of order settle to the same state as writes that arrive in order, and any read can ask for the table exactly as it stood at an earlier commit.
Three modes
A table is a typed schema plus a mode. The mode decides which writes the table accepts and which state it keeps; every other clause of the definition shapes where rows live and how they are stored.
An append table keeps every row it accepts and carries no primary key. Use it for events, readings and other facts that are never corrected in place; corrections are derived downstream, in a keyed table a transformation maintains.
One current row per primary key. A later write to the same key changes that row and the previous value is not retained. It is the default when no mode is written.
One current row per key, with every accepted version retained beside it. The same writes as an upsert table, plus reads at an earlier commit and an order that settles late arrivals.
Versioned tables
For each key the engine keeps the version that is greatest under one order: where the value takes effect, then which recording wins at the same position, then commit order to break a tie. Writes delivered in order, shuffled or late converge to the same current row and the same retained history.
The two clauses name non-null integer or timestamp columns that every write supplies. A table with neither keeps the last committed write.
CREATE TABLE balances ( chain_id UINT16 NOT NULL, address STRING NOT NULL, token STRING NOT NULL, amount UINT256 NULL, updated_at DATETIME64(3, 'UTC') NULL, balance_index UINT128 NULL, balance_version UINT64 NULL, transfer_count UINT64 NULL, last_transfer_at DATETIME64(3, 'UTC') NULL, activity_index UINT128 NULL, PRIMARY KEY (chain_id, address, token), ATTRIBUTE GROUP holding (amount, updated_at, balance_index, balance_version) VALID BY (balance_index) REVISION BY (balance_version), ATTRIBUTE GROUP activity (transfer_count, last_transfer_at, activity_index) VALID BY (activity_index))MODE VERSIONEDSHARD BY (address) SHARDS AUTOORDER BY (chain_id, address, token);Late writes
Two writes to one key, the second arriving after the first but carrying an earlier position. The group is ordered by where the balance takes effect, so the late write is retained as an earlier version and the current amount stays what the first write set.
UPSERT INTO balances ( chain_id, address, token, amount, updated_at, balance_index, balance_version)VALUES (1, '0x9c2e4f1a', 'USDC', 2750000000, '2026-10-01 12:05:00.000', 1002, 1)ATTRIBUTE GROUP holding;UPSERT INTO balances ( chain_id, address, token, amount, updated_at, balance_index, balance_version)VALUES (1, '0x9c2e4f1a', 'USDC', 2500000000, '2026-10-01 12:00:00.000', 1001, 1)ATTRIBUTE GROUP holding;| Arrival | balance_index | balance_version | Current row afterwards |
|---|---|---|---|
| 1 | 100 | 1 | write 1 |
| 2 | 200 | 1 | write 2 (later index) |
| 3 | 150 | 9 | still write 2: 150 is earlier than 200, so the high revision does not matter |
| 4 | 200 | 2 | write 4 (same index as write 2, higher revision) |
holding · VALID BY (balance_index) REVISION BY (balance_version) · then commit order
Point in time
A read at a commit returns each key’s winner at that commit, under the schema you have now; columns added later read at the value they were introduced with. The operand is a commit sequence, a positive integer.
Write it directly after the relation name, or after the filter, grouping and ordering clauses, before the row limit. Rows of every schema generation read under their current names, including renamed columns.
SELECT address, token, amountFROM balances AS OF COMMIT 42WHERE chain_id = 1;Attribute groups
An attribute group declares its own ordering clauses, which order only that group’s writes. A stale write to one group never holds back a newer write to another, and a group without clauses is ordered by commit.
The holding group is written by the service that tracks balances and ordered by where the balance takes effect; the activity group is maintained by a transformation over transfers and ordered by its own index. A read returns the whole row, both groups together.
holding · VALID BY (balance_index) REVISION BY (balance_version)
UPSERT INTO balances ( chain_id, address, token, amount, updated_at, balance_index, balance_version)VALUES (1, '0x9c2e4f1a', 'USDC', 100, '2026-10-01 12:00:00.000', 100, 1)ATTRIBUTE GROUP holding;A new group declares its columns inline, and they are nullable; a new producer or transformation can own it at once. Transformations that write the other groups keep running across the change and across restarts.
ALTER TABLE … ADD ATTRIBUTE GROUP
ALTER TABLE balances ADD ATTRIBUTE GROUP labels ( label_text STRING NULL, labelled_at DATETIME64(3, 'UTC') NULL);Retention
What a table retains, and for how long, is declared on the table, and the policy in force is a query away.
Every table is bound to policies, one per role: placement, storage, retention, compaction and more. Retention policies expire rows by age, storage policies tier data between local devices and object storage, and a named policy attaches with a trailing clause on a table or an index.
placement · storage · retention · compaction · POLICY <name>
An index declares which rows it covers: the current rows, the retained history, or both. It decides how much data a read touches, and every candidate row still passes the full predicate.
SCOPE CURRENT · SCOPE HISTORY · SCOPE BOTH
One row per table and role lists the policy bound for it, so the retention in force on any table is one read away, next to the storage plan of the table and the codec of every column.
system.table_storage_plans · system.table_column_codecs
What it gives you
Versions you can read back are what audit, investigation and training all need: not a reconstruction, the rows as they were.
Reread a balance or a risk score as it stood at the commit a decision was taken, under the schema you have now, and show which version won and why.
Keep security events in full, keep per-entity state versioned, and read either exactly as it stood at the commit an alert fired, with later writes left out.
Per-entity features maintained live on one row and read point-in-time for training, so a training set is built from the feature values that stood at a past commit.
What a technical buyer asks about versions, late writes and reads at a commit, answered from the docs.
An upsert table keeps the latest row per key only; a versioned table keeps the current row and every accepted version beside it. Both accept the same writes and both can use the ordering clauses to decide which write is the latest; only the versioned table can be read at an earlier commit.
Create a versioned table, write to it out of order and read it back as it stood. The getting-started guide includes a read at an earlier commit.