Meta Lamini
Tune Llama 3 for text-to-SQL with Lamini Memory Tuning
In this notebook, you'll learn how to tune Llama 3 with Lamini Memory Tuning for a SQL LLM to remove hallucinations and lift accuracy from 30% to 95%.
You'll be using the nba_roster database, which contains information about NBA players, teams, and games. This database will serve as the foundation for your tuning process.
This notebook is an in-depth tutorial. Expected runtime for the notebook is ~ 6 minutes, but including full data generation and training the entire notebook can take several hours to run. Included in the notebook are several pre-prepared generated datasets and pre-prepared models for your convenience! Hang in there - it's totally worth it!
If you haven't already, please install lamini first!
Requirement already satisfied: lamini in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (2.2.1) Requirement already satisfied: lamini-configuration[yaml] in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (0.8.3) Requirement already satisfied: requests in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (2.32.3) Requirement already satisfied: tqdm in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (4.66.4) Requirement already satisfied: numpy in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (1.26.4) Requirement already satisfied: jsonlines in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (4.0.0) Requirement already satisfied: pandas in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (2.2.2) Requirement already satisfied: azure-storage-blob in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (12.20.0) Requirement already satisfied: scikit-learn in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (1.5.0) Requirement already satisfied: aiohttp in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (3.9.5) Requirement already satisfied: faiss-cpu in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini) (1.8.0) Requirement already satisfied: aiosignal>=1.1.2 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from aiohttp->lamini) (1.3.1) Requirement already satisfied: attrs>=17.3.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from aiohttp->lamini) (23.2.0) Requirement already satisfied: frozenlist>=1.1.1 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from aiohttp->lamini) (1.4.1) Requirement already satisfied: multidict<7.0,>=4.5 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from aiohttp->lamini) (6.0.5) Requirement already satisfied: yarl<2.0,>=1.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from aiohttp->lamini) (1.9.4) Requirement already satisfied: azure-core>=1.28.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from azure-storage-blob->lamini) (1.30.1) Requirement already satisfied: cryptography>=2.1.4 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from azure-storage-blob->lamini) (42.0.8) Requirement already satisfied: typing-extensions>=4.6.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from azure-storage-blob->lamini) (4.12.1) Requirement already satisfied: isodate>=0.6.1 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from azure-storage-blob->lamini) (0.6.1) Requirement already satisfied: pyyaml<7.0,>=6.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from lamini-configuration[yaml]->lamini) (6.0.1) Requirement already satisfied: python-dateutil>=2.8.2 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from pandas->lamini) (2.9.0) Requirement already satisfied: pytz>=2020.1 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from pandas->lamini) (2024.1) Requirement already satisfied: tzdata>=2022.7 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from pandas->lamini) (2024.1) Requirement already satisfied: charset-normalizer<4,>=2 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from requests->lamini) (3.3.2) Requirement already satisfied: idna<4,>=2.5 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from requests->lamini) (3.7) Requirement already satisfied: urllib3<3,>=1.21.1 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from requests->lamini) (2.2.1) Requirement already satisfied: certifi>=2017.4.17 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from requests->lamini) (2024.6.2) Requirement already satisfied: scipy>=1.6.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from scikit-learn->lamini) (1.13.1) Requirement already satisfied: joblib>=1.2.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from scikit-learn->lamini) (1.4.2) Requirement already satisfied: threadpoolctl>=3.1.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from scikit-learn->lamini) (3.5.0) Requirement already satisfied: six>=1.11.0 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from azure-core>=1.28.0->azure-storage-blob->lamini) (1.16.0) Requirement already satisfied: cffi>=1.12 in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from cryptography>=2.1.4->azure-storage-blob->lamini) (1.16.0) Requirement already satisfied: pycparser in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (from cffi>=1.12->cryptography>=2.1.4->azure-storage-blob->lamini) (2.22) Note: you may need to restart the kernel to use updated packages. Requirement already satisfied: tabulate in /Users/jonathanli/miniconda3/envs/py311-new/lib/python3.12/site-packages (0.9.0) Note: you may need to restart the kernel to use updated packages.
Auth
Before we begin, make sure to authenticate!
Please head over to https://app.lamini.ai/account to get your api key.
You can authenticate by writing the following to a file ~/.lamini/configure.yaml
production:
key: <YOUR-LAMINI-API-KEY>
Alternatively, you can set your api key in this notebook by uncommenting lamini.api_key = '<YOUR-LAMINI-API-KEY>' and filling in your api key in the following cell before running!
Create a SQL Model with Llama 3 and Diagnose Hallucinations
First let's create a SQL LLM with Llama 3 and get a baseline. You can run the following python script which uses Llama 3.
Question: Who is the highest paid NBA player? Answer: To answer this question, we can use the following SQLite query: ```sql SELECT NAME, SALARY FROM nba_roster WHERE SALARY!= '--' ORDER BY CAST(SALARY AS REAL) DESC LIMIT 1; ``` This query first filters out the rows where the salary is '--' (i.e., the players who don't have a salary listed). Then, it orders the remaining rows by the salary in descending order (highest to lowest). Finally, it returns the top row, which corresponds to the highest paid NBA player.
make_llama_3_prompt and get_schema are commonly used throughout this notebook. Let's inspect them for a second
def make_llama_3_prompt(user, system=""):
system_prompt = ""
if system != "":
system_prompt = (
f"<|start_header_id|>system<|end_header_id|>\n\n{system}<|eot_id|>"
)
return f"<|begin_of_text|>{system_prompt}<|start_header_id|>user<|end_header_id|>\n\n{user}<|eot_id|><|start_header_id|>assistant<|end_header_id|>\n\n"
Meta Llama 3 Instruct uses a prompt template, with special tags used to indicate the user query and system prompt. You can find the documentation on this model card.
def get_schema():
return """\
0|Team|TEXT eg. "Toronto Raptors"
1|NAME|TEXT eg. "Otto Porter Jr."
2|Jersey|TEXT eg. "0" and when null has a value "NA"
3|POS|TEXT eg. "PF"
4|AGE|INT eg. "22" in years
5|HT|TEXT eg. `6' 7"` or `6' 10"`
6|WT|TEXT eg. "232 lbs"
7|COLLEGE|TEXT eg. "Michigan" and when null has a value "--"
8|SALARY|TEXT eg. "$9,945,830" and when null has a value "--"
"""
This get_schema function returns a description of the nba_roster table which you use to inform the model what the datatypes of the columns are (all TEXT) and provide some examples for each column.
This helps the model know how exactly columns are formatted.
For example, the HT column is formatted 6' 7" as opposed to 6'7". This distinction is important because you may need to CAST this column to numerical types in order to do comparison, search, and other mathematical operations on this column.
As you can see, this first script will run Llama 3 with prompt tuning to generate SQL queries that are relevant to this database. One thing you may notice is that the response is verbose, we'd have to parse out the sql from the model output. Let's double check the sqlite query itself.
Saddiq Bey|$4,556,983
Hey this is incorrect! Evaluating Llama 3 manually by hand will take too much time. We can start automating this process. The correct query is
SELECT salary, name
FROM nba_roster
WHERE salary != '--'
ORDER BY CAST(REPLACE(REPLACE(salary, '$', ''), ',','') AS INTEGER) DESC
LIMIT 1;
$51,915,615|Stephen Curry
Create an Evaluation Dataset
An Evaluation Dataset is a representative dataset you can use to make sure your model is consistently performing. It can start as few as 20-100 datapoints. The goal is to get started quickly on improving your model, and not get bogged down here.
Here, you can use the example dataset about the nba_roster database at data/gold-test-set.jsonl.
You can do it! Writing an initial evaluation dataset can feel tedious, but a minor investment in time can lead to drastic improvement in quality. In reality, this time investment is going to be made by an LLM user throughout the lifecycle of a model. For some rough time estimates, it took me ~20 minutes to write 20 queries, and that led to a jump in accuracy from 25% to 75%. Later in this notebook, a more intense ~1 hr long data cleaning workflow improved the model accuracy from 75% to 95%.
Evaluate the SQL LLM with an Eval LLM
Next, let's evaluate Llama 3's baseline accuracy for text-to-SQL. Here, we are using a Lamini Inference pipeline. Just as above, you'll see how the output of the model is used to query the SQL database.
First, define a QueryStage and ScoreStage by extending the GenerationNode class.
With these stages, you can define an evaluation pipeline using the Generation Pipeline class. In this pipeline, you can indicate that one stage feeds into the next by passing the output of the query stage into the input of the score stage in the forward function.
It's important that the input to the evaluation pipeline's call function be an iterable over instances of PromptObject. You'll be using these objects to store data as it passes through the pipeline.
You'll need to save your results somewhere! In this notebook, you can use the data/results directory to log a record of your eval experiments.
It's important to keep track of these experiments. To do this, you can log basic statistics, as well as errors and successes when the model is able to produce SQL which answers the question.
Now, run eval on Llama 3 and see how it does on your evaluation dataset!
Saving results: 0 results [00:00, ? results/s]2024-06-21 14:08:35,116 [ERROR] Failed to run SQL query: SELECT POS, MAX(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS Salary FROM nba_roster WHERE SALARY!= '--' GROUP BY POS 2024-06-21 14:08:35,120 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTRING(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL 2024-06-21 14:08:35,123 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL 2024-06-21 14:08:35,125 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS average_salary FROM nba_roster WHERE POS = 'PF' AND SALARY!= '--'; 2024-06-21 14:08:40,776 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) AS weight FROM nba_roster WHERE WT IS NOT NULL 2024-06-21 14:08:40,780 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) FROM nba_roster WHERE WT!= 'NA'; 2024-06-21 14:08:40,783 [ERROR] Failed to run SQL query: SELECT PERCENTILE(SALARY, 0.25) FROM nba_roster WHERE SALARY!= '--'; 2024-06-21 14:08:40,785 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.75) FROM (SELECT CAST(SUBSTR(salary, 2) AS INTEGER) AS salary FROM nba_roster WHERE salary!= '--') AS subquery 2024-06-21 14:08:40,788 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.99) FROM nba_roster WHERE salary IS NOT NULL Saving results: 16 results [00:13, 1.34 results/s]
Total results: 20 Total size of eval dataset: 20 Percent Valid SQL Syntax: 55.00000000000001 Percent Correct SQL Query: 30.0
You can view the results in the data/results directory, where there's a saved folder with the experiment arguments and results.
You can see that Llama 3 can answer correctly 30% of the time on the gold dataset. Additionally, Llama 3 can provide valid sql syntax as an answer 55% of the time on the gold dataset.
Generate Tuning Data with Data LLMs
You might be thinking, "I'd like to do a little better!" - so the next step is Lamini Memory Tuning.
First, you need tuning data. Let's use Llama 3 to generate some tuning data! You want question and sql datapoints to help tune the model to generate SQL about the nba_roster dataset. The trick here is to work backwards in a pipeline (generate SQL from the schema, then questions from the generated SQL) and to constrain the prompts, so that the generations are more likely to be correct.
You can do this using the following pipeline script.
Saving results: 20 results [00:13, 1.48 results/s]
You can define a new pipeline to generate queries. This one also has multiple stages, and as mentioned above, the trick is that you are working backwards. The first stage writes SQL, which is pertinent to nba_roster. You're using prompt tuning to get queries that may be inspired by a sample of our gold dataset—that way, you're getting examples that are relevant to the evaluation (ideally, showing correct examples similar to those that were previously incorrect). Then, you use the question stage to inspect those queries and generate a question that can be answered by the generated query.
Since the point is to create an model that can move forwards (generate), working backwards like this is just one creative method for data generation that can help constrain the prompts and produce more accurate generated data for tuning.
Saving results: 6 results [00:22, 3.31s/ results]
Take a minute to look over the generated data. You may notice that some of the datapoints are incorrect - the SQL is invalid, the questions are duplicated, or the questions may be irrelevant. Let's continue onwards for now - but we'll return to (programmatically) clean the data later!
Tune Llama 3 with Lamini Memory Tuning
Now it's time to tune Llama 3 with Lamini! You still want to use the Llama 3 template, so you can stream your training data with this in mind.
You can submit your data to Lamini Tuning easily. The best defaults for the top LLMs like Llama 3 have been optimized for you.
Saving results: 30 results [00:22, 1.35 results/s]
Uploading data.... Upload to blob completed for data. Data pairs uploaded to blob. Your dataset id is: 9d3e7264d1b5f24e8aaa60296517b638b157c7e6ef098582adaf90d280694d6e . Consider using this in the future to train using the same data. Eg: llm.train(dataset_id='9d3e7264d1b5f24e8aaa60296517b638b157c7e6ef098582adaf90d280694d6e') Training job submitted! Check status of job 7502 here: https://app.lamini.ai/train/7502
{'job_id': 7502,
, 'status': 'SCHEDULED',
, 'dataset_id': '9d3e7264d1b5f24e8aaa60296517b638b157c7e6ef098582adaf90d280694d6e'} Tuning jobs are queued immediately after you run the above cell! Once they begin, the estimated time is 30 minutes. You can continue in this notebook by using the four pre-prepared models provided in this notebook which we tuned for your convenience.
When your training job finishes, you can query the newly trained model by
- Finding the model id at
https://app.lamini.ai/train - Instantiating a model client with
llm = lamini.Lamini(model_name="<YOUR_MODEL_ID>")
Training jobs can fail! If it does, try resubmitting your job by re-running the training cell.
After you submit a job, you can monitor the job status at https://app.lamini.ai/train. There you'll have access to the interface shown below which will help you track jobs, view logs, and get the model ID once training is complete.
Tuning a model takes many attempts and iterations on the generated data, by re-running evaluation and sifting through the results to adjust the data generation pipeline to cover what's still missing.
Sometimes, those adjustments are incredibly minute—just like in prompt-engineering, it's hard to predict what those adjustments might be, so being able to quickly iterate using your evaluation pipeline and inspecting the results quickly is absolutely key.
That's why Lamini's high-performance inference engine is built to optimize processes for both evaluation and data generation, and then unify them with tuning effectively.
Just for a gauge of what's normal: in the creation of this notebook, over 20 models were tuned. So don't get discouraged if it's not top notch on your first try: the point is actually to build that muscle of iteration—that's the most important piece towards getting the best results.
You'll see one of the iterations in the following sections, to get a feel for what the workflow is like.
Here's a prepared tuned model, so you don't have to wait for the tuning to complete. This notebook has four prepared models for each of the four times we will tune.
First, go ahead and ask the tuned model a question!
Question: Who is the highest paid NBA player? Answer: select salary, name from nba_roster where SALARY!= '--' ORDER BY CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER) DESC LIMIT 1
Much better! You can check against the database that this is correct.
$51,915,615|Stephen Curry
Evaluate the tuned Llama 3
To compare how results have improved quantitatively, rerun the SQL pipeline with the tuned model:
Saving results: 0 results [00:00, ? results/s]2024-06-21 14:09:34,226 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, 1, INSTR(WT,' ')) as INTEGER) FROM nba_roster WHERE WT!= 'NA') as median Saving results: 20 results [00:26, 1.31s/ results]
Total results: 20 Total size of eval dataset: 20 Percent Valid SQL Syntax: 95.0 Percent Correct SQL Query: 75.0
You can see that the tuned model has 75% correct SQL (compared to 30% for base Llama 3). Bam!
Let's take a look at the sql_errors.jsonl file to try and figure out what the model is getting wrong. Here is the error analysis part, which is figuring out what types of errors are occurring. You find that there are 3 types of errors:
"What is the average salary of Power Forward players in the NBA"
SELECT AVG(CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as average_salary FROM nba_roster WHERE POS='PF' AND SALARY!= '--';
12355651.6714286
Reference:
select avg(CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as average_salary from nba_roster where POS = 'PF';
10948045.7848101
"What is the 75th percentile salary in the NBA?"
"What is the 25th percentile salary in the NBA?"
"What is the 99th percentile salary in the NBA?"
SELECT (CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as salary FROM nba_roster WHERE SALARY!= '--' ORDER BY salary DESC LIMIT 1 OFFSET (SELECT COUNT(*) FROM nba_roster WHERE SALARY!= '--')*75/100-1;
2421720
Reference:
SELECT (CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as percentile FROM nba_roster WHERE SALARY!= '--' order by percentile limit 1 offset (select count(*) from nba_roster where SALARY != '--')*75/100-1;
13932008
"What's the median age of the Miami Heat?"
SELECT AVG(AGE) FROM nba_roster WHERE team='Miami Heat';
Reference:
select CAST(AGE as INTEGER) as percentile from nba_roster where team='Miami Heat' order by percentile limit 1 offset (select count(*) from nba_roster where team='Miami Heat')/2;
Improve the Tuned Llama 3
You can improve the tuned model by improving the dataset you used based on your error analysis above. To do this, you can both increase the size, coverage, and quality of your generated dataset.
This next step will generate 10x more data. This dataset will still have quality issues, so actually playing a numbers game can help you: generating more data overall means you can filter bad examples from the dataset later and still have a hefty amount of data left.
Saving results: 6 results [00:36, 4.94s/ results]
Here's another piece of error analysis in your data generation pipeline. After sifting through the data, one thing that stands out is that some queries and questions are duplicated, and some queries may not run.
Here are a few improvements you can easily do — programmatically:
- Filter the dataset by removing duplicates
- Only keeping queries that are valid sql.
- Remove queries where we filter by "Null"
- Returns an empty dataframe
- Uses incorrect query components like "AVG(HT)" in the query
- Add a semicolon to the end if it does not exist
Saving results: 30 results [00:36, 1.20s/ results]
Great! The large 1000 datapoint dataset is filtered down to 364 datapoints. This makes it way easier for the next step of sifting through the data a second time, this time more closely. You'll notice that it's the combination of analyzing and categorizing errors, with building automated pipelines to address those errors that will serve you best. It's important to dive-deep analyses of your data when tuning models, so you can reveal issues that are very difficult to detect on the surface automatically—what's helpful, however, is that you can build out reusable automated pipelines from that, which you can re-run in future iterations of model improvement, when you upgrade your base model (e.g. to Llama 4!), and even when you develop similar adjacent model applications.
Here's what a simple manual look-over as a next step can look like:
- Print out the SQL queries and questions for easy reading
- Manually delete or fix obviously incorrect datapoints as you look over each datapoint
===================== 1 ======================
What college has the most players in the NBA who are 30 years old or older
SELECT COLLEGE, COUNT(*) AS count FROM nba_roster WHERE AGE >= 30 GROUP BY COLLEGE ORDER BY count DESC LIMIT 1;
COLLEGE count
-- --------- -------
0 -- 22
===================== 2 ======================
What is the total salary of all NBA players
SELECT SUM(CAST(SUBSTR(SALARY, 1, INSTR(SALARY, '$')-1) AS INTEGER)*1000000) FROM nba_roster;
SUM(CAST(SUBSTR(SALARY, 1, INSTR(SALARY, '$')-1) AS INTEGER)*1000000)
-- -----------------------------------------------------------------------
0 0
===================== 3 ======================
What are the most common positions in the NBA
SELECT POS, COUNT(*) AS num_players FROM nba_roster GROUP BY POS;
POS num_players
-- ----- -------------
0 C 81
1 F 95
2 G 96
3 PF 79
4 PG 75
5 SF 77
6 SG 97
===================== 4 ======================
What is the average salary for each age group in the NBA
SELECT AVG(CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as average_salary, AGE as age_group FROM nba_roster WHERE SALARY!= '--' GROUP BY AGE ORDER BY age_group;
average_salary age_group
-- ---------------- -----------
0 4.39334e+06 19
1 4.93876e+06 20
2 3.48698e+06 21
3 5.22664e+06 22
4 6.48673e+06 23
5 1.00229e+07 24
6 1.1199e+07 25
7 9.53451e+06 26
8 1.52048e+07 27
9 1.68002e+07 28
10 1.73774e+07 29
11 1.25041e+07 30
12 1.81367e+07 31
13 1.51997e+07 32
14 2.41203e+07 33
15 2.14952e+07 34
16 1.21162e+07 35
17 2.01971e+06 36
18 1.64275e+07 37
19 2.98073e+07 38
===================== 5 ======================
What are the top 5 colleges that have produced the most NBA players
SELECT COLLEGE, COUNT(*) as count FROM nba_roster WHERE COLLEGE!= '--' GROUP BY COLLEGE ORDER BY count DESC LIMIT 5;
COLLEGE count
-- --------- -------
0 Kentucky 28
1 Duke 27
2 UCLA 15
3 Arizona 14
4 Kansas 13
===================== 6 ======================
How many players in the NBA attended college
SELECT COUNT(*) AS num_college_players FROM nba_roster WHERE COLLEGE!= '--';
num_college_players
-- ---------------------
0 521
===================== 7 ======================
What are the top 3 colleges with the most players in the NBA
SELECT COLLEGE, COUNT(*) as count FROM nba_roster WHERE COLLEGE!= '--' GROUP BY COLLEGE ORDER BY count DESC LIMIT 3;
COLLEGE count
-- --------- -------
0 Kentucky 28
1 Duke 27
2 UCLA 15
===================== 8 ======================
What is the average age of all players in the NBA
SELECT AVG(AGE) FROM nba_roster;
AVG(AGE)
-- ----------
0 25.655
===================== 9 ======================
What is the most represented college in the NBA
SELECT COLLEGE, COUNT(*) as count FROM nba_roster WHERE COLLEGE!= '--' GROUP BY COLLEGE ORDER BY count DESC LIMIT 1;
COLLEGE count
-- --------- -------
0 Kentucky 28
===================== 10 ======================
Which college has produced the most NBA players
SELECT COLLEGE, COUNT(*) as count FROM nba_roster GROUP BY COLLEGE ORDER BY count DESC LIMIT 1;
COLLEGE count
-- --------- -------
0 -- 79
===================== 11 ======================
What is the average height of NBA players
SELECT AVG(CAST(SUBSTR(HT, 1, INSTR(HT,' ')-1) AS INTEGER) + CAST(SUBSTR(HT, INSTR(HT,' ')+1) AS FLOAT)/12) AS average_height FROM nba_roster;
average_height
-- ----------------
0 6.54986
This step can take time to do, for example an hour filtering through ~350 datapoints. VSCode had a view for the output, which you can get to by clicking into the "..." inside the output cell.
What you're looking for are obviously incorrect datapoints to quickly remove.
You are also scanning for interesting datapoints you had not thought to include in the Gold Dataset.
One hack was to reverse the order of inspection and start at the bottom of the file so you could keep the numbers relevant.
Here's an example datapoint which is incorrect upon inspection:
===================== 345 ======================
What is the average age of the tallest players in the NBA
SELECT NAME, TEAM, POS, AVG(AGE) AS AVG_AGE FROM nba_roster WHERE CAST(SUBSTR(HT, 1, INSTR(HT,' ')-1) AS INTEGER) + CAST(SUBSTR(HT, INSTR(HT,' ')+1) AS FLOAT)/12 > 6.67 GROUP BY NAME, TEAM, POS ORDER BY AVG_AGE DESC LIMIT 1;
NAME Team POS AVG_AGE
-- ------------ ------------------ ----- ---------
0 LeBron James Los Angeles Lakers SF 38
After doing this, you are left with 220 filtered and cleaned datapoints in a new file created manually generated_queries_large_filtered_cleaned.jsonl.
You can use this to tune the next iteration of your model.
Uploading data.... Upload to blob completed for data. Data pairs uploaded to blob. Your dataset id is: c133dc220b0cb24627b7064b0c8654b6e069abbf403ca730ce34df306619e704 . Consider using this in the future to train using the same data. Eg: llm.train(dataset_id='c133dc220b0cb24627b7064b0c8654b6e069abbf403ca730ce34df306619e704') Training job submitted! Check status of job 7504 here: https://app.lamini.ai/train/7504
{'job_id': 7504,
, 'status': 'SCHEDULED',
, 'dataset_id': 'c133dc220b0cb24627b7064b0c8654b6e069abbf403ca730ce34df306619e704'} Iteratively tune and improve the tuned Llama 3
Saving results: 0 results [00:00, ? results/s]2024-06-21 14:10:34,562 [ERROR] Failed to run SQL query: SELECT NAME FROM nba_roster WHERE TEAM='Brooklyn Nets' AND AGE=MAX(AGE); Saving results: 20 results [00:16, 1.21 results/s]
Total results: 20 Total size of eval dataset: 20 Percent Valid SQL Syntax: 95.0 Percent Correct SQL Query: 90.0
Yay! The new model improved to 90% correct on the gold dataset. You can continue this process, looking over the errors and adding, editing, and filtering better data. You can do this by continuing to build more involved programmatic pipelines and skimming manually to understand patterns in the data—until you are satisfied with the accuracy.
Accuracy on your Gold Dataset is a function of effort. You can reach near 100% accuracy on the Gold Dataset, for example. Typically, the right move is to have the easiest examples in the Gold Dataset that your best model still gets wrong.
Once you're satisfied with the results on your Gold Dataset, it's time to make your Gold Dataset harder, and then repeat the process of improving the model again.
Iterate on the Evaluation Dataset
Now that you've gotten good performance on the original Gold Dataset, it's a good time to expand the dataset to make evaluation harder, and in turn, get your tuned model to become even more capable. The augmented gold-test-set-v2.jsonl has a few more handcrafted datapoints looking to add coverage over additional complex queries.
First, on your new Gold Dataset, re-establish a baseline performance of Llama 3 on gold-test-set-v2.jsonl.
Saving results: 0 results [00:00, ? results/s]2024-06-21 14:10:42,361 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) AS weight FROM nba_roster WHERE WT IS NOT NULL 2024-06-21 14:10:42,363 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(WT, INSTR(WT,'') + 1) AS INTEGER) FROM nba_roster WHERE WT!= 'NA'; 2024-06-21 14:10:42,365 [ERROR] Failed to run SQL query: SELECT PERCENTILE(SALARY, 0.25) FROM nba_roster WHERE SALARY!= '--'; 2024-06-21 14:10:42,366 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.75) FROM (SELECT CAST(SUBSTR(salary, 2) AS INTEGER) AS salary FROM nba_roster WHERE salary!= '--') AS subquery 2024-06-21 14:10:42,368 [ERROR] Failed to run SQL query: SELECT PERCENTILE(salary, 0.99) FROM nba_roster WHERE salary IS NOT NULL 2024-06-21 14:10:42,504 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS average_salary FROM nba_roster WHERE POS = 'PF' AND SALARY!= '--'; 2024-06-21 14:10:47,647 [ERROR] Failed to run SQL query: SELECT POS, MAX(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS Salary FROM nba_roster WHERE SALARY!= '--' GROUP BY POS 2024-06-21 14:10:47,651 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTRING(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL 2024-06-21 14:10:47,652 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(HT, 0, INSTR(HT,'')-1) AS INTEGER) FROM nba_roster WHERE HT IS NOT NULL 2024-06-21 14:10:49,132 [ERROR] Failed to run SQL query: SELECT Team, AVG(CAST(SUBSTR(HT, 0, INSTR(HT,'')-1) AS INTEGER) AS Height) AS Average_Height FROM nba_roster GROUP BY Team ORDER BY Average_Height DESC LIMIT 1 2024-06-21 14:10:49,134 [ERROR] Failed to run SQL query: SELECT Team, AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS AVG_Salary FROM nba_roster WHERE SALARY!= '--' GROUP BY Team ORDER BY AVG_Salary LIMIT 1 2024-06-21 14:10:49,135 [ERROR] Failed to run SQL query: SELECT Team, SUM(CAST(SUBSTR(SALARY, 2) AS INTEGER) AS TotalSalary FROM nba_roster WHERE SALARY!= '--' GROUP BY Team ORDER BY TotalSalary DESC LIMIT 1 2024-06-21 14:10:52,500 [ERROR] Failed to run SQL query: SELECT * FROM nba_roster WHERE COLLEGE = '-- 2024-06-21 14:10:55,221 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) FROM nba_roster WHERE SALARY!= '--'; 2024-06-21 14:10:55,223 [ERROR] Failed to run SQL query: SELECT AVG(CAST(SUBSTR(SALARY, 2) AS INTEGER) FROM nba_roster WHERE SALARY!= '--'; Saving results: 36 results [00:27, 1.70 results/s]
Total results: 40 Total size of eval dataset: 40 Percent Valid SQL Syntax: 62.5 Percent Correct SQL Query: 35.0
Looks like there's plenty of room for improvement! You know how this works now:
- Generate a new training dataset
- Train a model
- Evaluate
Saving results: 55 results [00:36, 1.53 results/s]
Like before, go ahead and tune a model using this dataset.
Uploading data.... Upload to blob completed for data. Data pairs uploaded to blob. Your dataset id is: b69739e9dd2cd4e886902c39e31a544a7ee88824f3ef21d02648c6d1f85d8e8c . Consider using this in the future to train using the same data. Eg: llm.train(dataset_id='b69739e9dd2cd4e886902c39e31a544a7ee88824f3ef21d02648c6d1f85d8e8c') Training job submitted! Check status of job 7505 here: https://app.lamini.ai/train/7505
{'job_id': 7505,
, 'status': 'SCHEDULED',
, 'dataset_id': 'b69739e9dd2cd4e886902c39e31a544a7ee88824f3ef21d02648c6d1f85d8e8c'} 2024-06-21 14:11:49,387 [ERROR] Failed to run SQL query: SELECT team FROM nba_roster GROUP BY team ORDER BY COUNT(*) AS team_size ASC LIMIT 1; Saving results: 40 results [01:16, 1.90s/ results] 2024-06-21 14:11:57,590 [ERROR] Failed to run SQL query: SELECT (CAST(REPLACE(REPLACE(SALARY, '$', ''), ',','') AS INTEGER)) as percentile FROM nba_roster WHERE SALARY!= '--' order by percentile order by 1 ASC limit 1 offset (select count(*) from nba_roster where SALARY!= '--')*75/100-1; Saving results: 40 results [00:25, 1.56 results/s]
Total results: 40 Total size of eval dataset: 40 Percent Valid SQL Syntax: 95.0 Percent Correct SQL Query: 75.0
Like before, it's time for a large data generation and cleaning workflow on Lamini's optimized heavy-inference engine.
Saving results: 11 results [01:01, 3.64s/ results]
Saving results: 55 results [01:02, 1.13s/ results]
Uploading data.... Upload to blob completed for data. Data pairs uploaded to blob. Your dataset id is: cda99c9fe2b91b181c556558ca6845da8fd678d8cfc38b7af25fc35060d8c5c8 . Consider using this in the future to train using the same data. Eg: llm.train(dataset_id='cda99c9fe2b91b181c556558ca6845da8fd678d8cfc38b7af25fc35060d8c5c8') Training job submitted! Check status of job 7520 here: https://app.lamini.ai/train/7520
{'job_id': 7520,
, 'status': 'SCHEDULED',
, 'dataset_id': 'cda99c9fe2b91b181c556558ca6845da8fd678d8cfc38b7af25fc35060d8c5c8'} Evaluate the tuned Llama 3 (again)
Now that you've tuned another model, you can finally check and see how your tuning impacted the quality of the SQL output—and compare it quantitatively.
Saving results: 40 results [00:25, 1.57 results/s]
Total results: 40 Total size of eval dataset: 40 Percent Valid SQL Syntax: 100.0 Percent Correct SQL Query: 95.0
You've improved accuracy from 30% to 95% for valid SQL query accuracy by tuning Llama 3! Amazing.
Lessons
As a realistic overlay, here are details on what it took to create this notebook:
- Multiple automated and manual filtering and editing passes over the tuning data
- Iterated on the Gold Dataset by adding datapoints you want the model to have coverage over
- Many tuning jobs (30+) on different iterations of the tuning data
- Evaluation pipeline construction and prompt-engineering — to have robust evaluation
- Error analysis by reading the errors and determining if it's an error in our evaluation pipeline or a model error
All this to say - Lamini Memory Tuning is a highly iterative process, don't be discouraged if it doesn't work the first time! Trust that incremental progress can be made and codified by storing training datasets.
Keep in mind that you can always improve the model - even the archived datasets we hand filtered can be improved for further performance. Time box the process and don't hesitate to move on to the next step!
Shipping the model in production can often gather better feedback and datapoints to incorporate into the next tuning iteration—this makes gathering data more of an automated and you can get data that your users care about but that you wouldn't have thought of in a vacuum. To make it less daunting, "shipping in production" can even start with a limited release to 5 users.
Stay tuned for a follow on notebook where we explore How to build a SQL LLM on Lamini using Llama 3!
Contact us at Lamini to learn even better techniques for building highly accurate LLM models, as well as running this all in your own VPC or on-premise environments.