ETL - Transportation

Transportation in Data Warehouses

Transportation is the operation of moving data from one system to another system. In a data warehouse environment, the most common requirements for transportation are in moving data from:

1. A source system to a staging database or a data warehouse database
2. A staging database to a data warehouse
3. A data warehouse to a data mart

Introduction to Transportation Mechanisms in Data Warehouses

You have three basic choices for transporting data in warehouses:

1. Transportation Using Flat Files
2. Transportation Through Distributed Operations
3. Transportation Using Transportable Table spaces

Let me discuss just one transportation method.

Transportation Using Flat Files

The most common method for transporting data is by the transfer of flat files, using mechanisms such as FTP or other remote file system access protocols. Data is unloaded or exported from the source system into flat files using techniques as discussed in "Extraction in Data Warehouses", and is then transported to the target platform using FTP or similar mechanisms. Because source systems and data warehouses often use different operating systems and database systems, using flat files is often the simplest way to exchange data between heterogeneous systems with minimal transformations. However, even when transporting data between homogeneous systems, flat files are often the most efficient and most easy-to-manage mechanism for data transfer.