Can I build a training set from a SharePoint list without exporting it to CSV?

Yes. Replicate the list into Postgres or DuckDB and select the training rows with SQL. Lookups, people and multi-value columns arrive as id columns and child tables, dates arrive as dates, and every ticket is one row keyed by its SharePoint item id, so the cleaning a CSV needs has already happened.

Does SPSync keep the version history of each SharePoint item?

No. SPSync keeps the current state of each item, and a deleted item keeps its row with sp_deleted_at set. If a project needs earlier versions of an item, read them from Microsoft Graph's list item versions endpoint for the items you need, or add a column that records the value you care about from now on.

How do I make an evaluation set reproducible when the list keeps changing?

Save each set as a Parquet file named by the replica's last sync time, with a small manifest holding the row count and a fingerprint of item ids and etags. Rerunning the evaluation against that file gives the same cases, and a diff against the next snapshot shows which tickets were added, edited, relabelled or deleted.

Does SPSync label the data for me?

No. The labels are whatever your people entered in SharePoint, such as the ticket type an analyst confirmed at closure. Reviewed or corrected labels belong in a table of your own keyed by the SharePoint item id, because SPSync only reads from SharePoint and never writes back.

Training and evaluation sets from SharePoint records

11 October 2026

Tags: Relational databases Warehouses and lakes AI Desktop Command line

About 41,000 tickets resolved over three years sit in a SharePoint list called Tickets, each with a title and description the requester wrote and a ticket type an analyst confirmed at closure. Before the service desk lets a new assistant pre-fill the Ticket Type on portal tickets, its manager wants to know how often the suggestion is right, and on which ticket types it is wrong. That history is the evaluation set, and getting it out of SharePoint in a shape a data scientist can trust is where the first week goes.

A model project over SharePoint records starts with a week of cleaning

The Tickets list was designed for analysts handling one ticket at a time in a browser. A model project wants every ticket at once, as typed rows, and the list offers neither on the way out. Someone writes a script against Microsoft Graph or exports a view to Excel, and then spends days learning what the file holds.

The data scientist’s first questions are not about the model. They are about which column holds the label, why the service column holds a number, why ticket 18233 appears twice, and whether the file has every ticket or only the ones the view showed. Each needs an answer before the first score means anything.

What a raw SharePoint export gets wrong

Lookup columns come back as two fields. Graph returns the Affected Service lookup as AffectedServiceLookupId, the item id in the Services list, beside AffectedService, the display text. A script that keeps only the id loses the meaning, and one that keeps only the text loses the join.

Person columns are harder. On the wire, Assigned To is AssignedToLookupId, a small user id that only means something inside that one site. Turning it into a person means reading the site’s hidden User Information List, where the login is a claims string such as i:0#.f|membership|[email protected], so most scripts ship the number.

Multi-value columns such as Tags arrive from Graph as JSON arrays. Writing them to CSV means flattening them into a delimited string, and every later step has to split the string again and hope no tag contains the delimiter.

Dates are UTC strings. A date-only column like Resolved On is stored as local midnight and sent as a UTC instant, so on a UK site in summer a ticket resolved on 3 July reads as 2 July at 23:00. Every summer resolution date is a day early, which goes unseen until a monthly count disagrees with the desk’s own report.

Column names follow SharePoint internal names, which can differ from the names people see. The column the desk now calls Category was created in 2022 as Ticket Type, so its internal name is Ticket_x0020_Type, and renaming it changed nothing underneath. A script written against the display name finds no Category field, and someone has to dig the internal name out of the column settings page.

Graph returns list items in pages, each with a link to the next, and a script that stops following links writes the first page and exits cleanly. Filtering is a trap too: Graph refuses a filter on a column that is not indexed unless the caller opts in with a request header, and warns even then that such queries may fail on large lists. The Tickets list is several times past the 5,000-item view threshold.

On the second run, an export that appends tickets modified since last time writes a ticket edited three times as three rows in three states. A fresh full export avoids that but drops deleted tickets without a trace, so last quarter’s evaluation set and this quarter’s differ with no record of which tickets moved. The file that survives is tickets_final_v3.csv on someone’s laptop, with no record of when it was taken.

The Tickets list as a replica

SPSync reads the list through Microsoft Graph, read-only, and writes it into a database: a full load the first time, then a delta sync every few minutes. For data preparation the useful destinations are DuckDB, a single local file with no server to run, PostgreSQL when the replica also serves reports or an assistant, and Parquet files for a lake or a notebook. The paging, the view threshold and Graph throttling are handled inside the sync.

In the desktop app you connect the service desk site, tick Tickets and Services, and choose DuckDB with a file path. From the command line, generate a config, then add the site’s time zone so date-only columns come out on the right calendar day:

spsync export-config --output servicedesk.yaml --pipeline-name servicedesk --prefix sd \
  --destination-kind duckdb --connection-string-env SD_DUCKDB_PATH \
  --dataset-name main --client-id $SPSYNC_CLIENT_ID \
  --site-url https://contoso.sharepoint.com/sites/servicedesk \
  --list Tickets --list Services
