Behnam Analytics

Applied AI

AI playbook

Ground rules, 18 copyable prompts, and step-by-step workflows for using AI in SQL, DAX, Python, and stakeholder work without letting it decide what a number means.

How I use AI in analytics work

I use AI models for four jobs:

  • Drafting: first versions of SQL, DAX, Python, tests, and documentation.
  • Reviewing: a second reader that checks code against a checklist I give it.
  • Explaining: inherited code, unfamiliar functions, and statistical methods I want to sanity-check.
  • Automating the boring parts: data dictionaries, test boilerplate, synthetic fixtures, and turning notes into a README.

The model drafts and I own the result. It never decides what a metric means, and a number it produced never reaches a stakeholder until I’ve reproduced it in code. The rules below are what keep that true.

Ground rules

  1. Use only tools your organisation has approved. Never paste patient-identifiable, personal, or commercially confidential data into a tool that isn’t approved for it. If you’re not sure whether a tool is approved, treat it as not approved and ask your information governance team.
  2. Give the model the schema and the definitions, not the data. Table definitions, relationship lists, metric definitions, and a column profile you computed locally are enough for almost every task on this page.
  3. Ask for assumptions before answers. Every prompt below asks the model to state its assumptions and uncertainties. That list is often more useful than the code, because it shows where the model filled a gap with a guess.
  4. Verify every number. Numbers come from code you ran on the real data, never from the model’s prose. If a model writes “a 12% increase”, that’s a claim to check, not a result.
  5. Read every line before it reaches a stakeholder or production. If you can’t explain a line, it doesn’t ship.
  6. Test against a small dataset with known answers. A handful of rows where you’ve worked out the right result by hand catches mistakes that reading the code misses. Reviewing AI-written DAX and SQL shows how.
  7. Keep prompts in version control. Store the prompts that work next to the code they produce, with placeholders and no data, so a colleague can rerun them and you can see what changed when results change.

For rule 2, this profile is usually all the model needs to reason about a table. Check it before sharing: the minimum of a date-of-birth column is one person’s date of birth.

import pandas as pd


def profile(df: pd.DataFrame) -> pd.DataFrame:
    """One row per column: type, nulls, distinct values, and range for numbers and dates."""
    ranged = df.select_dtypes(include=["number", "datetime"])
    return pd.DataFrame(
        {
            "dtype": df.dtypes.astype(str),
            "nulls": df.isna().sum(),
            "distinct": df.nunique(),
            "min": ranged.min(),
            "max": ranged.max(),
        }
    ).reindex(df.columns)

Understanding a dataset

First look at a new table

Use when a new extract or table lands and you need its grain, a draft data dictionary, and questions for the data owner.

You are a senior analyst helping me understand a table before I build reporting on it. I can't share row-level data, so you have the schema and a column profile I computed locally.

<context>
Source system and what it records: {source_description}
What I need to report from it: {reporting_goal}
</context>

<schema>
{table_ddl}
</schema>

<column_profile>
{profile_output}
</column_profile>

Tasks:
1. State what you think one row represents (the grain) and the evidence for it in the schema and profile.
2. Draft a data dictionary as a table: column, likely meaning, type, null count, notes on anything suspicious.
3. List candidate keys and what would have to be true for each to be unique.
4. List the questions I should ask the data owner before trusting this table, most important first.

Label every inference as an inference. Where the profile doesn't support a conclusion, say so instead of guessing.

Draft data quality tests

Use before a table feeds a dashboard, to turn the grain and the business rules into tests you can schedule. Data tests that catch real problems covers which tests earn their keep.

You are an analytics engineer writing data tests for a {sql_dialect} table that feeds operational reporting.

<schema>
{table_ddl}
</schema>

<business_rules>
{rules_in_plain_english}
</business_rules>

Write each test as a SQL query that returns zero rows when it passes and the offending rows when it fails. Cover:
- uniqueness of the grain: {grain_columns}
- columns that must never be null
- accepted values for coded columns
- referential integrity to {related_tables}
- date logic (for example, discharge not before admission) and freshness

For each test give a short name, the query, and the business rule it protects.
Then list any rule you couldn't turn into a test because the schema lacks the information, and any rule you think is ambiguous.

