ETL with RDBMS mode

At present, Teradata Data Warehouse ETL operation using ELT mode, because the loading is too heavy, the need to transfer the ETL pressure to a dedicated ETL server. For ETL tools, there are already mature commercial/open source tools in the market, such as Informatica's PowerCenter, IBM DataStage, and open source kettle.

Here are some of my own thoughts, the starting point is, how to spend a relatively small price to switch the ELT mode to ETL mode. My recommendations are:
Use ETL with RDBMS mode instead of traditional ETL tools. The word ETL with RDBMS is modeled after SQL on Hadoop, which I think is very image. We do not need to reinvent the wheel, a combination of ETL and DB can be. In this combination, the ETL tool does not need to have the very powerful transform function, as long as has the efficient extract and the load function, the RDBMS needs to have the strong SQL support. At present, a better choice is the SSIS + SQL Server Enterprise Edition, this combination license cost is not high; Another cheaper combination is kettle + PostgreSQL.

Kettle I'm not using experience, the following SSIS illustrates some of the features of the scenario:
1. Efficient E and L performance
Microsoft website provides Oracle and Teradata dedicated SSIS connector, download link (http://www.microsoft.com/en-us/download/details.aspx?id=29283).
2. Smaller migration efforts
The migration idea of complex ELT operation can use Elt+el to do ETL.  The 1th ELT, complete the data extraction to SQL Server, and then use SQL in SQL Server to do transform, this SQL can draw on the original Teradata SQL. The 2nd El will extract the converted result data to Teradata.
3. Lower costs
Informatic Premium's list price (1-core) is about $80,000, and the IBM datastage list Price (1-core) is about $190,000, while the SQL Server 2012 Enterprise Edition List Price (1- Core) is about $7,000, and SSIS is a built-in ETL tool in SQL Server Enterprise Edition without the need for dedicated license.
4. Development environment: Schema Job Designer on visual Studio, throw other ETL tools several streets
5. Easy to extend: You can use popular c#/vb. NET syntax to accomplish some special processing requirements
6. File-based job carrier: Each job itself is a file, easy to deploy, natural for version management
7. More powerful communities, richer documentation (MSDN): General questions to find a solution on the web
8. Top Down Job design ideas: More in line with the thinking habits of developers


We look back at the benefits of the ETL tool claims:
1. Friendly work Design interface (visual drag and Pull component)
The users of ETL tools tend to be it developers, for whom SQL is often more readable than visual components
2. Extensibility
High-end ETL tools claim that they have better linear extensibility. After I test, datastage extensibility embodies is also good, powercenter not reflected. ETL with RDBMS This scheme does not have linear scalability because the compute engine uses an RDBMS. But I personally think that the meaning of linear extensibility is not large, if you can do more than one ETL job pressure to a few ETL nodes, more meaningful, this coarse-grained load balancer can be achieved with the help of scheduling, the difficulty is not large.


Given the need for multiple ETL servers, the traditional package deployment model is used instead of the default project deployment model of SSIS 2012. Project deployment mode requires the creation of a SSISDB database on SQL Server.

ETL with RDBMS mode