Notifications
Clear all

Hard rules (data type conversion)


Posts: 9
Topic starter
(@michadworakowski)
Active Member
Joined: 2 years ago

Hi All,
I have searched our forum for some threads regarding hard rules implementation but I could barely find any useful for me information ☹. Currently I’m working on a case where Raw Vault is populated with data that comes from flat files (flat files => stage => Raw Vault). Files specification tells that date fields are stored as “YYYYMMDD”. Unfortunately for few millions of source records there are few cases where type conversion from string to DATE is not possible because of invalid source value (there is no chance that these rows will be fixed in source system). To handle such cases in old solution that we are currently trying to replace there were defined following conversion rules:

Rule 1” – it could be described as “if text could be converted to date do the conversion, if not, return NULL”
IF IS_DATE(text_value) THEN
  RETURN TO_DATE(text_value)
ELSE
  RETURN NULL
END IF

Rule 2” – it is more “sophisticated” ? - if text could be converted to date do it and check whether date value is smaller then 1800-01-01. If yes, use 1800-01-01 instead.
IF IS_DATE(text_value) THEN
  IF TO_DATE(text_value) < DATE’1800-01-01’ THEN
    RETURN DATE’1800-01-01’
  ELSE
    RETURN TO_DATE(text_value)
  END IF
ELSE
  RETURN NULL
END IF

I'm curious what is your opinion about mentioned above conversion rules, could they be considered as hard rules and executed before Raw Vault load or they should be moved to Business Vault?
From my perspective, date fields stored as text in RAW vault, are not handy in use and in such case the only way we can make their use easier is to type/convert them and load to Business Vault. I was trying to convince my teammates to convert these fields on stage load and log any value that don't match expected format in Error Mart. Unfortunately they disagreed, pointing that our conversion rules change original value and in Raw Vault we should store untouched/unmodified values.

I will be appreciate for any advice.

Best Regards
Michael.

Reply
4 Replies