Establishing a consistent ID using Rank and Window

mvfavor

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 ?

MarkSnodgrass

You could potentially implement a recursive dataflow that would allow you to look for new records and then assign them new ids while keeping the ids that have already been created. I think an easier and more sustainable solution is to use your concactenation of the agency and advertiser names as your unique identifier downstream. This would hold up better when new agency and advertiser combinations come in.