An ontology can describe what a customer, contract, or payment means. It cannot, by itself, prove which columns should be joined.

Business ontology is getting a lot of attention in enterprise AI. That is a welcome development. Models need more than table names: they need definitions of customers, contracts, revenue, and the rules behind those terms.

But there is a problem that tends to appear one layer below the ontology.

You can define every business entity correctly and still generate the wrong SQL.

The reason is surprisingly mundane: the database does not always tell you how its tables connect.

Let's work through a realistic example.

The question is simple. The join path isn't.

A finance manager asks:

How much have customers paid against their active contracts this quarter?

The business concepts are reasonably clear:

  • A customer is the legal entity buying a service.
  • An active contract is a contract that meets the organization's approved status and date rules.
  • A payment is cash received and allocated to an invoice.

Now imagine the underlying data lives in four tables:

crm_customers customer_id customer_name sales_contracts contract_id customer_ref contract_status billing_invoices invoice_id agreement_no invoice_amount cash_allocations allocation_id invoice_ref received_amount received_at

The intended business route is:

Customer → Contract → Invoice → Cash Allocation

But the physical columns don't share a consistent naming convention. customer_ref points to customer_id; agreement_no contains contract identifiers; invoice_ref points to invoice_id.

And suppose none of those relationships is declared as a database foreign key.

An ontology might tell the AI that contracts belong to customers and payments settle invoices. It still needs evidence that billing_invoices.agreement_no is the right physical connection to sales_contracts.contract_id.

Without that connection, a text-to-SQL system may omit the invoice table, guess a join, or return no answer. A syntactically valid query does not resolve the uncertainty.

Why schema inspection alone is not enough

A common approach is to inspect database metadata:

-- PostgreSQL: inspect declared foreign keys SELECT tc.table_name, kcu.column_name, ccu.table_name AS referenced_table, ccu.column_name AS referenced_column FROM information_schema.table_constraints tc JOIN information_schema.key_column_usage kcu ON tc.constraint_name = kcu.constraint_name AND tc.constraint_schema = kcu.constraint_schema JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name AND tc.constraint_schema = ccu.constraint_schema WHERE tc.constraint_type = 'FOREIGN KEY';

This is useful when constraints exist. In legacy databases, imported ERP data, and analytical warehouses, they may not.

That does not mean the relationships are absent. It means they are not explicitly represented in the catalog.

Three common complications follow:

  1. Different names, same identifier. agreement_no may hold values from contract_id.
  2. A hidden intermediate table. Customer and payment may connect only through contract and invoice.
  3. Similar-looking columns, different meanings. Two fields named customer_id may refer to different identifier domains.

The right response is not to ask an LLM to make a more confident guess. It is to collect evidence.

Step 1: Find candidate relationships

Start with declared constraints, if available. Then inspect additional signals:

  • Column types and compatible formats
  • Column names and metadata descriptions
  • Distinct-value counts and uniqueness
  • Null rates
  • Value inclusion and overlap
  • Known query patterns and approved documentation

For example, compare contract IDs with invoice agreement numbers:

-- PostgreSQL example; assumes compatible identifier types. WITH invoice_keys AS ( SELECT DISTINCT agreement_no AS id FROM billing_invoices WHERE agreement_no IS NOT NULL ), contract_keys AS ( SELECT DISTINCT contract_id AS id FROM sales_contracts WHERE contract_id IS NOT NULL ) SELECT COUNT(*) AS distinct_invoice_keys, COUNT(c.id) AS matching_keys, ROUND( COUNT(c.id)::numeric / NULLIF(COUNT(*), 0), 4 ) AS inclusion_ratio FROM invoice_keys i LEFT JOIN contract_keys c ON i.id = c.id;

An inclusion ratio close to 1.0 is a useful signal. It is not proof of a valid business relationship.

A high ratio could result from shared sequential IDs, overlapping test data, or a mapping that was valid only during a migration. Conversely, a legitimate relationship may have a lower ratio because of delayed synchronization or historical records.

Treat the result as a candidate, not a verdict.

Step 2: Test cardinality before trusting the JOIN

A candidate join can match values and still be analytically unsafe.

Check whether the presumed parent key is unique:

SELECT contract_id, COUNT(*) AS occurrences FROM sales_contracts GROUP BY contract_id HAVING COUNT(*) > 1;

Then inspect the child-side multiplicity:

SELECT agreement_no, COUNT(*) AS invoice_count FROM billing_invoices WHERE agreement_no IS NOT NULL GROUP BY agreement_no ORDER BY invoice_count DESC LIMIT 20;

