Text-to-SQL on SharePoint data depends on the schema

10 October 2026

Tags: Relational databasesAIDesktopCommand line

Which clients have more than two projects at risk, and who owns them? The head of the project office at an engineering consultancy used to send that question to an analyst every Friday. Now she types it into a chat box over the firm’s project data, and the answer comes back as a neat table. The clients are listed as numbers and every owner is a string starting i:0#.f|membership|. Worse, one client appears that she knows has only two projects in trouble.

The team does not blame the model, and they are right not to. It wrote valid SQL and reported what came back. The trouble is what it was given to read: a copy of the PMO’s SharePoint Projects list, written each night into a database table by a Power Automate flow. That copy kept the rows and threw away most of what the rows mean.

The tool could be Copilot in Power BI, Databricks Genie, Snowflake Cortex Analyst, a LangChain SQL agent or something your own team built. Each one reads a schema and writes SQL against it, so each one is only as good as that schema.

Why an LLM writes wrong SQL over a SharePoint export

A text-to-SQL tool sees table names, column names, column types and often a few sample rows. LangChain’s SQL database wrapper puts each table’s CREATE TABLE statement and three sample rows into the prompt by default. That is the model’s entire knowledge of your business. If ClientId holds 17, the model knows a client is involved and nothing about which one.

The 2024 paper “The Death of Schema Linking? Text-to-SQL in the Age of Well-Reasoned Language Models” (Maamari and colleagues) found that a capable model handed the whole schema picks the right tables and columns well without a separate filtering step. Our reading is that the hard part moves onto the schema itself. The Spider 2.0 benchmark (Lei and colleagues, 2024), built from real enterprise databases with large, messy schemas, points the same way: models that do well on the older Spider and BIRD benchmarks solve a much smaller share of its tasks. BIRD hands the model a short “evidence” note with each question explaining what a column or value means, which is exactly the knowledge a SharePoint export strips out.

Our Projects list has a Client lookup into a Clients list, a Status choice column with the values Not started, On track, At risk, On hold and Closed, an Owner person column, a Tags multi-choice column and a Budget currency column. The Budget column was created when the list was first built by importing a spreadsheet, so its internal name is field_7. Here is the table the nightly flow writes:

-- projects_export: rewritten each night by a Power Automate flow from the Projects list
CREATE TABLE projects_export (
    "ID"       text,  -- '214'
    "Title"    text,  -- 'Northern line resignalling, phase 2'
    "ClientId" text,  -- '17'
    "Status"   text,  -- 'At risk'
    "Owner"    text,  -- 'i:0#.f|membership|[email protected]'
    "Tags"     text,  -- 'Rail;#Risk review;#Public sector'
    "field_7"  text,  -- 'GBP 1,250,000.00'
    "Modified" text   -- '14/09/2026 16:02'
);

Every column is text because the flow wrote text. Nothing tells the model that ClientId points at another list, that field_7 is a budget, or that Tags holds several values.

What does a lookup column look like to the model, and what should it look like?

In SharePoint a lookup column shows the client’s name. Underneath, it stores the id of an item in the Clients list, and the SharePoint REST API returns exactly that, as a field called ClientId. The export has ClientId and no Clients table, so the model either returns the number or invents a join to a table it hopes exists. Here is what our tool wrote for the PMO question:

SELECT "ClientId" AS client, "Owner" AS owner, COUNT(*) AS at_risk
FROM projects_export
WHERE ("Status" = 'At risk' OR "Tags" ILIKE '%risk%')
  AND "ClientId" IN (
      SELECT "ClientId" FROM projects_export
      WHERE "Status" = 'At risk' OR "Tags" ILIKE '%risk%'
      GROUP BY "ClientId"
      HAVING COUNT(*) > 2)
GROUP BY "ClientId", "Owner";

The structure is right: count at-risk projects per client, keep clients over two, then list the owners. Its answers are wrong for reasons the model could not see. The client comes back as 17. The owner comes back as a claims string. And because a sample row showed Risk review in the Tags column, the model decided tagged projects count as at risk too, which is how a client with two at-risk projects and one routine risk review made the list.

What the model needs from a lookup is a key it can join on and the name a person would read, both on the row. SPSync writes a single-value lookup as two columns, client_id and client_value, and replicates the Clients list as its own table whose sp_item_id matches. For “which clients” the model reads client_value with no join; for “which public sector clients” it joins to the Clients table.

