Exploring the 3 Eras of ELT: From On-Premises to SQL ELT

The emergence of new technologies, such as generative artificial intelligence (genAI), large language models (LLMs) and other machine learning  (ML) models, has made data integration and ingestion even more powerful. For example, an AI model may not serve its full purpose without the data needed to train and evaluate the accuracy of the model. In fact, the success of these AI/ML initiatives depends on how efficiently they get access to timely and trusted data.

For AI/ML projects to be successful, they require the processing of huge volumes of both structured and unstructured data at various data lakes, data warehouses and data lakehouses. That’s why extract, load, transform (ELT) — which optimizes performance and saves time — is best suited for these complex use cases. And with the evolution of data storing and processing capabilities of the data lake and warehouse, adoption of ELT processes have become more widespread. Let’s look at how ELT has changed over the years.

While ELT is not new, the technologies that support ELT have evolved over time. One notable advancement in ELT is the concept of pushdown technology. This refers to the ability to push certain transformation tasks directly to the data source rather than moving all the data to a separate processing engine.

By adopting pushdown capabilities in your ELT processes, you can enhance efficiency and reduce costs associated with unnecessary data transfers. This approach enables faster processing time and helps organizations make better use of existing infrastructure. Let’s walk through the three eras of ELT.

1. On-premises ELT: For years traditional data warehouses were built on-premises. Some examples include Oracle, SQL server and MySQL databases. Organizations used to spend capital expenses on procuring their own server and installing enterprise relational database licenses. To optimize cost and performance, they started to use pushdown to leverage the full compute of their servers to load and transform their business data. And yes, for more than 20 years, we have had our own on-premises ETL tool — Informatica PowerCenter — that, to this day, still uses relational pushdown to the source or target databases.
2. ELT on Spark or Hadoop: As data analytics became more advanced, companies started to lean toward Big Data infrastructure. In fact, we saw a technology and architecture shift when our customers started to migrate to open-source technology like Hadoop and Spark engines. To address such workloads, Informatica introduced our next-generation pushdown version of ELT on Spark. As an open-source analytics engine, Spark processes data much faster with horizontal scaling. It can also be customized and run on commodity hardware. This means improved performance and lower costs.  
3. ELT on the cloud: Over the last 10 years, organizations are generating even bigger amounts of data for AI/ML analytics. What’s needed to keep up is a scalable and flexible infrastructure to support these changes. With that in mind, companies are adapting to cloud solutions such as Google Cloud, Amazon Web Services (AWS) and Microsoft Azure. One of the key challenges? Moving the data efficiently and securely.

This is where ELT and pushdown technologies come into play. This refers to when data is ingested once in the cloud and transformed every time at the place where data resides, without moving the actual data in and out of the cloud. This saves thousands of dollars in egress charges. Plus, pushdown technology allows for more direct access to the source system than traditional ETL tools because it pushes part of the transformation down to the source system itself.

We reviewed the three eras of ELT: ELT on-premises, ELT on Spark and Hadoop, and ELT on the cloud. Now, let’s discover the next iteration of ELT innovation with Informatica SQL ELT.

To keep pace with the changing technology landscape, Informatica created a modern pushdown capability, SQL ELT, on Informatica Cloud Data Integration, a service of our Intelligent Data Management Cloud (IDMC). Our SQL ELT capability is built to natively support cloud data lake (CDL) to cloud data warehouse (CDW) or CDW to CDW use cases. It does this by pushing down compute workloads to underlying cloud data warehouses like Snowflake, Amazon Redshift, Azure Synapse, Databricks Delta or Google BigQuery.

Depending on the use case, you can choose the extract, transform, load (ETL) or ELT process for your data pipeline with Informatica Cloud Data Integration. In fact, Informatica Cloud Data Integration offers different ingestion and integration features and advanced pushdown optimization capabilities that can elevate productivity and cut costs — must haves in today’s fast-paced market.  

SQL ELT is especially popular with developers because of its advanced features and AI capabilities. One key advantage is improved efficiency in data processing and transformation. In fact, according to Nucleus Research¹, who interviewed Informatica Cloud Data Integration customers, developers observed 28% more efficiency by designing mapping using the intuitive Informatica graphical mapping designer.

Some key benefits of SQL ELT? Developers can set full ELT mode as a runtime property and preview the code before running it in production. This helps streamline data workflows and optimize the execution of complex data integration tasks — critical to compete in a digital economy.

Plus, Informatica SQL ELT helps reduce latency and egress costs by enabling data processing to run directly within the target database. This eliminates the need for unnecessary data movement between systems and minimizes network overhead. Case in point: According to the Nucleus report, organizations experienced 63% lower latency on average. In fact, one company reduced their data egress cost by $50,000 annually. Another organization would have had to spend an additional $400,000 to achieve the desired latency without Informatica SQL ELT.

Another finding in the Nucleus report? Informatica SQL ELT supports centralized security and governance. As part of IDMC, SQL ELT provides robust security measures to protect sensitive data throughout the entire ELT process. With comprehensive access controls and auditing capabilities, organizations can ensure compliance with regulations while maintaining a secure environment for their data.

Bottom line? Using Informatica SQL ELT can help improve efficiency, reduce latency and decrease associated costs. This alone can give you a competitive edge to stand out in a crowded landscape.

Ready to take your integration processes to the next level?

• See SQL ELT in action now with a free, 30-day trial of Informatica Cloud Data Integration. 
• Experience SQL ELT with up to 20 million rows free per month with Informatica Cloud Data Integration-Free.

_{¹Nucleus Research, ROI Guidebook: Informatica Cloud Data Integration Services, February 2023.}