Updating ETL / ELT data pipeline practices for more scale & efficiency 

Today, data comes into organizations from many places, and is stored in many more different systems and storage solutions depending on use cases. This creates a need for connecting and moving data between systems that generate and collect data, systems that store data, systems that analyze data, and more.

Data pipelines are the traditional method of moving that data from a source usually to a storage system or analytical system, such as a database or data warehouse. ETL and ELT are simply types of data pipelines that specialize in moving data from systems that generate data, like SaaS Apps, into data warehouses. ETL refers to a case where data is transformed before being loaded, and ELT refers to when data is transformed after landing in destination.

A limitation of data pipelines, including ETL/ELT, is their rigid structure, which restricts the variety and quantity of data sources and destinations that can be integrated. In addition, building and maintaining many data pipelines between sources and destinations is highly difficult and expensive, both in compute costs and time. As more data comes into the picture from more data systems, the number of data pipelines grow exponentially with each new source and destination. Think of them as creating a spiderweb of complexity that is unsustainable at scale.

Downsides of Traditional Data Pipelines

• Fragile Connectors: Data may come from a warehouse, data lake, SaaS application, or any other form of data store. A pipeline needs to be built from each source, and each source needs a connector; therefore, in a multimodal network, data pipelines are fragile, leading to data losses and/or gaps in governance.
• Inefficient Data Ingestion: When new data is added, the entire history is loaded into the data store. This is called backloading. After that, the code for backloading is removed to reduce strain on the data pipeline. Regular data ingestion happens daily or as scheduled to capture new data. If the data pipeline is damaged, backloading is needed to restore its function.
• Pipelines are Built into a Single Script: Most data pipelines are built into a single script, so when the script fails for a certain data type, data engineers need to go back and troubleshoot the pipeline. Even after the associated errors are fixed, the complete data pipeline then requires manual auditing, which is time-consuming and cumbersome.
• Lack of Quality Assurance: Once data is ingested into a pipeline, performing transformations in real time can be impossible or difficult. Once an error is induced in the data, the error will trickle down through the other pipelines into analytical algorithms and/or operational systems.

Data Flow: The New Paradigm of Data Movement

For this reason, companies have been turning to data flows in recent years as a scalable and cost-effective solution for all their data movement needs. While the terms might sound interchangeable, data flows are a technical term for an evolved data pipeline that is more flexible and responsive for needs, unrestricted by type of data system at the source or destination. For this reason ETL, ELT, and other types of data pipelines aren’t relevant to data flows. 

Data flows are a solution to moving data that moves beyond the type of data and data system particulars at source and destination to provide one solution to any particular combination that has to be created between source and target system. Adopting a data flow approach to moving data between systems allows companies to manage costs with growth while future proofing for new data sources and targets.

Benefits of Data Flows

• Bidirectional Flow: Operational data needs real-time or near-real-time processing, while for analytical data, batch-based processing is performed. Bidirectional data flows can help eliminate data sprawl. Using data flows, records can be requested at any time after a flow is built.
• Self-Service: Data flows can be automated based on each endpoint; hence, if a business leader wants to view customer data from the last week, they can do so without manually extracting the data and pipelining it through the process by using data flows. A low-code/no-code data flow reduces the required build time and enables inspection of the output.
• Governance and Access: Data flows create improved data governance. Bidirectional connectors enable users to use data as required, rather than creating copies of data to improve processing speeds. Access can be measured and controlled based on users and requirements without any extra layers of authentication.
• Branches from Data: Data flows can empower operational systems. Suppose that you run an eCommerce website on which a customer is facing an order delay. Customer support in a traditional pipeline environment would take hours to pull the associated data from logistics. But with data flows, customer support can obtain the order details and logistics data while the marketing department can also acquire a slice of that data to run their analytics.

What to Look for in a Data Flow Solution

With the limitations in traditional data pipelines and recent advancement in technology to build and manage data flows instead, it’s clear why companies are adopting a data flow management approach rather than combining multiple data pipeline styles as a band-aid solution. In fact, companies are beginning to move even beyond the data flow by instead building and managing data products, an abstraction of the data itself that can be easily plugged into any needed data flow. These advancements are the answer to build scalable, cost-effective solutions that address the inevitable growth in the data and data systems that any initiative will eventually require.