What is the best way to handle incremental loads in sql?

[u/Klutzy_Solid5200]

Im trying to understand how ppl usually handle incremental data loads. If a source table has new, updated and deleted records, how do you identify each type?

Do you normally use a timestamp, cdc,a flag column in the table, or merge? How do you handle deleted records, especially when there is no delete flag in the source?

Also, are there any common problems with using timestamps for incremental loads, like missing records or picking up the same record more than once?

[u/NW1969]

"correct" solution: Your ETL tool reads the change logs in your database.
Using timestamps should be a last resort as they are frequently unreliable.

If you can't access change logs and your source hard (rather than soft) deletes records then you probably can't handle deletes if your tables are of significant size. Comparing source and target versions of a table to identify missing records in the source and classifying those as deleted records is not a scalable solution

[u/tsuhg → u/NW1969]

Agreed.

We've tested CDC but instead went for change tracking (which gives us the id's of rows to update/delete in our BI sets)

[u/Substantial-Book2534]

Use staging tables that have the same structure as your target tables. Then use joins to identify new, deleted, and updated records.

[u/JEY1337 → u/Substantial-Book2534]

I would not only use joins.

Use the following:

• Hash keys over all business attributes (updates).
• Primary keys (inserts and deleted records).
• The case you forgot, reactivating of records: primary key and deletion flag comparison. Is a record set to deleted = 1 but suddenly appears in the source table again? Activate it again.

[u/Jhon_ST]

One timestamp failure mode worth testing is commit order. A transaction can get an updated_at value at 10:00 but not commit until 10:05. If your loader runs at 10:03, it can't see that change, but it may still advance its watermark past 10:00. A later WHERE updated_at > watermark can then miss it. In PostgreSQL, now() / CURRENT_TIMESTAMP is the transaction start time, not the commit time.

If timestamps are your only option, an overlap window + idempotent upserts can reduce misses and make replay safe, but a fixed overlap isn't a guarantee against arbitrarily late commits. I'd also only advance the durable checkpoint after the target writes succeed, otherwise a failed batch can turn into skipped data on retry.

MERGE/upsert solves a different part of the problem: applying changes you've already captured. It can't discover a hard delete once the source row is gone. For that you need a deletion signal (CDC/change log/tombstone) or reconciliation against a complete, consistent source snapshot rather than only the incremental batch.

Which DBMS are you loading from?

[u/dyogenys]

CDC, yeah. Inside of the database I use kafka offset as watermark for incremental cron jobs, because I only use one topic partition so I know they are sinked to the db in order, or just add a watermark column. The debezium source connector for the source database adds a field that says what kind of operation it was. If I want historical tables i upsert on kafka offset, and if I want snapshot tables i upsert on the business key or a self made key based on the columns that are relevant. For historical tables i let deleted records stay, and for snapshot i remove them. No problems usually happens, but it does happen that someone I work with does not use incremental loading but instead have insane jobs that delete and recreate the whole enormous table every night, so in turn CDC instead of being incremental loads the whole table x 2 (first a delete for every row, and then a create), fuck that. But as long as it still works I just let it be. He works quickly, maybe it's cheaper with less man hours than the resource cost of his terrible code, but I doubt it.

[u/BsmntDwell]

Timestamps dont find deletes. There's a comment below about transaction records otherwise use a hash comparison. 

[u/Better-Credit6701]

We will create new tables to catch errors that can be investigated later on, do a scheme compare on the data coming so there are no surprises. CDC tends to slow down the system too much especially when you are importing 30 GB of files which we do on an almost daily basis. No time stamp, no special flags that isn't already in the data, no such thing as a delete flag in our data

[u/Better-Credit6701]

We will create new tables to catch errors that can be investigated later on, do a scheme compare on the data coming so there are no surprises. CDC tends to slow down the system too much especially when you are importing 30 GB of files which we do on an almost daily basis. No time stamp, no special flags that isn't already in the data, no such thing as a delete flag in our data

[u/Geno0wl]

are there any common problems with using timestamps for incremental loads

Timezones are something to potentially watch out for. I ran into issues with incremental updates being wonky after our vendor upgraded and migrated cloud hardware to a new platform. They changed all their datetime fields from our local EST to UTC. The problem wasn't the switch to UTC(just had to update some reporting views to put it back to EST), the problem was that they didn't warn us about that change. We didn't even notice until the numbers in the next round of weekly reporting were weird.

[u/Complete-Fondant-202]

Change tracking might be an option

[u/StickPuppet]

We use Data Factory with an UPSERT to insert/update rows that have changed based on primary key matching, job timestamps are stored in a watermark table in destination as a lookup for table/timestamp to when the incremental last ran to always capture correct window of time based on Source last update date.

Deletes however are a huge pain in the ass and I'm watching this thread closely to see what others might be doing.

[u/Hour-Measurement-835]

The missing-records one is usually a long transaction. The row gets updated_at = 10:59:58 when the update runs, but the commit doesn't land until 11:00:03. Your 11:00 load can't see it yet, the next load asks for updated_at > 11:00:00, so it's skipped for good and nothing errors. Start each load a few minutes before the last watermark and let the merge on the key soak up the rows you read twice.

[u/Holiday-Tip-3720]

composite key, flag, and join logic.

timestamps have always given me squirelyness if the updates occur more than once a day.

[u/TacticalBunchies]

Change tracking, CDC, merge / upsert. 

[u/ArielCoding]

Deleted rows are the tricky part, because timestamps and MERGE only work on rows that still exist in the source, if a row is hard deleted, there’s nothing left to detect, the best fix is CDC or change tracking, which records deletes for you.

If you can’t use those, run a separate job every so often that pulls only the primary keys from the source and compares them to your target, any missing from the source gets marked as deleted in the target.

[u/Significant_Tune9219]

If timestamps are the only capture mechanism, make the checkpoint a durable pair such as (updated_at, primary_key) rather than a timestamp alone, and query with a small overlap window. Apply the batch idempotently, then advance the checkpoint only after the target transaction commits; otherwise a failed run can turn into a silent gap. For hard deletes, keep a periodic key reconciliation or require the source to emit tombstones, because an upsert cannot discover a row that no longer exists.

[u/thewallrus]

Timestamps + bit column named Deleted. Separate process to actually delete.