ETL Process

Reporting databases need to consolidate data from across many applications within an organization.  In order to successfully pull data from source applications and make it into usable data for reporting and analytics.  There are two paths that most Business Intelligence strategies can leverage, ETL or ELT.

Extract Transform and Load (ETL)

ETL is the original standard for loading a data warehouse or Operational Data Store (ODS).  Using this method data is moved from the source systems and transformations applied during the data movement phase.  The data flow processing would pull and manipulate the date in stream.  Ultimately with the data being stored in its destination environment.  For example, if you want to merge customer records from three different systems into a single customer table.  Your ETL would pull relevant information from all three source systems and then apply business rules during the transformation step to create a single record to use for insert, update or delete operations against the destination table see figure 1.

Figure 1

:

Extract Load and Transform (ELT)

ELT is a newer framework being leveraged to take advantage of advances in processing and storage.  In this model, data is extracted from the source system to tables in the destination database.  Often these tables are copies of the original source system tables.  After successfully moving the data off of the production source systems.  The transformations take place on the database server itself.  With the final copy of the data being inserted, updated, or deleted.  To continue the customer example.  In an ELT model all customer data would be pulled over to destination tables first.  This enables the reporting to be run against the destination database instead of the OLTP application databases.  By eliminating any transformations during the data pull, you can connect to the data sources get the required data and close the connections as quickly as possible.  Then once the data is stored in the destination tables.  Transformations can be run to get the data into the final format required for your reporting application.

Figure 2: