Import from SQL Database
Our companies, deals and line items live in a SQL database.
A company is associated with many deals.
A deal is associated with many line items.
The Flows
Although we could combine everything into one Flow, it makes a lot more sense to have Flow for each entity.
Each import Flow is almost identical, so we'll only show the companies import Flow.
Import companies Flow
Below is a screenshot of the Flow.
Deletes the hsc-company entries from the Data Store.
Query the customers table. Batching reads the rows in chunks (1'000 rows in this case). This is necessary for large amounts of data.
At this point, we divide the data into two edges.
The first edge is the raw data that we get from the customers table.
The second edge performs some data validation and mapping. The green bubble tells us this. Click here to see how this is done.
Both of these can now be passed onto the output edge. We are going to store this in teh Ziggy Data Store so we can see the before and after in the Data Browser.
We now write this data to the Data Store. We can view and check it later before we ever export the data into Hubspot.
The Batch End block is the end point for the batching loop.
Edge data viewing and mapping
You'll see that Step 3 above-mentioned data mapping. You can tell that an edge has a mapping from the green bubble (which alone indicates data validation) with a signpost symbol (indicating mapping).
If we click on the bubble, we get the following dialog.
Click on the tabs below for an explanation of each tab in the dialog.
The data that was output from the previous Block and arrives on the edge.
The data that is output from the edge after the mapping has been done (if there is one)
The Structure expected for data arriving on the edge (Northwind Customer). You can apply a validation at this point. Structures are defined elsewhere and can be re-used.
If you want to map, then you choose a target structure (Hubspot Company) to map to. You can then define the field level mapping. Far more complex data transformations can be handled, but this is beyond the scope of this example.
If you look at output edge data after of Merger Block, you can see how both the original data ad the mapped data are preserved. This is extremely useful when debugging your transformations.
