> **Can't find what you're looking for?** Use `search_docs` on the docs MCP server at `https://docs.ampdb.io/api/mcp` to find what you need.

# Tables

A table is a named set of rows that all have the same columns, like a table in any SQL database. Every
[dataset](/get-started/concepts/datasets) has one or more: an Ethereum raw dataset has `blocks`, `transactions`, and
`logs`. You query tables with SQL, as `"namespace/name".table`:

```sql
SELECT block_num, hash FROM "acme/eth_mainnet".blocks LIMIT 10
```

## Columns

A table's schema lists its columns. Each column has a name, a type, and whether it can be empty (`NULL`). The types are
[Apache Arrow](https://arrow.apache.org)'s, like `UInt64` for whole numbers, `Utf8` for text, `Timestamp` for dates and
times, and `FixedSizeBinary(32)` for 32 bytes, like a hash.

The dataset's manifest declares its tables' schemas. A raw dataset's tables are fixed by its kind: see the
[table schemas](/datasets/tables/evm-rpc) for each one. A derived dataset's tables are whatever its SQL produces.

## Block numbers

The tables of raw and derived datasets are organized by block: each row belongs to a block of the chain it came from.
Amp adds two columns to them:

* `_block_num`: the number of the block the row belongs to.
* `_ts`: the time that block was produced.

Amp uses them to know which blocks a table covers, to find the rows a query needs, and to handle
[reorgs](/get-started/concepts/reorgs). Names that start with `_` are reserved for Amp: your own column names and SQL
aliases can't.

In queries that combine several tables, use the `block_num()` and `ts()` functions rather than `_block_num` and `_ts`:
they return each result row's block number and time, wherever it came from.

A derived table doesn't always get both. If it reads several networks, it has `_block_num` only if its query selects
it. If it reads one network, it has `_ts` only if the tables it reads have it, or a `timestamp` column in its place.
[Reserved columns](/datasets/manifests#reserved-columns) has the full rule.

Static datasets' tables have no blocks, and no `_block_num` or `_ts`.

## How tables are stored

Amp stores each table as [Parquet](/get-started/glossary#parquet) files: a compressed, column-by-column file format that
most data tools read. Each file holds the rows of a range of consecutive blocks. Its name starts with its first block's
number, so listing a table's files lists them in block order. Files live in storage, in a directory per dataset, table,
and [table revision](/get-started/concepts/tables#table-revisions). After the [Quickstart](/get-started/quickstart), they look like this:

```text
.amp/data/                                        the quickstart's storage
└── acme/eth_mainnet/                             a dataset, namespace/name
    ├── blocks/                                   a table
    │   └── 01a18db7-52cb-7771-bbc0-326324e9d93b/ a table revision
    │       └── 024000000-f542407a8c944c44.parquet
    ├── logs/
    └── transactions/
```

Amp never changes a file after writing it. To change data, it writes new files, and records in the metadata database
which files make up the table. That's what lets queries read a table while a worker adds to it.

Two background processes keep the files in shape, in every deployed dataset:

* **[Compaction](/running-amp/storage/compaction)**: while it follows a chain, a worker writes many small files, which
  slow queries down. Compaction merges them into bigger ones.
* **[Garbage collection](/running-amp/storage/garbage-collection)**: files that compaction replaced stop being part of
  the table right away. Garbage collection deletes them later, once queries that were already reading them have had time
  to finish.

## Table revisions

A table revision is one complete set of a table's files, in its own directory, named with a unique, time-ordered ID.
Each table has one **active** revision, which every query reads. Other revisions can exist beside it, kept but not
queried: an older copy of the data, or one being restored from files already in storage.

Switching a table's active revision is instant, and queries never see a mix of two revisions. [Table
Revisions](/managing-amp/table-revisions) explains how to list, switch, and delete them.

:::note
Table revisions aren't dataset revisions. A dataset revision, like `1.0.0` or `latest`, selects a version of a dataset's
definition. A table revision is a set of files holding one table's data.
:::