Ontology Extraction
Description
This workflow ingests a table containing business entities and their relationship descriptions, performs data transformation by splitting and pairing comma-separated related entities with semicolon/newline-separated relationship sentences, and explodes these pairs into individual rows while preserving original metadata columns. It includes validation steps to handle mismatched list lengths and missing values, followed by persistence of the paired data into a new workspace table. Subsequent filtering removes null, blank, and self-referential rows to produce a refined dataset of business relationships, enabling accurate data lineage and dependency analysis. The workflow concludes with previews of intermediate and final tables for validation and orchestration monitoring.
Details
A business glossary tells you what a term means, but it does not tell you how terms connect. This recipe parses dbt manifest.json to extract models, sources, and join-key relationships, then uses AI to identify business entities and infer meaningful ontology relationships between them, such as "Customer Order identified by CustomerID" or "Order belongs to Customer." The output produces governed, domain-classified term pairs that are ready to be onboarded into the business glossary as actual ontology relationships rather than isolated definitions.
Business value
- Converts technical dbt metadata into governed business entities
- Automatically discovers semantic relationships between entities
- Classifies every entity into the correct governance domain
- Produces ontology-ready output for term and relationship creation
- Removes manual effort in ontology and glossary onboarding
Who benefits
- Data Governance Teams
- Business Glossary Stewards
- Ontology and Knowledge Graph Owners
- Analytics Engineering Teams
- Chief Data Office
What you receive
How the recipe works (guided flow)
Step 1 — Manifest Metadata Extraction
Parses the dbt manifest.json nodes and sources into a normalized catalog of models and sources with lineage, SQL logic, and column evidence.
Step 2 — Business Entity Identification
Uses AI to normalize each technical entity name into a meaningful Business Entity by removing technical prefixes and formatting.
Step 3 — Business Entity Projection
Publishes a clean, reviewable view of the manifest metadata enriched with the derived Business Entity.
Step 4 — Related Business Entity Discovery
Uses AI to identify all business concepts (entities, identifiers, attributes, measures, statuses) associated with each Business Entity using SQL joins, foreign keys, and column evidence.
Step 5 — Related Concept Projection
Combines the original metadata, the Business Entity, and the discovered Related Business Entity concepts into a single working dataset.
Step 6 — Business Relationship Discovery
Uses AI to generate a semantic ontology relationship (such as "has," "identified by," "belongs to") between the Business Entity and each related concept.
Step 7 — Ontology Snapshot
Publishes the fully enriched dataset with all generated business metadata fields for review before pairing.
Step 8 — Relationship Pair Expansion via SQL
Explodes the comma-separated related concepts and semicolon-separated relationship sentences into aligned, paired rows using recursive SQL.
Step 9 — Relationship Pair Expansion via Python
Provides a Python-based equivalent expansion, pairing related concepts with their relationship sentences row by row.
Step 10 — Relationship Type Extraction
Uses AI to extract only the ontology relationship predicate (e.g., "identified by," "belongs to") from each relationship sentence.
Step 11 — Relationship Pair Review
Displays all rows and columns of the paired relationship dataset for validation.
Step 12 — Final Business Relationship Filtering
Removes null, blank, and self-referential rows to produce a clean dataset of valid business entity relationships.
Step 13 — Add Governance Domain Catalog Context
Retrieves approved governance domains and appends the domain list and cleaned domain descriptions to every relationship row.
Step 14 — Refresh Domain Catalog Context
Rebuilds and re-attaches the governance domain list to ensure classification input stays aligned with the approved domain catalog.
Step 15 — Recommend Domain for Term 1
Uses AI to recommend the single most appropriate governance domain for the Business Entity based on domain descriptions.
Step 16 — Map Global Domain ID for Term 1
Resolves the recommended domain name for Term 1 into its authoritative global domain ID.
Step 17 — Recommend Domain for Term 2
Uses AI to recommend the single most appropriate governance domain for the Related Business Entity Item.
Step 18 — Map Global Domain ID for Term 2
Resolves the recommended domain name for Term 2 into its authoritative global domain ID.
Step 19 — Creation of Ontologies
Builds the final curated output table with Term 1, Term 2, their recommended domains, domain IDs, and the relationship type, ready for governed term and relationship creation.
Sample insights (illustrative)
| Insight Category | What the recipe discovered | Business Impact |
|---|---|---|
| Entity relationship | Customer Order identified by CustomerID | Establishes a governed identifier relationship for ontology onboarding. |
| Cross-domain relationship | Person Details belongs to Privacy domain via PhoneNumber | Flags sensitive attributes for privacy governance review. |
| Status attribute | Customer Order has Order Status | Surfaces operational attributes for lifecycle and fulfillment tracking. |
Make sure the following ingredients are available in your workspace:
- Connected datasets are crawled and profiled
- Columns have distinct counts and top values generated
Don't have a manifest.json handy? Download a sample dbt manifest file here and run this recipe end to end: Download sample manifest.json