A message from a device represents a historical event or fact at a particular point in time and typically such a message contains a date/time which identifies when the message was produced and/or the date/time of a specific event to which the message relates. In the Edge Intelligence platform, this message data is regarded as immutable fact data and may be stored in a local table for many months or years.
This message data often needs to be enriched at query time by joining the content of a message with dimension or reference data. For example, a message may contain a status code which can be joined with a table of defined status codes to yield a more meaningful status name. In the Edge Intelligence platform, dimension and reference data is typically stored in global tables - which can be updated as and when definitions require change.
Dimension data is likely to change during the retention period of message data it can be joined with. For example, the definition of a particular status code may vary over time; and it may be important to retain that history of change so that each message can be joined with the status code definition in force at the time the message was actually produced.
Retaining this history of change is known as temporal data and in the Edge Intelligence platform, both global and central tables are treated as temporal and are able to retain a full history of change. Indeed, it is possible to:
- Nominate a future date for a change - to allow change to be managed easily ahead of time.
- Identify specific range of dates for a change - to retrospectively correct any historical inaccuracies or missing data.
Every row in these tables have an effective start and end date column and this can be joined with the date/time of a message to retain a history of change. For example,
SELECT m.timestamp,m.event,c.name FROM messages m JOIN codes c ON c.code=m.code AND m.timestamp BETWEEN c.effective_from$ AND c.effective_to$;
Temporal data is important in situations where message data is kept for a long time.
Another way to think about temporal data is... if global data were not temporal, then every time it was updated it would effectively rewrite history - and we often don't want that.