AurelDemiri/claude-skills

Bad Data Analyser

This skill helps diagnose two very different kinds of "bad data":

1. Capped / clipped — the storage system silently lost information. The data was real, but it can't be trusted as recorded because it hit a ceiling: an INT32 primary key saturated at 2.1 billion, a TIME field that represents "days" got chopped to 838 hours, a VARCHAR(255) truncated names in the middle of a word, a 32-bit Unix timestamp rolled over to 1901.
2. Fabricated — the data was never real. It was hand-typed by a human trying to look random, generated by Faker/Mockaroo, or filled in under deadline pressure by someone who didn't want to do the work. Real-world numbers obey patterns — most famously Benford's Law — that fabricators, human or script, routinely fail to reproduce.

These two failure modes look different in the data and call for different tests. Don't mix them up in the report.

When this skill applies (and when it doesn't)

Applies well to:

• Tabular data (CSV, Excel, Parquet, database dumps, DataFrames) where columns have types.
• Numeric columns with enough rows (see sample-size rules in references/fake-data.md).
• Datasets where the user has an interpretation of each column (this column is "sales amount", that one is "user ID", etc.).

Does not apply (or applies weakly) to:

• Very small datasets (< 30 rows for cap checks, < ~500 rows for any fake-data test, < ~1000 for first-two-digits Benford).
• Columns that are naturally constrained and therefore look capped but aren't (e.g. age, percentage, star ratings, exam scores).
• Data where Benford's Law is not expected to hold — see the "When Benford applies" section of references/fake-data.md. Running Benford on ages or shoe sizes will produce a confident-looking but meaningless result, which is worse than not running it.

If the skill is clearly the wrong tool, say so upfront and explain why. A confident false positive from this skill is worse than no answer.

Workflow

Follow these steps in order. Don't skip the profiling step — the tests you run depend on what the columns actually contain.

Step 1 — Profile the data

Before running any test, build a profile of each column. The scripts/analyse.py script does this automatically, but you can also do it inline for small datasets. Record for each column:

• The inferred semantic type: integer, float, date/time, string, id-like, categorical, boolean.
• Row count, non-null count, unique count.
• For numbers: min, max, mean, median, stddev, number of values at the boundaries.
• For strings: min/max length, unique count, and whether lengths cluster near powers of two.
• For dates: min, max, whether values cluster at known epochs (1970-01-01, 1900-01-01, 2038-01-19, 9999-12-31).

The profile itself is a finding. A column with max = 2147483647 and 400 rows at exactly that value is telling a story before any test runs.

Step 2 — Run cap / clip checks

For every numeric, date, and string column, compare against the catalog in references/caps.md. The script scripts/analyse.py automates this. What to flag:

• Saturation at a known type ceiling. E.g. many values at exactly 127, 255, 32767, 65535, 2147483647, 4294967295, 9.22e18. The more rows pile up at the ceiling, the stronger the signal.
• Values exactly at a type floor in places where negative numbers don't make sense (e.g. -1 as a "not found" sentinel, -2147483648 as an int32 underflow).
• String lengths pinned at 255, 65535, or other VARCHAR/TEXT caps, especially with a suspiciously high fraction of strings at exactly that length.
• Dates pinned at epoch boundaries: 1970-01-01 00:00:00 (null-as-epoch), 1900-01-01 (Excel's floor), 2038-01-19 03:14:07 or 1901-12-13 20:45:52 (32-bit signed rollover), 2106-02-07 06:28:15 (32-bit unsigned rollover), 9999-12-31 (max placeholder).
• Durations pinned at 838:59:59 — that is the MySQL TIME cap, and it means someone stored a day-count in a time column.

For any hit, report: the column, the suspected cap, the count and fraction of rows at the boundary, and the downstream impact (e.g. "sums over this column are wrong by at least X").

Step 3 — Run fabrication checks

These are the Benford-family tests and the human-RNG bias checks. See references/fake-data.md for the full battery and the sample-size rules. In order of usefulness:

1. Benford first-digit test (needs ≳ 500 values, spans at least 2 orders of magnitude, not an ID/ZIP/age column).
2. Benford second-digit and first-two-digits tests (need ≳ 1000 values, more discriminating).
3. Last-digit / last-two-digits uniformity (should be uniform 0.10 per digit; deviations flag rounding or human fabrication).
4. Duplicate analysis — too-few duplicates is a fabrication signal (humans avoid obvious repeats); too-many may signal copy-paste or automated fill.
5. Terminal-digit preference — excess 0s and 5s (rounding), or excess 7s (humans picking "random" digits).
6. Placeholder / Faker fingerprints — scan strings for example.com, test@, John Doe, Jane Doe, Lorem Ipsum, 555-01xx phone numbers, invalid SSN prefixes (000, 666, 900-999), and the Woolworth wallet SSN 078-05-1120.
7. Timestamp regularity — suspiciously round seconds (everything at :00), impossible gaps, missing weekends.

Report each test with its conformity metric (MAD for Benford; χ² or a simple deviation for the others), the Nigrini cutoff it falls into ("close", "acceptable", "marginal", "nonconforming"), and — crucially — a plain-English interpretation. A number without an interpretation is noise.

Step 4 — Synthesise

Write a short report with three sections:

• Likely capped columns — with the suspected cap, the evidence, and what corrective action looks like (widen the column type, re-export from source, etc.).
• Likely fabricated columns or rows — with the test that flagged them and the strength of the signal. Be explicit when a signal is only "suggestive" rather than "damning"; Benford in particular has many false-positive modes.
• Clean columns — briefly note what passed, so the reader knows where this analysis does not raise concerns.

Rate each finding with a severity: HIGH (near-certain, with clear evidence), MEDIUM (suggestive pattern consistent with the hypothesis), LOW (worth noting but could easily be innocent). Err toward the lower severity when uncertain — crying wolf is a bigger cost here than a quiet miss, because users will act on HIGH findings.

How to run the automated analysis

The skill ships with a single script that handles most of the workflow:

python scripts/analyse.py <path-to-data-file> [--column NAME] [--sample N]

It accepts .csv, .tsv, .parquet, .xlsx, and .json (line-delimited or array). It produces a structured report covering profiling, cap checks, and the full Benford / last-digit / placeholder suite. Flags:

• --column NAME limits the analysis to a single column (useful when the user has a specific suspicion).
• --sample N subsamples very large files.
• --json emits machine-readable output instead of the human-readable report.

If the script's automatic column-type inference is wrong (e.g. it thinks a ZIP code is a number and runs Benford on it), re-run with --column targeting only the columns where the test is meaningful. Always sanity-check the inference against the user's interpretation before reporting.

References

• references/caps.md — exhaustive catalog of numeric, string, date/time caps across MySQL, PostgreSQL, SQL Server, Oracle, Excel, JavaScript, Java, and common file formats, plus common sentinel/placeholder values. Read this before writing up a cap finding.
• references/fake-data.md — Benford's Law (first, second, first-two, last-two digit variants), the MAD and χ² conformity metrics with Nigrini's cutoffs, human-RNG biases, terminal-digit preference, Faker/Mockaroo fingerprints, and the sample-size rules. Read this before writing up any fabrication finding.
• references/interpretation.md — common false-positive modes, how to phrase findings for non-technical readers, and what not to claim.

Guardrails

• Never conclude "this data is fake" from Benford alone. Benford is a screening tool, not a proof. A failing Benford test is a reason to investigate further — nothing more. Say so in the report.
• Always ask about the column's meaning when it matters. A column named id that fails Benford is uninteresting (IDs don't follow Benford). A column named revenue that fails Benford is interesting. Don't guess; ask.
• If you don't have enough data, say so. Running Benford on 50 rows produces a number but not a finding. The sample-size guidance in references/fake-data.md is not optional.
• Capping and fabrication have different fixes. Capping is a bug in the pipeline; fabrication is a fraud or quality issue with the source. Getting them mixed up in the report wastes the reader's time.