Data analysts spend roughly 60% of their week on work that isn't analysis — cleaning data, writing boilerplate SQL, formatting charts for stakeholders, and translating findings into language executives can act on. AI doesn't replace the analysis itself, but it dramatically speeds up the mechanical scaffolding around it.
I've been using a set of AI prompts to compress that scaffolding time. Below are 10 prompts I use regularly, each with the exact structure (Role → Context → Constraints → Output), a real example of what it produces, and notes on where it tends to hallucinate so you know what to verify.
If you want the full collection — 50+ prompts covering analytics, operations, sales, marketing, and engineering workflows — I maintain a Developer Productivity Prompt Library ($49) and an AI Startup Operations Prompt System ($199) for teams that need a complete system.
1. SQL Query Builder from Natural Language
Most "text-to-SQL" tools generate syntactically correct but semantically wrong queries — they miss joins, assume column names, or aggregate incorrectly. This prompt forces the model to state its assumptions before writing the query.
You are a senior data analyst. I will describe a question in plain English.
Before writing SQL, output:
1. Your understanding of the question (1 sentence)
2. Every table and column you assume exists (list them)
3. Any ambiguity in the question
Then write the SQL. Use CTEs for readability. Add a comment above each CTE explaining what it does.
Question: {YOUR_QUESTION}
Schema context: {PASTE_CREATE_STATEMENTS_OR_SAMPLE_ROWS}
Example output (for "What's the 30-day retention rate for users who signed up via organic search?"):
The model lists: users (user_id, signup_date, acquisition_channel), events (user_id, event_name, event_timestamp). Flags ambiguity: "retention" can mean D30 login or D30 revenue event. Asks you to confirm. Then writes a CTE-based query with WITH cohort AS (...) SELECT ....
Where it hallucinates: column names. Always paste your actual CREATE TABLE statements or a few sample rows. Never let it guess.
2. Dashboard Design Critique
Stakeholders ask for "a dashboard" and then complain it has too many charts. This prompt forces the model to evaluate a dashboard layout against decision-making criteria — not aesthetics.
You are a BI strategist reviewing a dashboard for an executive audience.
I will describe the dashboard's current layout (charts, filters, layout).
Evaluate it against these criteria:
1. Can the viewer answer their #1 question in under 10 seconds?
2. Is there a chart that doesn't change any decision? (If yes, recommend removing it.)
3. Are the time ranges consistent across charts?
4. Is there a clear "so what" — or is it just data display?
Output: a prioritized list of changes (impact × effort), then a revised layout sketch in text.
Example output: Identifies a "funnel by channel" chart that duplicates information already in a summary table. Recommends removing it. Suggests moving the "revenue trend" chart to top-left because it answers the most frequent stakeholder question. Flags inconsistent date ranges (one chart shows trailing 7 days, another shows calendar month).
3. Anomaly Investigation (Statistical)
When a metric drops, the first instinct is to check everything. This prompt forces structured root-cause analysis.
You are a data analyst investigating an anomaly.
Metric: {METRIC_NAME} dropped {PERCENTAGE}% on {DATE}.
Normal range: {DESCRIBE_BASELINE}
Generate 5 hypotheses ranked by likelihood. For each:
1. The hypothesis (what changed)
2. The dimension to slice by (segment, channel, geography, device, etc.)
3. The query to test it (SQL or pseudo-code)
4. What result would confirm vs. rule out this hypothesis
Do not speculate without giving me a test. Every hypothesis must be falsifiable.
Example output (for "conversion rate dropped 22% on July 15"): Hypothesis 1 — payment provider outage (slice by payment_method, check error rates). Hypothesis 2 — checkout page deploy (slice by version hash). Hypothesis 3 — traffic source shift (slice by utm_source, check if a low-quality traffic source spiked). Each with a specific SQL query.
4. Executive Insight Summary
Analysts write insights that are technically correct but unreadable to executives. This prompt enforces the "inverted pyramid" — conclusion first, evidence second, detail last.
You are a data analyst writing for a VP who reads this in 45 seconds during a commute.
Convert my raw findings into an executive summary.
Rules:
- First sentence states the conclusion and the action it implies.
- No raw numbers in the first paragraph — round to the nearest meaningful unit ("roughly 1 in 4 users" not "24.7%").
- Include exactly ONE chart recommendation (chart type + what it shows).
- End with "Open question:" followed by the one thing you couldn't determine from the data.
- Ban these words: leverage, utilize, robust, granular, actionable, holistic, synergy.
Raw findings: {PASTE_YOUR_NOTES_OR_QUERY_RESULTS}
Example output: "Organic search users retain 40% better than paid, but only on mobile. Recommend shifting 15% of the mobile paid budget to SEO content production. Open question: we can't tell if this is a creative quality issue or a targeting issue from this data alone."
5. Data Quality Audit (Column-by-Column)
Before any analysis, you need to profile the data. This prompt generates a systematic audit plan.
You are a data quality analyst. I will provide a table schema.
For EACH column, generate:
1. Expected data type and format
2. 3 potential quality issues (nulls, outliers, format drift, encoding, duplicates, referential integrity)
3. The SQL to check each issue
4. A severity rating (critical / warning / info)
Schema: {PASTE_CREATE_TABLE_STATEMENT}
Sample data: {OPTIONAL_PASTE_5_ROWS}
Example output: For a user_email column — checks for nulls (WHERE email IS NULL), format validity (WHERE email NOT LIKE '%_@_%._%'), duplicates (SELECT email, COUNT(*) GROUP BY email HAVING COUNT(*) > 1). Severity: critical for nulls (breaks downstream), warning for format issues, info for case sensitivity inconsistencies.
6. Metric Definition Document
Teams argue about what "active user" means. This prompt generates a formal metric definition that eliminates ambiguity.
You are a data governance analyst.
Define the metric "{METRIC_NAME}" in a way that any new analyst could compute it identically.
Output:
1. Plain-English definition (1 sentence)
2. Exact SQL or formula
3. Inclusions (what counts)
4. Exclusions (what does NOT count — be specific)
5. Edge cases (new users, deleted users, bot traffic, internal accounts)
6. Refresh cadence (real-time / hourly / daily) and why
7. 3 common misinterpretations to warn stakeholders about
Example output (for "Weekly Active User"): Defines WAU as "distinct user_id with at least one session_start event in the trailing 7-day window ending at midnight UTC Sunday." Excludes internal test accounts (email domain @company.internal). Edge cases: users who deleted their account mid-week still count for the weeks they were active. Common misinterpretation: "active" does not mean "engaged" — a user who opened the app and immediately closed it still counts.
7. A/B Test Result Interpreter
Test results are often misread — people focus on the headline lift and ignore statistical significance, segment interactions, and novelty effects.
You are an experimentation analyst. I will provide A/B test results.
Analyze them and output:
1. Headline: Did the treatment win? (State the lift, confidence interval, and p-value.)
2. Significance check: Is this result trustworthy? (sample size, power, multiple testing correction if applicable)
3. Segment breakdown: Did any subgroup behave differently? (If I provide segment data, analyze it. If not, list the 3 segments I should check.)
4. Novelty effect check: Is the lift decaying over time? (If I provide time-series data, analyze it. If not, tell me what pattern to look for.)
5. Recommendation: Ship / iterate / kill — with reasoning.
Raw results: {PASTE_TEST_DATA}
Example output: "Treatment won with +8.2% lift (95% CI: +2.1% to +14.3%, p=0.012). However, the mobile segment shows -3% while desktop shows +18% — this is a heterogeneous treatment effect. Do not ship globally without segment-specific analysis. The lift is stable over the 14-day window (no novelty decay detected). Recommendation: ship to desktop only, iterate on mobile."
8. Data Pipeline Documentation
Pipelines break when no one understands them. This prompt generates documentation from a code description.
You are a data engineer documenting a pipeline for a new team member.
I will describe the pipeline steps (sources, transformations, destinations).
For each step, document:
1. What it does (1 sentence)
2. Input: source table/API/file + expected schema
3. Output: destination table/file + resulting schema
4. Failure mode: what happens if this step fails (does downstream break? is there a fallback?)
5. Owner: who to contact (leave as TBD if unknown)
Then add a "Data lineage" section showing the flow as a text diagram:
source → transform → destination
Pipeline description: {DESCRIBE_YOUR_PIPELINE}
Example output: Documents a Kafka → Flink → S3 → Snowflake pipeline. Each step has input/output schemas, failure modes (Kafka lag alert, Flink checkpoint failure, S3 write timeout). Data lineage: [Kafka topic: events] → [Flink: dedupe + enrich] → [S3: parquet partitioned by date] → [Snowflake: external table] → [dbt: mart].
9. Stakeholder Request Triage
When a stakeholder asks for "a quick analysis," it's rarely quick. This prompt helps scope the request before committing.
You are a data analyst triaging an incoming request.
I will paste the stakeholder's message (Slack, email, or ticket).
Output a triage document:
1. Restate the business question in one sentence (what decision are they trying to make?)
2. Data requirements: which tables, what time range, what granularity
3. Complexity estimate: simple lookup (1 hr) / multi-table join (4 hrs) / statistical analysis (1+ day)
4. Ambiguities: what's unclear about the request? List questions to ask the stakeholder.
5. Suggest a "good enough" version if the full analysis would take >1 day.
Stakeholder message: {PASTE_THE_REQUEST}
Example output (for "Can you tell me why revenue is down?"): Business question: "Is the revenue decline a real trend or noise, and what's driving it?" Data requirements: revenue table (30-day + 30-day prior), sliced by product, region, customer tier. Complexity: multi-table join + segment analysis (4-8 hours). Ambiguities: "revenue" — gross or net? What time frame? Compared to what? Good-enough version: top-line trend + top 3 declining segments, deliverable in 2 hours.
10. Code Review for SQL / dbt
Data code reviews often skip logic errors because the reviewer focuses on syntax. This prompt enforces a logic-first review.
You are a senior data analyst reviewing SQL/dbt code.
Review for LOGIC errors, not style.
Check specifically:
1. JOIN correctness: are you joining at the right granularity? (Cartesian explosion risk)
2. Filtering: is the WHERE clause applied before or after aggregation? (Common error)
3. NULL handling: do COUNT(column) vs COUNT(*) give different results? Are NULLs excluded from AVG?
4. Time zone: are all timestamps in the same zone? Are you comparing UTC to local?
5. Window functions: is the PARTITION BY correct? Are you using ROWS vs RANGE correctly?
6. Deduplication: is there a risk of counting the same event twice?
Code: {PASTE_SQL_OR_DBT_MODEL}
Example output: Flags a LEFT JOIN on user_id where the right table has multiple rows per user — causing row multiplication. Identifies a WHERE event_date >= '2026-01-01' applied after a COUNT(*) window function, meaning it counts all-time events then filters. Recommends moving the filter into a CTE before the window function.
How I Test These Prompts
Every prompt above follows a testing protocol:
- Run 3 times with the same input — if the output varies significantly, the prompt is too vague. Tighten constraints.
- Generalize — after getting a good output for one dataset, try it on a different schema. Does it still work?
- Read the output aloud — if it sounds like AI-generated filler, add a negative constraint (ban specific words).
- Cut anything generic — if the output could apply to any dataset without modification, it's not adding value. Delete it and make the prompt more specific.
Want the Full System?
These 10 prompts are a starting point. If you're building a data-informed company and want a complete prompt system covering analytics, operations, sales, marketing, engineering, and leadership workflows:
- Developer Productivity Prompt Library — 30 prompts for individual contributors ($49)
- AI Startup Operations Prompt System — 50+ prompts + 10 workflows for teams ($199)
- AI Business Transformation Playbook — 100+ prompts + 12 workflows for org-wide adoption ($999)
Or start small with the Developer Product-Launch Prompt Pack ($9) — 7 prompts for shipping your next product.
What prompts do you use for data work? I'm always looking to add to this list — drop your favorites in the comments.
Top comments (0)