Text-to-SQL System with RAG-Enhanced Table Selection

pinterest · Tech · 2024 · source Agentic: no · promotional bias: low · source-backed · extracted with prompt 0.5.6 (schema v2)

Generated from extraction.json — do not edit by hand. Every claim cites verbatim source evidence; stated means the quote directly asserts the claim, implied means it supports it indirectly.

Problem

Domain: Large-scale data warehouse text-to-SQL

Pinterest wanted analysts to generate SQL from natural language in Querybook, but users had to navigate hundreds of thousands of tables and the initial LLM solution still required manual table selection.

Constraints:

The data warehouse contained hundreds of thousands of tables.
Users needed to choose relevant tables for the first implementation.
Benchmarks such as Spider were easier than Pinterest's real data environment.
The system had to keep humans in the loop for final confirmation.
Table retrieval needed to combine table and query summaries.

Architecture

Pinterest deployed Text-to-SQL in Querybook, first asking users to select tables, then adding RAG for automated table selection. The refined system indexes table metadata and query summaries, retrieves top candidate tables, uses an LLM to re-select the most relevant tables, then generates SQL for user confirmation.

Diagram confidence: reconstructed (reconstructed from prose).

flowchart LR
  querybook_ui["Querybook UI"] --> rag_table_retriever["RAG table retriever"]
  table_metadata_store["Table metadata store"] --> rag_table_retriever
  rag_table_retriever --> llm_table_re_selector["LLM table re-selector"]
  llm_table_re_selector --> sql_generation_llm["SQL generation LLM"]
  table_metadata_store --> sql_generation_llm
  sql_generation_llm --> querybook_ui

Drag to pan · scroll or use +/− to zoom · reconstructed from the source's described component topology

Components

Component	Role	Interacts with
`Querybook UI`	Open-source SQL query tool where analysts ask questions and review generated SQL.	`RAG table retriever`, `SQL generation LLM`
`RAG table retriever`	Searches table and query summaries to identify relevant tables.	`Table metadata store`, `LLM table re-selector`
`Table metadata store`	Stores schemas and summaries for warehouse tables.	`RAG table retriever`, `SQL generation LLM`
`LLM table re-selector`	Chooses the most relevant K tables from top retrieved candidates.	`RAG table retriever`, `SQL generation LLM`
`SQL generation LLM`	Generates SQL from question, dialect, and selected table schemas.	`Querybook UI`, `Table metadata store`, `LLM table re-selector`

Data flow

A user asks an analytical question in Querybook. The system retrieves table summaries and similar query context from a vector index, aggregates table scores, asks an LLM to choose the top relevant tables, compiles table schemas and dialect into a prompt, and streams SQL back to the user.

4 supporting quotes

“an initial LLM-based implementation that required users to specify tables, and a refined version incorporating Retrieval Augmented Generation (RAG) for automated table selection.”summary, stated

“The workflow began with the user asking an analytical question and selecting the tables to be used.”summary, stated

“After retrieving the top N tables from the vector index, Pinterest engages an LLM to choose the most relevant K tables”summary, stated

“keeping the human in the loop for final confirmation.”summary, stated

Why this architecture

The architecture adds RAG because table discovery, not just SQL syntax, is the hard part in a warehouse with hundreds of thousands of tables.

“addressing the challenge of navigating a large data warehouse with hundreds of thousands of tables.”summary, stated

“This NLP-based table search capability was also made available for general table search within Querybook”summary, stated

Models used: LLM · RAG table search

Scale

Pinterest's warehouse had hundreds of thousands of tables.
“navigating a large data warehouse with hundreds of thousands of tables.”summary, stated
AI assistance improved SQL task completion speed by 35%.
“achieved a 35% improvement in task completion speed for writing SQL queries with AI assistance”summary, stated
First-shot acceptance improved from 20% to over 40%.
“first-shot acceptance rates improving from 20% to over 40% as the system matured.”summary, stated

Alternatives considered (roads not taken)

Require users to manually specify tables

Rejected because: It left the biggest warehouse-navigation problem with the analyst.

“initial LLM-based implementation that required users to specify tables”summary, stated

Trust vector retrieval alone

Rejected because: Pinterest added an LLM re-selection step after retrieving top tables.

“After retrieving the top N tables from the vector index, Pinterest engages an LLM to choose the most relevant K tables”summary, stated

Patterns

`rag-retrieval` (schema retrieval)

Pinterest used RAG to retrieve relevant table context before SQL generation.

“incorporating Retrieval Augmented Generation (RAG) for automated table selection.”summary, stated

`human-in-the-loop-gate` (review)

The system keeps analysts in the loop to confirm generated SQL.

“Once tables are re-selected, they are returned to the user for validation before transitioning to the actual SQL generation stage, keeping the human in the loop for final confirmation.”summary, stated

Tradeoffs

Add RAG-based table selection.

Chose Automated table retrieval and LLM re-selection over Manual table specification.

Rationale: It improved usability in a warehouse with hundreds of thousands of tables.

“refined version incorporating Retrieval Augmented Generation (RAG) for automated table selection.”summary, stated

Gotchas (what bit them)

Academic text-to-SQL benchmarks understate warehouse complexity.

Root cause: Benchmarks often use a small number of pre-specified tables.

Resolution: Evaluate on internal user workflows and add RAG table selection.

“benchmark tasks were substantially easier than real-world problems their users face, particularly because benchmarks consider a small number of pre-specified ta”summary, stated

Evaluation

Pinterest evaluated against Spider, tracked first-shot acceptance, measured task completion speed, and compared real-world difficulty to benchmarks.

Metrics: 35% task completion speed improvement. · First-shot acceptance from 20% to over 40%. · Spider benchmark comparison.

“They tested against the Spider dataset and found comparable results, though they noted that benchmark tasks were substantially easier than real-world problems their users face, particularly because benchmarks consider a small number of pre-specified tables with few and well-labeled columns.”summary, stated

“The system was deployed in Querybook, Pinterest's open-source SQL query tool, and achieved a 35% improvement in task completion speed for writing SQL queries with AI assistance, with first-shot acceptance rates improving from 20% to over 40% as the system matured.”summary, stated

Outcomes

Text-to-SQL shipped inside Querybook.
Task completion speed improved 35%.
First-shot acceptance improved from 20% to over 40%.
RAG table search became useful beyond text-to-SQL.

Pinterest cites hundreds of thousands of warehouse tables and production deployment in Querybook.