What does each Airbyte sync mode do to the destination table?

## A sync mode is two decisions Every stream inside an Airbyte connection carries its own sync mode, shown as `Read mode | Write mode`. The left half decides what the **source** hands over on each run; the right half decides what the **destination** does with what arrived. They are independent decisions, which is why the modes read as pairs, and why one connection can sync a small lookup table one way and a large event table another. The read side has two values. **Full Refresh** re-reads the entire stream every time — every row of the table, every page of the API. **Incremental** re-reads only the records whose *cursor field* is at or past the cursor value Airbyte saved at the end of the previous sync. The cursor field is a column the connector declares (a *source-defined cursor*) or that you pick in the stream settings; it must be something that moves forward as rows change, typically `updated_at` or a monotonic id. The write side has three values: **Overwrite**, **Append**, and **Append + Deduped**. In the Airbyte protocol these are the destination sync modes `overwrite`, `append` and `append_dedup`. ## Full Refresh | Overwrite The destination ends the sync holding exactly what the source produced this run and nothing else. Destinations implement it as a load into a temporary location followed by a swap, so the final table is not left half-empty mid-sync. This is the self-healing mode: whatever drifted, was deleted, or was mangled at the source is corrected on the next run, because the previous state is discarded. Its cost is that you pay the full read every time and you keep no history. ## Full Refresh | Append The full read is added on top of what is already there. After ten syncs the table holds ten complete copies of the source, distinguishable only by the extraction timestamp Airbyte stamps on each row. This is useful when you deliberately want periodic snapshots — a slowly-changing reference table you want to audit over time — and it is a footgun when someone selects it expecting deduplication. Storage and query cost grow linearly with sync count. ## Incremental | Append Only records past the saved cursor are read, and they are appended. This is the cheapest mode for large append-mostly streams such as events or logs. Two properties surprise people. First, an updated row does not replace anything: you get a second row for the same business key, and downstream queries must pick the latest themselves. Second, the mode does not promise uniqueness — connectors generally re-read records *at* the last cursor value rather than strictly after it, so boundary records repeat, and a retried sync can re-append records the destination already accepted. ## Incremental | Append + Deduped The read is identical to Incremental | Append; the difference is the destination step. Records are appended to a raw table, then Airbyte builds a final table containing one row per **primary key**, keeping the version with the highest cursor value. This mode therefore needs both a cursor field and a primary key, and it is the one people mean when they say they want the warehouse table to mirror the source. Deletes are still invisible unless the source itself emits a deletion marker. ## Where the mode is set, and what constrains it Sync mode is a per-stream setting inside a connection. The source's catalog declares which read modes each stream supports — some API streams have no usable cursor at all and offer full refresh only — and the destination declares which write modes it supports. Airbyte offers the intersection, so 'incremental is greyed out' is usually a statement about the connector, not about your configuration. ## What the mode does not change Scheduling is separate: a connection runs manually, on a basic interval, or on a cron expression, and the sync mode says nothing about how often. The mode also does not change the fact that records land in Airbyte-managed raw storage first and are typed into the final table afterwards. Mode names have moved between versions — the deduped mode was previously presented as *Incremental | Deduped + History* and maintained an extra history table — so name the behaviour, not just the label, in an interview.