Prompt Patterns for Data Analysts: 20 Prompts That Save Hours

Most analysts write prompts like search queries: short, vague, and missing the one detail the model needs. A prompt with a schema, a business question, and an output contract gets usable code on the first pass. This guide gives you a reusable prompt structure, a Python helper that builds the context for you, and 20 copy-ready prompts grouped by workflow stage.

Quick Takeaways

• Structure beats cleverness. Every strong prompt has five parts: Role, Context, Task, Constraints, Output format.
• Ground the model in your schema. Paste column names, dtypes, null rates, and 3-5 masked sample rows. Never paste raw PII.
• Verify before you trust. Run LLM-generated code on a sample and assert row counts, key uniqueness, and null behavior.
• Prompts save the most time on repetitive work: profiling, cleaning, SQL drafting, test writing, and stakeholder summaries.

Time estimates are rough averages from typical analyst workflows. Your results depend on dataset complexity and how well you supply context.

The Anatomy of a Reliable Analyst Prompt

A prompt is a spec. Treat it like a ticket you would hand to a junior analyst who has never seen your warehouse.

Skip any one of these and the model fills the gap with a guess. The most common failure is missing context. The second most common is no output format.

Step Zero: Build the Context Block Automatically

Retyping schema details into every prompt wastes time and introduces errors. This helper generates a compact, PII-safe description of any DataFrame.

import pandas as pd


def build_context_block(
    df: pd.DataFrame,
    name: str = "df",
    sample_rows: int = 5,
    pii_cols: list[str] | None = None,
) -> str:
    """Return a compact schema + sample description for pasting into an LLM prompt."""
    pii_cols = pii_cols or []
    safe = df.drop(columns=[c for c in pii_cols if c in df.columns])  # never expose PII

    schema = pd.DataFrame({
        "dtype": safe.dtypes.astype(str),
        "null_pct": (safe.isna().mean() * 100).round(1),  # missingness at a glance
        "n_unique": safe.nunique(dropna=True),            # flags IDs vs. categoricals
    })

    sample = safe.head(sample_rows).to_csv(index=False)

    return (
        f"DataFrame `{name}`: {len(df):,} rows x {safe.shape[1]} columns\n\n"
        f"SCHEMA:\n{schema.to_string()}\n\n"
        f"SAMPLE ({sample_rows} rows, PII removed):\n{sample}"
    )


# Usage
# context = build_context_block(orders, name="orders", pii_cols=["email", "phone"])
# print(context)  # paste above your task in the prompt

Paste the output beneath your task description. Every prompt below assumes you have done this.

20 Prompts, Grouped by Workflow Stage

Replace the [BRACKETED] placeholders. Each template follows the five-part structure in compressed form.

Data Exploration and Profiling

Prompt 1: Fast data profile.

Role: senior data analyst. Using the schema and sample below, list (1) likely
primary keys, (2) columns with suspicious distributions or mixed types,
(3) five questions this dataset can answer, (4) three data quality risks.
Output: four numbered sections, max 5 bullets each.
[CONTEXT BLOCK]

Prompt 2: Hypothesis generator.

Business question: [QUESTION]. Given this schema, propose 5 testable hypotheses.
For each: the columns needed, the metric, the expected direction, and one
confounder that could mislead me. Output as a table.
[CONTEXT BLOCK]

Prompt 3: Anomaly checklist.

Write a Pandas script that flags, per numeric column: values beyond 3 IQR
fences, negative values where none are expected, and sudden day-over-day
jumps over [X]%. Return a tidy DataFrame of flagged rows. Include comments.
[CONTEXT BLOCK]

Prompt 4: Data dictionary draft.

Draft a data dictionary: column name, plain-English definition, inferred
grain, allowed values, and a "confirm with owner" flag where you are guessing.
Output as Markdown table. Do not invent business rules; mark unknowns.
[CONTEXT BLOCK]

Cleaning and Feature Engineering

Prompt 5: Cleaning pipeline.

