Reading data from Fabric

Pass values into queries and read totals for reports.

This page will show you how to read data from Microsoft Fabric in Python:

  • Passing values into a query
  • Reading totals for a report

Under the hood, conn.query sends T-SQL to the lakehouse's SQL analytics endpoint, the same one the SQL query view in Fabric uses, and returns the rows as a list of dicts.

Connection and IDs

The app registration, the Fabric connection in the Zato Dashboard, and the workspace and lakehouse IDs are the same as in the tutorial, so set them up as described there first.

Passing values into a query

In the tutorial, the location and the status were part of the SQL itself. In this example, a caller of the service sends a location and a date range, and the service returns the occupancy of that location for those days.

The service below will:

  • Take the location and the two dates from its input
  • Hand them to the query through params rather than glue them into the SQL
  • Return the rows to the caller
# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class GetOccupancy(Service):

    name = 'fabric.get-occupancy'
    input = 'location', 'date_from', 'date_to'

    def handle(self):

        # The two IDs from the tutorial ..
        workspace_id = '262ddd3d-3d0d-4495-b80f-6dddda1e23a1'
        lakehouse_id = '73811955-064d-481a-a7e9-f9c563124f9e'

        # .. the query to run ..
        sql = """
        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
        """

        # .. the values, taken from our input ..
        params = {
            'location': self.request.input.location,
            'date_from': self.request.input.date_from,
            'date_to': self.request.input.date_to,
        }

        # .. get a Fabric connection ..
        conn = self.microsoft.fabric['Zato Fabric']

        # .. run the query with the values ..
        rows = conn.query(workspace_id, lakehouse_id, sql, params)

        # .. and return the rows to our caller.
        self.response.payload = rows

After invoking the service with Riverside, 2026-08-30 and 2026-08-31 you'll see:

[{"location": "Riverside", "as_of": "2026-08-30", "occupied_beds": 95, "available_beds": 25},
 {"location": "Riverside", "as_of": "2026-08-31", "occupied_beds": 96, "available_beds": 24}]

About params:

  • Each :name marker in the SQL needs a key of that name in the dict
  • Dates and times go in as text in ISO format, e.g. 2026-08-31 or 2026-08-31 09:00:00
  • You should not use Python f-strings for the values, because a value glued into the SQL becomes part of the statement, so a caller could sends '; drop table occupancy -- and this would lead to an SQL injection attack

Reading totals for a report

In this example, on the first day of each month, a scheduled service reads last month's invoices grouped by insurer, and the totals are returned to callers for further processing, e.g. to be sent over email.

The service below will:

  • Work out where last month begins and ends
  • Run a query that groups the rows and sums them up
  • Return the totals
# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class MonthlyInvoiceSummary(Service):

    name = 'fabric.monthly-invoice-summary'

    def handle(self):

        # The two IDs from the tutorial ..
        workspace_id = '262ddd3d-3d0d-4495-b80f-6dddda1e23a1'
        lakehouse_id = '73811955-064d-481a-a7e9-f9c563124f9e'

        # .. get our time range ..
        today = self.time.today(format=False)

        this_month_start = today.floor('month')
        last_month_start = this_month_start.shift(months=-1)

        # .. for later use ..
        params = {
            'range_start': last_month_start.format('YYYY-MM-DD'),
            'range_end': this_month_start.format('YYYY-MM-DD'),
        }

        # .. our query to run ..
        sql = """
        select
            insurer,
            count(*) as invoices,
            round(sum(amount), 2) as invoiced,
            round(sum(
                case when status = 'paid' then amount else 0 end
            ), 2) as paid
        from invoices
        where sent_at >= :range_start
        and sent_at < :range_end
        group by insurer
        order by insurer
        """

        # .. get a Fabric connection ..
        conn = self.microsoft.fabric['Zato Fabric']

        # .. run the query ..
        rows = conn.query(workspace_id, lakehouse_id, sql, params)

        # .. and return the totals to our caller.
        month = last_month_start.format('YYYY-MM')
        self.response.payload = {'month': month, 'rows': rows}

After invoking the service you'll see:

{"month": "2026-08", "rows": [
  {"insurer": "Cascade Health Plan", "invoices": 8, "invoiced": 14050.00, "paid": 0.00},
  {"insurer": "Evergreen Assurance", "invoices": 8, "invoiced": 13750.00, "paid": 0.00},
  {"insurer": "Pacific Mutual", "invoices": 8, "invoiced": 13450.00, "paid": 13450.00}
]}

See also

PageWhat it covers
Your first Fabric integrationThe app registration, the connection and where the IDs come from
Writing data to FabricWriting the tables these services read
Fabric API - Queriesconn.query, conn.refresh_sql_endpoint and the Spark methods, with every parameter

Learn more

Development documentation Everything about writing services, in one place
Requests and responses What a service receives, what it returns and how to shape both
Integration examples Ready-made code for the systems you are likely to connect to
IDE and debugging Write services in the Dashboard or in your own editor
Data models Declare inputs and outputs and have them validated for you
In-depth API tutorial The full platform tutorial, from installation to production patterns