Field Mapping Prompt for ETL Transformations

This prompt is for ETL developers and data engineers who need to generate a row-by-row field mapping table between a known source extraction schema and a fixed target system schema. The job-to-be-done is automating the documentation of transformation rules before writing pipeline code. You provide two schemas—one from an upstream extraction step and one from a downstream database, API, or event contract—and the prompt produces a mapping that includes source field, target field, transformation logic, default values, and type compatibility warnings. This is a design-time prompt, not a runtime transformation engine. It helps you reason about the mapping, catch mismatches early, and produce a spec that a developer or code-generation tool can implement.

Use this prompt when you already have a defined source schema from an extraction step and a fixed target schema that cannot change. The prompt assumes you are mapping known fields to known targets. It does not design schemas, decide which fields to extract, or infer missing target columns. It also does not write the actual transformation code—it produces a mapping table that you can review, adjust, and then implement. If you need to generate extraction logic from raw documents, use a strict schema extraction prompt first. If you need to validate that extraction output matches a registered schema, use a data contract validation prompt. If you need to handle cross-field dependencies or assemble multiple records, use a multi-table record assembly prompt.

Before using this prompt, gather both schemas in a structured format—JSON Schema, SQL DDL, Avro definitions, or a simple field-name-and-type list. The prompt works best when you include type information, nullability constraints, and any target-side default values or required flags. After generating the mapping, review every row for correctness, especially transformation logic that involves type coercion, date formatting, or identifier canonicalization. For high-risk pipelines where incorrect mappings could corrupt downstream data, always have a second engineer review the mapping table before implementation. Do not use this prompt for real-time transformation decisions; it is a planning and documentation tool, not a runtime component.

The prompt below is designed to be dropped directly into your application or evaluation harness. It expects two schemas—source and target—and produces a structured JSON mapping table with type compatibility warnings, transformation rules, and blocking issue flags. Use it when you need a first-pass field map before writing actual transformation code.

textCopy
You are an ETL field mapping specialist. Your task is to generate a field mapping table from a source extraction schema to a target system schema.

## Source Schema
[SOURCE_SCHEMA]

## Target Schema
[TARGET_SCHEMA]

## Mapping Rules
- For each target field, identify the best matching source field by name similarity, semantic meaning, and data type compatibility.
- If no source field maps to a target field, specify a default value or mark it as unmapped.
- If a source field has no corresponding target field, note it as unmapped in the target.
- Flag any type incompatibilities with a warning level: 'safe' (implicit conversion possible), 'warning' (explicit conversion needed, possible data loss), or 'error' (incompatible types, manual transformation required).
- For each mapping row, provide a transformation rule in plain language. If the mapping is direct, state 'direct pass-through'.
- If a target field is required but unmapped, flag it as a blocking issue.

## Output Format
Return a JSON object with a 'mappings' array. Each element must have these fields:
- source_field: string or null
- target_field: string
- source_type: string or null
- target_type: string
- type_compatibility: 'safe' | 'warning' | 'error'
- transformation_rule: string
- default_value: string or null
- required_target: boolean
- notes: string

Also include:
- unmapped_source_fields: array of source fields with no target match
- unmapped_target_fields: array of target fields with no source match
- blocking_issues: array of required target fields with no source mapping

## Constraints
[CONSTRAINTS]

To adapt this template, replace [SOURCE_SCHEMA] and [TARGET_SCHEMA] with your actual schema definitions—preferably as JSON Schema, typed field lists, or table DDL. The [CONSTRAINTS] placeholder accepts additional rules such as naming conventions, forbidden transformations, or domain-specific type coercion policies. For high-stakes pipelines, always run the output through a validator that checks for missing required fields and type compatibility errors before feeding the mapping into your transformation engine.

After generating the mapping, review any rows flagged as warning or error manually. Blocking issues in the blocking_issues array must be resolved before the mapping can be used in production. Consider versioning your mapping outputs alongside your schema definitions so you can track how field maps evolve as upstream or downstream systems change.

This prompt is designed to be called programmatically within a data pipeline or schema mapping service. The LLM receives the prompt template populated with parsed source and target schemas, and it returns a JSON response containing a mappings array and a blocking_issues array. The application layer is responsible for validating this response before accepting it as a valid mapping configuration. Treat the LLM output as a proposal, not a final artifact, and always run structural and semantic checks before the mapping rules are applied to real data.

After calling the LLM, parse the JSON response and immediately validate that every field present in the target schema appears in the mappings array. If any target field is missing, reject the output and retry once with a stricter format instruction that explicitly lists the missing fields. Check the blocking_issues array before proceeding: if it is non-empty, route the entire mapping proposal to a human for schema review rather than attempting automated recovery. For type incompatibility warnings in the warnings array, generate a follow-up prompt asking the model to produce specific coercion logic for each flagged field, such as string-to-timestamp parsing patterns or decimal precision rules. Log every mapping run with the source schema version, target schema version, model identifier, and timestamp for auditability. If the output fails JSON parsing entirely, retry once with a stricter format instruction appended to the original prompt. If the second attempt also fails, escalate to a human operator with the raw model output attached and halt the pipeline.

Choose a model that reliably produces structured JSON output and respects schema constraints. Models with native function-calling or structured output modes are preferred because they reduce parsing failures and hallucinated fields. If your pipeline processes schemas that contain sensitive field names or business logic, ensure the model endpoint is deployed in a private environment and that no schema data is logged to external observability tools without redaction. For high-volume ETL workloads, consider caching mapping results keyed by a hash of the source and target schema versions to avoid redundant LLM calls. Before deploying any mapping configuration to production, run it against a representative sample of source records and validate that the transformed output matches the target schema's type constraints, nullability rules, and referential integrity requirements. A mapping that passes structural validation can still produce semantically broken data if coercion logic is lossy or field semantics are misinterpreted.