Local LLMs are surprisingly capable at data analysis tasks: generating SQL queries from natural language, explaining query results, suggesting analysis approaches, writing pandas transformations, and interpreting data patterns. All of this runs locally on your hardware — meaning you can work with sensitive datasets, internal databases, and confidential business data without sending any of it to external AI services. This guide covers practical workflows for SQL generation and data analysis with Ollama.
Text-to-SQL: Generating Queries from Plain English
The most immediately useful data analysis application for local LLMs is text-to-SQL — describing what you want to know in plain English and getting a SQL query back. The model needs two pieces of context to produce accurate queries: your schema (table names, column names, data types, relationships) and your question.
import ollama
import sqlite3
SCHEMA = """
Tables:
- orders (id, customer_id, created_at, status, total_amount, product_id)
- customers (id, name, email, country, created_at)
- products (id, name, category, price)
Relationships:
- orders.customer_id -> customers.id
- orders.product_id -> products.id
Status values: 'pending', 'shipped', 'delivered', 'cancelled'
"""
def generate_sql(question: str, schema: str = SCHEMA, dialect: str = "sqlite") -> str:
response = ollama.chat(
model="qwen2.5-coder:7b",
messages=[{
"role": "system",
"content": f"""You are a SQL expert. Generate accurate {dialect} SQL queries.
Return ONLY the SQL query, no explanation, no markdown fences.
Schema:\n{schema}"""
},
{"role": "user", "content": question}],
options={"temperature": 0, "num_predict": 300}
)
return response["message"]["content"].strip()
def run_query(sql: str, db_path: str = "data.db") -> list[dict]:
conn = sqlite3.connect(db_path)
conn.row_factory = sqlite3.Row
cursor = conn.execute(sql)
results = [dict(row) for row in cursor.fetchall()]
conn.close()
return results
# Example workflow
questions = [
"Show total revenue by country for orders in the last 30 days",
"Find customers who ordered more than 5 times but have not ordered in the last 60 days",
"What is the average order value by product category this month vs last month"
]
for q in questions:
sql = generate_sql(q)
print(f"Q: {q}")
print(f"SQL: {sql}\n")Temperature 0 is important for SQL generation — you want deterministic, precise output rather than creative variation. The model should return identical SQL for the same question on repeated calls. Setting num_predict to 300 caps output length, preventing the model from adding explanation after the query.
Schema Introspection: Auto-Building Context
Providing schema manually works for small databases. For larger schemas, extract it programmatically:
import sqlite3
def extract_schema(db_path: str) -> str:
conn = sqlite3.connect(db_path)
cursor = conn.cursor()
tables = cursor.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()
schema_parts = []
for (table,) in tables:
cols = cursor.execute(f"PRAGMA table_info({table})").fetchall()
col_defs = ", ".join(f"{c[1]} {c[2]}" for c in cols)
schema_parts.append(f"- {table} ({col_defs})")
conn.close()
return "Tables:\n" + "\n".join(schema_parts)
# For PostgreSQL (using psycopg2):
def extract_pg_schema(conn_str: str) -> str:
import psycopg2
conn = psycopg2.connect(conn_str)
cur = conn.cursor()
cur.execute("""
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position
""")
rows = cur.fetchall()
tables = {}
for table, col, dtype in rows:
tables.setdefault(table, []).append(f"{col} {dtype}")
return "Tables:\n" + "\n".join(f"- {t} ({', '.join(c)})" for t, c in tables.items())Feed the extracted schema directly into your generate_sql function. For large databases with hundreds of tables, filter the schema to the relevant tables for your question — including too many tables can confuse the model and increase context length unnecessarily.
Figure 1 — Data Analysis Workflow with Local LLM
Interpreting Query Results
Beyond generating SQL, local LLMs are useful for interpreting what the results mean. Pass the query, question, and results back to the model and ask for an explanation:
import json
def interpret_results(question: str, sql: str, results: list[dict]) -> str:
results_text = json.dumps(results[:20], indent=2) # limit to 20 rows
if len(results) > 20:
results_text += f"\n... and {len(results) - 20} more rows"
response = ollama.chat(
model="llama3.1",
messages=[{
"role": "system",
"content": "You are a data analyst. Interpret query results clearly and identify the key insight. Note any patterns, anomalies, or follow-up questions the data suggests."
},
{"role": "user", "content": f"Question asked: {question}\n\nSQL run: {sql}\n\nResults: {results_text}\n\nWhat does this tell us?"}],
options={"temperature": 0.3}
)
return response["message"]["content"]
# Full workflow
q = "Which product categories have declining revenue compared to last quarter?"
sql = generate_sql(q)
results = run_query(sql)
insight = interpret_results(q, sql, results)
print(insight)This two-step pattern — generate SQL, then interpret results — is more reliable than asking the model to answer the business question directly. Separating SQL generation from result interpretation keeps each step focused, makes errors easier to debug (you can see the SQL that ran), and lets you verify the results before having the model interpret them.
Pandas Data Analysis
For data that is already in Python dataframes, local LLMs generate pandas transformations effectively when given the dataframe schema as context:
import pandas as pd
import ollama
def df_to_schema(df: pd.DataFrame) -> str:
schema_lines = [f"DataFrame with {len(df)} rows and {len(df.columns)} columns:"]
for col in df.columns:
dtype = str(df[col].dtype)
sample = str(df[col].dropna().iloc[0]) if not df[col].dropna().empty else "empty"
schema_lines.append(f" - {col} ({dtype}), sample: {sample}")
return "\n".join(schema_lines)
def generate_pandas(question: str, df: pd.DataFrame) -> str:
schema = df_to_schema(df)
response = ollama.chat(
model="qwen2.5-coder:7b",
messages=[{
"role": "system",
"content": f"You write pandas Python code. The dataframe is called 'df'.\nSchema:\n{schema}\n\nReturn only executable Python code, no explanation."
},
{"role": "user", "content": question}],
options={"temperature": 0, "num_predict": 300}
)
return response["message"]["content"].strip()
# Use
df = pd.read_csv("sales_data.csv")
code = generate_pandas("Calculate monthly revenue trend and identify the highest growth month", df)
print(code)
# Inspect before running: exec(code)Always inspect generated pandas code before executing it. The model may generate correct-looking code that uses the wrong column name, applies the wrong aggregation, or handles missing values differently than you expect. Reading the code takes 30 seconds and prevents subtle bugs from making their way into your analysis.
The Privacy Case for Local Data Analysis
Data analysis is one of the most privacy-sensitive local AI use cases. Business data — customer records, revenue figures, operational metrics, HR data — is often highly confidential. Sending any of this to a cloud AI service, even for help with SQL or pandas code, creates legitimate privacy and compliance concerns. Many organisations have policies that prohibit uploading internal data to third-party cloud services, which effectively prevents using cloud AI for data analysis tasks on production data.
Local LLMs remove this constraint entirely. You can paste real customer data, actual revenue numbers, genuine operational metrics into your local model without any data leaving your machine. This unlocks AI assistance for exactly the data analysis work where you most need help — working with real production data on real business questions — rather than sanitised test datasets that may not reveal the actual patterns and edge cases in your data. For data engineers, analysts, and data scientists working with sensitive data, local AI assistance is often categorically more useful than cloud AI assistance precisely because it can safely work with the actual data rather than substituting artificial examples.
Choosing Models for Data Analysis
For SQL generation and pandas code, specialised coding models outperform general models. Qwen 2.5 Coder (7B or 14B) is the top recommendation — it has been trained on large quantities of SQL, data manipulation code, and analytical queries and produces noticeably more accurate SQL than general-purpose models. It handles dialect-specific SQL (PostgreSQL vs SQLite vs BigQuery syntax differences) more reliably, generates correct JOIN conditions more consistently, and produces pandas code that uses the right method for the right operation. For result interpretation and business insight extraction — where the output is natural language rather than code — any capable general model (Llama 3.1 8B, Mistral Small) works well alongside the coding model. The practical setup: Qwen 2.5 Coder as the SQL/code generation model and Llama 3.1 as the interpretation model, both loaded simultaneously if your hardware supports OLLAMA_MAX_LOADED_MODELS=2.
Building a Local Analytics Assistant
Combining these components — schema introspection, SQL generation, result interpretation — into a simple interactive tool creates a local analytics assistant that lets non-technical users query databases in natural language. A Streamlit app wrapping these functions gives a clean interface: a text input for the question, a display of the generated SQL for transparency, a table of results, and an AI-generated interpretation. All running locally, accessible to any team member on the local network, processing real business data without any privacy concerns. This kind of internal tool — a private, self-hosted natural language database interface — demonstrates one of the most compelling practical applications of local AI for organisations that have sensitive data and need AI assistance working with it.
Handling Multi-Step Analysis
Many real analysis questions require more than one query — they involve intermediate results that feed into subsequent queries, or comparisons that require building context step by step. Local LLMs handle multi-step analysis well when you structure the conversation to build on previous results. Rather than trying to get a single query that answers a complex question, break it into steps: generate and run the first query, pass the results to the model with your next question, let it generate the follow-up query informed by what the first query revealed.
This conversational approach to data analysis mirrors how a skilled analyst thinks — you rarely know the exact sequence of queries needed upfront. You start with an overview, find something interesting, drill into it, discover a hypothesis, test it. The local LLM as your query-writing assistant accelerates each step of this process without requiring you to know SQL syntax for every operation. The combination of your analytical intuition (knowing what to look for, what questions to ask next) with the model’s SQL generation capability (knowing how to express those questions as executable queries) produces analyses that neither could do as efficiently alone.
Limitations to Keep in Mind
Local LLMs for data analysis have real limitations worth acknowledging. SQL accuracy is high for common query patterns but decreases for complex analytical queries involving window functions, recursive CTEs, complex aggregations, or dialect-specific features. Always verify generated SQL logic, especially for business-critical analysis where query errors could produce misleading results. The model has no knowledge of your actual data distributions — it cannot tell you whether a result is anomalous or expected, whether the data looks clean or has quality issues, or whether the business context makes a result surprising. These interpretive judgments require your domain knowledge. Treat the model as a capable query writer and first-pass interpreter, not as a domain expert who understands your business or a database expert who catches all edge cases. The most effective use pattern is collaborative: the model handles the mechanical translation of questions to queries, you provide the business context, analytical judgment, and verification of results.
Getting Started in 10 Minutes
The fastest path to a working local SQL assistant: install Ollama, pull qwen2.5-coder:7b and nomic-embed-text, copy the generate_sql and run_query functions above into a Python file, update the SCHEMA variable to match one of your actual database tables, and run a test question against a simple query you already know the answer to. Verify the generated SQL is correct, then test a question you do not know the answer to and verify the result by running the query manually in your database client. Once you have confirmed the pattern works on your schema, extend it with the interpret_results function for natural language explanations. The whole setup takes ten to fifteen minutes of active work, and from that point you have a reusable local data analysis tool that can process your actual business data privately for as long as you need it.