Testing Meaning, Not Just Rows

Bringing data quality testing to the semantic layer

Data quality testing has a boundary problem. We have gotten good at testing tables: nulls, duplicates, referential integrity, row counts, schema drift. Every mature data team has a suite of these, and they catch real problems every week.

But nobody consumes a table. They consume net revenue, gross margin percent, active subscribers, days sales outstanding. Those are the artifacts the business argues about in meetings, and until now they were the least tested objects in the entire stack.

A table can pass every test you have written and still produce a wrong number. The error does not live in the rows. It lives in the definition. A relationship declared with the wrong cardinality quietly fans out and inflates a sum. An aggregation type set to sum on something that should be count distinct double counts. A filter baked into a metric excludes a channel nobody remembered was there. A new dimension member arrives from an upstream system and lands in an unmapped bucket, so the slices no longer add up to the total.

Row-level tests cannot see any of this. They are testing the ingredients. The mistake is in the recipe.

With the release of Coginiti 28.8, CoginitiScript works with Semantic SQL, which means the same assertion-based testing framework you already use on tables now points at semantic entities: your metrics, your dimensions, your relationships. You can assert that a metric sits in an expected range, that it reconciles across grains, and that it does not lurch between runs. The definition becomes testable.

Why this matters now

Two things changed at roughly the same time.

The first is that the semantic layer became something everything else now depends on. When metric definitions lived inside individual dashboards, a bad number was contained. One report was wrong, one analyst noticed, one person fixed it. When definitions are centralized and served to BI tools, applications, notebooks, and APIs, a single bad definition propagates everywhere at once, instantly, with full authority. Centralization is the right architecture. It also concentrates risk, and concentrated risk is exactly what testing exists to manage.

The second is agents. A human looking at a dashboard carries a lifetime of context about their business. If revenue reads three times its normal value, they squint, they check, they ask someone. That instinct is an unwritten, unpaid, and completely undocumented quality control layer, and we are now removing it from the loop. An AI agent querying the semantic layer has no intuition about whether a number is plausible. It receives a value, treats it as true, and reasons confidently onward from a wrong premise.

If meaning is going to be operationalized and served to machines, the definitions carrying that meaning have to be verifiable in code, on a schedule, with a pass or fail, not just documented.

How it works

If you have written CoginitiScript tests before, there is nothing new to learn about the mechanics. The contract is the same:

• Test passes: the query returns zero rows.
• Test fails: the query returns rows, and those rows are your diagnostic.

What is new is what goes inside the block. The body is Semantic SQL, so it queries semantic entities rather than physical tables, and measures are accessed through the MEASURE() function, which applies the aggregation defined in the semantic model. You are testing the metric as the business consumes it, not a hand-rolled reimplementation of it that can drift away from the real definition.

That distinction matters more than it might appear. A test that recomputes revenue from base tables is testing your test. A test written in Semantic SQL exercises the same definition, the same join path, and the same aggregation rules that Tableau, an application, or an agent will hit in production.

Is the metric in a plausible range?

The simplest and most valuable test in the set. Aggregate the measure, then filter for the values you never expect to see.

#+test sql MonthlyRevenueInRange()
#+meta {
  :doc "Monthly net revenue should fall between 2M and 6M.
        A breach usually means a load failure, a duplicated batch,
        or a change to the revenue definition."
}
#+begin
  SELECT *
  FROM (
    SELECT
      DATE_TRUNC('month', order_date) AS order_month,
      MEASURE(net_revenue) AS monthly_revenue
    FROM sales_detail
    WHERE order_date >= TO_DATE('2026-01-01')
    GROUP BY DATE_TRUNC('month', order_date)
  ) AS monthly
  WHERE monthly_revenue NOT BETWEEN 2000000 AND 6000000;
#+end

Ratio metrics are even better candidates, because they have natural bounds that are true regardless of season, volume, or business performance. A margin percentage outside zero to one hundred is never a business event. It is always a defect.

#+test sql GrossMarginWithinBounds()
#+meta {
  :doc "Gross margin percent must be between 0 and 100 for every product line."
}
#+begin
  SELECT *
  FROM (
    SELECT
      product_line,
      MEASURE(gross_margin_pct) AS margin_pct
    FROM sales_detail
    GROUP BY product_line
  ) AS m
  WHERE margin_pct < 0 OR margin_pct > 100;
#+end

Do the slices still add up?

This is the test that catches modeling errors, and it is the one you cannot write against tables. Compare the grand total of a measure to the sum of that same measure across a dimension. In a correct model these agree. When a relationship fans out, they do not.

