Data Apps Design: “The initial incremental clause looked like this: where true {% if is_i…”

The initial incremental clause looked like this:

    where true

    {% if is_incremental() %}
        and orders.__metadata_timestamp >= 
            (select max(__metadata_timestamp) as high_watermark from {{ this }})
    {% endif %}

So I basically took only rows where metadata timestamp was newer (later) than timestamp of those rows I already have in my table.

Then I realized sometimes metadata timestamp (which is the exact time when the row was fetched from source system) does not increase gradually, some rows get into different batches and land to DWH at different times, so I introduced a lookback period of 3 hours to make sure I don’t miss anything:

    where true

    {% if is_incremental() %}
        and orders.__metadata_timestamp >= 
            (select max(__metadata_timestamp) as high_watermark from {{ this }}) - interval '3 hours'
    {% endif %}

That means I have more assurance I will take all the missing rows, but on the other hand I will process a significant amount of rows multiple times as well as trigger more UPDATEs and DELETEs on my target table since incremental periods overlap.

It was all going OK until rare cases of EXTRACT - LOAD errors / pauses / outages showed that even 3 hours of lookback period sometimes is not sufficient.