dbt models on SharePoint data: keys, deletes and views

22 October 2026

Tags: Relational databasesWarehouses and lakesDesktopCommand line

You have been asked to put project spend next to the project register, and the register lives in a SharePoint list. Finance already has the ERP ledger in the warehouse. The Projects list holds the project name, the owning department (a lookup to a Departments list), the owner, a multi-choice Tags column and a budget. Someone exports it to CSV every Monday and drops the file in a bucket, and your staging model spends its first forty lines splitting the tags out of one cell and matching department names back to codes. Last month a project disappeared from the export because someone deleted the item, and the variance report changed under the finance director’s feet.

A replica fixes most of that before dbt sees the data. SPSync copies each list into the warehouse as a normalised table, keeps it current on a schedule and records deletes instead of dropping rows. That still leaves modelling decisions for you, and this post goes through the ones we see analytics engineers get stuck on. The examples use the Projects, Tasks and Departments lists from the sample schema in our schema reference. They are replicated with the table prefix ks, so every column named below is one SPSync really creates.

SharePoint lists as a dbt source

dbt needs a database connection and SharePoint’s APIs are not one, so something has to copy the lists first. SPSync writes to Postgres, SQL Server, BigQuery, Databricks, DuckDB and a long list of other destinations, and dbt connects to the result like any other schema. The dbt use case covers the setup.

Every list becomes one table named {prefix}_{list}. Each row carries metadata columns alongside the list’s own columns: sp_item_id, sp_list_id, sp_site_id, sp_created, sp_modified, sp_author_id, sp_editor_id, sp_deleted_at and sp_synced_at, among others. Column names come from the internal name SharePoint gives each column, so renaming a column’s display name in SharePoint never renames it in the warehouse. That matters more than it sounds, because display names are what list owners change most.

Declare the tables in a sources file. Only the list tables carry sp_synced_at, so set the freshness check per table and leave it off the child tables and sp_users:

version: 2

sources:
  - name: sharepoint
    schema: sharepoint
    tables:
      - name: ks_projects
        loaded_at_field: sp_synced_at
        freshness:
          warn_after: {count: 6, period: hour}
      - name: ks_tasks
        loaded_at_field: sp_synced_at
        freshness:
          warn_after: {count: 6, period: hour}
      - name: ks_departments
      - name: ks_projects__tags
      - name: ks_projects__team_members
      - name: sp_users

There is a catch with freshness on SharePoint data. SPSync sets sp_synced_at on every write, but a sync round that finds no changes writes nothing, so on a quiet list the newest sp_synced_at tells you when someone last edited an item. Departments might go a month without an edit. We give a freshness threshold to the busy lists, where a long gap really does mean the schedule has stopped, and leave it off the reference lists.

Which key to use for SharePoint list items in dbt

sp_item_id is SharePoint’s own item id and the primary key of every list table. It is unique within one list, which is why each list gets its own table, and for most staging models it is all you need.

Two situations need more. If you replicate the same list from several sites into one table (SPSync calls this merge_same_name), item ids repeat across sites, and SPSync adds sp_site_id to the table’s primary key for you. Test the pair with dbt_utils.unique_combination_of_columns and carry both columns into every join. The second case is a dimension that mixes rows from several lists or from another source system. There, build a surrogate key in staging from sp_list_id and sp_item_id, so the warehouse key never depends on a SharePoint number that means nothing anywhere else.

People are simpler. A person column becomes {col}_user_id, pointing at the shared sp_users table, and sp_users.id is derived from the person’s login. The same person has the same id on every list and every run, so it can be the people dimension’s key as it stands.

A staging model for Projects then looks like this:

with source as (

    select * from {{ source('sharepoint', 'ks_projects') }}

)

