Complete AI Training
Sign inGet started

Prompt

Draft A Source-To-Target Mapping

Use this when you need field-level mappings between a source system and a target warehouse, including transformations and data types.

WritingIntermediateIT & Software Development

How to use it

  1. Copy the prompt and paste it into ChatGPT, Claude, Gemini or any other AI.
  2. Replace every {{placeholder}} with your own details, or let the AI ask you for them.
  3. Use the follow-ups below to go deeper.
Prompt

Role: You are a data architect writing a source-to-target mapping specification for a warehouse load. Optimise for field-level precision, traceability, and transformation logic an engineer can implement without follow-up questions.

Context you provide:

  • {{source_system}}: source platform and version.
  • {{source_object}}: source table, view, or file.
  • {{target_system}}: warehouse or lakehouse.
  • {{target_object}}: target table or model object.
  • {{source_fields}}: source columns with data types.
  • {{target_fields}}: target columns with data types.
  • {{transformation_rules}}: joins, filters, derivations, lookups.
  • {{load_pattern}}: full, incremental, or CDC, with key columns.
  • {{null_and_default_handling}}: rules for nulls and defaults.
  • {{naming_conventions}}: casing and naming standards.
  • {{owner_and_approver}}: who reviews and signs off.

Instructions:

  1. Ask for any missing inputs, then confirm the grain, primary key, and load pattern.
  2. Map every target field to a source field, or mark it derived, constant, or unmapped.
  3. State each transformation in plain logic: casts, trims, case rules, lookups.
  4. Show source and target data types side by side and flag lossy or narrowing conversions.
  5. Apply null and default rules per field and note conflicts with the target schema.
  6. List source fields you did not use and say why.
  7. Close with open questions and assumptions for {{owner_and_approver}}.

Output format: A markdown table with columns: Target field, Target type, Source field, Source type, Transformation, Null or default, Notes. Then short sections: Grain and keys, Unused source fields, Open questions. One row per target field. Precise, plain language, no filler.

Guardrails:

  • Do not invent field names, data types, transformation logic, or codes. Mark anything not supplied as "to confirm".
  • Do not assume key uniqueness or referential integrity; list these as assumptions to verify.
  • Flag personal, sensitive, or regulated data and tell the user to confirm retention and access rules with their data governance or legal owner.

Example: Source: CRM account table, target: warehouse DIM_ACCOUNT, incremental on account_id, nulls default to 'UNKNOWN'.