Hello everyone, I am new to matillion, Where i was, trying to Implement SCD2. jobs.
So in my current scenario, i have a table where i have some X columns.
among those i have 4 columns which are SCD type-2.
(**see the first and second image for reference**)
where (SRC_ID, F_NAME,L_NAME,ZIP_CODE) are the SCD2 columns.
So as per my understanding according to the documentation, i have kept the 4 cols as match keys . and the remaining as compare keys.
But recently i have tested my job, using multiple scenarios like filling the NULL values by using COALESCE function and passing the data as it is which HAS NULL VALUS through DETECT CHANGES, where the result was exaclty same as when i was filling those columns with COALESCE(), and also Just Passing the rows which contain NULLs in them.
So my Question, here is does matillion takes care of the comparision values which has NULL values or should I Explicitly Fill the NULL values from the source system, by using COALESCE() function.
Hi @naveenkumarvadlamudi,
That's a great question, thanks for opening the discussion.
The best way to use the Detect Changes component is:
- Set the "Match Keys" to the column(s) which are the business key, i.e. the column(s) which uniquely identify a row. In your example you don't specify what the business key is, although I would guess it's either the credit_key and/or the src_id? Normally there are a small number of columns in the Match Key.
- Set the "Compare Columns" to the columns which are time variant. You have 4 columns which are SCD type-2 so this is where you would list those 4 columns. By the way, are you sure the src_id is a type-2 attribute? Maybe it's actually part of the business key?
Regarding null handling, the component simply does not process NULL values for any of the columns in the Match Key. I put together an example where the Match Key is set to just the "Name" column. Below is a screenshot showing the SQL. You can see it joins on the match key and filters out all rows where any columns in the match key are null, from both tables.
As a best practice I'd recommend you make all business key columns mandatory, so they are not permitted to contain NULL values. You are already using COALESCE for that purpose, which is a good solution.
If you have a need to handle NULL values in a business key, please could you add an idea in our Ideas Portal? I don't see anything along those lines currently.
Best regards,
Ian
Sorry for the late reply @ian.funnell (Matillion Limited) , it was the best answer, and as mentioned by you, i will create a topic in ideas section. as a suggestion.
