Keys and duplicates

Key findings identify columns or column sets that uniquely identify rows. Duplicate findings identify repeated rows. Both use exact counts or comparisons over the full table, with measured basis, full scope, and direct_comparison validation unless the comparison rule states otherwise.

Single-column keys¶

A column is a key candidate when it is null-free and its distinct count equals the row count. Both counts use the full column, at any cardinality. The profiler ranks candidates using:

0.4 × completeness + 0.4 × type affinity + 0.2 × name evidence.

Integer and unsigned dtypes score full type affinity, strings 0.8, temporal columns 0.4, and floats 0.2. A name containing a token such as id, key, code, no, num, number, uuid, guid, sku, or ref scores the name component. The top candidate appears in the headline. This score is a heuristic for choosing among verified candidates; it does not establish the column’s intended meaning.

Composite keys¶

When no single column is a key, pairs are screened: null-free columns with at least two distinct values are hashed over a 2,048-row stratified sample, and pairs that look unique there — up to 16 candidates (24 on deep), ranked by combined key score — are verified by exact canonical counting over the full table. Deep budgets continue to triples over the 12 most promising columns not already in a verified pair. A composite-key finding states the pair that jointly identifies rows, with the exact verification behind it.

Full-table verification establishes whether a screened pair is a key.

Dirty keys¶

A dirty key is a near-unique column with a small number of repeated values. The finding reports their count and examples. Joining on repeated values can multiply rows, so these examples help identify records to inspect.

Uniqueness and identifier role remain separate findings: uniqueness is measured from counts, while the role is inferred from those counts.

Row duplicates¶

Duplicate detection compares full rows. Hashes identify candidates, and canonical bytes confirm equality, so a hash collision alone cannot create a duplicate finding. The finding reports duplicate groups, redundant rows, the largest group, and example rows. Its specification records the comparison rule: exact equality, with missing values comparing equal.

A verified unique, null-free column rules out duplicate rows. In that case the duplicate pass skips the full-row comparison and records the deduction in coverage.

import random

import polars as pl

import onceover

rng = random.Random(5)
n = 3_000
external_ref = [f"R{i:05}" for i in range(n)]
external_ref[-1] = external_ref[0]     # two duplicated values
external_ref[-2] = external_ref[1]
region_id = [rng.randrange(3) for _ in range(n)]
region_name = [["emea", "amer", "apac"][r] for r in region_id]
rows = {
    "order_id": list(range(n)),
    "external_ref": external_ref,
    "region_id": region_id,
    "region_name": region_name,
}
rows.update({f"noise_{i}": [rng.random() for _ in range(n)] for i in range(3)})
df = pl.DataFrame(rows)
df = pl.concat([df, df[100:103]])      # three duplicated rows

report = onceover.profile(df)
print(report.overview["headline"])
for f in report.findings:
    if f["category"] in {"uniqueness", "duplicates"}:
        print(f"[{f['interestingness']:.2f}] {f['claim']}")
        for e in f["evidence"]:
            m = e["metrics"]
            if "examples" in m:
                print("   examples:", m["examples"][:3])

3003 rows × 7 columns (standard budget); 2 functional dependencies, 2 predictive dependencies, 6 conditional rules
[0.82] column external_ref is nearly unique with 5 repeated non-null values
   examples: ['R00000', 'R00001', 'R00100']
[0.82] column noise_0 is nearly unique with 3 repeated non-null values
   examples: ['0.549288', '0.947475', '0.995673']
[0.82] column noise_1 is nearly unique with 3 repeated non-null values
   examples: ['0.038087', '0.10828', '0.620278']
[0.82] column noise_2 is nearly unique with 3 repeated non-null values
   examples: ['0.379051', '0.406387', '0.419232']
[0.82] column order_id is nearly unique with 3 repeated non-null values
   examples: ['100', '101', '102']
[0.78] 3 rows repeat earlier values across the compared columns (3 groups, largest 2)
   examples: [[102, 3002], [100, 3000], [101, 3001]]

The headline reports no key candidate. Appending three duplicate rows made every column non-unique. The uniqueness findings report three repeated non-null values in order_id and each random noise_* column. The repeated order IDs are 100, 101, and 102. The region columns remain low-cardinality lookup fields.

The duplicates finding reports three groups of two identical rows and gives their row-index pairs. external_ref has two additional planted repeats, for five repeated values in total.

A dirty key, an identifier role, and a duplicate row describe different properties of these data: the column statements are measured full-column counts, the duplicate statement is a measured full-row comparison, and the role is inferred from the counts. The usage chapters show how these findings drive decisions (Quickstart and the vendor-extract case study).