select
    {{ dbt_utils.generate_surrogate_key(['sp_list_id', 'sp_item_id']) }} as project_key,
    sp_item_id as project_id,
    title as project_name,
    status,
    budget,
    start_date,
    department_id,
    department_value as department_name,
    owner_user_id,
    sp_created as created_at,
    sp_modified as modified_at,
    sp_deleted_at as deleted_at,
    sp_deleted_at is not null as is_deleted
from source

The lookup arrives as two columns. department_id joins to ks_departments.sp_item_id, and department_value holds the display value SharePoint showed on the item, so a report that only needs the name can skip the join.

Multi-value SharePoint columns as child tables

A multi-choice, multi-lookup or multi-person column does not fit in one cell without packing, so SPSync gives each one a child table named {prefix}_{list}__{col}, with one row per value. The Tags column on Projects becomes ks_projects__tags, with sp_item_id, sp_site_id, value and ordinal. A multi-lookup child table has lookup_id and lookup_value where the choice table has value, and a multi-person one has user_id. The primary key is sp_item_id plus ordinal.

Child tables always mirror the item’s current values. When someone unticks a tag, its row goes on the next sync. What a child table does not have is its own sp_deleted_at, so the delete state comes from the parent row. Join to the parent and filter there:

select
    t.sp_item_id as project_id,
    t.value as tag,
    t.ordinal
from {{ source('sharepoint', 'ks_projects__tags') }} as t
inner join {{ source('sharepoint', 'ks_projects') }} as p
    on p.sp_item_id = t.sp_item_id
where p.sp_deleted_at is null

In a star schema the child table is a bridge between the fact and the dimension. When a report wants one row per project with the tags as text, aggregate in an intermediate model with dbt’s cross-database macro, which saves you writing string_agg for Postgres and something else for BigQuery:

select
    project_id,
    {{ dbt.listagg(measure='tag', delimiter_text="', '", order_by_clause='order by ordinal') }} as tags
from {{ ref('stg_sharepoint__project_tags') }}
group by project_id

Handling soft deletes from SharePoint in dbt models

When an item is deleted in SharePoint, SPSync keeps the row and sets sp_deleted_at to the time of the sync that recorded the delete. That gives you something the list itself never did, which is a record that the item existed. It also means every model has to decide what to do with those rows.

Our default is to keep deleted rows in staging, flagged with is_deleted as in the model above, and filter them out in the marts. A month-end comparison, an audit model or a snapshot needs to see the project that vanished, and once staging has dropped it nothing downstream can get it back. The cost is one where not is_deleted in each mart, and a reviewer has to check it is there.

Snapshots need one adjustment. A delete sets sp_deleted_at and leaves sp_modified alone, so a snapshot using the timestamp strategy on sp_modified would never record it. The check strategy watches both columns:

{% snapshot projects_snapshot %}

{{
    config(
        target_schema='snapshots',
        unique_key='sp_item_id',
        strategy='check',
        check_cols=['sp_modified', 'sp_deleted_at']
    )
}}

select * from {{ source('sharepoint', 'ks_projects') }}

{% endsnapshot %}

Soft deletes are the default everywhere. A replication run with the command-line tool can set deletes: hard in its YAML config, which removes the row instead. For a dbt project we would leave it on soft, since a hard delete takes away the very row the audit model needs.

Can the SPSync reporting views be a staging layer?

With reporting views switched on, SPSync creates a view called v_{prefix}_{list} for each list table, so Projects gets v_ks_projects. The view selects every column of the list table, leaves out rows where sp_deleted_at is set, and joins each person column to sp_users to add name and login columns. The owner column comes through as owner_display_name and owner_login next to owner_user_id, and the item’s author and editor get the same treatment. Lookup display values are already on the row as {col}_value, so the view needs no join for those.

That is most of what a first staging model does, and for a quick report or a proof of concept we point a source at the views and move on. Two things make us switch to the base tables for a governed model. The view has no deleted rows, so there is nothing for a snapshot or an audit model to find. And multi-value columns are not in the view at all, because a one-to-many join has no single right shape, so you read the child tables for those whichever way you go.

