In this lesson, you will learn why query-based polling is inherently fragile for high-volume data integration and why it cannot detect records that have been deleted from a source database.
The Performance Overhead of Polling
Query-based polling relies on your connector periodically executing a SQL query, such as SELECT * FROM orders WHERE updated_at > :last_poll_timestamp. While simple, this approach forces your database to constantly scan indexes or tables to identify changes. As your data volume grows, the overhead of these frequent queries consumes valuable database CPU and memory, effectively taxing your production system just to check if "anything happened." If the polling interval is too long, your downstream systems lag; if it is too short, you risk overwhelming the source database, creating a bottleneck that impacts your primary application performance.
The "Hard Delete" Blind Spot
The most significant limitation of polling is its inability to capture hard deletes. When a record is deleted from a row in a source database, the row simply ceases to exist. Because your polling query typically looks for new or modified records—often using a WHERE clause on a timestamp column—there is no remaining trace of the deleted record for the connector to find.
Consider this scenario:
-- Initial State: ID 101 exists
-- Polling Query: SELECT * FROM orders WHERE updated_at > '2023-10-01 10:00:00'
-- Action: User deletes row with ID 101
-- Result: The polling query returns no rows, and the delete is never propagated to Kafka.
Because the source row is gone, the connector has no record to fetch, compare, or track. This leads to data drift, where your Kafka topic and downstream sink connectors remain permanently out of sync with the true state of your source system. To solve this, you would need to implement "soft deletes" (a deleted flag column), but that requires modifying application logic and table schemas, which is often not feasible in legacy or third-party environments. Polling is an "additive" tracking method; it is structurally incapable of observing the removal of data.
Key Takeaway: Query-based polling introduces significant resource contention on the source database and is fundamentally incapable of capturing hard deletes because it lacks the ability to detect the absence of data.