Stop Reloading Everything: The Case for Incremental Loading

do not reload everything.

You don’t re-read an entire book just to find the last chapter you left off on. So why do we reload entire databases?

If you are new to data engineering, you will quickly run into a fundamental question: when your pipeline runs, how much data should it move?

The obvious answer is: all of it. Pull everything from the source, load it into the destination, done. This approach is called a full load, and it is where almost every data engineer starts.

It works. Until it doesn’t.

This article explains why full loads break down as data grows, introduces a better approach called incremental loading, and walks through the different ways you can implement it — in plain terms.

First, what is a data pipeline?

Before diving in, a quick baseline.

A data pipeline is a process that moves data from one place to another. For example: from your production database (where your app writes data) to a data warehouse (where your analysts run reports). This pipeline typically runs on a schedule — every hour, every night, or even every few minutes.

The question every pipeline must answer is: how much data do I move on each run?

The full load approach

A full load answers that question simply: move everything, every time.

Every time the pipeline runs, it pulls every single row from the source and loads it into the destination. Think of it like photocopying an entire book every time someone adds a new sentence.

-- Full load: grab every row, every time
SELECT * FROM orders;

Why teams start here:

• Simple to build — no extra logic needed
• The destination always matches the source exactly
• Easy to reason about when something goes wrong

Why it eventually breaks:

Imagine your orders table starts with 100,000 rows. Loading that takes 2 minutes. Fine.

A year later, it has 50 million rows. Now loading takes hours. Your nightly pipeline that was supposed to finish before 9 AM is still running at noon. Analysts are looking at yesterday’s data. Your manager is asking questions.

That is the full load trap. It works fine at small scale, and then quietly becomes a problem as your data grows.

The incremental loading approach

Incremental loading answers the question differently: only move what is new or changed since the last run.

Instead of photocopying the whole book every time, you just copy the new pages.

-- Incremental load: only grab what changed
SELECT * FROM orders
WHERE updated_at > '2024-11-01 02:00:00';

The date in that query — 2024-11-01 02:00:00 — is called a watermark. It marks the point up to which data has already been loaded. On the next run, you move that marker forward.

The result: instead of moving 50 million rows, you might only move 5,000 — the ones that actually changed. The pipeline runs in seconds instead of hours.

Full loads move everything. Incremental loads move only what changed.

Methods of incremental loading

There is more than one way to do incremental loading. Here are the main approaches, from simplest to most advanced.

Method 1: Timestamp-based loading

This is the most common starting point. The idea: every table has a column that records when a row was last updated — usually called updated_at or modified_at. You use that column to filter for only the recently changed rows.

SELECT *
FROM orders
WHERE updated_at > :last_loaded_at

You save the timestamp of the latest record you loaded. Next time the pipeline runs, you use that saved timestamp as your starting point.

Think of it like: Checking your inbox for emails received after the last time you checked.

Good for: Most standard tables in transactional databases where records get created and updated.

Watch out for: This only works if the application reliably updates the updated_at column. If something updates a row without touching that column, you will miss it.

Method 2: Id-based loading

Some data never changes after it is created — event logs, user activity, payment records. For this kind of data, you can use the row’s ID as your watermark instead of a timestamp.

Every new row gets a higher ID than the last. So you just ask: give me everything with an ID greater than the last one I loaded.

SELECT *
FROM events
WHERE event_id > :last_loaded_id

Think of it like: Reading a book from where you left off using a bookmark.

Good for: Logs, events, audit trails — any data that is written once and never changed.

Watch out for: This does not work if old records ever get updated. It only tracks new rows, not changes to existing ones.

Method 3: Upsert (Insert + update) Loading

Sometimes you need to handle both new records and updates to existing ones. That is where the upsert pattern comes in.

Upsert means: insert the row if it is new, update it if it already exists.

The process has two steps:

1. Pull only the rows that changed recently (using a timestamp)
2. Apply them to the destination — adding new ones and overwriting old ones

MERGE INTO warehouse.orders AS target
USING staging.new_orders AS source
  ON target.order_id = source.order_id
WHEN MATCHED THEN
  UPDATE SET status = source.status
WHEN NOT MATCHED THEN
  INSERT (order_id, status, created_at)
  VALUES (source.order_id, source.status, source.created_at);

Do not worry if the SQL looks complex — the concept is simple. You are saying: “If this order already exists in the warehouse, update it. If it is new, add it.”

Think of it like: Syncing your contacts — updating existing people and adding new ones, without deleting anyone.

Good for: Transactional tables where records are regularly updated after creation (orders, users, subscriptions).

Method 4: Partition-based loading

Instead of tracking individual rows, this method divides data into chunks — usually by date — and only reloads the relevant chunk.

For example, if your pipeline runs daily, you only reload today’s data partition instead of the entire table.

-- Delete today's data and reload it fresh
DELETE FROM warehouse.events WHERE event_date = '2024-11-01';

INSERT INTO warehouse.events
SELECT * FROM source.events WHERE event_date = '2024-11-01';

Think of it like: Replacing one page in a binder instead of reprinting the whole thing.

Good for: Data naturally organized by date — daily reports, log files, billing records.

Watch out for: If old data can be corrected retroactively (e.g., a transaction from last week gets updated today), you may need to reload multiple past partitions, which reduces the efficiency of this approach.

Method 5: Hash-based change detection

What if the source table has no updated_at column and no reliable ID sequence? This method creates a fingerprint (called a hash) of each row's content. If the fingerprint changes between runs, the row has changed.

-- Flag rows where the content hash has changed
SELECT source.*
FROM source.products
LEFT JOIN warehouse.products_hashes wh
  ON source.product_id = wh.product_id
WHERE wh.product_id IS NULL           -- new row
   OR MD5(source.*) != wh.row_hash    -- content changed

Think of it like: Comparing a document’s checksum to know if someone edited it, without reading every word.

Good for: Legacy systems or flat file sources where you cannot modify the source to add timestamp columns.

Watch out for: You still need to read the entire source table to compute hashes, so it is not as efficient as timestamp-based methods for very large tables.

Method 6: Change data capture (CDC)

This is the most powerful and most complete method. Instead of querying the source table, CDC reads directly from the database’s internal transaction log — the record the database keeps of every single change it makes.

Every insert, update, and delete is captured automatically, in real time, without you needing to query the table at all.

Your Database → Transaction Log → CDC Tool → Your Warehouse
                                  (Debezium,
                                   Fivetran,
                                   Airbyte)

Think of it like: Instead of asking someone “what changed today?”, you just read their diary — you see every single thing that happened, in order.

Good for: High-volume pipelines, real-time data needs, and cases where you need to capture deletions (which most other methods miss entirely).

Watch out for: CDC is more complex to set up and maintain. It requires access to the database transaction log, which not every database or hosting provider allows. It is powerful, but it is not where you start as a beginner.

The one thing most methods miss: Deletions

Here is something worth understanding early: most incremental methods cannot see when a row is deleted from the source.

If a customer deletes their account, there is no updated_at change to pick up — the row simply disappears. Your warehouse will keep that record forever, showing a customer that no longer exists.

The common workarounds are:

• Soft deletes — instead of deleting a row, the application marks it as deleted with a flag (is_deleted = true). Now it shows up as an update and your incremental load picks it up.
• Periodic reconciliation — occasionally run a comparison between source and destination to find records that no longer exist in the source and remove them.
• CDC — captures deletions as real events, so nothing is missed.

Which method should you use?

Here is a simple guide:

Your Situation Start With Small data, just getting started Full load — keep it simple Data that is only ever added, never changed ID-based loading Data that gets created and updated Timestamp-based or upsert Data organized by date Partition-based loading No timestamps available in the source Hash-based detection Large scale, real-time, or need deletes CDC

Do not jump straight to CDC because it sounds impressive. Start with the simplest method that solves your problem. Complexity should be earned, not assumed.

A quick summary

Concept What It Means Full load Move all data every run Incremental load Move only new or changed data Watermark A saved marker of how far you have loaded Upsert Insert new rows, update existing ones CDC Capture every database change from the transaction log

The takeaway

Full loads are not evil. They are a perfectly reasonable starting point when your data is small and your pipeline is simple. The problem is treating them as the permanent solution rather than the starting point.

As your data grows — and it will — the cost of moving everything every time compounds quietly. Longer runtimes. Higher cloud bills. More strain on the systems your application depends on.

Incremental loading is how you build pipelines that stay fast, stay cheap, and stay reliable as the data behind them grows. Learning the different methods — and knowing when to apply each one — is one of the most practical skills you can develop as a data engineer.

Start simple. Add complexity only when you need it. And stop reloading everything.

Follow for more simplified breakdowns of real-world data engineering concepts.