Hey guys, first thanks for Airbyte, makes things much easier since i used to do everything in ADF 😄 I have a question regarding sync modes. Would it be feasible to create a mode that lets me sync data in the ways of slowly changing dimension 2 ? So if a entry changes i want to create a new row with a updated "changed_at" but also an additional column that this new entry is the most recent one. Regular incremental-append does create the new line with the new "changed_at" cursor field, but does not make it easy to find the most recent entry, while deduped removes history completely. Thank you for your time and help, Tobias :)

I'm not aware of any such functionality in Airbyte, but since you're already scanning the source table, I would just take the latest record using a window function along these lines (this would be BigQuery syntax):

SELECT
   ...
FROM
  ...
WHERE
  ...
QUALIFY
  ROW_NUMBER() OVER(PARTITION BY your_key ORDER BY changed_at DESC) = 1

This will pull only the latest record for each primary key represented by

your_key

. Because it doesn't use grouping, or

DISTINCT

, it will work fine when there's nested data as well.

QUALIFY

is a bit newer, but it's basically like a

WHERE

clause for window/analytic functions (the same way

HAVING

works for

GROUP BY

). Note: If you're in a different SQL that doesn't support

QUALIFY

, you can select that window/analytic function as a column, and then filter it in an outer query using

WHERE

.