Paginated OData Ingestion:SAP → Azure SQL

What gets pulled

• Business entities exposed as OData services — sales orders, materials, deliveries, finance documents
• Each entity is one entry in the metadata config, not one new pipeline

Why SAP is a demanding source

• No direct DB access — extraction goes through the OData service layer
• Response size caps — the service will not return an unbounded result set
• Large entities — full extracts are impractical at volume
• Constant change — records are updated in place, so the target must be merged

How the connection is defined

• A single ADF OData linked service pointed at the SAP gateway
• Credentials are Key Vault references, not stored values
• One dataset, fully parameterised — entity path passed in at runtime

Query construction

• The request is assembled from metadata: entity path, $select for required fields, $filter for the watermark, and the paging parameters
• Nothing entity-specific is baked into the connection or dataset

What the config carries

• entity_path — the OData entity set to pull
• schema_name / table_name — target schema and table in SQL
• load_type — full or incremental
• page_size — rows per OData request, tuned per entity
• mapping_file — pointer to the column mapping JSON
• watermark column & last-run value — the incremental boundary

Shape

{
  "entity_path": "A_SalesOrder",
  "schema_name": "sap",
  "table_name": "SalesOrder",
  "load_type": "incremental",
  "watermark_column": "LastChangeDateTime",
  "page_size": 5000,
  "mapping_file": "maps/salesorder.json"
}

Execution flow

• Lookup — read the metadata config from Blob storage
• Filter — keep only entities marked active and due to run
• ForEach — iterate that list
• Execute Pipeline — invoke the child, passing every value as a parameter

Why a master/child split

• Orchestration logic lives in one place and changes once
• The child pipeline stays small, testable and independently deployable
• A failure on one entity doesn't stop the rest of the run
• Parallelism and batch size are controlled centrally at the ForEach

How it works

• ForEach iterates the entity list returned by the metadata lookup
• Each iteration calls the same child pipeline with a different parameter set
• Batch count is kept deliberately conservative — the OData gateway is a shared, live resource
• Failures are captured per iteration, so one bad entity doesn't fail the run

Parameters passed down

Inside the pipeline

• Get watermark — read the last successful load boundary
• Initialise paging state — offset / skip token set to zero
• Until loop — request one page, land it, advance the offset, repeat
• Load to SQL — staged, then merged into the target table
• Update watermark — committed only after the final page completes

Design principles

• Idempotent — a re-run produces the same result, no duplicate rows
• Fail-safe watermark — a mid-loop failure means the next run retries the full window rather than resuming into a gap
• Zero-code onboarding — new entity, new config entry, done

How it works

• Each entity declares a watermark column (e.g. LastChangeDateTime)
• The pipeline reads the last successful watermark before the pull
• An OData $filter bounds the request to rows changed after it
• That filter is re-applied on every page, so the loop pages through the delta only, not the full entity
• Rows are merged into the target on the business key
• The watermark advances only after the last page commits

Full load still available

• load_type: "full" drops the filter and pages through the entire entity
• Used for initial loads, small reference entities, and recovery

The constraint

• SAP OData services cap rows per response — via service-side page size or an explicit $top
• An unbounded request either errors, times out, or silently truncates — the last being the dangerous case, since the pipeline reports success on partial data

The loop

• Set variable — initialise skip = 0, hasMore = true
• Until hasMore == false:
  • Copy activity requests $top={page_size}&$skip={skip} with the watermark $filter applied
  • Page is landed to Bronze and merged onward before the next request
  • skip += page_size
  • hasMore = (rowsReturned == page_size) — a short page means the end
• Safety ceiling — a max-iteration guard so a misbehaving source can't loop indefinitely

Why the termination condition is a short page

• The delta size is unknown at design time — a fixed iteration count would either truncate or waste calls
• A page returning fewer rows than requested is the reliable end-of-set signal
• Where the service returns @odata.nextLink, following it until absent is equivalent and preferable — it removes offset arithmetic entirely

The trap worth knowing about

• $skip-based paging assumes a stable ordering. If the underlying set shifts between calls, rows can be skipped or repeated
• Mitigated by ordering on a stable key and by merging on the business key — a repeated row updates rather than duplicates
• The bounded watermark window also limits exposure, since the delta set is small and short-lived compared to the full entity

What it handles

• SAP technical field → business-readable target column
• Data type alignment between OData and SQL
• $select projection — only required fields cross the wire

