Delphix Products

 View Only

  • 1.  Masking Date stored as NUMBER

    Posted 01-18-2021 07:51:00 AM
    Good morning all,

    I have a data warehouse that is storing a date as a number.   I tired using DATE SHIFT DISCRETE like the other columns in this domain with a format mask that matches the YYYYMMDD, but it's failing due to the mismatch in data type.

    In the SQL I can use a to_date(column_name, 'YYYYMMDD') to convert it to a number, however, for it to be persisted in the database, I'd  have to use a different column and then update my original column with the number values.

    Is there an easier way to accomplish this task?

    Let me know.

    Thanks!
    Lisa

    ------------------------------
    Lisa Garczynski
    Database Engineer III
    Paychex
    ------------------------------


  • 2.  RE: Masking Date stored as NUMBER

    Posted 01-19-2021 08:13:00 AM
    Hi Lisa,

    You can try using Segmented Algorithm, split the content in 3 Segment with
    1st as 4 Digit (Year)
    2nd as 2 Digit (Month) - Set a mask value min & Max between 1 to 12
    3rd as 2 Digit (Day) - Set a mask value min & Max between 1 to 28 (to satisfy all the max day in different months)

    If you want to retain any part of date (like year, don't use it in Segment, instead mention the range in Preserve Original Values) Or you can engage Professional Services for custom Algorithm.

    Hope this helps.

    ------------------------------
    Srinivasan Sarman
    Test Data Manager
    The Toronto-Dominion Bank
    ------------------------------

    Original Message


  • 3.  RE: Masking Date stored as NUMBER

    Posted 01-19-2021 09:42:00 AM

    Srinivasan,

    Thank you for your reply.  Your suggestion will provide a valid date, however it won't match the DATE SHIFT(discrete) algorithm unless I mask it as a DATE field.  I am going to try the to_date option and persist it in a different column of the table.  This *should* work. 

    Is there a way to trigger a post masking operation?  If not, I'll have to do it with our scripts which do post-masking tasks (update the existing column as a number and remove the masked date field).


    Thanks!
    Lisa



    ------------------------------
    Lisa Garczynski
    Database Engineer III
    Paychex
    ------------------------------

    Original Message


  • 4.  RE: Masking Date stored as NUMBER

    Posted 01-19-2021 10:07:00 AM
    As it is DB, you can attach Pre-script to create a column in DB and update that column by converting the data to date data type, mask the new field, in post script you update the original column with masked date field (after converting to text data type)

    ------------------------------
    Srinivasan Sarman
    Test Data Manager
    The Toronto-Dominion Bank
    ------------------------------

    Original Message


  • 5.  RE: Masking Date stored as NUMBER
    Best Answer

    Posted 01-20-2021 01:47:00 AM
    Hi Lisa,
    you can create a temporary column DATE2 using a date format on that table.
    Then you populate that column using the STRING column as input and use a NVL or equivalent to
    save a NULL into rows where the input STRING is not a valid date.
    The DATESHIFT algo runs then on DATE2 column and afterwards
    you overwrite the original string column using the masked value in DATE2 with a where clause
    "date2 is not null".
    This gives you also the option to create a report of invalid data rows/ids and
    provide it to the application team to fix.

    The OutOftheBox algorithms follow the rule to mask data but not fix data which means
    that invalid entries cause a fail (or are not masked).

    The other option is to create a custom algorithm using the Java based masking SDK
    and create a logic that masks the input string (for example using the SAME standard DateShift via algorithm chaining) but develop your own logic how to deal with "invalid string inputs".
    Or you ask Delphix Technical Services to create such a SDK logic for you.
    BR .. Tino

    --

    Tino Pironti 
    Senior Technical Principal



    Original Message


Related Content