Python Microsoft Fabric - Keeping lookup tables up to date

One nightly job that replaces small lookup tables from the systems that own them, so every report shows current names.

This page keeps small lookup tables in line with the systems that own them, once a night, with one service. Until a change in one of those systems reaches Fabric, every report that shows a name from such a table is out of date.

The tables before the refresh

The service below replaces three tables, locations, insurers and staff. Before it runs, the staff table still lists STF-104 as active:

The service

One service handles all three tables. Each list of rows comes from the system that owns it, and the service's only job is to hand each list to Fabric.

# -*- coding: utf-8 -*-

# Zato
from zato.server.service import Service

class RefreshLookupTables(Service):

    def handle(self):

        workspace_id = '11111111-2222-3333-4444-555555555555'
        lakehouse_id = '66666666-7777-8888-9999-000000000000'

        conn = self.microsoft.fabric['My Fabric']

        # The current list from one system ..
        locations = [
            {'location_id': 'LOC-01', 'name': 'Riverside',   'city': 'Portland', 'state': 'OR', 'beds': 120},
            {'location_id': 'LOC-02', 'name': 'Oak Hill',    'city': 'Salem',    'state': 'OR', 'beds': 80},
            {'location_id': 'LOC-03', 'name': 'Maple Grove', 'city': 'Eugene',   'state': 'OR', 'beds': 60},
        ]

        # .. from another ..
        insurers = [
            {'insurer_id': 'INS-01', 'name': 'Cascade Health Plan'},
            {'insurer_id': 'INS-02', 'name': 'Pacific Mutual'},
            {'insurer_id': 'INS-03', 'name': 'Evergreen Assurance'},
        ]

        # .. and from a third one ..
        staff = [
            {'staff_id': 'STF-101', 'location': 'Riverside',   'role': 'Scheduling',
                'cost_center': 'CC-100', 'active': True},
            {'staff_id': 'STF-102', 'location': 'Riverside',   'role': 'Front desk',
                'cost_center': 'CC-200', 'active': True},
            {'staff_id': 'STF-103', 'location': 'Oak Hill',    'role': 'Scheduling',
                'cost_center': 'CC-100', 'active': True},
            {'staff_id': 'STF-104', 'location': 'Oak Hill',    'role': 'Billing',
                'cost_center': 'CC-300', 'active': False},
            {'staff_id': 'STF-105', 'location': 'Maple Grove', 'role': 'Scheduling',
                'cost_center': 'CC-100', 'active': True},
        ]

        # .. replace each table with its list ..
        tables = {
            'locations': locations,
            'insurers': insurers,
            'staff': staff,
        }

        counts = {}

        for table_name, rows in tables.items():
            conn.write_table(workspace_id, lakehouse_id, table_name, rows)
            counts[table_name] = len(rows)

        # .. and report how many rows each one has now.
        self.response.payload = counts

The response after a run:

{"locations": 3, "insurers": 3, "staff": 5}

Scheduling it

In the Dashboard, go to Scheduler → Config, click "Create a new job" and pick the cron-style type with 0 2 * * * so the refresh runs at two in the morning, after the day's other loads and before anyone opens a report. The scheduler page shows the form and how to create the same job from code.

What the reports show

A Power BI report is Fabric's way of turning tables into charts, and a chart grouped by locations.name takes its labels from that column. After the refresh, a renamed row shows its new name the next time the report's data is refreshed, and the pipelines and reports page shows how to trigger that refresh from a service too.

Page	What it covers
Loading data into tables	Appending the tables that grow, replacing the ones that describe things
Running a pipeline and refreshing a report	Refreshing the report's data once the tables are current
Tables	write_table and its mode parameter in detail