I Built an AI Agent Data Analyst Without an API Key. Here’s What I Actually Learned.

How a boring college ETL project turned into a study in what “AI agent” actually means

My Business Intelligence professor assigned us a standard ETL project. I extracted some flight data, cleaned it, loaded it into a star schema, built a Power BI dashboard. Standard stuff. I finished it, got a grade, and moved on.

Then I got curious. What if, instead of opening Power BI every time someone wanted to check airline performance, the warehouse could just answer questions in plain English? Something like a data analyst who never sleeps and never charges by the hour.

My Assumption About AI Agent

I spent the first few weeks convinced that building an AI agent meant one thing: you need an LLM. Gemini, Claude, OpenAI. Pick one, get an API key, write a system prompt, done.

That assumption delayed me by about a month. I kept waiting to figure out the billing situation before touching the agent code.

I was wrong.

In 1972, a system called MYCIN diagnosed bacterial infections using around 600 hand-written if-then rules. No neural network. No training data. No GPU cluster. Doctors trusted it more than many junior physicians. MYCIN was unambiguously considered AI (a rule-based expert system), and it worked because the domain was specific and well-understood.

That history reframed everything for me.

What I Built

The project is called Travel Nusantara. It’s an end-to-end data pipeline for a fictional Online Travel Agency: 50,000 synthetic flight records, 8 airlines, 15 airports, loaded into a PostgreSQL data warehouse through a Medallion Architecture (Bronze to Silver to Gold layers). The business intelligence layer sits on top of that.

I added two things:

1. A local AI agent (Mode A, the default). This is the part that surprised me. The agent accepts a plain-English question, figures out what the user wants, and generates a valid PostgreSQL SELECT query entirely on local CPU. No API call. No internet. No cost.

2. An optional Gemini LLM integration (Mode B). When a user has a Gemini API key in their .env, the agent can call Google's model instead, handing off complex or ambiguous queries to the LLM.

How the AI Agent Works

Three components do the heavy lifting.

The RAG retriever. Before the agent generates any SQL, it runs the user’s question through a TF-IDF vectorizer, the same mathematical technique used in search engines since the 1990s. Each “document” is a hand-written knowledge chunk about the database: which table stores delays, what the carrier codes mean (AA = American Airlines, DL = Delta Air Lines), how the v_airline_performance view calculates composite scores.

The retriever computes cosine similarity between the question vector and each knowledge chunk, then hands the most relevant context to the SQL generator. It’s RAG (Retrieval-Augmented Generation) done with math instead of a model.

The intent router. The SQL generator reads the question and checks it against seven keyword dictionaries. If the user says “worst,” “late,” or “behind schedule,” it routes to the delay domain. If they say “rank” or “performance,” it hits the airline performance view directly. Sort direction, LIMIT clauses, year filters: all parsed from the question using regex, no LLM involved.

The reflection loop. If the generated SQL fails in PostgreSQL, the agent reads the error message, appends it to the prompt, and tries again, up to three times. This isn’t a novel idea; the retry pattern is standard engineering. But it makes the agent far more robust in practice than a one-shot generator would be.

The whole pipeline (RAG retrieval, intent routing, SQL generation) runs in about 30 milliseconds.

The Numbers That Changed My Perspective

I wrote a stress test suite that fires 200,000 queries at the local engine in sequence. It handled all of them at 316 queries per second, on a regular laptop CPU, with zero failures. A separate white-box test suite runs 10,000 test cases: 5,000 structured analytical questions and 5,000 adversarial inputs (SQL injection attempts, nonsense strings, empty queries). The block rate on the adversarial set is 100%.

Those numbers reframed what “free and fast” actually means at scale. A Gemini API call takes 1–3 seconds and costs roughly $0.001 per query. At 316 QPS, the local engine would serve about 27 million queries per day, for free.

What Research Backs This Up

