Title
#advice-data-transformation
Anatole Callies

Anatole Callies

04/12/2022, 9:13 AM
Hi, Say you have 2 tables that have a 1..n relationship (table B has N lines for every 1 line in table A), both of them are incrementally synced with airbyte, so you have many versions of each row in both scd tables. Has anyone already come up with an SQL query to retrieve the current version of the "parent" table for every version of the "child" table ? I want to join both scd tables using the shared identifier and the airbyte_start_at and airbyte_end_at columns but not sure how to efficiently do it
Chris Duong [Airbyte]

Chris Duong [Airbyte]

04/12/2022, 9:22 AM
Are you looking at doing something like this?
select * 
from A
inner join B
on A.some_id = B.some_id
and (
    (A.ended_at is not null and B.created_at between A.started_at and A.ended_at)
    or (A.ended_at is null and B.created_at >= A.started_at)
)
Anatole Callies

Anatole Callies

04/12/2022, 9:35 AM
Yes @Chris Duong [Airbyte], thanks ! Somehow I mixed it up
9:42 AM
what worries me is that for some instance of my object A, I don't have any valid version of my object B. See attached, object A has 3 versions (not sure why because 2 of them lasted less than 1 second), the latest one is valid from Nov 11, 2021 And object B has 6 versions, none of them valid before Dec 2
9:42 AM
Could it be caused by a connection reset ?
Chris Duong [Airbyte]

Chris Duong [Airbyte]

04/12/2022, 10:30 AM
maybe? did you do resets recently? But it’s strange that your B table got reset/truncated but not A