Join Discovery Without Declared Keys

The Problem: Tables With No Keys

A harmonized lakehouse rarely declares its relationships. A Databricks gold layer, a ClickHouse warehouse and most BigQuery datasets hold well-named tables and no FOREIGN KEY constraints at all. People who work with the data know that orders.customer_id points at customers. The catalog does not say so.

For Text-to-SQL that gap matters twice. GraphRAG builds its join graph from keys, so without them it has no edges and no join paths to hand the model. OBQC checks joins and fan-traps against the relationships in the ontology, and without relationships it cannot tell a lookup from a join that multiplies rows. The model is left to guess, and a guessed join that runs is the kind of mistake nobody notices.

Three Ways In

The three are not equivalent. Inference is a good guess that costs nothing. An ontology is your statement of how the tables relate, plus the business meaning on top. Declared constraints are the plainest form of the truth, and every tool that reads the catalog benefits from them, not only this one.

1. Joins Inferred From Column Names

When the schema is discovered, OrionBelt® Analytics looks at every column that is not already a declared foreign key or a primary key and asks whether its name points at another table. Two kinds of match are tried, in this order:

• A table name inside the column name, followed by id, _id, sk, _sk or nothing: supplier_customer_id or customerid find customers. Singular and plural forms are both tried (categories matches category_id), the longest table name wins, and table names under four characters are skipped because they match too much.
• Standard key patterns: customer_id, id_customer, fk_customer, customer_fk, customer_sk, and TPC-DS style prefixed surrogate keys such as ss_customer_sk.

Each inferred relationship gets a confidence. It rises when the column's type matches the target table's primary key, when the name matches the table exactly rather than through a singular or plural form, and when the column ends in id. The result is high, medium or low.

Where inferred joins go

• The generated ontology writes every inferred relationship as an owl:ObjectProperty with its join annotations, plus oba:isInferredRelationship, oba:inferenceConfidence and oba:inferencePattern. OBQC reads them like any other relationship, so joins and fan-traps are checked against them.
• The GraphRAG join graph takes only the high- and medium-confidence ones. Each join on a path carries "source": "inferred" and its confidence, and graphrag_find_join_path adds an inferred_joins_note asking the model to check the columns before relying on the path, unless the join has been confirmed against the data.

A key the database declares always wins over an inferred one between the same two tables. Inference fills gaps. It never overrides the catalog.

2. Your Own Ontology

When the column names are too cryptic for inference, or when you already maintain a domain model, you can load your own ontology with load_my_ontology. It accepts a Turtle file dropped into the chat (passed as ontology_content), or reads the newest .ttl file from an import folder on the server, which suits a shared, curated ontology.

The activation check

OBQC and join discovery read tables, columns and joins through oba: annotations and nothing else. So an uploaded ontology replaces the active one only if it maps to the database with them, namespace https://ralforion.com/ns/oba#:

The test is the one OBQC applies to itself: at least one mapped table, and at least one table with mapped columns. The response always says how it went, with activated and an oba_requirements block that counts the mapped tables, columns and joins and lists the annotations expected.

An ontology that fails the check is not thrown away. It is still loaded into the RDF store and can be queried with SPARQL. It just does not take over: query validation and join paths keep using the ontology that was active before, and the response tells you what to add.

Conformance to the OBA SHACL shapes is reported too, as advice rather than a gate. The shapes describe a generated ontology in full, so a hand-written one that also models business classes with no table behind them can fail them and still work perfectly well for OBQC.

Scope and precedence

• Your session only. An activated ontology's relationships extend join discovery for the session that loaded it, as a copy of the shared graph. Colleagues on the same database keep theirs, and the shared graph is untouched.
• The database is never changed. Nothing is written to it. The ontology lives in the server's workspace and RDF store.
• The most recently activated ontology wins. Running generate_ontology or apply_semantic_names after an upload replaces it with the generated one, so load your file again if you want it back.
• Declared keys still outrank it. Between the same two tables, a key from the catalog beats a relationship from your ontology in the join graph.

3. Informational Constraints in the Catalog

Several lakehouse and warehouse platforms let you declare PRIMARY KEY and FOREIGN KEY constraints that they record but do not enforce. Databricks Unity Catalog and Snowflake both work this way. Declaring them is a one-off DDL statement per table. No data is validated, rewritten or moved.

-- Databricks Unity Catalog: recorded, not enforced
ALTER TABLE gold.customers ADD CONSTRAINT customers_pk PRIMARY KEY (customer_id);
ALTER TABLE gold.orders ADD CONSTRAINT orders_customer_fk
  FOREIGN KEY (customer_id) REFERENCES gold.customers (customer_id);

