How ConstructConnect Built a Governed Semantic Layer for Self-Service Analytics with Claude

This article describes ConstructConnect's approach to agentic analytics, drawing on the patterns Anthropic published in How Anthropic Enables Self-Service Data Analytics with Claude. Where Anthropic solved the same problems at a language model company, we solved them at a construction technology platform. The architecture is similar, the domain is different, and one decision we made differently changed the entire design.

The Problem

ConstructConnect runs on a federated data system. Finance metrics live in Power BI, built by the finance team over years of careful work. Platform usage metrics were built in dbt and BigQuery. Other teams did the same: independent, parallel work across the organization. Each surface evolved on its own, each team made decisions that made sense locally, and the result is a system where the same business concept ("active account," "renewal," "an Auto Takeoff run") can mean something subtly different depending on where you look.

This is not a failure of discipline. It is what happens when capable teams build things that work. The federated system is not broken; it is the foundation. The question we needed to answer was: how do you centralize the definition layer without dismantling the work underneath it? How do you take years of Power BI development, dbt models, and institutional knowledge and make it consistent across platforms, without forcing every team onto the same tools?

The Analytics team had already made significant progress on this. Years of documentation, endorsed table guidance, and LLM-friendly context files made Claude meaningfully useful for analytical questions. But documentation is not governance. A well-written context file tells Claude what a metric is, but does not ensure that the number Claude returns matches the number Power BI shows.

The central challenge is always the same: mapping a user's question to specific, current entities in the data model. Get that mapping wrong and the answer is plausible but incorrect. Plausible-but-wrong is worse than obviously wrong. It gets used.

Why Not the dbt Semantic Layer

The first architectural decision we faced was whether to build on top of the dbt Semantic Layer (MetricFlow). We evaluated it carefully. The answer was no, and the reason shaped everything that followed.

The dbt Semantic Layer requires a live DirectQuery connection from Power BI to BigQuery. Every filter change and slicer interaction in a report fires a BigQuery query, trading Power BI's import-mode performance, and the richness of relationships and grain it can model, for metric consistency. Reports become slower and more constrained.

Beyond performance, the dbt Semantic Layer has no visibility into Power BI measures or anything outside the dbt/BigQuery stack. The moment a metric exists in more than one place (which every meaningful metric at ConstructConnect does), dbt alone cannot be the source of truth.

Any semantic layer that starts from the dbt Semantic Layer either lets Power BI drift or accepts worse report performance. This is not a decision made out of attachment to Power BI. It is a decision made because a compromised architecture was not necessary, and accepting one would have meant working around the problem rather than solving it.

semantic-one: Two Things at Once

Our semantic layer lives in a version-controlled repository we call semantic-one. It does two things simultaneously, and understanding both is necessary to understand why it works.

First, it is a cross-platform orchestrator. Finance metrics originated in Power BI. Platform usage metrics were built first in dbt. Each platform had its own representation of the same business concept, and those representations drifted without anyone noticing.

semantic-one holds the canonical definition for each metric and propagates changes bidirectionally. Every metric YAML includes an origin field (where the metric was first defined) and a canonical field (where it should be queried). When a metric definition changes in Power BI, it triggers an update to the semantic-one YAML, which triggers a CI job that generates updated dbt models and opens a merge request in the dbt project. Each platform receives the definition in the format it works best with: DAX for Power BI, SQL for BigQuery, plain language for Claude. No platform degrades to serve another.

Second, it is a hub built to talk to LLMs. Metric definitions in dbt YAML or Power BI DAX are written for compilers. semantic-one is written for Claude. Every metric includes a business_definition in plain language, a grain specifying the entity and time unit, a dimensions list of valid grouping fields, and a canonical_logic field with a generic SQL expression using named placeholders.

Today semantic-one holds 166 metric definitions across five product domains. The catalog is auto-generated and validated by CI on every merge.

What Made This Possible: Power BI in Version Control

There is a prerequisite to everything that follows that is easy to overlook: Power BI had to be treated as code before it could be governed as code.

For most of its history, Power BI was a black box. Reports and measures lived in .pbix files: binary, not diffable, not reviewable, not automatable. A measure could change and no system would know it happened. That made any kind of propagation or sync impossible. You cannot tag what you cannot read.

The move to the PBIP format changed that. PBIP stores Power BI semantic models as TMDL, plain text files that are structured, readable, and committed to version control like any other code. Every measure lives in a file. Every change produces a diff. Every merge can trigger a CI job.

That single change, moving Power BI into version control, is what unlocked the entire propagation architecture. The // SEM_ID tag in a DAX expression is only useful because a script can now read it.

The inbound sync script works because TMDL files are just text. The governance model works because changes go through merge requests. None of this was possible before PBIP.

The Schema: Three Fields, Three Questions

A design decision that matters more than it looks: three root-level fields in every metric YAML answer three distinct questions.

The other key schema element is endorsement. The Document Pipeline domain has both modern and legacy metrics. The legacy metrics are not duplicates; they measure a legacy pipeline that serves as the comparison baseline for the modern one. Every metric carries an endorsement field: canonical, legacy_baseline, comparison_only, or supplemental. Claude routes to canonical metrics by default and reaches for legacy baselines only when the question explicitly calls for comparison.

The Materialized Semantic Layer

The metric YAMLs define what each metric means. The materialized BigQuery tables are where Claude queries them, and they solve four problems at once.

