Change Data Capture Schema Mapping Prompt

This prompt is for data platform engineers who need to produce a rigorous, machine-readable mapping specification from source table schemas to CDC event payloads. The core job-to-be-done is translating a relational schema into a complete set of event types (insert, update, delete), payload structures, tombstone handling rules, and ordering guarantees that downstream consumers can rely on. Use this when you are designing a new CDC feed, onboarding a new source table into an existing event stream, or auditing an existing mapping for correctness gaps.

The ideal user brings the source DDL, known access patterns, and the target event envelope format (e.g., Debezium, CloudEvents, a proprietary contract). The prompt requires [SOURCE_SCHEMA] as DDL or a structured table description, [TARGET_ENVELOPE] to define the wrapper format, and [CONSUMER_REQUIREMENTS] that specify ordering needs, tombstone expectations, and any denormalization rules. Do not use this prompt for simple table-to-table ETL mappings, for designing the CDC infrastructure itself, or when the source schema is unknown or purely hypothetical. It is also inappropriate when the consumer contract is already fixed and you only need a mechanical translation script rather than a design review.

Before running this prompt, ensure you have resolved upstream decisions about the CDC tool (Debezium, Maxwell, custom WAL reader) and the message broker (Kafka, Pulsar, Kinesis). The prompt assumes a log-based CDC approach with at-least-once delivery semantics. If your system uses trigger-based or polling CDC, you must adapt the ordering guarantee section manually. After generating the mapping specification, validate it against the eval checks described in the full playbook: completeness of change event coverage, correct primary key propagation, tombstone format for deletes, and ordering column inclusion. For high-risk data paths involving financial ledgers or compliance-bound records, always run the output through a peer review and a consumer compatibility test before production deployment.

This prompt template is designed to produce a complete change data capture (CDC) mapping specification. It takes source table schemas, business context, and operational constraints as input and outputs a structured document detailing event types, payload schemas, tombstone handling, and ordering guarantees. The template uses square-bracket placeholders that you must replace with your specific source system details before execution.

textCopy
You are a data platform architect specializing in change data capture (CDC) system design. Your task is to produce a rigorous CDC schema mapping specification from a set of source table definitions.

## INPUT
Source System: [SOURCE_SYSTEM_NAME]
Source Tables and DDL:
[SOURCE_TABLE_DDL]

Business Context:
[BUSINESS_CONTEXT]

## CONSTRAINTS
- Target event format: [EVENT_FORMAT, e.g., Avro, Protobuf, JSON]
- Schema registry: [SCHEMA_REGISTRY_URL_OR_PATTERN]
- Ordering guarantee required: [ORDERING_GUARANTEE, e.g., per-partition, global]
- Tombstone strategy: [TOMBSTONE_STRATEGY, e.g., null payload, dedicated header]
- Downstream consumers: [CONSUMER_LIST_AND_TYPES]
- Retention policy: [RETENTION_POLICY]

## OUTPUT_SCHEMA
Produce a JSON object with the following structure:
{
  "source_system": "string",
  "mappings": [
    {
      "source_table": "string",
      "event_types": [
        {
          "type": "insert|update|delete|read",
          "topic_name": "string",
          "key_schema": { "fields": [{"name": "string", "type": "string", "source_column": "string"}] },
          "payload_schema": { "fields": [{"name": "string", "type": "string", "source_column": "string", "transformation": "string|null"}] },
          "tombstone_handling": "string",
          "ordering_guarantee": "string"
        }
      ],
      "primary_key_columns": ["string"],
      "capture_mode": "string",
      "filter_condition": "string|null"
    }
  ],
  "global_ordering_strategy": "string",
  "schema_evolution_policy": "string",
  "unmapped_tables": ["string"],
  "risks_and_recommendations": ["string"]
}

## INSTRUCTIONS
1. For each source table, identify all columns that should be included in the event payload.
2. Determine the appropriate key schema using primary key columns or a business key if specified.
3. Map source column types to the target event format types. Document any type conversions.
4. For each table, define event types for INSERT, UPDATE, and DELETE operations. Include READ events only if explicitly requested.
5. Specify tombstone handling for DELETE events according to the provided strategy.
6. Document the ordering guarantee that can be achieved given the source system capabilities.
7. Identify any source tables that cannot be mapped and explain why.
8. Flag risks related to schema evolution, large payloads, or type compatibility.

## EXAMPLES
[FEW_SHOT_EXAMPLES]

## RISK_LEVEL
[RISK_LEVEL, e.g., low, medium, high]

To adapt this template, replace each bracketed placeholder with concrete values from your environment. The [SOURCE_TABLE_DDL] placeholder should contain the full DDL for all tables under consideration. The [FEW_SHOT_EXAMPLES] placeholder is optional but strongly recommended for production use—include one or two complete mapping examples that demonstrate your organization's conventions for topic naming, schema structure, and type mappings. The [RISK_LEVEL] field should be set to high if the source system is a system of record with financial, healthcare, or compliance data; in such cases, always route the output through human review before applying the mapping to a live CDC pipeline.

Before using this prompt in production, validate the output against your schema registry's compatibility rules and test it with a representative sample of source data. Common failure modes include missing columns in the payload schema, incorrect type mappings for temporal or decimal types, and incomplete tombstone specifications that could cause downstream consumers to misinterpret deleted records. Run the output through an automated schema validator and a peer review step before committing it to your CDC configuration.

This prompt is designed to be a deterministic step in a schema management pipeline, not a one-off chat interaction. The primary integration point is a CI/CD check that runs whenever a source table DDL changes or a new CDC connector is configured. The application harness should capture the source DDL, existing event envelope schema, and any consumer contracts as structured inputs, then feed them into the prompt through a templating layer that enforces strict JSON output parsing.

The implementation must include a validation layer that parses the model's JSON output and checks for structural completeness before the mapping is accepted. At minimum, validate that every source table column is accounted for in at least one event type (insert, update, delete), that tombstone events are explicitly defined for hard-delete scenarios, and that the declared ordering guarantees (e.g., per-key ordering via partition columns) match the source system's actual capabilities. If the output fails structural validation, the harness should retry once with the validation errors appended as additional [CONSTRAINTS] in the prompt context. After two failures, escalate to a human reviewer through a ticketing system rather than silently accepting an incomplete mapping.

For production deployment, log every generated mapping alongside the input schema version hash, model version, and validation results. This creates an audit trail that connects CDC payload schemas back to the exact source schema state that produced them. When source schemas evolve, re-run the prompt against the new DDL and use a diff against the previous mapping to detect breaking changes in the event payload. Wire this diff into your schema registry's compatibility checks so that downstream consumers are alerted before incompatible CDC events reach their topics. Avoid using this prompt for real-time schema inference at event ingestion time—the latency and non-determinism of LLM calls make them unsuitable for hot-path CDC pipelines.