ETL is an abbreviation of the three words Extract, Transform and Load. It is an ETL process to extract data, mostly from different types of systems, transform it into a structure that’s more appropriate for reporting and analysis and finally load it into the database and or cube(s).
The three major steps in ETL
1. Extract from source
2. Transform the data
3. Load the data
|In this step we extract data from different internal and external sources, structured and/or unstructured. Plain queries are sent to the source systems, using native connections, message queuing, ODBC or OLE-DB middleware. The data will be put in a so-called Staging Area (SA), usually with the same structure as the source. In some cases we want only the data that is new or has been changed, the queries will only return the changes. Some ETL tools can do this automatically, providing a changed data capture (CDC) mechanism.||Once the data is available in the Staging Area, it is all on one platform and one database. So we can easily join and union tables, filter and sort the data using specific attributes, pivot to another structure and make business calculations. In this step of the ETL process, we can check on data quality and cleans the data if necessary. After having all the data prepared, we can choose to implement slowly changing dimensions. In that case we want to keep track in our analysis and reports when attributes changes over time.||Finally, data is loaded into a data warehouse, usually into fact and dimension tables. From there the data can be combined, aggregated and loaded into datamarts or cubes as is deemed necessary. The business user analysis and uses the transformed data with BI instruments like data visualization software, dashboards, OLAP tools and reporting tools.|
But, today, ETL is much more than that
Most ETL software also covers:
- data profiling and data quality control
- monitoring and cleansing of the data
- real-time and on-demand data integration
- extraction of Big Data using Hadoop
- master data management
An ideal ETL architecture contains a data warehouse
Below you’ll find the ideal ETL architecture supporting the three major steps in ETL.
Data profiling and data quality control
Profiling the data, wil give direct insight in the data quality of the source systems. It can display how many rows have missing or invalid values, or what the distribution is of the values in a specific column. Based on this knowledge, one can specify business rules in order to cleanse the data, or keep really bad data out of the data warehouse. Doing data profiling before designing your ETL process, you are better able to design a system that is robust and has a clear structure.
Meta data management & ETL
Information about all the data that is processed, from sources to targets by transformations, is often put into a metadata repository; a database containing all the metadata. The entire ETL process can be ‘managed’ with metadata management, for example one can query how a specific target attribute is built-up in the ETL process, called data lineage. Or, you want to know what the impact of a change will be, for example the size of the order identifier (id) is changed, and in which ETL steps this attribute plays a role.
Learn more and download our ETL Tools & Data Integration Survey
The ETL Tools & Data Integration Survey is a 100% vendor independent, extensive comparison report and market analysis (updated since 2004 on a very regular basis). Use it to choose the best ETL tool / data integration solution for your situation very quickly. Save time and get true insight. Our 100% independent ETL tools comparison is available for purchase.