Define a lookup against the table you want to check against. (Right-click on Reference Data node in the project browser, Add Lookup). The lookup can be to either staged data or external data (if the data is dynamic)
Then add a Lookup and Return processor to your process and configure the options. You can check for the correct cardinality if required.
If you are doing a 1:M lookup and want to create a 'join' in the process, input all the array attributes from Lookup and Return into a single Split Records from Array processor and then either Write or do other downstream processing on the joined stream.
Alternatively, you could just snapshot a view, or use SQL in the snapshot.
Thank you very much Mike. I was able to join 2 tables using 'Lookup and Return' and 'Split records from Array' processors. Is it possible to join more than two tables at once using these two processors?
Generally, I would say use multiple processors for this - one for each link between tables. From a DQ perspective this is better as the lookup checks the relationship as it processes.
It is possible to use SQL in a snapshot though and lookup onto this which would enable the use of a single processor, or even no processors if all you are trying to do is bring the data in rather than check consistency etc.