dbt models over replicated SharePoint tables

Last updated 20 October 2026

SharePoint lists cannot be a dbt source directly, so the data either stays out of the model layer or goes through a brittle, hand-maintained loading step first.

dbt can model anything that sits in a database it connects to, and SharePoint lists do not. Project codes, cost centres and approval status kept in SharePoint have no path to a sources.yml entry. Either that data stays out of the model layer, or someone writes a loading script and dbt is asked to trust a table that changes shape whenever the script does.

A source dbt can declare

SPSync replicates the lists into the database or warehouse dbt already uses, with a schema that behaves as a source table should. Column names follow the list, delta sync on a schedule keeps the tables current, and no dbt run ever touches SharePoint. Declare the tables in sources.yml, give them a freshness check tied to the sync schedule, and build staging models on top.

What staging models get to work with

Each list is a table. A lookup column is a key column pointing at another replicated table, so a staging model joins two source() tables on a key and never resolves an ID by hand. Multi-value columns arrive as child tables, one row per value, ready for an intermediate model to aggregate. Deleted items keep their row with sp_deleted_at set, which lets a model tell “removed” from “never existed”. If a flatter shape is easier to start from, SPSync’s generated reporting views can sit under a staging model.

Schema changes without broken models

Someone adds a column, someone removes one, someone turns a text column into a number. New columns appear on the next run and removed columns stay, so a model that selects them keeps compiling. On SQL Server, PostgreSQL, MySQL, ClickHouse, Oracle, SQLite and DuckDB, a retyped column writes to a new _v2 column and the type under an existing model stays put.

Running dbt after the sync

The usual arrangement is the command line: run SPSync as the step before dbt run, in whatever already orchestrates the project, whether cron, Airflow, dbt Cloud jobs or CI. A model that needs the latest data runs after each sync; one that needs a weekly baseline can run after the reconcile, weekly by default. Large lists are supported, so SharePoint sources are not limited to small reference tables, and SPSync never writes to SharePoint, so the project carries no risk to the lists it reads.

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