I’m not the only one who ended up here. A 2024 arXiv survey by Hong et al., “Next-Generation Database Interfaces: A Survey of LLM-based Text-to-SQL,” classifies text-to-SQL systems into three generations. Rule-based systems came first: deterministic, syntactically correct, but brittle against ambiguous phrasing. LLM-based systems came second: flexible, generalizable, but expensive and prone to hallucinating column names that don’t exist in your schema. Hybrid systems are emerging as the third generation and currently outperform both pure approaches on production benchmarks.

Zhu et al. (arXiv 2024) makes the same observation in “Large Language Model Enhanced Text-to-SQL Generation: A Survey.” The paper recommends combining deterministic pipelines for routine queries with LLM fallback for complex, novel ones.

The validation layer in Travel Nusantara is the security guard in db_tools.py. It strips anything that isn't a SELECT before it touches the database. The Gemini integration only activates inside that same sandbox.

For formal AI theory, Russell and Norvig’s Artificial Intelligence: A Modern Approach classifies rule-based systems as “Goal-Based Agents”: systems that perceive their environment and act toward a defined goal. Under that definition, what i build is academically, an AI agent.

When Local Wins and When It Doesn’t

Local agents are the better choice when:

• The domain is specific and stable (you know exactly what questions will be asked)
• Response time matters (milliseconds vs. seconds changes the user experience)
• The data is sensitive and can’t leave the machine
• You need every decision to be explainable and auditable

LLMs are the better choice when:

• The queries are unpredictable or cross-domain
• You’re prototyping and speed of development matters more than speed of execution
• The question requires multi-step reasoning across tables the local engine doesn’t know about

The honest answer is that neither wins on its own. The 2024 research consensus says hybrid is the right default architecture, and I think that’s correct. What’s worth knowing is that “hybrid” doesn’t have to mean starting with the LLM and adding rules as a filter. You can start with rules, ship a working product, and add the LLM as an upgrade path.

What I’d Do Differently

The biggest mistake I made wasn’t technical. It was spending too long assuming the problem required a more complex solution than it actually did.

I also underestimated how much domain knowledge matters. The agent works because someone sat down and wrote exactly what AA, DL, departure_delay, and v_airline_performance mean. That data dictionary is the agent's actual intelligence. Without it, the TF-IDF retrieval would have nothing to retrieve from. The 'AI' in this agent isn't in the code. It's in the structured knowledge of the domain that gets encoded before the code runs.

One thing I’m still unsure about: when the user’s question gets complex enough (“show me the 3 airlines with the highest on-time rate among routes connecting cities with more than 2 airports in the same state”), the local engine fails. It doesn’t have a template for that. Mode B (Gemini) handles it, but inconsistently. That’s the honest upper bound of what a rule-based system does well.

Takeaway

If you’re building analytics tooling for a specific business domain (a known database, a known set of questions, a known schema), you probably don’t need an LLM running every query. You need to understand your domain well enough to encode it, write a handful of SQL templates, and add a retrieval layer so the agent can pick the right one.

The agents that require LLMs are the ones that need to generalize. If your system doesn’t need to generalize, if it needs to be correct, fast, and free, the local approach is worth taking seriously.

The full project is open source: github.com/NazmiHakim/Travel-ETL-Data-Warehouse

It includes the ETL pipeline, the local NLP agent, the Gemini integration, a Streamlit dashboard, and the 10,000-case test suite.

References

• Hong, Z. et al. (2024). Next-Generation Database Interfaces: A Survey of LLM-based Text-to-SQL. arXiv preprint.
• Zhu, Y. et al. (2024). Large Language Model Enhanced Text-to-SQL Generation: A Survey. arXiv preprint.
• Anthropic. (2024). Building Effective Agents. Anthropic Engineering Blog.
• Russell, S., & Norvig, P. (2020). Artificial Intelligence: A Modern Approach (4th ed.). Pearson.
• Shortliffe, E. H. (1976). MYCIN: A rule-based computer program for advising physicians regarding antimicrobial therapy selection. Stanford University Technical Report.