Image by Author

What is a Data Warehouse?

A Data warehouse is a central repository that contains data, information, and other variables that can be analyzed to help businesses make informed decisions. For example, it can be used to measure performance or acquire validations.

It involves the maintenance of historical data which then benefits knowledge workers and others in the organization in their decision-making process. Data Warehouses provide companies with:

• A single source of truth
• Consistency
• Effective decision-making process

What is ETL?

ETL stands for EXTRACT, TRANSFORM and LOAD. It is the process of moving data from multiple sources to a centralized single database. It starts with the raw data being EXTRACTED from the source, and then TRANSFORMED on a separate processing server, in which it is then LOADED into the target database. 

Common Mistakes in Data Warehousing and ETL

Here is a list of the common mistakes that people face with Data Warehouses and ETL processes:

• A lack of understanding of all source data
• Lack of historical data
• Too much time is spent on profiling the source data
• Too much time is spent on testing the extract process
• Agreeing to a set of rules
• Not logging the ETL process
• Not being open to new technology

ETL Best Practises

1. Create a Roadmap for Your ETL Processes

With anything you do in life, it is better to start with a plan rather than diving into the deep end. You may want to write it down or you make want to create a visualisation of your process. But the roadmap is essential as it allows you to go back and make adjustments and learn via trial and error.

2. Populate Test Data Early

As you create your roadmap, you will be considering the end goal in mind. In your ETL process, you want to understand ‘what data model do you want to populate?’. Populating your data warehouse with sample data that is related to your end goal will make your process more effective. It helps you to keep in line with the task at hand and create rules. 

3. Review Source Data and Systems

The source system contains the data that is fed to the data warehouse. You can use profiling tools to help you identify NULL values or what the columns serve as. Rather than spending your time on profiling queries, reviewing your source system can improve your ETL process. 

You need to identify primary key definitions in every source table, and any possible information/data related to it. Use this practice as a verification stage of feeling confident about what you are feeding into your data warehouse.

4. Data Type Issues

When querying your data, you don’t want to be coming across various errors due to data type issues. It’s a problem that should be addressed early on in the process so that it doesn’t cause problems later on. 

5. Extracting the Data

Extracting the data from source systems is an important phase, which can cause many problems if not done correctly. Here are a few tips:

• Having a timestamp column in your source system will allow you to rely on that transaction date and ensure that all the necessary data has been extracted. 
• Extracting the data in incremental steps will help when you’re working with a very large source table. 
• Take note of how long your extract processes take, as there may be ways you can improve it. 

All your extracting data processes should be thoroughly reviewed and verified. 

6. Collating All Activity in ETL Logs

One of the best practise, not only with data warehouses but in life is logging everything. It’s better to go back to a whiteboard that had different ideas and processes scribbled everywhere, than a blank one. 

Through ETL logs, you can find valuable information such as extraction time, changes in rows, errors and more. 

7. Alerts

It can be overwhelming to watch an ETL process occur. You want to keep an eye on it, but sometimes it can take longer than you think and you might catch yourself up at ungodly hours. Some companies have created a messaging and alert procedure, which notifies them of any fatal errors that they need to be aware of.

Conclusion

Although some may say these are practise that everybody should be doing when working with data warehouses and ETL. It will surprise you how these are the main challenges that a lot of companies/teams face. 

If you would like to learn more about Data Warehousing and ETL, have a read of:

• Why Organizations Need Data Warehouses
• SQL and Data Integration: ETL and ELT
• ETL vs ELT: Data Integration Showdown

 
 
Nisha Arya is a Data Scientist and Freelance Technical Writer. She is particularly interested in providing Data Science career advice or tutorials and theory based knowledge around Data Science. She also wishes to explore the different ways Artificial Intelligence is/can benefit the longevity of human life. A keen learner, seeking to broaden her tech knowledge and writing skills, whilst helping guide others.