count_distinct
Count distinct entities by a stable key, compare with row count, and report gaps from repeated or blank keys.
Instructions
Count distinct entities on a stable key, and report the gap to a row count.
Use this instead of counting rows whenever the unit you are reporting on is an entity — customers, patients, households, sites, invoices — rather than a line in a table. One customer can appear on fifty rows; "50" is a row count, not a customer count, and the difference is the error that survives review because the number looks plausible.
This tool returns both numbers and the difference between them, broken into the two causes: keys repeated across rows, and rows that carry no usable key. Rows with a blank key are excluded from the count and listed individually — never counted as one entity, never dropped.
Args:
rows: The data, as an array of objects. Example:
[{"customer_id": "C-1", "site": "A"}, {"customer_id": "C-1", "site": "B"}].
key: The column holding the stable identifier. Choose a real identifier
(an account number, a registration key), not a name — names collide,
and two different people sharing a name are not one entity.
case_sensitive: If false (the default), keys are compared after trimming
surrounding whitespace and folding case, so "C-1" and " c-1 "
count as one entity. Set true to treat any difference as a different
entity. Nothing else is normalised: no punctuation is stripped and no
fuzzy matching is applied, because those change which records are
considered the same entity, and that is a decision for a person.
Returns:
An object with:
ok (true when the row count and the distinct count agree),
verdict ("clean" | "review_required"),
row_count, distinct_count, difference,
difference_breakdown ({from_repeated_keys, from_rows_with_blank_key}),
rows_with_a_key, rows_with_blank_key, blank_key_rows,
repeated_keys (each with its key and the row indexes it appears on),
findings, and guidance.
Report `distinct_count` as the entity count. Report `row_count` as the
row count. Never present one as the other.Raises:
ToolError: if key is not a non-empty string. Rows that are not
objects, or whose key is missing or blank, never raise — they are
held back, counted in rows_with_blank_key, and listed in
blank_key_rows.
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| key | Yes | ||
| rows | Yes | ||
| case_sensitive | No |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
No arguments | |||