Explain inherited code

Use when a stored procedure, Power Query script, or notebook lands on you without documentation.

I've inherited the {language} code below and need to understand it before I change anything. Explain it to an analyst who knows {language} but not this system.

<code>
{code}
</code>

<known_context>
{what_you_know_about_its_purpose_and_inputs}
</known_context>

Give me:
1. A summary in five sentences or fewer: inputs, output, and the grain of the output.
2. A walkthrough with one step per block of logic, quoting the lines each step refers to.
3. Every hard-coded business rule (codes, dates, thresholds, exclusions) as a table: line, value, what it appears to do.
4. Anything that looks like a bug, dead code, or a silent assumption, with your confidence in each.

Describe what the code does, not what it was probably meant to do. Where the intent is unclear, say so.

SQL

Write a query from a definition

Use when you have an agreed metric definition and need a first draft of the query behind it.

You are a senior analyst writing {sql_dialect} for a reporting pipeline. Correctness matters more than brevity.

<schema>
{relevant_table_ddl_with_keys}
</schema>

<definition>
Metric: {metric_name}
Definition: {metric_definition}
Inclusions and exclusions: {rules}
Output grain: one row per {grain}
Period and the date column that defines it: {period_and_date_column}
</definition>

Before writing the query, list your assumptions about keys, NULLs, status codes, date boundaries, and time zones, and the edge cases that would change the answer.

Then write one query using CTEs, with a comment above each CTE saying what one row of it represents.
- Use half-open date ranges (>= start AND < end).
- Don't use SELECT DISTINCT to remove duplicates. If a join could multiply rows, aggregate first or use EXISTS, and say which you chose and why.
- Guard every division against zero and integer division.

Finish with a query that checks the output has exactly one row per {grain}.

Speed up a slow query

Use when a query returns the right answer but takes too long, and you have the execution plan.

This {sql_dialect} query is correct but slow. Help me make it faster without changing its result.

<query>
{query}
</query>

<environment>
Row counts per table: {table_row_counts}
Indexes, partitioning, or clustering: {index_list}
Execution plan or query profile: {plan_text}
How often it runs and how long it takes now: {runtime}
</environment>

1. Point to the most expensive parts of the plan, quoting them, and explain why they cost what they do.
2. Propose changes in order of expected benefit, keeping query rewrites separate from index or schema changes.
3. For each rewrite, explain why the result stays identical, including for NULLs and duplicate keys.
4. Tell me what to measure to confirm the improvement.

If the plan doesn't show enough to be confident, say what else you need rather than guessing.

DAX and Power BI

Write a measure from a definition

Use when you need a new measure and can describe the model and the visuals it will sit in.

You are an experienced Power BI developer. Write a DAX measure for the semantic model described below.

<model>
Tables and relevant columns: {tables_and_columns}
Relationships (many side -> one side, cardinality, filter direction, active or inactive): {relationships}
Existing measures I can reuse: {existing_measures}
</model>

<definition>
Measure name: {measure_name}
Business definition: {measure_definition}
Where it will be used (visual type, rows, columns, slicers): {visual_usage}
A case I've checked by hand and its expected value: {known_case_and_value}
</definition>

Before the code, state which filters the measure must respect, remove, or override, and which relationship it relies on.

Write the measure using variables for intermediate values, DIVIDE for division by any expression that can be zero or blank, and a comment on each filter modifier.
Then say what it returns at the grand total and for a row with no data.
Finish with a DAX query (DEFINE MEASURE ... EVALUATE SUMMARIZECOLUMNS ...) that I can run in DAX query view or DAX Studio to check the known case.

Rewrite a slow measure

Use when a measure is right but a visual is slow, and you’ve captured timings. For how to capture them, see Power BI performance starts with VertiPaq.

This DAX measure returns the right numbers but is slow in {visual_description}. Rewrite it for speed without changing any result.

<measure>
{measure_code}
</measure>

<model>
{tables_row_counts_and_relationships}
</model>

<timings>
{performance_analyzer_or_server_timings}
</timings>

