Create table with effective_from_date and effective_to_date from history table in BigQuery

Question

I have a table in which data gets appended for the changes. No delete or update, only append is done by a cloud run job.

Base table

Supplier_ID	Supplier_Name	Supplier_Contact	Last_Modified
123	ABC	03 483 394	2023-05-01 12:34:56
124	ABD	02 848 939	2023-05-01 12:34:56
123	ABC	03 483 345	2023-05-02 10:45:05
124	ABD	02 848 837	2023-05-02 10:45:05
123	ABC	03 478 102	2023-05-08 11:09:15

I'd like to create another table like below.

Supplier_ID	Supplier_Name	Supplier_Contact	Effective_From	Effective_To	is_active
123	ABC	03 483 394	2023-05-01 12:34:56	2023-05-02 10:45:05	N
123	ABC	03 483 345	2023-05-02 10:45:05	2023-05-08 11:09:15	N
123	ABC	03 478 102	2023-05-08 11:09:15	9999-12-21 00:00:00	Y
124	ABD	02 848 939	2023-05-01 12:34:56	2023-05-02 10:45:05	N
124	ABD	02 848 837	2023-05-02 10:45:05	9999-12-21 00:00:00	Y

What would be the best way to achieve? I was trying to find a way to do this using dbt but unable to find any way.

Answer 1

You can use this below query for reference and create your model in dbt

    with raw as (
    select 
    '123' as Supplier_ID, 'ABC' as Supplier_Name, '03 483 394' as Supplier_Contact, cast('2023-05-01 12:34:56' as datetime) as Last_Modified
    union all 
    select 
    '124' as Supplier_ID, 'ABD' as Supplier_Name, '02 848 939' as Supplier_Contact, cast('2023-05-01 12:34:56' as datetime) as Last_Modified
    union all
    select 
    '123' as Supplier_ID, 'ABC' as Supplier_Name, '03 483 345' as Supplier_Contact, cast('2023-05-02 10:45:05' as datetime) as Last_Modified
    union all
    select 
    '124' as Supplier_ID, 'ABD' as Supplier_Name, '02 848 837' as Supplier_Contact, cast('2023-05-02 10:45:05' as datetime) as Last_Modified
    union all
    select 
    '123' as Supplier_ID, 'ABC' as Supplier_Name, '03 478 102' as Supplier_Contact, cast('2023-05-08 11:09:15' as datetime) as Last_Modified
    ),
    raw2 as (
    select Supplier_ID,Supplier_Name,Supplier_Contact,Last_Modified as Effective_From,lead(Last_Modified) OVER (partition by raw.Supplier_ID ORDER BY raw.Supplier_ID,raw.Last_Modified asc) as Effective_To   from raw order by raw.Supplier_ID,raw.Last_Modified asc
    )
    select *EXCEPT(Effective_To),ifnull(raw2.Effective_From,'9999-12-21 00:00:00') as Effective_To,
if(raw2.Effective_To is null, 'Y','N') as is_active
 from raw2