By design, SyncHub does very few data transformations. We do, however, make some minor changes to ensure consistency.
### Date Formats
When retrieving data from the API, we convert all dates and times to Coordinated Universal Time (UTC) to avoid ambiguity across your data set. Often a data source will provide the timezone of a store or location – you can use this to convert data, such as sales, back to the local time.
### Global fields
#### CreatedDate and ModifiedDate
All data tables contain CreatedDate and ModifiedDate columns. These will be populated provided we can ascertain the creation date or modification date of a record. Note that if a date field is provided by a data source then this will also be included in the data set.
The RemoteID is the unique identifier supplied by the data source. In the case of parent-child queries – where there is no clear unique identifier – we will generate a RemoteID using parent and child identifiers.
####Nested data and parent-child relationships
Often data sources will provide nested data, for example sale and sale items. In order to put this data in a tabular format, SyncHub will create a second table for the child items and insert a parent remote ID column. In this example, the Sale Item table would have a SaleRemoteID column.
Instead of removing data from your database, SyncHub will use an IsDeleted field to mark records which are no longer available at the data source. If a data source provides a deleted status field then we will update the isDeleted field accordingly.
SyncHub draws data from cloud platforms and stores it in a relational format in your data warehouse. To use the warehouse, you need to know the relationships between the tables. There are two main methods you can use.
####Method 1 – as provided by the cloud platform
In many cases, your cloud platform will provide "relationship" fields out of the box. The naming scheme for these varies, but it is intuitive. In the example below, our ProductPrice table has a column called ProductGuid. It is logical that this refers to the Guid column of the Product table:
####Method 2 – SyncHub auto-relationships
The above method only works if the cloud platform formats its API in a relational format. However, many APIs return their data nested. In these cases, there is no parent column provided because it is inferred by the nesting relationship.
By definition, your relational data warehouse does not store nested data – each entity is instead in its own table. In these scenarios, SyncHub will helpfully create additional columns which allow you to tie the child tables back to their parent tables:
- Every table has a RemoteID column – regardless of whether it is a parent or child table.
- If a table has a Child table, then the child will have a column named [Parent]RemoteID. In the example below, the column is called TaskRemoteID because the parent is the "Task" table: