3.1.1. Full vs. Incremental Loads

💡 First Principle: Full loads are the "nuclear option"—simple, comprehensive, but resource-intensive. Incremental loads are surgical—efficient, but require knowing exactly what changed. The choice depends on data volume, change frequency, and whether your source system can tell you what's new.

Scenario: A daily sales pipeline processes 10 million records. Full load takes 4 hours; incremental load of 50,000 new records takes 5 minutes. However, incremental load requires logic to identify new records—which means you need either timestamps, CDC, or comparison logic.

Full Load

• Concept: Extract and load complete dataset every run
• When to Use:
  • Small datasets (< 1 million rows)
  • Complete refresh required for accuracy
  • Source doesn't support change tracking
• Drawbacks: High resource consumption, long processing time

Incremental Load

• Concept: Extract only new or modified records
• Methods:
  • Watermark: Track highest processed value (timestamp, ID)
  • Change Data Capture (CDC): Source system tracks changes
  • Delta comparison: Compare current vs. previous snapshot
• When to Use:
  • Large datasets
  • Frequent updates
  • Low latency requirements

Visual: Loading Pattern Decision

⚠️ Exam Trap: Loading data directly from source without staging provides no recovery point. If the load fails midway, you have partial data and no way to resume. Staging data before loading provides recovery points, enables validation, and minimizes impact on source systems.