Profiling a dataset

Applies to: Embedded ❌, Enterprise ✅ Use SQL aggregates to summarize a dataset: count records, find missing values, and compare groups. These summaries help you understand the data before selecting records for search, evaluation or training.

Example data

Connect with an Enterprise SQL client. The examples use a small document table and a separate label table. Expand the setup below to create them.

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.

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.

Count records and missing values

Use count(*) to count rows and count(column) to count non-null values. Add DISTINCT to count unique values:

SELECT count(*) AS records,
       count(DISTINCT doc_id) AS documents,
       count(caption) AS captioned,
       count(*) FILTER (WHERE caption IS NULL) AS missing_caption
FROM curation_documents;

The example returns 6 records, 4 documents, 4 populated captions and 2 missing captions. count(column) excludes null values; it does not check whether a string is empty or whether a value is useful for the task.

Group records by label

Use GROUP BY to calculate a summary for each label:

SELECT label, count(*) AS documents
FROM curation_labels
GROUP BY label
ORDER BY documents DESC, label;

There are 2 cats and 1 dog in the label table. Document 40 is absent, so this query does not measure labeling coverage across all source documents. Use a left join to find documents without labels. Decide whether the distribution matches your intended use. A uniform random sample tends to preserve the source distribution; it does not balance classes. To cap the number selected per label, use ranking within a group.

Count records that match a condition

SELECT source,
       count(*) AS records,
       count(*) FILTER (WHERE score >= 0.9) AS high_score,
       count(*) FILTER (WHERE caption IS NULL) AS missing_caption
FROM curation_documents
GROUP BY source
ORDER BY source;

FILTER applies a condition to one aggregate. Here, books has 2 records, 1 high-score record and no missing captions. web has 4 records, 1 high-score record and 2 missing captions. Counts are per record, so repeated document IDs still contribute more than once.

Group numeric values into ranges

Use count(DISTINCT column) for the number of distinct non-null values. For numeric fields, group by a range or summarize their distribution. For example, this query counts records in score buckets of width 0.1:

SELECT floor(score * 10) / 10 AS score_bucket, count(*) AS records
FROM curation_documents
GROUP BY score_bucket
ORDER BY score_bucket;

For time-based data, group by the time interval relevant to the task. The aggregation reference and scalar function reference cover additional summaries, including approximate counts and percentiles. Use an approximate aggregate when an estimate is sufficient; use an exact count when the result must be precise.

Aggregate over a Function’s result

An aggregate can summarize a Function’s output the same as any other expression — the engine computes the Function once per row, then aggregates. Register caption_quality the same way any other curation Function is registered:

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()

SELECT source, avg(caption_quality(caption)) AS avg_quality
FROM curation_documents
WHERE caption IS NOT NULL
GROUP BY source
ORDER BY source;

Filtering out null captions first avoids calling the Function on rows with nothing to score.

Profile a sample

A sample can help you explore a large dataset before a full scan. Report the sample size and method alongside the result. A sample can miss rare classes, and fragment sampling can reflect the order in which records were written. Before accepting a curated dataset, check its required fields and key constraints directly rather than inferring them from a small sample.

Continue curating

Use these measurements to choose a sample, attach missing metadata, or select one record per document. See Aggregation for complete SQL syntax.

Introduction

Getting Started

Concepts

Data Ingestion

Search

Data Curation

Feature Engineering

Training

Management

Maintenance

Security

Deployment

SQL Reference

Profiling a dataset

Example data

Count records and missing values

Group records by label

Count records that match a condition

Group numeric values into ranges

Aggregate over a Function’s result

Profile a sample

Continue curating

​Example data

​Count records and missing values

​Group records by label

​Count records that match a condition

​Group numeric values into ranges

​Aggregate over a Function’s result

​Profile a sample

​Continue curating

Example data

Count records and missing values

Group records by label

Count records that match a condition

Group numeric values into ranges

Aggregate over a Function’s result

Profile a sample

Continue curating