Write a reusable Pandas function `clean_[NAME](df)` that: standardizes column
names to snake_case, parses [DATE_COLS] to UTC, strips whitespace in strings,
coerces [NUMERIC_COLS] with errors="coerce", and logs rows changed per step.
Return the cleaned frame. No in-place mutation.
[CONTEXT BLOCK]

Prompt 6: Missing-data strategy.

For each column with nulls, recommend a strategy (drop, median, mode, model-based,
or flag-only) and justify it by missingness mechanism (MCAR, MAR, MNAR).
Then provide Scikit-Learn `ColumnTransformer` code implementing it.
[CONTEXT BLOCK]

Prompt 7: Feature ideas.

Target: [TARGET]. Propose 12 engineered features from this schema (recency,
frequency, ratios, rolling windows, interactions). For each, give the
Pandas expression and a leakage warning if relevant. Output as a table.
[CONTEXT BLOCK]

Prompt 8: Regex and string repair.

Write a regex and a Pandas `.str.extract()` call to parse [FIELD] from these
examples: [5-10 EXAMPLES, INCLUDING EDGE CASES]. Add 5 unit tests with
`pytest` covering malformed inputs.

SQL and Query Work

Prompt 9: Schema-aware SQL draft.

Dialect: [BIGQUERY/SNOWFLAKE/POSTGRES]. Tables: [DDL OR COLUMN LISTS].
Task: [BUSINESS QUESTION IN PLAIN ENGLISH]. Use CTEs, explicit join keys,
and comments. State the grain of each CTE. List assumptions after the query.

Prompt 10: Query review.

Review this SQL for: fan-out joins, NULL-handling bugs, timezone issues,
non-sargable filters, and unnecessary scans. For each issue, show the line
and the fix. Then provide an optimized version.
[SQL]

Prompt 11: Window function builder.

Write SQL using window functions to compute [METRIC: e.g., 7-day rolling
retention, running total, rank within group]. Explain the PARTITION BY and
ORDER BY choices in one sentence each.

Prompt 12: SQL-to-Pandas translation.

Translate this SQL to idiomatic Pandas. Preserve join semantics (inner vs. left),
NULL behavior, and sort order. Flag any place where the two engines
behave differently.
[SQL]

Statistics and Modeling

Prompt 13: Test selector.

I want to compare [METRIC] between [GROUP A] and [GROUP B]. Sample sizes:
[N1], [N2]. Data is [NORMAL/SKEWED/BINARY]. Recommend a statistical test,
state its assumptions, give SciPy code, and explain how to report effect size.

Prompt 14: Baseline model scaffold.

Build a Scikit-Learn pipeline for [CLASSIFICATION/REGRESSION] on target
[TARGET]. Include a train/test split with stratification, preprocessing,
cross-validation, and metrics: [AUC-ROC, F1, RMSE]. Use a fixed random_state.
Return runnable code only.
[CONTEXT BLOCK]

Prompt 15: Model error analysis.

Here are predictions and actuals [PASTE SUMMARY OR SAMPLE]. Write code to
slice errors by [SEGMENTS], rank the worst segments by RMSE contribution,
and suggest three feature or data fixes per weak segment.

Prompt 16: Assumption audit.

Audit this analysis for statistical errors: multiple comparisons, peeking,
survivorship bias, Simpson's paradox risk, and leakage. List each risk as
Present / Absent / Unknown with evidence.
[ANALYSIS DESCRIPTION OR CODE]

Visualization, Reporting, and Communication

Prompt 17: Chart selection.

I need to show [INSIGHT] to [AUDIENCE]. Data: [COLUMNS]. Recommend the best
chart type, a runner-up, and what to avoid. Then write Matplotlib or Plotly
code with labeled axes, a takeaway title, and colorblind-safe colors.

Prompt 18: Executive summary.

Summarize these results for a non-technical VP in under 120 words: lead with
the decision implication, give two supporting numbers, and name one risk.
No jargon. No hedging phrases.
[RESULTS TABLE]

Prompt 19: Stakeholder Q&A prep.

