ETL : check child-parent relation integrity

Hello !

We are writing our own ETL, and I have a question about the best practices to manage child-parent relationship integrity in a SCD 2.

Let say I have this table (product referential) :



Basically, I want to check that a product type belongs to only one product line (for records with current flag set to "Y"), to respect hierarchy integrity.

How could I achieve that in a good way, at ETL & DB level ? (I'm using a SQL Server 2012 Standard Edition)

For the moment, the ETL is working in this way :
1. Read flat file line by line & check format integrity
2. If there is no error in the entire flat file :
 - insert new lines
 - modify existing lines if needed (put flag to "N" & insert end date)

I suppose I have to do something between step 1 & 2 to check dimension integrity.

Thanks for any advice =)

How do you decide to insert a new line? Simply add product type to the natural key.

I insert a new line if :
- product code doesn't exist,
- product code exists, but informations changed (product type code or product line code).

Why should I add product type to the natural key ? I want that at a time T1, one product type belongs to only one product line for all current records (I don't want a record with T1 to belong to both L1 & L2, for example), like it would be with a normalized model.

Ahh, then yes, you need something else to enforce the constraint. If the volume of changes is small, a DB trigger is fine. Just make sure the ETL process can recover gracefully from a failed insert.