One contract having multiple invoices may be perfectly normal. Multiple rows with the same contract_id in what you assumed was a unique contract master may not be.

This matters because finding a join path and proving a metric's aggregation grain are different tasks.

Suppose one invoice has three cash allocations. If you join invoice rows to allocation rows and then sum invoice_amount, you will count the invoice amount three times.

The join is valid. The metric is wrong.

Step 3: Write the query at the correct grain

For the original question, the amount to sum is received_amount, which already lives at allocation grain. The following is an illustrative PostgreSQL query, not a production-ready definition of “active” or “this quarter”:

SELECT c.customer_id, c.customer_name, SUM(a.received_amount) AS cash_received FROM crm_customers c JOIN sales_contracts s ON s.customer_ref = c.customer_id JOIN billing_invoices i ON i.agreement_no = s.contract_id JOIN cash_allocations a ON a.invoice_ref = i.invoice_id WHERE s.contract_status = 'ACTIVE' AND a.received_at >= :quarter_start AND a.received_at < :next_quarter_start GROUP BY c.customer_id, c.customer_name;

Before shipping this query, the team still needs to confirm several business rules:

  • Does ACTIVE mean active today or active when the payment was received?
  • Are refunds and reversals represented as negative allocations?
  • Can one invoice cover multiple contracts?
  • Are payment amounts stored in multiple currencies?
  • Does the business mean cash received, invoiced revenue, or recognized revenue?

Those are semantic questions. They cannot be answered by a join-discovery algorithm alone.

The engineering task is therefore two-sided: validate the physical relationships and validate the business interpretation.

Step 4: Build a relationship evidence record

Instead of handing an LLM a list of guessed column pairs, an engineering system can produce structured candidates:

{ "left": "sales_contracts.contract_id", "right": "billing_invoices.agreement_no", "evidence": { "declared_foreign_key": false, "type_compatible": true, "value_inclusion_ratio": 0.98, "parent_key_unique": true }, "status": "candidate_requires_validation" }

The numbers above are illustrative, not measurements from a real deployment.

A useful workflow is:

Schema metadata ↓ Candidate column pairs ↓ Value and uniqueness checks ↓ Candidate relationship graph ↓ Business-rule review ↓ Approved join paths for relevant use cases

This keeps the system honest about what it knows. A statistical relationship is evidence to investigate, not automatic authorization to execute business-critical queries.

Where relationship intelligence fits

This is the kind of underlying problem we work on with IntaLink, a data-relationship discovery component of the Arisyn platform. Its documented capabilities include identifying primary/foreign-key relationships and discovering candidate relationships using data-level signals such as inclusion and relationship strength.

Semora, the business-semantic and intelligent-query component, handles terminology, metrics, semantic mappings, ambiguity handling, and SQL generation and validation. In the platform's integration model, Semora manages its own data connections and permissions, maps the relevant data-source ID, and requests relationship information through IntaLink APIs.

That separation is intentional:

  • Business semantics help determine what the question means.
  • Relationship discovery helps determine how the relevant data may connect.
  • Validation and governance determine which interpretation and path are safe to use.

This is not a claim that every inferred relationship is already a production-approved join, or that an ontology can be automatically generated from a schema. Those are separate engineering and governance problems.

A practical test for any enterprise AI data system

If you are evaluating an AI analytics system, don't stop at “Can it generate SQL?”

Give it a question whose answer requires an intermediate table that the user never mentioned. Remove the foreign-key declarations from a test schema. Use inconsistent but realistic column names.

Then ask:

  1. Did it retrieve the bridge table, or silently skip it?
  2. What evidence supports each proposed join?
  3. Did it check key uniqueness and fanout risk?
  4. Did it distinguish an inferred relationship from an approved one?
  5. Did it ask for clarification when the business definition remained ambiguous?

A system that refuses to invent a join may be more useful than one that produces impressive SQL immediately.

The real missing layer

Business ontology is important. It gives AI a vocabulary for how an enterprise thinks about customers, contracts, payments, and revenue.

But a vocabulary alone cannot tell you whether agreement_no really links to contract_id, whether a hidden invoice table is required, or whether a one-to-many join will inflate a metric.

For enterprise AI, meaning and connectivity must meet at the physical data layer.

An ontology tells the model what the business is talking about.

Relationship evidence helps establish whether the model can query the right data without guessing.

Discussion question: In your data warehouse, how many important joins exist only in SQL code or engineers' heads—not in declared foreign keys?