Is it possible to do incremental models where the source data does not include an update timestamp?

The problem I’m having

The data source from which I’m getting raw data is updated by an outside provider every two weeks and, it appears, drops off data older than 5 years. For example, the update they made Aug 15, 2023 included data from August of 2018 but the update on Sept 1 dropped that August 2018 data.

My goal is to not loose data from my models when the source system drops those records off the published file. So, I started looking at incremental materializations, but the data source does not include any timestamp indicating when a record was created or updated. Is there a way, then, to set up an incremental materialization that doesn’t require a date/time value in the input data?

The other possibility

The other approach I’ve thought of, assuming an incremental model wil not work without a date/time, is to alter the ingestion approach to read out of the source file and write all records to a new table where I append a date stamp. This would result in potentially multiple records for the same record key (station_number, year, month) but I could filter from there into a derived source table by taking just the most recent record. That seems like a long way to go, though, so I’m hoping there’s something easier.*

  • Unfortunately, it appears getting the data provider to do sane things is not an option. :frowning: