https://linen.dev logo
Join Slack
Powered by
<@U04S8PLJWMT> I am getting duplicate records base...
# replication-ask-ai
i
@kapa.ai I am getting duplicate records based on primary key in a full refresh data sync from postgres source to S3 Data Lake connector. How do I trouble shoot and create a bug to report this issue?
k
There is some relevant information in the knowledge sources, but they don’t fully explain duplicates for a Full Refresh from Postgres to S3 Data Lake. I’ll summarize what we do know and how you can narrow it down before filing a bug. ### 1. Understand where duplicates are expected vs not - Airbyte is at-least-once delivery, so duplicates can appear in raw tables/files, especially when syncs are retried or resumed. [[Sync from Salesforce](https://discuss.airbyte.io/t/2310)] - For Full Refresh: - Overwrite: destination is recreated each run; duplicates can appear if a sync is resumed, which is why Overwrite + Deduped is recommended when avoiding duplicates is critical. [[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] - Overwrite + Deduped: final table should be unique per primary key; dedup is done by sorting on the cursor and keeping the latest row. [[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] For S3 Data Lake specifically, deduplication is implemented via Iceberg “merge‑on‑read”: - Primary keys are mapped to Iceberg identifier columns. - Each upsert is an equality‑delete on the PK followed by an insert. [[S3 Data Lake dedup](https://docs.airbyte.com/integrations/destinations/s3-data-lake#deduplication)] There is an open issue where S3 Data Lake deduplication fails on some tables, forcing users to fall back to Full Refresh Overwrite. [[S3 DL issue](https://github.com/airbytehq/airbyte/issues/63359)] ### 2. Concrete troubleshooting steps 1. Confirm the sync mode and where you see duplicates - Are duplicates in: - Raw data files only, or - The final Iceberg table that should be deduped? - Confirm whether you’re using: - 
Full Refresh | Overwrite
- 
Full Refresh | Overwrite + Deduped
[[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] 2. Check for resumed full refreshes - Full refresh syncs can be resumed, and when that happens, duplicates may occur. [[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] - Look for: - Transient failures and automatic retries - Multiple attempts for the same job in the Airbyte UI/logs 3. Validate primary key and cursor assumptions for S3 Data Lake - S3 Data Lake assumes either: - The source never emits the same PK twice in a single sync, or - If it does, records for that PK are emitted in cursor order from oldest to newest. [[S3 Data Lake dedup](https://docs.airbyte.com/integrations/destinations/s3-data-lake#deduplication)] - If Postgres emits out‑of‑order updates for the same PK within one sync, Iceberg may keep an older version, which can look like “duplicate” or incorrect records. 4. Check data quality on the primary key - Confirm in Postgres that: - The PK column(s) are truly unique and non‑null. - The open S3 Data Lake issue shows that even with perfect PKs, dedup can fail on some tables, so if your PKs are clean, that strengthens the case for a bug. [[S3 DL issue](https://github.com/airbytehq/airbyte/issues/63359)] 5. Try a controlled repro - Create a minimal test connection: - One small Postgres table with a simple integer PK. - Same destination (S3 Data Lake) and same sync mode. - Run: 1. One full refresh. 2. Optionally, another full refresh to see if duplicates appear only after retries/resumes. - If duplicates appear in this minimal case, it’s strong evidence of a connector/platform issue. ### 3. How to file a good bug report The knowledge sources don’t give a formal template, but based on the existing S3 Data Lake issue, include at least: - Environment - Airbyte version - Deployment type (Docker, Kubernetes, etc.) - Connectors - Source: Postgres (version) - Destination: S3 Data Lake (exact version) [[S3 DL issue](https://github.com/airbytehq/airbyte/issues/63359)] - Configuration - Sync mode: e.g. 
Full Refresh | Overwrite + Deduped
- Stream name(s) - Primary key fields configured in the connection - Observed behavior - Where duplicates appear (raw files vs final Iceberg table) - Example PK value that appears more than once - Evidence that PKs are unique and non‑null in the source - Whether the job was retried or resumed - Expected behavior - For Full Refresh Overwrite + Deduped, final table should have one row per PK. [[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] - Reproduction steps - Step‑by‑step actions you took (including any reset, re‑runs, etc.) - If possible, a minimal table definition and sample data that reproduces the issue You can then open an issue in the Airbyte GitHub repo similar to the existing S3 Data Lake deduplication issue, referencing that you’re seeing duplicates in a Full Refresh scenario with Postgres → S3 Data Lake and attaching the details above. [[S3 DL issue](https://github.com/airbytehq/airbyte/issues/63359)] If you share the exact sync mode and where you see the duplicates (raw vs final), I can help you tighten the repro description further.
i
@kapa.ai How long might clearing data from a S3 Data Lake Destination take?
k
A message from kapa.ai
The knowledge sources don’t provide any specific or approximate duration for clearing data from an S3 Data Lake destination, and they don’t give S3‑specific performance benchmarks, so I can’t state a concrete time expectation. What the docs do say is: - A Clear operation deletes all records in the destination tables/files for the selected streams; tables are emptied and files (for file-based destinations) are deleted. [[Clear guide](https://docs.airbyte.com/platform/operator-guides/clear)] - Clearing data causes data downtime and blocks scheduled syncs until it completes. [[Clear guide](https://docs.airbyte.com/platform/operator-guides/clear)] - If clearing fails, you may need to manually drop tables/files so Airbyte can continue syncing. [[Clear guide](https://docs.airbyte.com/platform/operator-guides/clear)] There is also a forum report where “Clearing storage area in destination” for an S3 destination took over 4 hours on a long‑lived instance with ~666 tables, while a fresh environment completed the same step in under a minute, indicating that runtime can vary dramatically with environment state and number of streams. [[S3 clear slow forum](https://discuss.airbyte.io/t/3620)] So, based on the available information: - Airbyte does not guarantee a fixed or bounded time for clearing S3 Data Lake data. - Duration depends on factors like number of streams/tables, amount of data, and environment state; in some cases it can be minutes, in others (per the forum report) hours. [[S3 clear slow forum](https://discuss.airbyte.io/t/3620)] If your clears are taking unexpectedly long, that forum thread suggests it may be worth checking for known issues and potentially opening a bug report with logs and environment details. [[S3 clear slow forum](https://discuss.airbyte.io/t/3620)]
i
What happens if a cursor field for Full Refresh - Overwrite + Deduped is the same across multiple records in postgres?
@kapa.ai What happens if a cursor field for Full Refresh - Overwrite + Deduped is the same across multiple records in postgres?
k
A message from kapa.ai
The docs don’t describe this exact edge case for Postgres specifically, but they do explain how Full Refresh – Overwrite + Deduped works and how deduplication is defined. For Full Refresh – Overwrite + Deduped: - Airbyte loads all data from the source, replaces the existing destination table, and then deduplicates so that the final table is unique per primary key. [[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] - Deduplication is done by sorting the data using the cursor field and keeping only the latest de‑duplicated data row for each primary key. [[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] If multiple records share: - The same primary key and - The same cursor value (your case), then, according to the docs, Airbyte still needs to pick one row per primary key in the final table, but the documentation does not specify how ties on the cursor are broken (e.g., deterministic order vs arbitrary). It only states that the final table is deduped by primary key after sorting on the cursor. [[Full refresh deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] So: - You should still end up with one row per primary key in the final table. - When multiple rows have the same PK and same cursor, the docs do not clarify which of those rows will be kept; the behavior in that tie case is not explicitly documented.
i
When would a primary key be duplicated in full refresh - overwrite?
@kapa.ai When would a primary key be duplicated in full refresh - overwrite?
k
A message from kapa.ai
In Full Refresh – Overwrite, Airbyte always re‑reads all records from the source and then replaces the destination table with that full dataset. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] The docs do not say that this mode enforces uniqueness on a primary key; that guarantee only exists for Full Refresh – Overwrite + Deduped, where the final table is explicitly described as “unique per primary key.” [[Full refresh overwrite deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] Based on the documentation: - In Full Refresh – Overwrite: - Airbyte replaces the destination data with whatever the source emits. - If the source itself contains duplicate primary keys, those duplicates will appear in the destination as well, because no deduplication step is described for this mode. [[Full refresh overwrite](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite)] - In Full Refresh – Overwrite + Deduped: - Airbyte replaces the data and then deduplicates so the final table is unique per primary key, by sorting on the cursor and keeping only the latest row. [[Full refresh overwrite deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped)] The docs also note that full refresh syncs may be resumed, and when that happens, duplicates can occur; they specifically recommend using Overwrite + Deduped instead of plain Overwrite if avoiding duplicates is a strong requirement. [[Full refresh overwrite deduped](https://docs.airbyte.com/platform/using-airbyte/core-concepts/sync-modes/full-refresh-overwrite-deduped); [Resumability](https://docs.airbyte.com/platform/understanding-airbyte/resumability)] So, a “primary key” can be duplicated in Full Refresh – Overwrite when: - The source data already has duplicate values for that key, and/or - A resumed full refresh causes records to be delivered more than once, and you are not using the deduped variant.
2 Views
Open in Slack
PreviousNext
Powered by