Skip to content
DMDQP AI Capability Hub

Source-to-Target Mapping Analysis

Source-to-Target Mapping Analysis

Context

Source-to-target mapping (STM) documents define how data moves from source systems to target systems — what fields map to what, how they are transformed, and what business rules apply. Gaps and ambiguities in an STM document are the most common source of rework during data integration development. Running a structured analysis of the STM before development starts catches these issues while they are still cheap to fix.

This prompt is typically the first step. Once gaps and ambiguities are resolved, use the Transformation Code Generation prompt to produce SQL or PySpark implementation from the cleaned-up mapping.

The Prompt

Review this source-to-target mapping document. Identify:
1. Fields in the target with no source mapping (missing mappings)
2. Transformation rules that are ambiguous or underspecified (could be interpreted multiple ways)
3. Fields where the source and target types differ and no conversion rule is specified
4. Business rules that are described in prose but have no concrete implementation guidance
5. Any dependencies between transformation rules that could cause ordering issues


Mapping document:
[PASTE OR DESCRIBE THE MAPPING HERE]


Target system: [TARGET DB / TABLE / FORMAT]
Source system(s): [SOURCE SYSTEMS]

Usage Instructions

  1. Open build-cli in your terminal.
  2. Copy the prompt above.
  3. Paste the STM document — works best as a table or structured list. Unstructured prose mappings will produce more general findings.
  4. Fill in the target and source system names.
  5. Use the output as an agenda for your next requirements or design session with the business.
  6. Follow up with: “For ambiguity #2, what are the two most likely interpretations, and what question should I ask the business to resolve it?”

Example Output

The AI will produce a structured findings list such as:

Missing mappings (target fields with no source):

  • created_by — no source field specified. Is this the ETL job identity or the originating system user?
  • record_status — target has ACTIVE/INACTIVE/ARCHIVED enum; no mapping from source status field found.

Ambiguous transformation rules:

  • Rule 3: “Apply exchange rate as of record date.” Which exchange rate table? Which currency pair? What if the rate is missing for a given date?

Type mismatches:

  • Source order_date is VARCHAR(10); target order_date is DATE. No conversion rule specified — implicit cast or explicit TO_DATE()?

Ordering dependency:

  • Customer lookup (Rule 7) depends on the result of the address standardisation step (Rule 4) — Rule 4 must complete before Rule 7 runs.