Seeking Advice on Efficient Incremental Refresh for Complex Table

Hello Community,

I've thoroughly read the documents for implementating incremental refreshes, and for the most part, my tables meet the criteria, operating seamlessly!

However, I've encountered a challenge with one particular table where an incremental refresh isn't viable. This is because the archived data may change outside the range of the incremental refresh, which understandably leads to duplicate rows.

Is there a strategy to resolve this issue without having to reload the entire table every time? To assist in this, our database development team has added a "last_updated" column, which allows us to identify changes at the row level. I'm wondering how we might use this new column to reload only the rows with a unique key that have been updated, instead of reloading the entire table.

The primary objective is to update this table as rapidly as possible. Direct querying isn't a feasible option due to the extended duration required to update the visuals. This table comprises roughly 100 columns (we're considering reducing this or splitting it into additional tables) and about 2 million rows.

Every hour, between 10,000 and a maximum of 300,000 rows are updated in this table, and it's imperative that we reflect these changes in our reports.

If anyone has suggestions or experiences to share on how we might approach this, I'd greatly appreciate hearing them. Concurrently, we're exploring the possibility of dividing the table based on columns that can or cannot be archived to facilitate the implementation of incremental loading.

Thank you in advance for your time and insights!