AI can discover a relationship in your data. That does not mean your business should trust it.
Enterprise data platforms are beginning to extract more knowledge from assets companies already have: SQL history, dashboards, transformation models, metadata, and usage patterns.
That is useful. Years of business logic are often buried in queries and reports rather than maintained in one clean catalogue.
But there is a dangerous shortcut:
Found in SQL
↓
Assumed to be correct
↓
Used by an AI agent
A frequently used query can contain an old business rule. A join can run successfully and still multiply rows. A column called revenue may represent booked orders rather than recognized revenue.
The engineering challenge is therefore not just knowledge discovery. It is building a pipeline that separates candidate knowledge from validated evidence and approved business definitions.
This article proposes a practical design for that pipeline.
1. Start with candidates, not facts
Imagine a discovery process scans historical SQL and finds this pattern repeatedly:
SELECT
c.customer_id,
SUM(o.order_amount) AS revenue
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id;
It may extract three candidates:
{
"candidate_metric": {
"name": "revenue",
"expression": "SUM(orders.order_amount)"
},
"candidate_relationship": {
"left": "customers.customer_id",
"right": "orders.customer_id"
},
"candidate_dimension": {
"name": "customer",
"source": "customers"
}
}
These are useful discoveries. They are not yet business facts.
The query might be an approved Finance report—or a temporary Sales analysis. It might be copied from an old dashboard. It might omit cancellations, refunds, or recognition rules.
Store the discovery as a candidate with provenance:
{
"candidate_id": "cand_1042",
"kind": "metric",
"name": "revenue",
"proposed_expression": "SUM(orders.order_amount)",
"discovered_from": {
"asset_type": "sql_query",
"asset_id": "query_8821"
},
"first_seen": "2026-10-14",
"last_seen": "2026-11-08",
"observed_uses": 137,
"status": "discovered"
}
The example values are illustrative. In a real system, usage counts and timestamps should come from the actual query and asset logs.
Keep the source evidence attached to the candidate. If a reviewer cannot trace where a proposed rule came from, it is difficult to assess or correct it.
2. Validate relationships with multiple kinds of evidence
Consider a possible relationship between:
orders.customer_id
customers.customer_id
A useful first pass checks whether the proposed key on the customer side is unique and whether order values are covered by the customer key set.
For PostgreSQL, a basic profile could look like this:
-- Is the proposed customer key unique?
SELECT
COUNT(*) AS row_count,
COUNT(DISTINCT customer_id) AS distinct_count,
COUNT(*) - COUNT(DISTINCT customer_id) AS duplicate_count
FROM customers;
-- What share of order customer IDs exist in customers?
SELECT
COUNT(*) AS order_rows,
COUNT(c.customer_id) AS matched_rows,
COUNT(*) - COUNT(c.customer_id) AS unmatched_rows,
ROUND(
100.0 * COUNT(c.customer_id) / NULLIF(COUNT(*), 0),
2
) AS match_percentage
FROM orders o
LEFT JOIN customers c
ON o.customer_id = c.customer_id;
These checks provide evidence, but interpret them carefully.
- Uniqueness on
customers.customer_idsupports a one-to-many relationship from customer to orders. - A high match percentage suggests value inclusion.
- Unmatched values may be legitimate because of historical records, data latency, or deleted entities.
- Matching values do not prove that the two columns represent the same business entity.
A candidate relationship should therefore carry separate evidence fields:
{
"relationship_candidate": {
"left": "orders.customer_id",
"right": "customers.customer_id",
"evidence": {
"right_key_unique": true,
"left_value_match_rate": 99.2,
"observed_cardinality": "many_to_one",
"metadata_foreign_key": false
},
"validation_status": "needs_review"
}
}
Do not collapse these signals into a single confidence score and treat that score as proof. Each signal answers a different question.
A join can be valid and still be wrong for a metric
Suppose an order has two order lines and three payment records. Joining both child tables directly through order_id creates six rows.
2 order lines × 3 payments = 6 joined rows
A query that sums order-line amounts after that join can multiply revenue by three.
Relationship validation must therefore consider cardinality and analytical grain, not only whether key values match.
A candidate join path can be structurally plausible and still be unsafe for a particular metric.
3. Validate metrics separately from joins
A relationship test cannot establish the meaning of a metric.
For example, these are all plausible measures:
Booked Revenue
Invoiced Revenue
Recognized Revenue
Cash Received
They may use different source tables, dates, filters, and aggregation rules.
A metric candidate should be checked against a business contract:
metric_id: recognized_revenue
display_name: Recognized Revenue
owner: Finance
source_expression: SUM(revenue_ledger.recognized_amount)
time_dimension: recognition_date
required_filters:
- posting_status = 'posted'
status: pending_business_review
This is an illustrative contract, not a vendor-specific schema.
A reviewer should be able to answer:
- Is this the approved definition?
- Is the source field correct?
- Is the time dimension correct?
- Are required filters included?
- Does the aggregation match the intended grain?
- Which business domain is allowed to use it?
A model can suggest an expression. It should not silently decide the organization's accounting policy.
4. Use a validation pipeline with explicit states
A practical lifecycle is:
DISCOVERED
↓
VALIDATING
↓
VALIDATED
↓
APPROVED
↓
PUBLISHED
And it needs failure and retirement paths:
VALIDATING ──→ REJECTED
PUBLISHED ──→ DEPRECATED
The states should mean different things:
| State | Meaning | Production use |
|---|---|---|
| Discovered | Extracted from an asset or inferred from data | No |
| Validating | Evidence checks and review are in progress | No |
| Validated | Technical and business checks have passed | Not automatically |
| Approved | Authorized owner accepted the definition | Subject to publication policy |
| Published | Released to the intended consumers | Yes, within scope |
| Rejected / Deprecated | Invalid or no longer current | No |
The important boundary is between validated and approved.
A technically valid relationship is not necessarily an approved business relationship. A metric that passes data tests may still use the wrong definition for Finance.
Approval should be assigned to a real owner or governance process, not inferred from the LLM's confidence.
5. A small policy gate before production use
The following Python example illustrates the final control: only approved and published knowledge is available to the production query planner.
from dataclasses import dataclass
from enum import Enum
class KnowledgeStatus(str, Enum):
DISCOVERED = "discovered"
VALIDATING = "validating"
VALIDATED = "validated"
APPROVED = "approved"
PUBLISHED = "published"
REJECTED = "rejected"
DEPRECATED = "deprecated"
@dataclass(frozen=True)
class KnowledgeObject:
object_id: str
status: KnowledgeStatus
owner: str | None
evidence_ref: str | None
scope: str | None
def can_use_in_production(obj: KnowledgeObject, requested_scope: str) -> bool:
return (
obj.status == KnowledgeStatus.PUBLISHED
and obj.owner is not None
and obj.evidence_ref is not None
and obj.scope == requested_scope
)
candidate = KnowledgeObject(
object_id="relationship.orders_customer",
status=KnowledgeStatus.VALIDATED,
owner="Data Governance",
evidence_ref="profile_run_2026_1108",
scope="sales_analytics",
)
assert not can_use_in_production(candidate, "sales_analytics")
The object is validated, but it has not been published. The gate blocks it.
This is intentionally simple. A production implementation would also check user permissions, effective dates, version, data-source identity, approval records, and policy inheritance.
The critical design rule is straightforward:
Do not let the query-generation model promote its own discoveries into production knowledge.
6. Keep governance proportional to risk
Not every discovered object needs the same review process.
A synonym such as "client" → "customer" may be low risk in a particular domain.
A metric used for regulatory reporting or executive financial decisions is not.
A practical risk model can consider:
- Financial or regulatory impact
- Number of downstream reports and agents
- Cross-domain reuse
- Relationship cardinality and fanout risk
- Whether the source is authoritative
- Whether the definition conflicts with another approved object
Low-risk metadata can follow lightweight review. High-impact metrics and relationships should require explicit owner approval and stronger evidence.
This avoids two extremes: publishing every AI inference automatically, or creating a manual approval bottleneck for every minor metadata change.
7. Preserve provenance and changes
When a published definition changes, do not silently overwrite its history.
Keep at least:
Object ID
Version
Previous version
Source evidence
Validation results
Owner
Approver
Approval timestamp
Effective date
Publication scope
For example:
recognized_revenue v2
status: deprecated
effective_to: 2026-10-31
recognized_revenue v3
status: published
effective_from: 2026-11-01
This allows a team to understand which definition was used for a past answer and why a new answer may differ.
It also makes it possible to invalidate downstream caches or trigger review when a source relationship or metric definition changes.
8. What to measure
A knowledge pipeline should be evaluated on more than how many objects it discovers.
Useful measures include:
| Metric | What it tells you |
|---|---|
| Candidate validation rate | How many discoveries survive evidence checks? |
| Rejection rate by source | Which sources produce unreliable candidates? |
| Unapproved production usage | Did any unapproved knowledge reach query execution? |
| Relationship test coverage | Which published joins have uniqueness and cardinality evidence? |
| Stale knowledge rate | How much published knowledge is past its review or effective date? |
| Conflict resolution time | How quickly are competing definitions resolved? |
A high discovery count is not necessarily success. If a system generates thousands of candidates that nobody can validate, it may simply create another metadata backlog.
The goal is to increase the amount of useful, explainable, approved knowledge available to AI applications.
9. Where semantic and relationship intelligence fit
This lifecycle requires different capabilities to work together.
A relationship intelligence layer can discover candidate links between tables and fields, and preserve evidence such as key patterns, value overlap, and observed cardinality.
A semantic layer can manage business terms, metrics, dimensions, mappings, ambiguity, and the approved definitions used by query workflows.
In our work on IntaLink and Semora, these responsibilities are separate but complementary:
IntaLink
Discover candidate data relationships
↓
Evidence for validation
↓
Semora
Business semantics and mappings
↓
Governed query context
↓
Natural-language query workflow
Semora manages its own data-source connections and permissions. It maps the relevant data source by ID and requests relationship information from IntaLink through APIs.
This is a foundation for connecting physical data relationships with business semantics. It should not be confused with a claim that the products already provide the complete automated validation, approval workflow, or enterprise knowledge registry described in this article.
Those are important areas for product evolution.
Final thought
AI can make enterprise knowledge discovery much cheaper.
But if every discovered pattern becomes a trusted rule, automation will scale old mistakes just as efficiently as it scales useful knowledge.
A reliable pipeline needs three separate responsibilities:
Discovery finds candidates. Validation establishes evidence. Governance grants authority.
The distinction matters because enterprise truth is not whatever the model finds most often.
It is what the organization can explain, verify, approve, and safely use.

Top comments (0)