How to Build a Fully Automated AI Data Analyst

A complete, reproducible build. You give it a data request in plain English; it writes SQL against your warehouse, runs it, returns the result and the query that produced it, and gets more accurate every time you correct it.

This is the entire method, written out. Not an outline, not a teaser — the architecture, the agent instruction file, the schema context file, the build prompt, the QA loop, and the deployment step, with working templates you can copy.

I'm publishing it in full for a straightforward reason: the information isn't the hard part. Getting a system like this to produce numbers you'd actually put in front of an executive is the hard part, and that comes from iteration and correction, not from reading. If you can build it from this page alone, good — build it.

What you're building

A small web application with a prompt box. Someone types "weekly sales for Canada over the last 12 months, grouped by product category" and gets back a table, a link to a Google Sheet, and the SQL that produced it.

Underneath, an agent reads a documented description of your warehouse, writes a query, executes it read-only, and formats the output. A feedback box lets you correct it, and those corrections get written back into its context so the same mistake doesn't recur.

The distinction that matters here is between a chatbot and an agent. A chatbot answers questions about SQL. An agent connects to the warehouse, inspects what's actually there, writes a query against real tables, runs it, and puts the results somewhere. The second category is what makes this a tool rather than a demo.

The architecture

The build separates instructions into three levels. This mirrors how a functioning analytics team already works, and the separation is what keeps output predictable.

The single most important design decision is at the execution layer: prefer running an existing script over generating new code.

If the agent regenerates a query from scratch every time, you get a slightly different query every time, and you have to re-verify every time. If it writes the query once, you verify it once, and from then on it re-runs a known-good script, then the output is stable. It's also dramatically cheaper, because executing a saved script costs a fraction of what generating one does.

So the rule is: generate once, verify, save, re-run. New generation only when the request genuinely has no existing script behind it.

Prerequisites

• Comfort with a terminal at the level of "I can run a command someone gives me and read the error."
• Read access to a SQL warehouse — Snowflake, BigQuery, Azure SQL, Redshift, or plain Postgres all work. For practice, any public dataset loaded into a local Postgres is fine.
• Roughly $20–$100/month in model API usage depending on how much you run it, plus free tiers for hosting.
• You do not need to know how to write the application code. You do need to be able to read a SQL query and tell whether it's correct — that skill is load-bearing and there's no way around it.

Step 1 — Set up the environment

Install Visual Studio Code as the workspace and Claude Code as the agent that writes and runs code inside it. Other coding agents work; this is the pairing I use.

VS Code is where the files live and where you'll read what the agent produced. Claude Code is the part that does the work — it reads your instructions, writes files, executes commands, and reports back.

Once both are installed, open a new folder as a workspace and confirm the agent can see it. Ask it to create a file and list the directory. If that works, the environment is ready. This should take under fifteen minutes.

Step 2 — Structure the workspace

Agents behave far better against a predictable layout. Create this before you build anything:

ai-data-analyst/
├── CLAUDE.md              # agent instructions (see Step 3)
├── .env                   # credentials — never commit this
├── .gitignore
├── context/
│   ├── schema.md          # table + column map
│   ├── metrics.md         # business definitions
│   └── gotchas.md         # filters and rules not visible in schema
├── scripts/
│   └── verified/          # queries that have passed review
├── output/                # generated files, gitignored
└── app/                   # the web interface

The context/ directory is the substance of the whole build. Everything else is scaffolding the agent will generate for you.

Step 3 — Write the agent instruction file

This is the standing instruction set the agent reads before every task. Claude Code reads CLAUDE.md from the project root natively; AGENTS.md is the emerging cross-tool convention that several other agents also read. If you might switch tools later, write AGENTS.md and point CLAUDE.md at it. The content is what matters, not the filename.

Most people write this file too politely. It should read like a runbook for a contractor you don't fully trust yet — specific, blunt, and heavy on prohibitions.

# AI Data Analyst — Agent Instructions

## Role
You are the orchestration and execution layer for an internal
data-analysis tool. You turn plain-English data requests into verified
SQL, run it read-only against the warehouse, and return both the result
and the query that produced it.

## Hard rules

1. Never invent a table, column, or enum value. If it is not documented
   in `context/schema.md`, treat it as nonexistent. Ask instead of
   guessing.

2. Every result ships with the SQL that produced it. No exceptions.

3. Check `scripts/verified/` before writing new SQL. If a verified
   script answers the request, run that script. Say which one you ran.

4. If the request is ambiguous on grain, date range, filters, or
   segmentation, ask a clarifying question before querying. Do not
   pick a reasonable default and proceed.

