How we built Text-to-SQL at Pinterest

Adam Obeng, J.C. Zhong, Charlie Gu2 Apr 2024

Problem

Writing queries is the core task of Pinterest data users. Two things make it hard: finding the right data among a very large warehouse, and turning an analytical question into correct, efficient code. The team wanted an LLM assistant inside Querybook, their open source SQL tool. A basic version works only if the user already knows which tables to use, and users often do not. The warehouse holds hundreds of thousands of tables, so table choice is a real obstacle.

Example from the article: a question like "how many active users are on the 'web' platform" can produce where platform='web' when the real value is 'WEB'.

Solution in brief

Version one packs the question, the SQL dialect and the schemas of user-chosen tables into a prompt. Version two adds table search: an offline index of LLM-written table and query summaries is searched by embedding similarity, then an LLM narrows the candidates. The user confirms the tables before SQL is written.

Questions this case answers
  • How to generate correct queries over a warehouse with hundreds of thousands of tables when the user cannot name the tables to use
  • How to ground an LLM in real column values and schema size limits so generated filters and joins match the data
  • How to help analysts write correct SQL from a plain-language question inside the tool they already use?
  • How to find the right tables among hundreds of thousands in a data warehouse when the user does not know them?

Tools and methods

  • Text-to-SQL prompt — question, dialect and table schemas go to the LLM, which returns JSON with a query or an explanation
  • Low-cardinality column values — unique values of frequently filtered columns added to the schema
  • Table and query summaries — LLM-written descriptions of tables and of sample queries, embedded for search
  • OpenSearch — vector store with built-in similarity search
  • LangChain — partial JSON parsing for streaming the response
Continue reading — it's free

Google or a one-time email code. No password.