Forum Discussion
ETL API Loop Code Example Help (POC)
import requests
import json
import time
from datetime import datetime
# Configuration (POC only - do NOT hardcode in production)
BASE_URL = "https://api.company.com" # paste url hereTOKEN = "PASTE_YOUR_BEARER_TOKEN_HERE" # paste bearer token here
HEADERS = {
"Authorization": f"Bearer {TOKEN}",
"Content-Type": "application/json"
}
# for 10 client
clients = [
"Client01",
"Client02",
"Client03",
"Client04",
"Client05",
"Client06",
"Client07",
"Client08",
"Client09",
"Client10"
]# define Source
sources = [
"orders",
"customers",
"products",
"inventory",
"payments",
"employees",
"pricing",
"transactions",
"suppliers",
"shipments"
]# ----------------------------------------------------
# Simple logging list
# ----------------------------------------------------log = []
# ----------------------------------------------------
# Main Loop
# ----------------------------------------------------for client in clients:
print(f"\nProcessing {client}")
for source in sources:
url = f"{BASE_URL}/{source}?client={client}"
#starting timestart = time.time()
try:
response = requests.get(
url,
headers=HEADERS,
timeout=300
)duration = round(time.time() - start, 2)
response.raise_for_status()
# Save raw JSON
with open(f"{client}_{source}.json", "w") as f:
json.dump(response.json(), f, indent=2)
# logging Pipeline thing
log.append({
"Client": client,
"Source": source,
"Status": "Success",
"DurationSeconds": duration,
"Timestamp": str(datetime.now())
})print(f"✔ {source} completed in {duration}s")
except Exception as ex:
duration = round(time.time() - start, 2)
log.append({
"Client": client,
"Source": source,
"Status": "Failed",
"DurationSeconds": duration,
"Error": str(ex),
"Timestamp": str(datetime.now())
})print(f"✖ {source} failed - {ex}")
# Continue with next source
continue# ----------------------------------------------------
# Save execution log
# ----------------------------------------------------with open("ExecutionLog.json", "w") as f:
json.dump(log, f, indent=2)print("\nPOC Complete")
this is sample code according your requirement you modify that. in this you get time impact for complete procees from calling api to store data into fileif my approch is helpfull for you then mark as solution
if you have still confusion then you can contact me
Thank you
v-aatheeque Thank You
1. I will be starting with the POC next week, I see the trial uses F4 and hoping this does not differ when getting F2 on Prod and devops complains
2. Can i start a trial via the PowerBI service (Admin access) becuase right now IT only involved with F2 subscription once all approved?
3. I really have no experience with python notebook and was hoping to share, where i can paste and change the api and place my param and credentials token in? Then i can just repeat for the next api source but it must trigger once data returned from previous api call before proceeding
4. Do you have a simple fabric pipeline with notebook setup to share please to get me started.
Sorry i have so many links and for my POC where it's a push back/challange i am afraid i m not experienced to build best performance POC example where impact/timing is the measure
Regards
From what you've described, I think the engineering team is less concerned about the actual code and more about proving that the ingestion approach is production-ready.
For the POC, I'd focus on demonstrating the following:
Controlled API concurrency so the client-facing API isn't overwhelmed.
Timeout handling (for example, stop processing a source if it exceeds a defined execution time).
Retry logic for transient failures (e.g., retry up to three times before marking the source as failed).
Centralized logging of every API call, including start/end time, duration, status, rows processed, and any error messages.
Proper error handling so a failure in one source or one client doesn't stop the entire ingestion process.
Monitoring and alerting (for example, send an email or trigger a pipeline notification when a failure occurs).
Restart capability so processing can resume from the last successful client/source instead of starting over.
A clear separation between raw ingestion (Bronze) and downstream transformations.
For the POC itself, I would keep the architecture as simple as possible. There are two approaches you could consider:
Option 1 – Single Notebook
Use a Fabric Pipeline to orchestrate the workflow.
Use a single Notebook to iterate through each client.
Within that notebook, loop through the eight API sources for the current client.
Land the raw API responses directly into the Bronze Lakehouse (JSON or Parquet) without introducing transformation logic.
Log each API execution to a Delta log table for auditing and troubleshooting.
If a source exceeds the configured timeout or repeatedly fails, log the failure, move on to the next client, and continue processing.
Configure the Fabric Pipeline to send notifications on failures.
This approach is straightforward and works well for a proof of concept where all APIs have similar authentication, pagination, and processing patterns.
Option 2 – Metadata-Driven Notebook (More Flexible)
Instead of hardcoding the eight APIs in the notebook, store the API definitions in a configuration table (API name, endpoint, timeout, retry count, enabled flag, etc.).
The pipeline still loops through each client, but the notebook reads the configuration table and dynamically processes each enabled API. This makes the solution much easier to maintain—adding or modifying an API becomes a configuration change rather than a code change.
This approach also allows you to define different timeout values, retry policies, or other settings for individual APIs without changing the notebook logic.
Both approaches land the raw data in the Bronze Lakehouse first and keep transformations separate. For a quick POC, Option 1 is perfectly reasonable. If you expect the number of APIs to grow or each API to have different behaviors, Option 2 provides a more scalable and maintainable design.
The main objective of the POC is to demonstrate reliability, operational control, and recoverability rather than building a complete ETL framework. Once the architecture is approved, you can extend it with Silver/Gold transformations, additional optimizations, and more advanced monitoring.
if my approch is helpfull for you then mark as solution
if you have still confusion then you can contact me
Thank you