What should I keep in mind when working with bitemporal systems?
They are not SQL but Datomic[1] and Crux[2] are first class bitemporal databases, look there for design guidelines and commentary.
The transactor design ensures serialization level isolation for writes- but semantically writes are considered "creating facts" and the time at which they are written is the time at which they are "known" and one can ask questions about the state of the world- the collection of facts- that were known as of a particular time. And of course time is also a data type, so one can time based questions on multiple levels.
The model is deeply general- facts are assertions that "this attribute of this entity had this value at this time." One neat capability is that you can "go back in time" and simulate applying transactions that did not occur, supporting counterfactual and hypothetical analytics, and various kinds of testing scenarios.
Attempting to carry through the implications of that model at the application level in SQL is very hard.
Bitemporality afaik means that you get two views on your data, one that describes state relative to individual writers (what you saw at write time in a distributed and potentially inconsistent system) and what is actually true considering global state (what if you had had absolute knowledge of the distributed state at write time).
There is some other project crux that has a lot of python and this pollutes the Google searches.
I think many projects try to re-implement in a ad-hoc way, maybe without knowing it, so if you can start with a bitemporal database it gives a big head start.
As noted above, the SQL:2011 standard supports bitemporality; here's a good summary from SIGMOD Record: https://cs.ulb.ac.be/public/_media/teaching/infoh415/tempfea...
This is a good summary of vendor support for temporal tables: https://www2.cs.arizona.edu/~rts/sql3.html
Oracle and DB2 both support bitemporal tables, SQL Server only supports system-versioned tables. There is an extension for Postgres that supports system-versioned tables, but it hasn't been updated in 2 years: https://github.com/arkhipov/temporal_tables
In the past, I've manually implemented system-versioning for audit purposes in SQL Server using triggers, T4 templates and the CONTEXT_INFO variable to store user IDs. It worked OK, but schema changes became much more difficult.
Also - start thinking about archiving now - n lg n is fast at the start - two years later, not so much.
Good luck
Why would they have few constraints, unless the DB lacks adequate support for the kind of constraints that logically apply with temporal data (now, admittedly, many databases do lack that support, but Postgres via exclusion constraints on ranges doesn't, and the functionality, while supporting bi-(or tri- or quad-, or N-, which once you start analyzing the temporal relations that logically apply to data are much more common than one might think, there's no magic universal model)temporal data, isn't limited to just that.
I've built a lot of temporal tables in SQL, by hand. Time can be a tricky concept, for both users and developers too. Time travelling is even more confusing for people.
Temporal tables were added to the SQL standard in SQL:2011 [1], so a number of SQL databases support it in the DBMS itself. I have not personally used any of these implementations in my own projects yet, and had been using my own implementations for this previously. But, I have been looking into these features over the last few years and considering switching to using something provided by the DBMS itself.
I have seen a number of different implementations/patterns of temporal support:
1) Record effectivity to/from dates, "current flag" to make it easier for people to find the current row. This can be used in normal 3NF relational databases, but a good example of where this is often applied (I see someone else mentioned it) is in denormalized dimensional models - slowly changing dimension (SCD) type 2. That is more for data warehousing, but the point of the temporal tracking columns is the same.
2) On the note above about dimensional models, a most common pattern is OLTP and OLAP, where transactional/OLTP data is append only with no deletes (or only soft deletes) and the historical/temporal tracking is done in a separate purpose built data warehouse/system (OLAP).
3) "Current" table vs. "history" table, so only current records are in one table and history are in another. This pattern can be helpful, if you have strict constraints on the "current" table, but don't want to enforce them on the "history". For example, imagine a column that needed to be unique. You want to ensure that this constraint is enforced by the database, but you can't enforce it in history necessarily where there would be multiple instances of a row. There are other ways around this problem, but something to think about. Filtered indexes (indexes that only apply to certain rows) can help with this problem.
4) "Audit logs" are not temporal solutions. I don't know how many times vendors have said "oh we track history" when in reality, they only track changes to records, and that is not a temporal database.
5) Data "snapshots" are another solution to being able to go back in time. I haven't used such a solution myself to solve a bi-temporal problem, as I don't think it's really intended for that, but there are solutions that exist that do this. Usually this is implemented by the DBMS or storage system, and it involves storing a snapshot of data on disk and then storing deltas/changes - but usually not for extremely long periods of time.
A few other thoughts:
1) Temporal databases are often append-only, this can actually be an advantage - because you never delete, it opens up certain database technologies as a possibility that only allow append (for example, many serialized data storage formats in HDFS are append only). HOWEVER, keep in mind privacy concerns. If you never delete, but have to comply with privacy laws (such as GDPR, right to be forgotten, etc.) you will have to think of how to address that.
2) Storage naturally grows faster with temporal systems since you can maintain many copies. Physical table/collection/dataset partitioning often comes in handy when you need to scale this pattern. You have to pay special attention to partitioning keys. For example, if you partition by a date tied to how fast your data grows, historical data might end up unevenly distributed throughout partitions. This applies if you are talking about a distributed system (e.g. cluster of nodes) or even a single system with a partitioned dataset across multiple disks, arrays, etc.
EDIT: 3) One simple thing people overlook - what to use as a date/time stamp! Temporal databases that natively support this often have sequence number management internally. If you are doing this yourself, make sure to use either GMT dates, or date/time with timezone offset. It will make your life easier in the long run if you have to deal with global time differences, and since such a system usually lasts a long time, it makes sense to use a standard. I would avoid using Unix timestamps or anything that is too implementation dependent (internal sequence numbers, etc.) if you are going to build it yourself.
I generally agree, but this might depend on the use case. Timestamps (as they are provided by the DB or OS) may not be granular enough if you need to record the exact order of changes in a system with a lot of near-simultaneous writes.
I ended up storing both sequence numbers and timestamps in one SQL Server implementation for this reason.
I guess I didn't mean to say that sequence numbers shouldn't be used at all, tables that are bitemporal still have keys and identity values, just to use real dates and preferably GMT for the from/to dates for consistency.
Sequence numbers are especially good for audit log forensics so long as the logs are immutable or protected. Gaps, etc. help with detection of deletions, tampering, etc.
I agree with your comment about use case. Good point!
- changing dimensions - handling changing types e.g. A column of strings becoming a compound of floats - efficiently handling appends - efficiently handling corrections of previous data - how to cleanup, remove old versions
The version store storage engine in Arctic provides fast and efficient versioning for numeric data. The code is quite readable so it might be worth having a look at its implementation too.
The ORM has built-in support for various time-series data concepts including bitemporality.
If you just need to get data at a point in time, you only need to store (so called) application-time. If you need to handle both a “created date” and “effective date” type scenario, then you’ll need bitemporality.
I've come up with a solution specifically for pandas dataframes. Not sure how similar a solution would be for your use case.
What are some good resources?
A spreadsheet is a bitemporal database of sorts right?