Prompting for analysts

Most bad answers from a model in analytics work are good answers to an under-specified question. The model doesn’t know your schema, how your organisation defines a DNA, which SQL dialect you run, or what the number is for. It fills each gap with the most typical guess, and typical is often wrong for your data.

The patterns below close those gaps. The general techniques come from Anthropic’s prompting best practices; the analytics framing and the examples are mine. For ready-made prompts built on them, see the AI playbook.

Give it the context a new colleague would need

Anthropic’s docs suggest treating the model as “a brilliant but new employee who lacks context on your norms and workflows”, and offer a test: show your prompt to a colleague with minimal context on the task and ask them to follow it. If they’d be confused, the model will be too.

For analytics work that context is the schema with its keys, the dialect, the grain you want, the definitions, and what the output is for.

Before:

Write a SQL query for the DNA rate by clinic last month.

After:

I need the DNA (did not attend) rate by clinic for September 2026, for a monthly outpatient performance report. We use SQL Server.

<schema>
appointments(appt_id PK, clinic_code FK, appt_start datetime2 in UK local time, status varchar)
clinics(clinic_code PK, clinic_name, site)
</schema>

<definition>
DNA rate = appointments with status 'DNA' / appointments with status 'Attended' or 'DNA'.
Cancelled appointments and appointments with no status yet are excluded from both.
</definition>

Return one row per clinic: clinic_code, clinic_name, attended, dna, dna_rate.
Include clinics with no appointments in the month, with NULL for the rate.

The second prompt removes six guesses: the dialect, the period, the denominator, what happens to cancellations and NULL statuses, the grain, and whether empty clinics appear. Each of those guesses was a chance for a quietly wrong number.

Say what the output should look like

State the format you want: one query or several, CTEs or not, comments, column names, a list of assumptions, a test query. The docs also recommend telling the model what to do rather than what not to do. “Write the query as CTEs, one per step, each with a comment saying what one row represents” gets further than “don’t write messy SQL”.

Ask for assumptions and edge cases

Ask the model to list its assumptions before it writes code, and to name the edge cases that would change the answer. The list shows you where it guessed. In analytics the usual suspects are NULLs, duplicate keys, cancelled or reversed records, boundary dates, time zones, and records that arrive late.

Give it explicit permission to say it doesn’t know, too. Anthropic’s guide to reducing hallucinations lists this as a basic technique.

Before:

Write a DAX measure for the average wait in weeks.

After:

Write a DAX measure for the average wait, in weeks, of patients still waiting at the end of the selected month.

<model>
Waits[Referral Date], Waits[Clock Stop Date] (blank while the patient is still waiting)
'Date' is marked as a date table. Active relationship: Waits[Referral Date] -> 'Date'[Date]
</model>

Before the code, list your assumptions about how the selected month reaches the measure, what it should return at the grand total, and what "still waiting" means on the last day of the month.
Then name the edge cases that would change the result.
If this model description isn't enough to write a correct measure, say what's missing instead of guessing.

Written this way, the prompt leaves room for the real problem to surface: the active relationship on referral date is the wrong one for a month-end snapshot, and patients whose clock stopped after the month end were still waiting at it. You want to hear that before you get code, not after.

Show an example of what you want

When the output needs a house style, such as metric documentation, commit messages, or a briefing format, an example works better than a description. The docs call examples one of the most reliable ways to steer format, tone, and structure. They suggest three to five for best results, wrapped in <example> tags so the model can tell them apart from the instructions. Make them varied, so the model doesn’t pick up patterns you didn’t intend, such as a fixed length or subject.

Structure long inputs

When you paste a lot of material (DDL for twenty tables, a long stored procedure, a data sharing agreement), wrap each piece in its own tag, such as <schema>, <code>, and <definition>, so the instructions and the inputs can’t blur together. Put the long material first and the question last. For long inputs, Anthropic’s docs report that queries at the end can improve response quality by up to 30% in their tests, especially with complex, multi-document inputs.

Split big tasks

“Build me the referrals pipeline” produces a lot of plausible code that you then have to review all at once. Split it into steps you can check: agree the grain and definitions, then the staging query, then the tests, then the final model. The docs call this prompt chaining and describe it as useful when you need to inspect the intermediate outputs. In analytics you almost always do.

Make it critique its own output against a checklist

The docs describe the most common chaining pattern as self-correction: generate a draft, have the model review it against criteria, then refine it. The criteria are what make it work. “Check your work” gets a vague yes. A checklist gets specific answers.

Before:

Is this query correct?

After:

Review the query below against each item. For each, answer PASS, FAIL, or UNSURE and quote the line that decides it.
1. Can any join multiply rows?
2. Does NOT IN, = or <> silently drop NULLs?
3. Is any condition on a LEFT JOINed table in WHERE instead of ON?
4. Are date ranges half-open (>= start AND < end)?
5. Is there integer division?
6. Is DISTINCT hiding duplicates?
Then give a corrected query if anything failed.

<query>
{query}
</query>

A yes/no question invites a yes/no answer. A verdict per item, backed by a quoted line, makes the model look at each line. The full checklist, with wrong and right examples in SQL and DAX, is in Reviewing AI-written DAX and SQL.

Iterate with test data

Give the model a small fixture with the answers you expect, then run what it writes yourself. A model tracing its query through five rows in prose is not evidence. Your database returning the expected rows is.

Below is a small fixture and the result I expect from it. Write the query for the general definition, not for these rows: the fixture is there to check the query, not to define it.
Then list which fixture rows test which part of the definition. I'll run the query and paste back any differences.

<fixture>
{insert_statements}
</fixture>

<expected>
{expected_rows}
</expected>

The “general definition” line matters. Anthropic’s docs note that Claude can sometimes focus too heavily on making tests pass at the expense of more general solutions, and a query tuned to five rows is no use on five million.

When the result differs, paste the actual output back rather than describing it, so the model reasons from the exact rows.

Anti-patterns

Vague asks

“Analyse this”, “make it better”, “write a query for waiting times”. The model will answer, fluently, a question you didn’t quite ask. If you can’t state the grain and the definition yet, the next step is a conversation with the stakeholder, not a prompt.

Pasting whole datasets

This fails twice. First on governance: patient-identifiable or confidential data must never go into a tool your organisation hasn’t approved for it. Then on technique: a language model reading rows is a poor substitute for code running over them, and it can’t be relied on to count, sum, or find the one bad row among thousands. Compute a profile locally, give the model the schema, the definitions, and the summary, and have it write the code that you then run.

Trusting fluent numbers

A model writes “DNAs rose 14% year on year” in the same confident tone whether it calculated the figure, copied it, or made it up. Treat every number in model output as a claim. Recompute it in code from the source data. For text-heavy tasks, ask the model to quote where each figure came from and to withdraw any claim it can’t support, which is one of the grounding techniques Anthropic documents. Numbers should reach stakeholders from your queries, not from the model’s prose.

Reviewing in the conversation that wrote the code

A review in the same thread shares the drafting assumptions. For anything that matters, start a fresh conversation, give it the intent and the schema, and ask it to find problems.

Keep the prompts that work

A prompt that produced a good measure is worth keeping. Store it in the repository next to the code it produced, with placeholders instead of data, and review changes to it the way you’d review changes to code. The AI playbook has 18 to start from. If you run an agent inside your repository rather than a chat window, Writing CLAUDE.md and AGENTS.md covers how to give it this context once instead of pasting it every time.