Y'all, what is the preferred way to replay an incrementally updating table from a given point in time? Some bad data got into a prod table that made its way downstream to Snowflake via Meltano, and I'd like to delete all of the rows for that table from Snowflake and then play things forward from the production table from that point in time to the present w/o having to do a full refresh if I can avoid it (one of the tables in question is rather large.)

@douwe_maan or @aaronsteers can you chime in? I was able to direct Josh to the

job

but manually updating the JSON is suboptimal.

Modifying the

payload

field in the

job

table is the only way I've been able to this so far. If the Meltano guys don't have another suggestion, it is ugly but not hard. You want to set

replication_key_value

for the table within the JSON in the payload field for the most recent, successful run. Make a backup first to ensure that you don't lose all your state if something goes wrong.

Does the tap support a

start_date

setting? I’d suggest setting that and then running

meltano elt

with

--full-refresh

so that it’ll skip the state. The tap will still start at

start_date

instead of going all the way back for a “full” refresh

@josh_wills - When I ran into bad data in the source system, what I found most effective to "heal" the downstream was to ask the DBA-equivalents for the upstream source to bump the

UpdatedOn

timestamp to

now()

for any records affected. That would be your ideal situation, although it takes effort from the upstream team, because your pipeline then auto-heals in its next run. Would that be an option here by chance?

I am the DBA equivalent for the upstream but need to think through the impls of doing that-- but I hear ya, it makes sense

Cool. Another option for long-term, but perhaps late for this specific case, is to use the log-based replication option instead of normal incremental. Because the change tracking is then all automatic and internal, you'd (at least in theory) get the same self-healing behavior without having to update the timestamp column. (Downside is that it probably requires a new full sync to enable this. Also it requires binlogs which may or may not already be turned on.)