incremental models sql_where value changes between delete and insert statements

[jthandy]

I have a model that specifies the following for an incremental model:

  sql_where = "rev_rec_date >= (select max(rev_rec_date) from {{this}})",
  unique_key = "account_id || '.' || date_month"

In theory, this is exactly how this is supposed to work. In practice, this fails. The delete statement runs, deletes the records, and then when the insert statement runs, it picks up a different value for max(rev_rec_date), which causes it to get a bunch more records.

It's deleting 40 records and then inserting 143 records, but it should actually be deleting 40 and inserting 40 (because they are daily records and I've already run it for today). The extra 103 records are actually duplicated records from the previous day, because they match the new value for max(rev_rec_date).

I can demonstrate this live if needed.

I think the only solution for this is to create a temporary table with the values and then run both the delete and the insert off of that temporary table. We've discussed this before, but I think this bug moves that from a nice enhancement to something we need ASAP to create correct behavior.