Schema Mapping: Source to Target

Before data is loaded into its final destination, typically after extraction and transformation, an important step is required. This involves ensuring the data fits perfectly into the structure, or schema, of your target system (such as a database table or a data warehouse dimension). This is the function of schema mapping.

Think of your transformed data as cargo ready to be loaded onto a ship (your target system). The ship has specific containers (fields or columns) designed for particular types of cargo. Schema mapping is the loading manifest; it explicitly tells the loading crew which piece of transformed data goes into which container on the ship. Without this mapping, data might end up in the wrong place, be misinterpreted, or fail to load entirely.

What is Schema Mapping?

Schema mapping is the process of defining the correspondence between the fields in your transformed source data and the fields in your target system's schema. It acts as a translation layer, ensuring that each piece of data finds its correct home in the destination.

Imagine you transformed customer data and have fields like customer_id, first_name, last_name, and signup_date. Your target data warehouse table, DimCustomer, might have columns named CustomerID, FirstName, LastName, and RegistrationTimestamp. Schema mapping defines these links:

• customer_id (source) maps to CustomerID (target)
• first_name (source) maps to FirstName (target)
• last_name (source) maps to LastName (target)
• signup_date (source) maps to RegistrationTimestamp (target)

Why is Mapping Necessary?

Data rarely flows perfectly from source transformation output to target system input without some guidance. Mapping is necessary for several reasons:

1. Field Naming Differences: Source systems and target systems often use different naming conventions for the same logical piece of data (e.g., user_email vs. EmailAddress). Mapping bridges this gap.
2. Data Type Compatibility: A source field might be a string (e.g., "2023-10-26"), but the target field requires a specific Date data type. The mapping process can include instructions for type conversion, ensuring the data conforms to the target schema's requirements. For example, mapping '20231026' (source string) to a OrderDate (target DATE type) requires a conversion function.
3. Structural Adjustments: Sometimes the structure needs minor adjustments during loading.
   • Combining Fields: You might map source.area_code and source.phone_number to a single target.PhoneNumber field.
   • Splitting Fields: A single source.full_address might need to be mapped to separate target.Street, target.City, and target.ZipCode fields.
   • Ignoring Fields: Not all transformed fields might be needed in the target. Mapping allows you to select only the relevant ones.
4. Adding Default or Calculated Values: Mapping rules can specify default values for target fields if the source data is missing (e.g., setting target.Status to 'Pending' if no status is provided). They can also define simple calculations based on source fields. For instance, if the target needs a TotalPrice field, the mapping might define it based on source fields: $TotalPrice = Quantity \times UnitPrice$.

Common Mapping Scenarios

Let's look at typical mapping patterns you'll encounter:

• Direct Mapping (One-to-One): The simplest form. A single source field maps directly to a single target field, possibly with a name change.
  • source.product_id -> target.ProductID
• Data Type Conversion: As mentioned, converting data types to match the target.
  • source.order_total (string) -> target.OrderTotal (decimal)
• Concatenation (Combining): Joining multiple source fields into one target field.
  • source.first_name, source.last_name -> target.FullName (e.g., using a rule like target.FullName = source.first_name + ' ' + source.last_name)
• Decomposition (Splitting): Dividing a single source field into multiple target fields.
  • source.timestamp (datetime) -> target.Date, target.Time
• Constant/Default Value: Assigning a fixed value to a target field, often used for metadata or status flags.
  • 'Processed' -> target.RecordStatus
• Expression/Derived Value: Calculating a target field's value based on one or more source fields using a formula or simple logic.
  • source.price, source.tax_rate -> target.PriceWithTax (e.g., using $PriceWithTax = Price \times (1 + TaxRate)$)
• Lookup: Enriching data by mapping a source key to a value looked up in another table or reference dataset (though complex lookups are often handled during the Transformation stage). A simple case might be mapping a country code to a country name.
  • source.country_code (e.g., 'US') -> target.CountryName (e.g., 'United States') via a lookup table.

Defining the Mappings

How do you actually define these mappings in an ETL process? It varies depending on the tools and techniques you use:

• ETL Tools with GUIs: Many visual ETL tools provide a graphical interface where you can drag lines between source and target fields to create mappings. They often have built-in components for common transformations like type conversions or concatenations.
• Configuration Files: Mappings can be defined in structured files like JSON, YAML, or XML. This approach is common in code-based ETL frameworks, allowing mappings to be version-controlled alongside the code.
• Code/Scripts: In custom ETL scripts (e.g., using Python), mappings are often defined directly within the code, perhaps using dictionaries or dedicated mapping functions. For example:

  # Example Python dictionary for mapping
  mapping_rules = {
      'CustomerID': 'source_data.user_id',
      'Email': 'source_data.email_address',
      'RegistrationDate': 'convert_to_date(source_data.signup_dt)',
      'Status': "'Active'" # Assign a default string value
  }
  

Example: Mapping Customer Data

Let's solidify this with a small example. Suppose your transformed data looks like this (as rows):

user_id	f_name	l_name	registered_on	city	state
101	Alice	Smith	2023-01-15T10:00Z	Newark	NJ
102	Bob	Jones	2023-02-20T14:30Z	Trenton	NJ

Your target database table Customers has the following structure:

• CustID (Integer, Primary Key)
• FullName (String)
• Location (String)
• RegisteredDate (Date)
• IsActive (Boolean)

The schema mapping rules might be:

1. user_id -> CustID (Direct map, potential type cast from string/number to integer)
2. f_name, l_name -> FullName (Combine with a space: f_name + ' ' + l_name)
3. city, state -> Location (Combine with a comma: city + ', ' + state)
4. registered_on -> RegisteredDate (Extract date part, cast to Date type)
5. (No source field) -> IsActive (Assign default value: True)

Here's a diagram illustrating these mappings:

Mapping rules connecting fields from the transformed source data structure to the target table structure, including combinations and default values.

Handling Mapping Challenges

While often straightforward, mapping can present challenges:

• Missing Source Fields: What happens if a source field used in a mapping doesn't exist in a particular record? Your mapping logic or ETL tool needs a strategy: skip the record, load NULL, load a default value, or raise an error.
• Mapping Validation: Ensure your mappings are correct before running a large load. Errors here can lead to data corruption or load failures. Test mappings with sample data.
• Schema Evolution: Source or target schemas can change over time. Mappings must be updated to reflect these changes. Keeping mappings maintainable (e.g., in configuration files or well-documented code) is important.

Schema mapping is the final alignment step before data is loaded. It ensures that the clean, transformed data precisely fits the structure and requirements of the target system, making the subsequent loading process smoother and the resulting data accurate and usable for analysis.