How to Build a Semantic Layer From SQL Query History
A bottom-up alternative to months of workshops: recover the metrics, filters and joins your team already agrees on, from the queries they already run.
- Most semantic layer projects stall at the blank page. The platform is installed in a week, then nobody can say which of the six revenue definitions is the real one.
- Query history is the only record of how a business actually measures itself. It is behavioral evidence, not documentation, so it does not go stale and nobody has to remember to update it.
- The method has six steps: collect history, rank by real usage, extract candidate metrics and filters, resolve conflicts with the owners, express the result in a portable format, then keep it current as new queries arrive.
- This is no longer a fringe idea. Snowflake ships Semantic View Autopilot and DataHub Cloud ships Context Intelligence, both mining query history. The open question is not whether the method works, it is whether your semantic layer is allowed to leave the platform that generated it.
- Query history tells you what is measured and how. It cannot tell you what should be measured, and it inherits every bad habit in the warehouse, so a human review step is not optional.
Why semantic layer projects stall
Buying a semantic layer is easy. Snowflake, dbt, Databricks, Cube and AtScale will all have you running inside a week. What none of them ship is your actual business logic, and that is the part that takes a year.
The usual approach is top-down. Convene the stakeholders, agree the metric definitions, write them into the modeling layer. In practice the first workshop discovers that finance, sales operations and the executive dashboard each compute revenue differently, all three have been correct in their own context for years, and nobody present has the authority to declare a winner. The project becomes a negotiation. Six months later the layer covers eleven metrics and the business has moved on.
The bottom-up approach inverts it. Instead of asking people to remember and agree on definitions in a meeting, you go and read what they have already been doing, at scale, and bring the disagreements to the table as evidence rather than opinion.
Where the definitions already are
Every warehouse keeps a query log. Snowflake has QUERY_HISTORY, BigQuery has INFORMATION_SCHEMA.JOBS, Databricks has the query history API, Redshift has STL_QUERY. That log is a record of every question the business asked, who asked it, how often, and precisely how it was answered in SQL.
Think about what a single recurring query contains. The SELECT carries the metric formula. The WHERE carries the business rule, the filter that decides what counts as an active customer or a completed order. The JOIN carries the relationship between entities and the grain the calculation is valid at. The fact that it ran four hundred times last quarter, under three different analysts, is the strongest possible evidence that this is the definition the business actually operates on.
None of this is in the data catalog, which inventories tables and columns. Very little of it is in Confluence, which is where documentation goes to expire. It is in the SQL, and the SQL is the one artifact that cannot drift from reality, because if it drifted the report would break and someone would fix it.
This is the distinction worth holding on to: a data catalog tells you what data exists. A semantic layer tells you what it means. Query history is the only large-scale record of the second one, and almost every organization already has years of it sitting unused.
What query history can and cannot tell you
Being honest about the limits is what separates a usable method from a sales pitch. Query history is evidence of behavior, and behavior includes bad habits.
| Query history answers this well | It does not answer this |
|---|---|
| How is this metric calculated today? The formula appears verbatim, often in hundreds of executions. | How should it be calculated? Consensus is not correctness. A widely repeated mistake still looks like a standard. |
| Which definition is dominant? Execution counts and distinct users rank the variants by real usage. | Which definition is authoritative? That is a governance decision a person has to make. |
| Where do definitions conflict? Competing filters on the same entity surface immediately. | Why they conflict. The reasoning usually lives in someone's head or an old Slack thread. |
| Who owns which logic? Authorship concentration is directly measurable, which is also how key-person risk shows up. | What is missing. A metric nobody has ever queried leaves no trace at all. |
| Which joins are safe? Frequently executed join paths are empirically validated by the fact that people trust their output. | Whether a join is semantically correct. Frequency is strong evidence, not proof. |
The practical consequence: extraction is automatable, adjudication is not. Any method that claims to skip the human review step is selling you a catalog of your own bad habits with a confidence score attached.
The method, step by step
- Collect the history. Three to twelve months is the useful window. Fewer than three and you miss quarterly and annual logic; more than twelve and you are mostly reading definitions that have since been retired. Volume matters more than beauty: ten thousand queries is a reasonable floor for a mid-sized analytics team. You need the query text, the user, the timestamp and the execution status. You do not need the query results, and you should not export them.
- Filter to signal. Most of a query log is noise: failed executions, ad-hoc exploration, SELECT * previews, dbt and BI tool boilerplate firing on a schedule. Drop the failures, separate machine-generated traffic from human-authored traffic, and keep what a person actually wrote and got an answer from.
- Rank by real usage. Before extracting anything, sort by how often a pattern recurs and how many distinct people run it. A calculation that eleven analysts have run over eight months is a business definition. One that a single person ran twice in March is a one-off. Ranking first is what stops the output being ten thousand equally weighted fragments.
- Extract candidates. For each recurring pattern, pull out the measure and its formula, the filter conditions that qualify the entity, the join paths and the grain, and the identifiers involved. Group variants of the same underlying measure together rather than treating each spelling as a separate metric. What comes out is a candidate list, not a finished model.
- Adjudicate the conflicts. This is the step that needs people, and it is much faster than a blank-page workshop because the question has changed. You are no longer asking "how should we define active customer?" You are asking "here are the four definitions in use, ranked by frequency, with the owner of each. Which is canonical, and what are the others?" That is a thirty-minute decision instead of a six-month negotiation.
- Express it portably, then keep it current. Write the agreed definitions into a vendor-neutral format so they can be loaded into whichever platform needs them, and re-run the extraction each quarter. New queries reveal new logic and catch drift, so the layer stays a live description of the business rather than a snapshot of the month it was built.
Bottom-up against top-down
Neither approach is wrong. They fail differently, and knowing how is most of the decision.
| Top-down modeling | Mining query history | |
|---|---|---|
| Starting point | A blank file and a stakeholder calendar | Years of executed SQL |
| Time to first coverage | Months, and it tracks the number of stakeholders | Weeks, and it tracks the volume of history |
| Coverage | Whatever the loudest teams asked for | Whatever the business actually measures |
| Main failure mode | Stalls in negotiation, then ships a layer nobody uses | Faithfully reproduces existing bad habits if nobody reviews |
| Staying current | Manual, and the first thing dropped under pressure | Re-run the extraction, since the source updates itself |
| Blind spot | What people forgot to mention | What has never been queried |
In practice the two combine well. Mining gives you most of the layer as a first draft in weeks; top-down work is then spent on the genuinely contested definitions and on the metrics that ought to exist but never have.
Who else mines query history
Two years ago this was a contrarian argument. It is not any more, and you should know the landscape before choosing an approach.
Snowflake Semantic View Autopilot, launched in 2026, mines roughly ninety days of query history from Snowsight, dashboards, Tableau and Power BI, and proposes verified queries, metrics and facts, reusable filters, and implicit join relationships for a human to validate. It is a serious implementation and it works only inside Snowflake.
DataHub Cloud Context Intelligence mines query logs together with BI dashboards and dbt models, producing context documents that capture business intent, metric definitions and validated join logic, including detection of cases where teams calculate the same metric differently.
Snowflake shipping this is the strongest possible validation that query history is where the definitions live. It also changes what the real question is. The method is settled. What is not settled is whether the semantic layer you build is allowed to leave the platform that generated it.
| Platform-native extraction | Cross-platform extraction | |
|---|---|---|
| Sees | The query history held by that one platform | History exported from any warehouse, including more than one |
| Output lives | In that platform's own semantic object | In a vendor-neutral model, translated into each platform's format |
| If you move warehouse | The work is re-done | The model moves with you |
| Feeding tools outside the warehouse | Whatever that vendor chooses to expose | The same model feeds BI, agents and RAG directly |
| Multi-warehouse estates | One extraction per platform, with no shared definition | One model spanning them |
| Where processing happens | In the vendor's cloud | Can run inside your own environment, offline if required |
If your estate is entirely Snowflake and you expect it to stay that way, the native tool is the path of least resistance and you should use it. If your definitions have to serve Databricks as well, or a BI tool, or an agent that never touches the warehouse, then the constraint that matters is portability, not extraction.
That is the approach we take at Momenta. Neuron runs as a single hardened Docker image inside your own environment, reads exported query text from whichever warehouse produced it, and emits an Open Semantic Interchange model alongside native Snowflake, Databricks and dbt formats, so the same definitions are readable everywhere without being rebuilt per platform.
What a finished layer contains
Whatever tooling you use, judge the output on whether it contains these four things. A list of table names is not a semantic layer.
A metrics library. Every measure with its SQL formula, the owner, how often it is used, and an honest confidence score reflecting how strong the evidence is. Confidence matters: a metric seen twice deserves less trust than one seen four hundred times, and burying that distinction is how a semantic layer starts lying.
A business rules library. The filter logic, separated from the metrics. This is the most consistently undocumented asset in any warehouse, and it is where AI agents most often go wrong, because the model has no way to guess that your definition of an active account excludes internal test tenants.
A relationship map. The join paths actually in use, with their grain, so that a generated query aggregates at a level that is valid.
A portable export. The same definitions emitted in the formats your platforms consume, rather than locked in one vendor's modeling syntax. This is exactly what the Open Semantic Interchange specification was created for, and it is the difference between a semantic layer you own and one you rent.
Is your query history good enough?
Four checks, all answerable before you commit to anything:
Volume. Ten thousand or more successful, human-authored queries over the window. Below that, recurrence is too weak to separate definitions from one-offs.
Span. At least three months, preferably covering a quarter close, so period logic appears.
Breadth. More than a handful of authors. If ninety percent of queries come from two people, you will learn a great deal about those two people, and you have discovered a key-person problem that matters more than the semantic layer.
Retention. Check how long your platform keeps history before you plan around it. Snowflake's ACCOUNT_USAGE.QUERY_HISTORY retains a year; the INFORMATION_SCHEMA view retains far less. Teams routinely discover the window they wanted has already closed.
FAQ
Can you really build a semantic layer automatically from query logs? You can build most of one. Extraction of metric formulas, filter conditions and join paths is mechanical and scales well. Deciding which of several competing definitions is canonical is a governance judgment that needs an owner. Every serious implementation, including Snowflake's own, proposes candidates for a human to validate rather than publishing them unreviewed.
How is this different from Snowflake Semantic View Autopilot? The method is broadly the same and Autopilot is a good implementation of it. The difference is scope. Autopilot mines Snowflake's query history and produces a Snowflake semantic view. A cross-platform approach reads exported query text from any warehouse and emits a vendor-neutral model that is then translated into Snowflake, Databricks, dbt or OSI. If you are Snowflake-only and expect to stay that way, use Autopilot. If your definitions have to serve anything outside Snowflake, portability is the deciding factor.
How much query history do I need? Three to twelve months, and ideally at least ten thousand successful human-written queries. The window matters as much as the volume: you want at least one full reporting cycle so that quarterly and year-end logic shows up rather than being invisible.
Does this require access to our actual data? No, and it should not. The method reads query text, not query results. The logic lives in the SQL itself, so the underlying rows never need to leave your environment. Any vendor asking for the data as well as the queries is doing something other than this.
What if our SQL is messy? Messy is normal and mostly fine, because the method relies on recurrence rather than elegance. The real risk is not ugly SQL, it is a warehouse where an incorrect pattern has been copied widely enough to look authoritative. That is what the human review step exists to catch, and it is why confidence scores and usage counts should be visible in the output rather than hidden.
How is this different from a data catalog? A data catalog inventories what data exists: tables, columns, owners, lineage. A semantic layer built from query history captures what the data means: the formulas, filters and relationships that turn columns into business measures. The two are complementary, and the catalog cannot substitute for the second one.
How long does the whole thing take? With the history already available, a first pass runs in weeks rather than months. In our own engagements it is typically four to six weeks from the export to the final deliverables, with something like eight to sixteen hours of the client team's time: one export, one working session on the contested definitions, one review.
What format should the output be in? A vendor-neutral one, then translated into whatever your platforms need. Open Semantic Interchange exists precisely so that a metric defined once can be read by Snowflake, dbt, Databricks and BI tools without being rewritten for each. Writing the canonical version directly into one vendor's syntax is how organizations end up doing this work twice.
Will this make our AI agents more accurate? It addresses the most common reason they are wrong. A text-to-SQL agent pointed at raw tables has to invent a definition of revenue, and it will invent a plausible one. Given a governed metrics and business rules library, it selects from definitions the business has already agreed on. That is the difference between an agent with database access and an agent with business understanding.
Your semantic layer does not have to start empty, and the industry now agrees the definitions are in your query history. The remaining question is whether the layer you build can leave the platform that built it.