API Data ingestion Orchestration

we have something similar for several of our pipelines, an api call can simply be done using a copy activity and a webconnector.

Step one is to get the contact in a table , so you can query them, that will be your 1st API call.

AKA: Read the contact from the API and ingest in , in our case a lakehouse table. If the contact are reasoanbly static you can choose to run that step seperately, or keep it as part of your pipleine

The next step is to use a lookup activity which you can feed into a loop. In our case we have 2 separate ID's we need to complete the request ID, both are in the same table

THe lookup is followed by a for each, in which we fill the data from our lookup as variable

Next we use the, in our case two variables, to create a third variable that is a concatenate of the base url and the viariables, to build the complete  url for the API request and that url is used to access the API in the copy data activity

In lakehouse you have two options: Write the json response to a file and use a copy activity outside of your loop ti ingest the files into your bronze table. Fabric will handle this itself , all you have to do is create a mapping in the tab of that copy activity. I would prefer that so I have a visual of the json resonse, which can be handy for trouble shooting. 

The second option is to direct ingest into a lakehouse table.

NOTE the fact the json response can vary per call is a real pain in the XXX .  You will need to find out what the filter array can have as "columns" in the eaxmle i see the filter array can have region, activitytype and activity duration.

I would contact the supplier of the API for detail information with regards to the contruct of that json response. You may end up having to manually create a table, using sparkSQL, and a mapping to get the data.

Once you have it in the table, you have full control, as it is just another table.

last but not least, pagination, not my freind either lol, it depends on the api , but roughly you will have to add a pagination rule to your copy activity. the value of the BodyURL depends on the actual api response, in our case the next link for pagination is  odata.nextlink, but that can vary per API. Use a tool like postman to get a response, and you will see what your link will be, or look at the API documentation. Some API's are well documented, sadly a lot of supplier only document minimally