Get the latest from Teradata's partners

Getting more from SQL

The traditional extract, transform and load (ETL) software market has grown during the past 10 years. Many have successfully used ETL products to help load their data warehouse.

But ETL tools of the past may not be the best approach to solving today's data integration needs.

In the 1990s, the value proposition brought to the market by ETL software vendors was compelling. At that time, the generic SQL provided by RDBMS vendors did not have the power to perform data transformation and aggregation processes required to prepare and maintain a successful data warehouse.

With SQL's limitations, many data warehouse teams turned to ETL tools—and their proprietary transformation languages—to perform the required tasks.

"With the improved performance of SQL, developers can now create data integration scripts with SQL and use existing RDBMS engines (running on a target system) to actually perform required transformations," says John Rauscher, COO of Sunopsis.

"Using this approach, data is extracted, then loaded and transformed on the Teradata server or wherever the data resides, thus creating an ELT (extract, load and transform) architecture."

A year ago, Sunopsis introduced the Sunopsis Open Connector for the Teradata Warehouse. The product helps organizations streamline the task of accessing and reconciling data from diverse production applications and speed the loading of data into their Teradata Warehouse. It includes open extensions that enable Sunopsis to leverage native SQL, optimized for the Teradata platform.

"From a performance perspective, the ELT approach is roughly 10 times faster than the ETL approach," Rauscher says. "The performance gain is mainly achieved thanks to the bulk processing of data, using the set processing features of the relational model. With traditional ETL tools, all data transformations executed inside the ETL engine are done on a row-by-row basis. With the ELT approach, the engine used for transformation is the Teradata RDBMS. All data processing is done with SQL in bulk, making the ELT approach much faster than the ETL (hub server) approach."

For more information about Sunopsis see "Getting more from SQL". T