Data integration in the era of big data and outdated ETL technology

Ensuring the consistency of business data in large enterprises is a very difficult problem. Generally speaking, customer or product-related data of multinational companies often come from multiple sources. This makes it difficult to answer even the simplest questions. In this case, data integration may be a solution.

Data integration provides organizations with a unified view of data stored in multiple data sources, and extraction, transformation, and loading (ETL) technology is an early attempt at data integration.

Using ETL, you can extract, transform, and load data from multiple source transaction systems to a single location, such as a company data warehouse. The extraction and loading part is relatively mechanical, but the conversion part is not so easy. To achieve this, you need to define business rules to explain which transformations are effective.

One major difference between ETL and data integration is that data integration is a broader field. It may also include data quality and the process of defining master reference data, such as defining customers, products, suppliers, and other key information related to the provision of business affairs within the company.

Data integration: Data classification and consistency

Let’s look at an example below. A large business company may need to categorize products and customers from several levels and expand marketing activities in segments. For the company’s smaller subsidiaries, this can be achieved through a simple product and customer classification hierarchy. In this example, a larger organization might classify a can of Coke as part of a carbonated drink, a beverage, and food, and beverage sales. However, smaller subsidiaries may include the same Coke in food and beverage sales without an intermediate classification. This is why classification consistency—or at least an understanding of differences—is needed to get a global view of the company’s overall sales.

Unfortunately, knowing who you are doing business with is not always easy. For example, Shell UK is a subsidiary of the oil giant Royal Dutch Shell. Companies like Aera Energy and Bonny Gas Transport are Shell entities, and some have other investors. Therefore, business transactions with these companies need to be added to Shell’s global view as customers, but from the company name, this relationship is not obvious.

The vice president of a well-known investment bank once told me that they don’t know how much business they have done globally, for example, Deutsche bank, let alone whether the company is profitable. The answers to these questions are buried in various global investment banks. Within the department’s system.

Data integration: Data quality issues

ETL technology is an early attempt to solve this problem. But to get the conversion steps correctly, you need to define business rules to determine what conversion is effective—for example, how to summarize sales transactions or map a database field. When “m” is used to define male customers, and “male” is used In another meaning. The development of technology is helpful to this process.

Facts have proved that the realization of integrated data is more extensive than ETL and data integration itself. Data quality is also an important factor. What if you find duplicate content in the customer or product files? In a project I participated in, 80% of customer records were duplicates. This means that the number of business customers of the company is only one-fifth of what it thinks.

In raw materials, the repetition rate of the master file is usually 20% to 30%. When a company overview needs to be summarized, these anomalies should be eliminated.

Data integration: Growing data volume

Although data integration has its advantages for large companies, it is not without challenges. Such as the continuous growth of unstructured data produced by companies.

Moreover, since data is stored in different formats-sensor data, weblogs, call logs, documents, images, and videos-ETL tools may be ineffective in this environment because they are not designed with these factors in mind. When there is a lot of data or big data, these tools will also encounter difficulties. Similar tools such as Apache Kafka try to solve this problem through real-time streaming data, which enables them to overcome the limitations of the previous message bus method on real-time data integration.

From the early ETL to the present, the related technologies and concepts of data integration have undergone great changes. But it still needs to continue to evolve to keep up with the constantly changing needs of enterprises and the new challenges emerging in the era of big data.