Data orchestration vs. ETL: What’s the difference?

Both data orchestration and ETL enable collecting, uniting, and transforming data to extract business value. However, both of these processes have very different scopes and take different approaches. Here’s how they overlap, how they differ, and which one provides a more modern and reliable approach to managing data at scale.

What is ETL?

ETL, or Extract-Transform-Load, is a process used to extract data from one location, convert it into another format, and place it into another. It’s used most often to transfer data from multiple sources —e.g., relational databases, flat files, CSVs, etc.—into a target system.

As denoted by the acronym, the process has three central parts:

Extract

Pull the data from the host system or systems and put it into a temporary staging area. This can be done as a batch process (e.g., a job that runs and queries data every x hour) or via a real-time replication mechanism such as Change Data Capture (CDC).

Transform

Coerce the data into a different format suitable for the new workload. At this stage, the process also cleans the data, standardizing the use of types and formats and correcting any errors (e.g., null values in required fields).

Load

Data is brought into the destination, where it’s available for query by data consumers.

ETL was primarily a process used before the advent of cloud computing. Originally, it loaded data from online transaction processing (OLTP) systems into a relational format optimized for reporting.

What is data orchestration?

Data orchestration is the process of automating the flow of data systematically throughout an enterprise. It gathers data that was previously siloed and unites it into a single location, where it can be discovered and used by multiple teams.

Data orchestration consists of three phases:

Organization

A data pipeline gathers data from various places —flat files, relational databases, APIs, etc.—and collects it in a data warehouse or a data lake.

Transformation

As in ETL, data is unified, cleansed, and corrected across its multiple formats. Newly arriving data goes through a series of manual and automated quality checks before it’s considered valid and promoted to a production database.

Activation

Data is delivered and put to use by Business Intelligence tools or other data-driven applications.

In contrast to ETL, data orchestration typically relies on a related method called ELT (Extract-Load-Transform). In ELT, data is loaded as is into a data warehouse and then transformed into multiple formats depending on the use case. This enables different teams to use the same core data in different ways.

Data orchestration vs. ETL: The key differences

Data orchestration and ETL differ in two key facets: scope and management.

Scope

ETL jobs are often one-off processes that solve a specific problem—e.g., producing a quarterly sales report, a monthly report on patient outcomes at a hospital, etc.

By contrast, data orchestration is about managing the flow of data across the enterprise, not just for one single purpose. It aims to eliminate data silos, islands of independent data that are hard to discover and, more often than not, not properly monitored or governed.

Management

ETL jobs have a reputation for being brittle and requiring constant manual care to keep running. Traditional ETL tools also have difficulty managing the volume of data that most enterprises manage.

Data orchestration focuses on creating automated data pipelines that run periodically—either on a schedule or in real-time as new data arrives in its source systems. A well-designed data orchestration system provides tools for tracking the status of data pipeline jobs, issuing alerts when errors occur, and providing centralized observability via metrics and logs.

Development

ETL jobs are often developed solely by data engineers working alone based on loose requirements provided by a business user. This often results in a mismatch between what engineering delivers and the needs of the business. As a result, it can take weeks or even months to deliver a new pipeline to production.

Data orchestration platforms provide tools that enable engineers, analysts, and decision-makers to work together closely in short, rapid cycles. We call this the Analytics Development Lifecycle, or ADLC.

In the ADLC, all three of these personas take an active role in designing, implementing, approving, and unblocking the creation of new data transformation pipelines. Because data pipelines are highly automated, teams can deploy, test, and re-deploy changes rapidly, shaving weeks off of traditional data pipeline development.

The benefits of migrating from ETL to data orchestration

Some companies still use ETL for processing data in highly regulated industries, such as health care. Most companies, however, can benefit from moving to a comprehensive data orchestration platform.

For years, dbt has provided industry-standard technology for modeling and transforming data across the enterprise. dbt Cloud expands upon these base capabilities, creating a data control plane you can use to manage the flow of data across your enterprise.

Using dbt Cloud, you can forge a data orchestration platform that provides numerous benefits over using ETL:

• Accelerated data delivery
• Improved data quality
• Democratization of data
• Improved compliance and data governance

Accelerated data delivery

Because data orchestration pipelines are automated, they can sync more data, more reliably, and in a shorter time frame. If a problem arises, the data orchestration platform provides tools to issue a notification, perform root cause analysis, and get the pipeline back up and running quickly.

Using dbt Cloud, data engineers, analytics engineers, and even tech-savvy business users can create dbt models using SQL code. They can check their code to a source control system like GitHub and automatically deploy any data model changes through a Continuous Integration (CI) job.

Improved data quality

Because they’re often one-off jobs, data quality in ETL pipelines can be hit or miss. By contrast, data orchestration emphasizes providing data consumers with high-quality data with every job run.

Using dbt Cloud, teams can ensure that all data model changes are reviewed by other team members before they go live. Engineers can also create data tests that are run and verified in multiple environments before new transformations are run on production data.

Democratization of data

ETL pipelines don’t make it easy to find and work with data. A data orchestration platform provides easy-to-use tools to find, transform, and activate data.

With dbt Cloud, analysts and decision-makers don’t have to file a trouble ticket and wait days—or weeks—for a dedicated engineer to resolve an issue in a data pipeline. Since dbt uses familiar languages like SQL, any authorized user with the requisite knowledge can apply changes to a data pipeline.

Once data models are published to production, engineers, analysts, and decision-makers can discover them easily using dbt Explorer. Users can find data, read any associated documentation, view the data’s lineage graph to discover its origins, and see how the source data was transformed. This boosts ‌users’ trust and confidence in the quality of their data.

Improved compliance and data governance

Accidental exposure of sensitive information can have a devastating impact on customer trust. It can also result in expensive regulatory fines. By providing a comprehensive data transformation solution, a data orchestration platform can provide better data security and governance than a more atomized, siloed approach.

dbt Cloud provides a number of tools to enforce and monitor data governance:

• Workflow governance. Engineers, analysts, and decision-makers can standardize on the same platform. Using dbt Semantic Layer, they can define a single source of truth for key organizational metrics.
• Role and access governance. Model and project owners can control who can access what via role-based access control.
• Data governance. Auto-generated documentation, version control, and integrated testing ensure that all changes are thoroughly vetted, recorded, and traceable.

Conclusion

ETL is an older, more traditional approach to managing data transformations on an ad hoc basis. Data orchestration provides a comprehensive approach to automating and monitoring the flow of data across your company. dbt Cloud acts as a data orchestration platform that any company can use to accelerate data delivery, increase data quality, and increase data democratization while simultaneously improving compliance and security.

Learn more about how dbt Cloud can serve as your data orchestration platform—ask us for a demo today.