Data quality, validation, PII detection and anomaly detection for DuckDB
Anofox Tabular is a DuckDB extension with about 100 SQL functions for checking and cleaning tabular data. It validates emails, phone numbers, addresses, VAT numbers and money values, runs data quality checks, finds and masks personal data, scores anomalies, diffs tables and profiles columns. Everything runs inside DuckDB, on your tables, with SQL.
Using an AI assistant? The repository is also a Claude Code plugin: six skills that give Claude the exact signatures, output columns and pitfalls of every function, so it writes working SQL from a request such as "check orders for nulls and duplicates every night and keep the results". Install with
/plugin marketplace add DataZooDE/anofox-tabular, then/plugin install anofox-tabular@anofox-tabular.
| Module | What it does | Docs |
|---|---|---|
Validate addresses by regex (format only: a typo like gmail.con passes), DNS MX lookup or SMTP handshake |
api/01-email | |
| Postal | Parse and normalize addresses with libpostal; needs ~500 MB of data in ~/.duckdb/extensions/libpostal; parts are lower-case and the country is the name as written, not an ISO code |
api/02-postal |
| Phone | Parse, validate, format and compare phone numbers of all 245 libphonenumber regions and its non-geographic numbers (+800, ...), with libphonenumber's metadata (a number with an unknown region or country code is not judged: NULL) |
api/03-phone |
| Money | Exact DECIMAL(18,3) money values, currency-safe arithmetic, formatting for 38 currencies |
api/04-money |
| VAT | Validate VAT numbers of 28 countries (27 EU member states plus GB/XI): syntax for all, check digits for 16 of them | api/05-vat |
| PII | Detect and mask emails, card numbers, IBANs, SSNs, dates of birth, German SV, health insurance (KVNR) and ID card numbers, names and more; scan whole tables | api/06-pii |
| Data quality | 17 checks, a table-driven suite runner and event outputs | api/07-data-quality |
| Anomaly detection | Isolation Forest, DBSCAN and OutlierTree with explanations | api/08-anomaly-detection |
| Diffing | Row-level diff of two tables on a primary key, column-level schema drift | api/09-diffing |
| Profiling | Per-column statistics, table overview, correlations | api/10-profiling |
- Installation
- Quick Start
- Conventions
- Network and Privacy
- Python
- Claude Code Skills
- Documentation
- Development
- Support and Feedback
- License
- Citation
- Acknowledgments
INSTALL anofox_tabular FROM community;
LOAD anofox_tabular;To upgrade to the newest release:
FORCE INSTALL anofox_tabular FROM community;Supported: DuckDB v1.5.6 and 2.0 on linux_amd64, osx_arm64 (Apple silicon) and
windows_amd64. Intel Macs (osx_amd64) are no longer supported since v2026.10.31. Releases use
date versions such as v2026.11.13.
The community build can be older than these docs. Check which release you have:
SELECT extension_version FROM duckdb_extensions() WHERE extension_name = 'anofox_tabular';This is the date version of the release (v2026.11.13). Builds for DuckDB v1.5.6 up to
v2026.10.29 report the short git commit hash of the release instead (for example a3bc642), as
does a build made from a checkout without the release tags. To map a hash to its release, open https://github.com/DataZooDE/anofox-tabular/commit/<hash>
(GitHub lists the release tags that contain the commit), or run git tag --contains <hash> in
a clone of the repository.
Some parts depend on the platform: the NER-based PII types (names, organizations, locations) need OpenVINO, which the Linux x64 and macOS x64 builds include, and the postal module is not available on Windows.
pip install anofox-tabular # or: uv add anofox-tabularThe package is imported as anofox. See Python.
See docs/dev/building.md.
This runs as pasted in the DuckDB CLI after the INSTALL above. It creates its own data.
LOAD anofox_tabular;
SET anofox_tab_trace_level = 'warn'; -- optional: hide the [anofox] [info] trace lines
-- Validate contact data
SELECT email_is_valid('user@example.com') AS email_ok,
vat_is_valid('DE136695976') AS vat_ok;
-- Find and mask PII in free text
SELECT pii_mask('Reach jane@corp.com, card 4111 1111 1111 1111', 'redact') AS masked;
-- A data quality check: one pass/fail row with a message
CREATE TABLE orders AS
SELECT i AS id,
CASE WHEN i % 50 = 0 THEN NULL ELSE i % 7 END AS customer_id,
CASE WHEN i = 77 THEN 99999.0 ELSE 20 + i % 13 END AS amount
FROM range(1, 501) t(i);
SELECT status, message FROM null_rate('orders', 'customer_id', 0.01);
-- Anomaly scores with Isolation Forest
-- (trees, sample size, contamination, output mode, seed)
SELECT row_id, value, round(anomaly_score, 3) AS score, is_anomaly
FROM isolation_forest('orders', 'amount', 100, 256, 0.01, 'scores', 42)
ORDER BY anomaly_score DESC
LIMIT 3;
-- Compare two versions of a table
CREATE TABLE customers_v1 AS
FROM (VALUES (1, 'Ada', 'ada@example.com'),
(2, 'Bob', 'bob@example.com'),
(3, 'Cy', 'cy@example.com')) t(id, name, email);
CREATE TABLE customers_v2 AS
FROM (VALUES (1, 'Ada', 'ada@example.com'),
(2, 'Bob', 'robert@example.com'),
(4, 'Dee', 'dee@example.com')) t(id, name, email);
SELECT * FROM diff_joindiff('customers_v1', 'customers_v2', 'id') ORDER BY id;Expected output:
┌──────────┬─────────┐
│ email_ok │ vat_ok │
├──────────┼─────────┤
│ true │ true │
└──────────┴─────────┘
┌───────────────────────────────────┐
│ masked │
├───────────────────────────────────┤
│ Reach [EMAIL], card [CREDIT_CARD] │
└───────────────────────────────────┘
┌─────────┬──────────────────────────────────────────────┐
│ status │ message │
├─────────┼──────────────────────────────────────────────┤
│ fail │ Null rate 0.02 (10/500) exceeds maximum 0.01 │
└─────────┴──────────────────────────────────────────────┘
┌────────┬─────────┬────────┬────────────┐
│ row_id │ value │ score │ is_anomaly │
├────────┼─────────┼────────┼────────────┤
│ 77 │ 99999.0 │ 0.752 │ true │
│ 25 │ 32.0 │ 0.557 │ false │
│ 38 │ 32.0 │ 0.557 │ false │
└────────┴─────────┴────────┴────────────┘
┌───────────┬───────┬─────────┬────────────────────┬─────────────────┐
│ diff_type │ id │ name │ email │ changed_columns │
├───────────┼───────┼─────────┼────────────────────┼─────────────────┤
│ changed │ 2 │ Bob │ robert@example.com │ [email] │
│ removed │ 3 │ NULL │ NULL │ NULL │
│ added │ 4 │ Dee │ dee@example.com │ NULL │
└───────────┴───────┴─────────┴────────────────────┴─────────────────┘
The first PII call downloads the NER model used for names, organizations and locations
(bert-multi, ~178 MB, for German, English and eight other languages; anofox_ner_model
selects the smaller English-only distilbert-en instead). Run SET anofox_ner_auto_download = false; first to skip it; the pattern-based
types (email, credit card, IBAN, …) work without it, and the output above is the same.
To try the Python example scripts, see examples/README.md.
More: Getting started. Importing CRM data? The CRM import recipe checks email, phone, VAT, amounts and currencies of a text import in one query.
Every function has a canonical name with the prefix anofox_tab_ and a short alias, so
vat_is_valid and anofox_tab_vat_is_valid are the same function. Arguments are positional;
id_column, start_date/end_date and every diff_* parameter also accept the named form
name := value. Table functions take the table name as a string, and a few of them need a
regular table instead of a TEMP table and read only committed data; see
API conventions.
A few things that often trip people up:
- Email mode
regex, the default, checks the format only:claire@exempleandgmail.conpass. Modednsalso looks up the domain's mail servers. - The phone
regionargument is an ISO 3166 alpha-2 code such as'DE';'UK'is accepted as'GB'; three-letter codes such as'DEU'leave the number unjudged (NULL).phonenumber_region(phone, region)also takes the region; passNULLto useanofox_tab_phonenumber_default_region. money_format(money, style)andvat_format(vat, style)need a style:'symbol','long'or'code'for money,'plain'or'iso'for VAT.money('1250.00', 'EUR')with a string literal is a binder error (theDOUBLEandDECIMALoverloads tie), also insideTRY(). Cast first:money(CAST('1250.00' AS DECIMAL(18,3)), 'EUR').- Validation functions return
falseorNULLfor bad values;moneyraises for an unknown currency. The table in API conventions lists what each module does.
The core functions run locally and send nothing. These parts use the network:
| Feature | Network use | Opt out |
|---|---|---|
| PII names, organizations, locations (NER) | Downloads a ~178 MB model (bert-multi; 66 MB for distilbert-en) from Hugging Face on first use |
SET anofox_ner_auto_download = false |
| Postal addresses | Downloads ~500 MB of libpostal data on first use or with postal_load_data() |
SET anofox_tab_postal_auto_download = false (then only postal_load_data() downloads) |
Email modes dns and smtp |
DNS query; SMTP connection to the mail host | Use mode regex (the default) |
| Telemetry | Anonymous usage events to PostHog EU; on by default | SET anofox_telemetry_enabled = false or DATAZOO_DISABLE_TELEMETRY=1 |
Telemetry sends the extension and DuckDB version, the platform and per-function call counts, never your data, table names or SQL. The environment variable also stops the event sent when the extension loads. Details: Network and privacy and TELEMETRY.md.
import anofox
import pandas as pd
from anofox import validate, anomaly
conn = anofox.connect() # downloads the extension on first use
customers = pd.DataFrame({
"id": [1, 2, 3],
"email": ["ana@example.com", "bob(at)example.com", "carla@example.org"],
})
print(validate.email_is_valid(conn, customers, column="email"))
readings = pd.DataFrame({
"sensor_id": range(1, 21),
"value": [10.0 + (i % 5) for i in range(19)] + [95.0],
})
scores = anomaly.isolation_forest(
conn, readings, "value",
output_mode="scores", n_trees=100, sample_size=16, contamination=0.05,
id_column="sensor_id",
)
print(scores[scores["is_anomaly"]][["sensor_id", "value"]])The extension also loads into any DuckDB client (R, Node.js, Java, Go, Rust, ...) with
INSTALL anofox_tabular FROM community. See the Python guide
and python/README.md.
The repository ships a Claude Code plugin. Its skills hold the SQL surface of the extension: signatures and parameter order, output columns, settings, and the pitfalls (TEMP tables, list versus comma-separated column arguments, network use). With the plugin installed, Claude writes the queries for a request in plain language instead of guessing at signatures.
Install it in any Claude Code session:
/plugin marketplace add DataZooDE/anofox-tabular
/plugin install anofox-tabular@anofox-tabular
| Skill | Covers |
|---|---|
anofox-tabular-data-quality |
The 17 checks, _events outputs, run_checks suites with a result history, the integration contract |
anofox-tabular-anomaly |
Isolation Forest, DBSCAN and OutlierTree: output modes, id_column, explanations, gating a pipeline |
anofox-tabular-pii |
PII detection and masking, table scans and audits, NER and its settings |
anofox-tabular-master-data |
Email, phone, VAT, postal address and money validation and standardisation |
anofox-tabular-diff |
diff_joindiff, diff_hashdiff, diff_schema and migration sign-off |
anofox-tabular-profiling |
profile_table, profile_summary, profile_correlations |
The skills live in plugins/anofox-tabular/. Every SQL example in
them runs against the extension in CI (make check-docs), like the examples in these docs. To
try a local change to the skills, start Claude Code with
claude --plugin-dir ./plugins/anofox-tabular.
The full map is in docs/README.md.
| Guide | Topic |
|---|---|
| Getting started | Install and run a first check, PII scan and anomaly score |
| Data quality checks | Single checks, check suites, result history |
| Anomaly detection | Score, explain and gate on outliers |
| PII | Find, audit and mask personal data |
| Diffing and migration | Compare two versions of a table |
| Python | The anofox package with DataFrames |
| Customer master data | Clean emails, phones, VAT numbers and money |
| Page | Page | Page |
|---|---|---|
| 00 Conventions | 04 Money | 08 Anomaly detection |
| 01 Email | 05 VAT | 09 Diffing |
| 02 Postal | 06 PII | 10 Profiling |
| 03 Phone | 07 Data quality | 11 Integration contract |
- Configuration: all settings with type and default
- Network and privacy
- Troubleshooting
Build, test and architecture notes are in docs/dev/: building, testing, architecture and updating DuckDB. Contributions are welcome; see CONTRIBUTING.md.
- Documentation: docs/
- Issues: GitHub Issues
- Discussions: GitHub Discussions
If anofox_tabular misbehaves (a profile that looks wrong, a PII match it missed, a diff that
surprises you), please open an issue.
Results depend on real data shapes we cannot reproduce here, so a report with a small sample of
your input is the fastest path to a fix. If it saved you time, a star on the repo helps other
people find it.
The first time you load the extension in an interactive terminal each day, a small banner says
the same. It never prints when output is piped, in notebooks or in CI. Silence it with
SET datazoo_banner = false; or DATAZOO_NO_BANNER=1.
Business Source License 1.1. Production use is permitted, except offering the extension to third parties on a hosted or embedded basis. Each version converts to MPL 2.0 five years after its first publication. See LICENSE for the full terms and THIRD_PARTY_NOTICE for the licenses of the bundled libraries.
If you use this extension in research, please cite:
@software{anofox_tabular,
title = {Anofox Tabular: Data Quality and Validation for DuckDB},
author = {Joachim Rosskopf and DataZoo GmbH},
year = {2026},
url = {https://github.com/DataZooDE/anofox-tabular}
}Built on:
- DuckDB and its extension framework
- libpostal for address parsing
- OpenVINO and NER models from Hugging Face (Davlan's multilingual NER-HRL BERT and DistilBERT, an English CoNLL-2003 DistilBERT) for name, organization and location detection
- c-ares for DNS lookups, spdlog for logging, curl and OpenSSL
- isotree, whose ideas (Extended IF, SCiForest, density scoring) shaped the Isolation Forest options
- Google's libphonenumber, the model for the phone module