Given this analysis, list the 8 toughest questions a skeptical stakeholder
will ask, with a concise evidence-based answer for each. Flag any question
I cannot answer with current data.
[SUMMARY]

Prompt 20: Documentation generator.

Write a README for this notebook: purpose, inputs, steps, outputs, how to
rerun, and known limitations. Add docstrings to each function in the code.
[CODE]

Choosing the Right Prompting Technique

The templates above mix several techniques. Match the technique to the task.

For high-stakes work, chain two prompts: generate the draft, then run Prompt 10 or Prompt 16 against it.

Verify LLM Output Before You Ship It

Generated code often runs without errors and still produces wrong numbers. This validator catches the usual failures after a cleaning or transformation step.

import pandas as pd


def validate_transform(raw: pd.DataFrame, clean: pd.DataFrame, key: str) -> dict:
    """Sanity-check an LLM-written transformation. Returns pass/fail per check."""
    common = raw.columns.intersection(clean.columns)  # tolerate added/renamed columns

    checks = {
        # Set to False manually if the transform intentionally dedupes or filters
        "row_count_preserved": len(raw) == len(clean),
        "key_is_unique": clean[key].is_unique,
        "no_new_nulls": bool((clean[common].isna().sum() <= raw[common].isna().sum()).all()),
        "no_columns_lost": set(raw.columns).issubset(clean.columns),
    }

    failed = [name for name, ok in checks.items() if not ok]
    if failed:
        print(f"FAILED: {failed}")  # surface failures loudly
    return checks


# Usage
# results = validate_transform(raw_orders, clean_orders, key="order_id")

Always test on a 1,000-row sample first. Compare one aggregate (a sum or count) against a figure you already trust.

Practical Examples and Real-World Use Cases

Predicting Customer Churn with Python

Start with Prompt 1 to profile the subscription table. Use Prompt 7 to generate recency, frequency, and support-ticket features. Prompt 14 produces the baseline pipeline, and Prompt 15 shows which customer segments the model misses. Run Prompt 16 before you present, because churn labels often leak through “cancellation date” fields.

Handling Missing Data in Real-Time Sensor Streams

IoT streams drop packets in bursts, which breaks the MCAR assumption. Use Prompt 6 and add this constraint: “Data arrives in 1-minute intervals; propose forward-fill limits and flag gaps over 5 minutes.” The model typically suggests time-aware interpolation plus a missingness indicator feature. Verify the choice against known sensor outage logs.

Automating the Weekly KPI Report

Feed the week’s metric table to Prompt 18 for the summary. Use Prompt 19 to prepare answers before the review meeting. Then use Prompt 20 to document the notebook so a teammate can rerun it. Teams that template this chain remove most of the manual write-up time.

Common Prompting Mistakes

Build Your Own Prompt Library

Store your best prompts in a versioned file next to your code. Name them by task, keep placeholders consistent, and note the dialect and library versions each was tested against. Review the library quarterly. When a prompt produces a bad result, fix the template instead of patching the single output.

FAQ

What are the best prompts for data analysts?

The best prompts include a role, your schema, a specific task, constraints, and an output format. For daily work, the highest-value templates cover data profiling, cleaning pipelines, SQL drafting, query review, and executive summaries. Each of these has a clear input and a checkable output.

Can ChatGPT or Claude replace a data analyst?

No. LLMs speed up code drafting, documentation, and summarization, but they cannot confirm business definitions, detect stale data, or take accountability for a decision. Analysts still define the question, validate the numbers, and interpret results in context.

How do I stop an LLM from hallucinating columns or functions?

Supply the exact schema and sample rows in every prompt, name the library and version, and require an assumptions list. Then run the code on a sample and compare one output against a trusted number. Hallucinated column names fail immediately at runtime, but wrong logic needs the validation step.

Is it safe to paste company data into an AI tool?

Only if your organization’s policy allows it. Remove or mask PII, credentials, and confidential identifiers first. Share the schema and a masked sample rather than full tables, and prefer an approved enterprise deployment over personal accounts.