Reference and master data teams keep in lists, served to every system

Last updated 22 October 2026

Half the business treats a SharePoint list as the source of truth for cost centres, product codes or approved vendors, and every system that needs it calls the Graph API separately.

Cost centres, product codes, approved vendors, office locations and charts of accounts often live in SharePoint lists, because the people who own them can edit them there without raising a ticket. Keeping the list in SharePoint is the right call. The trouble is everyone who reads it: a finance report, a procurement app and a data warehouse each call the list API on their own schedule, each resolve lookups and page around the 5,000-item view threshold in their own code, and each breaks in its own way when Graph throttles.

The list stays where the owner edits it

SPSync reads the master data list and replicates it into a database table on a schedule. It never writes back, so the owner’s editing workflow stays as it is. Every downstream system reads a plain SQL table. The same lists can go to several destinations, one replication each, so a finance database and an analytics warehouse both keep a current copy and neither adds load on SharePoint.

Reference data that joins

The list becomes a table with its choice and lookup columns; managed metadata columns are not replicated yet. Where one reference list points at another, a vendor at its category for example, the lookup is a key column, and downstream queries join the two as they would any SQL tables.

Two details matter for master data. A retired code keeps its row with sp_deleted_at set, so a system that cached a reference to it can still see what it meant. And the schema can change under the owner’s hands without breaking readers: a new column appears on the next run, and a retyped column moves into a new _v2 column on SQL Server, PostgreSQL, MySQL, ClickHouse, Oracle, SQLite and DuckDB, leaving existing queries on the column they expect.

Several sites, several readers

Pick only the reference lists, not the whole site. If the master data is split across site collections, SPSync can replicate several sites into one database with a prefix per site. Set the schedule by how quickly downstream systems need to see a change, then point each system’s queries at its replicated tables.

Because this is shared reference data, the sync should keep running whoever is on holiday. That usually means SPSync in your Azure, or the command line run from a scheduler, with each destination’s config reviewed like any other change.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name master_data_from_sharepoint --prefix master_data_from_sharepoint --destination-kind mssql --connection-string-env SPSYNC_DB_CONNECTION --dataset-name master_data_from_sharepoint --client-id 00000000-0000-0000-0000-000000000000 --site-url https://contoso.sharepoint.com/sites/example --all-lists
spsync doctor replication.yaml
spsync run replication.yaml

pipeline_name: master_data_from_sharepoint
prefix: master_data_from_sharepoint
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: mssql
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: master_data_from_sharepoint
scopes:
  - site_url: https://contoso.sharepoint.com/sites/example
    mode: entire_site
reconcile_interval_days: 7
create_reporting_views: false

# every night at 02:00
0 2 * * * cd /opt/spsync && SPSYNC_DB_CONNECTION="$(cat /etc/spsync/db.env)" uv run spsync run replication.yaml >> /var/log/spsync/run.log 2>&1

# .github/workflows/spsync-run.yml
name: SPSync
on:
  schedule:
    - cron: "0 2 * * *"
  workflow_dispatch: {}
jobs:
  run:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: astral-sh/setup-uv@v3
      - run: uv sync
      - run: uv run spsync run replication.yaml
        env:
          SPSYNC_DB_CONNECTION: ${{ secrets.SPSYNC_DB_CONNECTION }}
          SPSYNC_LICENCE_KEY: ${{ secrets.SPSYNC_LICENCE_KEY }}

# azure-pipelines.yml
schedules:
  - cron: "0 2 * * *"
    branches: { include: [main] }
    always: true
steps:
  - script: uv sync
  - script: uv run spsync run replication.yaml
    env:
      SPSYNC_DB_CONNECTION: $(SPSYNC_DB_CONNECTION)
      SPSYNC_LICENCE_KEY: $(SPSYNC_LICENCE_KEY)

# Windows Task Scheduler action (see /docs/cli/automation/)
$env:SPSYNC_DB_CONNECTION = (Get-Content C:\spsync\db.env)
uv run spsync run C:\spsync\replication.yaml
schtasks /Create /SC DAILY /ST 02:00 /TN "SPSync" /TR "powershell -File C:\spsync\run.ps1"

# ~/Library/LaunchAgents/io.spsync.run.plist, then: launchctl load ~/Library/LaunchAgents/io.spsync.run.plist