How to write AI prompts for data analysis work

A good prompt for data work has four layers: a role for the model, a spec that says exactly what you want, a shape for the answer, and a list of things the model must refuse to do. “Write me revenue by region” is only a wish, but a spec that names the level of detail, the filters, and the calendar is a contract the model can visibly fail against.

A wish is not a prompt. Models will happily grant a wish, and they may do it with invented columns, a fuzzy date range, and a join (a step that combines two tables) that counts refunds twice. The fix is to write a short, structured brief instead of a clever one-liner.

The four-layer prompt stack

Most useful data prompts are short structured briefs. Use the four layers below every time, until it becomes muscle memory, because a prompt missing one layer is the usual reason an answer comes back vague.

• Role: who the model is pretending to be, and who it is not. “Senior analytics engineer drafting for review” is very different from “executive ghostwriter.”
• Spec: the question, what one row of the result should represent, the time range, the filters, the metric definition, and which tables it may use.
• Output: the exact shape you want back, such as SQL (the standard language for asking a database for data) only, SQL plus a bullet list of risks, a table layout, or a checklist.
• Refuse: what the model must not do, such as inventing columns, inventing numbers, or hiding uncertainty.

A role without a spec is cosplay, and a spec without an output shape is an essay generator. An output shape without refusal rules is how you get beautifully formatted, invented fields, so stack all four.

Role: useful, not theatrical

Role lines work when they constrain behavior, and they fail when they turn into movie casting. Here is a useful one.

You are assisting an analytics engineer. Draft SQL for human review.
Prefer explicit joins and filters. Flag assumptions. Do not present
draft numbers as facts.

And here is a less useful one.

You are the world's greatest data scientist with 40 years of experience
at FAANG and you never make mistakes.

The second role encourages confidence theater, where the model sounds sure whether or not it should be. The first role matches the idea from the earlier post on where AI helps and where it does not: assist a person, do not replace one. Keep the role short and spend your words on the spec.

Spec: the contract the query must honor

If you would not accept a work ticket without certain fields, do not accept a prompt without them either. The table below lists those fields.

This is the same discipline as a metric one-pager in the metrics series. You are not being picky for its own sake. You are removing the freedom that models otherwise fill with fiction.

When the list of allowed tables is large, do not paste the whole data lake. Paste only the slice you need. The earlier post on what to give a model taught the same packing rule: two tables and a sample of rows beat a dump of 200 tables.

Output: shape the reply before it exists

An open-ended “explain your thinking” request often produces a long story with the SQL buried in the middle, or SQL that never runs because the model preferred storytelling. For data work, say in advance what order and format you want.

A strong default for asking for query help has four parts.

• Section 1 is the SQL alone, in one code block.
• Section 2 is a bullet list of assumptions and risks.
• Section 3 is a list of checks you should run, such as row counts, blank values, and known totals.
• Write no board narrative until you paste back verified results.

A strong default after you have a result table is different.

• Ask for three plain-language takeaways and two caveats, so the summary admits what it does not know.
• Ask for one question the table cannot answer, so the model states its limits instead of stretching the data.
• Tell the model not to invent extra metrics.

That split is the practical version of “SQL first, then explain.” You draft the transform against a contract, and you narrate only from verified outputs. It matches how careful people already work, and it matches the liability map from the earlier post on who is responsible for AI-assisted work.

Refuse: make hallucination expensive for the model

Models are trained to be helpful, and helpfulness without refusal rules turns into fabrication. Write your refusals as explicit instructions and not as vague hopes.

Refuse rules:
1) Use only tables and columns listed in the spec.
2) If a needed column is missing, stop and ask. Do not invent names.
3) Do not invent numeric results. You have no warehouse access.
4) If definitions conflict, list the conflict. Do not blend them.
5) If the question is underspecified, ask up to three questions, then stop.

Refusal is not only about columns. Tell the model to refuse requests to paste secrets into a tool that has not been approved, to refuse “make the growth look better,” and to refuse citing studies you never provided. Your job is to keep the assistant honest.

Worked example: weak versus strong for revenue by region

Here is the same business question with two completely different contracts.

