I have a general question about loading data into a data warehouse (DW). This is basically a followup to an older question of mine. I have a general understanding problem about fill the [Date] dimension.
I have a IoT base business model basically like this:
- Data (not include in the relational database)
- Device -ValueType
Its currently persisted by a relational database.
For storing mass data I designed a data warehouse model (snowflake) to allow effective storing into a DW.
Looks currently like this:
I read a lot about designing a proper warehouse and settings up an ETL process. I identified all your dimension tables as SCD and use the temporal tables feature of MSSql to solve updates.
Let assume only my fact and [Date] dim table exists. I have problems to understand how to perform proper inserts into the DW. If I understand correctly a default pattern would be using a staging table (on MSSQL, for example, a Polybase table) and then perform batch operations like:
- clean staging table
- fill staging table
- query for not existing dates
- insert not existing dates
- move staging to fact table with references to date dimension
This could be optimized for example with MERGE statements.
- Did I understand that correctly?
- Are there better best-practice strategies like that?
One last hint: Because the DeviceDate is IoT data there will be very unique dates (more timestamps). So I prefill all possible timestamp makes no sense for me.