A DuckDB extension for data quality, validation and anomaly detection in SQL - 18 data quality checks (volume, nulls, freshness, schema drift, regex and value-set compliance, duplicates, referential integrity, time-aware baselines) with a table-driven check-suite runner, column profiling and correlations including nested types, hash- and join-based table diffing, anomaly detection with Isolation Forest (categorical, Extended IF, SCiForest), DBSCAN and explainable OutlierTree, PII detection and masking (17 types including IBAN, credit cards, API keys and NER-based names, organizations and locations), email validation with DNS/SMTP checks, international address parsing (libpostal), phone number validation (libphonenumber), EU VAT validation (28 countries), and currency-aware money arithmetic.
Maintainer(s):
jrosskopf
Compatibility Matrix
This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.
| Platform | 1.4.4 | 1.4.5 | 1.5.5 | 1.5.6 | 2.0.0 |
|---|---|---|---|---|---|
| Linux AMD64 | |||||
| Linux ARM64 | |||||
| macOS AMD64 | |||||
| macOS ARM64 | |||||
| Windows AMD64 | |||||
| Windows ARM64 | |||||
| WebAssembly |
Installing and Loading
INSTALL anofox_tabular FROM community;
LOAD anofox_tabular;
Example
-- Validate contact data
SELECT email_is_valid('[email protected]'), vat_is_valid('DE136695976');
-- Find and mask PII in free text
SELECT pii_mask('Reach [email protected], card 4111 1111 1111 1111', 'redact');
-- Reach [EMAIL], card [CREDIT_CARD]
-- 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);
-- fail | Null rate 0.02 (10/500) exceeds maximum 0.01
-- Anomaly scores with Isolation Forest (trees, sample size, contamination)
SELECT * FROM isolation_forest('orders', 'amount', 100, 256, 0.1, 'scores')
ORDER BY anomaly_score DESC LIMIT 3;
About anofox_tabular
Anofox Tabular brings data quality engineering into DuckDB: checks that return pass/fail assertion rows, check suites defined in a SQL table, profiling, table diffs for migration validation, and anomaly detection, plus validators and normalizers for emails, addresses, phone numbers, EU VAT IDs, money and PII. Everything runs locally inside DuckDB.
Functions carry an anofox_tab_ prefix and a short alias (email_is_valid for
anofox_tab_email_is_valid). Documentation:
https://github.com/DataZooDE/anofox-tabular/blob/main/docs/API_REFERENCE.md
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| agg_check | table | Asserts that an aggregate ('avg', 'min', 'max', 'sum', 'median', 'stddev') of a numeric column is between lower_threshold and upper_threshold (NULL = unbounded). | NULL | [SELECT * FROM agg_check('orders', 'amount', 'avg', 10.0, 500.0);] |
| anofox_ner_status | table | Returns the status of the NER (Named Entity Recognition) model used for name detection. | NULL | [SELECT * FROM anofox_ner_status();] |
| anofox_tab_agg_check | table | Asserts that an aggregate ('avg', 'min', 'max', 'sum', 'median', 'stddev') of a numeric column is between lower_threshold and upper_threshold (NULL = unbounded). | NULL | [SELECT * FROM agg_check('orders', 'amount', 'avg', 10.0, 500.0);] |
| anofox_tab_compliance | table | Asserts that the share of rows satisfying a SQL boolean expression is at least min_rate. Rows where the expression is NULL count as non-compliant. The expression is executed with the caller's privileges. | NULL | [SELECT * FROM compliance('orders', 'amount > 0 AND status IS NOT NULL', 0.95);] |
| anofox_tab_currency_name | scalar | Returns the full English name of a currency given its ISO 4217 code (e.g., 'US Dollar'). | NULL | [SELECT currency_name('EUR');] |
| anofox_tab_currency_symbol | scalar | Returns the currency symbol for a given ISO 4217 currency code (e.g., '$' for USD). | NULL | [SELECT currency_symbol('EUR');] |
| anofox_tab_dbscan | table | Clusters rows by a numeric column using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan('orders', 'amount', 0.5, 5, 'clusters');] |
| anofox_tab_dbscan_mv | table | Clusters rows by multiple numeric columns (comma-separated) using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan_mv('orders', 'amount,qty', 0.5, 5, 'clusters');] |
| anofox_tab_diff_hashdiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]). Currently identical to diff_joindiff; the bisection_threshold/bisection_factor parameters are not implemented and rejected. | NULL | [SELECT * FROM diff_hashdiff('products_v1', 'products_v2', 'product_id');, SELECT * FROM diff_hashdiff('products_v1', 'products_v2', ['product_id', 'sku']);] |
| anofox_tab_diff_joindiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]), returning rows that were added, removed, or changed. Primary keys are matched NULL-safely (IS NOT DISTINCT FROM). | NULL | [SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', 'order_id');, SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', ['order_id', 'line_id']);] |
| anofox_tab_distinct_count | table | Asserts that the number of distinct values in a column is between min_distinct and max_distinct. | NULL | [SELECT * FROM distinct_count('orders', 'status', 1, 10);] |
| anofox_tab_duplicate_count | table | Asserts that the number of duplicate values in a column (or comma-separated column combination) does not exceed max_duplicates (default 0). | NULL | [SELECT * FROM duplicate_count('orders', 'order_id');, SELECT * FROM duplicate_count('orders', 'customer_id,order_date', 10);] |
| anofox_tab_email_config | table | Returns the current configuration settings for the email validation module. | NULL | [SELECT * FROM email_config();] |
| anofox_tab_email_is_valid | scalar | Returns TRUE if the email address passes validation using the specified mode ('regex', 'dns', or 'smtp'). Defaults to the configured mode. | NULL | [SELECT email_is_valid('[email protected]');, SELECT email_is_valid('[email protected]', 'smtp');] |
| anofox_tab_email_validate | scalar | Validates an email address using the specified validation mode ('regex', 'dns', or 'smtp'). Returns a struct with validation details. Defaults to the configured mode. | NULL | [SELECT email_validate('[email protected]');, SELECT email_validate('[email protected]', 'dns');] |
| anofox_tab_freshness | table | Returns rows where the most recent value in a timestamp column is older than the specified maximum age interval. Optionally accepts a reference time. | NULL | [SELECT * FROM freshness('events', 'created_at', INTERVAL '1 day');] |
| anofox_tab_iqr | table | Identifies rows where a numeric column value is an outlier by the IQR (interquartile range) method. | NULL | [SELECT * FROM iqr('orders', 'amount', 1.5);] |
| anofox_tab_is_valid_currency | scalar | Returns TRUE if the given string is a valid ISO 4217 currency code. | NULL | [SELECT is_valid_currency('USD');] |
| anofox_tab_is_valid_vat_country | scalar | Returns TRUE if the 2-letter string is a country that uses VAT numbers. | NULL | [SELECT is_valid_vat_country('DE');] |
| anofox_tab_isolation_forest | table | Detects univariate outliers in a numeric column using the Isolation Forest algorithm. Returns scores and outlier labels. | NULL | [SELECT * FROM isolation_forest('sales', 'amount', 100, 256, 0.05, 'scores');] |
| anofox_tab_isolation_forest_mv | table | Detects multivariate outliers across multiple numeric columns (comma-separated) using the Isolation Forest algorithm. | NULL | [SELECT * FROM isolation_forest_mv('sales', 'amount,qty', 100, 256, 0.05, 'scores');] |
| anofox_tab_match_rate | table | Asserts that the share of left-table rows with a join partner in the right table (comma-separated key columns) is at least min_rate. Doubles as a referential-integrity check. | NULL | [SELECT * FROM match_rate('orders', 'customers', 'customer_id', 'id', 1.0);] |
| anofox_tab_metric_anomaly_iqr | table | Flags the reference date as anomalous when its daily metric (row count or AVG of metric_column) falls outside Q1/Q3 +/- k*IQR of the trailing window. Mode 'both', 'upper' or 'lower' selects the bounds. | NULL | [SELECT * FROM metric_anomaly_iqr('orders', 'order_date', NULL, 30, 1.5, 'both');] |
| anofox_tab_money | scalar | Creates a money value from a decimal amount and ISO 4217 currency code. | NULL | [SELECT money(19.99, 'USD');] |
| anofox_tab_money_abs | scalar | Returns the absolute value of a money amount. | NULL | [SELECT money_abs(money(-19.99, 'USD'));] |
| anofox_tab_money_add | scalar | Adds two money values of the same currency. | NULL | [SELECT money_add(money(10.00, 'USD'), money(5.00, 'USD'));] |
| anofox_tab_money_amount | scalar | Extracts the exact numeric amount (DECIMAL(18,3)) from a money value. | NULL | [SELECT money_amount(money(19.99, 'USD'));] |
| anofox_tab_money_currency | scalar | Extracts the ISO 4217 currency code from a money value. | NULL | [SELECT money_currency(money(19.99, 'USD'));] |
| anofox_tab_money_format | scalar | Formats a money value as a string using the specified style ('symbol', 'code', or 'plain'). | NULL | [SELECT money_format(money(19.99, 'USD'), 'symbol');] |
| anofox_tab_money_from_cents | scalar | Creates a money value from an integer amount in the smallest currency unit (e.g. cents) and ISO 4217 currency code. | NULL | [SELECT money_from_cents(1999, 'USD');] |
| anofox_tab_money_in_range | scalar | Returns TRUE if the money amount is within the inclusive range [min, max]. | NULL | [SELECT money_in_range(money(15.00, 'USD'), 10.0, 20.0);] |
| anofox_tab_money_is_negative | scalar | Returns TRUE if the money amount is less than zero. | NULL | [SELECT money_is_negative(money(-5.00, 'USD'));] |
| anofox_tab_money_is_positive | scalar | Returns TRUE if the money amount is greater than zero. | NULL | [SELECT money_is_positive(money(19.99, 'USD'));] |
| anofox_tab_money_is_zero | scalar | Returns TRUE if the money amount is exactly zero. | NULL | [SELECT money_is_zero(money(0.00, 'USD'));] |
| anofox_tab_money_multiply | scalar | Multiplies a money amount by a scalar factor. | NULL | [SELECT money_multiply(money(10.00, 'USD'), 1.5);] |
| anofox_tab_money_same_currency | scalar | Returns TRUE if two money values have the same currency code. | NULL | [SELECT money_same_currency(money(10.00, 'USD'), money(5.00, 'USD'));] |
| anofox_tab_money_subtract | scalar | Subtracts the second money value from the first (must be the same currency). | NULL | [SELECT money_subtract(money(10.00, 'USD'), money(3.00, 'USD'));] |
| anofox_tab_null_rate | table | Asserts that the fraction of NULL values in a column does not exceed max_null_rate. | NULL | [SELECT * FROM null_rate('orders', 'email', 0.05);] |
| anofox_tab_occurrence | table | Asserts that the highest ('max') or lowest ('min') frequency of any single value in a column is between lower_threshold and upper_threshold (NULL = unbounded). | NULL | [SELECT * FROM occurrence('orders', 'customer_id', 'max', NULL, 100);] |
| anofox_tab_outlier_tree | table | Identifies statistical outliers in a table using the OutlierTree algorithm, returning an explanation of which conditions make each row an outlier. | NULL | [SELECT * FROM outlier_tree('transactions', 'amount,balance', 'summary');] |
| anofox_tab_phonenumber_example | scalar | Returns an example valid phone number for the given 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_example('US');] |
| anofox_tab_phonenumber_format | scalar | Formats a phone number string in the specified format: 'E164', 'INTERNATIONAL', 'NATIONAL', or 'RFC3966'. | NULL | [SELECT phonenumber_format('+14155552671', 'NATIONAL');] |
| anofox_tab_phonenumber_is_possible | scalar | Returns TRUE if the phone number length is plausible for its region (lighter check than is_valid). | NULL | [SELECT phonenumber_is_possible('+14155552671');] |
| anofox_tab_phonenumber_is_valid | scalar | Returns TRUE if the phone number is a valid, dialable number. | NULL | [SELECT phonenumber_is_valid('+14155552671');] |
| anofox_tab_phonenumber_is_valid_for_region | scalar | Returns TRUE if the phone number is valid for the specified 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_is_valid_for_region('+14155552671', 'US');] |
| anofox_tab_phonenumber_match | scalar | Returns TRUE if two phone number strings refer to the same number. | NULL | [SELECT phonenumber_match('+14155552671', '(415) 555-2671');] |
| anofox_tab_phonenumber_parse | scalar | Parses a phone number string and returns a struct with E164, national, international, and RFC3966 formats, plus the region code. | NULL | [SELECT phonenumber_parse('+14155552671');] |
| anofox_tab_phonenumber_region | scalar | Returns the 2-letter ISO region code (e.g., 'US', 'DE') for a given phone number. | NULL | [SELECT phonenumber_region('+14155552671');] |
| anofox_tab_phonenumber_status | table | Returns the current configuration and status of the phone number module. | NULL | [SELECT * FROM phonenumber_status();] |
| anofox_tab_pii_audit_table | table | Returns a row-level audit of PII detected in all string columns of a table. | NULL | [SELECT * FROM pii_audit_table('customers');] |
| anofox_tab_pii_config | table | Returns the current configuration settings of the PII detection module. | NULL | [SELECT * FROM pii_config();] |
| anofox_tab_pii_contains | scalar | Returns TRUE if the text contains any PII of the specified type (e.g., 'email', 'phone', 'ssn'). | NULL | [SELECT pii_contains('Contact us at [email protected]', 'email');] |
| anofox_tab_pii_count | scalar | Returns the total count of PII entities detected in the text. | NULL | [SELECT pii_count('John at [email protected], tel: +1-555-0100');] |
| anofox_tab_pii_detect | scalar | Detects all PII entities in a text string and returns them as a list of structs with type, value, start, and end positions. | NULL | [SELECT pii_detect('Contact John at [email protected] or +1-555-123-4567');] |
| anofox_tab_pii_detect_batch | scalar | Runs all PII detectors on an array of text strings and returns a combined list of all detected entities per input. | NULL | [SELECT pii_detect_batch(['[email protected]', 'SSN: 123-45-6789']);] |
| anofox_tab_pii_detect_credit_cards | scalar | Detects all credit card numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_credit_cards('Pay with 4111111111111111');] |
| anofox_tab_pii_detect_emails | scalar | Detects all email addresses in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_emails('Contact [email protected] or [email protected]');] |
| anofox_tab_pii_detect_ibans | scalar | Detects all IBAN numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ibans('Bank: DE89370400440532013000');] |
| anofox_tab_pii_detect_names | scalar | Detects all person names in the text using NLP and returns them as a list of structs. | NULL | [SELECT pii_detect_names('Signed by John Smith and Jane Doe');] |
| anofox_tab_pii_detect_phones | scalar | Detects all phone numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_phones('Call +1-555-0100 or 0800 123 456');] |
| anofox_tab_pii_detect_ssns | scalar | Detects all Social Security Numbers (SSNs) in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ssns('SSN: 123-45-6789');] |
| anofox_tab_pii_is_valid_credit_card | scalar | Returns TRUE if the string is a valid credit card number (uses Luhn algorithm). | NULL | [SELECT pii_is_valid_credit_card('4111111111111111');] |
| anofox_tab_pii_is_valid_crypto_address | scalar | Returns TRUE if the string is a valid cryptocurrency wallet address (Bitcoin, Ethereum, etc.). | NULL | [SELECT pii_is_valid_crypto_address('1A1zP1eP5QGefi2DMPTfTL5SLmv7Divf');] |
| anofox_tab_pii_is_valid_de_tax_id | scalar | Returns TRUE if the string is a valid German tax identification number (Steueridentifikationsnummer). | NULL | [SELECT pii_is_valid_de_tax_id('12345678901');] |
| anofox_tab_pii_is_valid_iban | scalar | Returns TRUE if the string is a valid IBAN (International Bank Account Number). | NULL | [SELECT pii_is_valid_iban('DE89370400440532013000');] |
| anofox_tab_pii_is_valid_nino | scalar | Returns TRUE if the string is a valid UK National Insurance Number (NINO). | NULL | [SELECT pii_is_valid_nino('AB123456C');] |
| anofox_tab_pii_is_valid_ssn | scalar | Returns TRUE if the string is a valid US Social Security Number (SSN). | NULL | [SELECT pii_is_valid_ssn('123-45-6789');] |
| anofox_tab_pii_mask | scalar | Masks all detected PII in a text string using the specified strategy ('redact', 'hash', 'partial'). Defaults to 'redact'. | NULL | [SELECT pii_mask('Call me at +1-555-123-4567');, SELECT pii_mask('[email protected]', 'hash');] |
| anofox_tab_pii_mask_column | scalar | Masks a value of a specific PII type using the specified strategy ('redact', 'hash', 'partial'). | NULL | [SELECT pii_mask_column('[email protected]', 'email', 'hash');] |
| anofox_tab_pii_redact_column | scalar | Detects and redacts all PII in a text value using the specified strategy. Defaults to 'redact'. | NULL | [SELECT pii_redact_column('[email protected]');, SELECT pii_redact_column('[email protected]', 'hash');] |
| anofox_tab_pii_scan_table | table | Scans all string columns of a table and returns a summary of detected PII types per column. | NULL | [SELECT * FROM pii_scan_table('customers');] |
| anofox_tab_pii_status | table | Returns the current configuration and status of the PII detection module. | NULL | [SELECT * FROM pii_status();] |
| anofox_tab_postal_expand_address | scalar | Expands a postal address into all normalized variants using libpostal. | NULL | [SELECT postal_expand_address('123 main st springfield il');] |
| anofox_tab_postal_load_data | scalar | Downloads and installs the libpostal data bundle (~500 MB) required for address parsing and expansion. | NULL | [SELECT postal_load_data();] |
| anofox_tab_postal_parse_address | scalar | Parses a free-form postal address string into its structural components (house_number, road, city, state, postcode, country). | NULL | [SELECT postal_parse_address('123 Main St, Springfield, IL 62701');] |
| anofox_tab_postal_status | table | Returns the current status of the libpostal address parser, including whether it is initialized and the data directory path. | NULL | [SELECT * FROM postal_status();] |
| anofox_tab_profile_correlations | table | Computes pairwise Pearson correlation coefficients for numeric columns in a table. | NULL | [SELECT * FROM profile_correlations('orders');, SELECT * FROM profile_correlations('orders', ['amount', 'qty']);] |
| anofox_tab_profile_summary | table | Returns a summary profile of all columns in a table, including min, max, null_count, and distinct_count. | NULL | [SELECT * FROM profile_summary('orders');] |
| anofox_tab_profile_table | table | Profiles selected columns of a table with detailed statistics (min, max, mean, stddev, null_count, distinct_count, sample rows). Optionally filters columns, sets sample size, or uses exact counts. | NULL | [SELECT * FROM profile_table('orders');, SELECT * FROM profile_table('orders', ['amount', 'status']);] |
| anofox_tab_regex_match | table | Asserts that the share of non-NULL values matching a regex pattern is between min_match_rate and max_match_rate. | NULL | [SELECT * FROM regex_match('users', 'email', '^[^@]+@[^@]+$', 0.99);] |
| anofox_tab_rel_count_change | table | Asserts that the daily (distinct) count on the reference date deviates from the rolling window average by a relative change within [lower_threshold, upper_threshold]. Defaults: count rows, 7-day window, reference = latest date in the data. | NULL | [SELECT * FROM rel_count_change('orders', 'order_date', NULL, 7, -0.5, 0.5);] |
| anofox_tab_rolling_values_in_set | table | Asserts that the share of values contained in the allowed set over the trailing window_days days (ending at the reference date, inclusive) is at least min_rate. | NULL | [SELECT * FROM rolling_values_in_set('orders', 'status', ['completed', 'pending'], 'order_date', 14);] |
| anofox_tab_run_checks | table | Runs every check defined in a checks table (columns: check_name, check_type, table_name, column_name, params JSON, lower_threshold, upper_threshold, monitor_only, identifier_column, filter_expr) and returns one uniform result row per check and identifier partition (run_ts, check_name, check_type, table_name, column_name, identifier, value, lower_threshold, upper_threshold, status, message). The checks table must be a regular (non-temporary) table; target tables may be temporary. | NULL | [SELECT * FROM run_checks('dq_checks');, INSERT INTO dq_results SELECT * FROM run_checks('dq_checks');] |
| anofox_tab_schema_check | table | Asserts that a table contains all the required column names. | NULL | [SELECT * FROM schema_check('orders', ['id', 'amount', 'status']);] |
| anofox_tab_values_in_set | table | Asserts that the share of non-NULL values contained in the allowed value set is at least min_rate. Reports up to 5 sample violations. | NULL | [SELECT * FROM values_in_set('orders', 'status', ['completed', 'pending', 'failed']);] |
| anofox_tab_vat | scalar | Parses a VAT number string and returns a struct with country code, normalized number, and validity flags. | NULL | [SELECT vat('DE123456789');] |
| anofox_tab_vat_country_name | scalar | Returns the English country name for the VAT number's country prefix (e.g., 'Germany'). | NULL | [SELECT vat_country_name('DE123456789');] |
| anofox_tab_vat_exists | scalar | Returns TRUE if the country prefix of the VAT number exists in the supported country list. | NULL | [SELECT vat_exists('DE123456789');] |
| anofox_tab_vat_format | scalar | Formats a VAT number using the specified style: 'plain' (digits only) or 'iso' (VAT country prefix + digits). | NULL | [SELECT vat_format('DE123456789', 'iso');] |
| anofox_tab_vat_is_eu_member | scalar | Returns TRUE if the VAT number belongs to an EU member state. | NULL | [SELECT vat_is_eu_member('DE123456789');] |
| anofox_tab_vat_is_valid | scalar | Returns TRUE if the VAT number has valid syntax and passes the country's check-digit validation where implemented. | NULL | [SELECT vat_is_valid('DE111111125');] |
| anofox_tab_vat_is_valid_syntax | scalar | Returns TRUE if the VAT number matches the expected syntax for its country prefix (no network check). | NULL | [SELECT vat_is_valid_syntax('DE123456789');] |
| anofox_tab_vat_normalize | scalar | Normalizes a VAT number by removing spaces, dashes, and other formatting characters. | NULL | [SELECT vat_normalize('DE 123 456 789');] |
| anofox_tab_vat_split | scalar | Splits a VAT number into a struct with 'country_code' and 'number' fields. | NULL | [SELECT vat_split('DE123456789');] |
| anofox_tab_volume | table | Asserts that a table has between min_rows and max_rows rows, returning the count and assertion status. | NULL | [SELECT * FROM volume('orders', 100, 1000000);] |
| anofox_tab_zscore | table | Identifies rows where a numeric column value deviates more than threshold standard deviations from the mean. | NULL | [SELECT * FROM zscore('orders', 'amount', 3.0);] |
| compliance | table | Asserts that the share of rows satisfying a SQL boolean expression is at least min_rate. Rows where the expression is NULL count as non-compliant. The expression is executed with the caller's privileges. | NULL | [SELECT * FROM compliance('orders', 'amount > 0 AND status IS NOT NULL', 0.95);] |
| currency_name | scalar | Returns the full English name of a currency given its ISO 4217 code (e.g., 'US Dollar'). | NULL | [SELECT currency_name('EUR');] |
| currency_symbol | scalar | Returns the currency symbol for a given ISO 4217 currency code (e.g., '$' for USD). | NULL | [SELECT currency_symbol('EUR');] |
| dbscan | table | Clusters rows by a numeric column using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan('orders', 'amount', 0.5, 5, 'clusters');] |
| dbscan_mv | table | Clusters rows by multiple numeric columns (comma-separated) using DBSCAN. Returns cluster labels and noise flags. | NULL | [SELECT * FROM dbscan_mv('orders', 'amount,qty', 0.5, 5, 'clusters');] |
| diff_hashdiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]). Currently identical to diff_joindiff; the bisection_threshold/bisection_factor parameters are not implemented and rejected. | NULL | [SELECT * FROM diff_hashdiff('products_v1', 'products_v2', 'product_id');, SELECT * FROM diff_hashdiff('products_v1', 'products_v2', ['product_id', 'sku']);] |
| diff_joindiff | table | Computes a row-level diff between two tables using a primary key (VARCHAR or VARCHAR[]), returning rows that were added, removed, or changed. Primary keys are matched NULL-safely (IS NOT DISTINCT FROM). | NULL | [SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', 'order_id');, SELECT * FROM diff_joindiff('orders_v1', 'orders_v2', ['order_id', 'line_id']);] |
| distinct_count | table | Asserts that the number of distinct values in a column is between min_distinct and max_distinct. | NULL | [SELECT * FROM distinct_count('orders', 'status', 1, 10);] |
| duplicate_count | table | Asserts that the number of duplicate values in a column (or comma-separated column combination) does not exceed max_duplicates (default 0). | NULL | [SELECT * FROM duplicate_count('orders', 'order_id');, SELECT * FROM duplicate_count('orders', 'customer_id,order_date', 10);] |
| email_config | table | Returns the current configuration settings for the email validation module. | NULL | [SELECT * FROM email_config();] |
| email_is_valid | scalar | Returns TRUE if the email address passes validation using the specified mode ('regex', 'dns', or 'smtp'). Defaults to the configured mode. | NULL | [SELECT email_is_valid('[email protected]');, SELECT email_is_valid('[email protected]', 'smtp');] |
| email_validate | scalar | Validates an email address using the specified validation mode ('regex', 'dns', or 'smtp'). Returns a struct with validation details. Defaults to the configured mode. | NULL | [SELECT email_validate('[email protected]');, SELECT email_validate('[email protected]', 'dns');] |
| freshness | table | Returns rows where the most recent value in a timestamp column is older than the specified maximum age interval. Optionally accepts a reference time. | NULL | [SELECT * FROM freshness('events', 'created_at', INTERVAL '1 day');] |
| iqr | table | Identifies rows where a numeric column value is an outlier by the IQR (interquartile range) method. | NULL | [SELECT * FROM iqr('orders', 'amount', 1.5);] |
| is_valid_currency | scalar | Returns TRUE if the given string is a valid ISO 4217 currency code. | NULL | [SELECT is_valid_currency('USD');] |
| is_valid_vat_country | scalar | Returns TRUE if the 2-letter string is a country that uses VAT numbers. | NULL | [SELECT is_valid_vat_country('DE');] |
| isolation_forest | table | Detects univariate outliers in a numeric column using the Isolation Forest algorithm. Returns scores and outlier labels. | NULL | [SELECT * FROM isolation_forest('sales', 'amount', 100, 256, 0.05, 'scores');] |
| isolation_forest_mv | table | Detects multivariate outliers across multiple numeric columns (comma-separated) using the Isolation Forest algorithm. | NULL | [SELECT * FROM isolation_forest_mv('sales', 'amount,qty', 100, 256, 0.05, 'scores');] |
| match_rate | table | Asserts that the share of left-table rows with a join partner in the right table (comma-separated key columns) is at least min_rate. Doubles as a referential-integrity check. | NULL | [SELECT * FROM match_rate('orders', 'customers', 'customer_id', 'id', 1.0);] |
| metric_anomaly_iqr | table | Flags the reference date as anomalous when its daily metric (row count or AVG of metric_column) falls outside Q1/Q3 +/- k*IQR of the trailing window. Mode 'both', 'upper' or 'lower' selects the bounds. | NULL | [SELECT * FROM metric_anomaly_iqr('orders', 'order_date', NULL, 30, 1.5, 'both');] |
| money | scalar | Creates a money value from a decimal amount and ISO 4217 currency code. | NULL | [SELECT money(19.99, 'USD');] |
| money_abs | scalar | Returns the absolute value of a money amount. | NULL | [SELECT money_abs(money(-19.99, 'USD'));] |
| money_add | scalar | Adds two money values of the same currency. | NULL | [SELECT money_add(money(10.00, 'USD'), money(5.00, 'USD'));] |
| money_amount | scalar | Extracts the exact numeric amount (DECIMAL(18,3)) from a money value. | NULL | [SELECT money_amount(money(19.99, 'USD'));] |
| money_currency | scalar | Extracts the ISO 4217 currency code from a money value. | NULL | [SELECT money_currency(money(19.99, 'USD'));] |
| money_format | scalar | Formats a money value as a string using the specified style ('symbol', 'code', or 'plain'). | NULL | [SELECT money_format(money(19.99, 'USD'), 'symbol');] |
| money_from_cents | scalar | Creates a money value from an integer amount in the smallest currency unit (e.g. cents) and ISO 4217 currency code. | NULL | [SELECT money_from_cents(1999, 'USD');] |
| money_in_range | scalar | Returns TRUE if the money amount is within the inclusive range [min, max]. | NULL | [SELECT money_in_range(money(15.00, 'USD'), 10.0, 20.0);] |
| money_is_negative | scalar | Returns TRUE if the money amount is less than zero. | NULL | [SELECT money_is_negative(money(-5.00, 'USD'));] |
| money_is_positive | scalar | Returns TRUE if the money amount is greater than zero. | NULL | [SELECT money_is_positive(money(19.99, 'USD'));] |
| money_is_zero | scalar | Returns TRUE if the money amount is exactly zero. | NULL | [SELECT money_is_zero(money(0.00, 'USD'));] |
| money_multiply | scalar | Multiplies a money amount by a scalar factor. | NULL | [SELECT money_multiply(money(10.00, 'USD'), 1.5);] |
| money_same_currency | scalar | Returns TRUE if two money values have the same currency code. | NULL | [SELECT money_same_currency(money(10.00, 'USD'), money(5.00, 'USD'));] |
| money_subtract | scalar | Subtracts the second money value from the first (must be the same currency). | NULL | [SELECT money_subtract(money(10.00, 'USD'), money(3.00, 'USD'));] |
| null_rate | table | Asserts that the fraction of NULL values in a column does not exceed max_null_rate. | NULL | [SELECT * FROM null_rate('orders', 'email', 0.05);] |
| occurrence | table | Asserts that the highest ('max') or lowest ('min') frequency of any single value in a column is between lower_threshold and upper_threshold (NULL = unbounded). | NULL | [SELECT * FROM occurrence('orders', 'customer_id', 'max', NULL, 100);] |
| outlier_tree | table | Identifies statistical outliers in a table using the OutlierTree algorithm, returning an explanation of which conditions make each row an outlier. | NULL | [SELECT * FROM outlier_tree('transactions', 'amount,balance', 'summary');] |
| phonenumber_example | scalar | Returns an example valid phone number for the given 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_example('US');] |
| phonenumber_format | scalar | Formats a phone number string in the specified format: 'E164', 'INTERNATIONAL', 'NATIONAL', or 'RFC3966'. | NULL | [SELECT phonenumber_format('+14155552671', 'NATIONAL');] |
| phonenumber_is_possible | scalar | Returns TRUE if the phone number length is plausible for its region (lighter check than is_valid). | NULL | [SELECT phonenumber_is_possible('+14155552671');] |
| phonenumber_is_valid | scalar | Returns TRUE if the phone number is a valid, dialable number. | NULL | [SELECT phonenumber_is_valid('+14155552671');] |
| phonenumber_is_valid_for_region | scalar | Returns TRUE if the phone number is valid for the specified 2-letter ISO region code (e.g., 'US'). | NULL | [SELECT phonenumber_is_valid_for_region('+14155552671', 'US');] |
| phonenumber_match | scalar | Returns TRUE if two phone number strings refer to the same number. | NULL | [SELECT phonenumber_match('+14155552671', '(415) 555-2671');] |
| phonenumber_parse | scalar | Parses a phone number string and returns a struct with E164, national, international, and RFC3966 formats, plus the region code. | NULL | [SELECT phonenumber_parse('+14155552671');] |
| phonenumber_region | scalar | Returns the 2-letter ISO region code (e.g., 'US', 'DE') for a given phone number. | NULL | [SELECT phonenumber_region('+14155552671');] |
| phonenumber_status | table | Returns the current configuration and status of the phone number module. | NULL | [SELECT * FROM phonenumber_status();] |
| pii_audit_table | table | Returns a row-level audit of PII detected in all string columns of a table. | NULL | [SELECT * FROM pii_audit_table('customers');] |
| pii_config | table | Returns the current configuration settings of the PII detection module. | NULL | [SELECT * FROM pii_config();] |
| pii_contains | scalar | Returns TRUE if the text contains any PII of the specified type (e.g., 'email', 'phone', 'ssn'). | NULL | [SELECT pii_contains('Contact us at [email protected]', 'email');] |
| pii_count | scalar | Returns the total count of PII entities detected in the text. | NULL | [SELECT pii_count('John at [email protected], tel: +1-555-0100');] |
| pii_detect | scalar | Detects all PII entities in a text string and returns them as a list of structs with type, value, start, and end positions. | NULL | [SELECT pii_detect('Contact John at [email protected] or +1-555-123-4567');] |
| pii_detect_batch | scalar | Runs all PII detectors on an array of text strings and returns a combined list of all detected entities per input. | NULL | [SELECT pii_detect_batch(['[email protected]', 'SSN: 123-45-6789']);] |
| pii_detect_credit_cards | scalar | Detects all credit card numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_credit_cards('Pay with 4111111111111111');] |
| pii_detect_emails | scalar | Detects all email addresses in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_emails('Contact [email protected] or [email protected]');] |
| pii_detect_ibans | scalar | Detects all IBAN numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ibans('Bank: DE89370400440532013000');] |
| pii_detect_names | scalar | Detects all person names in the text using NLP and returns them as a list of structs. | NULL | [SELECT pii_detect_names('Signed by John Smith and Jane Doe');] |
| pii_detect_phones | scalar | Detects all phone numbers in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_phones('Call +1-555-0100 or 0800 123 456');] |
| pii_detect_ssns | scalar | Detects all Social Security Numbers (SSNs) in the text and returns them as a list of structs. | NULL | [SELECT pii_detect_ssns('SSN: 123-45-6789');] |
| pii_is_valid_credit_card | scalar | Returns TRUE if the string is a valid credit card number (uses Luhn algorithm). | NULL | [SELECT pii_is_valid_credit_card('4111111111111111');] |
| pii_is_valid_crypto_address | scalar | Returns TRUE if the string is a valid cryptocurrency wallet address (Bitcoin, Ethereum, etc.). | NULL | [SELECT pii_is_valid_crypto_address('1A1zP1eP5QGefi2DMPTfTL5SLmv7Divf');] |
| pii_is_valid_de_tax_id | scalar | Returns TRUE if the string is a valid German tax identification number (Steueridentifikationsnummer). | NULL | [SELECT pii_is_valid_de_tax_id('12345678901');] |
| pii_is_valid_iban | scalar | Returns TRUE if the string is a valid IBAN (International Bank Account Number). | NULL | [SELECT pii_is_valid_iban('DE89370400440532013000');] |
| pii_is_valid_nino | scalar | Returns TRUE if the string is a valid UK National Insurance Number (NINO). | NULL | [SELECT pii_is_valid_nino('AB123456C');] |
| pii_is_valid_ssn | scalar | Returns TRUE if the string is a valid US Social Security Number (SSN). | NULL | [SELECT pii_is_valid_ssn('123-45-6789');] |
| pii_mask | scalar | Masks all detected PII in a text string using the specified strategy ('redact', 'hash', 'partial'). Defaults to 'redact'. | NULL | [SELECT pii_mask('Call me at +1-555-123-4567');, SELECT pii_mask('[email protected]', 'hash');] |
| pii_mask_column | scalar | Masks a value of a specific PII type using the specified strategy ('redact', 'hash', 'partial'). | NULL | [SELECT pii_mask_column('[email protected]', 'email', 'hash');] |
| pii_redact_column | scalar | Detects and redacts all PII in a text value using the specified strategy. Defaults to 'redact'. | NULL | [SELECT pii_redact_column('[email protected]');, SELECT pii_redact_column('[email protected]', 'hash');] |
| pii_scan_table | table | Scans all string columns of a table and returns a summary of detected PII types per column. | NULL | [SELECT * FROM pii_scan_table('customers');] |
| pii_status | table | Returns the current configuration and status of the PII detection module. | NULL | [SELECT * FROM pii_status();] |
| postal_expand_address | scalar | Expands a postal address into all normalized variants using libpostal. | NULL | [SELECT postal_expand_address('123 main st springfield il');] |
| postal_load_data | scalar | Downloads and installs the libpostal data bundle (~500 MB) required for address parsing and expansion. | NULL | [SELECT postal_load_data();] |
| postal_parse_address | scalar | Parses a free-form postal address string into its structural components (house_number, road, city, state, postcode, country). | NULL | [SELECT postal_parse_address('123 Main St, Springfield, IL 62701');] |
| postal_status | table | Returns the current status of the libpostal address parser, including whether it is initialized and the data directory path. | NULL | [SELECT * FROM postal_status();] |
| profile_correlations | table | Computes pairwise Pearson correlation coefficients for numeric columns in a table. | NULL | [SELECT * FROM profile_correlations('orders');, SELECT * FROM profile_correlations('orders', ['amount', 'qty']);] |
| profile_summary | table | Returns a summary profile of all columns in a table, including min, max, null_count, and distinct_count. | NULL | [SELECT * FROM profile_summary('orders');] |
| profile_table | table | Profiles selected columns of a table with detailed statistics (min, max, mean, stddev, null_count, distinct_count, sample rows). Optionally filters columns, sets sample size, or uses exact counts. | NULL | [SELECT * FROM profile_table('orders');, SELECT * FROM profile_table('orders', ['amount', 'status']);] |
| regex_match | table | Asserts that the share of non-NULL values matching a regex pattern is between min_match_rate and max_match_rate. | NULL | [SELECT * FROM regex_match('users', 'email', '^[^@]+@[^@]+$', 0.99);] |
| rel_count_change | table | Asserts that the daily (distinct) count on the reference date deviates from the rolling window average by a relative change within [lower_threshold, upper_threshold]. Defaults: count rows, 7-day window, reference = latest date in the data. | NULL | [SELECT * FROM rel_count_change('orders', 'order_date', NULL, 7, -0.5, 0.5);] |
| rolling_values_in_set | table | Asserts that the share of values contained in the allowed set over the trailing window_days days (ending at the reference date, inclusive) is at least min_rate. | NULL | [SELECT * FROM rolling_values_in_set('orders', 'status', ['completed', 'pending'], 'order_date', 14);] |
| run_checks | table | Runs every check defined in a checks table (columns: check_name, check_type, table_name, column_name, params JSON, lower_threshold, upper_threshold, monitor_only, identifier_column, filter_expr) and returns one uniform result row per check and identifier partition (run_ts, check_name, check_type, table_name, column_name, identifier, value, lower_threshold, upper_threshold, status, message). The checks table must be a regular (non-temporary) table; target tables may be temporary. | NULL | [SELECT * FROM run_checks('dq_checks');, INSERT INTO dq_results SELECT * FROM run_checks('dq_checks');] |
| schema_check | table | Asserts that a table contains all the required column names. | NULL | [SELECT * FROM schema_check('orders', ['id', 'amount', 'status']);] |
| values_in_set | table | Asserts that the share of non-NULL values contained in the allowed value set is at least min_rate. Reports up to 5 sample violations. | NULL | [SELECT * FROM values_in_set('orders', 'status', ['completed', 'pending', 'failed']);] |
| vat | scalar | Parses a VAT number string and returns a struct with country code, normalized number, and validity flags. | NULL | [SELECT vat('DE123456789');] |
| vat_country_name | scalar | Returns the English country name for the VAT number's country prefix (e.g., 'Germany'). | NULL | [SELECT vat_country_name('DE123456789');] |
| vat_exists | scalar | Returns TRUE if the country prefix of the VAT number exists in the supported country list. | NULL | [SELECT vat_exists('DE123456789');] |
| vat_format | scalar | Formats a VAT number using the specified style: 'plain' (digits only) or 'iso' (VAT country prefix + digits). | NULL | [SELECT vat_format('DE123456789', 'iso');] |
| vat_is_eu_member | scalar | Returns TRUE if the VAT number belongs to an EU member state. | NULL | [SELECT vat_is_eu_member('DE123456789');] |
| vat_is_valid | scalar | Returns TRUE if the VAT number has valid syntax and passes the country's check-digit validation where implemented. | NULL | [SELECT vat_is_valid('DE111111125');] |
| vat_is_valid_syntax | scalar | Returns TRUE if the VAT number matches the expected syntax for its country prefix (no network check). | NULL | [SELECT vat_is_valid_syntax('DE123456789');] |
| vat_normalize | scalar | Normalizes a VAT number by removing spaces, dashes, and other formatting characters. | NULL | [SELECT vat_normalize('DE 123 456 789');] |
| vat_split | scalar | Splits a VAT number into a struct with 'country_code' and 'number' fields. | NULL | [SELECT vat_split('DE123456789');] |
| volume | table | Asserts that a table has between min_rows and max_rows rows, returning the count and assertion status. | NULL | [SELECT * FROM volume('orders', 100, 1000000);] |
| zscore | table | Identifies rows where a numeric column value deviates more than threshold standard deviations from the mean. | NULL | [SELECT * FROM zscore('orders', 'amount', 3.0);] |
Overloaded Functions
This extension does not add any function overloads.
Added Types
This extension does not add any types.
Added Settings
| name | description | input_type | scope | aliases | typed_value |
|---|---|---|---|---|---|
| anofox_ner_cache_size | LRU cache size for NER results (0 to disable) | BIGINT | GLOBAL | [] | 10000 |
| anofox_ner_device | OpenVINO device for NER inference: AUTO (default), CPU, GPU, GPU.0, etc. | VARCHAR | GLOBAL | [] | AUTO |
| anofox_ner_model | NER model to use (supported: distilbert-en); must be set before the model is first loaded | VARCHAR | GLOBAL | [] | distilbert-en |
| anofox_pii_deep_validation | Enable deep validation using libphonenumber for phone numbers (default: false) | BOOLEAN | GLOBAL | [] | false |
| anofox_pii_default_mask_strategy | Default masking strategy (REDACT, HASH, PARTIAL, ASTERISK, NONE) | VARCHAR | GLOBAL | [] | redact |
| anofox_pii_enabled_types | Comma-separated list of PII types to detect (empty = all) | VARCHAR | GLOBAL | [] | |
| anofox_pii_min_confidence | Minimum confidence threshold for NER-based PII detection (0.0 - 1.0) | DOUBLE | GLOBAL | [] | 0.5 |
| anofox_tab_email_default_validation | Default validation mode for anofox_tab_email_is_valid (regex, dns, smtp) | VARCHAR | GLOBAL | [] | regex |
| anofox_tab_email_dns_timeout_ms | DNS resolver timeout in milliseconds | BIGINT | GLOBAL | [] | 1000 |
| anofox_tab_email_dns_tries | Number of DNS queries to attempt before failing (1-10) | INTEGER | GLOBAL | [] | 1 |
| anofox_tab_email_regex_pattern | Regular expression used during email regex validation | VARCHAR | GLOBAL | [] | ^[A-Za-z0-9.!#$%&'+/=?^_`{|}~-]+@[A-Za-z0-9-]+(?:.[A-Za-z0-9-]+)$ |
| anofox_tab_email_smtp_connect_timeout_ms | SMTP connect timeout in milliseconds | BIGINT | GLOBAL | [] | 5000 |
| anofox_tab_email_smtp_helo_domain | Domain value used during SMTP EHLO negotiation | VARCHAR | GLOBAL | [] | duckdb.local |
| anofox_tab_email_smtp_mail_from | MAIL FROM address presented during SMTP verification | VARCHAR | GLOBAL | [] | [email protected] |
| anofox_tab_email_smtp_port | SMTP port used when connecting to MX hosts | INTEGER | GLOBAL | [] | 25 |
| anofox_tab_email_smtp_read_timeout_ms | SMTP read/write timeout in milliseconds | BIGINT | GLOBAL | [] | 5000 |
| anofox_tab_phonenumber_default_region | Default region code used when the region hint is NULL | VARCHAR | GLOBAL | [] | US |
| anofox_tab_postal_data_path | Directory storing libpostal assets | VARCHAR | GLOBAL | [] | .duckdb/extensions/libpostal |
| anofox_tab_trace_enabled | Enable anofox tracing output | BOOLEAN | GLOBAL | [] | true |
| anofox_tab_trace_level | Minimum tracing level (trace/debug/info/warn/error/critical/off) | VARCHAR | GLOBAL | [] | info |
| anofox_telemetry_enabled | Enable or disable anonymous usage telemetry | BOOLEAN | GLOBAL | [] | true |
| anofox_telemetry_key | PostHog API key for telemetry | VARCHAR | GLOBAL | [] | phc_t3wwRLtpyEmLHYaZCSszG0MqVr74J6wnCrj9D41zk2t |
| datazoo_banner | Show the DataZoo feedback banner when an extension is loaded in an interactive terminal (at most once a day per extension). | BOOLEAN | GLOBAL | [] | true |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |