Forum Discussion
ETL API Loop Code Example Help (POC)
Good day,
i have been hit by engineering team rejecting my archetetcure and wants a POC and they preferred them developing the ETL to fabric using the client facing API and they incur the budget
So the client facing product API using fabric was for a one man, low cost, one ecosystem with copilot benefits but now the challange is that it impacts prod like a clinet having an API, so i need to proof test my 8 sources against stage for 1 client and then 5. So everything runs in a queue meaning that my loop will fater a time send request to add to queeu in product
1. So can i use fabric trial until i get F2 in august to develop this Pipeline with noteback task?
2. have pipeline with a notebook tscript super fast to loop and retreive data, if longer than x per source then stop and alert (email) then move to next client, need job management now in pythin or pipeline to say max 15min stop and report where it failed etc
3. I have not time to learn a very good scripted notebook that stored a credential "service account" as i was looging on as a client to loop per client complete the 8 sources then next client.
With this i think i shud have a bronze level because i dont want to add complxity to transform
Can someone please help and share me a solution or code to work with,and how please to get POC approved.
Please help, jeez, i feel i walked into a wall here becuase the true source the api uses does not have the full bus pord logic per client
Regards
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
10 Replies
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 youv-aatheeque Yash8160 Apologies for the delay
I need to sort out the mvp first, give me a few days. i wll try this in databrick personal web free edition correct?
Hi icassiem
Thanks for reaching out to Microsoft Fabric Community forum.Based on your POC requirements, here's an approach that should keep the solution simple while meeting the engineering team's expectations:
1.You can use the Microsoft Fabric Trial (60 days) to build and validate your POC. Once your F2 capacity is available, you can simply reassign the workspace to the F2 capacity there's no need to rebuild the solution. Just keep Spark workloads lightweight, as F2 provides only 2 CUs.
2.Use a Fabric Pipeline to orchestrate a single Notebook that loops through each client and their 8 data sources. Implement a hard 15-minute timeout per source, retry/backoff for HTTP 429 responses, and write execution details to a Bronze run-log Delta table. If a source fails, log the failure, continue with the next client, and return a JSON execution summary. The pipeline can then use the Office 365 Outlook activity (or another notification mechanism) to send an email summarizing any failures.
3. Avoid logging in as individual clients. Instead, store each client's API credentials in Azure Key Vault and retrieve them securely within the notebook (for example, using `notebookutils.credentials.getSecret()`). This follows the recommended service-account pattern and keeps credentials out of the code.For a POC, a Bronze-only approach is appropriate. Land the raw JSON responses together with ingestion metadata (such as timestamp, client, source, and run ID), and defer any transformations until the architecture is validated.
References : https://learn.microsoft.com/en-us/fabric/fundamentals/fabric-trial
https://learn.microsoft.com/en-us/fabric/data-factory/notebook-activity
Extract, transform, load (ETL) - Azure Architecture Center | Microsoft Learn
Hope this helps !!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
Hi icassiem
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
Hi icassiem
Following up to confirm if the earlier responses addressed your query. If not, please share your questions and we’ll assist further.Yash8160 , v-aatheeque Apologies, i am quickly wrapping up the MVP report and will start with the POC soon. Can i take the script of Yash8160 i place it in the Databricks personal to execute and changke what is needed like the api, credentials etc? I have received the stage api i just need to test in postman first
