Overview
Across multiple systems, the client needed to pull data from platforms that did not work like traditional databases. Many of these systems exposed data through REST APIs, requiring custom ingestion patterns instead of simple table copies.
Challenge
- Each REST source had different auth, pagination, rate limits, endpoints, and response formats.
- Some APIs required ID lookups before detail calls; others returned nested JSON.
- Needed one framework instead of one-off pipelines per source.
Solution
MSPowerhouse built a repeatable REST API ingestion pattern using Azure Data Factory. The pattern supported parent-child pipelines, dynamic endpoints, authentication headers, pagination, ForEach loops, raw JSON landing, run-date foldering, and future transformation.
This framework was used or designed across SaaS sources where standard connectors were not sufficient, including systems such as iCIMS, Smartsheet, Jira REST endpoints, and other API-driven platforms.
Technical Execution
- Azure Data Factory Web and Copy activities.
- REST linked services.
- Dynamic endpoint parameters.
- Header-based authentication.
- Bearer token/API key patterns.
- Parent-child pipeline orchestration.
- ForEach loops for object-level extraction.
- Pagination using page, offset, continuation token, or next-link patterns.
- Raw JSON landing into ADLS Gen2.
- Folder structure by source, object, and run date.
- Error handling and partial rerun design.
- Optional flattening for reporting-ready outputs.
Outcome
The client gained a scalable API ingestion framework that could support multiple SaaS and cloud systems.
Impact
This framework became a foundation for integrating modern cloud systems into Azure. Instead of treating each API as a one-off project, MSPowerhouse created a repeatable approach that could be extended across future systems.
