If I send 5 rows to a SCD stage with the same key for Type 2 determination, does it insert/update rows based on a certain criteria or does it do it at random?
For example, if I have data like this:
Business Type 2 Col
Key
12 X
12 A
12 Y
12 C
12 S
It sets the first 4 rows as not current and the final row inserted becomes current (not in the order I listed above). I tried doing a ORDER BY on the "Type 2 Col" before the data is sent to the Slowly Changing Dimension stage but it doesn't insert in that order. I would think that it would, considering that I am using a single node config file.
Slowly Changing Dimension stage with Type 2
Moderators: chulett, rschirm, roy
Okay, I'll bite.
No, there isn't anything random about it as that would make absolutely no sense. How about more details - I for one would be curious what your source is that is sending these rows and how it is configured. Would also like to know input versus output results. Saying "It sets the first 4 rows as not current and the final row inserted becomes current" sounds perfectly fine until you add the "not in the order I listed above" part. Which doesn't help us much if you keep the actual order it used to yourself.
Based on your "ORDER BY" comment, I would assume your source is a database where typically you would need to source them in chronological order in order to capture the history properly using load date, effective date, something. Don't rely on the database giving it back to you in any specific order without doing that.
No, there isn't anything random about it as that would make absolutely no sense. How about more details - I for one would be curious what your source is that is sending these rows and how it is configured. Would also like to know input versus output results. Saying "It sets the first 4 rows as not current and the final row inserted becomes current" sounds perfectly fine until you add the "not in the order I listed above" part. Which doesn't help us much if you keep the actual order it used to yourself.
Based on your "ORDER BY" comment, I would assume your source is a database where typically you would need to source them in chronological order in order to capture the history properly using load date, effective date, something. Don't rely on the database giving it back to you in any specific order without doing that.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers