{"id":2928,"date":"2013-08-30T16:06:28","date_gmt":"2013-08-30T20:06:28","guid":{"rendered":"http:\/\/www.iri.com\/blog\/?p=2928"},"modified":"2020-08-21T15:36:26","modified_gmt":"2020-08-21T19:36:26","slug":"etl-vs-elt-we-posit-you-judge","status":"publish","type":"post","link":"https:\/\/beta.iri.com\/blog\/data-transformation2\/etl-vs-elt-we-posit-you-judge\/","title":{"rendered":"ETL vs ELT: We Posit, You Judge"},"content":{"rendered":"

Full disclosure: As this article is authored by an ETL-centric company with its strong suit in manipulating big data outside of databases, what follows will not seem objective to many. Nevertheless it is still meant to present food for thought, and opens the floor to discussion.<\/em><\/p>\n

Since their beginnings, data warehouse architects (DWA) have been tasked with creating and populating a data warehouse with disparately sourced and formatted data. \u00a0Due to dramatic growth in data volumes, these same DWAs are challenged to implement their data integration and staging operations more efficiently. \u00a0The question of whether data transformation<\/a> will occur inside or outside the target database has become a critical one because of the performance, convenience, and financial consequences involved.<\/p>\n

In ETL<\/span> (extract, transform, load) operations, data are extracted from different sources, transformed separately, and loaded to a DW database and possibly other targets. In ELT<\/span>, the extracts are fed into the single staging database that also handles the transformations.<\/p>\n

ETL remains prevalent because the marketplace flourishes with proven players like Informatica, IBM, Oracle\u00a0— and\u00a0IRI with Voracity<\/a>, which combines\u00a0FACT<\/a> (Fast Extract), CoSort<\/a> or Hadoop<\/a> transforms, and bulk loading in the same Eclipse GUI<\/a>\u00a0—\u00a0to extract and transform<\/a> data. This approach prevents burdening databases designed for storage and retrieval (query optimization) with the overhead of large-scale data transformation.<\/p>\n

<\/div>\n
\"\"<\/a>
ETL Diagram<\/figcaption><\/figure>\n

However, with the development of new database technology and hardware appliances like Oracle Exadata that can handle transformations ‘in a box’, ELT may be a practical solution under certain circumstances. And there are specific benefits<\/a> to isolating the staging (load) and semantic (transform) layers.<\/p>\n

\n
\n
\"\"<\/a>
ELT Diagram<\/figcaption><\/figure>\n

A cited advantage of ELT is the isolation of the load process from the transformation process, since it removes an inherent dependency between these stages.<\/p>\n

We note that IRI’s ETL approach isolates them anyway because Voracity stages data in the file system (or HDFS). Any data chunk bound for the database can be acquired, cleansed, and transformed externally prior to a (pre-sorted) load. This takes the burden of large scale transforms off the database (as well as BI\/analytic tools, etc.).<\/p>\n

Data volumes and budgets are often what determines whether a DWA should develop a ETL or ELT solution. In his ITToolbox blog article “So What Is Better, ETL or ELT?”,\u00a0Vincent McBurney<\/a>\u00a0posits his pros and cons to either approach, which I’ve repeated here below, and then followed each with\u00a0a typical response that IRI ETL-oriented users make on point\u00a0<\/em>(per my initial subjectivity caveat):<\/p>\n

\n
Pros ETL<\/strong><\/div>\n