1

I think I am confusing things.

In ETL, data conversion starts at Database A and ends up in Database B, both are relational databases. Maybe I have a 20-year-old system. I use ETL to get the data between the two databases. I do not know what else ETL is used for.

In ELT, is that the same except the data from Database A never ends up in Database B? Instead, the raw data is sitting in tables or some other data structure on Server B, from Database A, but requires something like Hadoop to transform that data into something an application that is specifically designed to use Hadoop?

Edit: I have done it again. Hadoop != ELT. I was looking only at Hadoop and thought it was ELT or the manifestation of it. edit: and that ELT meant you required a unique non-RDBMS file data structure instead of an RDBMS and you dumped the RDBMS altogether.

3

I think this will be easier with an example:

Database A has table C. Database B has table D. C and D are very similar but not identical and the data needs to be cleaned up before being loaded in to D.

  • ETL

    An ETL program (SSIS, Pentaho, whatever) pulls the data from table C. It then makes some changes to the data to clean it up and put it in the format that is needed. The ETL process then moves/copies that data up to table D on Database B.

  • ELT

    The data from Table C is extracted. It is uploaded to Database B as table C. Database B now has two tables, C and D. A database process (SQL, a job, whatever) now makes some changes to the data in table C and puts it in the format that is needed. That same process then copies the now clean data in to table D. The temporary Table C is no longer needed in Database B and can be deleted or truncated.

In both cases the data is now loaded in Database B, Table D. The front end application continues to use Table D.

2

In both ETL and ELT the data from A ends up in B. No, you don't need Hadoop for ELT. Actually, using Hadoop between two databases would be more like ETL.

Maybe a diagram would help: enter image description here

  • How could Hadoop be more like ETL? It puts it in its own file system, or so I thought (my first mistake, thinking) and then transforms the data by its proprietary, but Open Source, query language, HQL (or some other flavor). – johnny Oct 12 '17 at 17:00
  • 1
    A common case when using the Hadoop framework is to extract the data, perform some large operation on it, then put it somewhere else. That sounds a lot like ETL! You can of course use tools within the Hadoop framework as your data warehouse (target database) so it could also function as ELT. – CalZ Oct 12 '17 at 17:02
0

TLDR; You are overthinking it. ETL is just a concept, moving any data from one place to another. Whether you Extract then Load, then Transform, or Extract, Transform, or Load, it's all the same.

ETL stands for Extract, Transform and Load, which is a process used to collect data from various sources, transform the data depending on business rules/needs and load the data into a destination database. The need to use ETL arises from the fact that in modern computing business data resides in multiple locations and in many incompatible formats. For example business data might be stored on the file system in various formats (Word docs, PDF, spreadsheets, plain text, etc), or can be stored as email files, or can be kept in a various database servers like MS SQL Server, Oracle and MySQL for example. Handling all this business information efficiently is a great challenge and ETL plays an important role in solving this problem.

Extract, Transform and Load

The ETL process has 3 main steps, which are Extract, Transform and Load.

Extract – The first step in the ETL process is extracting the data from various sources. Each of the source systems may store its data in completely different format from the rest. The sources are usually flat files or RDBMS, but almost any data storage can be used as a source for an ETL process.

Transform – Once the data has been extracted and converted in the expected format, it’s time for the next step in the ETL process, which is transforming the data according to set of business rules. The data transformation may include various operations including but not limited to filtering, sorting, aggregating, joining data, cleaning data, generating calculated data based on existing values, validating data, etc.

Load – The final ETL step involves loading the transformed data into the destination target, which might be a database or data warehouse.

Source http://www.sql-tutorial.net/ETL.asp

  • When I look at something like Hadoop, they seem very different. – johnny Oct 12 '17 at 16:54
  • Different tool. Same idea. – Anthony Genovese Oct 12 '17 at 16:55
-1

ELT, "extract, load, transform" does not actually exist and can be entirely ignored. The wikipedia article has no citations on it, and it is only mentioned on the website "smartdatacollective.com". It's likely marketing wank or a clever troll.

But let's entertain it, what would it even look like...

The whole idea is such that you do not transform the input before putting it into the store, or "lake." That's not a new paradigm or deserving of a new term. We have databases that do that, namely, the filesystem. And we do not call cp an ELT script.

The whole idea that you would do this with intent is pretty awkward too. It's just an attempt at dignifying bad practice. Given a database inode, xmlblob_Lake, I would think every established database administrator would cringe in response and not think "wow cool use of ELT".

  • Looking at Hadoop and a "Data Lake" seems like a big bet against a "regular" RDBMS that uses SQL and all the stuff a DBA is used to. It looks like Hadoop (just an example, I'm sure there are tons like it) gets the data from all different databases, puts them in its file type, compresses, etc. and then lets you use its version of SQL (HQL, and others), but in reality. It is not in any meaningful schema like I think of a schema. – johnny Oct 12 '17 at 16:58
  • 1
    Everyone dba gets data from all different sources. Nothing magically makes it useful. Cat /dev/urandom and ask for Shakespeare and you still have to answer the question 'what am I looking for?', and 'where am I looking for it?' If you don't know anything about the way the data is stored because you haven't transformed it into something you can query -- good luck. – Evan Carroll Oct 12 '17 at 17:02
  • You mean, "Know your data"? :) – johnny Oct 12 '17 at 17:08
-2

There are many ways i can think of having an ELT system and also treating Hadoop as an ELT design.

  1. Data warehouses with very high quality of keys across all application databases.
  2. Types of transformations which can be hundreds of ways, based on how the user wants to look at information. Users accesses data based on predefined list of transformations (For example, in a Financial Planning and Analysis reporting, we could have Calculations at Year-To-Date, YTD-1, YTD-2, YTD-3, QTD, QTD-1, QTD-2, QTD3, Month-To-Date..... 100s of such calculations are used by Analysts)

A Hadoop implementation generally gets data from many data sources. And having a data extraction layer such as Pig or Hive performing the transformations is quite similar to an ELT design.

As for a differentiation between ETL & ELT, it is based on scope of design.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.