1. Explain where the time is probably going (for example FILTER over a whole table, context transition inside an iterator over a large table, the same measure evaluated several times), citing the code and the timings.
2. Give the rewritten measure and explain, change by change, why results stay the same at totals, for blanks, and under slicers on {slicer_columns}.
3. List any case where the rewrite could return a different result, however rare.
4. Write a DAX query that returns only the rows where the old and new measures disagree, using == so that BLANK and 0 count as different.

Explain what a measure does in a visual

Use when a number in a report surprises you, or before you change a measure someone else wrote. CALCULATE and filter context covers the rules the answer should follow.

Explain, step by step, how this DAX measure is evaluated in one specific visual. I know DAX basics.

<measure>
{measure_code}
</measure>

<model>
{relationships_and_relevant_columns}
</model>

<visual>
Type: {visual_type}; rows: {rows}; columns: {columns}; slicers and filters: {slicers}
</visual>

For one data cell and for the grand total:
1. List the filters in context before the measure runs.
2. Show how each CALCULATE, filter modifier, and iterator changes that context.
3. Say what value you'd expect and why.

Then point out anything that would surprise a report user, such as a total that isn't the sum of the rows. Mark any step where you're unsure how the engine behaves.

Python and pandas

Turn notebook code into a tested function

Use when exploratory code needs to become something a pipeline can run.

Turn this notebook code into a reusable Python function with tests.

<code>
{notebook_cells}
</code>

<context>
Input: {input_description_and_columns}
Output: {expected_output}
Python and library versions: {versions}
</context>

Requirements:
- One function with type hints and a docstring that states the input columns, output columns, and output grain.
- No hidden state: no globals, no dependence on earlier cells, and a fixed random seed if anything is random.
- Identical behaviour, including NaN handling and row order. If you think the original has a bug, flag it separately rather than fixing it silently.
- pytest tests built from small inline DataFrames covering: normal rows, missing values, duplicate keys, and an empty input.

List any behaviour of the original you weren't sure how to preserve.

Generate a synthetic test dataset

Use when you need realistic data to build or test something without touching real records. Synthetic health data: what it’s for and where it stops covers what it can and can’t stand in for.

Write a Python script, using only numpy and pandas, that generates a synthetic dataset for testing {pipeline_or_report}. No real people, organisations, or identifiers.

<schema>
{target_schema}
</schema>

<patterns_to_plant>
{patterns, for example: weekly cycle, bank holiday dips, 5% missing discharge dates, duplicate keys, codes in mixed case}
</patterns_to_plant>

Requirements:
- A fixed random seed, so a rerun produces identical output.
- Row count and date range as parameters, defaulting to {rows} and {date_range}.
- Each planted pattern in its own small function named after the pattern, so the planted problems are documented in the code.
- A final check that prints the statistics showing each pattern is present.

Tell me which patterns you couldn't make realistic with this schema.

Forecasting and statistics

Plan a forecast backtest

Use before building a forecast, to agree how it will be judged. Backtesting forecasts with rolling origins explains the method.

You are a forecasting practitioner helping me design an honest evaluation for {series_description}.

<data>
Frequency and length of history: {frequency_and_length}
Known patterns and breaks: {seasonality_holidays_breaks}
How the forecast will be used: {decision_and_horizon}
</data>

Propose:
1. Candidate methods, starting with a seasonal naive baseline, and why each suits this series.
2. A rolling-origin backtest: number of origins, horizon, step, and minimum training window.
3. Accuracy metrics suited to the decision (for example MAE, MASE, prediction interval coverage), and why.
4. How to check the prediction intervals, not only the point forecasts.
5. The ways this evaluation could still flatter a model, such as leakage, revised history, or holidays falling in the test window.

Don't predict which method will win. Tell me what result would make you prefer each one.

Sanity-check a change in a rate

Use when someone reports that a rate “went up” and you need to decide whether it’s signal before anyone acts. SPC charts for operational metrics covers the better view.

A stakeholder says {rate_name} changed from {rate_before} ({numerator_before} of {denominator_before}) in {period_before} to {rate_after} ({numerator_after} of {denominator_after}) in {period_after}. Help me decide whether it's worth acting on.

