ETL
Testing:
What
is ETL, Extract Transform and Load?
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 figure below displays these ETL steps.
ETL – Extract from
source
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.
ETL – Transform the
data
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, for
example a customer moves from one region to another.
ETL – Load into the
data warehouse
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.
Data warehouse &
ETL architecture
But,
today, ETL is much more than that. It also covers data profiling, data quality
control, monitoring and cleansing, real-time and on-demand data
integration in a
service oriented architecture (SOA), and metadata management.
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.
Posts