# then add one line to servicedesk.yaml:  site_time_zone: Europe/London
spsync run servicedesk.yaml

spsync discover schema lists each column’s internal name beside its display name. Destination column names come from the internal names, so Ticket_x0020_Type becomes ticket_type and keeps that name whatever the column is called on screen. After the first run the DuckDB file holds:

sd_tickets, one row per ticket keyed by sp_item_id, with title, description, channel, status, ticket_type, resolution_notes, resolved_on as a real DATE, affected_service_id and affected_service_value, assigned_to_user_id and requester_user_id, plus sp_created, sp_modified, sp_etag, sp_deleted_at and sp_synced_at.
sd_services, the lookup target, one row per service.
sd_tickets__tags, the multi-choice Tags column as a child table: sp_item_id, sp_site_id, value and ordinal, one row per ticket and tag.
sp_users, shared by every list, with id, login, display_name, email and is_group, where id is the value the person columns hold.

A choice column such as ticket_type arrives as a text column holding the chosen value. Each ticket is one row however often it was edited.

How do you build a labelled evaluation set with SQL?

The task is to predict ticket_type from what the requester wrote.

CREATE OR REPLACE VIEW eval_ticket_type AS
SELECT
  t.sp_item_id               AS ticket_id,
  t.title,
  t.description,
  t.channel,
  t.affected_service_value   AS affected_service,
  t.ticket_type              AS label,
  t.resolution_notes,
  t.sp_created               AS raised_at,
  t.resolved_on,
  t.sp_modified > t.resolved_on + INTERVAL 2 DAY AS edited_after_resolution,
  CASE WHEN t.sp_created < TIMESTAMPTZ '2026-04-01 00:00:00+00'
       THEN 'dev' ELSE 'holdout' END AS split,
  t.sp_etag
FROM sd_tickets AS t
WHERE t.sp_deleted_at IS NULL
  AND t.status = 'Resolved'
  AND t.sp_created >= TIMESTAMPTZ '2023-10-01 00:00:00+00'
  AND t.ticket_type IS NOT NULL
  AND t.description IS NOT NULL
QUALIFY row_number() OVER (
  PARTITION BY lower(trim(t.title)), CAST(t.sp_created AS DATE)
  ORDER BY t.sp_item_id) <= 3;

The model’s input is the title and description, plus the affected service only if the portal asks the requester for it. Resolution notes stay in the set for the people reviewing failures, but never in the prompt. They name the fix, and the fix usually gives the category away.

edited_after_resolution exists because analysts sometimes paste their notes into the description after closing a ticket. The replica holds the current text, so a ticket modified more than two days after it was resolved may not show what the requester originally wrote. Score those tickets separately and see whether they flatter the assistant.

The split is by time. Tickets raised before April 2026 are dev, for prompt tuning and few-shot examples, and later tickets are the holdout you report on. OneUptime’s article “Why Did Your LLM Golden Dataset Go Stale?” names contamination, holdout examples leaking into prompts or few-shot examples, as one way a golden set goes stale, and a date boundary is the simplest guard.

QUALIFY keeps at most three tickets with the same title on the same day. When the mail service went down one morning in May, the desk logged hundreds of tickets titled “Outlook not working”, and without a cap that one outage would decide the Email score. The same OneUptime article recommends deduplicating so one incident burst does not dominate.

Check the label values before trusting them. On this desk, Hardware was split into Laptop and Peripherals in January 2025, so older tickets carry a label the assistant is never offered. Either map the old value in the view with a CASE, or start the set after the change. Scoring is then a join against the assistant’s suggestions:

SELECT e.label,
       count(*) AS cases,
       avg(CASE WHEN p.suggested = e.label THEN 1 ELSE 0 END) AS accuracy
FROM eval_ticket_type AS e
JOIN read_parquet('runs/2026-10-11/suggestions.parquet') AS p
  ON p.ticket_id = e.ticket_id
WHERE e.split = 'holdout' AND NOT e.edited_after_resolution
GROUP BY e.label
ORDER BY accuracy;

Per-label accuracy is what the manager asked for, and the lowest rows are the ticket types to keep off auto-fill. For fine-tuning, the same view filtered to split = 'dev' goes to a JSON Lines training file with COPY ... (FORMAT JSON).

Making the evaluation set reproducible

Dev Leader’s article “Golden Evaluation Sets for RAG: Synthetic Data, Human Calibration, and Drift” says each case should carry provenance and an immutable corpus revision, because a judgment only holds against the data that produced it. For a SharePoint-backed set, the revision is the moment of the last sync, and the replica records it:

SELECT greatest(max(sp_synced_at), max(sp_deleted_at)) AS revision
FROM sd_tickets;

sp_synced_at is set whenever SPSync writes a row and sp_deleted_at when it flags a deletion, so the later of the two is when the replica last changed. Name the snapshot after it and write a manifest beside it:

COPY (SELECT * FROM eval_ticket_type ORDER BY ticket_id)
  TO 'evalsets/ticket-type/2026-10-11T0600Z.parquet' (FORMAT PARQUET);

