How Twilio generated SQL utilizing Looker Modeling Language information with Amazon Bedrock

This put up is co-written with Aishwarya Gupta, Apurva Gawad, and Oliver Cody from Twilio.

At present’s main firms belief Twilio’s Buyer Engagement Platform (CEP) to construct direct, personalised relationships with their clients in every single place on the planet. Twilio allows firms to make use of communications and information so as to add intelligence and safety to each step of the shopper journey, from gross sales and advertising and marketing to development, customer support, and plenty of extra engagement use circumstances in a versatile, programmatic manner. Throughout 180 nations, hundreds of thousands of builders and tons of of 1000’s of companies use Twilio to create personalised experiences for his or her clients. As one of many largest AWS clients, Twilio engages with information, synthetic intelligence (AI), and machine studying (ML) companies to run their every day workloads.

Information is the foundational layer for all generative AI and ML functions. Managing and retrieving the suitable info might be advanced, particularly for information analysts working with massive information lakes and complicated SQL queries. To handle this, Twilio partnered with AWS to develop a digital assistant that helps their information analysts discover and retrieve related information from Twilio’s information lake by changing person questions requested in pure language to SQL queries. This digital assistant software makes use of Amazon Bedrock, a completely managed generative AI service that gives entry to high-performing basis fashions (FMs) and capabilities like Retrieval Augmented Technology (RAG). RAG optimizes language mannequin outputs by extending the fashions’ capabilities to particular domains or a company’s inside information for tailor-made responses.

This put up highlights how Twilio enabled pure language-driven information exploration of enterprise intelligence (BI) information with RAG and Amazon Bedrock.

Twilio’s use case

Twilio needed to supply an AI assistant to assist their information analysts discover information of their information lake. They used the metadata layer (schema info) over their information lake consisting of views (tables) and fashions (relationships) from their information reporting software, Looker, because the supply of reality. Looker is an enterprise platform for BI and information functions that helps information analysts discover and share insights in actual time.

Twilio carried out RAG utilizing Anthropic Claude 3 on Amazon Bedrock to develop a digital assistant software known as AskData for his or her information analysts. This software converts questions from information analysts requested in pure language (reminiscent of “Which desk accommodates buyer handle info?”) right into a SQL question utilizing the schema info out there in Looker Modeling Language (LookML) fashions and views. The analysts can run this generated SQL instantly, saving them the time to first determine the tables containing related info after which write a SQL question to retrieve the data.

The AskData software supplies ease of use and effectivity to its customers:

• Customers want correct details about the information in a fast and accessible method to make enterprise choices. Offering a software to attenuate their time spent discovering tables and writing SQL queries permits them to focus extra on enterprise outcomes and fewer on logistical duties.
• Customers usually attain out to the engineering assist channel after they have questions on information that’s deeply embedded within the information lake or if they will’t entry it utilizing varied queries. Having an AI assistant can cut back the engineering time spent in responding to those queries and supply solutions extra shortly.

Answer overview

On this put up, we present you a step-by-step implementation and design of the AskData software designed to function an AI assistant for Twilio’s information analysts. We talk about the next:

• Find out how to use a RAG strategy to retrieve the related LookML metadata akin to customers’ questions with the assistance of environment friendly information chunking and indexing and generate SQL queries from pure language
• Find out how to choose the optimum massive language mannequin (LLM) in your use case from Amazon Bedrock
• How analysts can question the information utilizing pure language questions
• The advantages of utilizing RAG for information evaluation, together with elevated productiveness and lowered engineering overhead of discovering the information (tables) and writing SQL queries.

This answer makes use of Amazon Bedrock, Amazon Relational Database Service (Amazon RDS), Amazon DynamoDB, and Amazon Easy Storage Service (Amazon S3). The next diagram illustrates the answer structure.

The workflow consists of the next steps:

1. An end-user (information analyst) asks a query in pure language in regards to the information that resides inside an information lake.
2. This query makes use of metadata (schema info) saved in Amazon RDS and dialog historical past saved in DynamoDB for personalised retrieval to the person’s questions:
   • The RDS database (PostgreSQL with pgvector) shops the LookML tables and views as embeddings which can be retrieved by way of a vector similarity search.
   • The DynamoDB desk shops the earlier dialog historical past with this person.
3. The context and pure language query are parsed by way of Amazon Bedrock utilizing an FM (on this case, Anthropic Claude 3 Haiku), which responds with a personalised SQL question that the person can use to retrieve correct info from the information lake. The next is the immediate template that’s used for producing the SQL question:

Human: The context info beneath represents the LookML information for Looker views and fashions. 
Utilizing this context information, please generate a presto SQL question that can return the proper end result for the person's query. 
Please present a SQL question with the proper syntax, desk names, and column names based mostly on the supplied LookML information.

<directions>

