• 8 min read •By Tiago Marques

The Semantic Gap: Specifying Business Reality

The Goal of This Post

In Part 1, we established that specification-driven development excels at generating physical infrastructure. You can write a configuration file, and a deterministic parser can reliably generate staging models, apply basic transformations, and deploy tests.

But extending this same basic configuration approach to core business logic reveals its limitations.

The “Semantic Gap” is the fundamental difference between specifying a physical constraint and defining business reality. Flat configuration files cannot capture the interwoven complexity of dimensional modeling—a challenge mature data teams address using dedicated Semantic Layers.

Physical Contracts vs. Semantic Reality

To understand the semantic gap, we must first look at what deterministic tooling already does well. Modern data teams use strict data contracts (e.g., schema.yml in dbt or native data platform contracts) to enforce the physical structure of a table:

# A physical data contract
models:
  - name: fct_transactions
    columns:
      - name: transaction_id
        data_type: integer
        constraints:
          - type: not_null
          - type: unique
      - name: customer_id
        data_type: integer
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: amount_usd
        data_type: numeric(18,2)

This ensures column types are correct, nulls are prevented, and referential integrity holds. If the contract is violated, the pipeline fails. This is deterministic, automatable governance at the infrastructure layer.

The Metadata Layer: Catalogs and Information Schemas

There is also an incredibly valuable intermediate layer between raw physical tables and complex semantic query engines: the Data Catalog.

By querying a modern data warehouse’s information_schema (or using tools like dbt docs, DataHub, and Alation), engineering teams can automatically extract referential relationships, primary keys, and constraints. Adding table- and column-level comments directly to the underlying SQL views or Python scripts means the Catalog can expose an always-up-to-date, highly visible version of the data model to the whole company.

But there is a critical limit here: a data catalog provides discoverability, but it doesn’t quantify logic.

It informs a human analyst that amount_usd is a number, but it doesn’t define the mathematical rules that prevent a BI query from returning incorrect results.


The Nuances of Semantic Modeling

A true dimensional model—the kind that analysts can explore safely without generating silent errors—must encapsulate complex business logic. Here is what a simple physical spec fails to articulate:

1. What Is the Grain of This Fact Table?

Grain is the atomic level of your fact table. It answers the question: What does one row represent?

Consider this definition:

“One row per transaction line item, per product, per day. Natural key: (product_id, transaction_id_line). Surrogate key: revenue_line_sk.”

Aggregating this table by product_category without respecting its grain can produce subtle, catastrophic errors. Analysts must understand the correct joins and cardinalities to avoid fan-outs or double-counting, which flat specifications cannot enforce.


2. How Do You Handle Slowly Changing Dimensions (SCDs)?

Imagine a product gets recategorized from “Electronics” to “Accessories” on March 15th. Does historical revenue from February shift to Accessories, or does it stay in Electronics?

“Historical transactions use the category as it existed at the time of purchase (Type 2 SCD). Only transactions after March 15th use the new category.”

SCDs dictate how history is written. A physical spec knows that a category_id exists. But a semantic model must understand the temporal relationship between the fact event and the dimension state. If a system doesn’t understand that a join must include an active_daterange qualifier, the history of the data warehouse rewrites itself every time a source system updates a record.


3. Is ‘amount_usd’ Fully Additivity?

Additivity determines what aggregations are mathematically valid:

Measure Additivity Valid Operations
amount_usd ✅ Fully Additive SUM() across all dimensions
inventory_qty ⚠️ Semi-Additive Can SUM() within a period, but not across periods.
unit_price ❌ Non-Additive Must AVG(), never SUM().

If an analyst writes SELECT SUM(unit_price) because a basic spec didn’t prevent it, the database will return a meaningless result. Defining additivity upfront prevents invalid aggregations and protects analysts from silent errors.


4. What Are the Conformed Dimensions?

“This fact table conforms to dim_product (Enterprise v2.0) and dim_date. All domain-specific facts must join through these conformed dimensions to ensure cross-departmental reporting matches.”

If the marketing team builds a fct_campaign_ROI table and creates their own local dim_product, their reports will inevitably disagree with the finance team’s reports. Specifying the physical columns doesn’t solve this; the organization needs an architectural constraint—a conformed dimension strategy—to ensure everyone is measuring the same entities.


5. What Is the Business Definition of “Revenue”?

“Revenue = settled_amount + pending_adjustments, excluding refunds issued > 30 days post-purchase. Do NOT include internal transfers.”

A column named amount_usd hides edge cases that matter to accounting and compliance. Generic documentation often fails to capture the conditional logic required for business accuracy.


Governance requires a translation layer between the raw database and the business user. Enterprise BI tools like Cognos or BusinessObjects provided this, but their rigidity eventually drove teams toward the Modern Data Stack. We replaced heavy, GUI-driven tools with version-controlled SQL, but in the process, we lost the semantic abstraction itself.

Data teams are now re-establishing these guardrails using code-first Semantic Layers like Cube (a headless BI platform) or dbt Semantic Layer.

The Reporting Tool “Solution”

Modern BI tools like Power BI or Tableau offer sophisticated semantic layers, but embedding logic directly into visualization tools creates vendor lock-in and decentralized silos. Business rules become inaccessible to downstream consumers like data scientists using Python or analysts querying via other platforms.

A centralized, code-first Semantic Layer addresses this by establishing the abstraction below the BI tool and above the database, serving as a governed source of truth for all consumers.

