Denormalizing or flattening data is a common need in a database when you want to store the data in one or a few tables containing all the information and with little enforcement of structure.
The use of flattened data may be used in analytics programs, graphing, or data warehouses. All the information is just stored in one large table, referred to as a denormalized schema.
In Synatic, there is a Split step to help with this task. You can take multiple arrays and break out the individual records according to the array field values.
Adding the Split Step
In Synatic, the Split step is located in the Mapper drawer of the Steps palette. To use the Split step in your flow, you simply select and drag it onto the desired location in the flow, and then you configure the step.
Configuring the Split Step
The Split step requires you to define the Array Path to identify where the denormalizing will occur. If required you can add fields, that reside outside of the array but are part of the main record, to be merged with the resultant records.
Array Path - Enter the field which identifies the array in the source dataset.
Merge Fields - Add one or more fields that reside outside of the array in the main record, which you want to be merged with the resultant records after denormalizing.
Add item - Click to add fields to merge.
A quick example of the Split step in use
In a typical example, the Split step would exist in a Synatic flow similar to the one shown below.
For the example below, let's assume our input record looks like the structure below. It consists of two records each containing a single array of transactions related to each main record.
{
"Name" : "Bob",
"Lastname" : "Jones",
"Transactions":[
{"Date":"2021-01-03", "Amount": 300, "Merchant": "Costco"},
{"Date":"2021-01-04", "Amount": 400, "Merchant": "Walmart"},
{"Date":"2021-01-05", "Amount": 230, "Merchant": "Target"}
]
},
{
"Name" : "Fred",
"Lastname" : "Smith",
"Transactions":[
{"Date":"2021-01-06", "Amount": 800,"Merchant": "Macys"},
{"Date":"2021-01-07", "Amount": 900,"Merchant": "Costco"},
{"Date":"2021-01-08", "Amount": 700,"Merchant": "Macys"}
]
}
Denormalizing record arrays and merging with main record Names
The field we will use to uniquely identify each array in the record has the Array Path field name 'Transactions'.
Let's assume we also want to include the Name and Lastname of the record holder from the above input records. Use the Merge Fields to add the fields and merge them with each associated record. The output looks like this:
If you compare the input and output data structure, you can see how the Split step functions. You can now add functionality in the flow to process the records further.
Options
None.
Advanced Mode Options
None.
Limitations and known issues
None.
Useful Tutorials
See the Split tutorial to learn how to configure the step and the basic flow.
Revisit the Getting Started page for a refresher.
Read more about Steps here.