You can map data from one schema to another by creating a field-by-field mapping document that defines how each source field translates to a destination field — including data type conversions, value transformations, and rules for fields that don't have a direct equivalent.
Schema mapping is one of the most common operations in data work, and one of the most error-prone. When two systems use different field names, different data types, or different representations of the same information, mapping defines how to translate between them. A poorly documented or poorly implemented mapping is a silent source of data quality failures that can persist for years.
When Schema Mapping Is Required
System integrations: Your CRM stores customers; your billing system stores accounts. The same entity, different schemas. A mapping defines how CRM fields translate to billing fields.
Sohovi finds gaps, duplicates, and format errors in your CRM data — so your team is working from records they can trust.
Data migrations: Moving data from an old system to a new one requires mapping the old schema to the new schema. Every field, every type, every value.
Vendor file imports: A supplier's product file uses their naming conventions. Your system uses yours. A mapping translates between them.
API integrations: An external API returns data in its own format. Your internal database uses yours. A mapping transforms the API response into your schema.
The Components of a Schema Mapping
A complete schema mapping document includes:
1. Source field → Destination field: The direct field name mapping. "customer_first_name" in the source maps to "first_name" in the destination.
2. Data type conversion: The source field is a string; the destination expects a date. Define how the string "2024-03-05" is converted to a date object.
3. Value transformation: The source stores status as integers (1, 2, 3); the destination expects strings ("Active", "Inactive", "Pending"). Define the integer-to-string mapping.
4. Default values: What value does the destination field get if the source field is null? Sometimes a default is appropriate; sometimes a null in the source should remain null in the destination.
5. Derived fields: The destination has a "full_name" field; the source has separate "first_name" and "last_name" fields. Define the concatenation logic.
6. Unmapped fields: Source fields that have no destination equivalent — document whether they're dropped or stored elsewhere.
Common Schema Mapping Failures
Silent type mismatches: A date stored as a string in the source is mapped to a date field in the destination without conversion. The import succeeds but dates are stored as strings, breaking all date calculations.
Undocumented value transformations: The mapping document says "status maps to status" but doesn't document that source status=1 → destination status="Active". The implementer guesses and gets some values wrong.
Unmapped required fields: A required destination field has no source equivalent. The mapping doesn't address this, so the import fails — or worse, fills required fields with nulls or incorrect defaults.
Case sensitivity: Source field names are case-sensitive in the destination system. The mapping uses the wrong case, causing mismatches.
Frequently Asked Questions
Q: What is schema mapping in data integration? Schema mapping is the process of defining how fields in one data structure translate to fields in another. It includes field name mapping, data type conversion, value transformation, default values for unmapped fields, and logic for derived fields.
Q: What's the difference between a schema mapping and an ETL transform? A schema mapping is the specification — the documentation of how fields translate. An ETL transform is the implementation — the code or configuration that executes the mapping. Both are necessary; the mapping document should exist before any ETL code is written.
Q: How do I handle source fields that don't have a direct destination equivalent? Three options: drop the field (document this decision), store it in a generic "notes" or "custom data" field in the destination, or extend the destination schema to include the field. The right choice depends on whether the information is needed in the destination system.
Q: What's the most common schema mapping failure? Undocumented value transformations — mapping documents that say "field A maps to field B" without specifying what happens when A has values that B doesn't expect. Always document the value transformation, not just the field name mapping.
Q: How do I validate that a schema mapping was implemented correctly? Run a sample of records through the mapping and compare the output to expected values. For each field in the mapping document, verify that the destination field contains the correct translated value. Check edge cases: nulls, special characters, maximum-length strings, boundary date values.
Q: How should I handle one-to-many field mappings (one source field maps to multiple destination fields)? Define the splitting logic explicitly: how does the source field value get distributed across destination fields? For example, a single "name" source field mapping to "first_name" and "last_name" destination fields requires a parsing rule.
Q: How should I handle many-to-one field mappings (multiple source fields map to one destination field)? Define the combination logic: which source field takes priority if both are populated? How are they concatenated if both should contribute to the destination value? What happens if one is null?
Q: What format should a schema mapping document be in? A structured table or spreadsheet is most practical: rows for each field mapping, columns for source field name, destination field name, data type conversion, value transformation rules, default value, and notes. The format matters less than the completeness and clarity.
Q: How do I version control schema mapping documents? Store them in a version-controlled repository (Git) alongside the ETL code that implements them. When either the source or destination schema changes, update the mapping document and the implementation together, with a version history showing what changed and when.
Q: What's the relationship between schema mapping and data quality? A complete, accurate mapping is a data quality investment. A mapping with gaps, ambiguities, or undocumented transformations is a data quality risk. Every undocumented decision in a mapping is a potential source of errors when the implementation deviates from what was intended.
Schema mapping is the contract between systems. Document it completely, validate it against real data, and version control it — the cost of a mapping error discovered late far exceeds the cost of thorough documentation upfront.
Sohovi lets you set up validation rules for any column and instantly see which rows fall outside them — no code or SQL required.
