--- warning: DO NOT CHANGE THIS MANUALLY, THIS IS GENERATED BY https://github.com/duckdb/community-extensions repository, check README there title: semantic_profile excerpt: | DuckDB Community Extensions Profiles what the values in a table mean rather than what shape they are — semantic type per column, PII and format drift, and cross-column contradictions extension: name: semantic_profile description: Profiles what the values in a table mean rather than what shape they are — semantic type per column, PII and format drift, and cross-column contradictions version: 0.3.0 language: Rust build: cargo license: MIT # wasm: no wasm target (the Rust template's staticlib example is dropped, since # its `mod lib;` shim cannot remap a multi-module crate). # windows_amd64_mingw: the Rust toolchain here targets MSVC, not GNU. excluded_platforms: "wasm_mvp;wasm_eh;wasm_threads;windows_amd64_mingw" requires_toolchains: "rust;python3" maintainers: - patricktrainer repo: github: patricktrainer/duckdb-semantic-profile ref: 5d0a34f1972535ec42b1bada0c55c0b491951699 variegata: 5d0a34f1972535ec42b1bada0c55c0b491951699 docs: hello_world: | -- Judgments come from TypeSafe's System One model, so the extension needs an -- API key. It is read from TYPESAFE_API_KEY, or from a file -- never from -- SQL, which would put it in query logs and shell history. SELECT sem_config('api_key_file', '~/.typesafe-key'); CREATE TABLE orders AS SELECT * FROM (VALUES ('Acme Industrial', 'ops@acme.com', 'US', '94607', '12 kg'), ('Harborline Ltd', 'post@hl.co.uk', 'GB', 'BS1 6QH', '9 kg'), ('Test Company', 'n/a', 'US', 'SW1A 1AA', '26.4') ) t(customer, email, country, postal_code, weight); -- SUMMARIZE finds nothing wrong here: every column is a VARCHAR, nothing is -- null, and the cardinalities are exactly what you would expect. SUMMARIZE orders; -- What each column actually holds, judged from the values rather than the name. SELECT column_name, semantic_type, unit_or_currency FROM sem_columns('orders'); -- weight -> physical_measurement, unit_or_currency = 'mixed' -- ('12 kg' and a bare '26.4' in one column) -- A dry run first: no API calls, so you see the cost before spending it. SELECT total_requests, approx_input_tokens FROM sem_cost('orders', rows := 3); -- The findings, with the offending values attached. A cross-column finding -- names the field it blames and the one it conflicts with. SELECT column_name, probe_id, rows_flagged, examples FROM sem_report('orders', rows := 3); -- email sentinel_used_as_value 1 ['n/a'] -- customer placeholder_or_test_value 1 ['Test Company'] -- postal_code category_product_mismatch, geo_inconsistent, internally_contradictory -- 1 ['postal_code=SW1A 1AA; country=US'] extended_description: | `SUMMARIZE`, `pandas.describe()` and similar tools profile the *shape* of data: types, ranges, quantiles, null and distinct counts. They are structurally blind to what the values mean. This extension asks the other question, using [TypeSafe](https://docs.typesafe.ai)'s System One model (Jev), which returns calibrated typed judgments rather than free text. It finds defects that move no statistic: fake rows that read as ordinary data, values that are not what the column claims, unit and format drift within a column, PII sitting in free text, and **contradictions between columns in the same row** — which a column-at-a-time profiler cannot reach even in principle. ### How it works Four steps. The second is what makes it adapt to a table rather than run a fixed checklist; the fourth is what makes a cross-column finding say what is wrong. 1. **Discover** — one request per column. What does this column actually hold, judged from its values, its neighbours and a few whole rows? The column name is treated as a hint that may be lying. 2. **Select** — one request per column. Code narrows a 20-probe catalog to those that could apply to the discovered semantic type, then asks which are worth running *here*. `sem_probes()` shows the result before you pay for it. 3. **Execute** — one request per row. The state is the whole row and the questions are every selected value probe plus every cross-column check, so the coherence checks ride along in a request already being sent. 4. **Attribute** — one small request per row a cross-column check suspects: which field is most responsible? Checks that back off when made to point at something are dropped, and the rest are grouped by field, so a defect repeated across rows usually reads as one finding. Cost is `2 × columns + rows`, plus the attribution requests, not `columns × rows`. A 20-column table at 500 sampled rows is about 540 requests, plus one per suspect row. `sem_cost()` dry-runs without calling out. ### Reading the output Findings carry calibrated probabilities, not booleans. `threshold` is an argument and re-slicing a report re-reads cached judgments, so it costs nothing. `sem_report` also reports `needs_review`: rows near 0.5, where the model is genuinely uncertain rather than mildly suspicious. Every finding carries the offending values and the rows they came from. ### Functions `sem_profile`, `sem_columns`, `sem_probes`, `sem_values`, `sem_report`, `sem_report_rows`, `sem_row_fields`, `sem_findings`, `sem_cost`, `sem_catalog`; the primitives `ts_ask`, `ts_noul`, `ts_choice`, `ts_score`; and `sem_config`, `sem_settings`, `sem_stats`, `sem_status`, `sem_sql`. ### Notes - Needs a [TypeSafe](https://docs.typesafe.ai) API key, and sends sampled values to that API. `sem_cost()` says how much will go before any of it does. - Responses are cached to disk, content-addressed, so re-runs are free and deterministic. That cache holds the sampled values in plaintext. - The macro layer installs automatically only on an in-memory database, where nothing persists. Profile a database file by attaching it `READ_ONLY`. extension_star_count: 0 extension_star_count_pretty: 0 extension_download_count: 851 extension_download_count_pretty: 851 image: '/images/community_extensions/social_preview/preview_community_extension_semantic_profile.png' layout: community_extension_doc --- ### Compatibility Matrix This table covers all DuckDB releases that have not reached [end-of-life](https://duckdb.org/release_calendar), 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 ```sql INSTALL {{ page.extension.name }} FROM community; LOAD {{ page.extension.name }}; ``` {% if page.docs.hello_world %} ### Example ```sql {{ page.docs.hello_world }}``` {% endif %} {% if page.docs.extended_description %} ### About {{ page.extension.name }} {{ page.docs.extended_description }} {% endif %} ### Added Functions
| function_name | function_type | description | comment | examples | |-----------------|---------------|-------------|---------|----------| | sem_config | scalar | NULL | NULL | | | sem_reset_stats | scalar | NULL | NULL | | | sem_settings | scalar | NULL | NULL | | | sem_sql | scalar | NULL | NULL | | | sem_stats | scalar | NULL | NULL | | | sem_status | scalar | NULL | NULL | | | ts_ask | scalar | NULL | NULL | | ### 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 | |----------------|---------------------------------------------------------|------------|--------|---------|-------------| | temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |