April 15, 2024
Pinterest Engineering
Pinterest Engineering Blog

Adam Obeng | Information Scientist, Information Platform Science; J.C. Zhong | Tech Lead, Analytics Platform; Charlie Gu | Sr. Supervisor, Engineering

Writing queries to unravel analytical issues is the core activity for Pinterest’s information customers. Nevertheless, discovering the precise information and translating an analytical downside into appropriate and environment friendly SQL code might be difficult duties in a fast-paced atmosphere with important quantities of information unfold throughout completely different domains.

We took the rise in availability of Giant Language Fashions (LLMs) as a possibility to discover whether or not we might help our information customers with this activity by creating a Textual content-to-SQL function which transforms these analytical questions straight into code.

Most information evaluation at Pinterest occurs by means of Querybook, our in–home open supply huge information SQL question device. This device is the pure place for us to develop and deploy new options to help our information customers, together with Textual content-to-SQL.

The Preliminary Model: A Textual content-to-SQL Resolution Utilizing an LLM

The primary model included an easy Textual content-to-SQL resolution using an LLM. Let’s take a better have a look at its structure:

The person asks an analytical query, selecting the tables for use.

  1. The related desk schemas are retrieved from the desk metadata retailer.
  2. The query, chosen SQL dialect, and desk schemas are compiled right into a Textual content-to-SQL immediate.
  3. The immediate is fed into the LLM.
  4. A streaming response is generated and exhibited to the person.

Desk Schema

The desk schema acquired from the metadata retailer consists of:

  • Desk title
  • Desk description
  • Columns
  • Column title
  • Column kind
  • Column description

Low-Cardinality Columns

Sure analytical queries, resembling “what number of energetic customers are on the ‘internet’ platform”, could generate SQL queries that don’t conform to the database’s precise values if generated naively. For instance, the the place clause within the response may bewhere platform=’internet’ versus the proper the place platform=’WEB’. To handle such points, distinctive values of low-cardinality columns which might incessantly be used for this sort of filtering are processed and included into the desk schema, in order that the LLM could make use of this data to generate exact SQL queries.

Context Window Restrict

Extraordinarily giant desk schemas may exceed the standard context window restrict. To handle this downside, we employed just a few methods:

  • Diminished model of the desk schema: This consists of solely essential components such because the desk title, column title, and kind.
  • Column pruning: Columns are tagged within the metadata retailer, and we exclude sure ones from the desk schema based mostly on their tags.

Response Streaming

A full response from an LLM can take tens of seconds, so to keep away from customers having to attend, we employed WebSocket to stream the response. Given the requirement to return different data apart from the generated SQL, a correctly structured response format is essential. Though plain textual content is simple to stream, streaming JSON might be extra complicated. We adopted Langchain’s partial JSON parsing for the streaming on our server, after which the parsed JSON can be despatched again to the consumer by means of WebSocket.


Right here is the present prompt we’re utilizing for Text2SQL:

Analysis & Learnings

Our preliminary evaluations of Textual content-to-SQL efficiency have been principally carried out to make sure that our implementation had comparable efficiency with outcomes reported within the literature, on condition that the implementation principally used off-the-shelf approaches. We discovered comparable outcomes to these reported elsewhere on the Spider dataset, though we famous that the duties on this benchmark have been considerably simpler than the issues our customers face, specifically that it considers a small variety of pre-specified tables with few and well-labeled columns.

As soon as our Textual content-to-SQL resolution was in manufacturing, we have been additionally in a position to observe how customers interacted with the system. As our implementation improved and as customers grew to become extra conversant in the function, our first-shot acceptance fee for the generated SQL elevated from 20% to above 40%. In follow, most queries which are generated require a number of iterations of human or AI technology earlier than being finalized. With a purpose to decide how Textual content-to-SQL affected information person productiveness, probably the most dependable technique would have been to experiment. Utilizing such a way, earlier analysis has found that AI help improved activity completion pace by over 50%. In our actual world information (which importantly doesn’t management for variations in duties), we discover a 35% enchancment in activity completion pace for writing SQL queries utilizing AI help.

Whereas the primary model carried out decently — assuming the person is conscious of the tables to be employed — figuring out the proper tables amongst the a whole bunch of 1000’s in our information warehouse is definitely a major problem for customers. To mitigate this, we built-in Retrieval Augmented Era (RAG) to information customers in deciding on the precise tables for his or her duties. Right here’s a evaluation of the refined infrastructure incorporating RAG:

  1. An offline job is employed to generate a vector index of tables’ summaries and historic queries in opposition to them.
  2. If the person doesn’t specify any tables, their query is remodeled into embeddings, and a similarity search is carried out in opposition to the vector index to deduce the highest N appropriate tables.
  3. The highest N tables, together with the desk schema and analytical query, are compiled right into a immediate for LLM to pick the highest Ok most related tables.
  4. The highest Ok tables are returned to the person for validation or alteration.
  5. The usual Textual content-to-SQL course of is resumed with the user-confirmed tables.

