Extract, transform, load
The separate systems containing the original data are frequently managed and operated by different stakeholders.
In many cases, this represents the most important aspect of ETL, since extracting data correctly sets the stage for the success of subsequent processes.
Common data-source formats include relational databases, flat-file databases, XML, and JSON, but may also include non-relational database structures such as IBM Information Management System 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 a web crawler or data scraping.
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.
Design analysis[5] should establish the scalability of an ETL system across the lifetime of its usage – including understanding the volumes of data that must be processed within service level agreements.
If the primary key of the source data is required for reporting, the dimension already contains that piece of information for each row.
In real life, the slowest part of an ETL process usually occurs in the database load phase.
Thus, for better performance, it may make sense to employ: Still, even using bulk operations, database access is usually the bottleneck in the ETL process.
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.
This enables a number of methods to improve overall performance of ETL when dealing with large volumes of data.
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.
Once at a checkpoint, it is a good idea to write everything to disk, clean out some temporary files, log the state, etc.
[citation needed] 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.
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 them into a database with as little code as possible.
An ETL instance can be used to periodically collect all of these batches, transform them into a common format, and load them into a data lake or warehouse.
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.
[1] Some of the benefits of an ELT process include speed and the ability to more easily handle both unstructured and structured data.
[12] Cloud-based data warehouses like Amazon Redshift, Google BigQuery, Microsoft Azure Synapse Analytics and Snowflake Inc. have been able to provide highly scalable computing power.
