Need Guidance Solid Structure for Incremental REST API Pipeline with Pagination in Fabric

Hey Om_Bhartiya  , 

What you’re building is absolutely valid and yes, this pattern is commonly implemented in Microsoft Fabric. Your current approach is technically correct, but for production you can make it cleaner, more scalable, and easier to maintain.

Below is a solid production-ready architecture pattern that many teams use successfully.

Recommended Production Architecture (Medallion Style)

• Control Layer (Watermark + Metadata Driven)
• Instead of hardcoding logic inside activities:
• Maintain a Watermark table in Warehouse
  • source_name
  • last_success_epoch
  • last_run_time
  • status
• Read watermark at pipeline start
• Calculate:
  • startDate = last_success_epoch
  • endDate = current_epoch

This keeps everything restartable and idempotent.

Ingestion Layer (Bronze – Raw Landing in Lakehouse)

For REST pagination + incremental:

Recommended Structure - Instead of complex Web + Until orchestration:

• One Until loop
• Inside loop:
  • Copy Activity (REST source)
  • Pass curPage
  • Set variable = nextPage

Stop condition:

@equals(variables('nextPage'), null)

✔ Keep pagination logic entirely inside the loop
✔ Do NOT mix schema handling here
✔ Always land raw JSON unchanged

Load into:

• Bronze table (append only)
• Partition by ingestion_date

This gives you replay capability.

Schema Handling (Do NOT rely only on OverwriteSchema)

OverwriteSchema works, but in production it can break downstream models.

Better pattern:

• Land raw JSON as string (Variant column or JSON column)
• Flatten in Notebook (Spark)
• Use: df = spark.read.json(...)
• Enable: spark.conf.set("spark.databricks.delta.schema.autoMerge.enabled", "true")

This allows controlled schema evolution in Delta tables.

Silver Layer (Flatten + Normalize)

Use a Notebook or Dataflow Gen2 to:

• Flatten nested JSON
• Standardize column names
• Cast data types
• Remove duplicates (based on business key + timestamp)

Store cleaned table in Lakehouse (Silver).

Warehouse MERGE (Upsert Pattern)

Now perform MERGE from Silver → Warehouse.

Use pattern:

MERGE INTO target t
USING source s
ON t.id = s.id
WHEN MATCHED AND s.updated_at > t.updated_at THEN UPDATE
WHEN NOT MATCHED THEN INSERT

Key best practices:

• Always compare timestamps
• Never blindly overwrite
• Keep it idempotent

Watermark Update (ONLY After Success)

Very important:

Update watermark only if:

• Pagination completed
• Merge completed
• No failure in pipeline

Wrap merge + watermark update in:

• Stored Procedure
  OR
• Sequential Script activities

If failure occurs → watermark must NOT update.

Final Production-Ready Structure

Pipeline Flow:

1. Get Watermark
2. Set start/end epoch
3. Until Loop (REST pagination → Bronze append)
4. Notebook (Flatten + Deduplicate → Silver)
5. MERGE Silver → Warehouse
6. Update Watermark
7. Log success/failure

This is a very common enterprise pattern in Fabric.

If this explanation helped, please mark it as the solution so others can find it easily.

If it helped, a quick Kudos is always appreciated it highlights useful answers for the community.

Thanks for being part of the discussion!