Full vs Incremental Loading in ETL: How Data Reaches the Data Warehouse

Quick answer:

The choice of full vs incremental load comes down to what moves on each run. A full load replaces the target table. An incremental load moves new or changed rows, detected with a timestamp cursor or change data capture.

Full loads suit small tables and reference data, and are easy to rerun. Incremental loads suit large or fast-changing tables, but need a reliable change signal, a unique key for upserts, delete handling and a rebuild path.

One of the end-goals of having an effective ETL process and ETL Data Warehouse, is the ability to reliably query data, obtain insights, and generate visualizations. An ETL Data Warehouse holds a number of advantages for organizations, allowing them to gather all of their data across the organization (think ERP, CRM, payment information, sales figures) and query this data, utilizing it to find patterns and relationships, identify inefficiencies, create reports and evaluate KPIs.

Traditionally, once data is loaded to the ETL Data Warehouse, it can be viewed using tools such as Tableau, Qlik, Power BI and Looker.

One of the most critical elements of the ETL process is the flow of data into the ETL Data Warehouse.

Transforming and Staging the Source Data

With data being collected and stored in many different systems, each with its own way of storing data, the process of collecting and collating this data, and making it useful for end users, is where ETL comes in.

The data is first extracted from these different systems, each one with its own format. It is then transformed into a format that is standardized and useable across the system. This stage consists primarily of removing extraneous or erroneous data, applying rules, and checking data integrity.

Then, there is staging: usually transformed data is not loaded directly into the target ETL Data Warehouse. Typically, data would enter a staging database, so that if something goes wrong, it is easier to roll back. It is also at this point that audit reports can be generated for regulatory compliance, and problems can be identified and corrected.

Finally, data is published to the ETL Data Warehouse and loaded to the target tables. However, if there has been an error, a corruption of data, a duplication, or missing records, the reliability of the ETL Data Warehouse is compromised. Data loaded will consist of historical data (records that already exist, such as transactions for the previous year) as well as new data that is constantly added.

This is where ETL Data Warehouse testing comes in, to ensure that the data in the ETL Data Warehouse is valid, accurate and complete. With the ETL process, data flows through a pipeline before reaching the ETL Data Warehouse. The entire ETL pipeline must be tested to ensure each type of data is transformed or copied as expected.

In ELT, raw data lands in a staging schema inside the cloud warehouse, then SQL transforms it there, often with dbt. Staging and testing still apply; transformation moves inside the warehouse. See ETL vs ELT for the distinction.

But how does this ETL process in Data Warehouse flow into an ETL Data Warehouse?

The Difference Between Full and Incremental Loading

There are two primary methods to load data into a warehouse:

Full load: with a full load, the entire dataset is dumped, or loaded, and is then completely replaced (i.e. deleted and replaced) with the new, updated dataset. No additional information, such as timestamps, is required.

For example, take a store that uploads all of its sales through the ETL process in data warehouse at the end of each day. Let’s say 5 sales were made on a Monday, so that on Monday night a table of 5 records would be uploaded. Then, on Tuesday, another 3 sales were made which need to be added. So on Tuesday night, assuming a full load, Monday’s 5 records as well as Tuesday’s 3 records are uploaded, an inefficient system, although relatively easy to set up and maintain. While this example is overly simplified, the principle is the same.

Incremental load: only the difference between the target and source data is loaded through the ETL process in data warehouse. Incremental loads differ in how changes are detected (a timestamp cursor, the database log, or a comparison of rows) and in how often they run: in batches, in micro-batches, or as a continuous stream.

Following the previous example, the store that made 3 sales on Tuesday will load only the additional 3 records to the sales table, instead of reloading all records. This has the advantage of saving time and resources, but increases complexity.

Incremental loading is of course much faster than a full load. The main drawback to this type of loading is maintainability. Unlike a full load, an incremental load can’t simply be re-run from scratch when something goes wrong: you either reprocess a window of changes or fall back to a full reload. Order matters too, especially for updates and deletes, so errors can compound the issue as other data queues up.