Why externalise it

• A source schema change is a config edit, not a pipeline redeploy
• Mapping is reviewable in Git alongside the rest of the metadata
• The same copy activity serves every entity because mapping is injected at runtime

Rules of the layer

• No transformation. Raw fidelity is the entire point
• Partitioned by load date, with each page landed as it arrives
• Append-only — history preserved even when the source overwrites it

Why it earns its place

• Downstream fixes are made by rebuilding Silver and Gold without re-paging the OData service — a meaningful saving when a full delta is dozens of round-trips
• Provides an audit trail of exactly what SAP returned, page by page

What happens here

• Type casting — OData strings become dates, decimals, booleans
• De-duplication — one row per business key, latest version wins; also the backstop against page-boundary repeats
• Standardisation — leading-zero handling, code normalisation, null semantics
• Validation — bad records flagged rather than silently dropped

What lives here

• Conformed dimensions and fact tables
• Business logic and derived measures applied consistently, once
• Models shaped for reporting performance, not for SAP's internal structures

The contract

• Business users and reports consume Gold — never Bronze or Silver
• Definitions live in one place, so two reports can't disagree on a metric

Load pattern

• Pages are written to a staging table first
• A MERGE upserts on the business key — updates existing rows, inserts new ones
• This is also what makes repeated rows across page boundaries harmless
• Wrapped in a transaction — a failed load leaves the target untouched

Access

• Connection strings resolved from Key Vault at runtime

Who reads this data

• Reporting and BI dashboards
• Business applications needing a consolidated operational view
• Analysts running ad-hoc SQL

The business outcome

• ERP data is queryable in SQL instead of via SAP extracts and change requests
• Analytical load is moved off the live ERP entirely
• Refreshed daily without anyone touching a pipeline

What's stored

• SAP OData service credentials
• SQL Database connection strings
• Storage account keys for the ADLS Gen2 landing zone

How it's used

• Linked services reference Key Vault secrets rather than storing values
• Access via managed identity — no credentials in code or config
• Secret rotation happens in the vault with no pipeline change
• Nothing sensitive lands in Git or an ARM template

The flow

• ADF is Git-integrated — every change is a branch and a pull request
• Publishing generates ARM templates from the collaboration branch
• Release pipeline deploys through Dev → Test → Prod
• Environment-specific values — including SAP gateway endpoints — injected via parameter overrides per stage

What it prevents

• No manual changes in the production Data Factory
• Full change history and a one-step rollback path
• Peer review before anything reaches production

Captured per run

• Pipeline run ID, entity, start and end time
• Iteration count and rows per page — not just a run-level total
• Termination reason — short page, nextLink absent, or safety ceiling hit
• Watermark value before and after

Why per-page logging is essential here

• A run-level row count can't distinguish "a quiet day" from "the loop exited after one page"
• Iteration counts make silent truncation visible — the failure mode that produces confidently wrong data
• A mid-loop failure is diagnosable to the exact page and offset

Operational handling

• Retry policy on transient gateway and network failures — common with OData under load
• Alerting on the safety ceiling being reached, which indicates a source or logic problem
• Per-entity isolation — one failing feed doesn't block the run

The constraint that shapes everything

• SAP OData caps rows per response. Any design ignoring this either fails loudly or — far worse — truncates silently and reports success
• Silent truncation is the dangerous failure mode: the pipeline goes green, the data is incomplete, and nobody finds out until someone questions a number

The two composed mechanisms

• Watermark filter bounds what is in scope — the delta, not the entity
• Pagination loop governs how that scope is carried across — bounded requests, landed incrementally
• Neither alone is sufficient: a filter without paging still overruns the cap on a heavy day; paging without a filter walks the entire entity nightly

Why dynamic termination rather than a fixed count

• Delta volume is unknowable at design time — it varies by orders of magnitude day to day
• A fixed iteration count either truncates on heavy days or wastes calls on quiet ones
• Looping until a short page (or an absent nextLink) is self-tuning; the safety ceiling only guards against a misbehaving source

Trade-offs, honestly

• A loop is harder to reason about than a single copy activity — hence per-iteration logging as a first-class concern
• $skip paging assumes stable ordering; business-key merge and a bounded delta window contain the risk rather than eliminate it
• Page size is an empirical tuning parameter per entity, not a constant that can be reasoned to
• Higher upfront design effort — repaid from the second entity onward