5. Read-only. SELECT and WITH are permitted. INSERT, UPDATE, DELETE,
   DROP, CREATE, ALTER, TRUNCATE, MERGE and GRANT are forbidden.
   If a request requires a write, refuse and explain why.

6. Apply every rule in `context/gotchas.md` relevant to the tables you
   touch. These encode business logic invisible in the schema, and
   skipping them produces numbers that look right and are wrong.

7. Never print credentials, connection strings, or the contents of
   `.env` — not in output, not in logs, not in error messages.

8. Cap any unbounded query with a LIMIT and a date filter. If the user
   genuinely wants a full-table scan, make them ask twice.

## Workflow

Follow Directive → Orchestration → Execution.

**Directive.** Restate the request in one sentence. If step 4 above
applies, stop here and ask.

**Orchestration.** Before writing SQL, state:
  - which tables you will use and why
  - the join keys
  - the grain of the result (one row per what?)
  - the date range and timezone
  - which gotchas.md rules apply
Keep this to a short block. It is a plan, not an essay.

**Execution.**
  - Run an existing verified script, or write new SQL.
  - Execute read-only.
  - Write results to the configured output target.
  - Return: the result, the SQL, the row count, and the runtime.

## Quality assurance

After any query returns, sanity-check before presenting:
  - Does the row count match the stated grain?
  - Are there unexpected NULLs in join columns? Report them.
  - Does the total differ by more than 20% from the prior period?
    If so, flag it as "worth verifying" rather than presenting it flat.
  - Are there date gaps in a time series? Say so explicitly.

Never present a number you have flagged as suspicious without the flag
attached.

## Feedback protocol

When the user submits feedback through the app's feedback box:
  1. Append the rule to the correct file in `context/`, phrased as a
     durable instruction, not a one-off note.
  2. Add a dated line to the changelog at the bottom of that file.
  3. Re-run the failed request and show the corrected result.
  4. If the fix invalidates a script in `scripts/verified/`, move it
     out of `verified/` and say which one you moved.

## Output conventions
- Dates as ISO 8601 (YYYY-MM-DD). Always state the timezone.
- Currency: state the currency and whether values are gross or net.
- Percentages to one decimal. Absolute values unrounded.
- Column names in output are human-readable, not raw warehouse names.

Step 4 — Connect source and destination

The data source

Create a dedicated service account for the agent with read-only access, scoped to the schemas it needs. Do not reuse your own credentials. This matters for two reasons: it makes it structurally impossible for the agent to write to production, and it gives you a clean audit trail of which queries came from the tool.

Store credentials in .env, add .env to .gitignore before you put anything in it, and confirm the agent instruction file forbids printing them.

WAREHOUSE_ACCOUNT=your-account.region
WAREHOUSE_USER=svc_ai_analyst
WAREHOUSE_PASSWORD=
WAREHOUSE_ROLE=READ_ONLY_ANALYST
WAREHOUSE_DATABASE=ANALYTICS
WAREHOUSE_SCHEMA=PUBLIC

GOOGLE_SERVICE_ACCOUNT_JSON=./credentials/gsheets.json
OUTPUT_SHEET_ID=

ANTHROPIC_API_KEY=

The output destination

Google Sheets is the pragmatic default. It has a clean API, everyone already has access, and a shareable link is what most requesters actually want. Create a Google Cloud service account, enable the Sheets API, download the JSON key, and share the target spreadsheet with the service account's email address.

If you want dashboards rather than tables, point the output at Tableau or Power BI instead. The pattern is identical — the agent writes to a destination it has credentials for.

Step 5 — Build the context file

This is where builds succeed or fail. Everything up to here is configuration that anyone can follow. This step is where your actual knowledge of the data goes in, and it's the step people rush.

The framing that makes it click: hiring the best data analyst in the world doesn't help you on day one, because they don't know your database. They don't know which table is authoritative, which columns are deprecated, how orders join to customers, or that one particular status value has to be excluded from every revenue calculation. You'd spend a week teaching them. An agent is the same, except it won't tell you when it's confused — it will confidently produce a wrong number.

Documenting schema context for an AI system is well-established practice. What most people get wrong isn't the concept; it's the depth. A column list is not context. Context is the reasoning a senior analyst applies without thinking about it.

Building it in four passes

Pass 1 — Automated map. Point the agent at the warehouse and have it enumerate the schemas, tables, columns, types, row counts, and foreign keys you actively use. Have it write context/schema.md. This gets you structure for free and takes minutes.

Pass 2 — Brain dump. Talk, don't write. Open a session and dump everything you know: which tables you actually use versus which ones exist, how things join, what's deprecated, which filters you always apply. It doesn't need structure — the agent will organize it. If you have a folder of queries your team already trusts, hand those over too and ask it to infer conventions from them. Queries are the highest-density source of tribal knowledge you have.

