Multi-modal product similarity search for manufacturing supply chain planning. Find similar materials by BOM component overlap (Jaccard), graph-structural similarity (Weisfeiler-Lehman kernel), text embeddings, and transactional patterns, with predecessor detection and cold-start analog matching for demand forecasting.
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_similarity FROM community;
LOAD anofox_similarity;
Example
-- Components (BOM) for each product
CREATE TABLE bom_items (parent_id VARCHAR, child_id VARCHAR);
INSERT INTO bom_items VALUES
('PUMP-A', 'SEAL-001'), ('PUMP-A', 'BEARING-X'), ('PUMP-A', 'MOTOR-M1'),
('PUMP-B', 'SEAL-001'), ('PUMP-B', 'BEARING-X'), ('PUMP-B', 'MOTOR-M1'), ('PUMP-B', 'GASKET-Y'),
('PUMP-C', 'FILTER-F1'), ('PUMP-C', 'VALVE-V2');
-- Find the 3 materials most similar to PUMP-A by shared components
SELECT material_id, similarity, shared_components, total_components
FROM find_similar_materials_jaccard('PUMP-A', 3, bom_table := 'bom_items')
ORDER BY similarity DESC;
-- PUMP-B 0.75 3 4 (high overlap)
-- PUMP-C 0.0 0 5 (disjoint)
-- Structural similarity that also weighs BOM topology, not just component sets
SELECT wl_kernel_similarity('PUMP-A', 'PUMP-B', iterations := 3, bom_table := 'bom_items');
About anofox_similarity
anofox_similarity helps manufacturers answer "which existing product is this
new one most like?" directly against ERP master data (SAP, Microsoft
Dynamics 365, or a plain bom_items table), which is the key input for
cold-start demand forecasting, predecessor detection, and analog-based
planning for products with no sales history yet.
Similarity signals compose across several table functions:
find_similar_materials_jaccardranks materials by BOM component overlap.wl_kernel_similarity/find_similar_materials_wl_kerneluse a Weisfeiler-Lehman graph kernel to capture BOM topology, not just which components are shared but how they're structurally arranged.infer_predecessorsdetects predecessor/successor relationships from structural similarity plus temporal anti-correlation in goods-movement history (an old part's usage declining as a new part's usage rises).cold_start_analogsfinds established products to seed a forecast for a brand-new material with no history at all.compute_fused_embeddingscombines structural, transactional, and text embedding signals into one fusable vector, indexable with DuckDB'svssextension via the bundled HNSW index helpers.
SAP (MARA/MAKT/MAST/STKO/STPO) and Dynamics 365 transformation macros
(sap_to_bom_items, sap_to_materials_with_desc, …) normalize common
ERP exports into the BOM/material schema the rest of the extension expects,
so integration is mostly SELECT * FROM sap_to_bom_items(...).
All table functions accept a fully qualified read-only DuckDB database and
degrade gracefully when optional companion extensions (anofox_forecast
for time-series features, duckpgq for property-graph BOM queries) are
not loaded.
The extension collects anonymous usage telemetry (an envelope-only
extension_loaded event; never table names, keys, or SQL). Disable with
SET anofox_telemetry_enabled = false or DATAZOO_DISABLE_TELEMETRY=1;
see TELEMETRY.md in the repository.
Added Functions
| function_name | function_type | description | comment | examples |
|---|---|---|---|---|
| aggregate_material_components | table_macro | Groups a BOM edge list into one row per parent material with its child components as a sorted list — the shape the Jaccard and cold-start functions expect. 'material_filter' restricts the result to the listed parents; NULL means all of them. | NULL | [SELECT * FROM aggregate_material_components(bom_table := 'bom_items')] |
| bom_common_components | table_macro | Components that appear in the BOMs of both given materials – the overlap used to judge how interchangeable two assemblies are. | NULL | [SELECT * FROM bom_common_components(material_1 := 'PUMP-A', material_2 := 'PUMP-B')] |
| bom_dfs_neighborhood | table_macro | All descendant components of a root material within a depth limit, as a reusable depth-first BOM traversal over a (parent_id, child_id) edge table. | NULL | [SELECT * FROM bom_dfs_neighborhood(root_material_id := 'PUMP-A', max_depth := 3)] |
| bom_explosion_1level | table_macro | Direct children of one parent material: returns (parent_material_id, child_material_id, quantity_per) for the immediate level only. 'header_table' and 'component_table' name the tables to read, so the macro works on any schema matching the universal BOM shape. | NULL | [SELECT * FROM bom_explosion_1level(query_material_id := 'PUMP-A')] |
| bom_explosion_multilevel | table_macro | Full multi-level BOM explosion under one parent, to 'max_depth' levels. Uses UNION rather than UNION ALL so a shared subassembly reached by many paths is expanded once – no exponential blow-up on diamond BOMs – and the depth gate is clamped to a hard ceiling of 64 so a cyclic BOM cannot iterate unbounded. | NULL | [SELECT * FROM bom_explosion_multilevel(query_material_id := 'PUMP-A', max_depth := 5)] |
| bom_to_items | table_macro | Flattens the universal bom_header + bom_component pair into the flat (parent_id, child_id, quantity) bom_items shape that the similarity algorithms expect. | NULL | [SELECT * FROM bom_to_items(header_table := 'bom_header', component_table := 'bom_component')] |
| bom_where_used | table_macro | Reverse BOM lookup: every parent material that consumes a given child, with the quantity each one uses. | NULL | [SELECT * FROM bom_where_used(child_material_id := 'SEAL-001')] |
| check_anofox_forecast_available | macro | Returns true when the anofox_forecast extension is loaded, so a caller can choose the richer time-series feature path over the built-in one. anofox_forecast is an optional soft dependency and is never auto-installed. | NULL | [SELECT check_anofox_forecast_available()] |
| check_duckpgq_available | macro | Returns true when the DuckPGQ extension is loaded, so a caller can choose the graph-based BOM traversal path over the SQL fallback. DuckPGQ is an optional soft dependency and is never auto-installed. | NULL | [SELECT check_duckpgq_available()] |
| check_statistics_freshness | table | Returns a single-row summary of the transactional_embedding_statistics table including stat_count, max_samples, current_version, last_updated, and is_fresh. is_fresh is TRUE when >= 30 statistics rows exist and last_updated is within 7 days. | NULL | [SELECT * FROM check_statistics_freshness();] |
| cold_start_analogs | table | Finds structural analogs for a cold-start material (one with little or no demand history). Returns established materials that are structurally similar and have at least min_history_months of goods movement data, enabling surrogate forecasting. | NULL | [SELECT * FROM cold_start_analogs('NEW-PART', 5);, SELECT * FROM cold_start_analogs('NEW-PART', 10, min_history_months := 12, min_similarity := 0.4);] |
| compute_domain_specific_statistics | table_macro | Computes the advanced domain-specific ERP features — movement type mix, day-of-week pattern and lifecycle position — used alongside the core time-series features. | NULL | [SELECT * FROM compute_domain_specific_statistics(movements_table := 'goods_movements')] |
| compute_fused_embeddings | table_macro | Combines the structural, textual and transactional embedding of every material in material_embeddings into one fused vector, returning (material_id, combined_embedding, fusion_weights). Weights are normalised to sum to 1, matching what fuse_embeddings applies internally, and a modality's vector is only required when its weight is above zero. | NULL | [SELECT * FROM compute_fused_embeddings(weights_structural := 0.6, weights_textual := 0.4)] |
| compute_jaccard_embeddings | table | Computes and RETURNS 128-dimensional MinHash embeddings (one row per material/seed) from BOM component sets; it does not write to material_embeddings — aggregate and upsert the result yourself. Each material's embedding is derived from its set of direct child components. Requires bom_table with columns parent_id and child_id. | NULL | [SELECT * FROM compute_jaccard_embeddings();, SELECT * FROM compute_jaccard_embeddings(bom_table := 'sap_stpo');] |
| compute_textual_embeddings | table_macro | Generates a semantic embedding per material from its description text, returning (material_id, textual_embedding). Reads descriptions from 'makt_table' in the given 'language' and embeds them with 'provider'; 'provider_config' passes provider-specific settings through to the backend. | NULL | [SELECT * FROM compute_textual_embeddings(makt_table := 'sap_makt', language := 'EN')] |
| compute_transactional_embeddings | table | Computes 128-dimensional transactional embeddings from goods movement data and upserts them into material_embeddings. Features include demand velocity, seasonality, frequency, and quantity statistics normalized against transactional_embedding_statistics. Supports batched computation via batch_size and batch_offset. | NULL | [SELECT * FROM compute_transactional_embeddings();, SELECT * FROM compute_transactional_embeddings(movements_table := 'sap_mseg', time_window_days := 365);, SELECT * FROM compute_transactional_embeddings(batch_size := 1000, batch_offset := 0);] |
| create_bom_property_graph | table_macro | Summarises the BOM graph for one source system as (graph_name, source, num_nodes, num_edges), counting distinct parent materials in bom_header and the components beneath them. | NULL | [SELECT * FROM create_bom_property_graph(source_system := 'SAP')] |
| dynamics365_to_bom_component | table_macro | Maps Dynamics 365 BOM lines into the universal bom_component shape (singular) — note this is not the flat bom_items form. Reads BOMId, LineNum, ItemId, Quantity and ScrapPercent. | NULL | [SELECT * FROM dynamics365_to_bom_component(bom_lines := 'BOM')] |
| dynamics365_to_bom_header | table_macro | Maps Dynamics 365 BOMTable joined to BOMVersion into the universal bom_header shape, keeping rows with Status = 0. Reads BOMTable.(BOMId, ItemId, Status) and BOMVersion.(BOMId, VersionId, ActivationDate, ApprovedStatus). | NULL | [SELECT * FROM dynamics365_to_bom_header(bom_table := 'BOMTable', bom_version := 'BOMVersion')] |
| dynamics365_to_materials | table_macro | Maps a Dynamics 365 InventTable to the universal materials shape. Reads ItemId, ItemName and ItemType (0/1/2) and returns the full materials column set. Projects a shape; it does not write an output table. | NULL | [SELECT * FROM dynamics365_to_materials(invent_table := 'InventTable')] |
| embed_text | macro | Embeds a single piece of text with the default local backend — shorthand for embedding_backend(description, 'gemma-local', ''). | NULL | [SELECT embed_text('centrifugal pump seal kit')] |
| embedding_backend | scalar | Generates a 384-dimensional text embedding (FLOAT[384]) for the given text. provider selects the backend and provider_config is a JSON string of provider-specific options (api_key, endpoint). NOTE: unless the extension is built with a local OpenVINO model or a real API backend is configured, all providers return a deterministic, hash-based PLACEHOLDER embedding (useful for wiring and tests, not semantic search). | NULL | [SELECT embedding_backend('diesel pump', 'gemma-local', '{}');, SELECT embedding_backend('valve seat', 'gemini-api', '{"api_key": "…"}');] |
| extract_material_descriptions | table_macro | Extracts material descriptions from a SAP MAKT table for one language, returning (material_id, description). | NULL | [SELECT * FROM extract_material_descriptions(makt_table := 'sap_makt', language := 'EN')] |
| extract_ts_features | table_macro | Shared time-series feature extraction over a goods-movement table, computed once as a CTE so callers do not each re-run the expensive per-material feature computation. 'batch_size' and 'batch_offset' page through large material sets; NULL batch_size means all of them. | NULL | [SELECT * FROM extract_ts_features(movements_table := 'goods_movements', time_window_days := 365)] |
| filter_recent_movements | table_macro | Filters a goods-movement table to a recent window and a minimum quantity. The window is anchored on the most recent movement date IN THE DATA rather than wall-clock today, which works without the ICU extension, is deterministic for tests, and is what planners want on historical ERP extracts where 'today' may be years after the last movement. | NULL | [SELECT * FROM filter_recent_movements(movements_table := 'goods_movements', time_window_days := 180)] |
| find_similar_materials_jaccard | table | Finds the k most structurally similar materials to a query material using exact Jaccard similarity on BOM component sets. Returns material_id, similarity, shared_components, and total_components. Filtered by min_similarity threshold. | NULL | [SELECT * FROM find_similar_materials_jaccard('MAT-001', 10);, SELECT * FROM find_similar_materials_jaccard('MAT-001', 20, min_similarity := 0.3);] |
| find_similar_materials_wl_kernel | table | Finds the k most structurally similar materials using the Weisfeiler-Lehman graph kernel, which captures multi-hop BOM structure beyond direct component overlap. iterations controls the number of WL refinement rounds (default 3). | NULL | [SELECT * FROM find_similar_materials_wl_kernel('MAT-001', 10);, SELECT * FROM find_similar_materials_wl_kernel('MAT-001', 10, iterations := 3, min_similarity := 0.2);] |
| fuse_embeddings | scalar | Fuses structural (FLOAT[256]), textual (FLOAT[384]), and transactional (FLOAT[128]) embeddings into a single 768-dimensional combined embedding using weighted concatenation. Each component is scaled by sqrt(weight). Weights do not need to sum to 1.0. | NULL | [SELECT fuse_embeddings(se, te, xe, {'structural':0.5,'textual':0.5,'transactional':0.0}) FROM material_embeddings;] |
| hnsw_compact_index | pragma | NULL | NULL | |
| hnsw_index_scan | table | NULL | NULL | |
| infer_predecessors | table | Identifies predecessor (superseded) materials for a given successor by combining BOM structural similarity with anti-correlated consumption patterns. Returns predecessor candidates ranked by confidence score along with correlation, similarity, and temporal overlap data. Useful for lifecycle transition planning and inventory run-down. | NULL | [SELECT * FROM infer_predecessors('NEW-PART');, SELECT * FROM infer_predecessors('NEW-PART', lookback_months := 24, min_similarity := 0.5, min_confidence := 0.6, lag_weeks := 4);] |
| items_to_bom | table_macro | The inverse of bom_to_items: expands a flat bom_items edge list back into the universal bom_header and bom_component shapes, tagging rows with 'source_system'. | NULL | [SELECT * FROM items_to_bom(items_table := 'bom_items', source_system := 'MIGRATED')] |
| jaccard_similarity | scalar | Computes the Jaccard similarity coefficient between two lists treated as sets. Returns a value in [0.0, 1.0] equal to |intersection| / |union|. Duplicates within each list are deduplicated before comparison. Returns NULL if either argument is NULL; returns 0.0 if both lists are empty. | NULL | [SELECT jaccard_similarity(['A','B','C'], ['B','C','D']); – 0.5, SELECT jaccard_similarity([1,2,3,4], [3,4,5,6]); – 0.3333] |
| pragma_hnsw_index_info | table | NULL | NULL | |
| recompute_embedding_statistics | table_macro | Recomputes the transactional embedding statistics from current movement data, returning the per-feature normalisation statistics the embedding pipeline uses. 'time_window_days' is the historical window and 'min_observations' the minimum data points a material needs to qualify. | NULL | [SELECT * FROM recompute_embedding_statistics(time_window_days := 365, min_observations := 3)] |
| sap_to_bom_items | table_macro | Converts the SAP MAST/STKO/STPO trio into the universal BOM shape. 'bom_alternative' picks the alternative BOM, 'reference_date' selects the version valid on that date, and 'bom_usage' filters by usage (NULL means any). Quantity passes through the source MENGE column's type, which is DECIMAL in SAP – cast if you need DOUBLE. | NULL | [SELECT * FROM sap_to_bom_items('MAST', 'STKO', 'STPO', bom_alternative := '01')] |
| sap_to_materials | table_macro | Extracts materials from a SAP MARA table into the universal materials shape. Requires MARA columns matnr, mtart, matkl, ersda and lvorm; rows flagged deleted via lvorm are excluded. The description column is always empty here – use sap_to_materials_with_desc to populate it from MAKT. | NULL | [SELECT * FROM sap_to_materials('MARA')] |
| sap_to_materials_with_desc | table_macro | Like sap_to_materials, but joins MAKT to populate the description column in the requested 'language' (SAP single-character language key, e.g. 'E'). | NULL | [SELECT * FROM sap_to_materials_with_desc('MARA', 'MAKT', language := 'E')] |
| vss_join | table_macro | NULL | NULL | |
| vss_match | table_macro | NULL | NULL | |
| wl_fingerprint | table_macro | Depth-expanded component fingerprint of one material, as component -> number of distinct depths at which it is reachable. Written as a non-recursive bounded expansion on purpose: DuckDB cannot decorrelate a recursive CTE inside a correlated scalar subquery, which is exactly how wl_kernel_similarity uses it. Iterations 1-5 are exact; higher values are capped at depth 5. | NULL | [SELECT * FROM wl_fingerprint('PUMP-A', 3, 'bom_items')] |
| wl_kernel_similarity | macro | Weisfeiler-Lehman kernel similarity between two materials' BOM structures, comparing their depth-expanded component fingerprints. Returns a score in [0, 1] where 1 means structurally identical. Iterations 1-5 are exact; higher values are capped at depth 5. | NULL | [SELECT wl_kernel_similarity('PUMP-A', 'PUMP-B')] |
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_telemetry_enabled | Enable or disable anonymous usage telemetry | BOOLEAN | GLOBAL | [] | true |
| anofox_telemetry_key | PostHog API key for telemetry | VARCHAR | GLOBAL | [] | |
| 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 |
| hnsw_ef_search | experimental: override the ef_search parameter when scanning HNSW indexes | BIGINT | GLOBAL | [] | NULL |
| hnsw_enable_experimental_persistence | experimental: enable creating HNSW indexes in persistent databases | BOOLEAN | GLOBAL | [] | false |
| hnsw_prefilter | experimental: apply static table filters before searching HNSW indexes | BOOLEAN | GLOBAL | [] | true |
| temp_directory | Set the directory to which temporary files are written. | VARCHAR | GLOBAL | [] | post.db.tmp |