Prompt patterns for data work

“Write me revenue by region” is not a prompt. It is a wish. Models will happily grant a wish with invented columns, a fuzzy date range, and a paragraph that sounds like it belongs in a quarterly letter. You will spend the next hour debugging confidence.

Analysts already know how to write better contracts. Metric specs, query comments, ticket acceptance criteria: same idea, smaller surface. Prompt patterns for data work are just those contracts written so a language model can draft something you can test.

This is Part 3 of Practical AI for analytics people. Part 1 set assist versus replace and liability. Part 2 packed the context suitcase. Here we stack a reusable prompt pattern: role, spec, output shape, and refuse rules, with a hard preference for “SQL first, explain second” and zero patience for hallucinated columns. Pair this with How to check AI-written SQL and the vocabulary in What are LLMs?.

What you will learn

A four-layer prompt stack for analytics tasks
How to write specs and constraints the model cannot “smooth over”
Why SQL-then-explain beats narrative-first answers
Refuse rules that block invented tables, columns, and sources
A side-by-side weak versus strong prompt for revenue by region
Mistakes and a practice loop for your next real ticket

The four-layer prompt stack

Most useful data prompts are not clever one-liners. They are short structured briefs. Use four layers every time until it is muscle memory:

Four step prompt pattern stack role spec output refuse

Role: who the model is pretending to be, and who it is not. “Senior analytics engineer drafting for review” is different from “executive ghostwriter.”
Spec: the question, grain, time range, filters, metric definition, and allowed objects.
Output: the exact shape you want back (SQL only, SQL plus bullet risks, table schema, checklist).
Refuse: what the model must not do (invent columns, invent numbers, hide uncertainty).

Role without spec is cosplay. Spec without output is a essay generator. Output without refuse is how you get beautiful invented fields. Stack all four.

Role: useful, not theatrical

Role lines work when they constrain behavior, not when they cosplay cinema.

Useful:

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.

Less useful:

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. The first role aligns with Part 1: assist, not replace. Keep the role short. Spend the tokens on the spec.

Spec: the contract the query must honor

If you would not accept a ticket without these fields, do not accept a prompt without them:

Spec field	Example	Why it matters
Decision or question	Board-ready net revenue by region	Stops random exploration
Grain	One row per region for the month	Prevents fan-out math
Time range	Calendar month 2026-01 in UTC	Avoids “last month” ambiguity
Metric definition	net = gross – discount	Blocks vibe metrics
Filters	exclude is_test = true	Removes silent pollution
Allowed objects	fct_orders, dim_region only	Blocks schema invention
Keys and joins	region_code	Stops creative join paths
Known unknowns	refunds not in these tables	Forces honesty

This is the same discipline as a metric one-pager in the metrics series. You are not being pedantic. You are removing degrees of freedom that models fill with fiction.

When the allowed objects are large, do not paste the lake. Paste the slice. Part 2’s suitcase rules still apply: two tables and a sample beat a 200-table dump.

Output: shape the reply before it exists

Open-ended “explain your thinking” often produces a long narrative with SQL buried in the middle, or SQL that never runs because the model preferred storytelling. For data work, specify order and format.

Strong default for query help:

Section 1: SQL only in one code block
Section 2: assumptions and risks as bullets
Section 3: checks I should run (row counts, nulls, known totals)
No board narrative until I paste verified results

Strong default after you have a result table:

Three plain-language takeaways
Two caveats
One question the table cannot answer
Do not invent extra metrics

That split is the operational version of “SQL then explain.” Draft transforms against a contract. Narrate only from verified outputs. It matches how careful humans already work, and it matches the liability map from Part 1.

Refuse: make hallucination expensive for the model

Models are trained to be helpful. Helpfulness without refusal rules becomes fabrication. Write refuses as explicit instructions, not vibes.

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.

Refuse is not only for columns. Refuse secret paste requests you should not fulfill in an unapproved tool. Refuse “make the growth look better.” Refuse citations to studies that were never provided. Your job is to keep the assist lane honest.

Worked example: weak vs strong for revenue by region

Same business ask. Completely different contracts.

Side by side weak prompt versus strong prompt for revenue by region

Weak prompt

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

Likely failure modes:

