Connect SPSync to DuckDB or MotherDuck

DuckDB is a single local file with no server, which makes it a good place to analyse a SharePoint replica on one machine. MotherDuck is DuckDB hosted in the cloud, and SPSync connects to it with the same destination kind, duckdb.

Before you start

For a file: a folder on the machine that runs SPSync. Nothing else to install, because SPSync includes DuckDB.
For MotherDuck: a MotherDuck account, a database and a token from the MotherDuck settings page.

Create the database and a least-privilege user

File: there is nothing to create. SPSync makes the file on its first run. The account that runs SPSync must be able to write to the folder. DuckDB allows one writing process at a time, so close any other tool that has the file open for writing before a sync.
MotherDuck: create the database in MotherDuck first, for example CREATE DATABASE spsync;. MotherDuck access is by token, and SPSync does not limit a token to one database, so use a token from an account that holds only what SPSync should touch.

Allow SPSync to reach it

File: not applicable, because nothing crosses the network. A file on your computer is not reachable from the Azure managed application.
MotherDuck: SPSync connects outbound over HTTPS to MotherDuck. There is no firewall on your side to open, but the machine running SPSync needs outbound internet access to MotherDuck.

Encrypt the connection

A DuckDB file is not encrypted by SPSync, so rely on full-disk encryption. A MotherDuck connection is encrypted in transit by MotherDuck.

Authentication SPSync supports today

A file has no authentication: the operating system’s file permissions decide. MotherDuck uses a token carried in the connection string. Microsoft Entra ID and cloud IAM tokens are not supported. Treat the token as a secret, because it is part of the string.

Connect from SPSync

A file path, or a MotherDuck string:

/Users/you/spsync/replica.duckdb
md:spsync?motherduck_token=PASTE-TOKEN-HERE

Desktop app: open the Destination screen, choose DuckDB / MotherDuck, enter the path or the md: string into File path and choose Test connection.

CLI:

export SPSYNC_DUCKDB_PATH=/Users/you/spsync/replica.duckdb
spsync export-config --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/x --list Projects \
  --pipeline-name spsync_dev --prefix dev \
  --destination-kind duckdb --connection-string-env SPSYNC_DUCKDB_PATH --dataset-name main \
  --client-id <entra-app-client-id>

main is DuckDB’s default schema.

AI assistant: with the admin MCP server connected, a file path can be passed directly. A MotherDuck string holds a token, so you enter it on the secure entry page.

Parquet export

You can also write every table to a Parquet file after each round, for tools such as Power BI that read Parquet. Set parquet_export_dir in the config’s destination: block:

destination:
  kind: duckdb
  connection_string_env: SPSYNC_DUCKDB_PATH
  dataset_name: main
  parquet_export_dir: ./export

Each table becomes <table_name>.parquet, a full snapshot of its current state, soft-deleted rows included, rewritten every round. You can also upload to an abfss:// or s3:// location with parquet_export_bucket_url; gs:// is not supported. For an s3:// bucket give an access key pair (and a session token for temporary keys), or leave them out to use the machine’s AWS credential, plus an optional region and endpoint for S3-compatible storage. For abfss:// give a SAS token, or leave it out to sign in with Entra ID.

The same settings are on the Destination screen in the desktop app and the server (the Parquet export fields under the file path), and an AI assistant can set the non-secret ones and send you to the secure entry page for the keys and token.

What Test connection and doctor check

Both open the file or the MotherDuck database and run SELECT 1. A pass means the path is writable or the token was accepted. When a Parquet export bucket is set, they also list the bucket, which proves its credentials work. They do not write a file. spsync doctor reports it as destination-connectivity.

Common errors

What you see	What it means and the fix
`Could not set lock on file`	Another process has the DuckDB file open. Close it, then run again.
`IO Error: Cannot open file`	The folder does not exist or is not writable by the account running SPSync.
`Invalid Input Error: ... motherduck_token` or an authentication failure	The token is missing, expired or revoked. Create a new one in MotherDuck and update the string.
`Catalog Error: Database ... does not exist`	The MotherDuck database in the `md:` string has not been created.