@JJ_Murrin
When deciding how to implement technically then details really matter.
For example: "Customer_ID can be assumed that an IDENTITY setting on the table"
1. I translate this means your source table is in SAS but your target table is in a database (which one?)
2. ID is maintained within the database (column of type Identity or something functionally similar)
-> the ETL is not loading it. To be excluded from SAS table metadata as else DIS will generate code for this column.
On a logical level - and you really need to spell this one out logically before diving into the technical translation:
1. CUSTOMER
Business Key: ? - It can't be Name as that's the column you update, it can't be customer_id as that's the generated key
Load technique: Upsert
2. INDIVIDUAL & ORGANIZATION:
Business Key: ? - the one from Customer plus Tax_ID?
Load technique: SCD2 - Insert for new business keys, Update and Insert for existing business keys but changed values for columns under type 2 change tracking
3. Business, Government_Agency, ....
Not enough information provided yet. The only column shown is Customer_ID as FK. This column can be in this table for query convenience but it doesn't need to be logically. The column that needs to exist as FK is the PK from ORGANIZATION
Also: Aren't there any type 2 columns in this table? The model as posted doesn't tell us enough.
Soo... Please "fix" the model and provide sufficient information on this level before trying to dive deeper.
"How would that work with rollback on any error?" That very much depends what you mean by that. On a table level or for a full "transaction", i.e. rollback of all the data loaded into all tables when the load fails for BUSINESS for a new customer? What needs to be done and how also depends on how you get the data from upstream (=interface spec).
... View more