Today, many organizations are moving away from processing and loading data in large batches, preferring, or driven by business needs, to process in real time using stream processing, meaning that as client applications write data to the data source, data is treated, transformed and saved to the target data store. Today, tools exist to enable this process, such as Apache Kafka, Apache Flink and Spark Structured Streaming. This removes the wait between batches and cuts latency, but it adds complexity of its own: state, late or out-of-order events, and exactly-once delivery all have to be handled.

Full vs incremental load at a glance

Choose a loading mode per table, based on its change signal and recovery requirements.

	Full load	Incremental load
What moves on each run	Entire dataset	Only new or changed rows
Change signal needed	None	Timestamp cursor, log-based CDC or row comparison
Deletes in the source	Disappear on the next run automatically	Need CDC delete events, a soft-delete flag or a periodic reconcile
Rerun after an error	Rerun the job	Reprocess a window or run a full refresh
Load on source and warehouse	Grows with table size	Grows with the volume of change
Best fit	Small tables, reference data, sources without reliable timestamps	Large fact tables, event data, frequently synced sources

Incremental speed gains depend on change volume: row comparison can still scan the entire source, and small tables may not justify incremental overhead.

Most pipelines mix both, using nightly full loads for small dimension tables and incremental loads for orders and events.

How Incremental Loading Works in Practice

A missed cursor update can silently leave warehouse data stale. Design change detection and recovery together.

Pick a change signal

Choose a signal your source maintains reliably:

• Timestamp cursor: Use a column such as updated_at. Store the largest successfully loaded value as a high-water mark, then read rows above it on the next run.
• Log-based change data capture: Use Debezium to read MySQL’s binary log or PostgreSQL’s write-ahead log through logical decoding.
• Row comparison: When neither signal exists, hash each row and compare it with the target.

A source change without a corresponding cursor update remains invisible to the cursor-based query.

Upsert with MERGE on a unique key

Stage changes, deduplicate to the latest version per key, then MERGE into the target. Snowflake, BigQuery and Redshift support MERGE; Redshift’s support reached general availability in April 2023.

MERGE INTO analytics.orders AS t
USING (
  SELECT *
  FROM staging.orders
  WHERE updated_at > (
    SELECT COALESCE(MAX(updated_at), '1900-01-01'::timestamp) FROM analytics.orders
  ) - INTERVAL '2 hours'          -- lookback window for late rows
  QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) = 1
) AS s
ON t.order_id = s.order_id
WHEN MATCHED AND s.updated_at >= t.updated_at THEN UPDATE SET
  status = s.status, amount = s.amount,
  is_deleted = s.is_deleted, updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (order_id, status, amount, is_deleted, updated_at)
  VALUES (s.order_id, s.status, s.amount, s.is_deleted, s.updated_at);

This uses Snowflake syntax; adapt it for BigQuery or Redshift. Require non-null keys and a deterministic tie-breaker if versions share an updated_at value.

Handle deletes

A cursor query cannot see a row that no longer exists. Choose a deletion mechanism:

• CDC delete events: Apply the source’s deletion event to the target.
• Soft-delete flags: Retain the source row with a flag such as is_deleted above.
• Periodic key comparison: Compare source and target keys to find removed records.

Fivetran flags deleted rows in primary-key tables with _fivetran_deleted; Airbyte CDC adds _ab_cdc_deleted_at. Account for these markers in downstream queries.

Allow for late-arriving data

Long transactions can commit with timestamps earlier than the high-water mark. Clock skew and retries can also delay rows.

Re-read a lookback window, as the 2-hour interval does above. Apply the overlap during extraction too, so late rows reach staging. Keyed MERGE safely reapplies the same rows, but a bounded window cannot catch every delay. Reconcile data and widen or backfill the window when necessary.

Make every run idempotent

Running the same load twice must give the same result. Use keyed MERGE instead of blind INSERT, and advance the watermark only after the load commits. Retry the same window after failure.

Keep a full-refresh path to rebuild damaged tables from the source. These controls turn the maintainability concern above into a recovery procedure.

Full and Incremental Loads in Modern ELT Tools

Most teams now choose loading modes through tool settings rather than hand-coding the extraction logic.

Check both extraction and destination settings: Airbyte’s Full Refresh Append reads everything without replacing existing destination rows.

