So the question here is should you have one enterprise ETL team and one ETL infrastructure for your entire enterprise/company/corporation
In my opinion Yes as soon as possible, but as always there is a but.
You can only have this is if you have standards in place (Or you are creating the standards with your first project) and when you have maturity in the team /enterprise to support and maintain this.
You need to clearly define the tools that you use for ETL – decide on your etl partner this partner is there to stay.
Create a process that can be re-used for each following etl job.
OK so what am I saying “re-usable etl job” – basically all ets job should for example support
- Batch_Run_id – in order to trace when data was inserted it should
- County Code – enterprises solution must scale across countries/States.
- Using the same Error table for logging
- Using the same metadata layer
- Using the same audit function
- Be re-executable.
- Be traceable – ie when where did it fail.
- If you use surrogate keys then all must use the same method to derive the key.
- Create components small components that can be re-used.
We can go on and on but you get the picture.
So what is your opinion?