# Building a Senior-Level AI Data Analyst in Python: A Six-Stage Pipeline
## The Problem with One-Breath Answers
When you ask a chatbot something like “which marketing promotion should we run more of,” it typically responds instantly. It picks a number, states it with confidence, and moves on. The problem is that it rarely pauses to consider how much data that number is built on. A promotion that appears stellar after 10 total orders is far less convincing than one that performs consistently well across 1,000 orders — yet the average chatbot treats both answers identically.
A seasoned data analyst works deliberately. They restate the question, form a testable hypothesis, write a query, and then verify that the result has enough data behind it before presenting any conclusion to leadership. This discipline — the willingness to slow down and check the foundation of an answer — is what separates reliable analysis from misleading snapshots.
The good news is that this discipline can be encoded into software. In this guide, we walk through building a lightweight Python toolkit that processes any analytical question through six deliberate stages rather than collapsing it into a single prompt call.
## The Dataset
Throughout this walkthrough, we work with a small table of order-level records called `online_orders.csv`. Each row represents a single transaction and contains the following fields: the product sold, the promotion applied, the per-unit cost, the customer identifier, the sale date, and the number of units sold. With 29 rows spanning three months, four promotions, and eleven products, this is exactly the kind of modest dataset where a quick, unexamined answer is most likely to mislead.
We begin by loading the data with Pandas and inspecting its structure, confirming that every column is present, every row is populated, and the date field is stored as text rather than a native date type. This kind of schema awareness is a basic but critical first step before any analysis begins.
## A Deterministic Sanity Check Before Any AI
Before invoking a large language model, we run a plain SQL query against the data using DuckDB — an in-process analytical engine that lets us query a Pandas DataFrame directly without setting up a database server. The query groups orders by promotion and calculates the number of orders, total units sold, total revenue, and average units per order for each promotion.
When sorted by average units per order, one promotion jumps to the top: 8.0 units per order. But that figure rests on a single transaction. A chatbot answering in one breath would recommend this promotion without a second thought. A responsible analyst — and the pipeline we are building — treats this as a red flag rather than a recommendation.
This deterministic check is the intellectual ancestor of the validation stage that appears later in our pipeline. It proves the point before we even involve an LLM.
## The LLM Wrapper
Our pipeline should be provider-agnostic. Whether you hold an Anthropic API key or an OpenAI API key, the rest of the code should remain unchanged. We achieve this with a thin wrapper class that accepts a client, a model name, and a provider identifier, then routes the call to the correct method.
For Anthropic’s API, responses can arrive as multiple content blocks, so the wrapper iterates through them to find the first text block. For OpenAI, it extracts the text from the first choice in the response. If the provider is unsupported or Claude returns no usable text, the wrapper raises an explicit error rather than passing garbage downstream.
Since every stage asks the model to return structured JSON, we also need a helper function that extracts JSON from a text response. Some models wrap their output in Markdown code fences, so the parser strips those first, then attempts to locate the first valid JSON object or array embedded in the text. This makes the pipeline resilient to common formatting inconsistencies without sacrificing reliability.
## Stage 1: Business Understanding
The first stage restates the original stakeholder question in terms the actual table can answer. It identifies the grain of the data — what a single row represents — and catalogs any visible limitations before a single query is executed. These limitations include sample size, date coverage, and any missing dimensions that might affect interpretation.
This stage is critical because it forces the model to ground itself in reality. A question about “which promotion performs best” has a completely different meaning when the analyst notes upfront that some promotions have only one order of data. The output from this stage is a structured restatement that feeds directly into hypothesis generation.
## Stage 2: Hypothesis Generation
The second stage proposes a small set of specific, testable hypotheses using only the columns that exist in the table. Each hypothesis must be something that can be answered with SQL. The goal is not breadth — two or three well-formed hypotheses are sufficient — but clarity. A good hypothesis is falsifiable, specific, and tied to the columns at hand.
For example, rather than asking a vague “which promotion is best,” a strong hypothesis might ask whether the highest-volume promotion outperforms the runner-up by a meaningful margin. The distinction matters: the first invites a raw average that can be skewed by a single outlier, while the second demands a comparison between well-supported groups.
## Stage 3: SQL Planning
The third stage takes the top-ranked hypothesis and translates it into an actual DuckDB SQL query. The key instruction here is that the query must include a count of rows alongside any grouped metric. The row count is what the validation stage will check next.
In practice, this often leads the model to write more sophisticated queries than a simple `GROUP BY`. For our example, the model constructed a common table expression (CTE) that ranks promotions by total units sold, joins the top two rankings into a single row, and calculates the percentage difference between them. The inclusion of `COUNT(*)` as `n_orders` is explicit and deliberate.
## Stage 4: Validation (Pure Code, No Model Call)
This is the stage that matters most. It is entirely deterministic — no LLM call, no interpretation, no judgment. The query result is executed, and any group with fewer than a minimum number of orders is flagged as low confidence.
The threshold is configurable, but the principle is non-negotiable: conclusions anchored on thin data should be marked as such, never presented as fact. On our dataset, the validation check had nothing to flag because the model’s chosen hypothesis compared two groups with 12 and 10 orders respectively, both comfortably above the minimum of 3. But the check ran anyway, and it would catch a fragile result just as readily in a different scenario or on a future dataset.
## Stage 5: Executive Summary
The fifth stage asks the model to write a concise, three-to-four sentence summary of what the validated result supports. The prompt explicitly instructs the model to exclude any low-confidence rows from the headline claim and to avoid inventing explanations that the data does not support. The model receives the full result table, the flagged rows, and the minimum support threshold as context.
This constraint is important because language models are prone to over-claiming. By telling the model precisely what it may and may not use as the basis for its summary, we prevent it from extrapolating beyond the evidence.
## Stage 6: Recommendations
The final stage builds on the executive summary to propose two or three specific business actions. The prompt reinforces the same rule: recommendations must follow from the evidence, must not rest on low-confidence data or invented facts, and should honestly recommend further analysis when the evidence is weak rather than manufacturing false certainty.
This stage mirrors how a real senior analyst communicates findings to a non-technical audience: clear actions, bounded by what the data actually supports, with an honest signal when more investigation is needed.
## Chaining the Pipeline
All six stages are orchestrated by a single `run` method. One call accepts a question and returns a dictionary containing every intermediate artifact: the business context, the generated hypotheses, the SQL plan, the validated result table, the executive summary, and the final recommendation. This transparency is a feature, not a bug — every decision the pipeline made is inspectable and auditable.
The setup code at the top of the execution block is identical regardless of provider. You set a provider constant, paste in your API key, and choose a model. The `LLMClient` wrapper absorbs all the provider-specific differences, so switching from Anthropic to OpenAI requires changing exactly two values.
## Key Takeaways
The individual stages in this pipeline are not technically complex. Restating a question, writing SQL, and summarizing a table are capabilities that a single prompt already handles adequately. The value lies in the architecture: the validation gate between query execution and summary generation is what transforms a casual answer into a trustworthy one.
On the dataset we explored, the plain SQL sanity check already exposed a promotion ranked first by average performance but supported by only a single order. The LLM-powered pipeline selected a different, more robust comparison — and the validation code confirmed it. Neither outcome depends on the other; each stage operates independently and contributes its own layer of rigor.
This toolkit is small — six methods on a single class — and fully reusable. Point it at a new table, feed it a new question, and it runs the same six stages with the same checks in place.
—
## Frequently Asked Questions
**What is the minimum data volume threshold used for validation?**
The example uses a threshold of 3 orders as the minimum support required before a group’s results are considered reliable. This threshold is configurable and should be adjusted based on the specific domain and dataset size. The key principle is that some threshold must exist and be enforced programmatically.
**Can this pipeline work with SQL databases other than DuckDB?**
DuckDB is used in the example for simplicity — it requires no server setup and queries DataFrames directly. The same architecture can be adapted to PostgreSQL, MySQL, or any other SQL-compatible engine by replacing the DuckDB connection with the appropriate connector. The validation logic itself is database-agnostic since it only inspects the result DataFrame.
**What happens if the LLM returns invalid JSON?**
The `parse_json` helper function first attempts to parse the entire response. If that fails, it strips Markdown code fences and then searches for the first valid JSON object or array in the text. If no valid JSON is found after all attempts, it raises a clear error rather than silently continuing with corrupted data.
**Why is the validation stage written in plain code instead of as an LLM prompt?**
Because this check must be enforced unconditionally. If you ask an LLM to “please check sample size,” it may or may not follow that instruction consistently. By implementing the check in deterministic Python code, the pipeline guarantees that low-confidence results are always caught, regardless of the model’s behavior.
**How does the pipeline handle questions that require joins or multiple tables?**
The current implementation is designed for a single table. Extending it to support multi-table analysis would require additional stages for schema discovery across tables and query planning that accounts for join keys and relationships. The six-stage framework is flexible enough to accommodate such extensions.
**Is this pipeline suitable for production use?**
The toolkit is a proof of concept and a teaching framework. For production deployment, you would want to add error handling, logging, retry logic for API calls, caching of intermediate results, and more sophisticated confidence scoring. The core idea — separating query execution from interpretation with a validation gate — is production-ready even if the implementation here is minimal.
**What are the limitations of this approach?**
The pipeline’s reliability depends on the LLM’s ability to write correct SQL and generate honest summaries. It does not catch all forms of analytical error — for instance, it checks row counts but does not perform statistical significance tests. It also inherits the biases and limitations of the underlying model. It is best understood as a structured framework for adding discipline to AI-assisted analysis, not as a replacement for human judgment.
—
## Conclusion
The gap between a chatbot’s instant answer and a senior analyst’s reliable conclusion is not a gap in intelligence — it is a gap in process. The chatbot answers. The analyst investigates. By encoding that investigation into a repeatable six-stage pipeline, we give any question the benefit of structured thinking: restatement, hypothesis, planning, validation, summarization, and recommendation.
None of these stages requires advanced machinery. They require only the discipline to run them in sequence and the rigor to enforce checks in code rather than relying on a model’s good intentions. The validation stage, in particular, is the linchpin — it is the moment where the pipeline decides whether a result is trustworthy enough to become a conclusion, and it does so without exception every time.
Whether you are working with 29 rows of order data or millions of records, the same principle applies: check the foundation before you present the answer.
Thank you for reading
