> **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.

# Bloom Filters

Status: Experimental.

A query that looks up one value, like a transaction by its hash, would otherwise read every file of the table. A bloom
filter is a small summary of the values in a column, stored with each part of the table's files. Before reading a part,
Amp checks its filter: when the filter says the value isn't there, Amp skips the part. Lookups then read far less data.

You choose which columns get bloom filters, table by table, in the dataset's manifest. Raw and derived datasets support
them; tables have none by default.

## Prerequisites

* A running Amp you can manage, like one in [solo mode](/running-amp/solo-mode), with [`ampctl`](/clis/ampctl) and
  [`ampsql`](/clis/ampsql).
* A raw or derived dataset, and its manifest. The example adds a bloom filter to `acme/usdc_transfers`, the
  derived dataset that [Derived Datasets](/datasets/derived-datasets#create-a-derived-dataset) creates, and edits its
  manifest, `usdc_transfers.json`.

## Add bloom filters to a table

::::steps
### Add the columns to the manifest

List the columns in the table's `bloom_filter_columns`, each with an estimate of how many distinct values it holds,
`ndv`. Here, the `usdc_transfers` table gets one on `tx_hash`, to look up a transaction's transfers. The rest is the
manifest Derived Datasets wrote, with its schema folded:

```json [usdc_transfers.json]
{
  "kind": "manifest",
  "dependencies": {
    "eth": "acme/eth_mainnet@1.0.0"
  },
  "tables": {
    "usdc_transfers": {
      "input": {
        "sql": "SELECT block_num, timestamp, tx_hash, log_index, evm_decode_log(topic1, topic2, topic3, data, 'Transfer(address indexed from, address indexed to, uint256 value)') AS transfer FROM eth.logs WHERE address = evm_encode_hex('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND topic0 = evm_topic('Transfer(address indexed from, address indexed to, uint256 value)')"
      },
      "schema": { // [!code collapse:42 collapsed]
        "arrow": {
          "fields": [
            { "name": "_block_num", "type": "UInt64", "nullable": false },
            { "name": "_ts", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false },
            { "name": "block_num", "type": "UInt64", "nullable": false },
            { "name": "timestamp", "type": { "Timestamp": ["Nanosecond", "+00:00"] }, "nullable": false },
            { "name": "tx_hash", "type": { "FixedSizeBinary": 32 }, "nullable": false },
            { "name": "log_index", "type": "UInt32", "nullable": false },
            {
              "name": "transfer",
              "type": {
                "Struct": [
                  {
                    "name": "from",
                    "data_type": { "FixedSizeBinary": 20 },
                    "nullable": true,
                    "dict_id": 0,
                    "dict_is_ordered": false,
                    "metadata": {}
                  },
                  {
                    "name": "to",
                    "data_type": { "FixedSizeBinary": 20 },
                    "nullable": true,
                    "dict_id": 0,
                    "dict_is_ordered": false,
                    "metadata": {}
                  },
                  {
                    "name": "value",
                    "data_type": "Utf8",
                    "nullable": true,
                    "dict_id": 0,
                    "dict_is_ordered": false,
                    "metadata": {}
                  }
                ]
              },
              "nullable": false
            }
          ]
        }
      },
      "bloom_filter_columns": [{ "column": "tx_hash", "ndv": 1000000 }] // [!code ++]
    }
  }
}
```

| Field    | Type    | Default  | Description                                                                              |
| -------- | ------- | -------- | ---------------------------------------------------------------------------------------- |
| `column` | string  | required | The column to filter, which the table's schema must have, like `"tx_hash"`.              |
| `ndv`    | integer | `10000`  | About how many distinct values the column holds, which sizes its filter, like `1000000`. |

In a raw dataset, add `bloom_filter_columns` to a table of the manifest `ampctl manifest generate` writes the same way,
like the `transactions` table of an `evm-rpc` dataset, whose transactions you look up by `tx_hash` too.

### Register it as a new version

Amp checks that each column is in the table's schema (see [Errors](/datasets/bloom-filters#errors)):

```bash
ampctl dataset register acme/usdc_transfers ./usdc_transfers.json --tag 1.1.0
```

### Deploy it

```bash
ampctl dataset deploy acme/usdc_transfers@1.1.0
```

The new version's job writes its files with a bloom filter on `tx_hash`.

### Look up a value

A query that compares the column to one value now skips the parts of the table whose filter says the value isn't
there. Without a revision, the query reads `latest`, which is now `1.1.0`:

:::terminal
```bash
ampsql "SELECT block_num, log_index, evm_decode_hex(transfer.to) AS recipient, transfer.value AS amount FROM \"acme/usdc_transfers\".usdc_transfers WHERE tx_hash = evm_encode_hex('0x3f6b08aedda3b0b336ea778ec2c3e83cb394ea2534b1c73523df9cb56d3e5825') ORDER BY log_index"
```

```
+-----------+-----------+--------------------------------------------+------------+
| block_num | log_index | recipient                                  | amount     |
+-----------+-----------+--------------------------------------------+------------+
| 24000000  | 1086      | 0x3611b82c7b13e72b26eb0e9be0613bee7a45ac7c | 3072095928 |
| 24000000  | 1089      | 0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae | 3072095928 |
| 24000000  | 1091      | 0x900e74b8cfb466bd8f0b961dff9b60bdb6ecea5f | 3072095928 |
+-----------+-----------+--------------------------------------------+------------+
```
:::
::::

## Choose columns

Bloom filters help with queries that compare a column to one value, with `=`. They don't help with ranges (`>`, `<`,
`BETWEEN`), and each one takes space in every file. So add them to:

* Columns you often look up one value in: hashes (`hash`, `tx_hash`), addresses (`address`, `from`, `to`), and IDs.
* Columns with many distinct values. A column with few, like a status or a boolean, is in nearly every part of the
  table, so its filter rarely skips anything.

Set `ndv` close to the column's real number of distinct values. Too low, and the filter wrongly reports more values
as present, so Amp skips fewer parts. Too high, and it takes more space than it needs. A wrong `ndv` never gives wrong
results: Amp only uses filters to skip parts that can't match.

## Limitations

* Changing a table's bloom filters only affects the files Amp writes afterwards. Files it already wrote keep the
  filters they had.
* [Phaser](/providers/phaser) datasets don't support them: their tables come from the bridge.

## Errors

### `bloom filter column 'sender' does not exist in table 'usdc_transfers'`

Registration fails with `MANIFEST_VALIDATION_ERROR` when `column` names a column the table's schema doesn't have.
Check the column's name in the schema.