The views exist on BigQuery, Databricks, ClickHouse, SQL Server, Postgres, MySQL, Oracle, SQLite and DuckDB. Turn them on with the “Create reporting views” toggle in the desktop app, or with --create-reporting-views when you write the config with spsync export-config. When a list gains a column, SPSync recreates the view on the next sync and the new column appears in it.

dbt relationships tests for SharePoint lookup columns

SPSync does not declare foreign key constraints in the warehouse. A lookup column is a logical key, and nothing in the database stops a task pointing at a project that is not there. That is the right place for a dbt test, and it is the test we would write first on any SharePoint project:

version: 2

models:
  - name: stg_sharepoint__tasks
    columns:
      - name: task_id
        data_tests:
          - unique
          - not_null
      - name: project_id
        data_tests:
          - relationships:
              to: ref('stg_sharepoint__projects')
              field: project_id
              config:
                where: "not is_deleted"
      - name: assigned_to_user_id
        data_tests:
          - relationships:
              to: source('sharepoint', 'sp_users')
              field: id

The where config limits the test to live tasks. An empty lookup is null, and the relationships test ignores nulls, so a task with no project does not fail it. Add a not_null test as well where the list owner has made the column required.

Two details are worth knowing before the test first runs. A lookup to a list you have not replicated still gets its {col}_id and {col}_value columns, since SPSync reads both from the item itself, but there is no table for the id to join to. Replicate every list your lookups point at, or the fact table carries ids with nothing behind them. The second detail is the order of a delete. A project deleted in SharePoint keeps its row with sp_deleted_at set, so tasks that still point at it pass the test against staging and then drop out of any mart that filters on is_deleted. If finance needs those tasks, test them against the mart instead and treat a failure as a question for the list owner.

Scheduling dbt runs after a SharePoint sync

SPSync’s default sync interval is five minutes with delegated permissions and one minute with application permissions. A reconcile also runs, weekly by default and configurable, which compares the replica with the list in full. You can run one yourself with spsync reconcile as a QA check, before a month-end close or after an outage on your side or Microsoft’s.

Your dbt models rarely need a five-minute cadence. The arrangement that works best for us is to run the two as one job at the cadence the model needs. A sync round only reads what changed, so running it straight before dbt costs little:

spsync run projects.yaml && dbt build --select source:sharepoint+

The && means dbt starts once the sync round has finished, and the selector rebuilds only the models downstream of the SharePoint sources. Cron, Airflow and a CI pipeline all run that pair the same way, and the CLI automation docs cover each scheduler. If dbt runs in dbt Cloud, where you cannot run another command first, leave SPSync on its own schedule and start the job with dbt source freshness, so the run stops when a busy list has not been written for longer than you allow.

The desktop app runs the sync on its own schedule in the background, which suits a team that has not moved its dbt runs onto a server yet. In that setup the freshness check is your link between the two schedules.

Alternatives to replicating SharePoint for dbt

A replica is not always the right answer. If the list is a few dozen cost centres that change twice a year, a dbt seed checked into the repository is simpler and nobody has to run anything. If you already run Fivetran or Airbyte, check whether their SharePoint connector reads list items or only files in a document library before you rule them out or in. An Azure Data Factory copy activity to CSV, as in the Monday export above, works for a one-off load, and the trouble starts when it has to keep working through column changes and deletes.

Schema changes are where a replica earns its place over a script. A new SharePoint column appears in the table on the next sync and a removed one is never dropped, so models that select it keep compiling. A column whose type changes moves to a new {col}_v2 column on SQL Server, Postgres, MySQL, ClickHouse, Oracle, SQLite and DuckDB. BigQuery and Databricks do not fork a retyped column yet, so on those warehouses agree with the list owners that column types stay fixed.

The warehouse modelling guide has the key table, the star schema mapping and the schema change rules in one place. SPSync writes to many destinations, and the same lists can go to more than one of them, one replication each. If the warehouse you use is not on the list, tell us which one.