Milestone in PWM — similar. I was using only the validFrom/validTill columns.
I think this is all about accountably modifying historical record.
snapshot — For a given observation date like 31/12/1999, “where TxStart < observDate and observDate < TxEnd” would return a snapshot as of 31/12/1999.
A “Tx” or “Transaction” — means a data entry. TxStart/TxEnd marks the observation window or
period of belief. If we observe the price on a day within this window, i.e. take a snapshot on that day, then all the data rows outside this window is filtered out.
Indexing for performance — an index on (TxStart, TxEnd) would speed up such “snapshot” queries.
Data error 1 — If a wrong price was entered but quickly corrected, and never affected any customer, then I doubt bi-temporal is designed for this. Therefore the “never-delete” is not so strict.
Data error 2 — However, if a customer was charged the wrong price, then “never-delete” principle holds. The wrong price was in force at that time, so it is valid history (though not valid by company policy). Valid history is never deleted and must be milestoned.