Transformation Types
● Direct Map:
○ Explanation: This is the simplest transformation where data is moved from a
source field to a target field without any modification. It's a one-to-one
mapping.
○ Example: Imagine you have a customer's first name in a "Source_FirstName"
column and you need to copy it directly to a "Target_FirstName" column. No
changes are made to the data itself.
● Selective Column and Row Type Transformation :
○ Explanation: This involves choosing specific columns or rows from the
source data to load into the target. You might filter rows based on certain
criteria or only select a subset of columns.
○ Example:
■ Column Selection: From a table with "CustomerID," "FirstName,"
"LastName," and "Address," you might only load "CustomerID,"
"FirstName," and "LastName" into the target.
■ Row Selection: From a sales dataset, you might only load rows where the
"SalesAmount" is greater than $1000.
● Translation Transformation :
○ Explanation: This involves converting data from one format or value to
another. It often includes data type conversions, encoding changes, or
replacing values.
○ Example:
■ Converting a date from "MM/DD/YYYY" format to "YYYY-MM-DD."
■ Replacing "Y" with "Yes" and "N" with "No" in a "Flag" column.
■ Converting a string to an integer.
● Lookups Transformation :
○ Explanation: This involves retrieving related data from another table or data
source based on a key value. It's used to enrich the source data with
additional information.
○ Example: You have a "CustomerID" in your source data. Using a lookup table
that maps "CustomerID" to "CustomerName," you can retrieve the
corresponding customer name and add it to your target data.
● Transpose Transformation :
○ Explanation: This transformation switches the rows and columns of a
dataset. Rows become columns, and columns become rows.
○ Example: If you have data where each row represents a product and each
column represents a month's sales, transposing it would make each row
represent a month, and each column represent a product's sales for that
month.
● Field Splitting Transformation :
○ Explanation: This involves dividing a single field into multiple fields based on
a delimiter or pattern.
○ Example: Splitting a "FullName" field (e.g., "John Doe") into "FirstName"
("John") and "LastName" ("Doe") fields, using the space as a delimiter.
● Field Merging Transformation :
○ Explanation: This combines multiple fields into a single field. It's the opposite
of field splitting.
○ Example: Combining "FirstName" and "LastName" fields into a "FullName"
field, separated by a space.
● Calculated and Derived Transformation :
○ Explanation: This involves creating new fields by performing calculations or
applying logic to existing fields.
○ Example:
■ Calculating "TotalSales" by multiplying "Quantity" and "UnitPrice."
■ Creating a "SalesCategory" field based on the "SalesAmount" (e.g.,
"High," "Medium," "Low").
■ Deriving someones age from their date of birth.
