https://linen.dev logo
Join Slack
Powered by
<@U04S8PLJWMT>, why am i seeing double records whe...
# replication-ask-ai
a
@kapa.ai, why am i seeing double records when the data is syncing from cdc enablement from sql server to clickhouse.
k
I'm sorry, but the knowledge sources do not provide a single definitive reason for seeing duplicate records in this specific scenario, but they do point to several architectural and operational factors that can cause this behavior. Here are the potential reasons based on the provided documents: ### 1. Understanding CDC Capture Behavior In CDC (Change Data Capture) mode, Airbyte captures all intermediate states of a record. If a record is updated multiple times between syncs, each update is recorded as a new entry in the destination. * Multiple operations per transaction: Specifically for SQL Server, a single update can generate two events: one for the state before the update (operation 3) and one for the state after the update (operation 4) [[Duplicated registries mssql CDC](https://discuss.airbyte.io/t/3752)]. * Historical tracking: Unlike standard incremental syncs, CDC is designed to capture every change, meaning the destination raw tables will naturally contain more rows than the source if updates have occurred [[Explore CDC replication](https://airbyte.com/tutorials/incremental-change-data-capture-cdc-replication)]. ### 2. ClickHouse Sync Mode and Engine Settings If you are seeing duplicates in your final tables, it may be related to how the ClickHouse destination handles deduplication: * Sync Mode: Ensure you are using Incremental - Append + Deduped. The legacy v1 ClickHouse connector did not support deduplication [[ClickHouse [ARCHIVED]](https://docs.airbyte.com/integrations/destinations/clickhouse-deprecated)]. * ReplacingMergeTree: The modern ClickHouse connector (v2) uses the 
ReplacingMergeTree
engine. This engine performs deduplication in the background during merges, but it does not guarantee immediate deduplication. To see a deduplicated view at query time, you must add the 
FINAL
operator to your SQL query:
Copy code
sql     SELECT * FROM your_table FINAL
[[ClickHouse Destination](https://docs.airbyte.com/integrations/destinations/clickhouse)]. ### 3. Potential Bugs or State Issues * State Reconciliation Bug: There is a known open issue where the "global CDC state" may not be marked as complete even after data is written. This can lead to the source thinking it needs to re-sync records it already processed, resulting in duplicates [[Failing With IllegalStateException](https://github.com/airbytehq/airbyte/issues/69746)]. * Last Record Overlap: Some users have reported that incremental syncs sometimes pull the last record from the previous sync again because they use the 
updated_at
timestamp (or equivalent) as a cursor, leading to overlaps in the 
_AIRBYTE_RAW
tables [[Incremental Syncing Issues](https://discuss.airbyte.io/t/504)]. ### 4. Primary Key Configuration If you are using the Connector Builder or custom settings, deduplication downstream relies on a correctly defined Primary Key. If no primary key is specified or if it is incorrectly defined, Airbyte cannot identify which records are duplicates of each other for the "Append + Deduped" mode [[Record processing meta data](https://docs.airbyte.com/platform/connector-development/connector-builder-ui/record-processing#meta-data)].
a
@kapa.ai i inserted 10k records in sql server and 10k are in clickhouse but the airbyte ui says 20k
k
A message from kapa.ai
The knowledge sources do not explicitly mention a specific bug causing a 10k vs. 20k discrepancy between SQL Server and ClickHouse, but they provide several reasons why the Airbyte UI displays a higher record count than what you see in your final destination table. ### 1. UI Count vs. Destination Count The record count displayed in the Airbyte UI represents the total number of JSON records emitted from the source and processed by the platform. This number is often higher than the final row count in your destination for the following reasons: * JSON Overhead: The size and count in the UI are calculated from the raw JSON records before they are normalized or deduplicated in the destination [[Every sync double the raw table sizes](https://discuss.airbyte.io/t/1579)]. * Intermediate States: In CDC mode, Airbyte captures every change. If your 10k records involved updates or were part of a complex transaction, the source may emit multiple records for a single "final" row state [[Explore CDC replication](https://airbyte.com/tutorials/incremental-change-data-capture-cdc-replication)]. ### 2. Deduplication in ClickHouse If you are using the Incremental - Append + Deduped sync mode, Airbyte will sync all versions of a record to the destination, but the destination is responsible for showing only the most recent version: * ReplacingMergeTree: The ClickHouse destination uses the 
ReplacingMergeTree
engine. This engine keeps multiple versions of a record and only removes duplicates during background merges. * Querying the Correct Count: To see the count of unique records that matches the source, you must use the 
FINAL
modifier in ClickHouse:
Copy code
sql     SELECT count(*) FROM your_table_name FINAL;
Without 
FINAL
, ClickHouse will return the count of all records ever emitted (which likely matches the 20k shown in the Airbyte UI) [[ClickHouse Destination](https://docs.airbyte.com/integrations/destinations/clickhouse)]. ### 3. SQL Server CDC Specifics For SQL Server CDC, an 
UPDATE
operation can generate two distinct records: one representing the data before the update (operation 3) and one after the update (operation 4). Both are emitted by the source and counted in the Airbyte UI, even if they ultimately result in a single updated row in your destination [[Duplicated registries mssql CDC](https://discuss.airbyte.io/t/3752)]. ### 4. Known State Issues There is a documented issue where "duplicate data processing" can occur if the global CDC state is not correctly marked as complete. This causes the UI to report more rows inserted than expected because it attempts to re-process rows for which it "lost" its cursor state [[Failing With IllegalStateException](https://github.com/airbytehq/airbyte/issues/69746)].
6 Views
Open in Slack
PreviousNext
Powered by