What does Airbyte's Incremental | Append + Deduped sync mode require from a stream?
answer
- two settings, or the mode is not offered
- one bounds the read, one collapses the write
- the append happens first, the collapse after
- greatest cursor per key wins the row
- a missing row is not the same as a deleted row
basics
~20 sIt needs a cursor field to decide what is new and a primary key to collapse it. Airbyte appends the incremental records to a raw table, then rebuilds a final table holding the highest-cursor row per primary key.
solid answer
~50 sTwo stream settings are mandatory: a **cursor field**, which bounds the read to records at or past the last saved cursor value, and a **primary key**, which tells the destination how to collapse them. The write path is append-then-collapse: records go into Airbyte's raw storage in arrival order, then the destination builds the final table with one row per key, keeping the version with the greatest cursor value and breaking ties on the extraction timestamp. Some connectors declare the cursor and key themselves; otherwise you choose them, and a composite key is allowed. Because the collapse is key-based, re-reading records is harmless — the same row simply wins again. What the mode does *not* do is remove rows deleted at the source, unless the source itself emits a deletion marker, as CDC-enabled database sources do with an `_ab_cdc_deleted_at` column.
code
json · 7 lines{
"stream": { "name": "orders", "namespace": "public" },
"syncMode": "incremental",
"destinationSyncMode": "append_dedup",
"cursorField": ["updated_at"],
"primaryKey": [["order_id"]]
}go deeper
Recall the two mandatory settings — a cursor field and a primary key — and that the result is one current row per key. Knowing that deletes are not handled automatically already puts you ahead.
Explain the append-then-collapse order: records land raw with duplicates, then the destination rebuilds a final table keeping the greatest cursor per key, tie-broken on extraction time. That order explains most observed behaviour.
Demonstrate judgment about key and cursor quality: what a non-unique key silently costs, why replays are safe here but not in append mode, and how you handle hard deletes for a source with no deletion marker.
Own the policy question: which streams may claim mirror-of-source semantics, how key choice is reviewed before a connection ships, and what reconciliation against source counts you run to catch a bad key before consumers do.
## What the mode promises `Incremental | Append + Deduped` is the mode people reach for when they want the warehouse table to look like the source table: one row per business key, holding the latest known values, updated cheaply. It delivers that by combining an incremental *read* with a deduplicating *write*, and it is the only stock mode that does both. ## The two required fields **Cursor field.** The cursor bounds the read. At the end of a sync Airbyte stores the maximum cursor value it saw; the next sync asks the source for records at or past that value. The column must move forward whenever a row changes — `updated_at` maintained by the application or the database, or a monotonic sequence — and it should not be nullable, because a record whose cursor is null cannot be compared and will typically never be selected. Some connectors ship a *source-defined cursor* you cannot change; database sources usually let you pick the column. **Primary key.** The key tells the destination which rows are versions of the same thing. It may be source-defined or chosen in the stream settings, and it may be composite, for example an order id plus a line number. It has to be genuinely unique per business entity in the source; if it is not, the collapse silently keeps one arbitrary member of each group and quietly discards the others. If either field is missing the mode is not selectable, which is the platform telling you it cannot honour the promise. ## How the collapse actually happens The destination does not update rows in place as they stream in. Records land in Airbyte's raw storage — a JSON payload plus metadata columns including an extraction timestamp — in whatever order they arrived, duplicates and all. After the records are committed, the destination runs its typing-and-deduplication step: it parses the payload into typed columns and writes a final table containing, for each primary key, the row with the greatest cursor value, using the extraction timestamp to break ties when two versions share a cursor value. This ordering matters for interviews. It explains why the raw table legitimately contains duplicates while the final table does not; why re-reading the same records after a retry is harmless (the same version simply wins again, so the mode is effectively idempotent for replays); and why the correctness of the final table depends entirely on the cursor being a faithful ordering of versions. If the source rewrites a row *without* advancing the cursor, the newer version loses the comparison — or is never read at all. ## When the primary key is wrong A non-unique key is the classic production failure: the final table looks plausible, row counts are lower than the source's, and nobody notices for weeks. A nullable key is similar — rows with a null key cannot be grouped meaningfully. Changing the primary key later does not retroactively fix already-collapsed history; you generally have to clear the connection's data and state and re-sync the stream so the final table is rebuilt from a full read. ## Deletes Deduplication answers 'which version wins', not 'does this row still exist'. If a row is hard-deleted at the source, an incremental read never sees it and the final table keeps serving the last version indefinitely. The exception is a source that emits deletions as records: Airbyte's CDC-enabled database sources add a deletion-timestamp column (`_ab_cdc_deleted_at`) so the delete arrives as a change record and the destination can drop or tombstone the key. For API and cursor-based sources, your options are a periodic full refresh of that stream or a source-side soft-delete flag that bumps the cursor. ## Version notes The mode was previously presented as *Incremental | Deduped + History* and additionally maintained a slowly-changing-history table alongside the final table; modern destinations dropped that extra table and produce only the current-state final table. Older destinations also produced the final table with a dbt-based normalization step rather than the built-in typing-and-deduplication path, so the machinery you find in a running deployment depends on its version — say so rather than asserting one implementation. ## Operational notes The collapse is a write-time cost that scales with the raw records accumulated for the stream, so very chatty streams can spend more time deduplicating than extracting. And because the final table is rebuilt from committed raw data, downstream models should read the final table, never the raw one.
- Two records share the same primary key and the same cursor value. Which one ends up in the final table?The destination breaks the tie on the extraction timestamp Airbyte stamps on each raw record, so the more recently extracted version wins. That is a mechanical tie-break, not a correctness guarantee: if a source stamps many genuinely different versions with one cursor value, choose a finer-grained cursor instead of relying on it.
- Why does the raw table still contain duplicates when the mode is called deduped?Deduplication is a destination-side step that runs after records are committed. The raw table is an append-only landing area preserving what arrived, including boundary re-reads and retry replays; the final table is rebuilt from it. Downstream consumers should read the final table only.
- You picked the wrong primary key and rows have been collapsing away for weeks. How do you recover?Fix the key on the stream, then clear that connection's data and state and re-sync so the raw records and the final table are rebuilt from a full read. Editing the key alone does not resurrect versions already collapsed away, and downstream models built on the bad final table need rebuilding too.
saying these in an interview costs you the question
- Thinks deduplication happens at the source before extraction
- Picks a non-unique or nullable column as the primary key
- Expects rows deleted at the source to vanish from the final table
- Says the cursor field is optional in this mode
- Believes the raw table also holds one row per key