Browse cheat sheets · AI Engineering for Data

Technology: AI Engineering for DataCategory: Prompt EngineeringDifficulty: IntermediateUpdated

Prompt Patterns for Data Engineering

Reusable prompt patterns for data engineering tasks such as SQL generation, schema interpretation, column mapping, data-quality rules and incident analysis, with structured output, explicit assumptions and validation.

On this page
  1. Problem
  2. Quick answer
  3. Base pattern
  4. Code / commands
  5. 1. SQL generation
  6. 2. SQL explanation
  7. 3. Schema interpretation
  8. 4. Column mapping
  9. 5. Data-quality rule generation
  10. 6. Log summarization
  11. 7. Incident analysis
  12. 8. Notebook and code explanation
  13. 9. Pipeline documentation
  14. 10. Metadata enrichment
  15. Reusable techniques
  16. Bad prompt vs better prompt
  17. Structured output pattern
  18. “Ask for assumptions” pattern
  19. “Return uncertainty” pattern
  20. Validation step
  21. What to look for
  22. Common mistakes
  23. 1. Sending secrets or raw customer data
  24. 2. Sending the whole schema
  25. 3. No output format
  26. 4. Running generated SQL directly
  27. 5. Mixing instructions and data
  28. 6. Asking for certainty
  29. Production notes
  30. Checklist
  31. Next step
  32. Related deep dives

Problem

LLMs can help with everyday data work:

  • writing and explaining SQL
  • interpreting unfamiliar schemas
  • mapping source columns to a target model
  • drafting data-quality rules
  • summarizing logs and incidents
  • documenting notebooks and pipelines
  • enriching metadata

A vague prompt gives a plausible answer that is hard to check. Data tasks need prompts that state the context, the constraints and the expected output, and a validation step that does not trust the model.

Quick answer

Use one structure for every data prompt:

Objective:   what to produce, for whom
Context:     only the schema, sample, logs or rules needed
Constraints: dialect, naming, limits, what not to do
Input:       the data or question, clearly delimited
Output:      exact format (JSON schema, SQL only, table)
Validation:  what makes the answer acceptable; list assumptions; say "unknown" when unsure

Then validate the output with code, the database or a reviewer before using it.

Never put secrets, credentials or raw sensitive customer data in a prompt.

Base pattern

## Objective
Write a T-SQL query that answers the question below.

## Context
Tables (only these exist):
orders(order_id int pk, customer_id int, order_date date, status varchar, amount decimal(12,2))
customers(customer_id int pk, country char(2), segment varchar)

## Constraints
- SQL Server 2022, read-only SELECT
- use only the tables and columns listed
- return at most 1000 rows

## Input
<question>Revenue by country for shipped orders in September 2026.</question>

## Output
Return JSON: {"sql": string, "assumptions": [string], "confidence": "high" | "medium" | "low"}

## Validation
If a needed column is missing, return "sql": null and explain in "assumptions".

Delimit inputs (<question>, <logs>, <schema>) so data is not confused with instructions.

Code / commands

1. SQL generation

Bad:

Write a query for revenue by country.

Better: use the base pattern above. It gives the dialect, the exact tables, the filters to interpret and a format to validate.

Validate before running: compile without executing, under a read-only login.

SET NOEXEC ON;   -- SQL Server: compile and check names, do not execute

SELECT TOP (1000) c.country, SUM(o.amount) AS revenue
FROM dbo.orders AS o
JOIN dbo.customers AS c ON c.customer_id = o.customer_id
WHERE o.status = 'shipped'
  AND o.order_date >= '2026-09-01' AND o.order_date < '2026-10-01'
GROUP BY c.country;

SET NOEXEC OFF;

Other engines have equivalents (for example EXPLAIN). Then compare results with a known number before trusting the query.


2. SQL explanation

Explain what this query does for a data analyst.

<sql>
...query...
</sql>

Output (Markdown):
1. Purpose in one sentence.
2. Step-by-step logic (joins, filters, grouping).
3. Grain of the result: what one row represents.
4. Risks: possible duplicates from joins, NULL handling, date boundaries.
Do not rewrite the query.

Asking for the grain and risks turns a paraphrase into a review.


3. Schema interpretation

Describe the likely business meaning of each table and the relationships between them.

<schema>
...DDL or column list with types, keys and a few masked sample values...
</schema>

Output JSON:
{"tables": [{"name": string, "meaning": string, "grain": string,
             "keys": [string], "evidence": string, "confidence": "high"|"medium"|"low"}]}

Mark anything inferred from names only as "confidence": "low".

4. Column mapping

Map source columns to the target model.

<source_columns>cust_no, e_mail, ctry, created_ts, mkt_optin</source_columns>
<target_columns>customer_id, email, country_code, signup_date, marketing_consent</target_columns>

Rules:
- map each source column to at most one target column
- if no target fits, use null
- do not invent target columns

Output JSON:
{"mappings": [{"source": string, "target": string | null,
               "transformation": string | null, "confidence": number}]}

Validate the mapping with code:

import json

ALLOWED_TARGETS = {"customer_id", "email", "country_code", "signup_date", "marketing_consent"}


def validate_mapping(raw: str, source_columns: set[str]) -> list[str]:
    """Return problems found; an empty list means the mapping can go to human review."""
    try:
        mapping = json.loads(raw)
    except json.JSONDecodeError as exc:
        return [f"not valid JSON: {exc}"]
    problems = []
    for item in mapping.get("mappings", []):
        source, target, confidence = item.get("source"), item.get("target"), item.get("confidence")
        if source not in source_columns:
            problems.append(f"unknown source column: {source}")
        if target is not None and target not in ALLOWED_TARGETS:
            problems.append(f"unknown target column: {target}")
        if not isinstance(confidence, (int, float)) or not 0 <= confidence <= 1:
            problems.append(f"invalid confidence for {source}")
    return problems

