KOEN
Read Original
#AI

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

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

Summary

Your Analysts Already Wrote the Perfect Prompt: Pinterest's Scalable Text-to-SQL Evolution

Beyond simple RAG: Encoding years of analytical intent and structural patterns into a production-ready knowledge base.

Explore how Pinterest solved the fundamental challenges of natural language data analysis in a massive data warehouse. By treating query history as a library of expert knowledge rather than raw syntax, they've built a system that understands business intent and delivers validated, trustworthy SQL results.

  • 01Unified Context-Intent Embeddings that bridge the gap between user questions and historical SQL logic.
  • 02Governance-aware ranking that fuses semantic similarity with data quality signals like table tiers.
  • 03Automated documentation propagation using AI and join-based lineage, cutting manual work by 70%.
  • 04Internal Vector DB as a Service built on AWS OpenSearch for scalable infrastructure.
  • 05A self-reinforcing learning cycle where every new expert query improves the entire system's accuracy.

RECOMMENDATION

Essential reading for Data Engineers and AI Practitioners looking to deploy production-grade RAG systems in complex enterprise data environments.

The Problem

Pinterest faced challenges in generating accurate SQL at scale, with over 100,000 tables and thousands of users, where simple keyword matching and basic table summaries failed to capture complex analytical intent. Standard LLM approaches lacked domain-specific context, validated join patterns, and awareness of data governance, leading to unreliable query generation.

The Solution

The team developed a production Analytics Agent using 'Unified Context-Intent Embeddings' that transform historical SQL queries into natural language business questions enriched with domain context. This was combined with structural/statistical patterns extracted from query logs and a governance-aware ranking system to prioritize trustworthy, high-tier tables during retrieval.

The Result

The system became the most widely adopted agent at Pinterest, reducing manual documentation effort by 70% through AI-generated docs and lineage propagation. Over 75% of AI-generated table descriptions were rated as 'usable' or better by human analysts.

Trade-off

The effectiveness of this approach is highly dependent on an established data governance framework (like table tiering) and a rich history of expert queries, suggesting it may not be as effective for organizations with low data maturity or sparse query logs.

03

Key Concepts

Concept · 01

Unified Context-Intent Embeddings

A methodology that transforms raw SQL syntax into semantically rich natural language descriptions capturing the underlying business purpose.

  • Translates SQL into high-level summaries and potential analytical questions.
  • Injects Pinterest-specific domain context like metrics and glossary terms before embedding.
Concept · 02

Governance-aware Ranking

A retrieval mechanism that combines semantic search scores with trust signals such as table tiers, data freshness, and documentation quality.

  • Prioritizes high-quality Tier-1 tables over undocumented or legacy data.
  • Integrates query success rates and usage frequency into the ranking algorithm.
Concept · 03

Join-Based Lineage Propagation

An automated documentation technique that infers column semantics by analyzing how tables are joined in query history.

  • Propagates glossary terms from well-documented columns to undocumented ones via a join graph.
  • Reduces manual tagging effort by automatically identifying business concepts across thousands of columns.