Weak prompt

Write SQL for revenue by region last month and explain what drove
the results. Use best practices.

This prompt is likely to fail in several ways.

• It may invent tables like sales, regions, or customers.
• “Last month” stays vague, and it may use the wrong time zone.
• “Revenue” is an undefined column, so the model has to guess what it means.
• It may add a causal story with no data behind it, such as “strong demand in the West.”
• It has no filter for test accounts.

Strong prompt

ROLE
You assist an analytics engineer. Draft for review. No fake results.

SPEC
Question: net revenue by region for calendar month 2026-01 (UTC).
Grain: one row per region_name.
Metric: net_revenue_usd = SUM(gross_amount_usd - discount_usd)
Filters: is_test = false only.
Join: fct_orders.region_code = dim_region.region_code
Time: order_ts_utc >= '2026-01-01' AND order_ts_utc < '2026-02-01'
Allowed objects only:
  fct_orders(order_id, order_ts_utc, region_code, gross_amount_usd,
             discount_usd, is_test)
  dim_region(region_code, region_name)
Known gap: refunds are not in these tables. Mention that in risks.

OUTPUT
1) SQL only in one fenced block
2) Bullets: assumptions
3) Bullets: checks I should run after executing
4) Do NOT write a board narrative or driver story yet

REFUSE
- Do not invent tables, columns, or numbers
- If something required is missing, ask instead of guessing
- Do not blend alternate revenue definitions

A reasonable draft response would look like the query below. It is illustrative SQL that you would still run and check.

SELECT
  r.region_name,
  SUM(o.gross_amount_usd - o.discount_usd) AS net_revenue_usd
FROM fct_orders AS o
JOIN dim_region AS r
  ON o.region_code = r.region_code
WHERE o.is_test = false
  AND o.order_ts_utc >= TIMESTAMP '2026-01-01 00:00:00'
  AND o.order_ts_utc < TIMESTAMP '2026-02-01 00:00:00'
GROUP BY r.region_name
ORDER BY net_revenue_usd DESC;

This query adds up net revenue (gross amount minus discounts) for each region in January 2026, leaves out test orders, and lists the regions from highest to lowest. It uses only the one agreed revenue definition, which is what makes the answer comparable to other reports.

Next to that draft you want to see assumptions and checks like these.

• Assumption: the region comes from the order, not from the customer’s home region.
• Assumption: discounts are never blank. If they can be, wrap them with COALESCE, which swaps a blank for zero.
• Check: the count of test rows that were excluded.
• Check: the sum of net revenue matches a known Finance figure within a small tolerance, because matching a trusted total is the quickest proof the query is right.
• Check: regions with a blank region_name after the join.
• Risk: refunds were not applied.

Only after the query runs and the result table is real do you open a second prompt for the narrative.

ROLE: plain-language editor for a product lead.
SPEC: use ONLY the verified table below. No extra metrics.
OUTPUT: 3 takeaways, 2 caveats, 1 follow-up analysis question.
REFUSE: do not invent drivers not present in the table.

region_name,net_revenue_usd
US East,420150.25
US West,301992.10
EU,188440.00

That second call is still assistance, because you own the slide.

Pattern library for common data tasks

1. Draft SQL for a known metric

Use the stack of a role, a full metric spec, the allowed tables, SQL-first output, and a rule against invented columns. Then follow the steps from the tutorial on checking AI-written SQL: read the query plan, look at sample rows, and compare to a known total.

2. Explain a query you already trust

Paste the SQL and ask for an explanation aimed at a specific audience. Add a refusal such as “do not change the logic while explaining.” That is safer than asking the model to invent the query and the story together.

3. Cleanup plan for a dirty extract

Paste a profile of the file, or twenty rows, and not the whole file. Ask for an ordered checklist covering data types, blank values, duplicates, and categories. Connect the results to the habits in the data quality series and to the practical reshaping in the Python series.

4. Metric definition stress test

Paste your draft metric spec and ask: “List ambiguous phrases, missing filters, and two ways two teams could compute different numbers.” Refuse numeric invention here, because this is a design review and not a calculator.

5. Incident notes

