SharePoint to DuckDB and MotherDuck

Last updated 19 October 2026

One way: SharePoint stays the system of record, and SPSync keeps a read-only copy of your lists current in DuckDB and MotherDuck.

Status: Shipped · product: SPSync for Warehouses & Lakes

DuckDB runs inside the process that queries it, so a SharePoint list replicated to a DuckDB file is ready for analysis the moment the run finishes: open it from Python, R, the DuckDB CLI or a notebook, with nothing to install or administer. When more than one person needs the data, the same connection setting can point at MotherDuck instead.

File or MotherDuck

The destination takes one value, held in an environment variable:

• A file path, such as /data/sharepoint.duckdb. SPSync creates the file on the first run.
• A MotherDuck connection string, md: followed by the database name and a motherduck_token. SPSync spots the prefix and writes to MotherDuck.

Tables go into DuckDB’s default main schema unless you name another, so a plain duckdb.connect(path) sees them straight away.

The schema

A project list with a Client lookup, an Owner person column and a multi-choice Tags column becomes pmo_projects, with client_id and client_value, an owner_user_id column that joins to sp_users, and a pmo_projects__tags child table with one row per tag. DuckDB does not enforce the joins between them, and none are declared, so rows can arrive in any order. Types are DuckDB’s own: UUID, TIMESTAMPTZ, JSON.

Columns added in SharePoint show up after the next run. When a column’s type changes, a _v2 column takes the new type and the original keeps its history.

Parquet from the same replication

A DuckDB destination can also export every table as Parquet each run: to a local folder, to Azure Blob or ADLS Gen2, or to Amazon S3 and S3-compatible stores. The Parquet page covers the layout. This is set in the command-line tool’s YAML config today.

How people use it

Recurring reports read the file in Power BI or Metabase without hitting the SharePoint list API on every refresh; see reporting and analytics and KPI dashboards.

Analytics engineers run dbt locally with DuckDB as a fast, disposable warehouse; see dbt on SharePoint.

Analysts who used to export lists by hand for Tableau, Looker or Metabase get one queryable file; see Tableau, Looker and Metabase on SharePoint lists.

Data engineers explore list data in DuckDB before writing it out to a lake; see SharePoint as a data lake.

Steps

1. Sign in with delegated access, or have an admin grant Sites.Selected on the site.
2. Choose the lists or libraries.
3. Set the file path or MotherDuck string.
4. Run. The first run creates the file and loads every item; later runs touch only rows that changed.
5. Open the file, or the MotherDuck database, in the tool you use.

A local DuckDB file takes one writer at a time, so leave writing to SPSync’s schedule and read from other processes. Copying the file is a complete backup. Neither DuckDB nor MotherDuck applies row-level security, so access to the file or database is the permission boundary.

Watch it run

Watch the full video

Chapters are in the video's description.

Watch the walkthrough: the Northwind Analytics demo lists in SharePoint, connecting to Microsoft 365 in the desktop app, picking lists, the run into a local DuckDB file, and the replicated tables and rows.

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