Semantic Layer: Why Your Revenue Numbers Never Agree

Three teams show three revenue figures because each one computes the metric its own way, in its own tool, from different tables, with its own rules on discounts, refunds, currency and reference date. None of them is wrong. A governed semantic layer fixes this by defining each metric once, in code, in a place every tool reads from. On a well-scoped perimeter it takes three to five weeks to stand up, not quarters.

The scene you already know

Monday morning, leadership meeting. The dashboard shows $4.21M revenue for August. The CFO has $4.08M in her reporting pack. The VP Sales quotes $4.35M from the CRM. Twenty minutes go to debating the source, the decision is postponed, someone is asked to "reconcile".

This meeting is expensive, and it repeats every month because reconciliation treats symptoms. Next month the same causes produce fresh gaps.

Where the gaps come from

Across years of this kind of investigation, the causes almost always fall into five families.

Cause	Typical example	Who is right
Scope	Sales counts signed orders, finance counts invoiced orders, the dashboard counts shipped orders	Everyone, for a different purpose
Calculation rules	With or without discounts, refunds, shipping, tax	Nobody until the rule is written down
Reference date	Order date, invoice date, payment date, ship date	Depends on the question
Source	CRM, ERP, e-commerce platform, each with its own sync lag	The system of record per domain, to be decided
Joins and duplicates	A multi-line order counted three times, a customer present twice	Usually the lowest number

The common thread: the definition lives in someone's head or in a hidden formula inside a report. It is not written, not shared, not tested.

What a semantic layer is

A semantic layer is the single place where you declare what the company's metrics and dimensions mean, independently of the tool that displays them. It translates "net revenue" into a precise formula on precise tables with explicit filters, and every consumer (dashboards, exports, AI agents, notebooks) goes through it.

Two implementations dominate on Google Cloud.

LookML, in Looker. Text files versioned in Git describe views (tables), dimensions, measures and Explores (joins). A measure is written once and used everywhere in Looker, in the API and in Conversational Analytics agents.

measure: net_revenue {
  label: "Net revenue"
  description: "Invoiced amount excluding tax, net of discounts and credit notes. Reference date: invoice date."
  type: sum
  sql: ${invoiced_amount_excl_tax} - ${discount_amount} - ${credit_note_amount} ;;
}

dbt, in BigQuery. dbt carries the transformations (clean fact and dimension tables) and their tests. Its metrics layer (MetricFlow) also lets you declare metrics that several tools can query. In practice, on a Looker stack, dbt prepares the tables and LookML carries the semantics exposed to users.

What "governed" adds: an owner per metric, code review before any change, history, automated tests, and one simple rule: if it is not in the semantic layer, it is not an official number.

Why spreadsheets and reports are not enough

A metric dictionary in a wiki is useful but prevents nothing: the report that calculates differently keeps running. A calculated field in a Looker Studio report is a definition, but local and invisible to everyone else. A well-built Power BI model is a semantic layer, but any analyst can publish another one.

The decisive difference is the mandatory checkpoint. When every tool reads the same measure, nobody can produce a different number without doing it on purpose, and if someone does, the gap against the official figure is visible and traceable.

The method in weeks, not quarters

"Data governance" programmes fail when they try to define everything before shipping anything. The method below reverses the order: one disputed metric, one arbitrated definition, one measure in production, then the next.

Week 1: pick and arbitrate

Take the metric that caused the last painful meeting, almost always revenue. Put the three people who produce the three figures in a room for two hours with their formulas. Have them write one definition together, one sentence, with scope, rules, reference date and source. Where a disagreement persists, you have two different metrics that deserve two names: "invoiced revenue" and "booked revenue", for instance.

Output: a one-page document per metric, signed by a business owner.

Week 2: prepare the table

In BigQuery, with dbt, build or fix the fact table that carries the metric: one row per event at the agreed grain, explicit amount columns (invoiced_amount_excl_tax, discount_amount), clean keys. Add dbt tests: key uniqueness, non-null amounts, reconciliation against the source.

This is where duplicates and joins get fixed once, rather than in every report.

Week 3: declare and expose

Write the measure in LookML with a label and a description that repeat the signed definition. Create or fix the Explore. Point the executive dashboard at it. Compare against the three old figures and document the differences: that reconciliation document, produced once, ends the debate.

Weeks 4 and 5: expand and lock

Move to the next metrics (margin, average order value, conversion rate) with the same method, faster because the tables and conventions exist. Lock it in: mandatory pull requests on the LookML repo, dbt tests in CI, progressive retirement of the old reports that recomputed the metric.

For a scope of ten to fifteen metrics and three or four sources, the whole thing fits in three to five weeks provided a business contact is available two hours a week.

What actually changes

What we see in production: in a large industrial group, the Looker semantic layer on BigQuery has served the executive dashboards for several years. The most visible change was not technical. Meetings stopped being about where numbers came from, and when a gap appears, the question is now "which business event explains it" rather than "which file is right".

The second effect comes later: when the organisation wants to plug in conversational agents, the semantic layer already exists. The agent answers by executing the governed measure, not by guessing a join.

The mistakes that turn weeks into quarters

Defining all 200 metrics before shipping the first. The governance committee meets, nobody decides, the project dies.

Leaving the definition to the data team without the business. A technically correct definition finance never signed will be contested at the first meeting.

Duplicating the semantics. One definition in dbt, another in LookML, a third in a Power BI model: you have rebuilt the problem with more tools. One layer exposed to users.

Skipping the description. A measure without a description forces everyone to guess. The definition signed in week 1 belongs word for word in that field.

Not retiring the old reports. As long as the old Excel file exists, someone will present it.

FAQ

Do you need Looker to have a semantic layer?

No. dbt with clean fact tables and declared metrics is already a semantic layer, readable by Looker Studio, notebooks or other tools. Looker adds governed exposure to end users, row-level security and Explores. The right choice depends on the number of consumers and the governance requirements.

How long until the first trustworthy metric?

Two to three weeks for revenue, if the business owner is available for arbitration. The rest goes faster; each additional metric reuses the tables and conventions.

What about historical numbers already published?

Do not silently rewrite them. Publish the new definition with an effective date and a bridge table explaining the gap against the old figure over the last twelve months. That is the document that reassures leadership.

Does a semantic layer slow analysts down?

At first, yes, because they can no longer invent a field on their own. After a month they are faster: the measures exist, they are documented, and nobody redoes the joins. Requests for new fields go through a pull request, usually handled within a day.

Next step

If the Monday morning scene sounds familiar, start with the one-page document: one metric, three formulas side by side, one arbitrated definition. The rest of the method unfolds from there.

See the BigQuery, dbt, Looker semantic layer service, and read next: The dbt, BigQuery, Looker stack and Conversational Analytics prerequisites.