When a pipeline (the automatic steps that load and clean data) breaks, paste the error, the job name, and the level of detail you expected in the result. Then ask for an order in which to investigate, and do not paste secrets. The stewardship and pipeline series cover who owns what and how data moves, and the model only helps you organize the hunt. See data pipelines and data stewardship.

SQL then explain: a non-negotiable order

Why force this order? There are four reasons.

• Auditability: SQL is something you can run and check, while a story is not.
• Less made-up detail: models love causal language, and causal language without a table is fiction writing.
• Cleaner reviews: reviewers can argue about a join, but they cannot efficiently argue with a paragraph that hides the join.
• Better teaching: junior analysts learn the contract of the query and not the tone of the memo.

There is one exception, which is pure writing tasks on tables you have already verified, such as slide bullets or an email summary. Even then, paste the table and forbid new metrics.

Connecting craft you already have

Prompt patterns do not replace SQL skill. They steer the model toward drafts that your SQL skill can judge. The same goes for Python transforms, quality checks, and metric ownership. If retrieval enters the picture, meaning the model first searches your wiki and then answers, the ideas about vector search and retrieval from the post on vector databases become relevant. Even so, retrieved text can be wrong or stale, so the refusal rules and human checks stay in place.

For broader paths, use the SQL series, the tutorials list, and the rest of the series map on the site.

Common mistakes

• Wish prompts: one sentence with no table names, while expecting production SQL.
• Role theater: a long persona wrapped around a short contract.
• Narrative first: asking for the drivers before you have a query that runs.
• Soft refusal: writing “try not to invent columns” when you should write “stop if a column is missing.”
• Definition shopping: re-prompting until the growth number matches the story you wanted.
• Skipping the human checklist: treating the first draft as ready to ship.
• One mega-prompt for five jobs: table discovery, five metrics, slides, and an email all in one call.

Practice: turn one ticket into a stack

Pick a real open question from your backlog. Write the four headings Role, Spec, Output, and Refuse in a document, and fill them in without opening a model. Then paste the stack into your approved tool and run the SQL. Complete the checks for AI-written SQL, and only then ask for a narrative on the verified result table.

Save the stack as a team template. Next time a colleague says “just ask ChatGPT,” send them the template instead of a lecture.

The next post in the series covers evals for humans, which means golden questions, spot-checks, and a small set of test prompts you rerun after every change to catch anything that got worse, so your patterns stay honest as models and tables change.

Quick recap

• Use a four-layer stack: role, spec, output, and refuse.
• Specs need the level of detail, the time range, the metric math, the filters, and the allowed tables.
• Shape the reply so that SQL comes first, checks come second, and the narrative comes only after verified results.
• Refuse invented columns, invented numbers, and blended definitions.
• Weak prompts are wishes, and strong prompts are contracts you can test.
• Patterns amplify the craft you already have in SQL, Python, quality, metrics, and stewardship. They do not replace it.

Your next step

Rewrite one prompt you use for data work with the four layers: role, spec, output, and refuse. Include the allowed tables and the metric math in the spec. Run old and new side by side on the same question. Seeing fewer invented columns in the new answer is the proof the structure works.

Series notes

This is Part 3 of Practical AI for analytics people. Next: evals for humans.

Sources

• OpenAI, prompt engineering guide
• Anthropic, prompt engineering overview and chain-of-thought guidance
• Google, prompting strategies in the Gemini API docs
• OpenAI, “Hello GPT-4o” and the model system cards family (capability and limitation framing evolves; read current model docs)
• National Institute of Standards and Technology (NIST) AI Risk Management Framework 1.0 (map, measure, and manage risk for AI-assisted workflows)
• Bender et al., “On the Dangers of Stochastic Parrots” (fluent text is not grounded knowledge)
• Google People + AI Research (PAIR), People + AI Guidebook (human-AI collaboration patterns)

Merch on Etsy

Open store →

Apache Iceberg T-Shirt View on Etsy → Mean Squared Error Shirt View on Etsy → You're On Mute Coffee Mug – Data Science Gift View on Etsy → Naive Bayes Coffee Mug View on Etsy →