What are the advantages and disadvantages of using ETL vs ELT tools and frameworks?

The difference is where you invest time and effort to wrangle data. Whoever designed this AI exprience should reflect on data literacy! It IS NOT the number of characters that determine the value of a post. I mean I could just type 234 letters randomly and you would be happy with the entry…

I have to disagree. Before the data is reliable, usable and of value there are still three steps? The only difference here between ELT and ETL is exactly in the acronym. The end of the acronym should be U for Use. Also, there are many ways to parallelize this process and a combination of both is normally the most performant and gives the most reliable and usable data in the fastest way. Certain slowly dynamic datasets and datasets of transactional data should be considered for the approach to decide what should be transformed first and what should be transformed later (example would be adress data linked to a customer, the customer is pretty static and the address is relatively dynamic). My opinion..

Answers to community questions are being used to train generative AI. Don't give your unique voice and experience to machines.

This article presents ETL as relying solely on specialized tools and scripts for transformation, while ELT uses pre-built transformational tools. This is not entirely accurate as both ETL and ELT can use a combination of tools for transformation. It also suggests that ELT is more easily scalable, but in reality, both ETL and ELT can be parallelized for all steps, regardless of the order of E/T/L. It additionally fails to acknowledge that loading the original extracted data can be important for certain business use cases, such as compliance regulations, preserving for future transformations, or debugging. This step need not be time-intensive, as it can be asynchronous or delayed until the ingestion of transformed data is complete.

I would say where you invest compute power.... As the time and effort would likely be similar. More compute power == faster more reliable and that means up to date, almost real time reliable results.

ETL (Extract, Transform, Load) processes data before it enters the data warehouse, offering advantages like cleaner data, reduced storage requirements, and the ability to handle complex transformations. However, it can be slower due to upfront processing and less flexible when business needs change. ELT (Extract, Load, Transform) loads data directly into the data lake or warehouse, making it faster and more adaptable to changes. It supports massive data volumes and real-time processing but can increase storage costs and requires powerful processing capabilities within the data storage environment.

ELT (Extract, Load, Transform) offers rapid data loading and flexibility in handling and querying raw data, making it well-suited for big data and real-time analytics. It leverages the processing power of modern data warehouses to transform data, facilitating scalability and reducing the need for separate transformation servers. However, ELT can pose data quality challenges, as transformations occur after loading, potentially impacting analysis with unclean data. It also requires strong governance to manage data access and security, given the storage of raw data in the warehouse.

Instead of both of these approaches, a connector / adaptor pattern that could be used to access the data in it's native form and utilise the compute power to bypass the whole process, work in near real-time and perform the aggregations required on demand for the actual output that you want to show / use. This is how a lot of real time risk systems work in banking and finance because the ETL / ELT process is slow, batch oriented (normally) and doesn't produce results quickly enough. By observing the variables that impact other outputs and mapping dependency tree knowledge on the algorithms coupled with having dynamic scalability and compute power, why bother loading all the data?