topic Re: DLT pipeline - SCD type 2 in Data Engineering

DLT pipeline - SCD type 2

ksenija — Tue, 23 Jul 2024 08:49:32 GMT

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

Re: DLT pipeline - SCD type 2

Rishabh-Pandey — Tue, 23 Jul 2024 09:23:10 GMT

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

Re: DLT pipeline - SCD type 2

ksenija — Thu, 25 Jul 2024 09:38:28 GMT

Hi @Rishabh-Pandey ,

Those are my catalog, schema and external table

Re: DLT pipeline - SCD type 2

ksenija — Thu, 25 Jul 2024 09:39:52 GMT

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

Re: DLT pipeline - SCD type 2

Rishabh-Pandey — Thu, 25 Jul 2024 10:19:36 GMT

@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 .

Re: DLT pipeline - SCD type 2

ksenija — Tue, 20 Aug 2024 09:50:14 GMT

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