A Bloom filter is a small data structure that answers "is this value definitely not in this file?" Databricks can store one per data file for chosen columns. A query like WHERE device_id = 'X' then skips every file whose filter says "not here", without reading it.
When it helps (and when it doesn't)
Delta already skips files using min/max statistics. That works well when values are clustered, as with dates or sorted IDs. It fails for high-cardinality, randomly distributed values such as GUIDs, hashes and email addresses: every file's min/max range covers almost everything, so nothing is skipped.
| Situation | Bloom filter? |
|---|---|
Equality lookups (=, IN) on random IDs, GUIDs, hashes | ✅ The use case |
Range filters (>, BETWEEN), LIKE | ❌ Not used |
| Low-cardinality columns (status, country) | ❌ No benefit |
| Column already used for liquid clustering / Z-order | ❌ Min/max skipping already works |
| Small tables (a few files) | ❌ Overhead only |
| Lookups that return a large share of rows | ❌ Every file is read anyway |
Note
Databricks documentation now positions liquid clustering and predictive I/O (on Photon / SQL warehouses) as the first choice for selective lookups. Treat Bloom filters as a targeted tool for one or two lookup columns that clustering can't serve, for example a second high-cardinality key.
Create the index
CREATE BLOOMFILTER INDEX
ON TABLE main.iot.events
FOR COLUMNS (device_id OPTIONS (fpp = 0.05, numItems = 50000000),
session_id OPTIONS (fpp = 0.1, numItems = 200000000));
| Option | Meaning | Guidance |
|---|---|---|
fpp | False-positive probability: how often the filter says "maybe" when the value isn't there | 0.01–0.1. Lower means bigger filters and fewer wasted file reads. |
numItems | Expected distinct values per file (approximation used for sizing) | Too low gives a high false-positive rate; too high wastes space |
Things to know:
- The index is built for data written after it was created. Existing files don't get filters until they are rewritten. Rewrite them deliberately, for example with a full
OPTIMIZErewrite or by re-writing the table, and test on a copy first. - Filters are stored alongside the data files and add some size and write cost.
- Bloom filter skipping is controlled by
spark.databricks.io.skipping.bloomFilter.enabled. It's on by default; check it if filters don't seem to work.
Check that it's being used
-- is the index defined? Look for the bloom filter metadata on the column
DESCRIBE TABLE EXTENDED main.iot.events;
EXPLAIN FORMATTED SELECT * FROM main.iot.events WHERE device_id = 'a91f-77c2-...';
The best proof is the query profile (SQL warehouse) or the Spark UI scan metrics: compare files read / pruned with and without the filter.
SET spark.databricks.io.skipping.bloomFilter.enabled = false; -- baseline
SELECT count(*) FROM main.iot.events WHERE device_id = 'a91f-77c2-...';
SET spark.databricks.io.skipping.bloomFilter.enabled = true;
SELECT count(*) FROM main.iot.events WHERE device_id = 'a91f-77c2-...';
Run each twice and compare the second run, so caching doesn't distort the result.
Remove the index
DROP BLOOMFILTER INDEX ON TABLE main.iot.events FOR COLUMNS (session_id);
Dropping removes the index definition, so new writes no longer build filters for that column.
Alternatives, in order of preference
- Liquid clustering on the lookup column(s):
ALTER TABLE main.iot.events CLUSTER BY (device_id, event_date), thenOPTIMIZE. Min/max skipping now works for that column. - Predictive I/O / Photon on SQL warehouses and serverless, which speeds up selective reads automatically.
- Statistics on the right columns. Delta collects stats on the first 32 columns by default. Make sure the lookup column is included (
delta.dataSkippingStatsColumns). - A lookup table keyed by ID, pointing to the partition or date, for the hottest access path.
- A serving store (Lakebase/Postgres, Cosmos DB, a cache) when you need millisecond single-row lookups at high concurrency. A lakehouse isn't a key-value store.
Example decision
A 4 TB
eventstable is clustered byevent_date. Support engineers look up a singlesession_id(a GUID) across the last 90 days, and each lookup scans about 40,000 files.
- Adding
session_idto liquid clustering would weaken date pruning for the main workload. - A Bloom filter on
session_id(fpp 0.05) let lookups read a few dozen files instead. It added about 1–2% to storage, and only new and recently rewritten files got filters. - That's a good fit. Results depend on file sizes and value distribution, so measure on your own data.
Tip
Before adding a Bloom filter, ask: "Could this column be a clustering key instead?" If yes, cluster. Keep Bloom filters for the second high-cardinality lookup key that clustering can't serve.