You Don't Have to Pick One: A Hybrid Approach to Enterprise Data Refresh

If you are a data engineer, you have probably come across this situation where you have to decide between two approaches for refreshing enterprise data. One approach focuses more on speed and less on compute, while the other focuses more on reliability and data correctness. Usually, when we talk about data refresh, the two options that come to mind are incremental load and full load, and the question becomes which one we should choose.

In my opinion, this is not necessarily a choice where we have to pick one approach over the other. Both approaches have advantages and disadvantages, and rather than relying on one approach, I believe we can combine them and use each where it makes the most sense.

I learned this firsthand when I was tasked with building a data warehouse from scratch for my organization, and one of the most important decisions I had to make, apart from the data model and schema, was the refresh strategy.

Why I Didn't Want to Pick Only One Approach

Incremental loading is the modern approach and is commonly used in enterprise environments. Instead of loading the entire table every time the refresh runs, incremental loading focuses only on records added or modified since the previous refresh. This significantly reduces the data to process, resulting in faster refreshes and lower compute usage.

However, incremental loading also has disadvantages, especially for historical data. Incremental loading assumes we can correctly identify all changes in the source system. For example, if a new record is added or an existing record is updated, we can generally identify those changes using a timestamp, watermark, or change data capture mechanism. The problem comes when a historical record is deleted from the source system.

If our incremental process is only looking for new and updated records, then the deletion may never be captured. The source system may no longer have that record, but the data warehouse will continue to have it. This can result in the data warehouse slowly becoming different from the source system without necessarily causing the ETL process to fail.

I consider this one of the biggest weaknesses of an incremental approach. The process can run successfully, but the data can still be incorrect. A full load, on the other hand, addresses this problem more simply. We can essentially reload the data from the source system and make sure that the warehouse reflects what is currently available in the source. This makes full load more reliable for historical changes and deletions. 

However, full load has its own problem. As the number of records increases from thousands to millions or even hundreds of millions, loading the entire table every time becomes expensive and time-consuming. A process that initially took a few minutes can eventually take hours, and running it every night may not be practical from a compute or maintenance-window perspective. So, in my opinion, neither approach is perfect.

Where I Ran into This Problem Myself

I was tasked with creating a data warehouse for my organization. When I investigated the existing environment, I found that the data I would be working with spanned millions of records across multiple tables, with a large number of columns. This is common when working with enterprise-level data, but it becomes an important consideration when designing a data warehouse and deciding how to refresh it.

Prior to the launch of this data warehouse project, the data was stored in normalized tables coming directly from the ERP database. The data itself was good, but it was not very usable for a business intelligence developer or a data analyst.

The main problem was that there was no one common place where an analyst could get all the information they needed. They had to work with multiple normalized tables and understand how those tables were related before they could even start building their analysis or dashboards.

This was one of the main reasons the data warehouse project mattered. Once we kicked off the project, one important decision I had to make, besides the data model and schema, was the refresh strategy. I had two conventional options.

The first option was incremental load, which is the modern approach where the primary focus is on faster refreshes and less compute. The second option was the traditional full load, where the primary focus is on reliability and data correctness, but it takes more time and compute because all the records are loaded every time the refresh runs instead of only loading the new and updated records. Now, in my opinion, both approaches have their own advantages and disadvantages.

The modern approach is to employ incremental refresh, and this is what most enterprises prefer because of the faster refresh times and reduced compute. But I thought a little differently. 

Although incremental refresh has an advantage when it comes to speed and compute, the traditional full load also has some important advantages. It provides more reliability when it comes to data correctness, especially when historical records are deleted from the source system. Full load is also conceptually simpler because you do not have to build and maintain complex logic to identify every possible type of change that can happen in the source system. So instead of choosing between the two, I thought of employing a third approach.

The Hybrid Approach

The approach I decided to use was a hybrid approach, which meant combining incremental and full load refreshes instead of relying completely on either one. 

