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

This publish 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 this planet. Twilio allows firms to make use of communications and information so as to add intelligence and safety to each step of the client 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 international locations, thousands and thousands of builders and a whole bunch 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) providers to run their every day workloads.

Knowledge is the foundational layer for all generative AI and ML purposes. Managing and retrieving the suitable info may 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 consumer 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 corporation’s inner information for tailor-made responses.

This publish 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 offer 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 fact. Looker is an enterprise platform for BI and information purposes that helps information analysts discover and share insights in actual time.

Twilio applied RAG utilizing Anthropic Claude 3 on Amazon Bedrock to develop a digital assistant software referred to as AskData for his or her information analysts. This software converts questions from information analysts requested in pure language (reminiscent of “Which desk comprises buyer handle info?”) right into a SQL question utilizing the schema info obtainable in Looker Modeling Language (LookML) fashions and views. The analysts can run this generated SQL straight, saving them the time to first establish 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 info in a fast and accessible method to make enterprise selections. Offering a software to reduce their time spent discovering tables and writing SQL queries permits them to focus extra on enterprise outcomes and fewer on logistical duties.
• Customers sometimes attain out to the engineering help channel once they have questions on information that’s deeply embedded within the information lake or if they will’t entry it utilizing numerous 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 publish, 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 focus on the next:

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

This resolution 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 concerning the information that resides inside a knowledge 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 consumer’s questions:
   • The RDS database (PostgreSQL with pgvector) shops the LookML tables and views as embeddings which can be retrieved via a vector similarity search.
   • The DynamoDB desk shops the earlier dialog historical past with this consumer.
3. The context and pure language query are parsed via Amazon Bedrock utilizing an FM (on this case, Anthropic Claude 3 Haiku), which responds with a personalised SQL question that the consumer can use to retrieve correct info from the info 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 may return the proper end result for the consumer's query. 
Please present a SQL question with the proper syntax, desk names, and column names based mostly on the offered LookML information.

<directions>

1. Use the proper underlying SQL desk names (desk identify 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 a part of tables as essential to get the proper end result. 
- Keep away from pointless joins if not explicitly requested by the consumer.

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

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 identify is represented as <schema>.<table_name> inside the LookML views. 
Use the present schema identify or "public" because the schema identify 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 consumer query:

<query>

{query}

</query>

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

The answer includes 4 principal steps:

1. Use semantic search on LookML metadata to retrieve the related tables and views similar to the consumer 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 internet software utilizing LangChain and Streamlit.
4. Refine your current 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 achieve entry to an FM, an AWS Identification and Entry Administration (IAM) consumer with enough permissions must request entry to it via the Amazon Bedrock console. After entry is offered to a mannequin, it’s obtainable 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 allows you to view a listing of obtainable fashions, the output modality of the mannequin, whether or not you might have been granted entry to it, and the Finish Person License Settlement (EULA). You need to overview the EULA for phrases and circumstances of utilizing a mannequin earlier than requesting entry to it. For details about mannequin pricing, seek advice from Amazon Bedrock pricing.

Mannequin entry

Construction and index the info

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

This resolution 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 cut up the file into chunks and as an alternative go the file in its entirety to the embeddings mannequin. The vector similarity search is ready to discover the proper recordsdata that include the LookML tables and views related to the consumer’s query. We are able to go all the LookML file contents to the LLM, profiting 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 varieties of details about the info lake. Views characterize particular person tables, and fashions outline the relationships between these tables. By separating these parts, we will first retrieve the related views based mostly on the consumer’s query, after which use these outcomes to establish 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 consumer query. The next diagram reveals 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 via a separate information pipeline (not proven).

Content material ingestion into vector db

Choose the optimum LLM in your use case

Choosing the suitable LLM for any use case is important. Each use case has completely different necessities for context size, token dimension, and the flexibility to deal with numerous duties like summarization, process completion, chatbot purposes, 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 purposes with safety, privateness, and accountable AI.

This resolution is applied utilizing Anthropic Claude 3, obtainable via Amazon Bedrock. Anthropic Claude 3 is chosen for 2 principal 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 consumer.
• Enhanced reasoning talents – Anthropic Claude 3 demonstrates enhanced efficiency when working with bigger contexts, enabling it to raised perceive and reply to consumer 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 resolution 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 purposes utilizing LLMs, and it’s easy to make use of Amazon Bedrock via LangChain utilizing the Amazon Bedrock component obtainable in LangChain. We use Streamlit to develop the frontend for this internet software.

For information analysts to effortlessly work together with and get queries to extract related information from their information lake, this resolution implements a chat engine utilizing the ConversationalRetrievalChain mechanism, which lets you go a customized vector retailer retriever, immediate, and dialog historical past to the LLM and generate personalised solutions to consumer questions. To retailer the chat historical past, we use DynamoDB with the consumer 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 consumer classes concurrently. The next screenshot reveals an instance of the chat interface developed utilizing Streamlit.

AskData consumer interface

The next is an instance state of affairs to get an concept of the consumer workflow when interacting with AskData:

Person Query - how do I discover a buyer handle?

AskData Response - Based mostly on the offered LookML information, 
you'll find the client handle by querying the raw_mailing_addresses desk, 
which comprises address-related info. 
Right here is the SQL question to retrieve the client 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 establish the client), 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 have a particular buyer’s account and also you need to retrieve the handle for that buyer, you’ll be able to 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 client whose handle you need to discover.

Optimize the applying

Though utilizing an LLM to reply consumer questions on information is environment friendly, it comes with acknowledged limitations, reminiscent of the flexibility of the LLM to generate inaccurate responses, usually as a consequence of hallucinated info. To boost the accuracy of our software and cut back hallucinations, we did the next:

• Set the temperature for the LLM to 0.1 to cut 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 offered within the immediate.
• Meticulously eliminated duplicate and redundant entries from the LookML information earlier than it was ingested into the vector database.
• Added a consumer expertise suggestions (a score 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.

Based mostly on consumer suggestions, the applying achieved a internet promoter rating (NPS) of 40, surpassing the preliminary goal rating of 35%. We set this goal as a result of following key elements: the dearth of related info for particular consumer questions inside the LookML information, particular guidelines associated to the construction of SQL queries which may should be added, and the expectation that typically the LLM would make a mistake despite all of the measures we put in place.

Conclusion

On this publish, we illustrated learn how to use generative AI to considerably improve the effectivity of information analysts. By utilizing 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 offered it as context to the LLM alongside consumer queries and any earlier chat historical past. The LLM then seamlessly generated SQL queries in response.

Our improvement course of was streamlined thanks to numerous AWS providers, significantly Amazon Bedrock, which facilitated the combination of LLM for question responses, and Amazon RDS, serving as our vector shops.

Take a look at the next sources to be taught extra:

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

Concerning the Authors

Apurva Gawad is a Senior Knowledge Engineer at Twilio specializing in constructing scalable programs for information ingestion and empowering enterprise groups to derive beneficial 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, all the time in search of recent experiences and views.

Aishwarya Gupta is a Senior Knowledge Engineer at Twilio centered 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 Knowledge 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 numerous domains and sectors. He has centered on creating revolutionary information options, considerably optimizing efficiency and decreasing prices.

Amit Arora is an AI and ML specialist architect at Amazon Internet Companies, serving to enterprise clients use cloud-based machine studying providers 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 crew at AWS. With over 10 years of expertise serving to clients undertake new applied sciences, he guides them via architecting end-to-end options spanning infrastructure, large information, and AI.