Scan the QR code with WeChat

Add your dedicated advisor

Save

Published: 2026-08-17 10:25:52 Views: 2201

Recommendation

Data R&D transformation practices in the AI era: from SQL to the Semantic+DataAgent paradigm, breaking through traditional bottlenecks and efficiently unlocking data value. Key content: 1. The three major bottlenecks of traditional data R&D (low efficiency, semantic gap, inconsistent metrics) 2. The Semantic layer + DataAgent solution (semantic structuring + Agent architecture + engineering governance) 3. Transformation results: real-time demand response, automated data retrieval, and R&D roles upgraded to semantic architects

Yang Fangxian

Founder of 53AI / Most Valuable Expert of Tencent Cloud (TVP)

This article summarizes the practices and reflections on data R&D in the AI era transitioning from traditional SQL development to the Semantic (semantic layer) + DataAgent paradigm. It points out that the traditional model suffers from bottlenecks such as low efficiency, a semantic gap, and inconsistent metrics. By building a semantic layer that includes metrics, dimensions, logical tables, and a CTE adaptation layer, combined with Meta-ontology and Wiki-RAG retrieval augmentation, structured definition of business semantics and AI-automated NL2SQL translation are achieved. The solution adopts a dual-track Agentic architecture, migrating data knowledge from code to semantic models, transforming R&D roles into semantic architects and AI coaches, and safeguarding asset quality through engineering validation and automated governance, ultimately achieving real-time demand response, automated data retrieval, and efficient release of data value.

Transformation and experimentation of the data R&D paradigm in the AI era, from humans to AI Semantic (human + agent).

From: business requirements → understand semantics → design models → write SQL → test and validate → deliver reports

To: business requirements ──→ [semantic layer + AI] ──→ SQL ──→ results

In the old era, humans were the absolute priority—the sole vehicle for communication, development, and knowledge storage, translating business requirements into accurate SQL! All experience, standards, and related knowledge lived in human brains (documents) and SQL code. Since humans were what mattered, the bottleneck that followed was naturally humans too—because humans are richly diverse: some are rock stars, some are novices, some are seasoned veterans, some are fearless rookies~

But nowadays efficiency comes first—all the advantages are no match for faster, better, cheaper!

So what we are doing now is shifting from SQL-oriented development to AI Semantic-oriented development~

  • Three trends
  • AI has evolved from a "conversational assistant" into an "autonomous agent" (Agentic AI). AI no longer just answers questions; it can:
  • Perceive the business environment
  • Understand data semantics
  • Autonomously generate SQL queries
  • Collaborate on complex analysis tasks
  • Pressure on efficiency and cost
  • The bottleneck in releasing data value In the past, data value was stuck at the "last mile":
  • The business has questions but can't write SQL → the semantic layer lets AI do it for them
  • The data has answers but no one asks → Agents proactively push insights
  • People who understand both data and business analysis are in short supply → the business trains its own specialized Agents
  • Three bottlenecks

Low efficiency, long cycles

Previously, the lifecycle of a single requirement:

Requirement review -> model design -> design review -> ETL development -> code review -> data validation -> deployment -> integration testing -> report building -> data backfill

That's at least a day, right? And it takes a skilled worker all in on this one requirement.

The business-technology semantic gap

The business says: "I want to see the repurchase rate of active users." Technology asks: "How do you define active users? 7-day active or 30-day active? Does repurchase include purchases after refunds?" This kind of conversation plays out every day. Translating between business language and technical implementation consumes over 30% of the data team's energy.

The jungle of metric definitions

The same "GMV" may be calculated in 5 different ways across different reports. Marketing's GMV includes coupons, Finance's doesn't; Operations looks at order placement, Warehousing looks at shipment. Inconsistent metric definitions are the biggest killer of data credibility.

Based on the pain points and bottlenecks of traditional data R&D and the changes brought by AI development, we have discovered the breakthrough for data R&D in the AI era—the semantic layer!

After all that buildup, we finally get to the key part. As a simple analogy, the Semantic Layer is a fisherman's polarized sunglasses!

  • It is the middle layer connecting raw data and business semantics, abstracting complex table structures, field relationships, and computation logic into "metrics" and "dimensions" that business people can understand.
  • The semantic layer helps AI call the business truth interface rather than guess the underlying structure

LLMs are powerful and great at natural language processing, but they don't understand your business or your data. The semantic layer is the polarized sunglasses configured specifically for them to see your business and data clearly, making every fish in your pond visible at a glance~

General-purpose AI large models seem to know everything, but they don't understand your business context—they don't know which table the data is in, what the GMV formula is, or which anomalous data to filter out. The semantic layer fills in this context.

  • Core elements

If the semantic layer is the "glasses" put on AI, then Agentic AI gives AI "hands and feet."

Core definition of Agentic AI: AI is no longer just a passively responsive model, but an agent that can perceive its environment, act proactively, has clear goals, and possesses collaboration capabilities.

Traditional AI: you ask it a question, it gives you an answer. (Passive) Agentic AI: it proactively discovers problems, finds data on its own, analyzes on its own, gives you suggestions on its own, and even executes decisions on its own. (Proactive)

