Thursday 15 October 2015

Difference and similarity between merge and merge join transformation

Difference and similarity between merge and merge join transformation?

Merge Transofrmations                                              Merge Join Transformation
1)The data from 2 input paths are merged into one     1) The data from 2 inputs are merged based on some common key.
2)Works as UNION ALL                                             2) JOIN (LEFT, RIGHT OR FULL)
3)Supports 2 Datasets                                                   3)1 Dataset

4)Metadata for all columns needs to be same                 4)  Key columns metadata needs to be same.

Pre-requisites 
5)Data must be sorted.
Merged columns should have same datatype i.e. if merged            5)  Data must be sorted.
 column is EmployeeName with string of 25 character in Input 1,
 it can be of less than or equal to 25 characters for merging to 
happen.

                                                                                       

                                                                                          Merged columns should have same datatype i.e. if merged column is EmployeeName with                              string of 25 character in Input 1, it can be of less than or equal to 25 characters for merging to happen.

Limitations 
Only 2 input paths can be merged.

Does not support error handling.                                      6)    Does not support error handling.
                                                                                                Use 
                                                                                              Merging of data from 2 data source

Can create complex datasets using nesting merge transformation,

                                                                                            7) When data from 2 tables having foreign key relationship needs to present based on common key.

No comments:

Post a Comment