Modelling a bitemporal data set in a data warehouse

Question

I have a bitemporal EAV type model.

There are multiple attribute tables(one for each value data type(int_attributes, float_attributes, etc. )) looking like:

|id|field_id|value|as_of_date_start|as_of_date_end|system_date_start|system_date_end|

And a reference table for attributes:

|field_id|name|data_type|target_attribute_table|

It is possible to construct an entity and get all its attributes at any point in time by querying a union of all attribute value tables.

Question

I want to move this data to a data warehouse(Snowflake). I wasn't able to find a whole lot of info on moving bitemporal data to a warehouse and how to model it. I don't understand where would I need a facts and dimensions related modelling in this scenario. Also would it make any sense to pivot this table to get attributes as columns when loading the data in the system?

I'm thinking of moving this data as is to the warehouse.

The main use cases of this data is to get the current snapshot of data, get entire history of some entities and get histories of selected attributes on selected entities and do some analysis on them.

I'm very new to data modelling in a warehouse.

Answer 1

I think you would find it helpful to read a book on Dimensional Modelling - the "bible" is The Data Warehouse Toolkit

To simplify things a lot, you need to do the following:

1. Define your entities (dimensions) e.g. Customer, Product, Country, Employee, etc.
2. Add the appropriate attributes to each entity
3. Add effective start and end dates attributes to each entity (to enable SCD2 logic)

For each record for each entity there will presumably be time periods when none of the attributes changed so you need to insert a record into the entity for each of these static periods. For example, if Customer A started off with a Value attribute of 1, then on 23rd Jan 2020 this was changed to 2 and then on 3rd August 2020 this was changed to 3 (and assuming no other attribute values changed during this time period for this Customer), you would end up with these records in your Customer Dimension:

Hope this helps?