@kapa.ai In a full refresh | overwrite + deduped, how does the deduplication choose which records to keep?

In Full Refresh | Overwrite + Deduped, deduplication works as follows: 1. All source data is pulled fresh — every record from the source is retrieved, just like a standard Full Refresh. 2. Duplicates are resolved by primary key — the data is deduplicated so that only one row per primary key value exists in the final table. 3. The cursor field determines which record "wins" — records are sorted using the cursor field, and only the latest (most recent) row per primary key is kept. [Full Refresh Overwrite+Deduped] Under the hood, for certified destinations, this is implemented using a window function pattern like: [Dataflow CDK Architecture]

WITH deduped AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY primary_key
    ORDER BY _airbyte_extracted_at DESC
  ) AS rn
  FROM temp_table
)
SELECT * FROM deduped WHERE rn = 1

The result is then merged into the final table. Key points • Why use Overwrite + Deduped over plain Overwrite? Full Refresh syncs can be resumed after transient errors, which can introduce duplicate records. The deduped variant eliminates those duplicates, whereas plain Overwrite does not. [Full Refresh Overwrite+Deduped] • Trade-off: Deduplication incurs additional warehouse compute costs. • Destination-specific behavior: Some destinations implement deduplication differently. For example, ClickHouse uses its

ReplacingMergeTree

engine rather than a SQL

MERGE

statement, and requires adding

FINAL

to queries to guarantee deduplicated results at query time. [ClickHouse Destination] 👍 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 but there's no way to choose the cursor field for this option?

The knowledge sources do not explicitly mention whether a cursor field can be configured specifically for Full Refresh | Overwrite + Deduped mode. What the sources do tell us is: • The cursor field is primarily a concept tied to incremental sync modes, where it tracks new/updated records since the last sync. [Airbyte Protocol - Cursor] • In the

ConfiguredAirbyteStream

schema,

cursor_field

is required only when

sync_mode

is

incremental

. For full refresh modes, it would be ignored at the source level. [ConfiguredAirbyteStream] • The Full Refresh Overwrite + Deduped docs describe deduplication as "sorting the data using the cursor field and keeping only the latest de-duplicated data row," but don't explicitly explain how that cursor field is selected in this mode. [Full Refresh Overwrite+Deduped] For the UI, the docs do note that you can modify a cursor or primary key per stream by navigating to the relevant stream in the Schema tab, but this guidance is given in the context of incremental sync modes. [Configuring Schemas] I'd recommend checking the Airbyte UI directly when configuring a Full Refresh | Overwrite + Deduped stream to see if a cursor field option is presented, or consulting the Airbyte documentation for more details. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.