Boolean Value Normalization Prompt

This prompt is designed for ETL engineers and data pipeline developers who need to convert heterogeneous boolean representations into a single, strict target format before ingestion. The job-to-be-done is mapping a wide variety of human-readable or legacy-system truthy/falsy values—such as 'yes', 'Y', 'true', '1', 'active', 'enabled', and their negative counterparts—into a predictable output like a strict boolean (true/false) or a numeric flag (1/0). The ideal user is building a data normalization layer and needs a repeatable, auditable step that eliminates downstream ambiguity in WHERE clauses, analytics filters, and feature flags.

Use this prompt when your source data contains boolean-like fields with inconsistent formatting and you need a mapping that is more exhaustive and configurable than a simple SQL CASE statement. It is particularly valuable when you must distinguish between an explicit false value, an unrecognized string that should be flagged for manual review, and a genuinely missing or NULL field. Do not use this prompt for simple true/false strings that a standard library can parse without ambiguity. It is also the wrong tool for extracting boolean meaning from complex natural language sentences (e.g., 'the system was not unavailable'), which requires a different classification approach.

Before wiring this prompt into a production pipeline, define your target schema contract: decide whether the output is a strict boolean type or a numeric 1/0 flag, and establish a policy for handling unrecognized values. The prompt is most effective when paired with a validation layer that checks the output against an allowed enum and a human review queue for flagged anomalies. Avoid using this prompt as a standalone data quality fix without logging the original value and the normalization decision for auditability.

The following prompt template is designed to be copied directly into your prompt management system, API call, or evaluation harness. It accepts a raw input value and a set of configuration rules, then returns a structured JSON object with the normalized boolean result, a confidence score, and a flag for unrecognized inputs. Every placeholder is enclosed in square brackets and must be replaced with your specific data, rules, or output schema before use.

textCopy
You are a strict data normalization engine. Your only job is to convert an input string into a normalized boolean representation based on a provided mapping configuration.

INPUT VALUE:
[RAW_INPUT_VALUE]

TRUE MAPPINGS (comma-separated, case-insensitive):
[TRUE_VALUES]

FALSE MAPPINGS (comma-separated, case-insensitive):
[FALSE_VALUES]

NULL REPRESENTATIONS (comma-separated, case-insensitive):
[NULL_VALUES]

EMPTY STRING BEHAVIOR:
[EMPTY_STRING_RULE]

OUTPUT FORMAT:
You must return a single, valid JSON object with no additional text, using this exact schema:
{
  "original_value": "string",
  "normalized_value": boolean | null,
  "normalized_integer": integer | null,
  "confidence": "HIGH" | "MEDIUM" | "LOW",
  "match_type": "EXACT" | "NULL_DETECTED" | "EMPTY_DETECTED" | "UNRECOGNIZED",
  "unrecognized_flag": boolean,
  "rationale": "string"
}

RULES:
1. Trim whitespace from the input before processing.
2. Perform a case-insensitive comparison against the TRUE, FALSE, and NULL lists.
3. If the input matches a TRUE mapping, set `normalized_value` to true and `normalized_integer` to 1.
4. If the input matches a FALSE mapping, set `normalized_value` to false and `normalized_integer` to 0.
5. If the input matches a NULL mapping, set both `normalized_value` and `normalized_integer` to null, set `match_type` to "NULL_DETECTED", and `confidence` to "HIGH".
6. If the input is an empty string after trimming, apply the [EMPTY_STRING_RULE]. If the rule is "TREAT_AS_NULL", handle as a null. If the rule is "TREAT_AS_FALSE", handle as false. If the rule is "FLAG_AS_EMPTY", set `match_type` to "EMPTY_DETECTED" and both normalized fields to null.
7. If the input does not match any list, set `normalized_value` and `normalized_integer` to null, set `match_type` to "UNRECOGNIZED", `confidence` to "LOW", and `unrecognized_flag` to true. In the rationale, state that the value requires manual mapping.
8. For exact matches, set `confidence` to "HIGH". For fuzzy matches or near-matches (if implemented), set to "MEDIUM".

To adapt this template for your pipeline, replace the [TRUE_VALUES] and [FALSE_VALUES] placeholders with your organization's canonical representations, such as yes,Y,true,1,active,enabled and no,N,false,0,inactive,disabled. The [EMPTY_STRING_RULE] is a critical configuration point; align it with your downstream database contract—treating an empty string as NULL prevents constraint violations in NOT NULL columns, while treating it as false may be appropriate for feature flags. After adapting, run this prompt against a golden dataset of known inputs and verify that the confidence and match_type fields behave as expected for edge cases like ' True ' (with whitespace) and completely unrecognized strings like 'maybe'.

This prompt is designed to be a deterministic normalization step within a broader data ingestion pipeline. It should be called as a stateless function: receive a raw string, return a structured JSON object. The primary integration point is immediately after field extraction and before the record is inserted into the target data warehouse or operational database. Because the prompt handles the distinction between null (absent) and empty string (known blank), your application code must pass the raw source value without pre-processing it into a default string. Wire the prompt into your ETL tool (e.g., Apache Airflow, Prefect, Dagster) as a task that maps over a batch of records, calling the LLM for each field that requires boolean normalization.

Validation and Retry Logic: The prompt's output schema is strict, but you must still validate the response before allowing it to proceed. Your harness should check that the normalized_value is strictly true, false, or null (for unrecognized inputs). If the status field is unrecognized, the record must be routed to a dead-letter queue or a manual review interface, not silently coerced to false. Implement a retry policy for transient model failures (e.g., malformed JSON, timeouts) with exponential backoff, but do not retry unrecognized statuses—those require a human to update the mapping table. Log every normalization decision, including the original_value, normalized_value, status, and mapped_from fields, to create an audit trail for data lineage and debugging.

Model Choice and Performance: For this task, prefer smaller, faster, and cheaper models. A capable small language model (e.g., Claude Haiku, GPT-4o-mini) is sufficient because the operation is a constrained classification, not a generative reasoning task. Avoid using large frontier models unless you are also handling complex, context-dependent boolean expressions in the same step. To reduce latency and cost, batch multiple boolean fields from the same record into a single prompt call by providing a list of field-value pairs and asking for an array of results. Ensure your application code can map the array of results back to the correct source fields. Do not use Retrieval-Augmented Generation (RAG) or external tools for this prompt; the logic is self-contained and should rely only on the mapping rules and examples provided in the prompt context.

Human-in-the-Loop Integration: The unrecognized status is your contract for human escalation. Build a simple review UI or a spreadsheet-based workflow where an operator sees the unrecognized value and can select the correct boolean mapping. Once a human makes a decision, your system should do two things: first, update the target record with the resolved value; second, feed the new mapping (e.g., 'active' -> true) back into the prompt's [EXAMPLES] or a dynamic lookup table for future runs. This closes the feedback loop and prevents the same value from being flagged repeatedly. Never auto-commit an unrecognized value as false—this is a common failure mode that silently corrupts data and masks upstream source system changes.