Pass 3 — Interview the expert. If it's not your data — you're building this for a client, or for a team whose warehouse you don't know — get the person who does know on a recorded call and ask them to walk you through it. Zoom's built-in recording produces a transcript automatically; you don't need a separate notetaker. Paste the transcript in and have the agent draft the context file from it. This turns a one-hour call into documentation that probably didn't exist before, which is often worth something to the client on its own.

Pass 4 — Gotchas. Separate file, and the highest-value one.

An example of what belongs in gotchas.md

On a contract with a delivery marketplace, I needed delivery counts for a given period. The obvious query counts rows in the orders table for that date range.

It's wrong. The platform also supported carry-out, where the customer collects the order themselves. Carry-out orders land in the same orders table with the same shape, but they aren't deliveries. Count them and every delivery metric is inflated — by a stable, plausible-looking margin that nobody questions.

Nothing in the schema tells you this. The column exists, the values are valid, the query runs. You only know because you've worked with that data. That is exactly the class of knowledge that has to be written down.

# Business rules not visible in the schema

Every rule here has a reason. Do not remove one without
understanding why it was added.

---

## orders

**Exclude carry-out from all delivery metrics.**
`orders` contains both delivered and customer-collected orders.
Any metric described as "deliveries" must filter:

```sql
WHERE is_carryout = FALSE
```

Counting raw order rows overstates deliveries. The error is
proportional and looks plausible, so it will not be caught downstream.

**Exclude test accounts.**
```sql
AND customer_id NOT IN (SELECT customer_id FROM dim_customer
                        WHERE is_internal = TRUE)
```

**Cancelled orders remain in the table.**
`order_status` retains cancelled rows. Revenue metrics require
`order_status IN ('COMPLETED','SETTLED')`. Volume metrics may or may
not include cancellations — ask which the requester wants.

---

## Timezones

`created_at` is UTC. Regional reporting is expected in local time.
Convert before grouping by day, or the day boundaries will be wrong
and every daily figure will be slightly off.

---

## Deprecated

- `orders_v1` — frozen 2024-03. Never query. Use `orders`.
- `dim_customer_old` — superseded by `dim_customer`.

---

## Changelog
- 2026-06-14 — Added carry-out exclusion after Q2 delivery
  count was reported ~11% high.
- 2026-06-28 — Added timezone note after daily revenue
  mismatched the finance close by one day.

Step 6 — Write the build prompt

Now you tell the agent to build the application. This prompt is long on purpose. Specificity in, quality out — that relationship holds more strongly here than anywhere else in the process.

Six things must be in it:

1. Objective — what the finished app does.
2. Resources — the connections and context files it may use.
3. Process — the ordered steps for handling a request.
4. QA — the feedback mechanism that writes back to context.
5. Memory — conversation state, so follow-ups work.
6. Interface — what the user sees.

Build a web application called AI Data Analyst.

OBJECTIVE
A user submits a data request in plain English. The app returns the
result as a table, writes it to Google Sheets, and displays the SQL
that produced it.

RESOURCES
- Warehouse connection via the WAREHOUSE_* variables in .env (read-only)
- Google Sheets via GOOGLE_SERVICE_ACCOUNT_JSON, writing to
  OUTPUT_SHEET_ID
- context/schema.md, context/metrics.md, context/gotchas.md —
  load all three before generating any SQL
- scripts/verified/ — check here before writing new SQL

PROCESS
For each request:
1. Restate the request in one sentence.
2. If grain, date range, filters, or segmentation are ambiguous,
   ask a clarifying question and stop. Do not assume.
3. Check scripts/verified/ for an existing match. If found, run it
   and note which script was used.
4. Otherwise write new SQL, applying all relevant rules from
   gotchas.md.
5. Execute read-only.
6. Write results to a new tab in the output sheet, named with the
   request and an ISO date.
7. Return, in this order: the result table (first 100 rows),
   the shareable sheet link, the full SQL, the row count,
   and the runtime.

QUALITY ASSURANCE
Include a feedback box beneath every result. When the user submits
feedback:
- append it as a durable rule to the appropriate file in context/
- add a dated changelog entry
- re-run the request and show the corrected output
- if a verified script was invalidated, move it out of verified/
  and report which one

MEMORY
Persist conversation state within a session so follow-ups resolve
against prior turns. "Same view but two years" must work. Show the
resolved interpretation before running, so the user can catch a
wrong reference.

INTERFACE
Single page. Prompt box at the top. Results below. Collapsible SQL
panel. Feedback box under each result. A sidebar listing the
session's prior requests. No other features — do not add
authentication, user accounts, charting, or export options unless
I ask for them.