• Airbyte: Offers Full Refresh Overwrite, Full Refresh Append, Full Refresh Overwrite + Deduped, Incremental Append and Incremental Append + Deduped. Cursor-based incremental modes read fields such as updated_at; Deduped modes keep the latest row per primary key. The first incremental sync behaves like a full refresh.
• Fivetran: Runs an initial historical sync, then incremental syncs. Database connectors default to every 15 minutes, configurable from 1 minute to 24 hours, using log-based methods where supported. A re-sync pulls every record again, like a full load.
• dbt: Incremental models use materialized='incremental' and filters inside is_incremental(). Set unique_key for strategies that update existing rows. Adapter-dependent strategies include append, merge, delete+insert, insert_overwrite and microbatch. dbt run --full-refresh rebuilds the table unless full_refresh configuration overrides it. Microbatch, added in dbt 1.9, supports time batches, lookback and date-range backfills.
• Warehouse-native options: Snowflake streams track row changes for tasks to consume. Dynamic tables refresh a declared query using TARGET_LAG, with refresh modes including INCREMENTAL, FULL and AUTO. BigQuery and Amazon Redshift support MERGE for applying incremental changes.

Compare options in ETL Tools. Use an orchestrator such as Apache Airflow or Dagster to schedule extraction and transformation runs and manage dependencies.

Challenges With ETL and Data Warehouse

When it comes to loading the data through the ETL process in Data Warehouse to the ETL Data Warehouse itself, particularly with incremental loading, a number of challenges are encountered.

1. Monitoring: as data is extracted from disparate sources and transformed, there are bound to be errors or anomalies. These can be caused by such things as the expiration of API credentials, or difficulty in communicating with an API. These errors or anomalies have to be identified and rectified as quickly as possible.
2. Incompatibility: new records can be added that render the existing data invalid; for example, a field that is expected to be an integer receives a date value. This is particularly problematic when real-time data is being added, and end-users are querying this data and receiving incorrect or incomplete results, and a bottleneck is created as new records cannot be added.
3. Order: data pipelines are often distributed systems, so that availability is maximized. This can result in data being processed in a different order than how that data is received, and is especially pertinent if data is being updated or deleted.
4. Dependencies: understanding dependencies between processes, or sub-processes, is critical when it comes to ETL management. For example, will you execute process 2 if process 1 failed? This becomes more complex as processes and sub-processes increase.
5. Reconciliation: another key challenge is the reconciliation process to ensure data in an ETL Data Warehouse is correct and consistent. While ETL testing should be done regularly, Data Warehouse reconciliation is a continuous process.

Deletes and late-arriving rows also require explicit handling, as covered in How Incremental Loading Works in Practice above.

These challenges have to be understood, mitigated, and managed.

Use data observability tools to monitor freshness and detect changes in row counts or schemas before they affect reports.

An example of managing this is finding the balance between parallel and serial processing.

Serial processing is when one task is completed at a time, and tasks are run by the processor in a sequence. So one task can start only when the previous one has ended. Parallel processing, as its name implies, is a type of processing in which multiple tasks are completed at the same time.

When it comes to loading data, if processes have no dependencies on one another, they can be processed in parallel, resulting in a significant time saving. If there are dependencies however, this can result in complications, and data will have to be processed serially.

Parallelism can also be achieved with data (splitting a single sequential file into smaller data files to provide for parallel access), pipeline, by allowing several components on the same data stream to run simultaneously, and component, that is running multiple processes on different data streams in the same job simultaneously.

Aiming to achieve these efficiencies can also be seen with cloud data warehouses such as Amazon Redshift and Google’s BigQuery. With Redshift, for example, Columnar Storage and MPP Processing enable high performance analytical query processing. BigQuery is serverless and provides data warehouse as a service, managing the data warehouse and enabling the running of very fast queries on large data sets.

What’s Needed To Be Successful In Production

