SCD Type-2

sudhanshu1 — Mon, 22 May 2023 13:15:19 GMT

Hi All,

I have 22 postgress tables and i need to implement SCD type 2 and create azure Databricks pipeline . However my project team doesn't want to use delta tables concept . Have anyone implemented this ? below is how i planned to do

try:

df_src = spark.read.jdbc(url=jdbcUrl_source, table="data_table_1", properties=connectionProperties)

except Exception as e:

print("not able to connect")

print(f"Error is :{e}")

raise Exception

try:

data_table_1_target_df = spark.read.jdbc(url=jdbcUrl_target, table="data_table_1", properties=connectionProperties)

except Exception as e:

print("Target path not found")

print(f"Error is :{e}")

raise Exception

merged_data_df = spark.sql("""

MERGE INTO target_table AS t

USING scd_data AS s

ON t.id = s.id

WHEN MATCHED AND t.status = TRUE THEN

UPDATE SET

t.effective_to_date = s.start_date,

t.status = FALSE

WHEN NOT MATCHED THEN

INSERT (id, cus_name, age, effective_from_date, effective_to_date, status)

VALUES (s.id, s.cus_name, s.age, s.start_date, s.end_date, s.is_active)

""")

topic SCD Type-2 in Data Engineering

SCD Type-2