- \n
- Extract<\/strong>: Raw data is accessed and moved from its source system to a staging area<\/li>\n
- Transform<\/strong>: In the staging area business rules are applied to cleanse<\/a>, transform and organize the data to ensure quality and consistency<\/li>\n
- Load<\/strong>: The data is then added to the repository<\/li>\n<\/ul>\n
Carried out using software tools, the ETL process can be automated to take place on a regular basis, through batch updates or in real-time when data in a source system changes. These ETL pipelines (or data pipelines), bring together tools and activities to automate data integration, increasing efficiency and enabling them to be replicated, monitored and improved.<\/p>\n
ETL vs ELT<\/h3>\n
ETL has been in use since the 1970s. More recently it has been joined by Extract, Load, and Transform (ELT). As the name suggests, this reverses the order of the second two steps, loading data into the target repository, where it is then transformed. This removes the need for a staging area, and adds flexibility to data analytics<\/a>. This means it works well for high volume, unstructured big data, such as that stored in data lakes.<\/p>\n
However, it requires central repositories both to have data mapping capabilities and access to sufficient processing power to successfully carry out the transformation step efficiently and quickly. This makes it best deployed in the cloud, which provides access to the scalable, on-demand computing power required to undertake transformations.<\/p>\n
Why is ETL important?<\/h2>\n
Structured and unstructured data is now produced by multiple systems inside and outside the organization, in a large number of different formats. To create value this data needs to be made available widely, especially to underpin business intelligence<\/a> and enable better, data-driven decision making<\/a>. This requires it to be centralized and standardized to ensure accessibility<\/a>, quality and governance<\/a>. Extract, Transform, Load processes enable this, making them central to:<\/p>\n
- \n
- Effective, informed data analysis and reporting through a consolidated view of information<\/li>\n
- Ensuring data quality<\/a> and governance by applying consistent standards<\/li>\n
- Delivering a single version of the truth for everyone<\/li>\n
- Providing historical context for business intelligence<\/li>\n
- Increasing efficiency by replacing manual processes<\/li>\n<\/ul>\n
What are the steps in Extract, Transform, Load (ETL)?<\/h2>\n
Extract<\/h3>\n
In the data extraction phase, raw data is copied or exported from source locations to a staging area. Data can be structured or unstructured and can come from sources such as relational databases, data storage platforms, cloud storage, or business applications (such as CRM or ERP systems).<\/p>\n
Transform<\/h3>\n
This is the most important part of the ETL process, as it ensures that the raw, extracted data is checked and transformed in the staging area so that it meets corporate guidelines around standards, quality and accessibility.<\/p>\n
Data transformation<\/a> normally includes:<\/p>\n
- \n
- Cleansing \u2014 removing\/resolving inconsistencies and missing values<\/li>\n
- Standardization \u2014 applying standard governance rules to the dataset<\/a><\/li>\n
- Deduplication \u2014 discarding redundant or duplicate data<\/li>\n
- Verification \u2014 removing unusable data and flagging anomalies<\/li>\n
- Encrypting\/protecting data – such as by anonymizing sensitive data<\/li>\n
- Sorting \u2014 organizing data by type<\/li>\n
- Improving – enriching datasets<\/a> with additional data (such as reference data) to increase its value<\/li>\n<\/ul>\n
Load<\/h3>\n
Once the data is transformed, it is then loaded into a centralized repository, such as a data lake or data warehouse<\/a>. All data can be loaded at once (full load) or at scheduled intervals\/when a record changes (incremental load). Typically, this final stage takes place outside peak business hours when traffic on the source systems and the data warehouse is at its lowest.<\/p>\n
<\/p>\n
- Transform<\/strong>: In the staging area business rules are applied to cleanse<\/a>, transform and organize the data to ensure quality and consistency<\/li>\n
![\"Ebook](\"https:\/\/no-cache.hubspot.com\/cta\/default\/2041226\/0e49dffc-8a37-49e3-9cab-bb2cf00834ee.png\")
<\/a><\/span>