SharePoint list to Azure Synapse Analytics

Last updated 22 October 2026

Status: Coming soon · product: SPSync for Warehouses & Lakes . We prioritise destinations by demand. Book a call to tell us you need it.

SPSync copies SharePoint lists one way into files and databases that Azure Synapse can read, and SharePoint stays the system of record; SPSync has no Synapse destination yet. Synapse pipelines can also copy a list themselves with Microsoft’s SharePoint Online List connector. This page compares the three ways in and what each does to your columns.

How do I copy a SharePoint list into Azure Synapse?

What the SharePoint connector does with each column type

Microsoft’s SharePoint Online List connector page covers Azure Data Factory and Synapse pipelines together. Its type map turns a Lookup column into Int32, and a Person or Group column into Int32 as well, so the copy carries the SharePoint item or user ID and no name. Managed Metadata, Attachment, Task Outcome and External Data columns are listed as unsupported, and the page notes that you cannot select more than one choice data type for a SharePoint Online List source. The page does not describe incremental reads, so a pipeline reads whatever rows its OData query selects on each run.

SPSync differs on lookups, people and multi-choice columns. A lookup arrives as {col}_id and {col}_value, a person as {col}_user_id joined to an sp_users table with the name and email, and a multi-choice column as a child table. It shares two of the connector’s gaps: managed metadata columns are skipped and reported on each run, and attachments are not copied. The schema reference lists every column type. If you already run a Synapse pipeline and only need text, number and date columns, the connector may be all you need; the comparison with Azure Data Factory goes further.

Reading SPSync’s Parquet from a serverless SQL pool

Replicate the lists to the shipped Azure Data Lake Storage Gen2 destination. Each table becomes a folder in your container with a current.parquet snapshot replaced on every run, plus one history file per run. A serverless SQL pool reads the snapshot in place with OPENROWSET:

SELECT *
FROM OPENROWSET(
  BULK 'https://<account>.dfs.core.windows.net/<container>/<prefix>/it_tickets/current.parquet',
  FORMAT = 'PARQUET'
) AS tickets;

Wrap that in a view and Power BI or any SQL client sees a table. The same pattern works for each child table and for sp_users, so a second view can join tickets to the people assigned to them. The run history files let a query go back to an earlier run.

Trade-offs of the serverless route

• Cost per query. Microsoft bills serverless SQL pools for the data each query processes, so a dashboard that refreshes often against a large list reads the whole snapshot each time.
• Deleted items are in the file. Each snapshot includes deleted items with sp_deleted_at set; add WHERE sp_deleted_at IS NULL to the view.
• Columns follow the file. A column added in SharePoint appears in the next snapshot. A view that lists its columns explicitly will not show it until you add it.

A SQL replica in Azure SQL

SPSync’s Azure SQL destination keeps each list as a table in Azure SQL Database, updated from SharePoint’s delta query on every run, with upserts and soft deletes. Power BI and applications read it directly, and a Synapse pipeline can copy from it into a dedicated pool if your warehouse needs the data there. The same Synapse documentation now carries a tip that anyone new to data warehousing should start with Fabric Data Warehouse, so if you are choosing a platform today, the Fabric routes are worth reading too.

What a native Synapse destination would add

Writing straight into a dedicated SQL pool would give you tables with SPSync’s usual upserts and soft deletes, without a serverless view layer on top. Before it can be built, the destination has to settle whether it targets a dedicated pool, a serverless pool or both, how tables are distributed and indexed, and how loads are staged and signed in. Delta sync, the schema mapping and soft deletes come from SPSync’s engine, so a Synapse destination would inherit them.

Tell us which pool type you run, roughly how many lists and rows you would sync, and how current the data needs to be. That decides the order of the roadmap, and we will contact you when Synapse ships. Like every destination, it would arrive in the desktop app, SPSync in your Azure and the command-line tool at once.

# This destination has no --destination-kind in the command line today.
# In the meantime, run this from a shipped destination (Postgres or SQL Server)
# and repoint --connection-string-env at your database.

# YAML for this destination's kind ships with its --destination-kind support.

# 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