DSXchange

Hi, I am using change capture stage to implement type 2 scd. Below is an example where I have multiple records for an id in source.
I would like to fetch reference data from database for every source record. Is there a way to achieve this?

source
id col1 col2 col3
s1 a b c
s1 a1 b c
s1 a1 b1 c

target dimension
key id col1 col2 col3 flag
1 s1 a b c n
2 s1 a1 b c n
3 s1 a1 b1 c y


design
(tgt reference)
file--changecapture---switch---new record insert with flag=y (tgt)

---update flag=n (tgt)
---insert record with flag=y (tgt)

I am aware of scd stage and facing some issue. Meanwhile wanted to try thsi approach.
Thanks for your help.