1. Say whether the change could plausibly be noise at these counts, and show the calculation (for example, a confidence interval for each rate or a two-proportion test), with the assumptions it makes.
2. List non-statistical explanations to rule out first: definition or coding changes, case mix, data completeness, seasonality, one-off events.
3. Suggest a better view than two points, such as a run chart or SPC chart over {n_periods} periods.
4. Draft two sentences for the stakeholder that carry the right amount of uncertainty.

Show your arithmetic step by step so I can recompute it in code.

Documentation

Document a metric

Use when a measure or KPI goes live, or when you find one that nobody can define. Metric definitions as code takes the idea further.

Write documentation for the metric below, following the structure and tone of the example. The readers are report users and other analysts.

<example>
{one_existing_metric_doc_in_house_style}
</example>

<metric>
Name: {metric_name}
Code (SQL or DAX): {code}
Agreed business definition: {definition}
Owner (role, not name): {owner_role}
</metric>

Include: a plain-English definition, numerator and denominator, inclusions and exclusions, grain, refresh frequency, known caveats, and how it differs from similar metrics ({similar_metrics}).

Where the code and the agreed definition disagree, don't pick one. List the difference under "Open questions".

Write a pipeline README

Use when a pipeline needs to survive you being on leave.

Write a README for the data pipeline below, for an analyst who has to run or fix it without me.

<pipeline>
Files and what each does: {file_list_with_purposes}
Schedule and upstream dependencies: {schedule}
Inputs and outputs: {inputs_outputs}
Known failure modes and fixes: {failure_notes}
</pipeline>

Sections: purpose (two sentences), how to run it, what success looks like (row counts and checks), common failures and fixes, who to contact (by role), and what not to change without review.

Use commands and paths exactly as I've given them. If a reader would need something that isn't in my notes, add a clearly marked TODO rather than inventing it.

Stakeholder communication

Clarify an ambiguous request

Use when a request arrives as one line (“can I get waiting times by site?”) and the answer depends on details nobody has stated.

A stakeholder sent this data request:

<request>
{request_text}
</request>

<context>
Their role and what they're likely to use it for: {stakeholder_context}
Data I have available: {available_data}
</context>

1. Restate the request as a precise analytic question: metric, population, period, and comparison.
2. List the ambiguities that would change the answer, each with two or three plausible interpretations.
3. Draft a reply under 150 words, in plain English, asking the three most important clarifying questions. Give a suggested default for each so they can simply agree.

Write a briefing note from findings

Use when the analysis is done and verified, and a busy reader needs the answer.

Turn the findings below into a briefing note for {audience}, who will read it in two minutes and needs to decide {decision}.

<findings>
{verified_findings_with_numbers}
</findings>

<caveats>
{data_limitations}
</caveats>

Format: a one-sentence headline, three to five bullet points with the numbers that support it, a short "what this doesn't tell us" paragraph, and one recommended next step.

Use only the numbers in <findings>. Don't calculate new ones or round them differently. If a sentence needs a number I haven't given you, write [NUMBER NEEDED] in its place.
Write in UK English, in plain language.

Reviewing AI output

Review SQL before it runs

Use on any AI-written query before it touches production data. Run it in a fresh conversation, not the one that wrote the query, so the reviewer doesn’t inherit the writer’s assumptions.

You are reviewing a {sql_dialect} query written by an AI assistant before it runs against production data. Don't assume anything is correct until you've checked it, and don't stop at the first problem.

<intent>
{what_the_query_should_return_and_its_grain}
</intent>

<schema>
{table_ddl_with_keys}
</schema>

<query>
{query}
</query>

Report PASS, FAIL, or UNSURE for each item, with a one-line reason that quotes the relevant line:
1. Join fan-out: can any join multiply rows? Is the key on the "one" side unique?
2. NULLs: NOT IN over a nullable column, = or <> comparisons that silently drop NULLs, COUNT(column) where COUNT(*) was meant.
3. Conditions on the right-hand table of a LEFT JOIN placed in WHERE, which turns it into an inner join.
4. Date boundaries: BETWEEN on datetime columns, inclusive end dates, financial year or week definitions.
5. Time zones: the zone the columns are stored in versus the zone the report uses.
6. Integer division.
7. DISTINCT or GROUP BY used to hide duplicates rather than fix their cause.
8. Whether the output grain matches the intent.

