CODEWITHSUNDEEP
sqlgoogle-bigquerysql-updatedbtscd
Ashok KS
Ashok KS

Reputation: 691

Create table with effective_from_date and effective_to_date from history table in BigQuery

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.

Upvotes: 0

Views: 74

Answers (1)

Println
Println

Reputation: 594

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

Upvotes: 0

Related Questions