SharePoint as a governed warehouse source

Last updated 20 October 2026

SharePoint lists are real operational data, but they sit outside the warehouse's normal ingestion pipeline because there is no clean, governed way to replicate them as a source.

Warehouses tend to take in the ERP, the CRM and the HR system with lineage, documentation and access control on every feed, and then leave SharePoint lists out. Budget approvals or project registers kept in a list end up missing from the board pack, because the only routes on offer are an ad hoc export or a Power Automate flow. Neither gives the platform a consistent schema or reliable change detection, and neither has a plan for the day a business user adds a column without telling anyone.

SharePoint as a governed source

SPSync replicates SharePoint lists into a relational database with a schema that has the same shape every run. The existing ETL or ELT pipeline picks the tables up from there, as it does any other relational source, and lineage, documentation and access control apply as they do everywhere else. If the warehouse is BigQuery or Databricks, SPSync can write to it directly; both are shipped destinations today.

Schema drift, handled before it reaches a model

The column a business user adds appears in the table on the next run. A column they remove stays in the table, so a downstream model that selects it keeps running. On SQL Server, PostgreSQL, MySQL, ClickHouse, Oracle, SQLite and DuckDB, a column whose type changes writes to a new _v2 column and the original keeps its type. Predictable behaviour under drift is what lets a platform owner accept the source at all.

Modelling the replicated tables

Each list is a table with its lookup, choice and person columns; managed metadata columns are not replicated yet. A lookup is a key column, so the warehouse layer models the relationship between two lists directly, with no step to resolve IDs. Multi-value columns are child tables, which keeps dbt models and SQL views clean. Deleted items keep their row with sp_deleted_at set, which gives a slowly changing dimension the deletion dates it needs. Where a flat shape helps, SPSync’s generated reporting views add person names to each row.

Onboarding the source

Choose the lists that deserve to be warehouse sources; most sites have a few. Replicate them into the database that sits ahead of ingestion, confirm the schema, and set the sync to match the warehouse’s refresh cadence. Then document the new tables in the data catalogue like any other source. SPSync never writes to SharePoint, so adding it carries no risk to the lists. Before production, test a source from the desktop app; in production, run it from your existing orchestration with the command line, or from SPSync in your Azure.

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