Agentic AI + the semantic layer is fundamentally changing the consumption model of data warehouses, in turn forcing a restructuring of warehouse architecture.

  • Data warehouse architecture changes

The traditional data warehouse architecture was designed with the assumption that humans are the ultimate consumers of data.

Therefore we needed:

  • Multi-layer data processing (ODS→DWD→DWS→ADS)
  • Complex ETL pipelines
  • Carefully designed reports and data products
  • Data analysts/BI as "translators"

But when AI agents become the primary consumers of data, this assumption is completely broken.

Every layer existed to make data more "human-understandable." But AI agents don't need any of this!

The dual-track Agentic architecture:

We built a hybrid Agentic Data Stack architecture: AI agents consume data directly through the semantic layer, while human analysts consume data through traditional BI/reports—the two tracks coexist and are chosen as needed. Agents are not just data consumers, but also maintainers of semantic assets.

Key changes:

  • Semantics becomes the new "layering"

Under the Agentic AI architecture, the semantic layer replaces physical layering as the core of data organization.

Problems solved by traditional layering:

  • ODS: data cleaning and standardization
  • DWD: business process modeling
  • DWS: pre-computed aggregation to improve query performance
  • ADS: application-oriented data marts

How the semantic layer solves these problems:

  • Data quality → the semantic layer defines data validation rules
  • Business modeling → the semantic layer defines entities, relationships, and measures
  • Query performance → vectorization, columnar storage, materialized views (handled by the warehouse engine)
  • Application adaptation → each Agent automatically assembles the data it needs based on semantics

Core insight:

In the short term, the semantic layer becomes the new abstraction layer for data organization. The traditional ODS→DWD→DWS→ADS physical layering hasn't disappeared, but it has become transparent to consumers—what the business sees is a unified semantic layer, while the physical layering becomes an implementation detail for performance optimization.

In the long term, when compute becomes powerful enough (running big SQL freely) and AI becomes smart enough (able to understand the semantics of raw data), the intermediate processing layer becomes redundant.

  • Changes in knowledge storage

Data knowledge is migrating from "scattered across SQL code, documents, and word-of-mouth tied to individuals" to "centralized in semantic models, understandable and reusable by agents." The carrier of business definitions has changed, and so have the management costs and maintenance approaches.

Challenges like keeping knowledge fresh and valid are also being solved in more efficient, more convenient, and more explainable ways.

  • Changes in the data development paradigm

From SQL Developer to Semantic Architect: the role of data development is evolving from "SQL Developer" to "Semantic Architect." The core value is no longer writing correct SQL, but defining accurate business semantics, designing semantic models that AI can consume, and continuously governing definition consistency.

The data team's positioning is shifting from "data factory" to "coach of AI agents."

  • A trident of three parts: the semantic layer (data semantics, business semantics, knowledge base), the server side (DSL, code generation, foundational services), and DataAgent (business insights, goal analysis, etc.)
  • The highest configuration is domain experts + AI, with clear division of labor and responsibilities, where 1+1+1>>>3
  • Quality over quantity: full-stack development (frontend, backend, data, AI applications), exploring new production relations for data development in the AI era

A new type of development team: Agent + experts + a Skill ecosystem~

Forward direction

  • From physical data warehouse table assets to semantic assets (logical tables, metrics, dimensions)
  • Fast local retrieval of the Wiki
  • NL2SQL, R2C (ADS)

Reverse direction

  • Governance Agent + Skill periodically scanning the semantic layer and tag roots
  • Expert review
  • Agent fixes

Horizontal direction

  • Freshness: linkage between tag roots and dimensions/metrics
  • End-to-end lineage
  • Usage coverage (erroneous queries, automatic DWS consolidation, discovery of low-/high-quality assets)

Through extensive communication and requirements research, we found: having a standardized data warehouse is of course great and very important—it greatly reduces the difficulty of building the semantic layer. The physical data warehouse is both the foundation and the ceiling of the entire system.

But you build products to serve users, not to educate them. Countless cases prove that an excellent, standardized data warehouse is as rare as a three-legged toad—you can't count on finding one. We can't demand this, that, and everything else endlessly; that doesn't match reality. Requiring users to jump through hoops before using our product is inherently anti-human—if I've already done all that, what do I need you for?

To minimize the cost of building the semantic layer and reduce the impact of non-standard data warehouses, the semantic layer makes appropriate compromises with adaptation layers for three types of data forms that cannot be directly consumed—wrapping data sources that are "unusable / awkward / too slow" into standard inputs that the semantic layer can use.

  • Four major needs

Compatibility with non-standard data warehouse tables

Cdm standard tables (dwd/dws/ads) have standardized layering, naming, partitioning, and granularity. But in reality, a large number of data sources fall outside these standards.

  • Ad-hoc and project tables from business teams (arbitrary naming/partitioning)
  • External data sources (outside CDM governance scope, but needed for business joins)