#+test sql RegionRollupReconciles()
#+meta {
  :doc "Revenue summed by region must equal total revenue.
        A mismatch points at join cardinality in the semantic model,
        not at the underlying data."
}
#+begin
  SELECT
    'Region rollup does not reconcile to total' AS issue,
    t.total_revenue,
    r.region_revenue,
    ABS(t.total_revenue - r.region_revenue) AS difference
  FROM (
    SELECT MEASURE(net_revenue) AS total_revenue
    FROM sales_detail
  ) AS t
  CROSS JOIN (
    SELECT SUM(region_total) AS region_revenue
    FROM (
      SELECT region_name, MEASURE(net_revenue) AS region_total
      FROM sales_detail
      GROUP BY region_name
    ) AS by_region
  ) AS r
  WHERE ABS(t.total_revenue - r.region_revenue) > 0.01;
#+end

Run the same pattern across each dimension a metric is commonly sliced by. It is cheap, and it turns a class of silent modeling bug into a loud one.

Is every fact covered by the dimension?

Unmapped members are how totals and slices quietly diverge. If a new sales region appears upstream and the model has no member for it, revenue does not disappear from the total, it just stops appearing in any breakdown.

#+test sql EveryOrderMapsToARegion()
#+meta {
  :doc "No revenue should be attributed to a missing or unknown region."
}
#+begin
  SELECT
    region_name,
    MEASURE(net_revenue) AS revenue
  FROM sales_detail
  WHERE region_name IS NULL OR region_name = 'Unknown'
  GROUP BY region_name;
#+end

Did the number move more than the business could have?

Range tests catch absurd values. Continuity tests catch the plausible ones that are still wrong. A metric that drops thirty percent overnight might be a genuine business event, and it might be a partial load. Either way, someone should look at it before an agent starts writing recommendations on top of it.

#+test sql RevenueDoesNotSwingUnexpectedly()
#+meta {
  :doc "Flag months where revenue fell more than 30% or grew more than 50%
        against the prior month. Tune the bounds to your seasonality."
}
#+begin
  SELECT *
  FROM (
    SELECT
      order_month,
      monthly_revenue,
      LAG(monthly_revenue) OVER (ORDER BY order_month) AS prior_revenue
    FROM (
      SELECT
        DATE_TRUNC('month', order_date) AS order_month,
        MEASURE(net_revenue) AS monthly_revenue
      FROM sales_detail
      WHERE order_date >= TO_DATE('2025-01-01')
      GROUP BY DATE_TRUNC('month', order_date)
    ) AS monthly
  ) AS trend
  WHERE prior_revenue IS NOT NULL
    AND (monthly_revenue < prior_revenue * 0.7
         OR monthly_revenue > prior_revenue * 1.5);
#+end

Running them together

Semantic tests are ordinary CoginitiScript tests, so they organize into packages and run through std/test like everything else.

#+import "std/test"
#+import "semantic_tests/metric_bounds"
#+import "semantic_tests/reconciliation"
#+import "semantic_tests/continuity"

-- Definitional errors are critical: stop the run.
{{ test.Run(
     packages=[metric_bounds, reconciliation],
     onFailure=test.Stop
) }}

-- Drift detection is monitoring: report everything, keep going.
{{ test.Run(
     packages=[continuity],
     onFailure=test.Continue
) }}

The split is deliberate. A metric that cannot reconcile to itself is a defect and should stop the pipeline. A metric that moved more than expected is a signal, and you want the full list of signals rather than the first one.

Where these tests belong

In the model change workflow. Semantic definitions are code. Changing an aggregation type or a relationship should trigger the same reflex a schema change does: run the tests, review the failures, then merge. Reconciliation tests are especially valuable here, because they fail on precisely the mistakes that are hardest to catch by reading a diff.

After the pipeline, before the consumers. Table tests validate that the load succeeded. Semantic tests validate that the answers built on that load are still correct. Both belong in a write-audit-publish flow, at different points.

On a schedule, against production. Bounds and continuity checks are monitoring, not gating. Run them hourly or daily on the metrics that matter most and route failures to the people who own the definitions.

The larger point

A semantic layer is a claim about meaning: what revenue means here, how churn is counted, the grain at which a number is valid. Most of the industry still treats that claim as documentation, something written down, reviewed occasionally, and trusted rather than checked.

If you want the mechanics, the data quality testing tutorial covers the assertion model end to end, and the Semantic SQL reference covers what the semantic layer supports. Both are worth reading side by side, because the interesting tests live at the intersection.