A TECHNICAL DEEP DIVE

Modernising enterprise data loads: why full reloads fail at scale and what to use instead

Most data engineers have encountered it: a data warehouse that reloads complete datasets, including full history, multiple times per day. The reasoning is usually pragmatic (“all the history is there”) but the operational consequences compound as systems scale. Resource contention increases, processing windows stretch into hours, and failures begin to cascade across dependent workloads.

This article examines a real-world transformation at an international bank, where Bragi was used to replace full reloads with incremental loading patterns. Critical data pipelines were reduced from runtimes exceeding five hours to completing in under four minutes. Crucially, this improvement was achieved without a wholesale platform rewrite, demonstrating that targeted optimisation can deliver material performance gains while limiting operational risk.

Understanding the full-load anti-pattern

How it emerges

The full-load pattern rarely appears by design. More often, it develops through a series of incremental decisions that are each reasonable in isolation. A new source system is introduced. The fastest way to ingest it is to pull all available data. Early volumes are small, processing is fast, and getting into production takes priority over optimising something that appears to work.

As time passes, datasets grow. Historical records accumulate. Additional sources are integrated using the same approach. Eventually, the warehouse is reprocessing giga- or terabytes of largely unchanged data multiple times per day across dozens of tables. Each load competes for database connections, memory, and compute, regardless of how little has actually changed.

How technical debt accumulates

This architecture introduces several compounding problems:

Resource contention: Full-table loads compete for limited I/O bandwidth, database connections, and memory.
Processing overhead: Staging logic must repeatedly identify “latest” records by scanning complete historical datasets.
Scalability limits: Runtime grows linearly with historical volume rather than with the size of actual changes.
Maintenance burden: There is no clear data lifecycle or archival strategy, increasing operational complexity.
Operational risk: Long-running jobs are more likely to fail, with knock-on effects for downstream processes.

What begins as a pragmatic shortcut becomes a structural constraint on scalability.

Why full reloads persist

One of the most challenging aspects of this anti-pattern is the resistance to changing it. The objections are familiar:

*“We don’t need archives; all the history is already there.”

“It’s working now, why change it?”

“A rewrite would take months we don’t have.”

Each contains a degree of truth: the history is accessible, the system does work (just about), and a full rewrite would be costly. What these arguments overlook is the trajectory: technical debt compounds over time. What works today will degrade as volumes grow and concurrency increases.

Context: an international bank under pressure

Infrastructure saturation

At a large international banking institution, these issues reached a tipping point. The data warehouse ingested data from multiple enterprise systems, with each source following a full-load pattern.

Infrastructure saturation became a daily operational concern. Jobs competed aggressively for shared resources, causing widespread slowdowns. Engineering teams described being “on fire all week” as they attempted to stabilise workloads. Several critical pipelines routinely exceeded five hours in runtime, and failures propagated across dependent jobs.

Baseline measurements

Before making changes, baseline performance and benchmarks were measured across three representative load configurations.

These figures indicate a system operating at the limits of available capacity. With average runtimes exceeding four and a half hours and loads running concurrently, there was minimal headroom for retries, additional processing, or growth.

The technical solution: high-watermark loading

Architectural overview

The solution combined three complementary components designed to eliminate redundant processing while preserving full historical traceability.

1. High-watermark tracking

Rather than reloading complete datasets, the pipeline tracks the most recent successfully processed record. Subsequent loads query only records created or modified after that point.

SELECT * 
FROM source_table 
WHERE modified_date > 
      (SELECT MAX(last_load_timestamp) FROM watermark_table)

High watermarks can be implemented using timestamps, sequence numbers, or any monotonically increasing identifier. Timestamps are commonly available and were used in this implementation.

2. Structured data archival

Instead of relying on source-system history, historical records are persisted in a dedicated archive optimised for long-term storage and analysis. This supports point-in-time reconstruction, audit requirements, and regulatory needs, while separating historical data from active processing paths.

Archive tables typically include:

All source attributes plus load metadata
Partitioning (commonly by date or source)
Compression to control storage costs
Indexing aligned with query patterns

Latest-state views derived from the archive

Rather than repeatedly scanning full history to derive current state, “latest” records are exposed via indexed queries or materialised views on the archive.

CREATE VIEW latest_records AS 
SELECT * 
FROM archive 
WHERE (record_id, load_timestamp) IN ( 
  SELECT record_id, MAX(load_timestamp) 
  FROM archive 
  GROUP BY record_id 
);