How multi-choice and people columns break a GROUP BY

Ask the export “how many at-risk projects carry each tag?” and the model has a column holding Rail;#Risk review;#Public sector. A GROUP BY "Tags" treats every combination as its own group, so Rail;#Bid and Bid;#Rail are two rows in the result and no row says how many projects are tagged Rail. The model can split the string with string_to_array or STRING_SPLIT, but then correctness depends on guessing the delimiter from three sample rows, and different export routes write different delimiters.

People columns fail in a way that is harder to see. A claims string such as i:0#.f|membership|[email protected] identifies one person, but a different flow, or the same flow after someone edits it, may write the display name or the email. When old and new rows sit in the same table, GROUP BY "Owner" splits one owner into two groups and both totals are too small. Each number on its own looks plausible, so the error goes unnoticed.

The fix for both is a shape a GROUP BY can use without parsing. A multi-choice column becomes a child table with one row per item and value, keyed (sp_item_id, ordinal). Counting projects per tag is then an ordinary join and group. A person column becomes an owner_user_id key into one shared sp_users table with each person’s display name and email, so each person has one id whichever list mentions them.

What a replicated schema gives the model

Here is the same Projects list after SPSync has replicated the PMO site into PostgreSQL with the table prefix pmo. Supabase and SQL Server get the same tables, SQL Server with its own type names. We have trimmed each table to the columns that matter here; the real ones also carry SPSync’s other metadata columns, such as sp_site_id and sp_created.

CREATE TABLE pmo_projects (
    sp_item_id      BIGINT PRIMARY KEY,
    sp_modified     TIMESTAMPTZ NOT NULL,
    sp_deleted_at   TIMESTAMPTZ NULL,       -- set when the item is deleted in SharePoint
    title           CHARACTER VARYING NULL,
    client_id       BIGINT NULL,            -- joins pmo_clients.sp_item_id
    client_value    CHARACTER VARYING NULL, -- the client's name, as SharePoint shows it
    status          CHARACTER VARYING NULL, -- 'At risk', 'On track', ...
    owner_user_id   UUID NULL,              -- joins sp_users.id
    field_7         NUMERIC(38,9) NULL      -- Budget (internal name from the import)
);

CREATE TABLE pmo_projects__tags (
    sp_item_id      BIGINT NOT NULL,        -- the project
    value           CHARACTER VARYING NOT NULL,
    ordinal         BIGINT NOT NULL,
    PRIMARY KEY (sp_item_id, ordinal)
);

CREATE TABLE pmo_clients (
    sp_item_id      BIGINT PRIMARY KEY,
    title           CHARACTER VARYING NULL,
    sector          CHARACTER VARYING NULL
);

CREATE TABLE sp_users (
    id              UUID PRIMARY KEY,
    login           CHARACTER VARYING NOT NULL,
    display_name    CHARACTER VARYING NOT NULL,
    email           CHARACTER VARYING NULL
);

Budget is now a number, so SUM(field_7) works without a chain of REPLACE calls to strip the currency code and the commas. Dates are timestamps, so “modified this month” compares dates instead of strings that sort 14/09 after 01/10. Calculated columns arrive as a typed column holding the value SharePoint computed. Deleted projects stay in the table with sp_deleted_at set, so “how many projects were removed last quarter” has an answer, and a query on the base table should filter on sp_deleted_at IS NULL.

SPSync names a column from its SharePoint internal name, decoded and lowercased, so Due_x0020_Date becomes due_date and HoursEstimated becomes hours_estimated. Display names get edited, and a database column that renamed itself would break every saved query, so field_7 stays field_7 and you fix it for the model with a view or a comment. Also, the relationships are columns you join on, with no declared foreign key constraints, so a tool that discovers joins only from constraints needs them spelled out in its instructions.

What describe table returns for a SharePoint list

An agent usually gets four tools over a database: list the tables, describe one table, run a read-only query and propose a change. A database MCP server pointed at the replica gives you the first three. The fourth has no counterpart, because SPSync reads from SharePoint and writes nothing back to it. The agent data layer use case covers that setup, and One SQL copy of SharePoint for every agent you run walks through giving several assistants one read-only copy.

The describe call is the moment the model learns your schema. Our Projects list also has a Programme lookup into a Programmes list, which we left out above. Most describe tools run something close to this on PostgreSQL:

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'pmo_projects'
ORDER BY ordinal_position;

   column_name   |        data_type         | is_nullable
