ETL processes are the transfer of data from source systems into a target system:
- Extract: The reading of data from the source systems.
- Transform: Various data modifications, customization of data types, elimination of duplicates, aggregation, and standardization of heterogeneous data structures.
- Load: Writing data in the target system.
The source systems usually involve one or more operational databases and their data is combined into a target system. It begins with an initial load and the periodic loading of data occurs incrementally to keep the data warehouse up-to-date.
ETL or ELT?
Instead of using the ETL tools, you can use SQL statements in the database to perform more complex data transformation than the data type conversion. For example, data modifications, aggregation, or schema manipulations.
You can also consider the ELT processes for better performance. The Transformation phase is performed after the Extract and Load. Exasol recommends this approach because it exploits the high performance of the parallel cluster and the database for complex operations.
The following are some of the ETL approaches: