Data Integration Tools vs. Data Processing Frameworks/Libraries/Engines
Introduction For any data engineer, data pipelines are very important. Choosing how to develop one is integral for the person building it since it’s going to determine the complexity of the process. When developing a data pipeline you can use two approaches. One of them is to use a programming language and a data processing framework or libraries like Apache Spark and Pandas. Another one is to use data integration tools that offer out-of-the-box components for data loading and processing. But how do you decide which one to use? How to determine which fits you better? Both approaches have their benefits and drawbacks, it depends on the situation what is best for you. For instance, if you do not expect your data pipeline to be huge and don’t have any experience with such tools, it is probably better to stick with a programming language you already know. If you expect your data pipeline to have many components, data sources, data sinks, and a lot of processing then data integration tools could help you to do that effectively. Also, you should consider how much data is there, do you need to utilize cluster computing platforms such as Apache Spark or you could get away with one server where you installed your tool. In our practice, we have used data integration tools more often than coding the processes. It saved us a lot of time and helped the team to have easier communication and understanding. Data Integration Tools There are many data integration tools on the market, some of them are open source and some require expensive licences. Each one has its advantages and disadvantages, and some are a better choice than others. The idea is not to describe and compare all of them but to mention a few and describe one that you can try for free. Deciding on one is determined by many factors and it’s usually your needs and goals that drive the selection. Some of the popular data integration tools are Pentaho Data Integration, AWS Glue, Hevo, Talend, Informatica PowerCenter, and Oracle Data Integrator (ODI). Pentaho Data Integration (PDI), nowadays known as Lumada Data integrator, is an ETL tool that contains built-in capabilities for extracting data from different sources, transformations, and loading data to target systems. It offers a free community and paid enterprise version that comes with the support. You can install it locally, or on a server hosted in a cloud or on-premise. Data can be extracted from almost all popular databases, input streams, different file formats from local or remote servers, files from cloud and clustering storages, input streams, and APIs. It supports built-in simple and complex transformations for cleaning data, combining data, processing data, and adding metadata. Those transformations are executed by Pentaho’s engine so you should ensure enough resources on your server when executing it on large portions of data. If built-in transformations are not flexible enough, you can write custom Python, JavaScript, Bash, or SQL scripts to achieve your goals. Also, you can execute external scripts and programs for data processing. Pentaho supports loading data to a wide variety of popular databases, files on local or remote servers, cloud and cluster storages, or even data streams. Although Pentaho is an ETL tool, we also use it as an ELT (Extract, Load, Transform) tool. You can achieve that by extracting and loading directly to a data warehouse which should be fast and efficient. The number of used built-in transformations is minimized. That way transformations that define business logic are done in the end with SQL statements which are executed in database engines that are optimized for data processing, especially if you use column-oriented databases like Vertica and Redshift. We could say that in the case of ELT, Pentaho serves as a data loading and workflow management tool (control flow, job order, transformation order). Now, why would you use ETL tools? ETL looks like a simple process, you just have three steps and you are done. But in reality, you have many problems to solve even in the first phase of extracting data from the source. For instance, data structure from input files can change over time, you can have incomplete and wrong data that needs to be filled or be marked for manual checks and not cause the whole process to stop. To sort out those kinds of issues, you’ll have to exchange a lot of emails or calls with your client which takes time. In situations like that, it is good to have a tool in which you can do all those changes without too much code refactoring and messing up the parts that work. The ultimate goal is to deliver a project on time, and utilizing those tools can help you in that. Benefits of data integration tools Drawbacks of data integration tools Code approach Some companies prefer to build their data pipelines by using popular programming languages, frameworks, and libraries. In most cases, the pipeline consists of Python, R, Scala scripts, Apache Spark jobs, MapReduce jobs, Apache Storm, or some other parallel processing frameworks. Scripts and jobs are usually orchestrated by Apache Airflow, Luigi, or other workflow management platforms. We have used Apache Spark with Apache Airflow to support the ETL process in some of our projects where we gained experience in utilizing these solutions. The important thing to remember is that you should choose the most appropriate technology. If there is no need for cluster computing then stick to Python and Pandas. If you need to implement a data pipeline and already have developers on board, they will be familiar with this approach as they know programming languages, documentation, testing, release process, and so on. There is nothing new for them in this approach, besides learning new technologies like Apache Spark. In many cases, it is easier to educate your developers than to find ones that are experienced with specialized data integration tools or have the will to learn one. The main benefit of this approach is performance