The idea was relatively simple. On weekdays, I used incremental refresh because business users and analysts are actively working with the data, and faster refreshes matter more. The incremental process would capture the latest and most frequently changing data without having to reload millions of records every time.

Then, during the weekend, I would run a full load or reconciliation process to capture changes in historical data that the incremental process may not have been able to identify, especially deletions.

This allowed me to achieve both objectives. I could have faster refreshes during the business days while still having a mechanism that periodically verifies that the data warehouse is aligned with the source system. 

In other words, instead of asking whether incremental or full load is better, I looked at where each approach was better and used both approaches accordingly.

How the Hybrid Approach Works

The approach can be generalized into a few steps.

1. Monday to Friday, Run Incremental Loads

During weekdays (Monday to Friday), the ETL process retrieves only the records that have changed since the previous refresh. This can be done using Change Data Capture, a watermark column, or another reliable mechanism available in the source system.

For example, if the source system has millions of records but only a few thousand records change during the day, there is no reason to process all the millions of records again. We can identify the changed records and process only those records.

2. Make the Process Idempotent

Another important consideration is making sure that the ETL process can safely be executed more than once. 

For example, if an ETL job fails halfway through and we have to restart it, we should not end up inserting duplicate records into the data warehouse.

This is where approaches such as MERGE-based upserts can be useful, although they need to be implemented carefully, especially in environments where multiple processes can update the same records concurrently.

3. Run a Full Reconciliation During the Weekend

On Saturday or Sunday, when activity is generally lower, we can run a more comprehensive reconciliation against the source system. For smaller dimension tables, a complete truncate-and-reload may be perfectly reasonable.

For very large fact tables, however, I would not necessarily recommend blindly truncating and reloading the entire table. Instead, we can compare the warehouse with the source and identify differences between the two. This is especially important for identifying historical records deleted from the source system.

4. Track What the Weekend Process Had to Correct

I also think it is important not to treat the weekend reconciliation simply as another ETL process. Every time the full reconciliation finds a difference, that difference tells us something about our incremental process.

For example, if the weekend process finds 100 deleted records that the incremental process did not capture, that is useful information. If that number continues increasing every week, it may indicate that there is a problem with the incremental logic or the way changes are being captured from the source. Therefore, the full load can also act as a validation mechanism for the incremental process.

5. Communicate the Refresh Window

Finally, communicate when the full reconciliation will happen. If reports or dashboards are connected to the warehouse, users should know that the data may temporarily look different while the reconciliation or reload is happening. This should be treated as a planned maintenance activity rather than something unexpected.

What This Approach Delivered

Going into the project, I initially thought that I would have to accept one weakness or the other. Either I could use incremental refresh and get faster refreshes but accept that some historical changes, especially deletions, might not be captured. Or I could use full load and have better data correctness but accept longer refresh times and higher compute costs. 

The hybrid approach allowed me to avoid that trade-off every day. The warehouse could remain fast and relatively inexpensive to refresh during the business days, while the weekend reconciliation provided an additional layer of reliability and data correctness. Most importantly, historical changes that an incremental-only approach could potentially miss were given another opportunity to be captured during the weekend process. This made the refresh strategy more resilient because I was not relying on one mechanism to solve every possible problem.

Where Would I Go From Here?

In my opinion, the question should not be whether incremental load or full load is better. The better question is how we can use both approaches together to get the advantages of each. If your data warehouse currently relies completely on incremental refresh, you don't necessarily have to redesign the entire architecture. Start small. Take your highest-risk fact table and introduce a weekend reconciliation process alongside the existing incremental process. Then track how many records that reconciliation has to correct. That number can tell you a lot about the reliability of your incremental process. If the number is close to zero every week, that gives you confidence that your incremental process is working well. If the number keeps increasing, you have identified an area where the incremental logic needs improvement.

For me, this is what made the hybrid approach interesting. It was not simply about making the data warehouse faster or cheaper. It was about finding a practical balance between performance, compute, reliability, and data correctness.

Rather than choosing one approach and accepting its weaknesses, I chose to use each approach for what it was actually good at.