Python Microsoft Fabric - Building an API on top of Fabric data
Answer REST calls from Fabric tables, with a cache in front so Spark is not hit on every call.
This page puts a REST API in front of a lakehouse table - a caller sends a location and a date range, a service runs the query and returns the rows.
The query in Fabric
The same rows, run as a query in a notebook against the lakehouse - one row per location and time for the range:
The service
query takes one SQL string with no bind parameters, so every value that goes into it is one the service produced itself - a location from its own list and dates that Python parsed and turned back into text. The service also keeps each answer in the cache for a minute, because a lakehouse query runs on Spark and takes seconds.
# -*- coding: utf-8 -*-
# stdlib
from datetime import date
from http.client import BAD_REQUEST
from json import dumps, loads
# Zato
from zato.server.service import Service
_locations = 'Riverside', 'Oak Hill', 'Maple Grove'
_cache_ttl = 60
class GetOccupancy(Service):
name = 'portal.get-occupancy'
input = 'location', 'date_from', 'date_to'
def handle(self):
location = self.request.input.location
date_from = self.request.input.date_from
date_to = self.request.input.date_to
# Only the locations the table has ..
if location not in _locations:
self.response.status_code = BAD_REQUEST
self.response.payload = {'error': 'Unknown location'}
return
# .. only real dates, which the SQL receives as Python writes them ..
try:
date_from = date.fromisoformat(date_from)
date_to = date.fromisoformat(date_to)
except ValueError:
self.response.status_code = BAD_REQUEST
self.response.payload = {'error': 'Invalid date'}
return
# .. the same question in the last minute has the same answer ..
cache_key = f'occupancy:{location}:{date_from}:{date_to}'
if cached := self.cache.get(cache_key):
rows = loads(cached)
self.response.payload = {'rows': rows}
return
# .. otherwise ask the lakehouse ..
workspace_id = '11111111-2222-3333-4444-555555555555'
lakehouse_id = '66666666-7777-8888-9999-000000000000'
conn = self.microsoft.fabric['My Fabric']
sql = f'''
select location, as_of, occupied_beds, available_beds
from occupancy
where location = '{location}'
and as_of between '{date_from}' and '{date_to}'
order by as_of
'''
rows = conn.query(workspace_id, lakehouse_id, sql)
# .. and keep the answer for a minute.
data = dumps(rows)
self.cache.set(cache_key, data, ex=_cache_ttl)
self.response.payload = {'rows': rows}
The channel
Under Connections → REST → Channels, create a channel with the URL path /api/occupancy, the service portal.get-occupancy and an API key security definition that callers will send. In enmasse YAML:
security:
- name: Portal API Key
type: apikey
username: portal
password: Zato_Enmasse_Env.PortalApiKey
channel_rest:
- name: api.occupancy
service: portal.get-occupancy
url_path: /api/occupancy
security: Portal API Key
data_format: json
Calling it
A caller, here curl, gets the same rows the notebook showed:
curl -H "X-API-Key: <the key>" \
"https://api.example.com/api/occupancy?location=Riverside&date_from=2026-03-11&date_to=2026-03-12"
{
"rows": [
{"location": "Riverside", "as_of": "2026-03-11T10:00:00Z", "occupied_beds": 97, "available_beds": 23}
]
}
The first call for a location and range takes a few seconds, every call in the following minute takes milliseconds.
About the Spark session
The connection opens one Spark session per lakehouse the first time query runs and keeps it for later queries, which is why the first call is the slow one. A session that sits unused is closed by Fabric after a while and the connection opens a new one when needed. When a service will not query a lakehouse again for a long time, conn.close_spark_session(workspace_id, lakehouse_id) closes the session right away instead of leaving it to time out.
See also
| Page | What it covers |
|---|---|
| Looking up Fabric data from your services | When the same rows are needed by many services, all the time |
| Sending reports on a schedule | The same query, delivered as a file instead of an API |
| Queries | query, run_spark and Spark sessions in detail |