Definition:
Incremental Data Ingestion is a data loading strategy where only new, modified, or deleted data is captured and transferred since the last ingestion cycle, rather than reloading the entire dataset every time. This approach significantly reduces processing time, resource consumption, and system load by focusing only on changes.
Key characteristics:
- Processes only changed data (delta)
- Requires change tracking mechanism
- Dramatically faster than full loads
- Reduces system resource usage
- Keeps data warehouse near real-time
Example:
E-Commerce Orders Database Scenario: Daily sync of 10 million orders to data warehouse
FULL INGESTION:
┌─────────────┐ ┌─────────────┐
│ SOURCE │ ALL │ DATA │
│ DATABASE │ ══════► │ WAREHOUSE │
│ 10 Million │ Records │ │
└─────────────┘ └─────────────┘
Time: 5 hours | Resources: High | Network: Heavy
INCREMENTAL INGESTION:
┌─────────────┐ ┌─────────────┐
│ SOURCE │ Changes │ DATA │
│ DATABASE │ ──────► │ WAREHOUSE │
│ 10 Million │ Only │ │
└─────────────┘ (5,000) └─────────────┘
Time: 2 mins | Resources: Low | Network: Light
Real-World Pipeline Example:
┌─────────────────────────────────────────────────────┐
│ INCREMENTAL INGESTION PIPELINE │
└─────────────────────────────────────────────────────┘
Step 1: Check Last Sync Point
┌─────────────────────┐
│ Last Sync: 2024-01-01 12:00:00 │
│ High Watermark ID: 1000000 │
└─────────────────────┘
│
▼
Step 2: Extract Delta
┌─────────────────────┐
│ Query: SELECT * FROM orders │
│ WHERE modified_at > '2024-01-01 12:00:00' │
│ Result: 5,000 records │
└─────────────────────┘
│
▼
Step 3: Transform
┌─────────────────────┐
│ Clean data │
│ Apply business rules │
│ Format for warehouse │
└─────────────────────┘
│
▼
Step 4: Load (Merge)
┌─────────────────────┐
│ INSERT new records │
│ UPDATE modified records │
│ DELETE removed records │
└─────────────────────┘
│
▼
Step 5: Update Checkpoint
┌─────────────────────┐
│ New Sync: 2024-01-02 12:00:00 │
│ New High Watermark: 1005000 │
└─────────────────────┘