Then give a corrected query, and a minimal fixture (a few INSERT statements per table) with the expected output, designed so each FAIL would show up as a wrong number.

Review DAX before it ships

Use on any AI-written measure before it goes into a shared semantic model.

You are reviewing a DAX measure written by an AI assistant before it goes into a shared semantic model. Check it against this model, not against what models usually look like.

<intent>
{business_definition_and_visual_usage}
</intent>

<model>
{tables_columns_and_relationships_with_active_flags_and_directions}
</model>

<measure>
{measure_code}
</measure>

Report PASS, FAIL, or UNSURE for each item, with a one-line reason:
1. Filter context: which filters from the visual and slicers reach the measure, and does each CALCULATE filter replace them or intersect with them (KEEPFILTERS) as the intent requires?
2. ALL, REMOVEFILTERS, ALLEXCEPT, ALLSELECTED: does each remove filters from exactly the intended tables or columns? Remember that ALL on a fact table also clears filters coming from its dimension tables.
3. Blanks: comparisons with = that treat BLANK as 0, and any + 0 or COALESCE that forces a value into every row of a visual.
4. Division: DIVIDE wherever the denominator can be zero or BLANK.
5. Relationships: does every relationship it relies on exist, with the right active flag and filter direction, between columns of matching type (no time part on a date key)?
6. Performance: FILTER over a whole table where a column predicate would do, iterators that trigger context transition over a large table, the same measure evaluated repeatedly without a variable.
7. Totals: what it returns at the grand total, and whether a report user would expect that.

Then give a corrected measure if one is needed, and a DAX query that checks it against {known_case_and_value}.

Workflows

The prompts are most useful chained together, with a human check between each step.

New dataset to first dashboard

  1. Profile the table locally with the function above. Run First look at a new table with the schema and profile.
  2. Take the model’s questions to the data owner. Correct the data dictionary with their answers, not the model’s guesses.
  3. Run Draft data quality tests, schedule the tests, and fix or document every failure.
  4. Agree each KPI definition with its owner, then run Document a metric so the definition exists before the code.
  5. Run Write a query from a definition for each source view. Review it with Review SQL before it runs and test it on a fixture with known answers.
  6. Run Write a measure from a definition for each measure, review it with Review DAX before it ships, and check the known cases with the DAX query it gives you.
  7. Launch with Write a briefing note from findings, using only numbers you’ve reproduced.

Rewriting a slow DAX measure

  1. Measure first. Use Performance Analyzer in Power BI Desktop to find the slow visual, copy its query, and record server timings in DAX Studio.
  2. Run Explain what a measure does in a visual, so you know what the current measure returns at totals and for blanks before you change it.
  3. Run Rewrite a slow measure with the timings.
  4. Add the candidate as a query-scoped measure (DEFINE MEASURE) and run the disagreement query across the slicers people actually use. It must return no rows.
  5. Time it again. Keep the rewrite only if it’s faster and identical, and record both timings in the commit message.

Reviewing an AI-written SQL query before it runs

  1. Write the intent and the output grain in one sentence. If you can’t, the query isn’t ready to review.
  2. Build a small fixture with the right answers worked out by hand, including the awkward rows: NULL statuses, a record on the last day of the month, a key that appears twice.
  3. Run Review SQL before it runs in a fresh conversation.
  4. Read every line yourself, then run the query on the fixture and compare it with your hand-worked answers.
  5. Run it on a development copy, check row counts after each CTE, and reconcile the totals with a trusted source.
  6. Only then run it against production.

From a vague request to a briefing note

  1. Run Clarify an ambiguous request and send the reply before writing any code.
  2. Write and review the query with the SQL prompts above.
  3. If the answer involves a change over time, run Sanity-check a change in a rate and recompute the statistics in code.
  4. Run Write a briefing note from findings and check every number in the draft against your query output.

Agent harnesses and further reading

The prompts above work in a chat window. For work inside a repository, an agent harness such as Claude Code can read your files, run queries, and execute tests, which changes what you should delegate and how you review it.

Guides

All AI workflow articles