Invented tables like sales, regions, customers
Vague “last month” in the wrong timezone
“Revenue” as an undefined column
A causal story with no data (“strong demand in the West”)
No test-account filter

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 shape (illustrative SQL 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;

Assumptions and checks you want to see next to that draft:

Assumption: region comes from the order, not the customer home region.
Assumption: discounts are never null; if they can be, wrap with COALESCE.
Check: count of test rows excluded.
Check: sum of net revenue matches a known Finance tile within tolerance.
Check: regions with null region_name after the join.
Risk: refunds not applied.

Only after the query runs and the table is real do you open a second prompt for 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 assist. You own the slide.

Pattern library for common data tasks

1. Draft SQL for a known metric

Stack: role + full metric spec + allowed schema + SQL-first output + no invented columns. Then run the AI SQL check tutorial steps: read the plan, sample rows, compare to a known total.

2. Explain a query you already trust

Paste the SQL and ask for audience-specific explanation. Refuse: “do not change the logic while explaining.” This is safer than asking the model to invent the query and the story together.

3. Cleanup plan for a dirty extract

Paste a profile or twenty rows, not the whole file. Ask for a ordered checklist: types, nulls, dupes, categories. Connect results to habits in the data quality series and practical reshaping in the Python series.

4. Metric definition stress test

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

5. Incident notes

When a pipeline breaks, paste the error, the job name, and the expected grain. Ask for a triage order. Do not paste secrets. Stewardship and pipeline series cover ownership and path; the model only helps you structure the hunt. See data pipelines and data stewardship.

SQL then explain: a non-negotiable order

Why force the order?

Auditability: SQL is a artifact you can run. A story is not.
Reduced confabulation: models love causal language. Causal language without a table is fiction writing.
Cleaner reviews: reviewers can argue about a join. They cannot efficiently argue with a paragraph that hides the join.
Better teaching: juniors learn the contract of the query, not the tone of the memo.

Exception: pure writing tasks on already verified tables (slide bullets, email summary). Even then, paste the table and forbid new metrics.

Connecting craft you already have

Prompt patterns do not replace SQL skill. They route the model toward drafts that your SQL skill can evaluate. Same for Python transforms, quality checks, and metric ownership. If retrieval enters the picture (search your wiki, then answer), vector and RAG ideas from vector databases become relevant. Still: retrieved text can be wrong or stale. Refuse rules and human checks stay.

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

Common mistakes

Wish prompts: one sentence, no schema, expect production SQL.
Role theater: long persona, short contract.
Narrative first: asking for drivers before a runnable query.
Soft refuse: “try not to invent columns” instead of “stop if missing.”
Definition shopping: re-prompting until growth matches the story.
Skipping the human checklist: treating the first draft as the ship artifact.
One mega-prompt for five jobs: schema discovery, five metrics, slides, and email 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 / Refuse in a doc. Fill them without opening a model. Then paste the stack into your approved tool. Run the SQL. Complete the AI SQL checks. Only then ask for a narrative on the verified result table.

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

Coming next in the series: evals for humans (golden questions, spot-checks, a tiny regression set of prompts) so your patterns stay honest as models and schemas change.

Quick recap

Use a four-layer stack: role, spec, output, refuse.
Specs need grain, time, metric math, filters, and allowed objects.
Shape the reply: SQL first, checks second, narrative only after verified results.
Refuse invented columns, invented numbers, and blended definitions.
Weak prompts are wishes. Strong prompts are contracts you can test.
Patterns amplify existing craft in SQL, Python, quality, metrics, and stewardship. They do not replace it.

Sources

OpenAI, prompt engineering guide: https://platform.openai.com/docs/guides/prompt-engineering
Anthropic, prompt engineering overview and chain-of-thought guidance: https://platform.claude.com/docs/en/build-with-claude/prompt-engineering/overview
Google, prompting strategies in Gemini API docs: https://ai.google.dev/gemini-api/docs/prompting-strategies
OpenAI, “Hello GPT-4o” and model system cards family (capability and limitation framing evolves; read current model docs): https://platform.openai.com/docs/models
NIST AI RMF 1.0 (map/measure/manage risk for AI-assisted workflows): https://www.nist.gov/itl/ai-risk-management-framework
Bender et al., “On the Dangers of Stochastic Parrots” (fluent text is not grounded knowledge): https://dl.acm.org/doi/10.1145/3442188.3445922
Google PAIR, People + AI Guidebook (human-AI collaboration patterns): https://pair.withgoogle.com/guidebook