How do you handle API ingestion in Azure Data Factory when the source system is an ERP?

solanki srikanth 0 Reputation points
2026-10-01T11:42:13.6033333+00:00

A common pattern with ERP integrations is to use Azure Data Factory as the orchestration layer and APIs as the source interface, rather than making changes to the ERP itself.

One practical architecture is: ERP/API → Azure Data Factory → ADLS Gen2 → transformation layer → SQL/semantic layer → Power BI

A few implementation details matter here.

1. Keep ingestion separate from transformation

ADF should primarily handle orchestration and movement of source data. The raw response can first be landed in ADLS Gen2 rather than applying extensive business transformations during ingestion.

This gives you a recoverable source layer and makes downstream processing easier to rerun.

2. Separate raw and standardized data

A Bronze/Silver/Gold structure can help when the same ERP contains finance, inventory, procurement, and manufacturing data.

Bronze: source-aligned data

Silver: cleaned and standardized data

Gold: analytics-ready datasets

The exact implementation can vary, but the separation prevents reporting logic from becoming tightly coupled to the source API response.

3. Design for API limitations

ERP APIs often have pagination, authentication, rate limits, incremental extraction requirements, and inconsistent response sizes.

For incremental ingestion, the pipeline should have a reliable watermark or source-side change indicator where the API supports one. Otherwise, repeatedly extracting the complete dataset becomes expensive and increases processing time.

4. Keep business logic downstream

Calculations such as inventory KPIs, production metrics, or financial measures are generally better handled after ingestion and standardization rather than embedded inside every API extraction flow.

This also makes the resulting data reusable for Power BI and other analytical workloads. The interesting part is that the ERP does not necessarily need to be modified to create a modern analytics layer around it.

For those working with ERP APIs in ADF, what pattern are you using for incremental extraction and watermark management when the source API does not expose a straightforward change-tracking mechanism?

Azure Data Factory
Azure Data Factory

An Azure service for ingesting, preparing, and transforming data at scale.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Senthil kumar 2,580 Reputation points
    2026-10-01T12:10:32.77+00:00

    Hi @solanki srikanth

    Your proposed architecture aligns with a pattern commonly used for ERP-to-analytics integrations:

    ERP/API → Azure Data Factory → ADLS Gen2 → Transformation Layer → SQL/Semantic Layer → Power BI

    Keeping ingestion separated from transformation, maintaining raw and curated data layers, and designing around API limitations are all good practices that help improve maintainability, reprocessing, and auditability.

    Regarding your question about incremental extraction and watermark management when the source API does not provide native change tracking, there is unfortunately no universal approach, but the following patterns are commonly used:

    Last Modified Timestamp

    • If the API exposes a LastModifiedDate, UpdatedDate, or similar field, store the latest successfully processed value in a control table.
      • Use that value as a watermark for subsequent extractions.
        • Consider using a small overlap window to account for late-arriving updates.
        High-Watermark Based on Business Keys 
        - Where records are created sequentially, an increasing identifier can sometimes be used as a watermark.

   - Store the highest processed value and retrieve records greater than that value.

   **Snapshot Comparison**

      - If no change indicator is available, land periodic snapshots in the Bronze layer and compare them against the previous extraction.

         - This approach increases storage and processing requirements but provides a reliable method for detecting inserts, updates, and deletes.

         **Hash-Based Change Detection**

            - Generate a hash of significant attributes for each record after ingestion.

               - Compare the current hash with the previous version to identify changes.

                  - This is often implemented in the Silver layer rather than during extraction.

                  **Combined Control Framework**

                     - Maintain a metadata/control table containing:

                           - Source system

                                 - Entity name

                                       - Last successful extraction time

                                             - Watermark value

                                                   - Load status

                                                         - Record counts

                                                            - This simplifies pipeline restart and recovery scenarios.

    In environments where the API offers neither timestamps nor change tracking, I generally prefer full extraction into Bronze combined with downstream change detection in Silver, as it minimizes dependency on source-system behavior and provides a reproducible history of the received data.

    Have others implemented metadata-driven watermark management in ADF for APIs that lack reliable change indicators, and if so, what approach has proven most scalable in production environments?

    Thanks.

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.