Backfill process implementation

About project:

Client overview

The client, a data-driven organization, was experiencing significant delays in data migration projects due to an inefficient initial data loading process for their data warehouse. They sought a faster, more effective solution to streamline large data transfers and accelerate migration timelines.

Tech Stack:

ETL pipelines, traditional data migration tools

Tech stack after migration:

dblink, custom backfill process

Time to deliver project:

4-6 Weeks

Problem

  • The client approached us with a critical issue: their initial data loading process for the data warehouse was excessively slow. This long backfill time was causing significant delays in their data migration projects and hindering their ability to utilize new data quickly.

Inspection

  • We found that the existing ETL pipelines were designed for incremental updates, not bulk data transfers. These pipelines were impractical for large initial loads, often taking days to complete. We discovered that data sources were accessible via dblink, and network latencies were low. This presented an opportunity for a more efficient data transfer method.

Recommendation

  • For future initial downloads or large data migrations, temporarily remove indexes, triggers, and constraints from the DWH tables before the transfer. Restore these database objects after completing the download via dblink. This can significantly speed up the insertion process while maintaining data integrity and query performance in the long run.

Resolution

We implemented a custom backfill process using dblink for direct database-to-database transfers. This new approach bypassed the slower ETL pipelines for large initial loads. As a result, data migrations that previously took 3-4 days were completed in just 4-6 hours. We achieved a transfer rate of about 1 million rows per minute, a tenfold improvement over the previous method.

Similar projects

Do you want
the same one?

Leave a request and our manager will contact you to discuss your project and give an assessment of a similar project.

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Our website use cookies
Read our Privacy Policy.
Order an audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Order Black box audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number

Order White box audit

Please enter your name

Please enter your email

Please enter valid email

Please enter valid phone number