Table Schema Inference Prompt for Unlabeled Data

Data engineers frequently encounter tables extracted from PDFs, legacy systems, or unstructured sources that arrive without headers, with ambiguous column labels, or with no documented schema. Before these tables can be loaded into a warehouse, joined with other datasets, or validated, someone must determine what each column represents, what data type it holds, and whether it can serve as a primary key. This prompt automates that inference step. It takes raw cell values from an unlabeled table plus optional surrounding document context and produces a proposed schema with column names, data types, primary key candidates, foreign key hypotheses, and confidence scores. The ideal user is a data engineer or pipeline operator who needs a first-pass schema proposal that a human can review and refine, not a guaranteed production schema generated without oversight.

Use this prompt when you have a table with unknown or missing column headers and need to bootstrap a schema for ingestion, when you're processing tables from multiple sources with inconsistent labeling, or when you need to validate an existing schema hypothesis against the actual data. The prompt works best when you can provide at least 10-20 sample rows so the model has enough signal to distinguish IDs from names, dates from timestamps, and measures from dimensions. Providing surrounding document context—such as the paragraph preceding the table, the document title, or a caption—significantly improves accuracy for domain-specific columns. Do not use this prompt when the table schema is already known and you only need type coercion (use a deterministic validator instead), when the data contains PII that cannot be sent to an external model, or when the table has fewer than three rows (insufficient signal for reliable inference).

The prompt outputs a proposed schema with confidence scores per column, not a final production schema. Every output should be treated as a draft that requires human review, especially for columns with confidence below 0.85. For high-stakes pipelines—financial reporting, clinical data, regulatory filings—pair this prompt with a human-in-the-loop review step and a validation harness that checks the proposed schema against known business rules (e.g., 'every table must have a date column' or 'IDs must be unique'). The next section provides the copy-ready prompt template you can adapt for your own pipeline.

This prompt template is the core instruction set you will send to the model. It is designed to be copied directly into your prompt management system, IDE, or orchestration layer. The template uses square-bracket placeholders for all dynamic inputs. Before sending this to the model, you must replace every placeholder with real data from your ingestion pipeline. The prompt instructs the model to analyze cell patterns, surrounding document context, and value distributions to propose a structured schema with confidence scores.

textCopy
You are a data engineer analyzing an unlabeled table extracted from a document. The table has no headers or has ambiguous column meanings. Your task is to infer a complete schema.

**Input Table Data:**
[INPUT_DATA]

**Surrounding Document Context (if available):**
[DOCUMENT_CONTEXT]

**Domain Hints (e.g., finance, healthcare, logistics):**
[DOMAIN_HINTS]

**Output Requirements:**
Produce a single JSON object conforming to this exact schema:
{
  "proposed_table_name": "string",
  "columns": [
    {
      "column_index": "integer",
      "inferred_name": "string",
      "inferred_data_type": "string (e.g., VARCHAR, INTEGER, DECIMAL, DATE, BOOLEAN)",
      "sample_values": ["string", "string"],
      "null_ratio": "float (0.0 to 1.0)",
      "is_primary_key_candidate": "boolean",
      "is_foreign_key_candidate": "boolean",
      "potential_referenced_table": "string | null",
      "confidence_score": "float (0.0 to 1.0)",
      "reasoning": "string (brief explanation of the inference)"
    }
  ],
  "proposed_primary_key": ["string (column name)"],
  "proposed_foreign_keys": [
    {
      "column": "string",
      "likely_references": "string (table.column)",
      "confidence": "float"
    }
  ],
  "overall_confidence": "float (0.0 to 1.0)",
  "notes": ["string (any ambiguity, missing context, or assumptions made)"]
}

**Constraints:**
[CONSTRAINTS]

**Few-Shot Examples:**
[EXAMPLES]

**Risk Level for this extraction:** [RISK_LEVEL]

To adapt this template, start by populating [INPUT_DATA] with a serialized representation of your table, such as a JSON array of arrays or a CSV string. The [DOCUMENT_CONTEXT] field should contain the text immediately before and after the table in the source document; this is critical for disambiguating columns like 'Date' that could mean 'Transaction Date' or 'Maturity Date'. Use [DOMAIN_HINTS] to pass any known business context from upstream classification. The [CONSTRAINTS] field is where you enforce business rules, such as 'All date columns must be ISO 8601' or 'Currency columns must be identified with their ISO 4217 code'. If you have a library of validated schema examples, populate [EXAMPLES] with one or two input-output pairs to ground the model's behavior. Finally, set [RISK_LEVEL] to low, medium, or high to control the verbosity of the model's reasoning and the strictness of its self-critique. After receiving the output, always validate the JSON structure and check that column_index values are sequential and match the input column count before ingesting the schema into your catalog.

The schema inference prompt is not a standalone utility; it is a component inside a larger ingestion pipeline. You send raw, unlabeled tabular data to the model and receive a proposed schema. The harness around this prompt is responsible for validating that schema, deciding whether to accept, retry, or escalate, and then applying the schema to downstream normalization and loading steps. Treat the model's output as a proposal, not a committed schema. The harness must enforce structural checks before any data is written to a target system.

Input assembly starts with extracting the raw table from the source document. If the table comes from a PDF, run layout-aware extraction first to produce a grid representation. Pass that grid as [TABLE_GRID] along with any surrounding document context as [DOCUMENT_CONTEXT]. The context should include the page number, section heading, and a few paragraphs of text before and after the table. This context is critical for disambiguating column meanings—a column labeled 'Q1' means something different in a financial statement than in a project plan. Model choice matters: use a model with strong structured output support (GPT-4o, Claude 3.5 Sonnet, or equivalent) and enable JSON mode with the output schema defined in [OUTPUT_SCHEMA]. For high-throughput pipelines, consider batching multiple small tables into a single request to reduce per-call overhead, but keep total token usage under 4K output tokens to maintain schema fidelity.

Validation is the core of the harness. After receiving the proposed schema, run these checks before accepting it: (1) Every column in the input grid must appear in the schema with a non-null inferred_name. (2) Every data_type must be one of your allowed type enum values. (3) Primary key candidates must reference columns that actually exist in the schema. (4) Foreign key candidates must reference valid column pairs and include a confidence score. (5) If confidence for any column falls below your threshold (start with 0.7), route that column to human review. Retry logic: if validation fails on structural grounds (missing columns, invalid types, malformed JSON), retry once with the validation errors appended to [CONSTRAINTS]. If the retry also fails, log the failure and escalate. Do not retry more than twice—diminishing returns set in fast. Human review integration: when confidence is low or validation fails after retries, generate a review payload containing the original table snippet, the model's proposed schema, the specific validation failures, and a simple accept/reject/edit interface. Store all review decisions as ground truth for future fine-tuning or few-shot example selection.

Logging and observability are non-negotiable. Log every inference request with: the prompt version, model identifier, input token count, output token count, latency, validation pass/fail status, confidence distribution across columns, and whether human review was triggered. These logs become your regression test dataset. When you change the prompt or upgrade the model, replay a sample of logged requests and compare schema outputs for drift. Downstream integration: once a schema is accepted, pass it to the normalization step that casts values to the inferred types and maps column names to your canonical data model. If the schema includes foreign key candidates, use them to suggest join paths but do not automatically enforce referential integrity—let a data engineer confirm. The schema output should be versioned alongside the extracted data so that any downstream errors can be traced back to the inference decision that produced them.