DEV Community

ArisynData
ArisynData

Posted on

From Discovered Metadata to Trusted Business Knowledge: Building a Validation Pipeline

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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"
  }
}
Enter fullscreen mode Exit fullscreen mode

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"
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

These checks provide evidence, but interpret them carefully.

  • Uniqueness on customers.customer_id supports 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"
  }
}
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

This is an illustrative contract, not a vendor-specific schema.

A reviewer should be able to answer:

  1. Is this the approved definition?
  2. Is the source field correct?
  3. Is the time dimension correct?
  4. Are required filters included?
  5. Does the aggregation match the intended grain?
  6. 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
Enter fullscreen mode Exit fullscreen mode

And it needs failure and retirement paths:

VALIDATING ──→ REJECTED
PUBLISHED  ──→ DEPRECATED
Enter fullscreen mode Exit fullscreen mode

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")
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

For example:

recognized_revenue v2
    status: deprecated
    effective_to: 2026-10-31

recognized_revenue v3
    status: published
    effective_from: 2026-11-01
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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)