I'm creating ingestions using delta live tables, the dlt support the use of schema, with constraints like foreign keys. The problem is: how can i create foreign keys between the same pipeline, that has no read/write relation, but has foreign key relation. The dlt graph will run both in the same time, and it will break in constraint telling that the table referenced does not exist. There is a way to ignore constraints in the first run ? avoid error ? or i need to use some alter table after dlt runs, to do this relations ?
i testes something like the code below, to resolve the problem, but i think that is not a good solution
def add_fk(constraint_name, foreign_key, full_table_path😞
try:
table_exist = spark.sql(f"DESCRIBE TABLE {full_table_path}")
return f",CONSTRAINT {constraint_name} FOREIGN KEY({foreign_key}) REFERENCES {full_table_path}({foreign_key}) NOT ENFORCED"
except:
return ""
@dlt.table(
schema=f"""
SALESORDER STRING NOT NULL PRIMARY KEY,
SALESORDERTYPE STRING,
SALESORDERPROCESSINGTYPE STRING,
CREATEDBYUSER STRING,
LASTCHANGEDBYUSER STRING,
load_ts TIMESTAMP NOT NULL
{add_fk("fk_dlt_teste","SALESORDER", "mul_dev_tests.dlt_sap_test_new_new.sales_order")}
"""
)
def sales_order_2():
return (
spark.readStream.table("adm_sap_qas_bronze_dev.sd.sales_order")
.select("SALESORDER","SALESORDERTYPE","SALESORDERPROCESSINGTYPE","CREATEDBYUSER","LASTCHANGEDBYUSER")
.withColumn('load_ts', F.current_timestamp())
)
