ETL (Extract, Transform, and Load)

Short Background

The pace of information technology is increasingly developing very rapidly nowadays. Almost every aspect of life can’t be separated from information technology, like communication, transaction, research & development, sports, medicine/healthcare, and etc. The more information technology develops, the data that will be exchanged in the process will simultaneously increase. It makes data come from various sources with various types. And in the 1970s/1980s, a healthcare organization claimed that they had validated the data, carried out transformations, and stored it into a data warehouse for the purpose of research and disease diagnosis. Then it became the forerunner for data integration. The goal is to integrate the data. ETL became the standards method for taking data from disparate sources and transforming it before loading it to a target source or destination.

An Introduction to ETL

Has been known that ETL stands for Extract, Transform, and Load. ETL is a process that extracts the data from different source systems, then transforms the data and finally loads the data into the Data Warehouse system. It’s tempting to think that creating a Data warehouse is simply extracting data from multiple sources and loading into the database of a Data warehouse. This is far from the truth and requires a complex ETL process. The ETL process requires active inputs from various stakeholders including developers, analysts, testers, top executives and is technically challenging.

In order to maintain its value as a tool for decision-makers, Data warehouse system needs to change with business changes. ETL is a recurring activity of a Data warehouse system and needs to be agile, automated, and well documented.

Why do we need ETL?

The importance of ETL in an organization is in direct proportion to how much the organization relies on data warehousing. It will help organizations to analyze the data for taking critical business decisions. ETL also became a method of moving data from various sources into a data warehouse. If well-designed, ETL will have automated-integration of data when the data in sources have changed. Now more and more organizations think that they need ETL to handle their data. Along with it, the tools for ETL also are increasingly being developed and have their own differences compared to another tools.

Types of ETL Tools

• Hand-Coding

Hand coding involves writing functional code or layout directions in the basic languages in which they are compiled. Companies are still manually hand coding ETL jobs and are losing time and money in the process. But this type of ETL has start to abandoned due to some reasons such custom code with hand-coding is expensive, means to have custom infrastructures etc.

• Batch Processing Tools

Batch processing is a method of running high-volume, repetitive data jobs. The batch method allows users to process data when computing resources are available, and with little or no user interaction. Batch processing was traditionally used for workloads that aren’t urgent, such as monthly or annual reports. However, modern batch processing can be very rapid, making data available in hours, minutes, or even several seconds — just not real time.

• Cloud Based Tools

Cloud-based means stored, managed, and processed on a network of remote servers hosted on the internet, rather than on local servers or personal computers. Like legacy batch processing, cloud-based batch processing preps data without affecting the performance of on-premises systems.

• Real-Time Tools

By comparison, real-time ETL tools capture data from and deliver data to applications in real time using distributed message queues and continuous data processing. This type allows us to query real time data sources like sensors, search engines, and other streaming data. However, this speed often comes at a hefty price tag.

The different types of organization/company can choose different types of tools. Commonly it’s based on many aspects such as the the needs, financial readiness, and etc.

Advantages of Using ETL

By applying ETL on the business, the advantages will obtained like:

• More Integrated Data
• Increase Business Intelligence
• Easily Accessible
• Enhanced Quality and Consistency
• Timely Access to Data
• High Return on Investment