Points to remember while writing ETL from scratch

An Introduction to ETL

ETL is a type of data integration process referring to three distinct but interrelated steps (Extract, Transform and Load) and is used to synthesize data from multiple sources many times to build a Data Warehouse, Data Hub, or Data Lake.

The most common mistake and misjudgment made when designing and building an ETL solution is jumping into writing code before having a comprehensive understanding of business requirements/needs.

There are some fundamental things that should be kept in mind before moving forward with implementing an ETL solution and flow.

Why ETL?

It is essential to properly format and prepare data in order to load it in the data storage system of your choice. The triple combination of ETL provides crucial functions that are many times combined into a single application or suite of tools that help in the following areas:

• Offers deep historical context for business.
• Enhances Business Intelligence solutions for decision making.
• Enables context and data aggregations so that business can generate higher revenue and/or save money.
• Enables a common data repository.
• Allows verification of data transformation, aggregation and calculations rules.
• Allows sample data comparison between source and target system.
• Helps to improve productivity as it codifies and reuses without additional technical skills.

A basic ETL process can be categorized in the below stages:

1. Data Extraction
2. Data Cleansing
3. Transformation
4. Load

The feasible approach should not only match with your organization’s need and business requirements but also performing on all the above stages.

In many places Load is done before Transformation, that process is also known as ELT.

Points to keep in mind:

1. Know and understand your data source — where you need to extract data.
2. Never loose your raw data.
3. Learn best ways to extract data from source.
4. Try your best to implement incrimental extract (don't miss updated data).
5. Choose a suitable cleansing mechanism according to the extracted data.
6. Know and understand your end destination for the data — where is it going to ultimately reside.
7. Decide if transformation should be done before load or after load.
8. If writing your own ETL than write it modular and expandable for future data sources to, but do not complicate the code.
9. Take care of duplicate data and Null values.
10. If source is database, take care of lag.

There are many points to consider while writing the ETL. I have just mentioned just few. It would be great to hear from you about critical points I forgot to mention.