GTM Galaxy

Article

The High-Watermark Trap: Why `updated_at` Polling Silently Drops GTM Records

Claudine Baumbach / / Pacific

You notice it during a month-end reconciliation or a lead routing audit. A record exists in your CRM but never made it to your data warehouse or your marketing automation platform. You check the sync logs. Everything is green. The poller ran on schedule. It requested every record updated since the last run. It simply didn't "see" the missing record.

This isn't a fluke or a transient API error. It is a fundamental race condition in how relational databases handle transactions. If you rely on a high-watermark sync—querying where updated_at >= last_sync_time—you are gambling on the speed of your database commits. In high-volume GTM environments, you will eventually lose.

The Mechanics of the Ghost Record

To understand why records vanish, you have to look at how a database like PostgreSQL handles time and visibility. Most operators assume that if a record is updated at 10:00:05, a query running at 10:00:06 will see it. In reality, the database cares about the transaction state, not just the clock.

In PostgreSQL, the now() or CURRENT_TIMESTAMP function returns the start time of the current transaction. It remains fixed until the transaction ends.

Imagine a bulk lead update starts at 10:00:00. This transaction is heavy; it updates 5,000 records and triggers several automation flows. It finally commits at 10:00:10. All 5,000 records now have an updated_at timestamp of 10:00:00 (the transaction start time), but they are not visible to other queries until 10:00:10.

Now, consider your polling sync job running every five seconds:

  1. 10:00:05: The poller runs. It asks: SELECT * FROM leads WHERE updated_at >= 10:00:00.
  2. The Trap: The lead update transaction started at 10:00:00, but it hasn't committed yet. Under Multi-Version Concurrency Control (MVCC), these uncommitted rows are invisible to the poller. The sync finds zero records and updates its internal high-watermark to 10:00:05.
  3. 10:00:10: The lead update transaction finally commits. Those 5,000 records are now visible to the world with their 10:00:00 timestamp.
  4. 10:00:15: The poller runs again. It asks: SELECT * FROM leads WHERE updated_at >= 10:00:05.

Those 5,000 records are skipped. Their timestamp (10:00:00) is earlier than the new high-watermark (10:00:05). They have fallen into the trap and will never be picked up by an incremental sync again.

Salesforce: SystemModstamp vs. LastModifiedDate

In the Salesforce ecosystem, the labels change but the race condition remains. Salesforce provides two primary timestamps: LastModifiedDate and SystemModstamp.

LastModifiedDate is what users see in the UI. It can be backdated during data migrations and is not always indexed, making it a poor choice for replication.

SystemModstamp is a read-only system field. It tracks user edits and automated system updates (like roll-up summaries). Crucially, Salesforce indexes SystemModstamp specifically for performance in integration queries. While more reliable than LastModifiedDate, it is still subject to the same transaction visibility lag. If an API call hits the server before a long-running Apex transaction commits, those records are missed, and your sync pointer moves past them.

Note that SystemModstamp has its own operational overhead. Certain system-wide events—like rotating your Salesforce encryption keys—can trigger a SystemModstamp update across every record in the instance. If your sync logic isn't prepared to handle a massive spike in "updated" records, a routine security update could unexpectedly exhaust your API credits or crash your downstream processing pipeline.

Implementing a Sliding Lookback Window

The most practical fix for most GTM teams is the sliding lookback window. Instead of querying from the exact microsecond the last sync finished, you intentionally overlap your queries.

If your sync runs every minute, you might query for updated_at >= last_sync_time - 5 minutes. This five-minute buffer gives long-running transactions time to commit. Even if a record was written with a timestamp slightly in the past, it will fall within the overlap of the next several sync cycles.

-- The naive approach (vulnerable)
SELECT * FROM leads 
WHERE updated_at > :last_sync_time;

-- The sliding window approach (robust)
SELECT * FROM leads 
WHERE updated_at > (:last_sync_time - INTERVAL '5 minutes');

This approach requires idempotency. Since you are now fetching the same records multiple times, your downstream system must handle duplicates gracefully. In a SQL-based warehouse, this means using INSERT ... ON CONFLICT (id) DO UPDATE (upserting). In a marketing tool, it means the API should recognize the record ID and update the existing entry rather than creating a duplicate.

When to Move Beyond Polling

For low-volume CRMs with minimal concurrent writes, naive polling might only fail once a quarter. But as you scale, the overhead of redundant reads from sliding windows becomes a burden. In those cases, consider two alternatives:

1. Log-Based Change Data Capture (CDC): Instead of querying the table, tools like Debezium or Salesforce CDC stream changes directly from the database's write-ahead log. These logs record changes in the exact order they are committed, bypassing the timestamp race condition entirely.

2. Monotonic Sequence Tracking: Some databases use an auto-incrementing Sequence ID or a Log Sequence Number (LSN) that is assigned at the moment of commit. If you track last_seen_id instead of last_sync_time, you eliminate the clock-based race condition. However, this requires your database schema to support a strictly increasing, gapless sequence—something not always available in SaaS APIs.

Verdict

If you are building custom GTM syncs or managing data pipelines, stop treating updated_at as an absolute marker of truth. The database isn't lying about the time; it is simply showing you a snapshot of a world that hadn't finished committing yet.

Implement a sliding window today. It’s a cheap architectural insurance policy against the "missing lead" tickets that plague RevOps teams.

— C.B.