SharePoint to ClickHouse

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 ClickHouse.

Status: Shipped · product: SPSync for Warehouses & Lakes

ClickHouse scans and aggregates large tables quickly, which makes it a good home for SharePoint lists that have grown into years of operational history. A Grafana panel that counts incidents by month, or a Metabase question over every approval since the list was created, returns in a moment, and the list data can sit beside the event and log data your ClickHouse already holds.

Connecting, and the two ports

The connection string looks like clickhouse://user:password@host:9440/database?secure=1 and lives in an environment variable. ClickHouse uses two ports:

• Native protocol. 9440 with TLS, as on ClickHouse Cloud. A self-hosted server without TLS in front of it uses 9000 with secure=0.
• HTTP, for loading and mutations. 8443 by default, matching ClickHouse Cloud. A self-hosted server normally listens on 8123; add &http_port=8123 to the string.

ClickHouse has databases but no schemas, so the destination’s dataset name becomes a table-name prefix. With a dataset name of sync, a list table named ops_incidents is created as sync___ops_incidents, and several replications can share one database.

How rows are written

Each list becomes a plain MergeTree table. A run upserts changed rows by deleting the old version and inserting the new one, so the table always holds one current row per item and a query needs no FINAL modifier. Multi-value columns get child tables, one row per item and value, and every person column points at the shared sp_users table.

Soft deletes use ClickHouse’s ALTER TABLE ... UPDATE mutation with mutations_sync = 1, so the deletion is visible when the run ends. ClickHouse queues mutations per table and starts refusing writes when too many are pending; SPSync issues at most one per table per run, which matters only for a table with constant deletion churn.

Text is ClickHouse’s unbounded String, timestamps are DateTime64(6, 'UTC'), and JSON-shaped values such as a location column’s raw form are stored as String. A column added in SharePoint appears on the next run, a removed one stays, and a retyped column gets a _v2 column beside it.

Where it helps

Dashboards over long histories. Grafana or Metabase panels over years of list data; see Tableau, Looker, Metabase and Grafana on SharePoint data.

Operational screens. A wall dashboard of open incidents updates within a few minutes on the default five-minute schedule; see operational dashboards.

A warehouse source. SharePoint lists sit beside other ingested data under the same access model; see SharePoint as a governed warehouse source.

dbt. The key columns give dbt a stable source to model from; see dbt models over replicated SharePoint tables.

Setting it up

1. Authorise SharePoint access with delegated sign-in, or per-site Sites.Selected app permissions for a sync that runs without a user.
2. Create a ClickHouse user that can create tables, and views if you want them.
3. Store the connection string in an environment variable.
4. Choose the lists or a whole site and run. Turn on reporting views if your BI tool prefers a pre-joined shape with deleted rows already filtered out.
5. Set a schedule, every few minutes up to hourly.

ClickHouse does not enforce keys, so SPSync’s upserts are what keep one row per item. SPSync copies list and library metadata, never file contents, and never writes to SharePoint.

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 an empty ClickHouse server, and the replicated tables and analytics queries in ClickHouse Play.

Watch the command-line version of this walkthrough.

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