Clean, typed, deduplicated training and evaluation sets from SharePoint-held records

Last updated 23 October 2026

Every model project that draws on SharePoint records starts with a scripted export, and the export needs cleaning and retyping before it is usable.

An evaluation set has three requirements: the values must have the right types, each record must appear once, and the set must stay still while models are scored against it. A Graph export of a SharePoint list meets none of them. It stops at the 5,000-item view threshold or slows under throttling, it turns every number, date and lookup into a string, and it changes every time it is re-run.

Take three years of service desk tickets, labelled by outcome, wanted for scoring a triage model. The first week goes on the export and the second on cleaning it. The next model project would start the same way, from the same list.

Start from typed tables

SPSync copies the source lists into database tables once and refreshes them on a schedule. The preparation pipeline reads a stable schema in which numbers are numbers and dates are dates, so the first cleaning pass is done before anyone opens a notebook. Large lists replicate the same way as small ones.

Why each row appears once

Every item has a stable primary key, the SharePoint item ID, and one row. Running the sync again duplicates nothing. A lookup is a key column you can join on, and a multi-choice column is a child table, so a label stored as “Billing; Refund” never has to be split by hand.

Deleted tickets keep their row with sp_deleted_at set. A record removed from SharePoint is still in the table, marked as removed, so last month’s evaluation set keeps every row when someone tidies the list.

How do you get a SharePoint list into a pandas DataFrame?

Read it from the replica with one query. The service desk list arrives in Postgres as sd_tickets, with its Tags multi-choice column as the child table sd_tickets__tags, and pandas reads both through SQLAlchemy:

import os

import pandas as pd
from sqlalchemy import create_engine

engine = create_engine(os.environ["SERVICEDESK_REPLICA_URL"])

tickets = pd.read_sql(
    """
    SELECT sp_item_id AS ticket_id, title, description, ticket_type,
           sp_etag, sp_synced_at
    FROM servicedesk.sd_tickets
    WHERE sp_deleted_at IS NULL AND ticket_type IS NOT NULL
    """,
    engine,
)
tickets["ticket_type"] = tickets["ticket_type"].astype("category")

tags = pd.read_sql(
    "SELECT sp_item_id AS ticket_id, value AS tag FROM servicedesk.sd_tickets__tags",
    engine,
)
tag_matrix = pd.crosstab(tags["ticket_id"], tags["tag"]).clip(upper=1)

Dates come back as timestamps and currency as decimals, so there is no astype pass for every column. The same query runs against SQL Server or DuckDB once the connection string and schema name change.

Using a choice column as the label

People already label records in SharePoint without calling it that. A Ticket Type or Outcome choice column is filled in by the person who closed the item, from a fixed list of values, which makes it a usable target for a classifier. SPSync writes a single choice as a text column on the row, so the label sits beside the text it describes. A multi-choice column becomes one row per value in its child table, which the crosstab above turns into one column per tag, holding 0 or 1, for a multi-label model.

Two checks are worth running before training. Count the rows per value, since a value added to the choice list last month will have few examples. And look for the catch-all (“Other”, “General”) that people pick when nothing fits, which is usually better dropped than learned.

Freezing a set for evaluation

A training set can follow the list; an evaluation set has to stand still. Copy the rows you labelled into a snapshot table, or export them to Parquet, and score every model revision against that file. Within a list the primary key has already removed duplicates; across lists or sources, that is still for your pipeline.

For reproducibility, record which copy of the list the snapshot came from. Every row carries sp_synced_at and sp_etag, which changes whenever the item changes in SharePoint, so saving the latest sync time and a hash of the ids and etags beside the file lets you prove later that two runs scored the same rows:

snapshot = tickets.sort_values("ticket_id")
snapshot.to_parquet("evalsets/ticket-type-2026-10.parquet", index=False)
fingerprint = pd.util.hash_pandas_object(snapshot[["ticket_id", "sp_etag"]], index=False).sum()
print(snapshot["sp_synced_at"].max(), fingerprint)

Pick the destination that matches how you work. Postgres or SQL Server fit a database workflow; DuckDB or Parquet fit a notebook on a laptop. BigQuery, Databricks, ClickHouse and Azure AI Search ship today as well. Fabric and Snowflake are still on the roadmap.

Training and Evaluation Sets from SharePoint Records works through the service desk example in full, with the SQL, the Parquet snapshot and the diff between two revisions of the set.

spsync auth login --client-id 00000000-0000-0000-0000-000000000000
spsync export-config --output replication.yaml --pipeline-name ai_data_preparation --prefix ai_data_preparation --destination-kind postgres --connection-string-env SPSYNC_DB_CONNECTION --dataset-name ai_data_preparation --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: ai_data_preparation
prefix: ai_data_preparation
auth:
  mode: delegated
  client_id: 00000000-0000-0000-0000-000000000000
  tenant_id: common
destination:
  kind: postgres
  connection_string_env: SPSYNC_DB_CONNECTION
  dataset_name: ai_data_preparation
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