Connect an AI assistant to your data and ask it a simple question: which of our key accounts have gone quiet? You will usually get an answer. It will be fluent, well formatted and confident. Whether it is right depends far less on the model than on what the model can see.
Most AI analytics projects stall in the same place. The model is capable. The data underneath is scattered across tools, inconsistently named and undocumented, so the AI has to guess what “revenue” means, which table holds customers and how an invoice relates to an account. Guesses produce plausible wrong answers, and a plausible wrong answer does more damage than no answer at all.
The fix is a semantic data foundation: a layer that tells people, dashboards and AI tools what the data means, once, in one place.
What a semantic layer is
A semantic layer sits between your raw data and anything that reads it. It turns tables and columns into business concepts:
- Entities. One definition of a customer, an account, an order, an invoice.
- Relationships. Which invoice belongs to which account; which contact works for which business.
- Metrics. The agreed calculation for revenue, repeat rate or days to pay, including what is left out.
- Descriptions. Plain-English notes on what each table and column holds: units, time range, quirks.
None of this is new. Good BI teams have built semantic layers for years, in LookML, dbt or Power BI semantic models, so that two dashboards don’t disagree about revenue. What has changed is the reader. A person can ask a colleague what a column means. An AI model can’t. It reads your metadata literally and acts on it at speed, so whatever ambiguity your data has, the AI will find it.
What we measured
We tested this on our own live demo: a fictional UK coffee roaster whose orders, invoices, CRM and web analytics live in Shopify, Xero, HubSpot and Google Analytics. We joined them into one model in Metabase, switched on its built-in AI assistant, and asked it a fixed bank of 12 business questions with known answers. They were the questions an owner or sales lead actually asks: which wholesale accounts dropped most this quarter, how long each region takes to pay, which marketing channels bring customers who come back.
Then we improved the semantic layer and asked the same questions again. We didn’t change the model, the prompts or the tool.
Before, the assistant fully answered 4 of the 12. After, it answered 8.
It’s a small test on synthetic data, and the second round used one run per question rather than three, so read it as directional. But the direction was clear, and every improvement traced back to a specific change in the data layer.
Five changes that made the difference
1. One identity for each real-world thing
The roaster’s café customers existed three times: as a Shopify customer, a Xero contact and a HubSpot company, each with its own ID and a slightly different name. Until those records are resolved into one account, “revenue by account” splits or double-counts, and an AI has no way to know that “Green Fern Coffee Ltd” and “green fern coffee” are the same business.
Identity resolution, matching records across systems on emails, company domains, normalised names and addresses, is the least glamorous part of the foundation and the most important. Everything else hangs off it.
2. Declare relationships, and test them
At first the assistant couldn’t say how many days each region takes to pay its invoices. By its own diagnosis, it didn’t know that invoices linked to accounts, because that relationship had never been declared anywhere it could read. Once every fact table had a declared, tested link to the things it describes, and “average days to pay” was defined (see point 4), it answered correctly.
A relationship that only exists in an analyst’s head doesn’t exist for an AI.
3. Build tables shaped like the questions people ask
AI assistants are good at finding the right table and filtering it. They are much weaker at multi-step arithmetic across rows: comparing this quarter with last, working out a ratio, finding the accounts that didn’t order.
Asked which wholesale accounts dropped most between two quarters, the assistant fetched the right revenue for both quarters and stopped there, leaving 478 unsorted rows. We added a table of revenue by account by quarter with the change already worked out. The same question then returned the 20 accounts ranked by their drop, each with its current sales rep.
Question-shaped tables for your most frequent questions are one of the cheapest wins available.
4. Define each metric once
“Repeat rate” can be calculated half a dozen ways. So can “days to pay”: paid invoices only? From the invoice date or the due date? When we defined these as named metrics with the rules written in, the assistant used them directly instead of improvising. On the channel question, its answer matched our reference exactly.
A governed metric is a decision your business makes once, rather than a decision every analyst, dashboard and AI tool makes separately and differently.
5. Write descriptions for a reader with no context
Descriptions are how an AI finds and interprets your data. On almost every question, the assistant’s first step was a search that found the right table by its description, and its caveats (“revenue is ex-VAT”, “the owner shown is the current rep”) came straight from our column notes.
Three habits matter most:
- State units and exclusions. Currency, tax, refunds, test orders.
- State the time range each table covers. Without it, the assistant told us the order data ran from October 2023 to May 2024. It had only sampled the earliest rows.
- Say what a column is for, and where it is blind. A ranking of top accounts over the last 12 months quietly drops an account that stopped ordering a year ago, which is exactly the account you want to find. We added each account’s rank in the 12 months before that, described as “key accounts a year ago”, so questions about lost customers have something to stand on.
Measure it, don’t assume it
The most useful thing we built wasn’t a table. It was the test: a fixed bank of real questions, each with a reference answer written in SQL, scored automatically before and after every change.
It turns “the AI seems better” into “the AI answers 8 of 12, and here are the four it still gets wrong, and why”. It shows where to invest next, and it catches regressions when the data changes underneath.
If you’re evaluating an AI analytics tool, start here. Write down the 20 questions your team actually asks, with the right answers. Then measure.
Where AI still needs guard rails
A semantic layer raises the floor. It doesn’t make AI infallible.
- Failures can be silent. In our tests, a dropped network connection sometimes produced an empty answer with no error message. Monitor for it.
- Keep reported numbers deterministic. Our written briefings use rule-based SQL checks to find what changed and an AI model only to write it up. Before anything is published, every number in the text is checked against the data.
- Limit what the AI can touch. An assistant that writes SQL should do it through a read-only login that sees only the curated layer, with a time limit on queries.
A starting checklist
- Resolve identities for your core entities: customers, accounts, products.
- Declare and test every relationship between facts and the things they describe.
- Build question-shaped tables for your ten most frequent questions.
- Define key metrics once, with exclusions written in.
- Describe every table and column: meaning, units, time range, blind spots.
- Give AI read-only access to the curated layer, never the raw tables.
- Write a question bank with reference answers, and measure before and after.
None of this needs a new platform. It works in the warehouse and BI tools you probably already have, whether that’s BigQuery, Snowflake, Databricks or Postgres underneath and Power BI, Looker or Metabase on top.
See it working
Our live demo shows the result: a coffee roaster’s orders, web analytics and CRM joined into one model, with a dashboard, AI-written briefings and an AI you can ask questions.
If your data isn’t ready for AI yet, you’re in good company. Book a free scoping call and we’ll tell you honestly where to start.