Unified Context-Intent Embeddings for Scalable Text-to-SQL

Pinterest concluded that in an environment with 100,000+ analytics tables and 2,500+ analytics users, simple keyword search alone cannot build a reliable Text-to-SQL system. Even when table names or documentation descriptions look similar, the system fails to find answers unless it understands the actual analytical intent and the correct join patterns.

The core solution rests on two axes.

• Unified context-intent embeddings: Rather than simply summarizing past SQL, it converts them into natural language descriptions that capture the business question and analytical intent, then embeds them.
• Structural & statistical patterns: It extracts patterns from queries such as verified join keys, filters, aggregations, usage frequency, and success rate, and ranks them together with governance metadata like table tier, recency, and documentation quality.

Intent embeddings are built in three stages. First, context is enriched by injecting PinCat, glossary, metric definitions, and domain knowledge around the SQL. Next, an LLM converts the SQL into a summary, analytical questions, and detailed breakdown, restoring in text form "what this query was trying to solve." Finally, these natural language descriptions are vectorized to find past queries that are semantically similar to a user's new question.

For example, an ad performance SQL query gets transformed into something like "a query that calculates impressions, CPC, and CPM by keyword for a specific advertiser," and gets linked to questions like "top-performing keywords," "CPC," and "ad effectiveness." Temporary information such as dates or specific IDs is removed, while metric types and entity meaning are retained to increase reusability.

At the same time, the structural facts of the query are stored separately. Signals such as which tables are joined on which keys, which WHERE conditions are frequently used, which aggregation methods have been verified, whether the query succeeded or failed, and whether it has been used frequently and recently are all gathered to build reliability. This is combined with governance information like Tier 1/2/3, so that even when semantically similar, tables that are more trustworthy are prioritized over ones that are outdated or poorly documented.

As a result, the system becomes an Analytics Agent that doesn't just find "similar tables" but has learned both what is being asked and how to safely answer it. Pinterest summarizes this as "your analysts already wrote the perfect prompt." In other words, the SQL left behind by analysts is itself a library of domain knowledge, forming a self-reinforcing learning structure where that knowledge accumulates with every new query.

Documentation also needs automation. So Pinterest continuously enriches table and column descriptions using AI-generated table docs, lineage, glossary propagation, and search-based semantic matching, organizing its vast warehouse into a form that AI can use.