I have a database table made up of records that have an ID, payload, and timestamp. These are streamed into the database using only inserts, so there are multiple records with the same ID, but different timestamps. I’d like to provide a view (and models) of this data that’s similar to a database where all the records were upserted, so there is only 1 of each ID, that has the payload of the record with the most recent timestamp.
We’d be able to accomplish this by creating another data source that does use upserts, but as we expect to have many more upserts than we do queries on the data, it seems more economical to collapse the data set down at query time than at insertion time.
Articles I’ve found that cover similar topics below, but only refer to a measure, and don’t provide the user experience I’m looking for. I’d like the fact that the database contains more than 1 record per ID to be completely hidden from the user.
Could anyone point me in the right direction? Please let me know if I can provide any more information.