CDC ( incremental load ) in fabric pipline - help

If your source is a REST API, then true CDC is not available in Microsoft Fabric, because CDC relies on database transaction logs. However, you can implement an industry-standard incremental ingestion pattern that achieves the same outcome.

1. Ingest incrementally from the API (source-side filtering)

If the API supports it, use:

• A lastModified, updatedAt, or similar timestamp
• Or a monotonically increasing ID

In Fabric, this can be implemented using:

• Copy Data activity with a REST connector, or
• Notebook-based ingestion (for complex pagination or auth)

You store and reuse a watermark value (last successful timestamp or ID) between runs to fetch only new or changed records.

2. Land data in a Bronze (staging) area in the Lakehouse

• Store the raw API responses as JSON or Delta
• This provides:
  • Replayability
  • Schema evolution handling
  • Auditability (industry best practice)

This staging step is strongly recommended in modern lakehouse architectures. 

3. Apply changes using MERGE (CDC-style processing)

Once data is in the Lakehouse:

• Use Spark / SQL MERGE INTO on Delta tables to:
  • Insert new records
  • Update changed records
  • Handle deletes (if the API provides delete indicators)

Delta Lake’s MERGE operation is the standard mechanism for CDC-style processing in Fabric Lakehouses. 

4. Handling deletes (often missed)

Industry best practice for APIs:

• If the API provides:
  • A deleted flag > soft delete
  • Or delete events > propagate deletes via MERGE
• If not:
  • Periodic reconciliation or snapshot comparison may be required

Fabric does not automatically detect deletes for APIs—this must be handled explicitly in your logic.