Watermark-Based Incremental Sync for SaaS Application Databases

A SaaS application database that starts at a few gigabytes does not stay there. As tables grow into the tens or hundreds of millions of rows, pulling the entire dataset on every sync cycle stops being a reasonable option. A full reload re-extracts every row every time, whether that row changed an hour ago or has sat untouched for three years. That approach eats bandwidth, puts repeated load on a source database that is usually also serving live application traffic, and stretches sync intervals out to the point where the data arriving downstream is already stale by the time anyone looks at it. Incremental sync fixes this by moving only the delta, the rows that changed since the last run, letting a pipeline run every few minutes and touch a small slice of the table. Watermark-based sync is the most common way to do this: the pipeline remembers the highest timestamp or sequence number it has already processed, and on the next pass it asks the source for only the rows that moved past that point. It earns its popularity for three plain reasons: it's simple to build when the source table already has a trustworthy "last changed" column, it adds very little strain to the source database compared to scanning everything, and the watermark value itself gives operators a plain number they can check at any time to see how far the pipeline has caught up.

How the watermark mechanism works, step by step

The pattern, stripped down, runs in three repeating steps: change detection, selective transfer, and watermark advancement. First comes change detection: instead of scanning the whole table, the pipeline runs a query with a filter on the watermark column, something close to WHERE last_updated >:stored_watermark, so the database itself does the work of narrowing down which rows qualify. Second is selective transfer: only the rows that pass that filter get pulled out, reshaped into whatever format the destination expects, and written there. Third is watermark advancement: once the batch finishes, the pipeline updates its stored cursor to match the highest watermark value it saw in that batch, and that new value becomes the floor for the next run's query.

That third step is where most of the real engineering risk sits. The watermark has to live somewhere durable, not just in a running process's memory. If a pipeline advances its watermark in memory, crashes before writing it down, and restarts from an older saved value, that's a recoverable situation: the next run simply re-reads a bit of extra data. The dangerous failure runs the other way. If the watermark gets recorded as advanced before the corresponding rows are confirmed to have landed at the destination, a crash in between can leave the pipeline believing it processed data it never actually delivered, and that window of changes is gone for good, because the next run's query starts above it. The fix is procedural: the watermark only advances after the destination has acknowledged the write, or as part of the same transaction that commits the rows, never before.

Choosing the right watermark column for the source schema

With the mechanics in place, the question becomes which column the whole system should trust, and that question turns out to be harder than it looks. The ideal watermark column only ever goes up, updates automatically on every insert and update without the application needing to remember to set it, and never gets backfilled or hand-edited by a support script at two in the morning. Few production schemas offer that out of the box, so real pipelines work with whatever is actually there.

The most familiar option is a datetime column, something like LastUpdatedDate or ModifiedAt. These are easy to reason about and widely supported, but they depend entirely on application code remembering to set them correctly every time a row changes, and application code does not always cooperate: a batch update, a direct database edit, or a buggy code path can leave the timestamp untouched even though the row changed. A sturdier option, where the database supports it, is a row-version column, an integer the database engine itself increments on every modification independent of application logic. That independence from app-layer discipline is why Microsoft Fabric Data Factory's Copy job treats ROWVERSION as a first-class watermark type: its monotonic nature makes it safe to track progress across runs without worrying about an engineer forgetting to update a timestamp field. Some source systems complicate things further by storing timestamps as strings rather than native datetime values, which historically forced engineers to write custom casting logic into every extraction query; Fabric Data Factory's Copy job now supports string-encoded watermark columns that can be interpreted as datetime directly, with no schema changes needed on the source side. Other schemas only track changes at the day level rather than the second, and Fabric Data Factory added native Date watermark support for exactly that case, a sign of how common coarse-grained date columns actually are in production systems, not a reason to prefer one tool over another.

Where no column in the source schema reliably reflects change, teams either add a trigger-maintained column themselves, switch to log-based change data capture, or fall back to periodically reconciling the full set of keys, each of which trades simplicity for cost in a different place. One failure mode deserves its own warning regardless of which column type a team picks: a source system running on local time rather than UTC can produce timestamps that appear to move backward across a daylight-saving transition, and a pipeline that isn't watching for this will silently skip records that fall into that reversed window. The fix is to normalize every change timestamp to UTC the moment it enters the pipeline, before any comparison against the stored watermark happens.

Boundary Inclusivity and Overlap Windows

Even with a solid watermark column chosen, the query logic itself hides a trap. The natural instinct is to query strictly greater than the stored watermark, on the theory that anything already seen should not be seen again. Picture two records written at the exact same millisecond as the previous run's watermark value. A strict > predicate on the next run excludes both of them, because neither row's timestamp is greater than the stored value, only equal to it. No error gets thrown, nothing in the pipeline's logs flags a problem, and the destination simply never receives those two rows. They vanish from the sync permanently.

The fix is the overlap window. Rather than querying from the exact stored watermark, the pipeline subtracts a small buffer from it and re-scans that buffer zone on every run, deliberately re-reading some records it already processed. That re-reading is harmless as long as the destination write is an upsert keyed on the record's natural identifier, since writing the same row twice just overwrites it with itself. The size of that buffer should be set based on the worst clock skew or write latency actually observed in the source system: too small and late-arriving writes still slip through; too large and the pipeline pays for redundant query volume without gaining any additional correctness. This same overlap logic also rescues back-dated or late-arriving edits, records whose timestamp technically falls below the current watermark even though they were only just written, which would otherwise be invisible to the pipeline forever. Fabric Data Factory's Copy job applies this kind of delayed extraction automatically for Date-type watermark columns specifically to prevent data loss or overlap between runs, which shows this isn't a theoretical edge case but a concern serious enough to build into the platform layer itself. In any context where auditors or compliance teams will eventually ask how current the synced data is, the watermark's value and history should be logged over time, since it is the literal record of how far the pipeline has reconciled against the source at any given moment.