-----------------+--------------------------+-------------
 sp_item_id      | bigint                   | NO
 sp_list_id      | character varying        | NO
 sp_site_id      | character varying        | NO
 sp_etag         | character varying        | NO
 sp_created      | timestamp with time zone | NO
 sp_modified     | timestamp with time zone | NO
 sp_author_id    | uuid                     | YES
 sp_editor_id    | uuid                     | YES
 sp_content_type | character varying        | NO
 sp_deleted_at   | timestamp with time zone | YES
 sp_synced_at    | timestamp with time zone | NO
 title           | character varying        | YES
 client_id       | bigint                   | YES
 client_value    | character varying        | YES
 programme_id    | bigint                   | YES
 programme_value | character varying        | YES
 status          | character varying        | YES
 owner_user_id   | uuid                     | YES
 field_7         | numeric                  | YES

The same call on the Tags child table returns four rows:

 column_name |     data_type     | is_nullable
-------------+-------------------+-------------
 sp_item_id  | bigint            | NO
 sp_site_id  | character varying | NO
 value       | character varying | NO
 ordinal     | bigint            | NO

On a real replica each table also ends with a couple of loader bookkeeping columns whose names start _dlt_, and the table list includes the loader’s own _dlt_ tables. We cut them here. One line in the instructions tells the model to ignore them.

Nothing in that output declares a relationship, since SPSync creates no foreign key constraints. The model works the joins out from the names. A programme_id beside a programme_value reads as a lookup whose key matches sp_item_id in pmo_programmes. A table called pmo_projects__tags with sp_item_id at the front of its key reads as one row per project and tag. Asked “which programmes have at-risk rail projects, and how much budget sits in each?”, the model wrote:

SELECT g.title           AS programme,
       COUNT(*)          AS at_risk_rail_projects,
       SUM(p.field_7)    AS budget
FROM pmo_projects AS p
JOIN pmo_projects__tags AS t
  ON t.sp_item_id = p.sp_item_id
 AND t.value = 'Rail'
JOIN pmo_programmes AS g
  ON g.sp_item_id = p.programme_id
WHERE p.status = 'At risk'
  AND p.sp_deleted_at IS NULL
GROUP BY g.sp_item_id, g.title
ORDER BY budget DESC;

The tag filter is an equality on a child row, so Rail never matches Rail freight by accident. Grouping on the programme’s key as well as its title keeps two programmes with the same name apart. The query above could not have told the model that field_7 is the budget. Our describe tool also returns column comments, and we had commented that column by hand.

How do reporting views help a model that only gets one table?

Some text-to-SQL tools work best against one wide table, and some are only given one. SPSync can generate a reporting view per list, named v_pmo_projects here, on PostgreSQL, Supabase, SQL Server and its other relational and warehouse destinations. The view selects every column of the list’s table, joins sp_users for each person column so owner_display_name and owner_login sit on the row, and leaves deleted items out. Lookup display values such as client_value are already on the row. Multi-value columns stay in their child tables, because flattening them into one row would bring back the delimited string problem. Pointed at the view, the tool wrote this for the PMO question:

SELECT client_value       AS client,
       owner_display_name AS owner,
       COUNT(*)           AS projects_at_risk
FROM v_pmo_projects
WHERE status = 'At risk'
  AND client_id IN (
      SELECT client_id FROM v_pmo_projects
      WHERE status = 'At risk'
      GROUP BY client_id
      HAVING COUNT(*) > 2)
GROUP BY client_value, owner_display_name
ORDER BY client_value, projects_at_risk DESC;

Clients and owners come back by name, and Tags never enters the query because it is no longer a column on this table that a model could mistake for a status. The client the export wrongly listed drops out, because its third project carries the Risk review tag but not the At risk status.

Other things that help a text-to-SQL tool

Column descriptions go first. SPSync does not write a SharePoint column’s description into the database as a comment today, so you add each one by hand, one statement per column. On PostgreSQL and Supabase that is COMMENT ON:

COMMENT ON COLUMN pmo_projects.field_7 IS 'Project budget in GBP';
COMMENT ON COLUMN pmo_projects.programme_id IS 'Joins pmo_programmes.sp_item_id';
COMMENT ON TABLE pmo_projects__tags IS 'One row per project and tag, joined on sp_item_id';

