Everything About ETL.

What is ETL?

Here Source systems can be anything(Oracle, SQL Server, Teradata, Flatfile)

ETL stands for Extract, Transform and Load. It’s a generic process in which data is firstly acquired, then changed or processed, and is finally loaded into a data warehouse or databases or other files such as PDF, or Excel.

You can extract data from any data sources such as Files, any RDBMS/NoSql Database, Websites, or real-time user activity, transform(like applying calculations, concatenations, etc.) the acquired data and then load the transformed data into a data warehouse for business uses such as reporting or analytics.

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

Need of ETL?

• ETL provides a method of moving the data from various sources into a data warehouse.
• As data sources change, the Data Warehouse will automatically update.
• ETL process allows sample data comparison between the source and the target system.
• ETL process can perform complex transformations and requires an extra area to store the data.
• ETL helps to Migrate data into a Data Warehouse. Convert to the various formats and types to adhere to one consistent system.
• ETL is a predefined process for accessing and manipulating source data into the target database.
• ETL in a data warehouse offers deep historical context for the business.
• It helps to improve productivity because it codifies and reuses without a need for technical skills.

Best practices while doing the ETL process

Following are the best practices for ETL Process steps:

Never try to cleanse all the data:

Every organization would like to have all the data clean, but most of them are not ready to pay to wait or not ready to wait. To clean it all would simply take too long, so it is better not to try to cleanse all the data.

Never cleanse Anything:

Always plan to clean something because the biggest reason for building the Data Warehouse is to offer cleaner and more reliable data.

Determine the cost of cleansing the data:

Before cleansing all the dirty data, it is important for you to determine the cleansing cost for every dirty data element.

To speed up query processing, have auxiliary views and indexes:

To reduce storage costs, store summarized data into disk tapes. Also, the trade-off between the volume of data to be stored and its detailed usage is required. Trade-off at the level of granularity of data to decrease the storage costs.

Summary:

• ETL stands for Extract, Transform and Load.
• ETL provides a method of moving the data from various sources into a data warehouse.
• In the first step of extraction, data is extracted from the source system into the staging area.
• In the transformation step, the data extracted from the source is cleansed and transformed.
• Loading data into the target data warehouse is the last step of the ETL process.

Thank you for reading !!!

If you enjoy this article and would like to Buy Me a Coffee, please click here.

you can connect with me on Linkedin.