Hi,
Say you have 2 tables that have a 1..n relatio...
# advice-data-transformationa
Anatole Callies
04/12/2022, 9:13 AMHi,
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
c
Chris Duong [Airbyte]
04/12/2022, 9:22 AMAre you looking at doing something like this?
Copy code
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)
)a
Anatole Callies
04/12/2022, 9:35 AMYes @Chris Duong [Airbyte], thanks ! Somehow I mixed it up
Anatole Callies
04/12/2022, 9:42 AMwhat 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
Anatole Callies
04/12/2022, 9:42 AMCould it be caused by a connection reset ?
c
Chris Duong [Airbyte]
04/12/2022, 10:30 AMmaybe? did you do resets recently?
But it’s strange that your B table got reset/truncated but not A
2 Views