What watermark sync cannot see: the delete problem

No amount of careful column selection or overlap-window tuning solves the one thing watermark sync structurally cannot do: see a deleted row. When a row is deleted from the source, it does not pick up a new watermark value and simply disappears. The pipeline's next query simply finds nothing where that row used to be, and nothing in the watermark mechanism tells it a deletion happened. This isn't a bug to fix or a configuration someone got wrong. It follows directly from how the pattern works: the only thing a watermark query can ever return is rows that still exist and carry a watermark value above the stored cursor, and a deleted row satisfies neither condition.

Left unaddressed, this produces a destination that slowly accumulates rows the source no longer has, with the two systems drifting further apart the longer the pipeline runs. Any report, dashboard, or downstream application logic that assumes the destination mirrors the source becomes quietly wrong, often with no obvious signal that anything is off. Three mitigations address this, each with its own cost. Soft deletes have the source application mark a row with a deleted_at timestamp or an is_deleted flag instead of actually removing it, which turns a delete into an update the watermark mechanism can see, but this only works if the application is built to honor that discipline consistently. Log-based change data capture reads the database's transaction log directly, which records every insert, update, and delete as it happens and so captures deletions natively, but it requires access to that log, something hosted SaaS databases rarely grant to outside pipelines. Periodic full-key reconciliation sidesteps both requirements by comparing the complete set of primary keys in the source against the destination at intervals and removing anything that no longer exists upstream; it costs more to run but works even when nothing about the source can be changed.

When Watermarking Is the Right Call

Watermark-based sync is the right default for most pipelines moving SaaS application data into a warehouse, and it only gives ground to change data capture when delete accuracy or near-real-time latency cannot be compromised on. The case for watermarking is strongest when the source schema already has a column that updates reliably and automatically, whether that's a datetime field, a ROWVERSION integer, or a string-encoded timestamp. It holds up well when deletions in the source are rare, handled through a soft-delete convention, or cleaned up through periodic reconciliation. It's a good fit when the downstream use case tolerates sync intervals measured in minutes or hours rather than requiring sub-minute freshness, and when a team values a pattern whose progress can be checked at a glance, since the watermark value itself is always inspectable. It's also often the only workable option when the source is a hosted SaaS database or third-party API that does not expose a transaction log for CDC to read.

Change data capture becomes the better choice when deletions absolutely must propagate downstream and soft-delete conventions aren't realistic to enforce, when the business genuinely needs data to land within seconds rather than minutes, and when the team controls the source database closely enough to access its transaction log. Many mature data platforms don't treat this as an either-or decision: they run watermark sync across the bulk of their tables for inserts and updates, and reserve CDC for the specific tables where delete fidelity or latency requirements actually demand it. For SaaS product teams building warehouse sync as a feature their own customers configure, rather than as an internal data pipeline, the properties that make watermarking attractive carry extra weight: it can be stood up without asking a customer to modify their database configuration or grant access to a transaction log, which matters enormously when the "customer" is someone else's production system that a vendor has no authority to reconfigure. Pricing models that charge based on the number of connected destinations rather than the volume of rows moved also pair naturally with incremental sync's efficiency, since moving only the delta means the cost of keeping a destination current doesn't balloon just because the underlying dataset grew.

Making watermark sync reliable in production: the engineering checklist

A watermark sync that passes every test in a staging environment can still fail once it meets real production traffic, and the gap between the two almost always traces back to a short list of missing controls. Idempotent writes at the destination matter because the overlap window deliberately re-reads records the previous run already processed; an upsert keyed on the record's natural identifier turns that overlap into harmless re-confirmation, while a plain insert turns it into duplicate rows. UTC normalization at the moment data enters the pipeline matters because a source system running on local time can produce timestamps that appear to move backward across a daylight-saving transition, and any record caught in that reversal falls below the stored watermark and gets silently skipped with no error to flag it. Observability matters because the watermark's current value, the time of the last successful run, and the row count ingested in that run should all be logged and visible to whoever operates the pipeline. That visibility is one of the pattern's genuine strengths, since a plain number showing how far the sync has progressed is easy to check at a glance, and a production deployment that doesn't expose it has thrown away exactly the advantage that made watermarking worth choosing. Schema evolution needs handling too: source schemas change over time as columns get added or renamed, and a pipeline needs to either detect those changes and propagate them to the destination automatically or alert an operator before the mismatch causes data to silently stop flowing. Finally, even a well-tuned watermark pipeline should run a periodic full-key reconciliation job on a schedule that matches how often deletions actually happen in the source, since nothing in the watermark mechanism itself will ever catch a deleted row on its own. For SaaS product teams shipping sync as a feature embedded in their own product, these controls stop being pipeline-specific concerns and become platform requirements: the integration layer itself needs to guarantee durability, idempotent writes, schema-change handling, and observable run state, so that no individual product team building on top of it has to solve these same problems again for every new customer destination.