CONSTRAINTS
- Read-only database access. Reject any write operation.
- Never render credentials or .env contents anywhere in the UI
  or in error messages.
- Every generated query gets a LIMIT unless I explicitly override.
- Log every executed query with a timestamp to output/query_log.jsonl.

That last constraint block is worth keeping even when you're moving fast. The query log is what lets you answer "where did this number come from?" three weeks later.

Step 7 — Run it and validate

Execute the prompt and let the agent build. Expect iteration — connection errors, permissions issues, a UI that isn't quite right. This is normal, and correcting it is faster than writing any of it yourself.

Then, before you trust it with anything real:

1. Ask it questions you already know the answer to. Pull three or four numbers you can verify independently — last month's revenue, a count you've reported before. If it can't reproduce known figures, it cannot be trusted on unknown ones.
2. Read every generated query. Not skim, read. This is the load-bearing skill in the whole build. A second model reviewing the SQL in a separate session catches a useful fraction of mistakes, but it does not replace you reading it.
3. Deliberately test the gotchas. Ask for delivery counts and confirm the carry-out filter appears. If a documented rule doesn't get applied, the context file isn't being loaded properly and everything downstream is suspect.
4. Try an ambiguous request. Ask "how are sales doing?" It should ask you a clarifying question. If it produces a confident answer instead, rule 4 isn't landing and you need to tighten the instruction file.

Once a query is verified, save it into scripts/verified/ with a comment recording what it answers and when you checked it. That library is the asset. After a few months it's most of what the system runs.

Step 8 — Deploy

Vercel is the path of least resistance. Create an account, point it at your repository, set the environment variables in the project settings, and deploy.

Two things before anyone else touches it:

• Put authentication in front of it. An unauthenticated endpoint that queries your production warehouse on demand is not something you want on the public internet. Vercel's built-in password protection is a reasonable minimum; SSO is better.
• Confirm .env never entered the repository. Check the git history, not just the current state. If credentials were ever committed, rotate them — removing the file doesn't remove it from history.

If it's just for you, running locally is completely legitimate and skips both concerns.

Operating it in practice

Building it is the smaller half. How you position yourself around it determines whether it's useful.

The instinct is to hand it to the business and let people self-serve. Resist that, for three reasons that are really the same reason.

First, requests arrive vague. "Show me how sales in Canada are doing" is not answerable — sales of what, over what period, at what grain, segmented how? An analyst knows to go back and ask. An end user typing into a box doesn't know they need to, and they'll accept whatever comes back.

Second, output needs verification. Even a well-built system will occasionally produce a query that runs clean and answers the wrong question. Someone has to read the SQL. That someone needs to be able to read SQL.

Third — and this is the part people are reluctant to say out loud — if the business can query it directly, the business doesn't need you. That's not a reason to hide the tool. It's a reason to be the person operating it.

So the workflow is:

1. The request comes to you.
2. You pin down specifics — grain, period, segmentation, filters. Usually one or two follow-up questions.
3. You write the precise prompt: "Weekly sales for Canada, last 12 months, grouped by product category and subcategory."
4. You read the SQL. If it's wrong, you correct it through the feedback box so the correction persists.
5. You deliver the result.

You've cut the mechanical work substantially. The judgment — translating a vague ask into a precise question, and knowing when a number looks wrong — stays with you, because that's the part that was never really about typing SQL.

If you're building this for clients rather than an employer, the same structure is why this is a retainer and not a one-off build. The system needs an operator who maintains the context file as the business changes. That's ongoing work, and it's the honest version of the pitch.

Where this breaks

Worth being direct about, because knowing the limits is most of what separates someone who can operate this from someone who just built it once.

• Undocumented data. If nobody can explain how the warehouse works, the context file can't be built, and the system will be confidently wrong. This is the most common failure and it isn't a technical problem.
• Genuinely novel questions. Anything requiring experiment design, causal reasoning, or a judgment call about methodology. It will produce an answer. The answer will be shaped like an analysis and won't be one.
• Warehouse drift. Schemas change. Columns get deprecated, tables get rebuilt, business definitions shift. The context file goes stale silently — nothing errors, the numbers just quietly stop being right. Someone has to own keeping it current.
• Scale and cost. Fine for ad-hoc analysis. Not a replacement for a modeled semantic layer if you're running hundreds of queries a day, and the credit cost of generating rather than re-running adds up fast if you skip the verified-scripts discipline.
• Governance. In a regulated environment, an agent with warehouse access is a compliance conversation before it's a technical one. Have it early.

None of this makes the build less useful. It does mean the value sits with the person who understands the data well enough to write the context file and read the output — not with the tooling, which anyone can install in an afternoon.