Enum and Lookup Table Value Resolution Prompt Template

This prompt is designed for data engineers and backend developers who need to map free-text, user-provided, or machine-generated descriptions to a controlled vocabulary stored in a database lookup table. The core job-to-be-done is resolving a fuzzy input like 'the big apple' or 'NY' to a canonical foreign key reference, such as city_id: 3 for 'New York City'. It is a critical component in data ingestion pipelines, ETL workflows, and AI-assisted data entry forms where downstream analytics, joins, and application logic depend on strict referential integrity, not approximate string matches.

Use this prompt when you have a predefined set of canonical values (an enum or a lookup table) and you need the model to act as an intelligent mapper, not a classifier that invents new categories. It is ideal when the input is messy—containing typos, synonyms, abbreviations, or multilingual variants—and a simple LIKE or Levenshtein distance function fails. The prompt should be integrated into a pipeline where the model's output is a structured JSON object containing the resolved ID, the canonical value, and a confidence score. This allows the calling application to automatically process high-confidence matches and route low-confidence ones to a human review queue. Do not use this prompt for open-ended tagging, generating new taxonomy terms, or when the lookup table has millions of rows that exceed the model's context window; in those cases, a retrieval-augmented generation (RAG) approach that pre-filters candidates is required before invoking this resolution step.

The primary constraint is that the model must never hallucinate a foreign key that does not exist in the provided lookup table. The prompt template enforces this by requiring the full lookup context to be injected into the prompt at runtime. Before deploying, you must implement a post-generation validation step that confirms the returned resolved_id is present in your source-of-truth lookup table. A common failure mode is the model correctly identifying the canonical concept but returning an ID from its pre-training data instead of the one you provided. Your harness must catch this mismatch and either retry with a stricter prompt or escalate for manual mapping. The next section provides the exact template to implement this controlled resolution.

This template resolves fuzzy, inconsistent, or abbreviated free-text inputs into controlled vocabulary values suitable for foreign key references in a database. It is designed for data engineering pipelines where raw user input, third-party data, or legacy system values must be normalized before insertion. The prompt expects a target lookup table schema, a list of valid canonical values, and the raw input to resolve. It returns a structured object with the best match, a confidence score, and explicit handling for out-of-vocabulary cases.

textCopy
You are a data normalization assistant. Your task is to map a free-text input value to the most appropriate canonical value from a controlled vocabulary.

## INPUT
Free-text value: [RAW_INPUT]

## CONTEXT
Lookup table: [LOOKUP_TABLE_NAME]
Canonical values (valid enum members):
[VALID_VALUES_LIST]

Additional resolution rules:
[RULES]

## OUTPUT_SCHEMA
Return a JSON object with exactly these fields:
{
  "canonical_value": string | null,  // The matched canonical value, or null if no match
  "confidence": number,               // 0.0 to 1.0
  "match_type": "exact" | "synonym" | "abbreviation" | "fuzzy" | "no_match",
  "reasoning": string,                // Brief explanation of the match decision
  "fallback_action": "use_default" | "flag_for_review" | "reject" | null
}

## CONSTRAINTS
- Only return canonical values that appear exactly in the provided list.
- If the input is ambiguous, return the most likely match and set confidence below 0.8.
- If no reasonable match exists, set canonical_value to null, confidence to 0.0, and match_type to "no_match".
- Do not invent new canonical values.
- For abbreviations, expand to the full canonical form.
- Handle common misspellings and case differences.

## EXAMPLES
[EXAMPLES]

## RISK_LEVEL
[RISK_LEVEL]

To adapt this template, replace each square-bracket placeholder with concrete values from your pipeline. [RAW_INPUT] receives the free-text string to resolve. [VALID_VALUES_LIST] should be a complete, copy-pasted list of allowed enum members or lookup table display values. [RULES] can include domain-specific resolution logic, such as "map 'NYC' to 'New York'" or "prefer the most specific category." [EXAMPLES] should contain 2-4 few-shot demonstrations showing correct resolution, including at least one no-match case. Set [RISK_LEVEL] to "low", "medium", or "high" to control downstream behavior: high-risk workflows should route no-match and low-confidence results to a human review queue rather than silently inserting a default value.

The enum resolution prompt is designed to be called as a stateless function within a data ingestion or cleaning pipeline. Each call receives a single free-text value and a controlled vocabulary, returning a canonical match or a structured fallback. This makes it straightforward to integrate into ETL jobs, API request handlers, or batch processing scripts. The prompt should be treated as a deterministic mapping service with a confidence score, not as a conversational agent. Wrap the prompt call in a thin application function that accepts (input_text, enum_list, context) and returns a typed result object containing the resolved value, confidence, and any fallback flags.

Validation and retry logic is critical because downstream systems often reject out-of-vocabulary values. After receiving the model response, validate the JSON structure against a strict schema that requires resolved_value to be either null or present in the provided enum list. If the model returns a value not in the list, treat it as a low-confidence fallback rather than accepting it. Implement a single retry with a more explicit instruction when confidence is below your threshold (e.g., < 0.85). On the retry, include the original input, the rejected output, and a directive like: The previous response contained a value not in the allowed list. Return the closest match or set resolved_value to null. If the retry also fails, log the input, the enum list, and both responses for later review. For high-volume pipelines, consider caching resolved values in a lookup table to avoid repeated LLM calls for identical inputs.

Model choice and latency matter here. This task requires precise instruction following and low hallucination, not creative reasoning. Use a fast, instruction-tuned model such as gpt-4o-mini, claude-3-haiku, or a fine-tuned open-weight model if you have a large corpus of historical mappings. Avoid models with high latency or verbose reasoning tendencies unless you explicitly need chain-of-thought for ambiguous cases. Set temperature=0 to maximize reproducibility. For batch processing, parallelize calls with a concurrency limit appropriate for your API tier. If processing millions of rows, pre-filter exact string matches before calling the LLM to reduce cost and latency.

Observability and human review are essential for out-of-vocabulary inputs. Every time the model returns fallback_required: true or confidence below your threshold, write a structured log entry with the input, the enum list, the model's suggestion, and the pipeline stage. Route these logs to a review queue where a data steward can approve the mapping, add a new enum value, or mark the input as intentionally unmappable. Over time, this review queue becomes a training dataset for fine-tuning or for expanding your controlled vocabulary. Do not silently drop unmapped values—missing foreign keys in downstream analytics are harder to debug than explicit nulls with audit trails.

Integration pattern: In a typical Python pipeline, wrap the prompt call in a function like resolve_enum(input_text, valid_enums, context_hint) that constructs the prompt, calls the model, parses the JSON response, validates the result against the enum list, and returns a dataclass with fields resolved_value, confidence, fallback_used, and raw_response. Use this function as a .map() operation over a DataFrame column or as a step in an Airflow task. For real-time APIs, cache the function result with a TTL and expose a separate admin endpoint for the review queue. This keeps the LLM call isolated, testable, and replaceable without touching the rest of the pipeline.