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

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

At this time’s main corporations belief Twilio’s Buyer Engagement Platform (CEP) to construct direct, personalised relationships with their prospects in every single place on this planet. Twilio allows corporations 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 to development, customer support, and lots of extra engagement use circumstances in a versatile, programmatic manner. Throughout 180 international locations, tens of millions of builders and a whole lot of 1000’s of companies use Twilio to create personalised experiences for his or her prospects. As one of many largest AWS prospects, Twilio engages with information, synthetic intelligence (AI), and machine studying (ML) companies to run their day by day workloads.

Information is the foundational layer for all generative AI and ML functions. Managing and retrieving the appropriate data might be complicated, particularly for information analysts working with giant information lakes and sophisticated SQL queries. To deal with 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 device makes use of Amazon Bedrock, a totally managed generative AI service that gives entry to high-performing basis fashions (FMs) and capabilities like Retrieval Augmented Generation (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 submit highlights how Twilio enabled pure language-driven information exploration of enterprise intelligence (BI) information with RAG and Amazon Bedrock.

Twilio’s use case

Twilio wished to supply an AI assistant to assist their information analysts discover information of their information lake. They used the metadata layer (schema data) over their information lake consisting of views (tables) and fashions (relationships) from their information reporting device, 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 applied RAG utilizing Anthropic Claude 3 on Amazon Bedrock to develop a digital assistant device known as AskData for his or her information analysts. This device converts questions from information analysts requested in pure language (corresponding to “Which desk accommodates buyer tackle data?”) right into a SQL question utilizing the schema data obtainable in Looker Modeling Language (LookML) fashions and views. The analysts can run this generated SQL immediately, saving them the time to first establish the tables containing related data after which write a SQL question to retrieve the data.

The AskData device 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 device 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 usually attain out to the engineering assist channel once 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 rapidly.

Resolution overview

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

• The right way to 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
• The right way to choose the optimum giant language mannequin (LLM) to 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 decreased 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 Simple 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 data) 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 might 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 data from the info lake. The next is the immediate template that’s used for producing the SQL question:

Human: The context data under represents the LookML information for Looker views and fashions. 
Utilizing this context information, please generate a presto SQL question that can return the right outcome for the consumer's query. 
Please present a SQL question with the right syntax, desk names, and column names based mostly on the offered LookML information.

<directions>

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

{{instance redacted}}

2. Be part of tables as essential to get the right outcome. 
- 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> throughout the LookML views. 
Use the prevailing 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 foremost 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 consider data.
3. Create a easy net software utilizing LangChain and Streamlit.
4. Refine your present software utilizing strategic strategies corresponding to prompt engineering, optimizing inference parameters and different LookML content material.

Stipulations

To implement the answer, you must have an AWS account, model access to your alternative 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 Identity and Access Management (IAM) user with sufficient 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 enables 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 Consumer License Settlement (EULA). You need to assessment the EULA for phrases and situations of utilizing a mannequin earlier than requesting entry to it. For details about mannequin pricing, discuss with Amazon Bedrock pricing.

Mannequin entry

Construction and index the info

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

This resolution makes use of two separate collections which might 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 a substitute go the file in its entirety to the embeddings mannequin. The vector similarity search is ready to discover the right information that comprise the LookML tables and views related to the consumer’s query. We are able to go the complete LookML file contents to the LLM, making the most of its giant context window, and the LLM is ready to choose 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 info lake. Views signify particular person tables, and fashions outline the relationships between these tables. By separating these parts, we are able to 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 exhibits how each subsets of metadata are chunked and saved as embeddings in several vectors for enhanced retrieval. The LookML view and mannequin data is introduced into Amazon S3 via a separate information pipeline (not proven).

Content material ingestion into vector db

Choose the optimum LLM to your use case

Choosing the appropriate LLM for any use case is crucial. Each use case has totally different necessities for context size, token measurement, and the flexibility to deal with varied duties like summarization, process completion, chatbot functions, and so forth. Amazon Bedrock is a totally managed service that provides a alternative of high-performing FMs from main AI corporations 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 resolution is applied utilizing Anthropic Claude 3, obtainable via Amazon Bedrock. Anthropic Claude 3 is chosen for 2 foremost 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 complicated or intensive information, so the LLM has entry to the mandatory data 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 possibly can acquire 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 functions 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 net 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 allows you to 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 exhibits an instance of the chat interface developed utilizing Streamlit.

AskData consumer interface

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

Consumer Query - how do I discover a buyer tackle?

AskData Response - Based mostly on the offered LookML information, 
you'll find the client tackle by querying the raw_mailing_addresses desk, 
which accommodates address-related data. 
Right here is the SQL question to retrieve the client tackle:

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 tackle, the second line of the tackle (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. You probably have a selected buyer’s account and also you wish to retrieve the tackle 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 client whose tackle you wish to discover.

Optimize the appliance

Though utilizing an LLM to reply consumer questions on information is environment friendly, it comes with acknowledged limitations, corresponding to the flexibility of the LLM to generate inaccurate responses, usually because of hallucinated data. 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 cut back the LLM’s propensity for overly inventive 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 elective 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 appliance achieved a internet promoter rating (NPS) of 40, surpassing the preliminary goal rating of 35%. We set this goal because of the following key elements: the dearth of related data for particular consumer questions throughout the LookML information, particular guidelines associated to the construction of SQL queries that may must 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 submit, we illustrated find out how to use generative AI to considerably improve the effectivity of knowledge 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 data 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 growth course of was streamlined thanks to varied AWS companies, notably 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 at present, and depart your suggestions and questions within the feedback part.

Concerning the Authors

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

Aishwarya Gupta is a Senior Information Engineer at Twilio centered on constructing information methods 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 centered on creating revolutionary information options, considerably optimizing efficiency and lowering prices.

Amit Arora is an AI and ML specialist architect at Amazon Internet Companies, serving to enterprise prospects 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 throughout the Strategic Accounts group at AWS. With over 10 years of expertise serving to prospects undertake new applied sciences, he guides them via architecting end-to-end options spanning infrastructure, massive information, and AI.