Cloud concept · Updated

Incremental Loading - Incremental loading ingests only new or changed data since a previously recorded processing point instead of reloading an entire dataset.

In short

Incremental loading reduces ingestion time, source-system load, data transfer, and processing cost by limiting each run to a defined set of changes. Reliable implementations combine change detection, durable progress tracking, and retry-safe target writes so that failures, updates, and late-arriving data do not produce gaps or duplicates.

Cloud Concepts Hub →

6 key aspects of Incremental Loading

Reliable incremental loading combines appropriate change detection, durable checkpoints, bounded batches, retry-safe writes, and explicit recovery procedures.

01
Explicit change detection

Choose a change signal that matches the source: increasing keys for append-only data, reliable modification timestamps for updates, or change logs for inserts, updates, and deletes. A creation timestamp or increasing identifier alone cannot detect every change to existing records.

02
Durable progress tracking

Persist a checkpoint, watermark, bookmark, or processed-object ledger for each independently tracked source. Advance progress only after the corresponding target writes succeed, and preserve enough metadata to diagnose or replay a batch.

03
Bounded and configurable batches

Define extraction boundaries and configure schedule, batch size, parallelism, and source limits. Fixed batch boundaries simplify retries and auditing, but their correctness still depends on source ordering and transaction visibility.

04
Retry-safe target writes

Use stable record keys, deduplication, merges, or transactional writes so replaying a batch does not corrupt the target. Source progress tracking alone does not guarantee exactly-once results.

05
Late changes and deletion handling

Account for late-arriving records and updates with overlap windows, safety delays, or log-based capture where appropriate. Propagate deletions explicitly rather than expecting a timestamp query to discover records that no longer exist.

06
Controlled replay and reconciliation

Support backfills and checkpoint resets without unintentionally duplicating output. Periodically reconcile source and target data to detect missed changes, incorrect filtering, or failed delete propagation.

Cloud services for Incremental Loading

AWS DMS can capture database changes and deliver them to Amazon S3 for downstream processing. AWS Glue can process newly arrived files in scheduled batches, using bookmarks where supported to track source progress. The pipeline can then load prepared data into Amazon Redshift staging tables and merge it into analytical tables. These services address different responsibilities: change capture, durable storage, transformation, and target application. Correctness still depends on stable keys, change ordering, explicit delete handling, and checkpoint coordination; no individual service setting automatically makes the entire pipeline exactly once.

AWS GlueAWS Glue

AWS Glue supports incremental batch processing through job bookmarks for supported sources, including Amazon S3 and JDBC sources. Bookmark enable, pause, and reset behavior controls source-state tracking, but jobs must separately implement appropriate target deduplication, merges, or output cleanup.

Amazon S3Amazon S3

Amazon S3 commonly stores newly ingested files and immutable change batches for incremental pipelines. Object metadata, ingestion-oriented prefixes, manifests, or an external processing ledger help jobs select the intended objects; S3 does not itself track which records a consumer has successfully loaded.

Amazon RedshiftAmazon Redshift

Amazon Redshift can load incremental files from S3 into staging tables with COPY, then apply inserts, updates, or deletes through SQL such as MERGE. Deduplicate and order incoming changes before applying them, and coordinate target commits with pipeline progress tracking.

Incremental Loading exam questions

Real practice exam questions on Incremental Loading, with the correct answer and its explanation. Answer them with every option in the exam simulator.

DEA-C01

A company runs a scheduled AWS Glue Spark job that incrementally ingests JSON files from an Amazon S3 prefix. The job uses job bookmarks and a stable transformation_ctx for its source reader. After several successful runs, a data engineer must replay only the files originally processed after successful run A through successful run B, including run B. Additional successful runs have occurred since run B. All source files remain unchanged in S3. The scheduled job will be suspended during the replay, and an existing output parameter will direct replay results to a separate S3 prefix. The engineer must preserve the current bookmark state so that normal ingestion can resume without rereading previously processed files. Which configuration meets these requirements with the least custom logic?

Show answerCORRECT ANSWER

Run the job with --job-bookmark-option set to job-bookmark-pause, --job-bookmark-from set to run A's ID, and --job-bookmark-to set to run B's ID.

Bookmark pause mode supports replaying a bounded range without updating the saved bookmark state. The from run is excluded and the to run is included, selecting files processed after run A through run B while preserving normal ingestion progress.

Practice Incremental Loading in a full exam

Questions on Incremental Loading in each practice exam, with every answer option and an explanation for each:

Start a free practice exam

What is Incremental Loading

3 min read

Incremental loading is a data ingestion strategy in which each run processes changes rather than the complete source dataset. A pipeline commonly starts with a full load to establish a baseline, then runs scheduled batches containing newly inserted records, updated records, or deletion events. It is not inherently a streaming technique: an hourly database extraction or a nightly batch of newly arrived files is also incremental loading.

The first design decision is how to identify changes. An append-only table might use an increasing identifier, while a mutable table might expose a reliable last-modified timestamp. A pipeline can store a high-water mark representing its last successfully processed boundary and use that value in the next extraction predicate. For example, an orders pipeline could read records with modification times after the previous boundary and up to a fixed upper boundary captured for the current batch. An index on the filtering column can reduce source scanning, although actual performance depends on the query plan.

A timestamp alone does not guarantee completeness. Multiple records can share the same timestamp, source clocks can differ, and transactions can become visible after an extraction has passed their timestamp. Depending on source behavior, use a compound cursor, a safety delay, an overlapping extraction window with deduplication, or transaction-log-based change data capture. A simple increasing identifier detects new rows but does not identify updates to older rows or hard deletes. Deletions require an explicit mechanism, such as tombstones, soft-delete flags, change logs, or periodic reconciliation.

For file-based cloud ingestion, incrementality usually means selecting newly arrived or modified objects rather than rereading every object. Organizing files by ingestion date and recording processed object identities can make selection efficient. However, a new file can contain old business events, and a modified object can contain records already loaded. File-level progress tracking therefore does not replace record-level deduplication or handling of late-arriving data.

In AWS Glue batch jobs, job bookmarks can track supported source processing state across runs. For supported Amazon S3 sources, bookmarks use object last-modified information; for supported JDBC sources, they use bookmark keys subject to ordering requirements. Enable bookmarks, retain consistent transformation contexts for tracked sources, and commit the job after successful processing. Bookmark pause supports processing without advancing saved state, while reset or rewind supports reprocessing. Bookmarks track source progress, not target contents, so reprocessing does not automatically remove previously written output.

Target write behavior must match the source changes. Appending can be sufficient for immutable events, provided retries cannot create logical duplicates. Mutable entities usually require an upsert or merge using a stable business key, with change ordering to prevent older updates from replacing newer values. Advance the checkpoint only after successful target writes. If writing succeeds but checkpoint persistence fails, a retry may replay the batch, so use idempotent writes, staging tables, or transactional coordination rather than assuming exactly-once delivery.

Batch configuration should balance freshness, throughput, and operational overhead. Choose schedules, extraction windows, file sizes, parallelism, retry policies, and source query limits according to source capacity and recovery requirements. Smaller batches improve freshness but increase scheduling and transaction overhead. Monitor checkpoint age, row counts, duplicate rates, rejected records, and source-to-target reconciliation results. Incremental loading generally lowers routine processing cost, but it adds state-management complexity and still requires a deliberate strategy for backfills, schema changes, and recovery.

Learn Incremental Loading in lessons

2 short lessons on Incremental Loading in 2 certification courses.

Open the lessons