An overview of bitemporality

In this talk we'll quickly look through, first unitemporal and then bitemporal database versioning and auditing mechanism that allows active querying of the audit logs.

Transcript

1. Guns don't kill people -- a lack of audit logs kills people A presentation with a click-bait title about temporality by Akos Krivachy
2. A story about Bob ☻/ This is bob. Copy and paste him so he can take over YouTube. /▌ /\
3. Bob is a secret agent from YouTube and gets paid after every 1M views he’s a top comment
4. Bob tracks his money from YouTube MONEY table: balance 1000
5. Bob tracks his money from YouTube MONEY table: balance 2000 A destructive edit
6. Bob doesn’t know when he was paid and how much
7. Let’s improve this id balance valid_from valid_to 1 1000 01/01 01/02 2 2000 01/02 01/03 3 3000 01/03 ∞ Unitemporal Our latest “open” record The valid timeline can be thought as the “business” timeline as in what happened in the real world
8. Bob has a boss: Stewie Let’s see a report query to him
9. Report emailed to Stewie from time-to-time id balance valid_from valid_to 1 1000 01/01 01/02 2 2000 01/02 01/03 3 3000 01/03 ∞ SELECT * FROM money WHERE ‘01/02’ BETWEEN valid_from AND valid_to; To query this row we filter on the valid timeline
10. Great! Are we done?
11. Uh,oh YouTube retroactively deployed a new fake view count filter
12. With fake views id balance valid_from valid_to 1 1000 01/01 01/02 2 2000 01/02 01/03 3 3000 01/03 ∞
13. Without fake views id balance valid_from valid_to 1 500 01/01 01/02 2 600 01/02 01/03 3 700 01/03 ∞ With only a valid timeline we are forced to overwrite the balances: a destructive edit
14. Reports are still sent to Stewie id balance valid_from valid_to 1 500 01/01 01/02 2 600 01/02 01/03 3 700 01/03 01/04 4 800 01/04 ∞ Bob sends off the next numbers to Stewie, he only reviews them later
15. Stewie’s view of the world
16. Stewie’s view of the world
18. At this point, due to the lack of audit logs, we can’t answer Stewie’s question with data, only memories :)
20. BITEMPORALITY
21. Let’s go back and fix this id balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 ∞ 2 2000 01/02 01/03 01/02 ∞ 3 3000 01/03 ∞ 01/03 ∞ VALID TIMELINE == TRANSACT TIMELINE in “normal” cases Let’s add a second timeline, the transact or “system” timeline. This is what the IT systems see of the world. On inserts they always use NOW() for transact_from on closing out records they always use NOW() for transact_to.
22. Queries now need to filter on valid AND transact id balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 ∞ 2 2000 01/02 01/03 01/02 ∞ 3 3000 01/03 ∞ 01/03 ∞ SELECT * FROM money WHERE ‘01/02’ BETWEEN valid_from AND valid_to AND ‘01/02’ BETWEEN transact_from AND transact_to;
23. Amendments to the past (happening on 01/04) id balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞
24. Close out records: essentially delete id balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞ First we close out records by setting their transact_from to NOW()
25. Insert new records id balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞ Insert new records with the amended data. These will be considered the open records “as of” 01/04
26. Use old valid time id balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞ Very important that we use the old valid times, therefore looking like an ammendment.
27. Amendments to the past (happening on 01/04) id balance valid_from valid_to transact_from transact_to 1 1000 01/01 01/02 01/01 01/04 2 2000 01/02 01/03 01/02 01/04 3 3000 01/03 ∞ 01/03 01/04 4 500 01/01 01/02 01/04 ∞ 5 600 01/02 01/03 01/04 ∞ 6 700 01/03 ∞ 01/04 ∞
29. Graphing a unitemporal view 1000 2000 3000 _ 01 02 03 04 valid
30. 01 02 03 04 01 02 03 04 1000 2000 3000 valid transact New records are at this point
31. 01 02 03 04 01 02 03 04 3000 valid transact 1000 2000 <= valid is 2.5 & transact is 2.5 <= valid is 2.5 & transact is 1.5 Querying for data with different transact constraints
32. 01 02 03 04 01 02 03 04 1000 2000 valid transact 3000
33. 01 02 03 04 01 02 03 04 1000 2000 3000 valid transact 500 600 700 Amendments can be visualized like this
34. 01 02 03 04 01 02 03 04 1000 2000 3000 valid transact 500 600 700 Queries with different transact constraints: we can actively query for the “old” data and the “new” data.
35. Bob survived! ☻/ /▌ /\
36. Morale of the story: Make sure you know that the data you destroy will never be needed again. If it needs to be actively queried consider temporality.
37. Some specifics This was just an intro to temporality to understand the concept. Lots of databases support temporal columns/auditing out of the box: MSSQL, Oracle, DB2, Teradata, Postgres SQL:2011 standard has defined features for temporal support There are other application level solutions as well: Hibernate Envers, event sourcing, CQRS, etc. Home-rolling bitemporality can become complicated and should be considered carefully.
38. Thanks! Questions? Get in touch: @akoskrivachy