The ETL process in Data Warehouse is complex and involves many stakeholders. These can include data scientists, data engineers and infrastructure engineers, not to mention those generating and/or consuming the data, such as marketing executives. Ultimately, of course, the entire ETL process, including the ETL Data Warehouse, is useless if the various pieces don’t come together resulting in data that can be trusted by all parties, and acted upon where necessary. This data also has to be available, operational downtime must be minimized, data must be queryable quickly and easily, and all of this should serve the needs of the different types of users using the system.

This of course creates frustration, as often the data scientists get exasperated at the slow pace of the data engineers, while the data engineers get upset about the unrealistic expectations and poor technical understanding of the data scientists, the infrastructure engineers have to keep adapting to ever-increasing resource demands, and end-users demand more features and better performance.

To help those building and consuming dashboards and data visualizations better understand what’s needed to be successful in production, it is important that there is general understanding of the process.

Often there are complaints from end users such as “The dashboard is taking too long to load”, or expectations that might be unrealistic, such as “I need this data before I get into work Monday morning”.

By all users being aware of the process and pitfalls, from those users seeing the ETL architecture in data warehouses, to those consuming the data in the form of dashboards or other BI visualization tools, an organization can successfully leverage the data to succeed in their business goals.

Having said this, there are some concrete steps that can be taken to ensure that the ETL process loads data successfully to an ETL Data Warehouse, and that everyone benefits:

1. Gaining an understanding of the overall system, including both the Data Source and the Target. This will help keep the team focused, minimize the impact of irrelevant data, and ensure seamless and speedy loading of data.
2. Planning: this is arguably the most important step in the process, and yet the one where many teams spend the least amount of time or effort. It’s so true that too many teams jump straight into the ETL process itself, trying to get the data loaded quickly, end up cutting corners, and costing the organization even more down the line when it comes to increased maintenance costs, problems scaling, fixing errors, and the disruption of business operations.
3. Use the Team: by involving all the relevant stakeholders in the process, you are more likely to get buy-in, assistance, insights, and help when things go wrong or take an unexpected turn. Everyone from the marketing team to the engineers should be involved to ensure a smooth process that everyone owns and in whose success everyone is invested.
4. Review often. Ensure performance is consistent, get feedback from users, and review plans and back-ups to ensure every foreseeable situation is planned for.

Keep Things Simple

With the traditional ETL methodology, every aspect of the process, from extracting data, to transforming it and finally loading it to the ETL Data Warehouse, is fraught with risk. The complexity, the moving parts, and the size of the team involved, mean that there are multiple things that can go wrong.

Traditional ETL, while having faithfully served for so long, has drawbacks that make coping with modern business demands increasingly difficult and resource-intense.

Use a managed loader feeding a pay-per-use cloud warehouse. Version SQL transformations in dbt and schedule them after ingestion succeeds. Managed extraction reduces connector maintenance; you still own data correctness.

Start with full loads for small tables. Switch to incremental when full loads become too slow or expensive, but retain a full-refresh path. Confirm a reliable change signal and downstream delete handling before switching. The ETL Tools chapter and tool directory provide options without requiring the same vendor throughout.

Common questions

What is the difference between a full load and an incremental load?

A full load replaces the target dataset; an incremental load moves only new or changed rows. Full loads need no change-tracking signal, while incremental loads require one. See Full vs incremental load at a glance above for recovery requirements and suitable tables.

When should you use a full load instead of an incremental load?

Use full loads for small or reference tables, or sources without reliable change signals. A full refresh also repairs source-target drift or rebuilds results after transformation changes. Keep this recovery option available when the source retains the necessary data.

How do you handle deleted records in an incremental load?

Use a deletion mechanism:

• CDC: Apply delete events.
• Soft deletes: Process a source deletion flag.
• Key comparison: Reconcile source and target keys.

A timestamp cursor cannot detect missing rows. Managed tools may retain deletion markers; downstream transformations must process them.

Is change data capture the same as incremental loading?

No. CDC detects changes; incremental loading applies changes to a destination. Log-based CDC reads database logs and captures deletes as well as inserts and updates. Incremental pipelines can instead use timestamp cursors or row comparisons.

Where to next

Compare loaders in ETL Tools, follow the Python and SQL pipeline in ETL Tutorial, and plan validation with Data Warehouse Testing 101. Document your table’s change signal and recovery path before implementing incremental loading.