A step by step guide to denormalizing records

Praise Magidi avatar
Written by Praise Magidi
Updated over a week ago

The Split step can be used to denormalize or flatten data and create a dataset of records from normalized or indexed data, and then process them later in your flow. This is useful when you want to use the data in analytics programs, graphing, or data warehouses.

In this tutorial, we will use the Split step to extract data from a dataset that is normalized or indexed and retrieved from some data source. For this example, we'll use a small sample of test data.

There are several steps to creating, configuring, and using a Split step:

Watch the video below to learn how to add the Synatic Split step, configure and then test it.

Creating a flow to Split records

We'll create a flow, which is constructed as shown below, to illustrate the operation of the Split step.

Synatic - Add Split step

The flow consists of associated steps to process and denormalize a dataset. Click on the links below to learn more about adding and configuring each step.

Step 1

Step 2

Step 3

Adding the Split Step

The Split Step is located in the Mapper drawer in the Steps palette.

To use the Split Step in your flow:

  1. Select and drag it onto the desired location in the flow after the JSON step.

  2. Now you can configure it.

Configuring the Split Step

The Split step requires the Array Path to identify the array field to use when flattening the data and the Merge Fields from the main record fields to attach to the flattened data.

Synatic - Edit Split step

When the Split step opens:

  1. Click on the Array Path and enter the field name of the array, "Transactions".

  2. In Merge Fields, click on the Add item field and add two fields, "Name" and "Lastname".

  3. When done, click Save Step.

An example of generic application in a Synatic Flow

The steps below describe the function of each of the individual steps required for the Flow to operate.

In this tutorial, we'll use the field "Transactions" to identify the arrays and "Name" and "Lastname" to identify the main record fields to attach to the flattened dataset.

1. Retrieving the data

To retrieve data from an HTTP Service, we'll add an HTTP Service step as the first step and configure it to read from a specific web-based dataset.

2. Reading the data

The data from the source contains JSON data, so we'll add the JSON Reader to parse the data from the source.

To test the JSON reader step:

  • Select the blue play ▶ icon adjacent to the JSON step in the flow.

  • We can view the data from the source and get an idea of its format.

Synatic - Split step JSON source data

For clarity, each record retrieved has the following structure:

"Name" : "Bob",
"Lastname" : "Jones",
{"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",
{"Date":"2021-01-06", "Amount": 800,"Merchant": "Macys"},
{"Date":"2021-01-07", "Amount": 900,"Merchant": "Costco"},
{"Date":"2021-01-08", "Amount": 700,"Merchant": "Macys"}

The field we will use to uniquely identify each array has the field name 'Transactions'.

3. Splitting the records

If you examine the records in step 2 above, you will notice that some records have the Transactions field with an attached array of transactions records.

For example, all the records in the array will be denormalized into a single table of rows and columns. The Name and Lastname in the main record will be merged to each normalized record as shown in step 4 below.

4. Displaying the Records

To test the Split step display of the resulting denormalized data up to this point, we'll add the Calculator step. The Calculator step is not typically needed, but we use it for this tutorial to display the output data when using the debug test in the flow.

So, let's test the flow after the Calculator step.

  • Select the blue play ▶ icon adjacent to the Calculator step in the flow.

  • We can see the denormalized records created by the Split step and get an idea of the data and its format.

  • Compare this to the data in step 2. You'll note that the main record fields "Name" and "Lastname" are merged with each record.

Synatic - Split step data result

This confirms that the Split step function was successful. You can now add functionality in the flow to process the records further.

Did this answer your question?