How to Build Your Own Data Analysis Agent
Commercial data analysis tools like Julius AI, ThoughtSpot, and Power BI Copilot work well for general-purpose analysis. Building your own makes sense when your database schema is complex enough that generic tools struggle with accuracy, when your business has domain-specific terminology and analytical patterns that need to be encoded, when you have strict security requirements that prevent sending data to external services, or when you want deep integration with internal systems that no commercial tool supports. If none of these apply, start with a commercial tool and consider building custom only if you hit its limitations.
Step 1: Set Up the Foundation
You need four components: a Python environment, an LLM provider, a database connection, and an agent framework.
Python environment. Python 3.10+ with the following core packages: langchain and langchain-community for the agent framework, sqlalchemy for database connectivity, pandas for data manipulation, matplotlib and plotly for visualization, and streamlit or gradio for the frontend. Create a virtual environment and install everything with pip. If you want to run a local LLM instead of calling an API, add Ollama as the model server and langchain-ollama as the integration package.
LLM provider. For text-to-SQL accuracy, larger models outperform smaller ones significantly. GPT-4, Claude, and Gemini Pro produce the most accurate SQL on complex schemas. For simpler schemas (under 20 tables, clear column names), smaller models like GPT-3.5-turbo or locally hosted models via Ollama work adequately and cost much less. Start with a larger model to establish baseline accuracy, then test whether a smaller model meets your needs for production.
Database connection. Create a read-only database user specifically for the agent. Grant SELECT privileges on the schemas and tables the agent should access. Never use your admin or personal database credentials. Store the connection string in environment variables or a secrets manager, never in code. Test the connection with a simple query to verify access and response time.
Agent framework. LangGraph is the recommended framework for data agents because it supports stateful, multi-step workflows where the agent can write a query, check the results, decide whether to refine the query, and generate a visualization, all as a coordinated graph of steps. LangChain's SQLAgent is simpler and works for straightforward question-to-SQL use cases. CrewAI is useful if you want multiple specialized sub-agents (one for SQL, one for visualization, one for statistical analysis) that coordinate on complex requests.
Step 2: Build the Schema Layer
The schema layer is the single biggest determinant of your agent's accuracy. It provides the LLM with the context it needs to write correct SQL for your specific database.
Start by extracting the raw schema from your database. SQLAlchemy's inspect() function gives you table names, column names, data types, and foreign key relationships. This raw schema is the minimum viable context, but it is not enough for high accuracy on real databases where column names like "amt_q4_adj" give no indication of meaning.
Enhance the schema with documentation. For each table, write a one-sentence description of what it contains and when it is the right table to use. For each column, write what the values mean, what the units are, and any business rules that apply. "revenue_net: Net revenue in USD after discounts and refunds. Always use this instead of revenue_gross for revenue reporting." This documentation goes into the prompt alongside the schema definition, giving the LLM the institutional knowledge that a human analyst would build over months of working with the database.
Add a business glossary that maps common business terms to specific SQL definitions. "Active customer" = "customer WHERE status = 'active' AND last_order_date > CURRENT_DATE - INTERVAL '90 days'". "Churn rate" = "COUNT of customers whose status changed from 'active' to 'cancelled' in the period, divided by COUNT of active customers at the start of the period." Without these definitions, the agent guesses at meanings and often gets them wrong in ways that are plausible but incorrect for your business.
Include 10-30 example queries with their natural language descriptions. These examples serve as few-shot prompts that teach the agent your database's patterns. If every revenue query in your examples filters out test accounts (WHERE customer_type != 'internal'), the agent will apply that filter consistently in new queries. The examples should cover the most common query patterns your team uses, including the join paths, filter conditions, and aggregation methods that are standard for your data.
For large databases (100+ tables), implement dynamic schema retrieval. Instead of loading the entire schema into every prompt (which would exceed context limits and reduce accuracy), use embedding-based search to find the 5-10 most relevant tables for each question. Store table and column descriptions as embeddings in a vector database, and when a user asks a question, retrieve the most semantically similar schema elements. This keeps the prompt focused and the accuracy high, even on very large schemas.
Step 3: Implement Text-to-SQL
The core of your data agent is the pipeline that takes a natural language question and produces an executed SQL query with results.
Build the pipeline with these stages. First, retrieve the relevant schema for the question (either the full schema for small databases or the most relevant tables for large ones). Second, generate the SQL query by sending the schema context and question to the LLM with a carefully crafted system prompt that instructs it to write SQL for your specific database dialect, use only the tables and columns provided, and follow your business definitions. Third, validate the query by checking that it references only tables and columns that exist in the schema (catching hallucinated table names), that it does not contain data-modifying statements (extra safety beyond read-only credentials), and that the estimated execution cost is reasonable. Fourth, execute the query against the database with a timeout and row limit. Fifth, handle errors by sending the error message back to the LLM and asking it to correct the query, allowing up to 3 retry attempts.
The system prompt for SQL generation is critical. It should specify: the SQL dialect (PostgreSQL, MySQL, etc.), any functions or syntax specific to your database version, instructions to use the table and column names exactly as documented, instructions to apply standard filters (like excluding test data), and formatting requirements (always alias calculated columns with clear names, use proper date functions instead of string manipulation). A good system prompt is 200-500 words and encodes the most important rules your human analysts follow when writing queries.
Test the pipeline against a benchmark of 30-50 questions with known correct answers. Run each question, compare the results to the expected output, and calculate accuracy. If accuracy is below 80%, improve the schema documentation and example queries before moving forward. If accuracy is 80-90%, you have a usable system. Above 90% is excellent for a custom build.
Step 4: Add Visualization and Analysis
Once the agent can query data reliably, add the ability to generate charts and perform statistical analysis.
For visualization, give the agent access to a Python code execution tool that can run matplotlib or plotly code. When the user asks for a chart or when the query results would be better communicated visually (trend data, comparisons, distributions), the agent writes Python code that takes the query results and produces a chart. The code execution should happen in a sandboxed environment (a container or restricted subprocess) for security. Include chart type selection logic in the agent's instructions: use bar charts for comparisons, line charts for trends, scatter plots for relationships, and tables for detailed breakdowns.
For statistical analysis, add tools that give the agent access to scipy.stats and scikit-learn functions. The agent can then perform hypothesis tests ("Is the difference between Group A and Group B statistically significant?"), regression analysis ("What factors predict customer churn?"), and descriptive statistics with appropriate measures of central tendency and spread. Instruct the agent to report confidence intervals and p-values with plain English explanations, not just numbers.
For result interpretation, add a final LLM call after the data is retrieved and any visualizations are generated. This call takes the raw results and produces a natural language summary of the findings, including key numbers, notable patterns, comparisons to previous periods or benchmarks, and suggested follow-up questions. This interpretation layer is what makes the agent feel like an analyst rather than a query tool.
Step 5: Build the Interface and Deploy
Streamlit is the fastest way to build a chat interface for your data agent. A basic Streamlit app with a chat input, message history, and chart rendering takes about 100 lines of code. Streamlit handles the web server, the UI components, and the session state, letting you focus on the agent logic. For internal team use, Streamlit is usually sufficient.
Gradio is an alternative that offers more customization and embedding options. Gradio apps can be embedded in existing web applications, which is useful if you want to add a data agent to your internal dashboard or wiki.
Add conversation memory so the agent remembers what was asked earlier in the session. When a user asks "Break that down by region", the agent needs to remember that "that" refers to the revenue query from two messages ago. LangChain's ConversationBufferMemory or a simple list of previous messages works for this. For persistent memory across sessions (the agent remembers that a specific user always cares about EMEA data), use a database-backed memory store.
Deploy with access controls appropriate to your data sensitivity. At minimum, require authentication (SSO integration via your identity provider). For sensitive data, add role-based access control that restricts which database schemas each user group can query. Log all queries for audit purposes. If you are deploying on cloud infrastructure, containerize the application with Docker for consistent deployment and scaling.
Start with a small group of beta testers (3-5 analysts who know the data well) before rolling out to the broader team. Their feedback will surface accuracy issues, missing schema documentation, and UX problems that you can fix before wider adoption. Plan for 2-3 weeks of iteration based on beta feedback.
Architecture Decisions
Single agent vs multi-agent. A single agent that handles SQL, visualization, and interpretation works well for most use cases and is simpler to build and debug. A multi-agent architecture (separate agents for query planning, SQL generation, visualization, and interpretation) makes sense when the complexity of each step justifies specialized handling. For example, a dedicated SQL agent that uses chain-of-thought reasoning to plan complex queries before writing them can outperform a general-purpose agent on multi-join queries. Start with a single agent and split into multi-agent only if you identify specific weak spots that benefit from specialization.
Model selection. Use the largest model you can afford during development and testing to establish the accuracy ceiling. Then test progressively smaller models to find the smallest one that meets your accuracy threshold. Many organizations use a tiered approach: a small model (GPT-3.5, Haiku) for simple single-table queries, and a large model (GPT-4, Claude, Opus) for complex multi-table queries. Route based on question complexity, which can be estimated by the number of tables the schema retrieval step returns.
Local vs cloud LLM. Cloud LLMs (OpenAI, Anthropic, Google) offer the best accuracy and require no infrastructure. Local LLMs (via Ollama, vLLM, or TGI) keep all data on your infrastructure, which is required for some compliance frameworks. Local models have improved dramatically and can handle straightforward SQL generation well, but they still lag cloud models on complex multi-table queries. If compliance requires local hosting, plan for appropriate hardware (a GPU with at least 24GB VRAM for a 13B parameter model, 48GB+ for 70B models).
Building a custom data agent is a tractable project for a Python-proficient developer. The critical success factor is the schema layer, which provides the LLM with enough context about your database to generate accurate queries. Start with schema documentation and example queries, build a basic text-to-SQL pipeline, test against known answers, and iterate until accuracy exceeds 80%. Add visualization, statistical analysis, and a chat interface once the query layer is reliable.