Extract, transform, load
- Extracts data from homogeneous or heterogeneous data sources
- Transforms the data for storing it in proper format or structure for querying and analysis purpose
- Loads it into the final target (database, more specifically, operational data store, data mart, or data warehouse)
Usually all the three phases execute in parallel since the data extraction takes time, so while the data is being pulled another transformation process executes, processing the already received data and prepares the data for loading and as soon as there is some data ready to be loaded into the target, the data loading kicks off without waiting for the completion of the previous phases.
ETL systems commonly integrate data from multiple applications(systems), typically developed and supported by different vendors or hosted on separate computer hardware. The disparate systems containing the original data are frequently managed and operated by different employees. For example a cost accounting system may combine data from payroll, sales and purchasing.
The first part of an ETL process involves extracting the data from the source system(s). In many cases this represents the most important aspect of ETL, since extracting data correctly sets the stage for the success of subsequent processes.
Most data-warehousing projects consolidate data from different source systems. Each separate system may also use a different data organization and/or format. Common data-source formats include relational databases, XML and flat files, but may also include non-relational database structures such as Information Management System (IMS) or other data structures such as Virtual Storage Access Method (VSAM) or Indexed Sequential Access Method (ISAM), or even formats fetched from outside sources by means such as web spidering or screen-scraping. The streaming of the extracted data source and loading on-the-fly to the destination database is another way of performing ETL when no intermediate data storage is required. In general, the extraction phase aims to convert the data into a single format appropriate for transformation processing.
An intrinsic part of the extraction involves data validation to confirm whether the data pulled from the sources have the correct/expected values in a given domain (such as a pattern/default or list of values). If the data fails the validation rules it may be rejected entirely or in part. The rejected data is ideally reported back to the source system for further analysis to identify and to rectify the incorrect records. In some cases the extraction process itself may have to modify a data-validation rule in order to accept the data to flow to the next phase. In some cases it is not rejected or modified because removing records with invalid values may skew statistical analyses on the final datasets. In these cases, invalid values can be flagged in separate columns or fields.
In the data transformation stage, a series of rules or functions is applied to the extracted data in order to prepare it for loading into the end target. Some data do not require any transformation at all; such data are known as "direct move" or "pass through" data.
An important function of transformation is the cleaning of data, which aims to pass only "proper" data to the target. The challenge when different systems interact is in the relevant systems' interfacing and communicating. Character sets that may be available in one system may not be available in others.
In other cases, one or more of the following transformation types may be required to meet the business and technical needs of the server or data warehouse:
- Selecting only certain columns to load: (or selecting null columns not to load). For example, if the source data has three columns (aka "attributes"), roll_no, age, and salary, then the selection may take only roll_no and salary. Or, the selection mechanism may ignore all those records where salary is not present (salary = null).
- Translating coded values: (e.g., if the source system codes male as "1" and female as "2", but the warehouse codes male as "M" and female as "F")
- Encoding free-form values: (e.g., mapping "Male" to "M")
- Deriving a new calculated value: (e.g., sale_amount = qty * unit_price)
- Sorting: Order the data based on a list of columns to improve search performance
- Joining data from multiple sources (e.g., lookup, merge) and deduplicating the data
- Aggregation (for example, rollup — summarizing multiple rows of data — total sales for each store, and for each region, etc.)
- Generating surrogate-key values
- Transposing or pivoting (turning multiple columns into multiple rows or vice-versa)
- Splitting a column into multiple columns (e.g., converting a comma-separated list, specified as a string in one column, into individual values in different columns)
- Disaggregation of repeating columns into a separate detail table (e.g., moving a series of addresses in one record into single addresses in a set of records in a linked address table)
- Look up and validate the relevant data from tables or referential files for slowly changing dimensions.
- Applying any form of data validation. Failed validation may result in a full rejection of the data, partial rejection or no rejection at all, and thus none, some or all of the data are handed over to the next step depending on the rule design and exception handling. Many of the above transformations may result in exceptions, for example, when a code translation parses an unknown code in the extracted data.
The load phase loads the data into the end target that may be a simple delimited flat file or a data warehouse. Depending on the requirements of the organization, this process varies widely. Some data warehouses may overwrite existing information with cumulative information; updating extracted data is frequently done on a daily, weekly, or monthly basis. Other data warehouses (or even other parts of the same data warehouse) may add new data in a historical form at regular intervals—for example, hourly. To understand this, consider a data warehouse that is required to maintain sales records of the last year. This data warehouse overwrites any data older than a year with newer data. However, the entry of data for any one year window is made in a historical manner. The timing and scope to replace or append are strategic design choices dependent on the time available and the business needs. More complex systems can maintain a history and audit trail of all changes to the data loaded in the data warehouse.
When keeping historical data, it is important to understand that in most cases the data must represent its state at a point in time. In other words, the data represents a snapshot at any given point in time. For example, If a customer's city was "Boston" in 1996 but "Houston" in 1997, then the original address of the customer has to be maintained for queries that involve locations in 1996. This is an important design consideration for data warehouses and especially in dimensional models where history must be maintained in slowly changing dimensions.
As the load phase interacts with a database, the constraints defined in the database schema — as well as in triggers activated upon data load — apply (for example, uniqueness, referential integrity, mandatory fields), which also contribute to the overall data quality performance of the ETL process.
- For example, a financial institution might have information on a customer in several departments and each department might have that customer's information listed in a different way. The membership department might list the customer by name, whereas the accounting department might list the customer by number. ETL can bundle all of these data elements and consolidate them into a uniform presentation, such as for storing in a database or data warehouse.
- Another way that companies use ETL is to move information to another application permanently. For instance, the new application might use another database vendor and most likely a very different database schema. ETL can be used to transform the data into a format suitable for the new application to use.
- An example of this would be an Expense and Cost Recovery System (ECRS) such as used by accountancies, consultancies and lawyers. The data usually end up in the time and billing system, although some businesses may also utilize the raw data for employee productivity reports to Human Resources (personnel dept.) or equipment usage reports to Facilities Management.
Real-life ETL cycle
The typical real-life ETL cycle consists of the following execution steps:
- Cycle initiation
- Build reference data
- Extract (from sources)
- Transform (clean, apply business rules, check for data integrity, create aggregates or disaggregates)
- Stage (load into staging tables, if used)
- Audit reports (for example, on compliance with business rules. Also, in case of failure, helps to diagnose/repair)
- Publish (to target tables)
- Clean up
ETL processes can involve considerable complexity, and significant operational problems can occur with improperly designed ETL systems.
The range of data values or data quality in an operational system may exceed the expectations of designers at the time validation and transformation rules are specified. Data profiling of a source during data analysis can identify the data conditions that must be managed by transform rules specifications. This leads to an amendment of validation rules explicitly and implicitly implemented in the ETL process.
Data warehouses are typically assembled from a variety of data sources with different formats and purposes. As such, ETL is a key process to bring all the data together in a standard, homogeneous environment.
Design analysts should establish the scalability of an ETL system across the lifetime of its usage. This includes understanding the volumes of data that must be processed within service level agreements. The time available to extract from source systems may change, which may mean the same amount of data may have to be processed in less time. Some ETL systems have to scale to process terabytes of data to update data warehouses with tens of terabytes of data. Increasing volumes of data may require designs that can scale from daily batch to multiple-day micro batch to integration with message queues or real-time change-data capture for continuous transformation and update.
ETL vendors benchmark their record-systems at multiple TB (terabytes) per hour (or ~1 GB per second) using powerful servers with multiple CPUs, multiple hard drives, multiple gigabit-network connections, and lots of memory. The fastest ETL record is currently held by Syncsort, Vertica and HP at 5.4TB in under an hour, which is more than twice as fast as the earlier record held by Microsoft and Unisys.
In real life, the slowest part of an ETL process usually occurs in the database load phase. Databases may perform slowly because they have to take care of concurrency, integrity maintenance, and indices. Thus, for better performance, it may make sense to employ:
- Direct Path Extract method or bulk unload whenever is possible (instead of querying the database) to reduce the load on source system while getting high speed extract
- Most of the transformation processing outside of the database
- Bulk load operations whenever possible.
Still, even using bulk operations, database access is usually the bottleneck in the ETL process. Some common methods used to increase performance are:
- Partition tables (and indices). Try to keep partitions similar in size (watch for null values that can skew the partitioning).
- Do all validation in the ETL layer before the load. Disable integrity checking (disable constraint ...) in the target database tables during the load.
- Disable triggers (disable trigger ...) in the target database tables during the load. Simulate their effect as a separate step.
- Generate IDs in the ETL layer (not in the database).
- Drop the indices (on a table or partition) before the load - and recreate them after the load (SQL: drop index ...; create index ...).
- Use parallel bulk load when possible — works well when the table is partitioned or there are no indices. Note: attempt to do parallel loads into the same table (partition) usually causes locks — if not on the data rows, then on indices.
- If a requirement exists to do insertions, updates, or deletions, find out which rows should be processed in which way in the ETL layer, and then process these three operations in the database separately. You often can do bulk load for inserts, but updates and deletes commonly go through an API (using SQL).
Whether to do certain operations in the database or outside may involve a trade-off. For example, removing duplicates using distinct may be slow in the database; thus, it makes sense to do it outside. On the other side, if using distinct significantly (x100) decreases the number of rows to be extracted, then it makes sense to remove duplications as early as possible in the database before unloading data.
A common source of problems in ETL is a big number of dependencies among ETL jobs. For example, job "B" cannot start while job "A" is not finished. One can usually achieve better performance by visualizing all processes on a graph, and trying to reduce the graph making maximum use of parallelism, and making "chains" of consecutive processing as short as possible. Again, partitioning of big tables and of their indices can really help.
Another common issue occurs when the data are spread among several databases, and processing is done in those databases sequentially. Sometimes database replication may be involved as a method of copying data between databases - and this can significantly slow down the whole process. The common solution is to reduce the processing graph to only three layers:
- Central ETL layer
This allows processing to take maximum advantage of parallel processing. For example, if you need to load data into two databases, you can run the loads in parallel (instead of loading into 1st - and then replicating into the 2nd).
Sometimes processing must take place sequentially. For example, dimensional (reference) data are needed before one can get and validate the rows for main "fact" tables.
A recent[update] development in ETL software is the implementation of parallel processing. This has enabled a number of methods to improve overall performance of ETL processes when dealing with large volumes of data.
ETL applications implement three main types of parallelism:
- Data: By splitting a single sequential file into smaller data files to provide parallel access.
- Pipeline: Allowing the simultaneous running of several components on the same data stream. For example: looking up a value on record 1 at the same time as adding two fields on record 2.
- Component: The simultaneous running of multiple processes on different data streams in the same job, for example, sorting one input file while removing duplicates on another file.
All three types of parallelism usually operate combined in a single job.
An additional difficulty comes with making sure that the data being uploaded is relatively consistent. Because multiple source databases may have different update cycles (some may be updated every few minutes, while others may take days or weeks), an ETL system may be required to hold back certain data until all sources are synchronized. Likewise, where a warehouse may have to be reconciled to the contents in a source system or with the general ledger, establishing synchronization and reconciliation points becomes necessary.
Data warehousing procedures usually subdivide a big ETL process into smaller pieces running sequentially or in parallel. To keep track of data flows, it makes sense to tag each data row with "row_id", and tag each piece of the process with "run_id". In case of a failure, having these IDs help to roll back and rerun the failed piece.
Best practice also calls for checkpoints, which are states when certain phases of the process are completed. Once at a checkpoint, it is a good idea to write everything to disk, clean out some temporary files, log the state, and so on.
As of 2010[update] data virtualization had begun to advance ETL processing. The application of data virtualization to ETL allowed solving the most common ETL tasks of data migration and application integration for multiple dispersed data sources. So-called Virtual ETL operates with the abstracted representation of the objects or entities gathered from the variety of relational, semi-structured and unstructured data sources. ETL tools can leverage object-oriented modeling and work with entities' representations persistently stored in a centrally located hub-and-spoke architecture. Such a collection that contains representations of the entities or objects gathered from the data sources for ETL processing is called a metadata repository and it can reside in memory or be made persistent. By using a persistent metadata repository, ETL tools can transition from one-time projects to persistent middleware, performing data harmonization and data profiling consistently and in near-real time.
Dealing with keys
Keys are some of the most important objects in all relational databases, as they tie everything together. A primary key is made up of one or more columns that uniquely identify each row in a table. Keys that are made up of more than one column are called composite keys.
A foreign key is made up of one or more columns in a table, say Table_A, that refers to the primary key of another table, say Table_B. Note that Table_A and Table_B can be same, in which case the table is said to have a self referencing foreign key. An easy example is the foreign key that depicts "Line Manager of Person_A is Person_B", with both Person_A and Person_B are in the same PERSON table.
In many tables one will find that the primary key is a single column containing a machine generated integer that gives absolutely no clue as to what each row of the table represents. Such machine-generated and meaning-free integers are commonly called surrogate id-s or surrogate key-s. In contrast primary keys made up of natural key column/s do reveal - or at least give reasonable clue to - what each row of the table stands for. For example, if a column named customer_id/num is the primary of a table, one can make the reasonable guess that the table has one row per customer. Likewise if the primary key of a table is said to be "order_number, order_line" pair of columns of a table one could make the reasonable guess that each row of that table represents an order line, AND THAT order_number is in all probability the primary key for another table each row of which would hold the order header details. In contrast if a table has a surrogate id/key one cannot readily tell anything about the table.
It should be noted that machine-generation of surrogate id/key implies that there must exist logic does a one-to-one correlation between the natural key (made up of one or more columns) and the surrogate id/key (which of course is a single column). By definition, surrogate id-s are a short-hand for the composite keys (which could be made up of large number of columns).
This concise short-hand form of identifying is very useful when relating transaction records to numerous concepts or notions by which the transaction records might be analysed. For example, the "sales invoice line" transaction data will typically be analysed using the following concepts or notions:
(1) the date of sale (2) product/item sold (3) store that sold (4) the sold-to customer
to pose queries such as (but not limited) ..
q1. what was the total $-sale for a period? q2. how much did customer-x buy from each of the stores? q3. what is the sales trend of a product on a week-by-week basis? q4. what is the $-sale by state (assuming that the state of the selling store is present in the data warehouse)
In data warehousing context, the concepts and notions by which transaction data is analysed are called "dimensions", and the transaction data is called the measures or metrics or facts. 
Analysing for patterns requires capturing relevant attributes about the dimensions. And analysing for changes in patterns over time warrants tracking changes in the attributes of the dimensions. For example, did the sale increase or decrease when the store was relocated from a normal street into a shopping mall? Did the beer sell more or less after the change from 4-pack to 6-pack?
A recommended way to track changes in dimensions is to utilise the technique of assigning surrogate id-s to rows in the dimension tables and using these to relate measure/metric/fact rows to the dimensions. For each of the dimnsions, the ETL process uses the appropriate logic to determine whether a new row is warranted (i.e., whether there is a change that is to be tracked) and assigns the next available surrogate id/num. There are different types of change tracking - the details of which are best dealt with separately. Refer to www.kinballgroup.com for comprehensive coverage of change tracking in dimensions.
Programmers can set up ETL processes using almost any programming language, but building such processes from scratch can become complex. Increasingly, companies are buying ETL tools to help in the creation of ETL processes.
By using an established ETL framework, one may increase one's chances of ending up with better connectivity and scalability. A good ETL tool must be able to communicate with the many different relational databases and read the various file formats used throughout an organization. ETL tools have started to migrate into Enterprise Application Integration, or even Enterprise Service Bus, systems that now cover much more than just the extraction, transformation, and loading of data. Many ETL vendors now have data profiling, data quality, and metadata capabilities. A common use case for ETL tools include converting CSV files to formats readable by relational databases. A typical translation of millions of records is facilitated by ETL tools that enable users to input csv-like data feeds/files and import it into a database with as little code as possible.
ETL Tools are typically used by a broad range of professionals - from students in computer science looking to quickly import large data sets to database architects in charge of company account management, ETL Tools have become a convenient tool that can be relied on to get maximum performance. ETL tools in most cases contain a GUI that helps users conveniently transform data as opposed to writing large programs to parse files and modify data types—which ETL tools facilitate as much as possible.
Commercially available ETL tools include:
- North Concepts Data Pipeline
- Informatica PowerCenter
- IBM Datastage
- Ab Initio
- Oracle Data Integrator (ODI)
- Microsoft SQL Server Integration Services (SSIS)
- Pentaho Data Integration (or Kettle) opensource data integration framework
- Rhino ETL
- SAP Business Objects Data Services
- Clover ETL opensource engine supporting only basic partial functionality and not server
- Architecture Patterns (EA Reference Architecture)
- Create, read, update and delete (CRUD)
- Data cleansing
- Data integration
- Data mart
- Data mediation
- Data migration
- Electronic Data Interchange (EDI)
- Enterprise architecture
- Expense and Cost Recovery System (ECRS)
- Hartmann Pipeline
- Legal Electronic Data Exchange Standard (LEDES)
- Metadata discovery
- Online analytical processing
- Spatial ETL
- "New ETL World Record: 5.4 TB Loaded in Under 1 Hour - Syncsort"
- Virtual ETL
- Kimball, The Data Warehouse Lifecycle Toolkit
- ETL poll produces unexpected results