1. Use the proper underlying SQL desk names (desk title in sql_table_name) 
and column names (use column names from the scale of the view as they're the proper column names). 
Use the next for example:

{{instance redacted}}

2. Be part of tables as essential to get the proper end result. 
- Keep away from pointless joins if not explicitly requested by the person.

3. Keep away from pointless filters if not explicitly requested by the person.

4. If the view has a derived desk, use the derived question to reply query 
utilizing desk names and column names from derived question. Use the next for example:

{{instance redacted}}

5. The schema title is represented as <schema>.<table_name> inside the LookML views. 
Use the prevailing schema title or "public" because the schema title if no schema is specified.

</directions>

That is the chat historical past from earlier messages:

<chat_history>

{chat_history}

</chat_history>

<context>

{context}

</context>

That is the person query:

<query>

{query}

</query>

Assistant: Here's a SQL question for the person query:

The answer includes 4 important steps:

1. Use semantic search on LookML metadata to retrieve the related tables and views akin to the person questions.
2. Use FMs on Amazon Bedrock to generate correct SQL queries based mostly on the retrieved desk and examine info.
3. Create a easy net software utilizing LangChain and Streamlit.
4. Refine your present software utilizing strategic strategies reminiscent of immediate engineering, optimizing inference parameters and different LookML content material.

Conditions

To implement the answer, it is best to have an AWS account, mannequin entry to your selection of FM on Amazon Bedrock, and familiarity with DynamoDB, Amazon RDS, and Amazon S3.

Entry to Amazon Bedrock FMs isn’t granted by default. To realize entry to an FM, an AWS Identification and Entry Administration (IAM) person with ample permissions must request entry to it by way of the Amazon Bedrock console. After entry is supplied to a mannequin, it’s out there for the customers within the account.

To handle mannequin entry, select Mannequin entry within the navigation pane on the Amazon Bedrock console. The mannequin entry web page enables you to view an inventory of obtainable fashions, the output modality of the mannequin, whether or not you’ve gotten been granted entry to it, and the Finish Consumer License Settlement (EULA). You must assessment the EULA for phrases and circumstances of utilizing a mannequin earlier than requesting entry to it. For details about mannequin pricing, confer with Amazon Bedrock pricing.

Mannequin entry

Construction and index the information

On this answer, we use the RAG strategy to retrieve the related schema info from LookML metadata akin to customers’ questions after which generate a SQL question utilizing this info.

This answer makes use of two separate collections which can be created in our vector retailer: one for Looker views and one other for Looker fashions. We used the sentence-transformers/all-mpnet-base-v2 mannequin for creating vector embeddings and PostgreSQL with pgvector as our vector database. So long as the LookML file doesn’t exceed the context window of the LLM used to generate the ultimate response, we don’t break up the file into chunks and as an alternative cross the file in its entirety to the embeddings mannequin. The vector similarity search is ready to discover the proper recordsdata that comprise the LookML tables and views related to the person’s query. We are able to cross your complete LookML file contents to the LLM, benefiting from its massive context window, and the LLM is ready to decide the schemas for the related tables and views to generate the SQL question.

The 2 subsets of LookML metadata present distinct forms of details about the information lake. Views symbolize particular person tables, and fashions outline the relationships between these tables. By separating these elements, we will first retrieve the related views based mostly on the person’s query, after which use these outcomes to determine the related fashions that seize the relationships between the retrieved views.

This two-step process supplies a extra complete understanding of the related tables and their relationships to the person query. The next diagram exhibits how each subsets of metadata are chunked and saved as embeddings in several vectors for enhanced retrieval. The LookML view and mannequin info is introduced into Amazon S3 by way of a separate information pipeline (not proven).

Content material ingestion into vector db

Choose the optimum LLM in your use case

Deciding on the suitable LLM for any use case is important. Each use case has totally different necessities for context size, token measurement, and the power to deal with varied duties like summarization, job completion, chatbot functions, and so forth. Amazon Bedrock is a completely managed service that gives a selection of high-performing FMs from main AI firms like AI21 Labs, Anthropic, Cohere, Meta, Mistral, Stability AI, and Amazon inside a single API, together with a broad set of capabilities to construct generative AI functions with safety, privateness, and accountable AI.

This answer is carried out utilizing Anthropic Claude 3, out there by way of Amazon Bedrock. Anthropic Claude 3 is chosen for 2 important causes:

• Elevated context window – Anthropic Claude 3 can deal with as much as 200,000 tokens in its context, permitting for processing bigger LookML queries and tables. This expanded capability is essential when coping with advanced or intensive information, so the LLM has entry to the mandatory info for correct and knowledgeable responses to the person.
• Enhanced reasoning talents – Anthropic Claude 3 demonstrates enhanced efficiency when working with bigger contexts, enabling it to higher perceive and reply to person queries that require a deeper comprehension of the views, fashions, and their relationships. You’ll be able to achieve granular management over the reasoning capabilities utilizing a number of immediate engineering strategies.

Construct an online software

This answer makes use of LangChain and Streamlit to construct an online software and combine Amazon Bedrock into it. LangChain is a framework particularly designed to simplify the creation of functions utilizing LLMs, and it’s easy to make use of Amazon Bedrock by way of LangChain utilizing the Amazon Bedrock part out there in LangChain. We use Streamlit to develop the frontend for this net software.

For information analysts to effortlessly work together with and get queries to extract related information from their information lake, this answer implements a chat engine utilizing the ConversationalRetrievalChain mechanism, which lets you cross a customized vector retailer retriever, immediate, and dialog historical past to the LLM and generate personalised solutions to person questions. To retailer the chat historical past, we use DynamoDB with the person session ID as the first key. DynamoDB is a extremely scalable and sturdy NoSQL database service, enabling you to effectively retailer and retrieve chat histories for a number of person periods concurrently. The next screenshot exhibits an instance of the chat interface developed utilizing Streamlit.

AskData person interface

The next is an instance situation to get an thought of the person workflow when interacting with AskData:

Consumer Query - how do I discover a buyer handle?

AskData Response - Primarily based on the supplied LookML information, 
you will discover the shopper handle by querying the raw_mailing_addresses desk, 
which accommodates address-related info. 
Right here is the SQL question to retrieve the shopper handle:

SELECT
    account,
    address1,
    address2,
    metropolis,
    province,
    postal_code,
    iso_country
FROM
    raw_mailing_addresses

This question will return the account SID (which can be utilized to determine the shopper), the primary line of the handle, the second line of the handle (if any), the town, the province or state, the postal code, and the ISO nation code for all entries within the raw_mailing_addresses desk. When you’ve got a particular buyer’s account and also you wish to retrieve the handle for that buyer, you possibly can add a WHERE clause to the question:

SELECT
    account,
    address1,
    address2,
    metropolis,
    province,
    postal_code,
    iso_country
FROM
    raw_mailing_addresses
WHERE
    account="YourCustomerAccount" -- Substitute with the precise account

Substitute ‘YourCustomerAccount‘ with the precise account of the shopper whose handle you wish to discover.

Optimize the applying

Though utilizing an LLM to reply person questions on information is environment friendly, it comes with acknowledged limitations, reminiscent of the power of the LLM to generate inaccurate responses, typically because of hallucinated info. To reinforce the accuracy of our software and cut back hallucinations, we did the next:

• Set the temperature for the LLM to 0.1 to scale back the LLM’s propensity for overly artistic responses.
• Added directions within the immediate to solely generate the SQL question based mostly on the context (schema, chat historical past) being supplied within the immediate.
• Meticulously eliminated duplicate and redundant entries from the LookML information earlier than it was ingested into the vector database.
• Added a person expertise suggestions (a ranking from 1–5 with an non-compulsory textual content enter for feedback) as a part of the UI of AskData. We used the suggestions to enhance the standard of our information, prompts, and inference parameter settings.

Primarily based on person suggestions, the applying achieved a web promoter rating (NPS) of 40, surpassing the preliminary goal rating of 35%. We set this goal because of the following key components: the shortage of related info for particular person questions inside the LookML information, particular guidelines associated to the construction of SQL queries which may must be added, and the expectation that generally the LLM would make a mistake regardless of all of the measures we put in place.

Conclusion

On this put up, we illustrated tips on how to use generative AI to considerably improve the effectivity of information analysts. Through the use of LookML as metadata for our information lake, we constructed vector shops for views (tables) and fashions (relationships). With the RAG framework, we effectively retrieved pertinent info from these shops and supplied it as context to the LLM alongside person queries and any earlier chat historical past. The LLM then seamlessly generated SQL queries in response.

Our growth course of was streamlined thanks to varied AWS companies, significantly Amazon Bedrock, which facilitated the mixing of LLM for question responses, and Amazon RDS, serving as our vector shops.

Try the next sources to be taught extra:

Get began with Amazon Bedrock at this time, and depart your suggestions and questions within the feedback part.

In regards to the Authors

Apurva Gawad is a Senior Information Engineer at Twilio specializing in constructing scalable programs for information ingestion and empowering enterprise groups to derive precious insights from information. She has a eager curiosity in AI exploration, mixing technical experience with a ardour for innovation. Outdoors of labor, she enjoys touring to new locations, at all times searching for contemporary experiences and views.

Aishwarya Gupta is a Senior Information Engineer at Twilio targeted on constructing information programs to empower enterprise groups to derive insights. She enjoys to journey and discover new locations, meals, and tradition.

Oliver Cody is a Senior Information Engineering Supervisor at Twilio with over 28 years {of professional} expertise, main multidisciplinary groups throughout EMEA, NAMER, and India. His expertise spans all issues information throughout varied domains and sectors. He has targeted on creating revolutionary information options, considerably optimizing efficiency and decreasing prices.

Amit Arora is an AI and ML specialist architect at Amazon Net Providers, serving to enterprise clients use cloud-based machine studying companies to quickly scale their improvements. He’s additionally an adjunct lecturer within the MS information science and analytics program at Georgetown College in Washington D.C.

Johnny Chivers is a Senior Options Architect working inside the Strategic Accounts workforce at AWS. With over 10 years of expertise serving to clients undertake new applied sciences, he guides them by way of architecting end-to-end options spanning infrastructure, huge information, and AI.