Generating bitemporal tables from Dagster assets

[0xg0nz0]

Is there any established pattern for updating bitemporal tables in Dagster? I suspect this is best done with a custom IOManager that maybe then delegates to another IOManager for interacting with the database. It's things like:

• generate a unique ID (SEQUENCE / SERIAL) for each unique entry, e.g. for a product table, per product SKU
• populating valid_from, valid_to, sys_from, sys_to standard columns appropriately
  • if this materialization of the asset updates the row's value, set old row valid_to and new row valid_from to same time
  • if this materialization of the asset removes the row, set old row valid_to to now
  • if this materialization is a new key, set valid_from to now and valid_to to 9999-12-31 or similar end-of-time marker

This can also be done with database triggers, outside Dagster; for now am just evaluating options.

https://softwarepatternslexicon.com/103/2/10