The most straightforward yet profound question for newcomers in data engineering is: What is the difference between ETL and ELT?

You can work with tools like dbt and data warehouses without actually considering the difference, but understanding it is crucial as it leads to the right tool choice depending on the use case and requirements.

You might think of ETL as an older concept, from the time when data warehouses were used primarily for storing the results of the Transformation step. This required a powerful ETL server capable of processing the same volume of data, ready to read each record and every row in a table or file. With large volumes of data, this could be expensive.

However, with the rise of Cloud and Cloud Data Warehousing, the need for powerful ETL compute has diminished. Now, we can simply COPY data into cloud storage and then into the cloud data warehouse. After this, we can leverage the powerful compute capabilities of distributed cloud data warehouses or SQL engines.

The advent of cloud computing wasn't the only pivotal moment. Even before the cloud, ETL tools like Informatica employed a 'push down' approach, pushing all data into MPP data warehouses like Teradata, and then orchestrating SQL transformations.

Let's consider a simple example:

In the case of ETL:
1. Extract Orders and Products data.
2. Transform the data (join, clean, aggregate).
3. Load the data into the data warehouse, often using INSERT (a slower, row-by-row process).

In the case of ELT:
1. Extract Orders and Products data.
2. Load the data into the data warehouse, often using COPY for storage accounts and data warehouses (a faster, bulk load process).
3. Transform with SQL or tools like DBT or Dataframes.

Reflecting on the role of Spark, it becomes clear that Spark is an actual ETL tool since it reads the data when performing transformations.

Link to share to like: https://www.linkedin.com/posts/dmitryanoshin_etl-elt-dataengineering-activity-7137583690678747136-7imR