Analytics over the tables you already write to

Give each table the layout its reads deserve, prune with partitions and indexes, and let the engine keep your heavy aggregates answered before you ask. No copy into a warehouse, no second schema to keep in step.

Get startedStorage model

Storage profile

Row, columnar or both, declared per table

A table’s physical layout is one clause of its definition. Choose a row-oriented projection for point access, a columnar one for scans, or both at once, and the choice applies to the table’s current rows and to the history it keeps.

ROW: A row-oriented projection: keyed rows for point access.
COLUMNAR: A columnar projection: column blocks that favour scans. Append tables always store their parts as column blocks.
HYBRID: Both projections at once, for a table that serves lookups and scans.
ENCODING · CODEC: Per column: a dictionary for repeated values, and a chain of codec stages checked against the column type.

CREATE TABLEStorage model

an append table laid out for scans

CREATE TABLE transfers (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  sender STRING NOT NULL,
  recipient STRING NOT NULL,
  amount UINT256 NOT NULL,
  transferred_at DATETIME64(3, 'UTC') NOT NULL,
  _index UINT128 NOT NULL,
  INDEX transfers_ts (transferred_at) USING MINMAX SCOPE HISTORY GRANULE 8192
)
MODE APPEND
SHARD BY (token) SHARDS AUTO
PARTITION BY MONTH(transferred_at)
ORDER BY (chain_id, token, _index)
STORAGE PROFILE COLUMNAR;

Partitions and indexes

Touch less data, never a different answer

Shards, partitions and indexes decide how much data a read touches. Every candidate row still passes the full predicate, so pruning changes the work, not the result.

Indexes

SHARD BY · SHARDS AUTO

Split by key across tablets

Each table is split into tablets by its shard key, with a count you set or one the engine picks. A row-value list that covers the shard key reads only the shards that own the listed tuples.

PARTITION BY HOUR · DAY · MONTH · HASH

Bucket rows so reads prune

Partition a shard’s rows by an hour, a day, a month or a hash of a column. A read that is bounded by the bucket skips the rest.

MINMAX

Range pruning per region

Per region of data, the smallest and largest value. A range predicate skips every region that cannot match.

BLOOM

Membership, ruled out early

Rules out the regions that cannot contain the value you look for, so an equality lookup touches only the regions that might.

POSTINGS

Exact lookup by value

Maps each key value to the positions that hold it, for the lookups that ask which rows carry exactly this value.

SCOPE CURRENT · HISTORY · BOTH

Index current rows, history or both

An index declares which state it covers. Declare one on a table that already holds rows and the table stays readable and writable while it fills.

An index changes how much data a read touches, not what it returns. Indexes are local to each tablet and maintained with its writes.

Keys, shards and partitions

One-shot analytics

Group, filter, order and page, in one statement

Grouped aggregates with a group filter, ordered and paged results, and subqueries that nest wherever a relation is expected. Plain SQL over the current state of your tables, with the aggregates you expect and arithmetic over them.

SELECT

Group, then filter the groups

Every non-aggregate output is grouped, and the group filter keeps only the groups whose aggregate passes.

tokens with more than a few transfers

SELECT token, COUNT(*)
FROM transfers
WHERE chain_id = 1
GROUP BY token
HAVING COUNT(*) > 4;

GROUP BY · HAVING

Order it, then page through it

Order by any output, with nulls first or last, and page the result with a limit and an offset.

the next page of the largest holders

SELECT address, amount
FROM balances
WHERE chain_id = 1 AND token = 'USDC'
ORDER BY amount DESC
LIMIT 10 OFFSET 10;

ORDER BY · LIMIT · OFFSET

Nest a derived table

Derived tables and subqueries nest anywhere a relation or a value is expected, and a named query can lead the statement.

totals per token, filtered once more

SELECT token, total
FROM (SELECT token, SUM(amount) AS total FROM balances GROUP BY token) totals
WHERE total > 0;

subquery · WITH

Maintained aggregates

The heavy query, already answered

Describe the aggregate once. The engine applies every committed change to it and keeps the result exact, across restarts, until you drop it. Your dashboards read a table, not a scan.

01
Name the result
A materialized view is maintained continuously into an engine-owned table and read by its name. Inserts, updates and deletes on the source reach it, and it is exact after a restart.
CREATE MATERIALIZED VIEW
volume per token, kept current
```
CREATE MATERIALIZED VIEW token_volume AS
SELECT chain_id, token, SUM(amount) AS volume, COUNT(*) AS transfers
FROM transfers
GROUP BY chain_id, token;
```
02
Read it like a table
The result is a relation like any other: filter it, order it, stream it. The grouped scan you used to run on a schedule becomes an ordinary read.
read by name
the maintained result
```
SELECT *
FROM token_volume;
```

Fold into a table you own

A transformation writes the fold into a table of yours, or one attribute group of it, so several definitions maintain one row together and the next transformation can read the result.

CREATE TRANSFORMATION · INTO … ATTRIBUTE GROUP

a fold into one attribute group

CREATE TRANSFORMATION balance_activity INTO balances ATTRIBUTE GROUP activity AS
SELECT
  chain_id,
  recipient AS address,
  token,
  COUNT(*) AS transfer_count,
  MAX(transferred_at) AS last_transfer_at,
  MAX(_index) AS activity_index
FROM transfers
GROUP BY chain_id, recipient, token;

COUNT · SUM · MIN · MAX · AVG · COUNT(DISTINCT …), and arithmetic over aggregate calls, such as a weighted mean.
Transformations

Joins

Enrich at read time or as rows arrive

Join in a one-shot query, which reads a consistent snapshot of each side, or in a transformation, which keeps the joined target current as any side changes. The price in force when a transfer happened, or the current price when the event is processed: each is one join.

INNER · LEFT · RIGHT · FULL · CROSS: Equalities between the sides. In a transformation every side is retained, and a change on any side re-derives the affected joined rows.
ASOF LEFT JOIN: Each left row joins the latest right row whose time is not after its own. In a transformation, a late right-side revision is gated, parked or retracted, as you declare.
LOOKUP JOIN: Each event reads the keyed relation’s current value when it is processed and emits exactly one row. A change to the lookup applies to the next event for that key.

Joins

each transfer with the price in force at its time

SELECT t.token AS token, t.amount AS amount, p.price_usd AS price_usd
FROM transfers AS t
ASOF LEFT JOIN token_prices AS p
  ON t.chain_id = p.chain_id
    AND t.token = p.token
    AND p.priced_at <= t.transferred_at;

History

Analyse the state as it was

A versioned table keeps every accepted version beside the current row. Read it at an earlier commit and your query runs over the state behind a decision, an audit or a replay, under the schema you have now.

AS OF COMMIT <n>: Written after the relation, or after the filters and the ordering, before the limit. The operand is a commit sequence.
each key’s winner: The read returns the row that was current for each key at that commit. Columns added later read at the value they were introduced with.
DELETE keeps history: A deleted key leaves current reads and stays readable at an earlier commit.

Versioned tables

balances at an earlier commit

SELECT address, token, amount
FROM balances AS OF COMMIT 42
WHERE chain_id = 1;

Frequently asked questions

The questions an analytics team asks before it stops copying tables into a second system. Anything else is in the docs.

Storage model

No. Choose a columnar or hybrid layout per table, declare partitions and indexes, and run grouped queries over the same tables your services write to. An aggregate you read often is maintained for you by a materialized view or a transformation.

Ask the heavy question once

Lay out a table for scans, declare a maintained aggregate over it and read the answer like any other table.

Get startedTransformations