Offline Vector Index Creation

There are two kinds of doc embeddings within the vector index:

  • Desk summarization
  • Question summarization

Desk Summarization

There’s an ongoing desk standardization effort at Pinterest so as to add tiering for the tables. We index solely top-tier tables, selling the usage of these higher-quality datasets. The desk summarization technology course of includes the next steps:

  1. Retrieve the desk schema from the desk metadata retailer.
  2. Collect the latest pattern queries using the desk.
  3. Primarily based on the context window, incorporate as many pattern queries as doable into the desk summarization immediate, together with the desk schema.
  4. Ahead the immediate to the LLM to create the abstract.
  5. Generate and retailer embeddings within the vector retailer.

The desk abstract consists of description of the desk, the information it incorporates, in addition to potential use eventualities. Right here is the present prompt we’re utilizing for desk summarization:

Question Summarization

Moreover their function in desk summarization, pattern queries related to every desk are additionally summarized individually, together with particulars such because the question’s function and utilized tables. Right here is the prompt we’re utilizing:

NLP Desk Search

When a person asks an analytical query, we convert it into embeddings utilizing the identical embedding mannequin. Then we conduct a search in opposition to each desk and question vector indices. We’re utilizing OpenSearch because the vector retailer and utilizing its inbuilt similarity search capability.

Contemplating that a number of tables might be related to a question, a single desk might seem a number of instances within the similarity search outcomes. Presently, we make the most of a simplified technique to mixture and rating them. Desk summaries carry extra weight than question summaries, a scoring technique that might be adjusted sooner or later.

Apart from getting used within the Textual content-to-SQL, this NLP-based desk search can also be used within the common desk search in Querybook.

Desk Re-selection

Upon retrieving the highest N tables from the vector index, we have interaction an LLM to decide on probably the most related Ok tables by evaluating the query alongside the desk summaries. Relying on the context window, we embody as many tables as doable within the immediate. Right here is the prompt we’re utilizing for the desk re-selection:

As soon as the tables are re-selected, they’re returned to the person for validation earlier than transitioning to the precise SQL technology stage.

Analysis & Learnings

We evaluated the desk retrieval part of our Textual content-to-SQL function utilizing offline information from earlier desk searches. This information was inadequate in a single necessary respect: it captured person conduct earlier than they knew that NLP-based search was obtainable. Due to this fact, this information was used principally to make sure that the embedding-based desk search didn’t carry out worse than the prevailing text-based search, moderately than trying to measure enchancment. We used this analysis to pick a way and set weights for the embeddings utilized in desk retrieval. This strategy revealed to us that the desk metadata generated by means of our information governance efforts was of great significance to general efficiency: the search hit fee with out desk documentation within the embeddings was 40%, however efficiency elevated linearly with the burden positioned on desk documentation as much as 90%.

Whereas our currently-implemented Textual content-to-SQL has considerably enhanced our information analysts’ productiveness, there’s room for enhancements. Listed here are some potential areas of additional improvement:

NLP Desk Search

Presently, our vector index solely associates with the desk abstract. One potential enchancment might be the inclusion of additional metadata resembling tiering, tags, domains, and so on., for extra refined filtering throughout the retrieval of comparable tables.

  • Scheduled or Actual-Time Index Replace

Presently the vector index is generated manually. Implementing scheduled and even real-time updates each time new tables are created or queries executed would improve system effectivity.

  • Similarity Search and Scoring Technique Revision

Our present scoring technique to mixture the similarity search outcomes is moderately fundamental. Positive-tuning this side might enhance the relevance of retrieved outcomes.

Question validation

At current, the SQL question generated by the LLM is straight returned to the person with out validation, leaving a possible danger that the question could not run as anticipated. Implementing question validation, maybe utilizing a constrained beam search, might present an additional layer of assurance.

Consumer suggestions

Introducing a person interface to effectively acquire person suggestions on the desk search and question technology outcomes might provide helpful insights for enhancements. Such suggestions might be processed and included into the vector index or desk metadata retailer, finally boosting system efficiency.


Whereas engaged on this mission, we realized that the efficiency of text-to-SQL in an actual world setting is considerably completely different to that in present benchmarks, which have a tendency to make use of a small variety of well-normalized tables (that are additionally prespecified). It will be useful for utilized researchers to provide extra life like benchmarks which embody a bigger quantity of denormalized tables and deal with desk search as a core a part of the issue.

To study extra about engineering at Pinterest, try the remainder of our Engineering Weblog and go to our Pinterest Labs website. To discover and apply to open roles, go to our Careers web page.