This approach simplifies downstream logic while significantly improving query performance.

Implementation with Bragi

Although these patterns are well understood, implementing them reliably is non-trivial. Teams must manage watermarks, handle partial failures, coordinate archive updates, and maintain lineage, without disrupting production workloads.

Bragi automated this operational complexity. The platform managed watermark progression, coordinated incremental loads with archive updates, and captured lineage automatically. This allowed the engineering team to focus on identifying which pipelines to optimise, rather than building and maintaining the underlying infrastructure.

Key Bragi capabilities used in this implementation included:

Automatic watermark management per source
Archive automation with partitioning and indexing
End-to-end data lineage from source to consumption
Environment lifecycle management across dev, test, and production
Fault-tolerant execution with retries and recovery

Implementation approach

The transformation was deliberately incremental. Rather than rebuilding the entire warehouse, the team focused on the most problematic pipelines. Specifically, the three loads responsible for the majority of runtime and contention.

The rollout followed a structured pattern:

Analysis – identify highest-impact loads
Pilot – convert a small number of tables
Validation – run old and new patterns in parallel
Cutover – switch production traffic after validation
Monitoring – confirm stability before expanding further

This approach reduced risk while demonstrating tangible value quickly.

Several common challenges were addressed explicitly:

**Late-arriving data ** A configurable lookback window (24–48 hours) ensured updates to historical records were captured even after the watermark advanced.

**Initial historical loads ** First-time archive population was scheduled during maintenance windows and executed in batches to minimise source-system impact.

**Deletes ** Source systems used soft deletes, allowing deletion flags to be handled within the incremental logic. Where hard deletes are present, periodic reconciliation can be applied.

Results and impact

Average runtime fell from approximately 4.5 hours to just over three minutes. These pipelines moved from dominating batch windows to completing quickly and predictably.
Before optimisation, the three pipelines consumed roughly 13.5 hours of cumulative runtime per cycle. After optimisation, this dropped to under ten minutes.

This released capacity enabled:

More frequent refresh cycles
Safer retry strategies
Increased concurrency
Onboarding of new sources without infrastructure upgrades

Jobs that previously failed due to timeouts or contention now completed reliably.

Lessons learned

Optimise pain points first

Focusing on the most problematic pipelines delivered rapid impact and built organisational confidence. A fourth table with complex downstream dependencies was intentionally deferred, buying time to rework consumer access patterns and shift usage towards the archive.

Measure before and after

Baseline metrics were critical, both for validating success and for overcoming organisational resistance. Runtime, resource utilisation, and failure rates provided objective evidence.

Validate in parallel

Running old and new pipelines side by side ensured correctness, surfaced edge cases, and built trust before cutover, particularly around business-critical periods.

Codify anti-patterns

Documenting the full-load anti-pattern helped prevent its reintroduction during future integrations.

Broader implications

This project reinforces an important architectural principle: transformational outcomes do not require transformational initiatives. Incremental, well-targeted optimisation can deliver significant value while minimising risk, particularly in regulated environments such as financial services.

The full-load anti-pattern is common because it is initially convenient. Recognising it early and having tooling that lowers the cost of best practices makes it possible to correct course before reaching crisis.

Conclusion

This transformation demonstrates that entrenched technical debt can be addressed incrementally and safely. By replacing full reloads with high-watermark–based incremental loading on a targeted subset of pipelines, the bank reduced runtimes drastically, alleviated infrastructure pressure, and created momentum for broader modernisation.

With measured optimisation and modern automation, even heavily constrained data platforms can be stabilised and modernised without wholesale replacement.

Applying these patterns in your own data platform

The high-watermark and archival patterns described in this article are well understood but implementing them consistently across enterprise data platforms is often where teams struggle, particularly when dealing with failure handling, lineage, and production change control.

Bragi is designed to automate these concerns. It provides native support for incremental loading, archive management, and end-to-end data lineage, allowing engineering teams to focus on modelling and optimisation rather than building and maintaining ingestion infrastructure.

If you are managing long-running batch jobs, facing infrastructure contention, or working with full-reload pipelines that no longer scale, you can explore how Bragi supports incremental data loading and warehouse modernisation in the detailed case study linked below, or get in touch to discuss your own environment.

About the author

See Bragi in action

Learn more about Bragi in a personalised demo

Speak directly to Bragi’s co-founders, not a sales agent, and explore how Bragi can transform your data workflows.