When to update the table.

[anshumangupta1206]

Hi All,
I have a doubt, may be it sounds silly but I want to confirm. In my project we are validating record in one stage and forming a file after that. And in the new job by using same file we are updating table. Can anybody tell me, should I use the same job for both the purpose.

Thanks.

[kcbland]

It's up to you, technically it all works. If you are just writing a single isolated process, do what you want. If you are designing an ETL standard, then you need to factor in performance, restartability, scalability, maintainabilty, etc as well as setting the path for all other developers to follow. This is a long answer, because you are talking about a "framework" for developing jobs consistently.

[ray.wurlod]

Your use of a "staging area" is best practice for achieving restartability (you have already done the transformations, why do them again just because the target database may be unavailable?). If overall speed is the ultimate criterion, you would lose the intermediate file, but at the cost of needing to extract from source again and perform the transformations again in the case where the target table is not available.

Design and document some standards. Create templates that supply these standards. Make sure all developers follow the standards. For example:

• Job naming and categories
  Stage and link naming
  Annotation colours (e.g. warnings are red on white)
  Description annotations
  Peer review processes
  Unit and system test processes