These tables can neither be directly imported with type=0 (which would pollute the semantic layer's standardization) nor referenced by business teams writing raw SQL (which would lose governance). The role of CTE: write a SELECT that projects these non-standard data sources into a shape "conforming to the semantic layer contract," then register it. Non-standard content is isolated inside the statement, and downstream only sees standardized fields.

Business-defined custom logic

Some semantic concepts cannot be obtained directly from any single standard table:

  • "Active streamers in the last 7 days": requires window aggregation + time filtering on the detail table
  • "Most recent status after deduplication": requires ROW_NUMBER + QUALIFY
  • "Merging business status across two tables": requires UNION / COALESCE
  • "Blacklisted / invalid sessions excluded": requires writing business filter conditions in SQL

These are the business team's domain knowledge, not artifacts of data warehouse engineers' modeling.

The role of CTE: allow the business team (or data developers) to consolidate this custom logic into a named, approvable, and traceable asset.

Performance optimization for large tables

The core fact tables in the live-streaming domain are often at the tens-of-billions-of-rows scale (daily incremental detail tables like dwd_tb_ctlive_xxx_di). If DSL2SQL always runs against the detail table:

  • Data extraction SQL takes tens of minutes or even times out
  • Full table scans waste compute resources
  • The same aggregations are repeatedly executed across different metrics

An intermediate acceleration layer is needed. The role of CTE: pre-aggregate commonly used aggregation granularities (e.g., account_id + ds) and common metrics (e.g., SUM(pay_amt)) via GROUP BY into a CTE, so downstream metrics / dimensions reference this small table directly. The CTE acts as a "lightweight dws" in the semantic layer—not a true materialized table, but at the SQL level the computation has already been reduced.

Secondary processing of standard tables

Some things that even a standardized data warehouse can't solve, or transformation logic needed when the downstream ads layer uses the data, can be consolidated and abstracted away in the CTE layer:

  • dwd to dim: some dwd tables need to be joined in certain scenarios, such as order details
  • Primary key deduplication: some dimension tables are reused at multiple granularities, but the primary key doesn't fully match the granularity; in such cases, CTE-based secondary processing is needed to make the join granularity the primary key

Using CTEs to consolidate reusable logic and prevent downstream data anomalies

  • Three pillars

Isolation — keep the "dirt" inside the statement

  • A statement can contain any valid ODPS SQL: cross-project, non-standard partitions, complex JOINs, subqueries
  • But the fields output to the semantic layer (fieldList) must be standardized, stable, and explainable
  • Downstream bindings only see the fields in fieldList, not the internal complexity of the statement

Single-point maintenance — one SQL snippet reused in many places

  • The same "active streamers in the last 7 days" logic referenced by 5 metrics → only 1 statement to maintain
  • When the business definition changes (7 days to 30 days) → modify the statement + re-approval → 5 metrics update automatically
  • Counter-example: each metric writes its own window aggregation in its binding expression → one change requires edits in 5 places

Controllable performance — proactively defining aggregation granularity

  • aggrList is declared manually (e.g., ['account_id','ds']) → telling the semantic layer "what the minimum granularity of this SQL is"
  • DSL2SQL generates SQL based on this granularity, avoiding pointless fine-grained scans
  • If aggrList is written incorrectly (e.g., missing a partition field), the generated SQL will be slow or even wrong — so the CTE's aggrList must accurately reflect the true granularity of the statement

The underlying belief of the CTE is that the heterogeneity of data sources should not pollute the uniformity of the semantic layer; it lets the semantic layer not worry about underlying details (physical table structures, custom logic, performance optimization, etc.).

Good things are best in moderation. As mentioned earlier, a standardized physical data warehouse is the foundation and the ceiling; the CTE is only a stopgap. To go far, you need solid infrastructure~

  • Decoupling: a wall between the physical layer and the semantic layer
  • Abstraction: focus on usage rather than implementation

FACT (fact tables - metrics) vs DIM (dimension tables - dimensions):

Determined by business needs, not by table name prefixes. A real case: tbcdm.dim_tb_ctlive_itm_tag, a physical dim table, can be used both as a dimension table (create a logical dimension table bound to the dimension) and as a fact table (create a logical fact table basic_plh with metrics attached). One physical table, two logical tables, two sets of semantics, without interfering with each other.

  • One logical table can have multiple physical tables attached at the same time
  • One dwd/dim base table, detail layer, multiple fields
  • One dwd_ext/dim_ext table, detail-layer extension, slightly worse performance but more complete information coverage
  • Multiple dws/dim tables, pre-aggregated tables, speeding up queries
  • The metric configuration layer must strictly correspond to the physical table type — the DWD layer uses commonExpression + dwdAggFunction, the DWS layer uses expression + aggFunction; the two independent configuration sets cannot be mixed. The SQL generator automatically selects the specific physical table based on the query scenario.

Fuzzy physical modeling

Logical table types are determined by business needs, not by physical table name prefixes:

Tables with the dim_ prefix can have logical fact tables created (metrics need to be computed), and tables with the dwd_ prefix can also have logical dimension tables created (dimensions need to be exposed). The criterion is "how downstream uses it" — if it needs GROUP BY + SUM/COUNT, it's a fact table; if it needs a JOIN to supplement dimension information, it's a dimension table.

Business process isolation:

The same physical table can be associated with multiple logical tables, isolated by business process.

tbcdm.dws_tb_ctlive_jiangjie_vst_gc_itm_pv_lead_trd_1d

├→ jiangjie_pay (LT, businessProcess=pay)

├→ jiangjie_ipv (LT, businessProcess=ipv)

└→ jiangjie_read (LT, businessProcess=read)

When a metric is bound to a logical table, its businessProcess must match the logical table's businessTag.

  • Semantic graph
  • Four layers of binding

This layer of binding solves the "what to call it" problem. When pay_amt_risk was created earlier, it was rejected the first time because the name was not in the type=4 dictionary — the root-word binding was broken, and the server didn't accept it.

Layer 2: physical table → logical table (LT-PT binding)

This layer solves "where the data comes from".

# basic_pay bound to two physical tables at the same timeLT-PT(binding 1): basic_pay ↔ tbcdm.dwd_tb_ctlive_tcp_pay_di(mappingType=1, DWD main table)LT-PT(binding 2): basic_pay ↔ tbcdm.dwd_tb_ctlive_tcp_pay_di_ext(mappingType=1, DWD extension table)LT-PT(binding 3): basic_pay ↔ tbcdm.dws_tb_ctlive_tcp_pay_1d(mappingType=2, DWS acceleration table)

Many-to-many relationships: one physical table can be bound to multiple logical tables (isolated by business process), and one logical table can be bound to multiple physical tables (dwd/dws/dim).

Layer 3: dimension → logical table (LT-Dim binding)

This layer solves "how to join".

# account_id 绑到 basic_pay，映射到物理字段 account_idLT-Dim: dimension=account_id(1990) → lt=basic_pay(110)        expression="${account_id}"  ← 物理表实际字段名        role=0  ← 属性维度# is_tcp_arbitration_fail 绑到 tcp_commission_order 维表LT-Dim: dimension=is_tcp_arbitration_fail(2643) → lt=360_confirm_lock_real(313)        expression="'Y'"  ← 常量，不是字段引用        role=0  ← 属性维度# item_id 绑到 CTE 维表作为主键LT-Dim: dimension=item_id(2085) → lt=cte.ib_center_promot_bonus_itm(357)        expression="${item_id}"        role=1  ← 主键维度，维表专属
  • expression is the core: it declares "in the physical table corresponding to this logical table, which field this dimension takes". The same dimension is ${visitor_id} in table A and ${buyer_id} in table B (different field names but the same semantics).
  • The role=1 design: only the primary keys of dimension tables use role=1. When the SQL generator sees role=1, it knows this is a JOIN key and uses LEFT JOIN dim_table ON fact.key = dim.pk. Attribute dimensions with role=0 don't need a JOIN; values are taken directly from the fact table.
  • Constant expressions: the expression of is_tcp_arbitration_fail is 'Y' rather than ${is_ad} — because all records in the view are arbitration-failed orders, so once joined it is Y, and no field value needs to be taken. This is a generalization of the is_fans pattern.

Layer 4: metric → logical table + metric → metric (metric binding)

This layer solves "how to compute".

# 原子指标直接绑逻辑表指标 pay_amt(6637) → bindLogicTableId=110 (basic_pay)    commonExpression="CASE WHEN ${is_pay}='Y' THEN ${div_pay_amt} END"    dwdAggFunction=SUM# 派生指标绑逻辑表 + 依赖原子指标指标 pay_amt_risk(7704) → bindLogicTableId=110 (basic_pay)    basePointId=6637, basePointName="pay_amt"    dependsOn=[{type:POINT, id:"6637"}]    commonExpression="CASE WHEN GET_JSON_OBJECT(${risk_extend},...)=...THEN ${div_pay_amt} END"    determiner="risk", qualifiers=[{...type:QUALIFIER, id:"2561"...}]# 组合指标依赖多个指标指标 sale_amt_union(7623) → bindLogicTableId=284    dependsOn=[{id:"7621", alias:"sale_amt_b1"}, {id:"7622", alias:"sale_amt_b2"}]    expression="${sale_amt_b1} + ${sale_amt_b2}"
  • dependsOn is the lifeline of derived/composite metrics: it declares the computation dependencies. A derived metric depends on one atom (basePointId + basePointName), and a composite metric depends on multiple metrics (each with an alias for reference in expression). If this edge is broken, the SQL generator cannot find the expressions of upstream metrics, and the whole chain breaks.
  • determiner + qualifiers: derived metrics are distinguished via determiner (pay_amt vs pay_amt_risk) and declare qualifying conditions via qualifiers (the risk qualifier). nameAndDeterminer is the unique identifier of a metric — metrics with the same name but different determiners share the same type=4 root word, but their configurations are independent.

The essence of relationship binding is to break SQL JOIN logic into declarable, composable, verifiable edges. Each edge describes only one fact.

  • Several advantages

1. Physical layer changes don't affect the consumer side

Physical table restructuring/renaming/migration only requires updating the LT-PT bindings; the expressions of metrics and dimensions don't need to change.

2. Unified consolidation of metric definitions

Metrics bind to logical tables (bindLogicTableId), dimensions bind to logical tables (LT-Dim), and all definitions are consolidated through logical tables. The same metric can reuse a name across different topics as long as nameAndDeterminer differs — logical tables are naturally isolated by topic + businessProcess.

3. Flexible multi-table, multi-layer composition

One logical table attaches both DWD + DWS physical layers, and the SQL generator picks the optimal path by scenario. The same physical table can be bound to multiple logical tables (different business processes) without interfering with each other.

4. Dimension reuse and decoupling

Dimension entities are global (not bound to tables); they are bound to different logical tables via LT-Dim, and the expression on each table can differ (${account_id} vs cast(${account_id} as string)). Changing a physical field name only requires updating the LT-Dim expression, without touching the dimension entity.

5. RAG Retrieval Friendliness

wiki_rag organizes directories by logical table: topic → bp → points/, and _relations.md fetches in one hop all relationships from logical table → physical table → dimension → metric. The logical table is the relationship hub of the knowledge base; without it, retrieval would require a full-table scan.

6. Ontology Metadata Enhancement

metadata-ontology encodes ontology tags such as @entity/@kind/@pair in dictionary descriptions, allowing dimension roots to be aggregated by entity, filtered by form, and completed by pairing. Dimensions bound to logical tables gain semantically enhanced retrieval through root ontology, not just text matching.

▐ Design Philosophy - Wiki-rag

Poor or unstable AI application results happen frequently, for many reasons. Setting aside the model itself and the users, let's just talk about context: often the input you give is too much yet not clear or standardized enough, leading to AI misjudgment.

Therefore we need a single truth, a centralized, standardized single caliber with final interpretation authority, maintained by dedicated people, owned by dedicated people, and accountable to dedicated people. That is SSOT, single source of truth.

wiki-rag is not a data source; it is a read-only projection optimized for the LLM context window. The truth always lives on the server-side platform. It only serves as a fast, efficient local quick reference, read-only — a localized lightweight replacement for the previous lightRag.

  • Four Elements

1. SSOT (projection ≠ truth)

  • Server-side platform = the only storage + final interpretation authority

2. Centralized standard caliber

  • The wiki projects the calibers already finalized on the server side as-is
  • When the agent clarifies assets, it can only cite the wiki, not guess calibers from physical table fields
  • Sync completion = unified caliber baseline across the whole ecosystem; downstream DSL / SQL / reports all start from the same baseline

3. Fast retrieval

  • File-level indexing (_index.md / _enum_index.md) + path-as-semantics (basic/pay/points/pay_amt.md)
  • grep-friendly, usable offline, no embedding semantic loss
  • The agent gets asset IDs in < 100ms, without depending on remote APIs

4. Context engineering

  • Tiered loading: index.md → topic _index.md → single-asset .md, read layer by layer on demand
  • Density first: keep only ID/name/showName/formula/aggrType/description, drop audit fields
  • Verifiable: every number can be traced back through wiki → server-side platform
  • Rebuildable: resyncing a dirty wiki is cheap; letting the agent reason with stale calibers is expensive
  • Collaboration Flow

Anti-pattern quick reference:

Editing wiki files / bypassing the wiki to guess calibers / stuffing everything into the prompt / multiple coexisting wikis — all break the SSOT chain.

This is an initial exploration of building the business semantic layer. meta-ontology is the etymology layer + ontology layer of the semantic layer — it governs not only "what it's called" but also "what it is, what category it belongs to, and how it relates to other things." The semantic layer knows "how to query" but not "what it is."

Philosophical ontology studies three things: ① what exists in the world (being) ② how to classify it (categories) ③ how things relate to each other (structure).

meta-ontology brings this framework into the data domain.

Core belief: data assets are not a pile of IDs but a consensus-based conceptual system. meta-ontology is the tool that makes this conceptual system explicit, computable, and governable.

  • Semantic layer assets — physical tables, logical tables, dimensions, metrics — are essentially a set of structured technical configurations. It knows:
  • the account_id dimension is bound to the basic_pay logical table, with expression ${account_id}
  • the pay_amt metric's commonExpression is CASE WHEN ${is_pay}='Y' THEN ${div_pay_amt} END, with aggregation type SUM
  • the basic_pay logical table is bound to the physical table tbcdm.dwd_tb_ctlive_tcp_pay_di
  • But it doesn't know:
  • account_id and account_name are two forms of the same entity (id↔name pairing)
  • cate_level1_id and cate_level2_id form a hierarchy, where level1 is the parent of level2
  • pay_amt belongs to the "amount" category of metrics, not the same class as pay_cnt (count-type)
  • the is_ad dimension is typically used together with pay_amt and expo_uv
  • when using the belong_tag dimension, note that "historical data may be inconsistent with the current production data"

When the business side asks "which metrics involve category dimensions," the semantic layer can only do fuzzy search by name. When the business side asks "which metrics pair well with this dimension," the semantic layer has no answer. metadata-ontology solves exactly this gap.

1. Roots = basic naming units (ontology: atoms of concepts)

  • 5 types of roots correspond to 5 basic categories: business process (1) / metric (4) / dimension (6) / topic (8) / qualifier (10)
  • The name of every metric / dimension must be assembled from existing roots
  • Roots are the minimal semantic units, equivalent to "concept atoms" in ontology

2. description as semantic contract (ontology: axiomatization)

  • The description uses @key=value tags as machine-readable axiom declarations: 支付风险金额 @business_process=pay_risk @kind=amount @formula=CASE_WHEN_...
  • This is not just "explanatory text" but an axiomatic definition of the concept — retrievable by RAG, reasonable over by agents, and comparable by validators
  • Assembly via build_description(free_text, metadata) is mandatory, ensuring a stable axiom structure

3. Dedup = preventing naming corruption (ontology: concept uniqueness)

  • One concept must not have multiple names (pay_amt / pay_amount / payment_amt coexisting = ontology corruption)
  • check_before_create is mandatory before creation: exact conflicts block, similar ones warn
  • In ontology this is called Concept Identity — one concept, one identifier

4. CDM approval = the valve of ontology evolution (ontology: consensus governance)

  • Creation = EDIT state, never goes live directly
  • Going live requires approval from the CDM expert group
  • The ontology is not the personal product of an individual developer; it is the team's shared conceptual system—evolution must go through a consensus process
  • How to implement it

Encode ontology metadata in the description, without changing the server-side schema, adding new fields, or calling new APIs. Reuse the existing description field of dictionary entries, encoding structured metadata with @key=value tag syntax: first-level category id, from the product category system, top-level node @entity=cate @kind=identifier @level=1

@pair=cate_level1_name @usage=category drill-down analysis, product filtering

@metrics=pay_amt,expo_uv,clk_pv @source=item_info.cate_id+cate_tree

@caveat=categories are adjusted every year; historical data may be inconsistent with the current production environment @contact=xxx

Humans see a Chinese description plus a bunch of tags. After the machine parses it with parse_description(), it gets:

{  "entity": "cate",          // 属于"类目"实体  "kind": "identifier",       // 形态是"标识符"  "level": 1,                 // 层级深度 1  "pair": "cate_level1_name", // 配对词根  "usage": ["类目下钻分析", "商品筛选"],  "metrics": ["pay_amt", "expo_uv", "clk_pv"],  "source": "item_info.cate_id+cate_tree",  "caveat": "每年类目调整;历史数据可能与现网不一致",  "contact": "xxx"}

This layer of metadata upgrades "what a dimension is" from "a name + Chinese name" to "entity ownership + form classification + hierarchy + pairing relationships + usage scenarios + associated metrics + data source + caveats + contact person."

  • Boundaries and relationships

metadata-ontology: manages "what it is"—entities, forms, hierarchies, pairings, business scenarios

data-manager: manages "how to query"—tables, fields, bindings, expressions, SQL

When data_manager creates dimensions/metrics, name/showName must be taken from the roots of metadata-ontology. If a root doesn't exist → stop, create it via metadata-ontology → wait for CDM approval → then continue with semantic layer operations.

But metadata-ontology doesn't know about the existence of semantic layer assets. It doesn't know which logical table account_id is bound to, what the expression is, or whether status is online or offline. It only manages dictionary-layer metadata—this is a clean separation of responsibilities.

The bridge is name. The same name (e.g., account_id) refers to the same concept in both systems: a type=6 root in metadata-ontology, and a dimension entity in data_manager. name is the join key, linking the business semantics of the dictionary layer to the technical configuration of the semantic layer.

  • What AI (agents) can and should do

Broadly speaking, AI can do almost anything—if you ask it to do something, there's nothing it can't do. What we're discussing here is using AI more elegantly and efficiently, not just using AI. Through extensive hands-on practice, we've found several things AI is suited for and should do:

  • Exploratory analysis
  • Executing and operationalizing standard workflows

Things that are not suitable, or not yet suitable:

  • 100% precise delivery
  • Development of requirements with complex backgrounds and complex definitions
  • The boundary between engineering and AI

To ensure system stability, determinism, and robustness (reducing hallucinations), my experience is as follows:

  • Avoid AI wherever possible; use engineering to solve problems whenever engineering can
  • If you can write code, don't write prompts

If you can clearly articulate the rules and standards, that means it can be solved with engineering or code rather than relying on prompts. Prompts work quickly but don't fix the root cause—you never know when they will hallucinate.

  • Changes in developer roles
  • Practice

As you saw above, our semantic layer is extremely complex. Currently it is produced and maintained solely by the data-manager skill. In practice, we've found that relying on a powerful skill alone to guarantee semantic layer quality is really, really hard—it places high demands on both users and the model, and hallucinations are unavoidable.

At first we implemented go-live controls for the semantic layer, but in the end we found you simply can't keep up with code review. We ultimately had to use engineering-side mandatory interception and validation, plus standardized, clear error messages, to optimize and safeguard the quality of data-manager's output.

Overall, we built two lines of defense on the server side to ensure the creation quality and go-live safety of semantic assets (physical tables/logical tables/metrics/dimensions):

Line of defense one: pre-validation at creation (Import Validation)

Positioning: intercept non-compliant data before assets are written to the database, with Chinese error messages precise down to the field.

Core capabilities:

  • Strong root binding — name/showName must come from the dictionary, and showName must start with the root's Chinese name (MT-28/DM-11)
  • status approval gate — status=1 (online) is forbidden at creation; only 0/3 are allowed (DM-12 + double interception at the service layer)
  • Referential integrity — a logical table must have a physical table first, metrics must be bound to a logical table, and expression fields must exist in the CTE/physical table
  • Deduplication within the same batch — prevents duplicate submissions of the same asset in a single import

Line of defense two: go-live approval interception (BPMS Approval)

Positioning: when an asset goes from draft/offline to online (status=1) or is deleted, execution only happens after approval by a CDM approver.

  • Semantic layer asset compliance scanning

This task targets "online assets"—that is, metrics, dimensions, logical tables, physical tables, and their binding relationships already in use. It scans 11 rules, which can be understood as a "full physical exam" for the semantic layer:

  • The first layer is expression compliance (rules 1-2): checks whether the SQL expressions in metrics and LT-Dim bindings are well-formed, e.g., whether physical table fields are correctly wrapped in ${}, and whether entire function calls have been mistakenly wrapped.
  • The second layer is asset connectivity (rules 3-6): detects "orphan assets"—whether metrics are bound to a valid logical table, whether the logical tables referenced by LT-Dim bindings exist, whether the physical tables bound by LT-PT actually exist, and whether logical tables have at least one valid binding. These are essentially data model consistency checks to prevent ghost assets that are "configured but unqueryable."
  • The third layer is field validity (rule 7): fields referenced in LT-Dim binding expressions must actually exist in the underlying physical table, otherwise queries will throw field-not-found errors.
  • The fourth layer is naming and metadata consistency (rules 8-10): whether a metric's stored logicTableName matches the actual logical table name, whether the name and showName of metrics/dimensions/logical tables align with the tag root system (P0: name not in roots; P1: showName prefix mismatch; P2: topic/businessTag inconsistency), and whether a metric's fieldType matches its aggFunction.
  • The fifth layer is orphan dimensions (rule 11): if a live dimension has no LT-Dim bindings, it means it isn't used by any logical table and is redundant configuration.

Scan results are graded by severity (P0/P1/P2), and reports are pushed via IM. After confirmation, the Agent automatically executes fixes—changing showName, taking orphan assets offline, correcting expressions, etc.—then verifies with a GET lookup after fixing.

  • Semantic tag root freshness linkage + quality scanning

This task targets the "root-word dictionary" — i.e., the metadata root nodes of metrics, dimensions, business processes, and qualifiers (type=1/4/6/10). It has two phases:

  • Phase one: freshness sync. Pull the latest state of online assets and check in reverse whether the root-word dictionary needs to be updated in sync. For example, if a new metric goes live but the root words corresponding to its name haven't had @source or @dimensions added, the freshness script will automatically backfill them. Essentially, this lets the root-word dictionary evolve automatically with business changes, rather than relying on manual maintenance.
  • Phase two: quality scanning (5 checks):
  • Non-compliance: root words missing required metadata tags (@usage, @entity, @kind), or empty description, or untranslated name and showName
  • Ambiguity: showName too short (
  • Ontological duplicates: root word pairs with identical entity+kind within the same type, possibly duplicate entries
  • Spelling similarity: edit distance of name
  • Semantic duplicates: identical showName or similarity >= 0.85, a stricter semantic-level deduplication than ontological duplicates

The report is automatically compared with the previous day's, outputting incremental change trends.

The relationship between the two:

The former scan governs the "asset layer" (configuration correctness of metrics/dimensions/logical tables), while the latter scan governs the "metadata layer" (completeness and consistency of the root-word dictionary).

The two layers are interrelated — the root-word metadata backfilled by the freshness sync directly affects the results of the former's naming consistency checks. This forms a positive loop of "underlying metadata governance → upper-layer asset compliance."

The overall design philosophy is to transform semantic-layer quality assurance from "manual periodic governance" into "daily automated inspection + Agent-assisted remediation," discovering, confirming, and fixing issues every day to form a closed loop, with quality metrics (non-compliance count, semantic duplicate count, etc.) continuously converging.

Roughly the following directions:

Cost

  • Reduce onboarding cost: quickly turn non-standard tables into standard tables (ods->cdm), and from reports to semantics (decompose reports, API definitions, and lineage into semantic-layer generation)
  • Reduce comprehension cost: continuously build the business semantic layer, bridging the blind spots between business semantics and data semantics

Experience

  • Faster queries: optimize Odps first
  • Permission isolation, application, and security controls

Ecosystem

  • Usage monitoring, analysis, and optimization, including Agent, Skill, and specific data-run SQL, etc.
  • Tracking and governance of garbage semantics

53AI, the top service provider for enterprise LLM deployment

Product: scenario implementation consulting + LLM application platform + industry solutions

Commitment: free POC validation; cooperate only after results meet the bar. Zero-risk LLM deployment, delivered to 160+ medium and large enterprises

Loading...

Scan to consult

Scan to log in

By logging in, you agree to the "53AI Website Service Agreement"

Service Agreement

Welcome to the [53AI official website] (hereinafter "this website" or "we"). This "Membership Service Agreement" (hereinafter "this Agreement") is a legal agreement between you (hereinafter "Member" or "User") and [Shenzhen Bosixiechuang Network Technology Co., Ltd.] regarding registration, login, and use of this website's membership services.

Before registering or logging in, please read and fully understand the terms carefully, especially the liability exemption or limitation clauses, intellectual property clauses, and dispute resolution clauses. Such clauses will be highlighted in bold for your attention. When you successfully log in to this website via WeChat official account authorization, SMS verification code, or other means, you are deemed to have fully understood and agreed to accept all contents of this Agreement.

I. Definitions

This website: refers to the website and related mobile pages operated by [Shenzhen Bosixiechuang Network Technology Co., Ltd.] with the domain name [53ai.com].

Membership services: refers to the knowledge base article access, content search, and other related value-added services provided by this website to registered members.

Knowledge base content: refers to the digital content resources published by this website, including but not limited to text, charts, data, research reports, industry analysis, etc.

II. Account Registration and Login

Login methods: this website supports the following login methods, which you may choose according to your actual situation:

WeChat official account authorized login: you agree to authorize your WeChat OpenID information to this website for creating or linking a member account.

SMS verification code login: you need to provide a real and valid mobile phone number and complete identity verification and login/registration via SMS verification code.

Account security: your account is for your personal use only; gifting, lending, renting, transferring, or selling it is prohibited. Losses such as account theft or password leakage caused by your improper safekeeping shall be borne by you.

Real-name authentication: as required by relevant laws and regulations, we may require you to complete real-name authentication for certain features. If you refuse to provide it, you may be unable to use some or all services.

Protection of minors: if you are under 18, please read this Agreement accompanied by your legal guardian and use this service with the guardian's consent.

III. Service Content and Rules

Knowledge base access rights: after logging in, members have the right to browse and search relevant articles and content in this website's knowledge base online, within the scope of permissions corresponding to their membership level.

Service changes: we have the right to adjust, change, or terminate part of the service content as needed for business development, and will notify you in advance via website announcements, official account messages, etc.

Prohibited conduct: when using the services, you must not:

Use technical means to batch-crawl, download, or re-store knowledge base content;

Use knowledge base content for commercial purposes or disseminate it to third parties without authorization;

Interfere with the normal operation of this website or infringe upon the legitimate rights and interests of other users;

Publish illegal or non-compliant information or engage in activities contrary to public order and good morals.

IV. Intellectual Property Statement

Rights ownership: the intellectual property rights of the layout design, software code, and other content in this website's knowledge base belong to [full company name] or the original rights holders, and are protected by laws such as the Copyright Law of the People's Republic of China.

Limited license: this website grants members a non-exclusive, non-transferable, non-sublicensable ordinary license, limited to online access to knowledge base content for personal study and research purposes only.

Infringement liability: without written permission, no organization or individual may copy, reprint, excerpt, mirror, compile, or otherwise use the above content in any form. Once discovered, we reserve the right to pursue legal liability.

V. Personal Information Protection

We value the protection of your personal information. For how we collect, use, store, and protect your personal information, please read the "Privacy Policy" separately.

The information you provide through WeChat official account authorization or mobile phone verification will be handled strictly in accordance with the Personal Information Protection Law, and used only for necessary purposes such as identity verification, service provision, and security verification.

You may at any time exercise your rights to access, correct, and delete personal information and withdraw authorization consent through website settings or by contacting customer service.

VI. Disclaimer

Content accuracy: knowledge base content is for reference only and does not constitute professional advice. We make no express or implied warranties regarding its completeness, accuracy, or timeliness; you should judge for yourself and bear the risks of use.

Force majeure: we are not liable for breach of contract for service interruptions or delays caused by force majeure such as natural disasters, changes in policies and regulations, network failures, or third-party platform interface anomalies (such as WeChat interface maintenance or carrier SMS channel failures).

Third-party links: this website may contain links to third-party websites, whose content and services are beyond our control; please assess the risks yourself.

VII. Liability for Breach

If you breach this Agreement, we have the right to take measures such as warnings, feature restrictions, suspension of services, or account termination depending on the severity of the violation, and we reserve the right to claim compensation for losses.

If your breach causes us to suffer administrative penalties, third-party claims, or damage to our goodwill, you shall bear full liability for compensation (including but not limited to fines, damages, attorney's fees, notarization fees, etc.).

VIII. Governing Law and Dispute Resolution

The formation, performance, and interpretation of this Agreement shall be governed by the laws of the Mainland of the People's Republic of China.

Any dispute arising from or in connection with this Agreement shall first be resolved through friendly negotiation between the parties; if negotiation fails, either party may file a lawsuit with a people's court of competent jurisdiction in the location of [the Company].

IX. Miscellaneous

This Agreement constitutes the entire agreement between the parties regarding the Service and supersedes any prior oral or written agreements.

If any provision of this Agreement is held invalid or unenforceable, it shall not affect the validity of the other provisions.

We have the final right to interpret this Agreement and reserve the right to modify it at any time within the extent permitted by law. Any modified Agreement takes effect upon publication, and continued use of the Service shall be deemed acceptance of the revised terms.