5. Data-quality rule generation

Propose data-quality checks for this table.

<table_profile>
column, type, null_pct, distinct_count, min, max, sample_values (masked)
...
</table_profile>

Output JSON:
{"rules": [{"column": string, "check": "not_null"|"unique"|"range"|"allowed_values"|"pattern",
            "parameters": object, "reason": string, "severity": "error"|"warning"}]}

Base every rule on the profile. Do not propose thresholds without evidence; mark them "to confirm".

Turn accepted rules into code (SQL, PySpark or your DQ framework) and review them like any other code.


6. Log summarization

Summarize these pipeline logs for an on-call engineer.

<logs>
...last 200 relevant lines, secrets and tokens removed...
</logs>

Output:
- First error (timestamp, component, message)
- Probable sequence of events (max 5 bullets)
- What is NOT shown in these logs
Quote log lines exactly; do not paraphrase error messages.

Filter logs with code first (level, time window, run ID). Do not send entire log files.


7. Incident analysis

Help analyze this incident. You are assisting, not deciding.

<timeline>...</timeline>
<metrics>...</metrics>
<recent_changes>...</recent_changes>

Output JSON:
{"hypotheses": [{"cause": string, "supporting_evidence": [string],
                 "contradicting_evidence": [string], "next_check": string}],
 "unknowns": [string]}

Rank hypotheses by evidence. Do not state a root cause as fact.

8. Notebook and code explanation

Explain this notebook cell for a data engineer new to the project.

<code language="python">
...
</code>

Output:
1. What it reads and writes (tables, files, paths).
2. Transformations in order.
3. Side effects (overwrites, deletes, merges).
4. Performance concerns (collect(), loops over rows, wide shuffles).

9. Pipeline documentation

Draft documentation for this pipeline from its definition.

<pipeline_definition>...JSON/YAML, secrets removed...</pipeline_definition>

Output Markdown with sections: Purpose, Schedule, Sources, Targets, Steps,
Failure handling, Owners (leave "TBD" if not in the input).
Only describe what is in the definition.

10. Metadata enrichment

Suggest business descriptions and tags for these columns.

<columns>name, type, table, sample values (masked)</columns>

Output JSON:
{"columns": [{"name": string, "description": string,
              "tags": [string], "pii_suspected": boolean, "confidence": "high"|"medium"|"low"}]}

Use only these tags: finance, customer, product, operational, reference.

Store suggestions as drafts for a data steward to approve.

Reusable techniques

Bad prompt vs better prompt

Bad Better
“Fix this SQL” “This T-SQL returns duplicates per order. Explain why and return a corrected query. Keep the output columns unchanged.”
“Summarize the logs” “List the first error, the failing component and the last successful step. Quote lines exactly.”
“Map these columns” “Map each source column to one target column or null. Return JSON matching this schema.”

Structured output pattern

  • Define the schema in the prompt, or use the provider’s structured output feature.
  • Use enums for categories.
  • Parse and validate with code; retry or escalate on failure.

“Ask for assumptions” pattern

Before the answer, list the assumptions you made about the data
(grain, time zone, meaning of status values, NULL handling).

Assumptions are where data answers go wrong; making them explicit makes review fast.

“Return uncertainty” pattern

If the input does not contain enough information, return "unknown"
and list what is missing. Do not guess.

Give the model a legitimate way to say it does not know.

Validation step

Output Validate with
SQL Compile without executing, read-only login, row limit, compare to a known total
JSON Parse + schema validation + allowed values
Mappings / rules Code checks + human approval
Summaries Quoted lines exist in the source
Code Tests, linting, review

What to look for

  • Answers that use tables or columns not in the context.
  • Missing assumptions about grain, time zones or NULLs.
  • Confident answers to questions the input cannot answer.
  • Outputs that pass the schema but fail business checks.
  • Sensitive values appearing in prompts or logs.

Common mistakes

1. Sending secrets or raw customer data

Connection strings, keys, tokens and personal data do not belong in prompts. Mask, sample, synthesize or aggregate first.

2. Sending the whole schema

Large irrelevant context costs tokens and invites wrong joins. Send the relevant tables.

3. No output format

Free text is hard to validate and to use in a pipeline.

4. Running generated SQL directly

Compile it, run it read-only with limits, and check the result.

5. Mixing instructions and data

Data pasted without delimiters can be misread as instructions, including instructions hidden in the data itself.

6. Asking for certainty

“What is the root cause?” invites a confident guess. Ask for hypotheses with evidence.

Production notes

  • Keep prompts in version control as templates; log the template version with each call.
  • Build prompts with code from templates, not by string concatenation of user input into instructions.
  • Treat retrieved documents, logs and user input as untrusted data.
  • Use a read-only database role for any generated query.
  • Mask or remove personal data and secrets before prompts are built; check what your logging captures.
  • Keep a small evaluation set per prompt and re-run it after edits or model changes.

Checklist

  • Objective, context, constraints, input, output and validation are explicit.
  • Only the needed schema, sample or logs are sent.
  • Inputs are delimited.
  • Output has a defined format.
  • The model is asked for assumptions and allowed to answer “unknown”.
  • Output is validated by code, the database or a reviewer.
  • No secrets or raw sensitive data in prompts.

Next step

  • Prompts are too long or expensive → LLM Token Optimization.
  • Unsure what context to include → LLM Context Engineering.
  • Results are weak even with a good prompt → Choosing the Right AI Model.

← All cheat sheets