Don’t just index tables.  Index queries.

The table indexing trap

One of the most common design patterns for text-to-SQL today is table indexing. At setup time, the tool crawls your data warehouse, catalogs a ton of tables, columns, and data types, and stuffs that metadata into a vector store or markdown file. When a user asks a question, the system retrieves the most relevant schema fragments and hands them to a language model to generate SQL.

This sounds reasonable. It’s also insufficient:

• It scales poorly with the number of tables
• It encourages users to over-engineer their data model, by adding tables that are rarely used in practice
• It over-relies on metadata - it knows there’s a column named ‘State’ but has no idea if it uses “Florida” or “FL”
• It fails to capture the nature of joins, leading to fan- and chasm- traps

Does this scale?

A production data warehouse at any serious company has hundreds of tables, often thousands. Schema indexing treats all of them as equally valid candidates. When someone asks about "revenue," the retrieval layer might surface a raw Stripe transactions table, a dbt-transformed fct_orders model, an accounting reconciliation table, and a deprecated staging table that nobody has touched since 2022. The model now has to guess which one your organization actually trusts for revenue reporting. 

And it guesses wrong more often than vendors like to admit.

What query history actually encodes

Your data warehouse already contains a detailed record of how your organization thinks about its data: it lives in the query logs.

Every query that your analysts, dbt models, and BI dashboards run is an artifact of institutional knowledge. These queries encode which tables your team actually trusts for revenue reporting. They encode the specific join patterns between orders and line items that avoid double-counting. They encode the WHERE clauses that filter out test accounts, internal usage, and deprecated product lines. They encode the subqueries that transform raw columns into business metrics.

When you index query history instead of schema, you give the model a map of what matters. The model can see that 95% of revenue queries at your company hit fct_orders_daily rather than raw_stripe_charges. It can see that the join between orders and discounts always goes through an intermediate deduplication step. It can see that "revenue" in your organization means gross revenue minus returns minus credits, calculated in a specific subquery that your finance team validated two years ago.

To make this concrete: suppose a VP of Sales asks "What was our average deal size last quarter, excluding renewals, for enterprise accounts?" A schema-indexed tool sees deals.amount, deals.close_date, deals.type, and accounts.segment. It writes a reasonable-looking join, filters on date and type, and averages the amount. The result is $187,000.

A query-history-indexed tool has seen how your analysts actually compute deal size. It knows that deals.amount stores values in cents, not dollars. It knows that "enterprise" at your company means accounts.segment IN ('Enterprise', 'Strategic') because the Strategic segment was split out of Enterprise eighteen months ago and many stakeholders still use the old label. It knows that your team's convention is to exclude partner-sourced deals from average deal size calculations unless specifically asked. The result is $1,430, and it is correct.

The schema-indexed tool wasn’t dumb. It wrote valid SQL. It just lacked the context that turns valid SQL into correct answers.

The symmetric aggregate problem

We’ve all seen the traps to navigate whenever joining through a many-to-many relationship. Orders to discount codes, users to teams, products to categories. Naively aggregating a measure like revenue after the join inflates the result. Every row gets duplicated across the fan-out, and SUM happily adds up the duplicates. The correct approach is to compute the aggregate in a subquery before joining. Your experienced analysts know this. Their past queries reflect it. Nothing in a CREATE TABLE statement warns an AI about it.

This is why these errors are so dangerous: the resulting query runs without error and returns a number. It is just the wrong number.

‍

But Query indexing is also hard

At enterprise scale, the query logs themselves become a firehose. A large organization might generate millions of queries per month. Indexing all of them recreates the same signal-to-noise problem that schema indexing has, just at a different layer.

You need recency weighting so that last month's validated query patterns outrank the approach your team abandoned in 2023. You need role segmentation so that queries from the finance team inform revenue questions while queries from the product team inform usage questions. You need deduplication so that a scheduled dashboard query running every 15 minutes doesn't dominate the index just because it executes 3,000 times a month.

But here is the critical difference in how this scales. Schema indexing scales with the number of tables, which grows as your warehouse sprawls, often without bound and without correlation to business value. Query history indexing scales with the number of concepts your organization actually uses. A company might have 2,000 tables but only 150 distinct analytical patterns that matter. Indexing against concepts rather than infrastructure is a fundamentally better scaling curve.  This isn’t mathematically robust, but in practice it feels like linear versus logarithmic scaling.

‍

The Architecture That Works

At Zenlytic, we learned this the hard way. When we first built our AI layer, the obvious approach was to crawl the warehouse schema and use it as context. We had customers with Snowflake instances containing 1000+ tables across a dozen schemas. The model drowned.

The turning point was realizing the knowledge we needed wasn’t in the schema. It was in what people actually did with the schema. A single real query encodes which tables are actually used together, the correct join keys, which columns are dimensions versus measures in practice, and which aggregation patterns are standard.

Take a concrete example from one of our customers. Calculating deal stage duration requires joining DEALS_PROPERTY_HISTORY to DEALS to COMPANIES, parsing HubSpot's internal stage IDs through a specific CASE statement that maps them to human-readable names like "Pre-pilot" or "Negotiation," backfilling missing stage dates using window functions, and then computing intervals between stages. None of that logic is in the schema. The schema says: here are columns, here are types. The query history says: here is how these columns compose into the actual business concept of "average pilot duration."

So we built an architecture that indexes query history at setup time and retrieves it at runtime. When a user asks a question, we match against a concept index first. The LLM does not see 400 tables and guesses. It sees the 3-5 relevant patterns that historically answered similar questions, complete with correct joins, appropriate aggregations, and standard filters.

Schema still plays a role. You need it for column types, for discovering new tables, for validating that generated SQL is syntactically legal. But it’s only one piece of the essential context.

The result is a system that gets revenue right on the first try. Not because it has a better language model, but because it has better context about what "revenue" means at your company.

If you’re evaluating text-to-SQL tools, ask your vendor a simple question: are you indexing our schema, or are you indexing how our team actually uses our data? The answer will tell you everything about whether the tool will survive contact with your real-world users.

‍