@kapa.ai When doing full refresh syncs, does the update method in a PostgreSQL connector have any effect?

Full Refresh Syncs and the Update Method in PostgreSQL Yes, there are some relevant behaviors to be aware of, depending on whether you're looking at the source or destination Postgres connector. Source Postgres: CTID-Based Scanning For the source-postgres connector, full refresh syncs use a CTID-based scan (scanning physical page ranges in batches). A known issue exists where rows updated by concurrent transactions during a multi-batch scan can be silently dropped. [GitHub Issue] Here's why: • Each batch opens a new MVCC snapshot. • When a row is updated mid-scan, PostgreSQL marks the old physical location as dead and writes the new version to a different location. • The new version may land in a page range already scanned by a previous batch (under an older snapshot), making it invisible to all batches. This means a row that was updated while the scan was running could be missed entirely — with no error or warning logged. [GitHub Issue] This is especially problematic with Full Refresh | Overwrite, because each sync replaces the entire destination table. Unlike Append mode, there's no accumulation of previous syncs to "self-heal" missed rows. [GitHub Issue] Destination Postgres: Full Refresh Overwrite Behavior On the destination side, Full Refresh | Overwrite replaces all data in the destination table on every sync — not just updated rows. Every record gets a new

_airbyte_emitted_at

and

_airbyte_normalized_at

timestamp, regardless of whether it changed in the source. [Full Refresh Tutorial] There is also an open bug where, if the destination table is shared by more than one connection, Full Refresh | Overwrite may append rows instead of replacing them (a regression introduced with the Direct Load feature in destination-postgres v3.0.11). [GitHub Issue] Recommendations • For write-heavy source tables, consider switching to Incremental | Append + Deduped with a cursor column (e.g.,

updated_at

) to avoid the CTID race condition. [GitHub Issue] • If deduplication is important and syncs may be interrupted/resumed, prefer Full Refresh | Overwrite + Deduped over plain Overwrite. [Overwrite + Deduped Docs] • Ensure each destination table is used by only one connection to avoid the append-instead-of-overwrite bug. [GitHub Issue] 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai if the destination is Snowflake, does that have any impact?

Impact on Snowflake Destination with Full Refresh | Overwrite Yes, there is a known bug specific to the destination-snowflake connector with Full Refresh | Overwrite. The Generation ID Bug In destination-snowflake 4.0.43 (direct-load architecture),

full_refresh | overwrite

streams can append data forever instead of overwriting, under certain conditions. [GitHub Issue] The root cause: the connector decides whether to truncate or append by sampling one arbitrary row from the existing table:

SELECT "_AIRBYTE_GENERATION_ID" FROM <table> LIMIT 1

If that sampled row has a

_airbyte_generation_id >= minimumGenerationId

(the current sync's generation), the connector takes the append path instead of the truncate/swap path. This misfires permanently when the table contains stale rows with higher generation IDs than the current stream's counter — for example, after re-pointing a connection to a different destination namespace. [GitHub Issue] Observed impact in production: • A 741-row dimension table grew to 14,000+ rows • Downstream views joining affected tables cross-multiplied to billions of rows • Job status remained

succeeded

throughout — no visible errors [GitHub Issue] Workaround The only recovery found was manually deleting the stale high-generation rows (or emptying/dropping the final table), after which normal truncate-per-sync behavior resumed. [GitHub Issue] Fix Status The Airbyte team acknowledged the bug and planned to fix it by using strict equality (

==

) instead of

>=

in the generation ID comparison. However, as of the issue comments, the CDK change was awaiting rollout into the Snowflake connector version. [GitHub Issue] > Note: The source-postgres CTID scanning issue described previously is independent of the destination — it affects what rows are read from Postgres regardless of where they're written. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai Is this still a problem with insert-only tables such as an event tracking table?

The knowledge sources do not contain enough information to give a definitive answer specific to insert-only/event tracking tables in this context. However, based on what the knowledge sources do tell us, here is what can be reasonably inferred: Why It Still Matters for Insert-Only Tables The generation ID bug in destination-snowflake affects any

full_refresh | overwrite

stream, regardless of the nature of the source data. The problem is in the destination connector's truncate decision logic, not in the type of data being synced. [GitHub Issue] That said, the practical impact depends on your sync mode: • If you're using Full Refresh | Overwrite on an event table, the bug still applies — the table would grow unboundedly instead of being replaced each sync. • If you're using Incremental | Append for an event tracking table (which is the typical choice for immutable event data), the generation ID bug does not apply — that bug is specific to the truncate path used by

full_refresh | overwrite

streams. [GitHub Issue] Recommendation For insert-only event tables, Incremental | Append is generally the appropriate sync mode anyway, since you want to accumulate all events rather than overwrite them. In that case, you would not be affected by this particular bug. If you have a specific reason to use Full Refresh | Overwrite on an event table, the bug would still apply and the same manual workaround (clearing stale high-generation rows) would be needed until the fix is rolled out. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.

@kapa.ai to clarify: does the postgres source problem that you mentioned still matter for append-only (aka insert-only) source tables?

Good clarification. Based on the knowledge sources, the answer is no — the CTID race condition does not affect insert-only tables. Here's why: The root cause of the source-postgres CTID bug is PostgreSQL's MVCC behavior when a row is updated: the old physical location (CTID) is marked dead and a new version is written to a different physical location. If that new location falls in a page range already scanned by a previous batch, the row is missed entirely. [GitHub Issue] For insert-only tables: • Rows are never updated, so their CTID never changes after insertion. • A row inserted before the scan begins will stay at its original physical location for the duration of the scan. • There is no risk of a row's old version becoming dead while its new version lands in an already-scanned page range. The knowledge sources explicitly describe the problem as arising from concurrent

UPDATE

transactions during the scan. [GitHub Issue] If no updates are occurring, that race condition cannot happen. > One caveat: the knowledge sources do mention that the code handles VACUUM separately (file node changes between batches). If aggressive VACUUM or

VACUUM FULL

runs concurrently and rewrites the table, that could theoretically affect any table. But for normal operations on an insert-only table, the CTID bug is not a concern. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.