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 (capacity units: a heavy reload competing with other workloads can be throttled or rejected), long processing time
• Empty-table window: Run as its own step, TRUNCATE commits immediately, so readers see an empty or partial table until the reload finishes. Load into a staging table and apply it in one atomic step instead

Incremental Load

• Concept: Extract only new or modified records
• Methods:
  • Watermark: Track highest processed value (timestamp, ID). Blind to hard deletes and to rows backdated below the stored value; if a migration resets or renames the change column, runs silently load zero rows
  • Change Data Capture (CDC): Source database records every insert, update and delete from its transaction log. Use it when deletes must propagate or latency is tight (Fabric database mirroring replicates source changes, deletes included, this way)
  • Delta comparison: Compare current vs. previous snapshot. When the source gives no change signal (e.g., an API returning only current state), extract in full and MERGE into the target so only real changes are applied
• When to Use:
  • Large datasets
  • Frequent updates
  • Low latency requirements

Implementing incremental loads:

• Persist the watermark in a control table (one row per source table). Pipeline pattern: Lookup reads the stored value, Copy loads rows where the change column > watermark, then a Stored Procedure/Script activity writes the new MAX after the load commits. Pipeline variables and parameters reset every run (a hardcoded date parameter needs a manual edit after every run), and @pipeline().TriggerTime is the run's start time, not the data's high-water mark.
• Tooling: in a lakehouse, a notebook implements the logic (read only new rows or files, then Delta MERGE INTO or append); a pipeline orchestrates but does not contain the logic. Auto Loader (cloudFiles) is an Azure Databricks feature, not part of Fabric Spark.
• Apply via staging + MERGE: land the extract in a staging table, deduplicate it on the business key, then MERGE into production: WHEN MATCHED → UPDATE, WHEN NOT MATCHED (by target) → INSERT, WHEN NOT MATCHED BY SOURCE → DELETE. Duplicate source keys make MERGE insert duplicate rows (new keys) or fail (keys already in the target). For a full snapshot of a partition, Spark SQL INSERT OVERWRITE of that partition replaces it atomically; MERGE suits a small share of changed rows. Either way production never goes empty, and Delta keeps the prior version for time travel.
• Idempotency: a retried run must leave the same result. MERGE on a unique key, partition overwrite and truncate-reload are idempotent; a plain INSERT duplicates rows on retry.
• Reconcile: compare source and destination row counts after each load. "Succeeded" only means no activity errored; a filter added to the source query, or rows skipped by fault tolerance, drops records silently.

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.