> ## Documentation Index
> Fetch the complete documentation index at: https://docs.lancedb.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Statistical Sampling

> Draw a random subset of a table with SQL TABLESAMPLE, by rows or by fragments.

**Applies to:** Embedded ❌, Enterprise ✅

Statistical sampling draws a random subset of a table's rows using SQL's
`TABLESAMPLE` clause, attached directly to a table in `FROM`. Use it to
explore unfamiliar data, run a smaller experiment, or estimate aggregate
statistics without reading every row. For a random subset through the
Python SDK instead -- in Embedded or Enterprise -- see
[Permutation](https://docs.lancedb.com/training/permutation.md). To select specific, known rows
rather than a random fraction, see [Selective Sampling](https://docs.lancedb.com/curation/selective-sampling.md).

## Example data

Connect with an [Enterprise SQL client](https://docs.lancedb.com/basics/sql.md). The examples use a small
document table and a separate label table. Expand the setup below to create them.

> [accordion] Create the example tables
>
> `curation_documents` has six records describing four documents.
> `curation_labels` has one label for each of three documents. `row_id` identifies
> a record; `doc_id` identifies the document it describes.
>
> Run each statement separately in a scratch database or namespace. Create these
> tables once for the whole section, using fresh names if they already exist.
>
> ```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
> CREATE TABLE curation_documents (
>     row_id BIGINT, doc_id BIGINT, version BIGINT,
>     source VARCHAR, caption VARCHAR, score DOUBLE
> );
> INSERT INTO curation_documents VALUES
>     (1, 10, 1, 'web', NULL, 0.80),
>     (2, 10, 2, 'web', 'A cat on a chair', 0.95),
>     (3, 20, 1, 'books', 'A dog in a field', 0.90),
>     (4, 30, 1, 'web', NULL, 0.70),
>     (5, 30, 1, 'web', 'A cat by a window', 0.85),
>     (6, 40, 1, 'books', 'A bird in a tree', 0.60);
>
> CREATE TABLE curation_labels (doc_id BIGINT, label VARCHAR);
> INSERT INTO curation_labels VALUES
>     (10, 'cat'), (20, 'dog'), (30, 'cat');
> ```
>
> `score` is a sample quality score, not a search result score. Document 10 has
> two versions. Document 30 has two records at the same version. Document 40
> has no label. These cases let you check the effect of each operation.

## Basic sampling

A percentage selects a fraction of the source; `ROWS` requests a row count:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT row_id, doc_id FROM curation_documents TABLESAMPLE (50);
SELECT row_id, doc_id FROM curation_documents TABLESAMPLE (3 ROWS);
```

A percentage sample does not guarantee an exact number of rows. Use a count
when the desired sample size is the main constraint, and inspect the result
rather than assuming it contains every category you need.

`TABLESAMPLE` requires a table that is not WAL-backed. See the
[sampling reference](https://docs.lancedb.com/sql/dql/sampling.md#refused) for this restriction; do not
change a production table's write configuration just to try an example.

## Sampling methods

Choose a method according to how records should be selected:

| Method | Selection | When to use it |
| - | - | - |
| `BERNOULLI` (default) | Select individual rows | When records should be sampled independently of their storage fragments |
| `SYSTEM` | Select whole Lance fragments | When reducing reads matters and grouping by fragment is acceptable |

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT row_id, doc_id
FROM curation_documents TABLESAMPLE BERNOULLI (50);

SELECT row_id, doc_id
FROM curation_documents TABLESAMPLE SYSTEM (50);
```

If write order groups records by label, date or source, a fragment sample can
reflect that grouping. On a tiny table stored in one fragment, it may include
all records or none. Row sampling also needs an adequate sample size to
represent rare groups; check that the resulting sample covers the groups you need.

## Repeatable samples

A seed requests a repeatable draw:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT row_id, doc_id
FROM curation_documents TABLESAMPLE (50) REPEATABLE (42);
```

Keep the source data and sampling configuration fixed when comparing draws.
For a lasting evaluation set, save the selected records rather than relying
on a seed to preserve membership as the source changes:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
CREATE TABLE curation_eval AS
SELECT * FROM curation_documents TABLESAMPLE (50) REPEATABLE (42);
```

Create this output once with a fresh name and keep it unchanged. Record the
source version and selection policy. Sampling records independently can place
revisions of the same document into different splits; decide the
[grouping policy](https://docs.lancedb.com/curation/windowing.md#splits) before using the result for
training or evaluation.

## Sampling with joins

Sampling both input tables independently changes which pairs can match.
For a one-to-one match, independent 10% row samples retain a given pair with
about 1% probability, not 10%.

To sample documents while retaining their available labels, sample only the
document table:

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT curation_documents.row_id, curation_documents.doc_id, l.label
FROM curation_documents TABLESAMPLE (50)
JOIN curation_labels AS l ON l.doc_id = curation_documents.doc_id;
```

This samples source records, not distinct documents. If you need to sample the
result of a join or deduplication, first save that result as a table suitable
for sampling. `TABLESAMPLE` cannot be applied directly to a subquery result.

## Apply a Function to a sample

Sample first, then call a [Function](https://docs.lancedb.com/curation/create-function.md) only on
the rows you drew -- useful for trying an expensive Function on a subset
before running it over the whole table. Register `caption_quality` first:

```python Python icon="python" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
from lancedb import udf

@udf
def caption_quality(caption: str) -> float:
    ...  # score the caption, e.g. with a lightweight heuristic or model

db.create_function_async(caption_quality).wait()
```

```sql SQL icon="database" theme={"theme":{"light":"vitesse-light","dark":"catppuccin-mocha"}}
SELECT row_id, caption, caption_quality(caption) AS quality
FROM curation_documents TABLESAMPLE (50)
WHERE caption IS NOT NULL;
```

The Function runs only on the sampled rows, not the whole table -- a quick
way to check a Function's output, cost or latency before committing to a
full [column refresh](https://docs.lancedb.com/feature-engineering/refresh-column.md).

## Related guides

Count the sampled rows and [inspect their distribution](https://docs.lancedb.com/curation/aggregate.md)
before using them. Preserve the selected data when the exact membership matters.
See [SQL sampling](https://docs.lancedb.com/sql/dql/sampling.md) for the complete syntax and restrictions,
and [Permutation](https://docs.lancedb.com/training/permutation.md) for a random subset through the
Python SDK.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.