Schema Documentation Generation Prompt

This prompt is designed for data platform engineers and database administrators who need to transform raw Data Definition Language (DDL) statements and system catalog metadata into a comprehensive, human-readable data dictionary. The core job-to-be-done is eliminating the manual, error-prone process of documenting hundreds of tables and columns by producing a structured artifact that captures table purposes, column descriptions, data types, constraints, relationships, and usage notes. The ideal user has access to a database's information schema or a DDL export and needs documentation that can be reviewed by data consumers, analysts, and downstream engineers before being published to an internal data portal or wiki.

Use this prompt when you have a stable or near-stable schema that is ready for documentation. It works best with relational schemas where foreign keys, check constraints, and comments are already defined in the DDL, as the model can infer relationships and constraints directly from the source. The prompt is not suitable for undocumented NoSQL schemas with no formal structure, for schemas still in early prototyping where tables and columns are renamed daily, or for generating documentation that must satisfy regulatory submission requirements without human review. It is also not a replacement for a data catalog tool; it produces a documentation artifact, not a live, queryable metadata service.

Before using this prompt, ensure you have extracted the complete DDL for all target tables, including CREATE TABLE statements, ALTER TABLE additions, comments, and relevant portions of the system catalog such as information_schema.columns and information_schema.table_constraints. The prompt expects this raw input to be provided in the [INPUT] placeholder. If your schema contains Personally Identifiable Information (PII) in column names, comments, or sample data, redact it before passing it to the model. The output is a draft data dictionary that must be reviewed by a human who understands the domain to catch inferred descriptions that may be plausible but incorrect, especially for columns with cryptic or abbreviated names where the model guesses the meaning.

This prompt template is designed to ingest raw Data Definition Language (DDL) statements and system catalog metadata, producing a structured, human-readable data dictionary. The output is intended for data platform engineers who need to onboard new team members, support downstream consumers, or prepare for an audit. The template uses square-bracket placeholders so you can inject the specific schema artifacts, output format requirements, and any organizational standards without rewriting the core instruction.

textCopy
You are a senior data architect and technical writer. Your task is to generate a complete, accurate data dictionary from the provided database schema artifacts.

## Input
- Source DDL: [DDL_STATEMENTS]
- System catalog metadata (optional): [CATALOG_METADATA]
- Existing documentation fragments (optional): [EXISTING_DOCS]

## Output Schema
Generate a JSON object conforming to this structure:
{
  "database_name": "string",
  "generated_at": "ISO 8601 timestamp",
  "tables": [
    {
      "table_name": "string",
      "purpose": "A concise, business-meaningful description of the table's role.",
      "estimated_rows": "number or null",
      "columns": [
        {
          "column_name": "string",
          "data_type": "string",
          "nullable": true,
          "default_value": "string or null",
          "description": "A clear explanation of what this column stores, including any units, enumerations, or business rules.",
          "foreign_key_reference": "string or null"
        }
      ],
      "primary_key": ["column_name"],
      "unique_constraints": [["column_name"]],
      "indexes": [
        {
          "index_name": "string",
          "columns": ["column_name"],
          "type": "BTREE | HASH | GIN | etc.",
          "purpose": "Why this index exists, referencing specific query patterns if known."
        }
      ],
      "relationships": [
        {
          "type": "BELONGS_TO | HAS_MANY | HAS_ONE",
          "target_table": "string",
          "foreign_key_columns": ["column_name"],
          "description": "A plain-English explanation of the relationship."
        }
      ],
      "usage_notes": "Any critical operational notes, such as expected write frequency, partitioning strategy, or known pitfalls."
    }
  ]
}

## Constraints
- [CONSTRAINTS]
- If no specific constraints are provided, default to: "Derive all descriptions strictly from the DDL, catalog metadata, and provided documentation. Do not invent business logic. If a column's purpose is ambiguous, state 'Ambiguous: [reason]' in the description."

## Examples
[EXAMPLES]

## Risk Level
[RISK_LEVEL]

To adapt this template, replace the placeholders with concrete inputs. [DDL_STATEMENTS] should contain the CREATE TABLE, ALTER TABLE, and CREATE INDEX statements. [CONSTRAINTS] is where you enforce organizational rules, such as 'Use PascalCase for all entity names' or 'Flag any column storing PII with a pii_risk boolean in the description.' The [EXAMPLES] placeholder is critical for teaching the model the desired tone and depth; provide one fully documented table as a few-shot example. Set [RISK_LEVEL] to high if the documentation will be used for audit or compliance, which should trigger stricter validation in the implementation harness. After copying the prompt, always run the output through a schema validator before publishing it to a data catalog.

This prompt is designed to be the core of an automated documentation pipeline, not a one-off chat interaction. The primary input is a DDL script or a system catalog extract. The pipeline should parse this input, inject it into the [SOURCE_DDL] placeholder, and optionally supply [DIALECT] and [BUSINESS_CONTEXT] from a configuration file or metadata store. The model's output must be treated as a draft that requires structural validation before it can be published to a data catalog or wiki.

The implementation harness should enforce a strict output contract. After the model generates the documentation, a post-processing validator must parse the output—ideally as JSON or a strongly-typed object—and check for the presence of all required fields: table purpose, column descriptions, data types, nullability, default values, primary and foreign key definitions, and index descriptions. If the validator finds missing sections or malformed structures, the harness should trigger a retry loop. A common failure mode is the model omitting columns from large tables; the validator must compare the count of documented columns against the count of columns in the source DDL. For high-stakes schemas (e.g., financial or healthcare data), the harness must route the validated output to a human review queue before publication, flagging any columns where the model's confidence was low or where the description was auto-generated from the column name alone.

For model choice, prefer models with strong structured output capabilities and large context windows to handle extensive DDL. Use the model's native JSON mode or function-calling interface to constrain the output shape, rather than relying solely on the prompt's [OUTPUT_SCHEMA] instructions. Log every generation attempt, the validation result, and the final approved output to an audit table. This traceability is critical for governance and for debugging schema drift. The next step is to integrate this harness into your CI/CD pipeline so that documentation is regenerated whenever a migration script is committed, ensuring the data dictionary never falls out of sync with the live schema.