Optimizing ETL Data Pipelines

When building ETL data pipelines using Azure Data Factory (ADF) to process huge amounts of data from different sources, you may often run into performance and design-related challenges. This article will serve as a guide in building high-performance ETL pipelines that are both efficient and scalable.

Below are the major guidelines to consider when building optimized ETL data pipelines in ADF:

Linked Services and Datasets

A linked service is a connection to a data source that can be created once and reused across multiple pipelines within the same ADF. It is efficient to create one linked service per source for easy maintenance.

Similarly, datasets are derived from the linked services to fetch the data from the source. These should ideally be a single dataset for each linked service and reused across all pipelines in the same ADF. Separate datasets are created when the data format from the source varies.

Resiliency and Reliability

When you build pipelines with numerous activities, it can complicate troubleshooting when the failure occurs. To achieve reliability, split the complex logic by dividing it into multiple pipelines and creating dependency with each other. Additionally, intermittent network issues can cause pipeline failures. To avoid manual intervention, enable the retry policy at the activity level to automatically rerun when it fails.

Scheduling Using Triggers

In a Big Data environment, processing large amounts of data is often required to develop and schedule multiple pipelines to run in batches. To optimize resource utilization, stagger pipeline schedules where possible. In case business requirements demand processing within a strict SLA, it is best to scale up resources on Integration Runtime (IR) and source/sink to effectively utilize and manage resources.

Activity Type Selection

ADF provides many types of activities to orchestrate the data, and based on the need and business requirement, choose the one that best fits your business. For example, when you copy data from an on-prem Oracle data source to an on-prem SQL Server (sink) that has many tables, consider using a combination of Lookup, For Each, and Copy Activity to optimally design the pipeline.

Data Transformations Using External Compute

When running transformations on data that is stored externally, it is efficient to use the source compute for those transformations. For instance, triggering a Databricks notebook activity can leverage the compute on the Databricks cluster to perform data processing; this can reduce data movement and optimize performance.

Cost Optimization

Pipeline costs highly correlate with the number of activities, transformations, and runtime duration of each activity. The majority of cost occurs due to Azure IR utilization. It is essential to apply the optimization techniques discussed in this guide to lower costs. To reduce costs, enable Azure IR with a time-to-live setting when running data flow activities in parallel.

Security Considerations

During the execution of a pipeline, data movement typically occurs over public networks. To enhance security, there is an option to enable ADF native-managed private endpoints to ensure that the connection to your sources is private and secure.

Backup and Restore Pipelines

To be able to safeguard your ADF pipelines, ADF needs to be integrated with GitHub or Azure DevOps. This will ensure that pipelines can be restored from their source control if the ADF resource is accidentally deleted.

Note that you need to restore other resources separately that are configured as sources or sinks in the pipelines. ADF can only restore pipeline configuration, not the actual resources that are deleted. For instance, if you are using Azure Blob Storage as a source, then this needs to be restored separately if it was deleted.

Monitoring and Alerts

Setting up monitoring for pipeline resources is critical for proactive reaction and prevention, ensuring no issues occur that may lead to escalation. Monitor the pipeline runs, integration runtime utilization, and set up alerts to notify you via email or phone.

Identifying Bottlenecks and Improving Throughput

There are two main things in ADF that will affect throughput performance. Those are compute using in-copy such as Azure Public or managed VNET Integration Runtime (IR), Self-Hosted Integration Runtime (SHIR), and the number of parallel copies. If the compute is not properly scaled for copy job size, it will result in slow performance when it is overutilized. As well as scaling parallel copies to achieve faster performance, this will require even more compute from the IR and from the sources involved. So, as we scale one option, we might need to scale other things as well.

When observing bad performance during copy activity execution, below are the most common causes.

SHIR Capacity Issue

Check if SHIR is overutilized for resources like CPU, and memory during the copy run. This can be checked in the ADF Studio integration runtime monitoring tab. If it is overutilized (ideally usage threshold is 80%), then it needs to scale out or scale up to handle the job. This problem might have occurred due to recent changes in the execution of the number of pipelines in ADF or could be changed in the dataset size that is being copied as part of the job.

For Azure Public IR and Managed VNET IR, the resources are handled through Data Integration Units and Copy compute scale options, respectively.

Parallel Copies Not Enabled

Check if the parallel copies have been enabled or not in the copy activity. If it is not enabled (by default), then it needs to enable this setting to achieve parallelism and increase throughput performance. By increasing the number of parallel copies, it will have more load on the Integration Runtime, the source, and the sink data stores. So, consider scaling up resources on these as well.

Also, when enabling this feature, we need to make sure the source dataset table has an optimal partition column to choose to split the data evenly within each parallel thread. This property also depends on which type of source it is, as some sources don’t support this property. For those scenarios, we need to implement the dynamic query and utilize the ForEach activity to increase parallelism. Refer to this document for more information on parallel copy.

Network Bandwidth and Latency

Check if there are any issues with bandwidth in Express Route when transferring data using SHIR from on-prem data sources. Make sure that the bandwidth is configured as per the data transfer needs. When using SHIR, make sure that this is close to the source data store and in the same local network path to avoid latencies. If the SHIR is running on Azure Virtual Machine (VM), then make sure that the VM is on higher network bandwidth and close to the region where the source data store is located to avoid latencies.

Source/Sink Resource Utilization Issue

After scaling ADF IR resources and adjusting parallel settings, then we need to further scale source and sink data stores to handle the ADF requests and process them. Monitor the source/sink side resource utilization stats (CPU, memory, Input/Output operations) and scale out the resources accordingly.

Check the query option for the source data store, if applicable. If the copy is using a query on the source side to filter or transform the data before copying, make sure the query is efficient and optimized. The ADF monitor shows that it took a long time to read the data, so it is most likely an issue with the query or source data store resource scaling. When we run the same query outside of ADF using some other client tools and it also takes a long time, this is a query issue and a matter of optimizing the query to be more efficient.

Conclusion

It is evident that optimizing ETL pipelines in Azure Data Factory (ADF) necessitates a strategic approach focusing on design pipelines, resource management, and performance monitoring. By carefully choosing appropriate activities, effectively managing resources, and continuously monitoring performance to identify and address bottlenecks, organizations can enhance the efficiency and scalability of their data processing workflows. Implementing these best practices ensures that ADF pipelines are both reliable and capable of meeting evolving business requirements.