Delphix Products

Expand all | Collapse all

Filters and masking 2 columns

Jump to Best Answer
  • 1.  Filters and masking 2 columns

    Posted 09-29-2020 11:15:00 AM

    Hello Data Masking Experts,

    I have an audit table where the before/after data is on the same row to make reporting easier.

    A simple example is below:

    tin_audit_table
    Contains 2 columns
    tin_before  varchar2(11)
    tin_after  varchar2(11)

    The application allows for "applied" as a valid TIN value.   I want to mask the data in both columns with a segmentation algorithm that generates valid, but different tins.  If I filter the tin_before of "applied" then I'll miss the data in the tin_after that need to be masked.  If I use the segmentation algorithm on "applied",  I get non-conformant data and tin_after is not masked.

    One solution would be two different rule sets, one for tin_before and one for tin_after that filters "applied" in either case, but that would require multiple masking jobs and filters on the same table(s).

    Is there an easier way to accomplish this task?  Can a segmentation algorithm be  ignore alpha characters (i.e. applied)?

    Thanks for your help!



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


  • 2.  RE: Filters and masking 2 columns

    Posted 09-29-2020 12:44:00 PM
    Hi Lisa,

    It seems that the case isn't clear enough to give you an answer.

    - can you please detail what allowed values accept your columns in clear

    - what values are you expecting for masked values

    - how do you set the filter, I guess you've done it from the UI

    - Give an example for the before and after situation you expect.

    you can set the filter using a custom sql, you can check for it here https://support.delphix.com/Delphix_Masking_Engine/Using_Custom_SQL_(KBA1764)

    Regards,

    Mouhssine

    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------



  • 3.  RE: Filters and masking 2 columns

    Posted 09-29-2020 02:56:00 PM
    Mouhssine,

    Thank you for the information on the custom SQL.  I'm not sure if it will help here or not.  I'll provide data detail that may help to clarify what I mean.

    Table Description:
    id - number(20)
    tin_before varchar2(20)
    tin_after varchar2(20)
    reason varchar2(20)

    Example audit date (assume all tins are valid even though this is bogus data)
    Original Data
    id    tin_before         tin_after                   reason
    10   applied             123-45-6789            client received tin
    20   987-12-6789     applied                    input error

    Masked Data (desired)
    id    tin_before          tin_after
    10   applied              675-54-1234
    20   123-54-5678      applied

    The segmentation algorithm changes the tin to a different, valid SSN but it needs to skip an alphabetic word applied.

    A sql filter of:
    select tin_before where tin_before != 'applied'  or tin_after != 'applied';

    Would skip both rows where tin_before and tin_after both need to be masked, just the word 'applied' skipped.

    Does that help?

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



  • 4.  RE: Filters and masking 2 columns

    Posted 09-29-2020 03:49:00 PM
    Hi Lisa,

    Indeed, now it's clear and got the case right.

    For information you've got non-conforming data when using the segment mapping with 'applied' because it didn't match the pattern set (so it's just normal behavior).

    My preposition to get it into work is to use a custom sql where you replace the string 'applied' with either null or empty string, this way it will pass the algorithm check pattern and get values masked. Once this step is done you will have to add a post script in your job to update back the null or empty string with the value ´applied'.

    KR,

    Mouhssine

    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------



  • 5.  RE: Filters and masking 2 columns

    Posted 09-29-2020 04:24:00 PM
    Mouhssine,

    If I create a filter the row will get skipped, correct?

    Your suggestion of  setting it to something that gets masked (but you know what it is) and then setting the data back post masking makes sense.

    In your experience, is this faster than masking the table twice (first time masking the tin_before values, skipping 'applied' in that row, then second time masking the 'after' value?

    Your suggestion will be less overall maintenance but has more data manipulation to get it to work properly.

    Thanks for the help!

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



  • 6.  RE: Filters and masking 2 columns

    Posted 09-29-2020 04:39:00 PM
    Lisa,

    There is a lot of parameters to take into account to answer the question (memory job size, stream, ...)

    The trouble with masking columns one by one is configuring one job per column

    You can give both solutions a try and benchmark the best one and easiest one to maintain / manage.

    Regards,

    Mouhssine

    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------



  • 7.  RE: Filters and masking 2 columns
    Best Answer

    Posted 09-30-2020 01:46:00 AM
    Hello Lisa,
    please reach out to your Delphix contact to get custom algorithm SM_UNI, it has an option for your requirement:
    Setting numeric_only = true
    This way it will mask only the numerics within the string and ignore alfa and punctuation.
    Best regards,
    Tino

    ------------------------------
    Tino Pironti
    Technical Services
    Delphix
    ------------------------------



  • 8.  RE: Filters and masking 2 columns

    Posted 09-30-2020 04:20:00 AM
    Hi Lisa,

    Just to complet Tino's answer, please ensure that you have only numeric chars in tin codes values

    ex : this one is ok 987-12-6789
           this one isn't A87-12-6789

    Regards,

    Mouhssine

    ------------------------------
    Mouhssine SAIDI
    Community Member
    Delphix Community Members
    ------------------------------