ETL Architecture

Hi
My company is currently planning on building a datawarehouse. We are an oracle shop and are considering the approach for data migration from client servers to the datamart.

Thoughts we have
1) Asynchronous CDC . I believe oracle now is replacing this with Golden Gate.
2) Export / Import
3) Db Links
4) Oracle data pump. (Similar to export / import)

Questions
Anyone have any other option. Not sure if ETL tools are used for this. My thoughts have been that the ETL tools are used to transform data from staging tables to the Star Schema.

Any thoughts would be appreciated.
Thank you for your time in advance.

I think what you will find is all of the above. Each data source presents its own unique set of challenges. I agree with your take on ETL. ETL normally takes over once the data is staged. The majority of our data arrives in text files. Our ETL tool takes over from there, loading to Oracle staging tables and then transforming to the dimensional models.

ETL tools are commonly used for source extraction to staging as well (after all, that is what the E stands for...). It's just that, in your case, you have a more homogenous environment, so you can make use of Oracle features that are not available in a mixed database environment.

However, good data warehouse design should assume that legacy data sources will change, but the data warehouse (to a large extent) will not. This philosophy needs to trickle down to how the staging and loading of data takes place. Some level of transformation needs to occur in the staging process (such as abstracting natural keys) so that the staging table represents a standard, stable interface to the load processes. This allows the more complex transformation and load processes to remain stable should data sources (handled by simpler staging processes) change.