← Back to context

Comment by necubi

4 months ago

It's not particularly rare in analytical databases/query engines, nearly all of which support AsOf joins these days, including

  * Clickhouse
  * DuckDB
  * BigQuery
  * Snowflake
  * QuestDB

2 comments

necubi

Reply
refset  4 months ago

AsOf join in those systems solves a rather narrow problem of performance and SQL expressiveness for data with overlapping user-defined timestamps. The bitemporal model solves much broader issues of versioning and consistent reporting whilst also reducing the need for many user-defined timestamp columns.

In a bitemporal database, every regular looking join over the current state of the world is secretly an AsOf join (across two dimensions of time), without constantly having to think about it when writing queries or extending the schema.