ETL: Following Best Practices

What is ETL?

In a few words, the ETL process is the process used to move data from one source to one destination. This process typically includes programmatically retrieving data from one or more sources, in the middle of the process, update the data could be necessary before pushing the data to the destination.

Every new data developer in some point in their career will Extract, Transform and Load (ETL) data, the ETL process is a centerpiece to handle the amount of data we are producing or receiving every day. The ETL process is not only essential for the Data Warehouse loads, nowadays the ETL is used in multiple stages of the companies.

Because of this, the ETL process needs to be a trusted process, reliable, robust, and consistent. Following Best Practices for an ETL process will result in a load process with the following characteristics:

1. Reliable
2. Resilient
3. Reusable
4. Maintainable
5. Secure
6. Good Performing

Beyond this point, any ETL tool or orchestration you choose for the ETL process needs to have the described characteristics, and it could be applicated to any architecture.

You need to remember these best practices are not written in stone, in other words, the best practices are a guideline to apply concepts that seem fundamental to the process.

The following best practices should be the core of the Architecture of any ETL implementation.

When, and How of Incremental Loads:

Speed up your load processes and improve their accuracy by only loading what is new or changed. The ETL process should consider using CDC for looking at what and when the data have changed, in this way we can pull only the necessary records to move to the destination.

Logging:

A proper logging strategy is key to the success of any ETL architecture.

Auditing:

A load without errors is not necessarily a successful load. A well-designed process will not only check for errors but also support the auditing of row counts, financial amounts, and other metrics.

Data Lineage:

Understanding where data originated from when it was loaded, and how it was transformed is essential for the integrity of the downstream data and the process that moves it there.

ETL Modularity:

Creating reusable code structures is important in most development realms, and even more so in ETL processes. ETL modularization helps avoid writing the same difficult code over and over and reduces the total effort required to maintain the ETL architecture.

ETL Atomicity:

How big should each ETL process be? In this post, I discuss the merits of properly sizing your ETL logic.

Dealing with Errors:

What happens when things go wrong? An ETL Developer will need to handle with bad data, inconsistencies, or a wrong data types. However, a robust ETL process could be able to handle it, sending the wrong data to a stage table for review or firing up an alert about records present on the source.

Using ETL Staging Tables:

Often, the use of interim staging tables can improve performance and reduce the complexity of ETL processes.

---

In future post we will go more in detail to compare ETL vs ELT process, when to apply each one.

See you next time Folks! 😊