Hi,
With using MagicETL i wanted to understand if it possible to enforce a consistent ID where only net new rows would receive a new ID associated to it
Currently i have a dimension table that contains two columns agency_name and advertiser_name where in the cases of agency_name being unknown it is assigned a unique identifier "unknownar[numberical string]" (i.e unknownar00001). I am using a combination of rank and window and formula (using concat) to create this identifier. Noting that this table only contains the distinct unique combinations of agency_name and advertiser_name
The rank and window function is set to order by advertiser name ascending. Given that my current ETL recreates this table everyday, what will happen is that if a net new agency_name + advertiser_name combination is introduced where lets say the advertiser_name string has a higher alphabetical sorting, the "unknown" identifier that is being applied will change given the new rank/order.
This causing complexity with a downstream process in which we are mapping that unknown identifier to the intended "agency_name" and effectively cause a situation where previously mapped values will need to be remapped.
Is there a way to enforce a consistent ID using magic etl ?