Lookup Table Reference Prompt for Value Mapping

This prompt is for ETL developers and data engineers who need to map a source value to a canonical target value using a provided reference lookup table. The core job is to accept a raw input string and a structured mapping context, then return the correct target value, a confidence indicator, and actionable diagnostics when the mapping is missing, ambiguous, or stale. This is not a prompt for building the lookup table itself, nor is it for fuzzy entity resolution against a knowledge base—it assumes you already have a defined mapping table and need the model to apply it consistently.

Use this prompt when your pipeline encounters values that must be translated before they can enter a downstream system with a strict contract—for example, mapping legacy department codes to a new chart of accounts, normalizing free-text country names to ISO codes, or converting internal product names to SKU identifiers. The prompt is designed to be wired into a data pipeline as a transformation step, where the lookup table is injected as context at inference time. It is most effective when the mapping table is small enough to fit in the prompt context window and when the mapping rules are deterministic, not based on complex semantic similarity. For large-scale entity resolution against millions of records, use a vector search or dedicated matching service instead, and reserve this prompt for the final disambiguation or human-review step.

Do not use this prompt when the mapping logic requires real-time access to an external system of record, when the lookup table changes faster than you can update the prompt context, or when the cost of a wrong mapping is high and you cannot afford a human review loop. In regulated environments—such as financial transaction coding or clinical data mapping—always pair this prompt with a confidence threshold that routes low-confidence results to a human review queue. The prompt includes instructions to flag missing mappings and suggest candidates, but it should never be the sole authority for high-stakes transformations without an audit trail and approval step. Before you copy the template, define your target schema, your confidence thresholds, and your escalation path for unmapped values.

This prompt template is designed to be wired directly into an ETL pipeline or data normalization service. It accepts a raw source value and a serialized lookup table as context, then returns a structured JSON object containing the mapped target value, a confidence score, and a clear status flag. The template uses square-bracket placeholders for all dynamic inputs, making it safe to parameterize in code before sending to the model. The primary goal is to offload deterministic string matching to a lookup table while using the model's reasoning only for fuzzy matching, ambiguity resolution, and suggesting new candidate mappings when no exact match exists.

textCopy
You are a precise data mapping engine. Your task is to map a source value to a target value using the provided lookup table. Do not invent mappings. If a mapping is missing, ambiguous, or appears stale, you must indicate this clearly.

## INPUT
Source Value: [SOURCE_VALUE]

## LOOKUP TABLE CONTEXT
[LOOKUP_TABLE]

## OUTPUT SCHEMA
You must respond with a single JSON object conforming to this schema:
{
  "status": "matched" | "missing" | "ambiguous" | "stale_candidate",
  "source_value": "string (the original input)",
  "mapped_value": "string | null (the resolved target value)",
  "confidence": 0.0-1.0,
  "candidate_mappings": [
    {
      "target_value": "string",
      "match_type": "exact" | "fuzzy" | "abbreviation" | "pattern",
      "rationale": "string"
    }
  ],
  "rationale": "string (brief explanation of the mapping decision)"
}

## CONSTRAINTS
- If an exact match is found in the lookup table, set status to "matched" and confidence to 1.0.
- If multiple potential matches exist, set status to "ambiguous", confidence to 0.5 or lower, and list all candidates with match types.
- If no match is found, set status to "missing", confidence to 0.0, and suggest up to three candidate mappings based on pattern similarity to existing keys.
- If a match is found but the lookup table entry is flagged as deprecated or has an expiration date that has passed, set status to "stale_candidate".
- Never hallucinate a target value that does not derive from the lookup table or a reasonable pattern match against its keys.
- Preserve the original source value in the output for auditability.

## EXAMPLES
[EXAMPLES]

## RISK LEVEL
[RISK_LEVEL]

To adapt this template, replace each placeholder with production data. [SOURCE_VALUE] is the raw string from your source system. [LOOKUP_TABLE] should be a serialized representation of your reference data, such as a JSON object or a markdown table of key-value pairs, including any metadata like deprecation flags or expiration dates. The [EXAMPLES] placeholder should be populated with 2-3 few-shot demonstrations that show the model how to handle exact matches, missing values, and ambiguous cases specific to your domain. The [RISK_LEVEL] placeholder accepts a value like low, medium, or high to adjust the model's caution; for high-risk domains such as finance or healthcare, the system should be instructed to default to status: "missing" and route to a human review queue when confidence is below 0.9. After copying this template, validate that the output JSON parses correctly and that the status field drives the correct branching logic in your application code.

The lookup table reference prompt is not a standalone script; it is a transformation step inside a larger data pipeline. Wire it as a stateless function that receives a source value and a serialized lookup table context, then returns a structured mapping result. The application layer is responsible for fetching the correct lookup table slice, formatting it into the prompt's [LOOKUP_TABLE] placeholder, and parsing the model's JSON output into the next stage of the pipeline. Do not rely on the model to remember mappings across invocations—each call should be self-contained with its own lookup context to prevent cross-record leakage and hallucinated mappings.

Build a thin wrapper that handles three concerns before and after the model call. First, input validation: confirm the source value is a non-empty string and the lookup table is a valid JSON array of objects with at least source_value and target_value keys. Reject malformed inputs before they reach the model. Second, output validation: parse the model's JSON response and verify it contains the required mapped_value, mapping_status (one of exact_match, fuzzy_match, no_match, stale_mapping), and confidence fields. If the output fails schema validation, retry once with a repair prompt that includes the validation error. Third, routing: if mapping_status is no_match or confidence is below your threshold (e.g., <0.85), route the record to a human review queue with the original value, the lookup table context, and any candidate_mappings the model suggested. For stale_mapping results, flag the lookup table itself for maintenance rather than the individual record.

Choose a model that performs reliably on structured JSON extraction with low latency, since this prompt typically runs on high-volume record streams. GPT-4o or Claude 3.5 Sonnet work well for complex fuzzy matching; for simple exact-match lookups where the model adds no value, skip the prompt entirely and use a deterministic hash map in application code. Log every mapping decision—input value, lookup table hash, model version, mapped output, confidence, and status—so you can audit mapping quality over time and detect when a lookup table update causes regression in downstream systems. Never let an unmapped value silently pass through; always either map it, queue it for review, or write an explicit unmapped sentinel to the target field.