New Feature - Append+Dedup Event Stream to create a completely deduplicated event stream of data

[williamkaper]

Today, there are two options for appending data. You can (incrementally or full) append all, which creates a duplicate on any update event, and you can append-dedup, which basically modifies the record using the PKey, leaving only 1 record per unique PKey based on the latest cursor / airbyte synced record.

What is missing is a way to create a deduplicated event stream, where airbyte basically keeps distinct PKEY + Cursor rows. Rows with the same PKEY + Cursor value would be de-duplicated, leaving the one with the OLDEST airbyte_extracted_at date, preserving a clean and noiseless event stream.

PROS:

• When you want a true event stream, this would eliminate the noise created by some connectors that occasionally bring over updates that don't modify the cursor value or any piece of the record, (Stripe is guilty of this to name one)

CONS:

• Minor one - It would require a second indexed window function that would be Partitioned By: PKey, Cursor ORDER BY airbyte Extracted Asc

[williamkaper]

I don't have the skill to add a new option to the CDK to try this in Postgres, but if the team would be open to doing it I would volunteer to help review and test it.