Browse cheat sheets · AI Engineering for Data
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
- Problem
- Quick answer
- Base pattern
- Code / commands
- 1. SQL generation
- 2. SQL explanation
- 3. Schema interpretation
- 4. Column mapping
- 5. Data-quality rule generation
- 6. Log summarization
- 7. Incident analysis
- 8. Notebook and code explanation
- 9. Pipeline documentation
- 10. Metadata enrichment
- Reusable techniques
- Bad prompt vs better prompt
- Structured output pattern
- “Ask for assumptions” pattern
- “Return uncertainty” pattern
- Validation step
- What to look for
- Common mistakes
- 1. Sending secrets or raw customer data
- 2. Sending the whole schema
- 3. No output format
- 4. Running generated SQL directly
- 5. Mixing instructions and data
- 6. Asking for certainty
- Production notes
- Checklist
- Next step
- 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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.