Introduction

Use-Case

• Convert wide rows and reducing data to it canonicalize form.
• Reduce the need for restructuring the collection, as new types of data are being added.
• Let’s assume that we are building a customer 360 Master table that aggregates data for a user from multiple sources. Each of the source has it’s own type of data to be added to a customer id. Instead of creating wide columns, normalization allows one to transform into it’s canonical form and update the customer 360 profile simultaneously from multiple sources. 

Use Stories

• User should be able to specify a record field mapping. The record field mapping, just maps the field in input schema to the field in the output schema. These are the fields that would be available in every record. 
• User should be able to specify a normalize field mapping. This will describe the input field name and to what output field it should be mapped to and where the value needs to be added. 

Example

• Customer ID
• First Name
• Last Name
• Shipping Address
• Credit Card
• Billing Address
• Last Update Date

• Record Field Mapping
  • Input Field : Customer ID, map to : ID
  • Input Field : Last Update Date, map to : Date
• Record Field Normalizing
  • Input Field : First Name, to : Attribute Type
    • Value to : Attribute Value
  • Input Field : Last Name, to : Attribute Type
    • Value to : Attribute Value
  • Input Field : Credit Card, to : Attribute Type
    • Value to : Attribute Value
  • Input Field : Billing Address, to : Attribute Type
    • Value to : Attribute Value

• ID
• Attribute Type
• Attribute Value
• Date

• Customer Id
• Item ID
• Item Cost
• Purchase Date

• Record Field Mapping
  • Input Field : Customer ID, to : ID
  • Input Field : Purchase Date, to : Date
• Normalize Field Mapping
  • Input Field : Item ID, to : Attribute Type
    • Value to : Attribute Value
  • Input Field : Item Cost, to : Attribute Type
    • Value to : Attribute Value

• ID
• Attribute Type
• Attribute Value
• Date

• Name, String
• Year, Int
• Month, Int
• Rent, Float
• Food, Float
• Transportation, Float

• Record Field Mapping
  • Input Field : Name, to : Name
  • Input Field : Year, to : Year
  • Input Field : Month, to : Month
• Record Field Normalizing
  • Input Field : Rent, to : ExpenditureType
    • Value to : Expenditure
  • Input Field : Food, to : ExpenditureType
    • Value to : Expenditure
  • Input Field : Transportation, to: ExpenditureType
    • Value to : Expenditure

• Name, String
• Year, Int
• Month, Int
• ExpenditureType, String
• Expenditure, Float

Design

• Output schema will be created on the basis of inputs to fieldMapping and fieldNormalizing fields, input json will look like as below:

     {
        "name": "Normalize",
        "plugin": {
        "name": "Normalize",
        "type": "transform",
        "label": "Normalize",
        "properties": {
           "fieldMapping": "Customer ID:ID,Purchase Date:Date",
           "fieldNormalizing": "Item ID:Attribute Type:Attribute Value,Item Cost:Attribute Type:Attribute Value"
         }
       }
     }

• fieldMapping and fieldNormalizing will be mandatory fields
• Type of all output schema fields will be STRING
• Type and Value mapping for all normalize fields must be same, otherwise it is invalid.
  Example: Below mapping shows, Item ID and Item Cost mapped to normalize columns Attribute Type and Attribute Value.

  "fieldNormalizing": "Item ID:Attribute Type:Attribute Value,Item Cost:Attribute Type:Attribute Value"

  But below mapping is invalid as Item Cost mapped to different normalize columns.

  "fieldNormalizing": "Item ID:Attribute Type:Attribute Value,Item Cost:Cost Type:Cost Value"