On SQL Server the same text goes in the MS_Description extended property:

EXEC sys.sp_addextendedproperty
    @name = N'MS_Description', @value = N'Project budget in GBP',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'pmo_projects',
    @level2type = N'COLUMN', @level2name = N'field_7';

Check that your describe tool reads them. The information_schema query above has no comment column, while psql’s \d+ shows the PostgreSQL comment. If your tool reads only names, point it at a view of your own that aliases field_7 AS budget. Keep these statements in a script you re-run after a column is added, and put comments on tables or your own views, since SPSync recreates its generated reporting views when a list’s columns change.

Next, a short instruction block. Genie spaces have instructions, Cortex Analyst has a semantic model with descriptions and synonyms, Copilot in Power BI has AI instructions on the semantic model, and a LangChain agent has its system prompt. Ours reads:

Lookup: pmo_projects.client_id -> pmo_clients.sp_item_id; client_value is the name.
Columns ending _user_id, and sp_author_id and sp_editor_id, join sp_users.id.
sp_item_id is each table's item key. Other sp_ columns are metadata, not joins.
Multi-value columns live in child tables named pmo_<list>__<column>, joined on
sp_item_id. Prefer v_pmo_ views, which exclude deleted items; on base tables add
sp_deleted_at IS NULL. "At risk" means status = 'At risk'. Tags are labels, never status.
Ignore every table and column whose name starts _dlt_.

Then a small set of example questions with the SQL you would accept for them. Cortex Analyst calls these verified queries, Genie calls them example SQL queries, and in a home-grown tool they are a few-shot block in the prompt. Five or six good ones teach the model your joins faster than any amount of prose.

Last, give the tool a database login that can only read. The model never needs SharePoint access at all:

CREATE ROLE pmo_reader LOGIN PASSWORD '...';
GRANT USAGE ON SCHEMA public TO pmo_reader;
GRANT SELECT ON v_pmo_projects, pmo_projects__tags, pmo_clients, sp_users TO pmo_reader;
ALTER ROLE pmo_reader SET statement_timeout = '15s';

SPSync copies list items and their columns, not SharePoint’s item-level permissions, so the database is where you decide what the model may see. This role is enough for a tool where everyone may see every project.

If different people should see different clients, the generated views will not do it. SPSync creates them as plain views, and PostgreSQL (so Supabase too) checks a plain view’s permissions and row-level security as the view’s owner, so a policy on pmo_projects does not filter rows read through v_pmo_projects. Put the policies on the base tables, grant the base tables in place of the generated views, and have the tool connect as the person asking. Then either tell the tool to add sp_deleted_at IS NULL itself, in place of the “Prefer v_pmo_ views” line, or give it a view of your own created WITH (security_invoker = true), which applies the asker’s policies on PostgreSQL 15 and later, including current Supabase.

How do you test the answers before people trust them?

Write down ten questions people will really ask, write by hand the SQL you would accept for each, and check its result against the SharePoint list once. Those are your golden questions. Run the tool on each one and compare result sets, since two correct queries rarely look alike, running both queries against the replica at the same moment so they see the same sync.

Include questions designed to catch the failures in this article. Ask for at-risk projects when some carry a Risk review tag. Then try “how many are open” after deleting a test project in SharePoint and waiting for the next sync, to check the model leaves deleted items out. Ask something about the budget, so you find out whether the model has learned that field_7 means money.

Re-run the set whenever you change the model, the instructions, the views or the lists being replicated. A change that improves one answer and breaks another shows up here before it shows up in the Friday report.

Setting up the replica for a text-to-SQL tool

Sign in to the SharePoint site, pick the lists the questions are about (here Projects and Clients), choose the database and turn on reporting views. The first sync writes every row; after that SPSync reads only what changed, on a schedule of a few minutes, so answers are as current as the last sync. The chat with your SharePoint data use case page has the short version of these steps.

Pick the database your tool already speaks. PostgreSQL suits most home-grown tools and LangChain agents, Supabase adds row-level security policies and generated APIs on the same Postgres tables, and SQL Server fits a team whose reports already live there; Seven steps from a SharePoint list to a SQL Server table walks through that setup. The same schema shape is what makes a slow Power BI report on a list fast again, as Why Power BI refreshes on SharePoint lists slow down shows, and the licensing page explains how licences are bought.