Where the OrionBelt® Analytics driver reads the catalog's constraints, as it does for Databricks and Snowflake, these become ordinary declared keys. They feed OBQC and GraphRAG alike, and they win over anything inferred. The platform's own rules apply: on Databricks, for example, primary-key columns must be NOT NULL and the tables must live in Unity Catalog.

Not every driver reads constraints today. The BigQuery and Dremio connections report no keys, so on those, inference and your own ontology are the way in. Run discover_schema once after declaring keys and check that it reports them before you rely on it.

Checking a Join Against the Data

A join inferred from a column name, or written into an uploaded ontology, is a claim about the data. validate_relationship tests it. Give it the table and the key column, and the referenced table if the column points at more than one. It works the same for declared, inferred and uploaded relationships, and refuses one the active ontology does not state.

It runs two read-only queries: how many non-null values of the key column exist in the referenced column, and whether the referenced column is unique. Both tables are scanned. The verdict:

The verdict does not stay in the conversation. It is written onto the relationship in the active ontology as a new version, with oba:validationStatus, oba:validationMatchRatio, oba:validationCheckedRows and oba:validationCheckedAt; it goes into the RDF store; and it is kept in the workspace, so a regenerated ontology gets it back. From then on graphrag_find_join_path shows each join's verdict. A confirmed inferred join loses its "check this" note, and a refuted, partial or non-unique one carries a warning, so the model stops relying on it.

The queries are built as syntax trees, with table and column names emitted as quoted identifiers in each engine's own syntax. A name taken from an uploaded ontology cannot turn into extra SQL.

Recommended Setup

Workflow With Your Own Ontology

1. Start. Download the generated ontology with download_artifact (type ontology). It already carries every oba: mapping. Or start from one you maintain.
2. Edit. Add the relationships the database does not declare, rename classes and properties to business terms, and write descriptions. OrionBelt® Ontology Builder does this in the browser; Protégé or any OWL editor works too.
3. Upload. Drop the .ttl file into the chat, or put it in the import folder.
4. Activate. load_my_ontology checks the mapping and either activates the ontology or lists what is missing.
5. Ask. Join paths now include your relationships, and OBQC validates every query against your model, fan-traps included.

The smallest useful upload maps two tables, the columns the queries touch, and the join between them:

@prefix oba:  <https://ralforion.com/ns/oba#> .
@prefix owl:  <http://www.w3.org/2002/07/owl#> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix xsd:  <http://www.w3.org/2001/XMLSchema#> .
@prefix :     <http://example.com/sales#> .

:Customer a owl:Class ;
    rdfs:label "Customer" ;
    oba:tableName "customers" ;
    oba:schemaName "gold" .

:Order a owl:Class ;
    rdfs:label "Order" ;
    oba:tableName "orders" ;
    oba:schemaName "gold" .

:order_net_amt a owl:DatatypeProperty ;
    rdfs:label "Net revenue" ;
    rdfs:domain :Order ;
    rdfs:range xsd:decimal ;
    oba:tableName "orders" ;
    oba:columnName "net_amt" .

:order_customer_id a owl:DatatypeProperty ;
    rdfs:domain :Order ;
    oba:tableName "orders" ;
    oba:columnName "customer_id" .

:customer_customer_id a owl:DatatypeProperty ;
    rdfs:domain :Customer ;
    oba:tableName "customers" ;
    oba:columnName "customer_id" .

:placedBy a owl:ObjectProperty ;
    rdfs:label "placed by" ;
    rdfs:domain :Order ;
    rdfs:range :Customer ;
    oba:foreignKeyColumn "customer_id" ;
    oba:referencedTable "customers" ;
    oba:referencedColumn "customer_id" ;
    oba:relationshipType "many_to_one" .

The full vocabulary, with SQL types, primary-key flags, join conditions and the business annotations, is documented at the oba: namespace. The machine-readable files are oba.ttl, the shapes in oba-shacl.ttl, and a complete annotated example in oba-example.ttl.

Related OrionBelt® Concepts

• OrionBelt® Analytics: the documentation page, with the tools, configuration and deployment.
• GraphRAG Schema Discovery: how vector search finds the tables and the join graph connects them.
• OBQC: Ontology-Based Query Check: what the relationships are checked for, fan-traps first.
• The oba: Namespace: the annotation vocabulary an ontology needs to map to a database.

Frequently Asked Questions