English
As digitization increases, the number of IT solutions and the data they store grows. Sometimes different systems store similar or even the same information. When the need arises to combine data from different sources into a whole, various problems may emerge due to different structure or form of storage.
The ETL process is designed to help centralize the data by making it more consistent, e.g. removing duplicates, making a uniform record of recalled products, splitting an address from one attribute into several fields.
What is the ETL process?
ETL process (Extract, Transform, Load) - the process of transforming unstructured or scattered data, into a unified structure giving consistent and homogeneous data. This makes it possible to quickly verify the quality and completeness of the data, analyze it or introduce a classification standard. This process is advisable when data from different sources are combined into a single structure, e.g. for a PIM/MDM or Business Intelligence system.
The process consists of three parts: extraction from different sources and structures, transformation into a single data model and loading it into the destination.
The stages of the ETL process
Extraction - collection of data from all identified sources. This can be data from different systems sent in different forms e.g.: as flat files, via API, directly from the database. Sometimes it will be information stored outside the systems e.g. in Excel.
Before collection, it is a good idea to do an analysis of data sources, during which we will get the necessary information from business owners and evaluate the usefulness.
Transformation - consists of processing of extracted data, i.e. consolidation, cleaning and correction of errors, calculations, changes in data types, filling in empty values, grouping or combining attributes, among others. The result of the transformation is information prepared for further use.
In the example it is possible to create a tree structure from a flat product structure, which consists of categories, products and their variants.
Load - the final stage in the ETL process, in which the cleaned and unified data is sent to the target storage location. From the ETL tool to the database or from the indirect environment to the target.
ETL vs. ELT
ELT process (Extract, Load, Transform) - a modified ETL process in which the stages of data loading and transformation are switched in order. The data is loaded straight into the target system, and the transformations and re-structuring take place there. This eliminates storing and processing data in multiple places.
Implementation of ETL / ELT process
A key element of implementation is the tool for the ETL / ELT process. The choice of tool and type of ETL / ELT process depends on the destination. This is because of the cost, competency and technological environment.
If the target system would be Tableau, then Tableau Prep would be the best choice. For Microsoft technology, it would be SSIS (SQL Server Integration Services).
When the target of a unified data structure is a PIM or MDM system, it is best to use it as a tool for the ETL / ELT process. For example, the Pimcore platform can be used in this way. The benefit is great flexibility in transofmation, because all the options that the programming language gives are possible. The other side of the coin is the lack of a graphical interface, where you usually choose data transformations.
What is Pimcore?
Pimcore is an open-source platform that differs strongly from other e-commerce platforms. It owes this to its origins as a PIM system for managing product data. As a result, it has a very flexible architecture, allowing you to give it any structure you want or use an already existing one within a standard such as the ETIM classification system.
Another example is its use as an ETL/ELT tool, where data undergoes various transformations before it reaches its target structure.
Such a comprehensive platform allows you to meet all your needs without writing everything from scratch. It's no surprise that customers are highly satisfied, as reflected in awards from the Gartner Research Institute for e-commerce and other categories. Learn more about the Pimcore platform.