COPY (
  SELECT '2026-10-11T0600Z' AS revision,
         count(*) AS cases,
         count(*) FILTER (WHERE split = 'holdout') AS holdout_cases,
         md5(string_agg(ticket_id || ':' || sp_etag, ',' ORDER BY ticket_id)) AS fingerprint
  FROM eval_ticket_type
) TO 'evalsets/ticket-type/2026-10-11T0600Z.manifest.json' (FORMAT JSON);

sp_etag changes whenever the item changes in SharePoint, so the fingerprint changes if any ticket in the set was edited, added or removed. Commit the manifest with the evaluation code and keep the Parquet file in storage. Six months later, a rerun against that file scores the same cases.

DuckDB lets one process write a database file at a time, so take snapshots between syncs. The DuckDB destination can also export every table as Parquet after each sync. parquet_export_dir overwrites a full copy of each table in a local folder, and parquet_export_bucket_url, pointed at Azure storage or S3, keeps a current.parquet plus one file per sync round that is never overwritten, with a random run id as its name. Record that file name in the manifest. Both are settings in the YAML config.

If the replica is in Postgres, attach it to DuckDB read-only (ATTACH 'dbname=servicedesk host=...' AS pg (TYPE postgres, READ_ONLY)). The attached tables need the catalog and the replica’s schema, which is the config’s dataset_name. With dataset_name: servicedesk, every query that reads a replica table uses the qualified name, so the view, the revision query and the join back to the tickets read FROM pg.servicedesk.sd_tickets. The view and the COPY statements stay in the local DuckDB session. Give that connection a role that reads only the tables the set needs.

How do you keep an evaluation set honest as the source changes?

Tickets reopen, get recategorised and get deleted, whether raised in error or removed on request. The replica follows on the next sync. A deleted ticket keeps its row with sp_deleted_at set, so it leaves the view, and you can still see when it went. A diff between two snapshots shows exactly what moved:

SELECT coalesce(n.ticket_id, o.ticket_id) AS ticket_id,
       CASE WHEN o.ticket_id IS NULL THEN 'added'
            WHEN n.ticket_id IS NULL THEN 'dropped'
            WHEN o.label <> n.label THEN 'relabelled'
            ELSE 'edited' END AS change,
       o.label AS old_label,
       n.label AS new_label
FROM read_parquet('evalsets/ticket-type/2026-07-01T0600Z.parquet') AS o
FULL OUTER JOIN read_parquet('evalsets/ticket-type/2026-10-11T0600Z.parquet') AS n
  ON n.ticket_id = o.ticket_id
WHERE o.ticket_id IS NULL OR n.ticket_id IS NULL OR o.sp_etag <> n.sp_etag
ORDER BY change, ticket_id;

A dropped ticket was deleted in SharePoint or no longer matches the filter, for instance because it reopened, and a join back to sd_tickets tells you which. A relabelled ticket is the one to read, because either an analyst corrected a bad label or the taxonomy moved. The Dev Leader article treats each refresh as a review gate: a changed revision says old evidence might be stale, and a person decides the answer. Keep the old snapshot, review the changes, then publish the new revision.

A soft-deleted row suits an audit, but a ticket removed for a data-protection request should leave the replica. Set deletes: hard and tickets deleted from then on are removed outright; rows flagged earlier stay until you delete them. Old snapshots still hold the ticket, so the evaluation folder needs the same retention rules as your other personal data.

Before cutting a revision you plan to keep, run spsync reconcile servicedesk.yaml. It compares every item id in the replica with SharePoint, a quality check that the replica holds exactly the tickets SharePoint holds. Reconcile also runs on its own schedule, weekly by default.

What SPSync does not do for a training set

It does not copy SharePoint version history. Each ticket is its current row, with sp_deleted_at for deletions. If you need the ticket type as first assigned, Microsoft Graph’s list item versions endpoint returns earlier versions, which is workable for a sample of a few hundred tickets. From now on, an Initial Ticket Type column filled by a flow on creation replicates like any other column.

It does not label anything. The labels are what analysts chose. If reviewers correct labels, store the corrections in a table of your own keyed by ticket_id, because SPSync never writes back to SharePoint.

It replicates list items and document library metadata, not file contents. If a ticket carries a screenshot or a log file as an attachment, the replica records only that one exists, in sp_has_attachments. Embeddings are generated on sync only for pgvector and Azure AI Search, which matters if the assistant retrieves similar past tickets; Keeping a SharePoint vector index current as items change covers that side.

SharePoint item permissions do not travel with the rows. Tickets hold names and sometimes health or HR detail, so bound access with database roles and row-level security on the replica and with storage permissions on the snapshot folder. If ticket text must not leave your network at all, Running a local AI model over SharePoint data in DuckDB runs the model against the same kind of file.

For a one-off prototype, a filtered view exported to Excel is fine. A few hundred tickets, read by one person and thrown away, do not need a replica. Once the same set has to be rebuilt next quarter and give the same answer, the export becomes the project.

Text-to-SQL on SharePoint data depends on the schema shows lookup, multi-choice and people columns before and after replication, and Why Copilot cannot count the rows in your SharePoint list uses a similar help desk list. The AI data preparation use case covers the wider setup, and licensing explains how each destination family is licensed.