The LLM Query Decision Tree

When Claude receives an analytical question, it does not immediately write SQL. It follows a structured decision tree:

User query via Claude / Glean
  ↓
Skill → semantic-one lookup
  ↓
Metric found?
  ├── YES → Route to BigQuery materialized table (fast path)
  │         ↓
  │     Dimension matches?
  │         ├── YES → Return result
  │         └── NO  → Adjust query dimension using canonical template
  │                   (swap time, account, user, etc.)
  │                   (valid dimensions logged in semantic-one)
  │                   (governed upstream tables unchanged)
  │                   → Return result
  │
  └── NO  → Use existing query skills (no degradation)
           ↓
       Log miss to gap table (measure name, intent, timestamp)
           ↓
       Analytics team adds to semantic-one
       (next query succeeds via fast path)

Governing Dimension Adjustments

Claude can flex the grouping dimension. It cannot substitute tables. The governed upstream tables never change. The canonical SQL logic never changes. Only the dimension changes, and only to one that is explicitly listed in semantic-one.

Raw Data Access Is Not Restricted, But It Is Explicit

Accessing raw tables requires a deliberate, unambiguous request. A casual question routes to the governed path by default. Governed data is the default; freeform raw access is opt-in.

Skills: Procedural Knowledge at Scale

The semantic layer tells Claude what data exists and how to measure it. Skills tell Claude how to behave: which filters are mandatory, which tables to trust, when to stop and ask for clarification.

Our analytics plugin has 23 skills across four categories: domain query skills that encode mandatory business rules (currency normalization, internal user exclusion, mandatory filters); validation skills that verify results before they reach users; dbt development skills that translate analytical work into durable models; and discovery skills that identify where the data model is incomplete.

When a new analytics project ships, an accompanying skill or semantic-one update ships in the same cycle. The plugin has had an updated version nearly every week for the past year. As the semantic layer matures, updates to measure definitions propagate automatically through the tagging infrastructure.

The Gap Logging Loop

Two questions tell us whether the system is working. The first: are people using it? That answer lives in our dbt models. The second: are we meeting demand? That answer lives in the gap log.

Every time Claude receives a question and cannot find the measure in semantic-one, it logs the request to a BigQuery table. The analytics team models that data. Gaps surface in reporting. Frequently-requested but undefined metrics get prioritized and added to semantic-one. The next time a user asks the same question, it routes through the fast path.

Propagation: One Definition, Every Platform

The initial focus has been Power BI, because that is where the most work has already been done. But the tagging system is origin-agnostic by design. A metric can be defined first in dbt, first in semantic-one directly, or first in Power BI. The tag travels with the definition regardless of where it started. You can define a metric in semantic-one and propagate it into Power BI. You can define it in dbt and surface it there. The system does not privilege any platform as the starting point.

Every Power BI measure is tagged with a // SEM_ID=<metric_id> comment in the DAX expression. A sync script reads the Power BI TMDL files, finds the SEM_ID tag, and maps each measure to its YAML. A change in one platform propagates to all others. The SEM_ID is the spine that connects three representations of the same metric (DAX, SQL, and plain language) into one governed definition.

Gap logging extends beyond question-answering. Periodic scans of the Power BI and dbt repositories surface measures that are not yet tagged, definitions that overlap with existing semantic-one entries, and metrics that exist in one platform but not another. The gap log is a continuous audit of the federated system itself.

The Agentic Review Layer

When a metric definition changes in semantic-one and merges to main, the CI pipeline generates the updated dbt models and opens a merge request in the dbt project. That is the limit of what a pipeline should own. That is where the agent takes over.

After the CI pipeline opens the merge request, an agentic workflow picks up the branch. Its job is not to generate; the pipeline already did that. Its job is to validate, polish, and certify before a human ever sees the MR.

• Step 1: End-to-end QC. The agent runs a full validation pass: dbt compile and build, SQL style guide enforcement, logic checks, and cross-platform QC queries that compare BigQuery output against expected results. Any model that fails compilation, breaks a style rule, or produces numbers that diverge from expectations is flagged before a human is asked to review it.
• Step 2: Cross-platform QC. The agent runs the canonical SQL against BigQuery and compares results against the corresponding Power BI measure. If they diverge, the agent investigates and comments with findings before escalating.
• Step 3: MR description. The agent populates the merge request template. A reviewer sees a document, not a blank template.
• Step 4: Human review. A human sees clean SQL, a passing compilation check, QC evidence, and a populated description. The review is a confirmation, not an investigation.

The pipeline creates the work; the agent certifies it; the human approves it.

What This Enables

Anthropic reports 95% accuracy on business analytics queries with their semantic layer in place. We are building toward the same target. The gap log tells us exactly how much of our data estate still falls in the gap.

Self-service analytics fails, usually, not because the tools are wrong but because the layer between question and data has too many moving parts and no one owns all of them. The semantic layer is that layer, governed and versioned. When it works, the analyst stops being the person who knows which table to use and becomes the person who decides what to measure next. That is the shift we are building toward.

Built by the ConstructConnect Analytics Engineering Team. The semantic layer architecture described here was informed by Anthropic's published approach to self-service analytics with Claude, adapted for a multi-platform environment where no single data tool can serve as the authoritative source across Power BI, dbt, BigQuery, and AI agents simultaneously.