---
title: "SQL sampling"
sidebarTitle: "SQL Sampling"
description: "Choose a sample for exploration or evaluation and preserve the selected records when reproducibility matters."
icon: dice
keywords: ["sampling", "tablesample", "subset", "eval set", "reproducible", "shuffle"]
availability:
oss: unavailable
enterprise: available
summary: >-
Enterprise supports SQL `TABLESAMPLE`. For sampling in OSS and Enterprise through the Python SDK, see [Sample without SQL](#training).
---
import { PySampleDataset } from '/snippets/curation_sampling.mdx';
Sampling selects records from a larger table. Use it to explore unfamiliar
data, run a smaller experiment, or prepare an evaluation dataset. Enterprise
SQL provides `TABLESAMPLE` for selecting a percentage or number of rows.
This page covers Enterprise SQL sampling and a complete
[Python SDK example](#training) that runs locally.
## Example data {#example-data}
Connect with an [Enterprise SQL client](/basics/sql). The examples use a small
document table and a separate label table. Expand the setup below to create them.
`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
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 {#quick}
A percentage selects a fraction of the source; `ROWS` requests a row count:
```sql
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](/sql/dql/sampling#refused) for this restriction; do not
change a production table's write configuration just to try an example.
## Sampling methods {#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
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 {#fixed}
A seed requests a repeatable draw:
```sql
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
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](/curation/windowing#splits) before using the result for
training or evaluation.
## Sampling with joins {#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
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.
## Sample without SQL {#training}
The Python SDK provides a permutation builder for selecting and ordering rows
in OSS and Enterprise. A split selects a subset; a shuffle changes the order
in which records are read. `Permutation.identity(table)` retains stored order.
Install the Python SDK with `pip install lancedb`, save the program as
`sample_dataset.py`, and run `python sample_dataset.py`. It creates a temporary
local database with ten rows and reads a random split containing three rows.
The temporary database is removed when the program finishes.
{PySampleDataset}
The program prints three distinct IDs. It uses a fixed seed, but does not
promise the same IDs across SDK versions or changes to the source dataset.
For shuffled training reads, worker configuration and data loaders, see
[Loading data for model training](/training/data-loading).
## Related guides {#reference}
Count the sampled rows and [inspect their distribution](/curation/aggregate)
before using them. Preserve the selected data when the exact membership matters.
See [SQL sampling](/sql/dql/sampling) for syntax and restrictions.