Implementing Polling Based Change Detection

In this lesson, you will learn how to build a mechanism that incrementally pulls new data from a source database by tracking specific markers like timestamps or primary keys.

The Logic of Polling

When you don't have access to native database transaction logs (as we discussed in our previous CDC lessons), you must implement Polling Based Change Detection. This is a "pull" strategy where your application periodically queries the source database to check for changes that have occurred since the last successful sync.

Defining Your Markers

To effectively capture changes without duplicating data or missing updates, you need a stable, monotonically increasing marker in your source table:

1. Timestamp-based: You track the updated_at column. In your polling loop, you query: SELECT * FROM table WHERE updated_at > :last_sync_timestamp.
2. ID-based (High-Water Mark): You track the maximum id (primary key). In your polling loop, you query: SELECT * FROM table WHERE id > :last_synced_id ORDER BY id ASC.

The Implementation Flow

A typical polling cycle looks like this:

-- 1. Fetch data since the last checkpoint
SELECT * FROM orders 
WHERE created_at > '2023-10-27 10:00:00' 
ORDER BY created_at ASC;

-- 2. Process records and push to your stream
-- 3. Update your local "high-water mark" to the latest timestamp found

Critical Considerations

• The "Gap" Problem: If your query range is too narrow, you might miss records being inserted at the exact millisecond of the query. Always use an inclusive range or a slight overlap to ensure consistency.
• Deletes: This is the primary limitation of polling. If a record is physically removed from the database, a standard timestamp-based query won't register that the data is gone. To track deletions, you must implement "soft deletes" (e.g., adding an is_deleted flag column) so the poller can still see the update.
• Load Impact: Because polling requires constant querying, set your polling interval carefully. Too frequent, and you overwhelm the source database; too infrequent, and your streaming data loses its near-real-time benefit.

Key Takeaway: Polling based change detection uses a high-water mark or timestamp to pull only the newest records, providing a reliable—though limited—alternative to log-based CDC.