Target as Lookup

[jerome_rajan]

Hi,

We have a job that looks up from the same table that the job is loading into. Something like this.

Code: Select all

                         Table A
                             |
                             |
                             |
                         Hash File
                             |
                             |
              SrcFile---->Transformer-------->Table A

Is this a good/correct practice? Won't the target keep changing even while it is being used as reference due to the pipe-lining of data? Will this produce correct results?

[ray.wurlod]

That is a perfectly good practice. The output of a passive stage (such as a hashed file stage) is not opened until its input is closed. The contents of Table A are loaded into the hashed file before anything else happens.

If you want to be able to look up against changes too, you need to implement a design that also writes to the hashed file, and make use of the "lock for updates" property whether or not you're using caching.

Code: Select all

         TableA  ---->   HashedFile
                              |
                              |
                              V
         SrcFile  ---->  Transformer  ---->  TableA
                              |
                              |
                              V
                         HashedFile

Pet peeve: it's properly "hashed" file, not "hash" file.

[chulett]

Ray's illustrated technique is akin to a "Dynamic Lookup" in Informatica, if that helps or means anything to anyone playing along from home.