DLT pipeline - SCD type 2

[ksenija]

I created my table using SCD type 2 in SQL. I need to do full refresh to load all of the data. Whenever I update data in my source table, in my new table scd_target I see only the latest record, history is not being saved.

CREATE OR REFRESH STREAMING TABLE scd_target;

APPLY CHANGES INTO live.scd_target

FROM (

    SELECT id,

           name,

           surname,

           updated_at

    FROM stream(sources_test.sources_test.source)

) KEYS (name)

SEQUENCE BY updated_at

STORED AS SCD TYPE 2;

I need to use SQL since we are using it for other stuff in DLT pipeline

[Rishabh-Pandey]

hi @ksenija  i got your use case but could you please tell me , what do you mean by "sources_test.sources_test.source" 

[ksenija]

Hi @Rishabh-Pandey ,

Those are my catalog, schema and external table

[ksenija]

Hi @Retired_mod ,

I still don't have a solution for my problem, so I still don't want to close this conversation.

Best regards,

Ksenija

[Rishabh-Pandey]

@ksenija The logic which you were using is correct from my side , i am still wondering what this is happening so if possible can you please drop the checkpoints paths and tables and recreate the tables to check once or you can provide me with some screen shots if possible .

[ksenija]

Hi @Rishabh-Pandey ,

I completely missed your message. At the end we decided not to use SCD 2, so I don't have any more data related to this query.

Thank you for your time!

Best regards,

Ksenija