These tools succeed because they don’t just specify physical columns—they introduce domain-specific abstractions designed expressly for semantic concepts. Let’s look at how a mature semantic layer handles additivity and join paths:

// A Semantic Layer configuration (e.g., Cube)
cube(`transactions`, {
  sql_table: `public.fct_transactions`,

  joins: {
    products: {
      relationship: `many_to_one`, // Prevents fan-out errors
      sql: `${CUBE}.product_id = ${products.product_id}`,
    },
  },

  measures: {
    revenue: {
      sql: `amount_usd`,
      type: `sum`,           // Explicitly fully additive
    },
    avg_price: {
      sql: `price`,
      type: `avg`,           // System prevents invalid SUMs
    },
  },
});

Here, the abstraction understands the business rules:

  • Aggregation type is codified: The system structurally prevents SUM(price).
  • Joins declare cardinality: many_to_one tells the generation engine how to handle the grain correctly during a join.

Mapping the Control Boundaries: Spec vs. Code

To return to the original promise of spec-driven development—where does it actually fit? What does the spec control, and what does the code control?

In a mature, correctly layered data architecture, you use different tools to govern different phases of execution:

  1. The Physical Spec (Data Contracts / schema.yml)
    • Controls: Database DDL. It enforces what shapes the data takes (types, nullability, unique keys, hard referential integrity).
    • Goal: Fails the pipeline if bad data arrives.
  2. The Execution Code (SQL / Python)
    • Controls: The actual data transformations. It takes raw data and shapes it into dimensional models, handling edge cases and data cleansing.
    • The Micro-Spec (Inline Comments): Code isn’t just execution; it’s also the most accurate source of business logic. A one-line comment (-- Exclude test accounts prefix 'QA_') explains why a filter exists. If written using a strict convention (e.g., Javadoc style, dbt doc blocks, or Sphinx-SQL), these comments can be programmatically extracted by a CI/CD process to automatically populate your data catalog. This ensures the granular “why” stays perfectly synced with the execution code.
    • Goal: Populates the physical tables with correct historical state and embeds the micro-reasoning alongside the logic.
  3. The Metadata Spec (Data Catalogs / information_schema)
    • Controls: Discoverability and human understanding. It surfaces descriptions, owner assignments, and extracted relationships.
    • Goal: Tells a human analyst what the table means.
  4. The Semantic Spec (Cube / dbt Semantic Layer)
    • Controls: Runtime BI query generation. It maps exactly how a BI tool is allowed to aggregate or join facts to dimensions dynamically.
    • Goal: Prevents analysts from generating technically correct SQL that returns mathematically wrong numbers.

When a team tries to use a flat “Physical Spec” to govern “Semantic Rules” (e.g., feeding a schema.yml to an LLM and hoping it generates valid BI logic), they are using the wrong abstraction for the layer.

The Semantic Layer as a Service

Rather than forcing AI to rediscover business logic from raw tables, we can expose the semantic model as a structured service. Agents gain access to human-authored definitions—metrics, grain specifications, and join paths—that are unavailable in standard database catalogs.

Tools like dbt-mcp or Cube’s REST/GraphQL APIs enable this by providing structured endpoints for semantic metadata. This context allows an agent to understand that revenue requires specific aggregation rules, preventing common errors like double-counting during joins.

The architecture looks like this:

graph TD Consumer["AI Agent / Consumer"] InfraGov["Infrastructure Governance"] LogicGov["Business Logic Governance"] Warehouse["Data Warehouse<br/>(Physical tables/contracts)"] Consumer -- "<b>Database Metadata</b><br/>(Tables, Columns, Lineage, Tests)" --> InfraGov Consumer -- "<b>Semantic Model Access</b><br/>(Metrics, Grain, Joins, Aggregation Rules)" --> LogicGov InfraGov --> Warehouse LogicGov --> Warehouse style Consumer fill:#f9f,stroke:#333,stroke-width:2px style Warehouse fill:#bbf,stroke:#333,stroke-width:2px style InfraGov fill:#fff,stroke:#333,stroke-dasharray: 5 5 style LogicGov fill:#fff,stroke:#333,stroke-width:2px

By interacting with the semantic model, consumers leverage pre-defined logic rather than attempting to derive it from first principles. The choice of protocol (MCP, REST, GraphQL) is incidental; the value lies in the human-curated semantic asset.

Federated Semantic Contracts

While this pattern is distinct from Data Mesh, it shares the core principle of self-serve consumption. Downstream consumers—whether AI agents, BI tools, or analysts—interact with a governed interface rather than writing arbitrary SQL against raw tables. The interface enforces domain rules without requiring consumers to manage the physical implementation.

This federated semantic contract pattern works whether the semantic layer is centralized or domain-owned. In both cases, the contract surface is the semantic model, and the interaction happens through a structured interface.

The Verdict

Use deterministic specifications like Data Contracts to govern infrastructure and physical pipelines. Use a semantic layer to govern business logic and provide a consistent API for both humans and machines.

By separating the governance of the shape of data from the governance of its meaning, you build a system that is both technically reliable and business-accurate.

To see how to capture the “why” behind these human decisions and automate the review of your documentation, check out BI Docs: Automated Documentation for Data Platforms.


This post is part of the series Spec-Driven Development in BI: A Pragmatic Series.

Tiago Marques
Written by

Tiago Marques

Principal Data Analytics Engineer specializing in cloud data platforms, Databricks, Kimball dimensional modeling, and reliable BI architectures.