Introduction

Users sometimes need to transform one source row into multiple target rows. For example, attributes are stored in columns in one table or file and may need to be broken into multiple records - one record per column attribute. In general it allows one to convert columns to rows. 

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

Example 1

Customer 360 Use-case

Assume we have a source ‘Customer Profile’ table and ‘Customer Purchase’ table. 

Custom Profile Table

Build a pipeline that ingest the above data into a Customer 360 Master table that has the following schemas and configuration.

Input Schema

• Customer ID

• First Name

• Last Name

• Shipping Address

• Credit Card

• Billing Address

• Last Update Date

Normalize

• 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

Output Schema

• ID

• Attribute Type

• Attribute Value

• Date

Following is the output from running the pipeline with the configurations specified above. Let’s call this ‘Master Customer 360 Table'

Custom Purchase Table

Now, let’s assume the user configures the pipeline for the above data to be added to ‘Master Customer 360 Table'

Following are schema and configuration

Input Schema

• Customer Id

• Item ID

• Item Cost

• Purchase Date

Normalize

• 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

Output Schema

• ID

• Attribute Type

• Attribute Value

• Date

Master Customer 360 Table would be updated as follows

Example 2

Assume we got following data from any relational store as source

Configuration is specified as follows

Input Schema

• Name, String

• Year, Int

• Month, Int

• Rent, Float

• Food, Float

• Transportation, Float

Normalize

• 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

Output Schema

• Name, String

• Year, Int

• Month, Int

• ExpenditureType, String

• Expenditure, Float

Assume we got following data from any relational store as source