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

[Kaniz_Fatma]

Hi @ksenija, Thank you for reaching out to our community! We're here to help you.

To ensure we provide you with the best support, could you please take a moment to review the response and choose the one that best answers your question? Your feedback not only helps us assist you better but also benefits other community members who may have similar questions in the future.

If you found the answer helpful, consider giving it a kudo. If the response fully addresses your question, please mark it as the accepted solution. This will help us close the thread and ensure your question is resolved.

We appreciate your participation and are here to assist you further if you need it!

[ksenija]

Hi @Kaniz_Fatma ,

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