Yeah, been fighting variants of this problem as a data engineer where the upstream database would only let you see the current state of the table and did not provide you a changelog / cdc
So you have to
(a) repeatedly query incremental date ranges, with some overlap
(b) deal with late arriving rows
(c) deal with occasional bugs where a bug in a sproc caused the timestamp not to update
(d) query each day for all primary keys and remove rows that had been deleted (but only if you have newer data, otherwise you delete old values but don't have the updates)
(e) Sweat about if this goes wrong on a large table, how can you rapidly determine what state is out of sync? (Idea: how do I "hash" this in a way that hints at where the problem is?)
(f) Deduplicate, as mentioned
Martin Kleppmann's "Designing Data-Intensive Applications" book has some detailed discussions of some of the challenges...
Comments
Yeah, been fighting variants of this problem as a data engineer where the upstream database would only let you see the current state of the table and did not provide you a changelog / cdc
So you have to
(a) repeatedly query incremental date ranges, with some overlap (b) deal with late arriving rows
(c) deal with occasional bugs where a bug in a sproc caused the timestamp not to update
(d) query each day for all primary keys and remove rows that had been deleted (but only if you have newer data, otherwise you delete old values but don't have the updates)
(e) Sweat about if this goes wrong on a large table, how can you rapidly determine what state is out of sync? (Idea: how do I "hash" this in a way that hints at where the problem is?)
(f) Deduplicate, as mentioned
Martin Kleppmann's "Designing Data-Intensive Applications" book has some detailed discussions of some of the challenges...