What replaces the Copilot in SharePoint site exclusion list after 1 November 2026
Disclosure: I work on M365 Scouts, a vendor that makes read-only audit scripts for Microsoft 365. This post is about Microsoft's own controls and doesn't need our tools. If you excluded SharePoint sites from Copilot using the preview cmdlets (KnowledgeAgentScope, KnowledgeAgentSelectedSitesList, KnowledgeAgentSelectedSitesListOperation), those settings are only honoured until 1 November 2026. The cmdlets are retired. Microsoft says general availability of Copilot in SharePoint begins rolling out on 30 September 2026. What replaces them: Site AI settings. Set by the site owner (Settings > Site AI). They hide the Copilot button on that site for visitors. Restricted Content Discovery. Set by a SharePoint admin, for example Set-SPOSite -Identity <site-url> -RestrictContentOrgWideSearch $true. It limits discovery of the site in organization-wide search and Copilot responses, and removes the AI entry points on that site. It needs a Copilot licence and SharePoint Advanced Management, and it covers SharePoint sites only, not OneDrive. On sites over 500,000 items an update can take more than a week. Neither control changes permissions. People who already have access can still open the files, and Microsoft describes Restricted Content Discovery as a temporary control to use selectively. Restricted SharePoint Search is also retiring, and new enablement has been blocked since 31 July 2026. So the real work before 1 November is knowing which sites are overshared: Anyone links, Everyone except external users, broken inheritance, ownerless sites and missing sensitivity labels. Copilot in SharePoint also needs a Microsoft 365 Copilot licence for each user. Sources: Microsoft Learn pages on Copilot in SharePoint, Restricted Content Discovery and Restricted SharePoint Search. Please check them against your tenant, because the GA page is still changing.
Moving Files from a Shared Drive to a SharePoint site
Hi, I've seen the moving of files from a Shared Drive to a SharePoint site can be tricky, depending on the size of the files that are to be moved. What is the best and most effective way to move folder, files. I'm not sure yet of the number of folders and size of the files that will need to be moved? Can anyone provide an advise on this, please.. Regards Chris
Sharing with an external user who has changed their email
I have had an issue with trying to share a folder with somebody who changed their email from, for illustrative purposes only, mailto:email address removed for privacy reasons to mailto:email address removed for privacy reasons. because his company EW had merged with Elsewhere. I believe that the second email was an alias for the first as both his actual addresses will reach him. The issue was that when I went to share a folder with his new email it would default back to his original email every single time. Eventually our IT department deleted all trace of his original email from Sharepoint which then recognised and allowed me to share with his new email. My question is - how does Sharepoint know these two different emails are the same person? Is it randomly deciding there is enough similarity in the email addresses- which in my view would be very dangerous, or does it know that both domains are registered to the same organisation?MGDC for SharePoint: AadObjectId may sometimes be missing in User metadata (fixed)
We identified issues affecting specific Microsoft Graph Data Connect (MGDC) for SharePoint scenarios. In some cases, user identity records in the SharePoint Sharing and SharePoint Groups (SPGroups) datasets lacked the Entra ID object ID (formerly Azure Active Directory Object ID, or AadObjectId). As a result, the same user could appear differently across reports, causing discrepancies in reported user counts. We resolved the issue by improving the identity resolution. The Sharing and SPGroups datasets now use Entra ID identity information to retrieve the complete and accurate AadObjectId values. We also identified a scenario where a user was deleted from Entra Id but still showed in the dataset either with a stale or an empty value for AadObjectId. Going forward, those users will consistently show up with an empty AadObjectId. If you use the Sharing or SPGroups datasets, you may notice more accurate user information and changes in user counts compared with earlier reports. User objects may now include AadObjectId values where these did not show before and stale user objects will show up with empty values for AadObjectId. Delta datasets will also reflect these updated or removed objects as a result of this fix. This update is being rolled out in the next few weeks. It improves the accuracy and consistency of user identities in permissions datasets, giving customers greater confidence when reviewing which users have access to SharePoint and OneDrive content.MGDC for SharePoint: New Columns in September 2026
The MGDC for SharePoint team continues to improve the available datasets. This time around, we are introducing additional columns to some of our existing datasets. Here is a summary based on the different scenarios they support: Archive – Extends support for the SharePoint Archive feature. Includes a site property for size of archived files and a file property to indicate that the file is archived. E-signature – Supports the E-signature by adding a file to indicate there is a digital signature and the provider’s name. Filtering – The RootWebTemplateId was added to SharePoint Groups and SharePoint Permission dataset to facilitate filter by type of site, like it is currently available for Sites, Files and File Actions. Hub Site – Extends support for Hub Sites, introducing a column for ParentHubSiteId, to represent a hub site hierarchy. Restricted – Multiple columns indicating restricted flags in sites, including Restricted Access Control (RAC), Restricted Content Discovery (RCD) and Restricted Content Org-wide Search Policy. Retention – Support a set of file properties about Retention Labels, including label name, type, who applied it and when it was applied. Sharing – Extends the SharePoint Permissions properties to support the new sharing model. This includes information about the Main Link and Variants. Versions – Extends support for versions, including version storage metrics and version history policy. Here is a summary of all 26 columns in 4 datasets (Sites, Groups, Permissions and Files): Dataset Column Data type Description Sites ArchiveStorageUsed Number Total size of all archived storage in bytes Sites IsRestrictContentOrgWide SearchPolicyEnforcedOnSite Boolean Restricted Content Org-wide Search Policy Sites IsRestrictedAccess ControlPolicyEnabled Boolean Restricted Access Control Sites IsRestrictedContentDiscovery forCopilotAndAgentsEnabled Boolean Restricted Content Discovery Sites ParentHubSiteId String, GUID Parent hub site for this hub site Sites StorageMetrics, VersionCount Number Number of versions Sites StorageMetrics, VersionSize Number Total size of versions in bytes Sites StorageMetrics, VersionMetadataSize Number Total size of version metadata in bytes Sites VersionHistoryPolicy String Version history policy. Possible values: None, Automatic, Manual. If manual, uses “major version limit”. Sites MajorVersionLimit Number Limit for number of major versions Groups RootWebTemplateId Number Root web template id Groups Description String Description of the group Permissions RootWebTemplateId Number Root web template id Permissions SharingVariant String Sharing link variant. Possible values include: None, PasswordProtected, TrackLinkUsers, Embeddable, Ephemeral, RestrictToExistingRelationships, MainLink, NonDurableRedeem. Multiple values can be set at once. Permissions MainLinkAudience String Main link audience. Possible values include: Organization, Empty (people with existing access). Permissions MainLinkRole String Main link role. Possible values include View, Edit, others. Files RetentionLabel Object Retention label Files RetentionLabel, AppliedByEmail String Email of user who applied retention label Files RetentionLabel, AppliedByName String Name of user who applied retention label Files RetentionLabel, AppliedTime DateTme Time when retention label was applied Files RetentionLabel, Name String Name of retention label Files RetentionLabel, Type String Type of retention label. Possible values: Normal, Record or Regulatory Record Files HasDigitalSignature Boolean File has a digital signature Files DigitalSignatureProvider String Name of digital signature provider if digital Signature exist Files IsFileArchived Boolean File is Archived These changes are non-breaking, which means that your existing pipelines will continue to run. We also let this change sit for 21 days, so that delta datasets are also not impacted. Anyone starting fresh with MGDC for SharePoint will automatically get the new columns in their pipelines. If you have existing pipelines and want to use the new columns, you will need to update your MGDC application, get that update approved and then update your pipeline to pull the new schema. For more details about updating your pipelines to include new columns, or to see how these can be leveraged in specific scenarios, see these articles: Helpful blog on schema changes: MGDC for SharePoint FAQ: How to deal with schema changes Main MGDC for SharePoint article: https://aka.ms/SharePointData Updated schema for SharePoint datasets in MGDC: https://aka.ms/SharePointDatasetsMGDC for SharePoint and SharePoint Embedded
1. MGDC for SharePoint and SharePoint Embedded In this post, we’ll discuss how to use data from Microsoft Graph Data Connect (MGDC) for SharePoint to understand how SharePoint Embedded is being configured and used in your tenant. If you’re new to MGDC, get an overview at https://aka.ms/SharePointData. If you’re not familiar with SharePoint Embedded, check this Overview of SharePoint Embedded. 2. Introduction SharePoint Embedded is an API-only solution to use the power of the Microsoft 365 file and document storage platform. You can use it to build applications that expose Microsoft 365 features including those from Office, Purview and Copilot. The SharePoint Embedded files are stored in “File Storage Containers”, created by your enterprise applications or a third-party application. 3. MGDC for SharePoint and SharePoint Embedded One important detail is that MGDC for SharePoint sees these containers and files used by SharePoint Embedded. You can see the Containers (under Sites), the permissions granted and even each individual file inside the Containers. 4. Web Template Id To filter sites for SharePoint Embedded containers, just look for SharePoint Sites in MGDC that have the RootWeb.WebTemplateId of 70. This is used to differentiate this scenario, in the same way that OneDrives are sites with the RootWeb.WebTemplateId of 21. You can also check for the RootWeb.Template, which shows as “CSPCONTAINER” for SharePoint Embedded. A typical container for SharePoint Embedded will show in the SharePoint Sites datasets like this: { "ptenant": "00000001-0000-0000-0000-000000000000", "Id": "00000002-0000-0000-0000-000000000000", "Url": "https://tenantname.sharepoint.com/contentstorage/CSP_00000009-0000-0000-0000-000000000000", "ArchiveState": "None", "RootWeb": { "Configuration": 0, "Id": "00000003-0000-0000-0000-000000000000", "Title": "Ideas", "WebTemplate": "CSPCONTAINER", "WebTemplateId": 70, "LastItemModifiedDate": "2025-01-10T22:55:18.000Z" }, "WebCount": 1, "StorageQuota": 27487790694400, "StorageUsed": 149186, "StorageMetrics": { "MetadataSize": 8506, "TotalFileCount": 2, "TotalFileStreamSize": 41924, "TotalSize": 149186 }, "GroupId": "00000000-0000-0000-0000-000000000000", "GeoLocation": "CAN", "IsInRecycleBin": false, "RecycleBinItemCount": 0, "RecycleBinItemSize": 0, "SecondStageRecycleBinStorageUsage": 0, "IsTeamsConnectedSite": false, "IsTeamsChannelSite": false, "TeamsChannelType": "None", "IsCommunicationSite": false, "IsHubSite": false, "HubSiteId": "00000000-0000-0000-0000-000000000000", "IsOneDrive": false, "BlockAccessFromUnmanagedDevices": false, "BlockDownloadOfAllFilesOnUnmanagedDevices": false, "BlockDownloadOfViewableFilesOnUnmanagedDevices": false, "IsExternalSharingEnabled": true, "ShareByEmailEnabled": true, "ShareByLinkEnabled": true, "SensitivityLabelInfo": {}, "IBMode": "Open", "RelatedGroupId": "00000000-0000-0000-0000-000000000000", "Owner": { "Name": "System Account" }, "SecondaryContact": {}, "ReadLocked": false, "ReadOnly": false, "CreatedTime": "2025-01-09T22:54:50Z", "LastSecurityModifiedDate": "2025-01-09T22:57:11Z", "Operation": "Full", "LastUserAccessDate": "2025-01-09T22:54:51Z", "SnapshotDate": "2025-01-11T00:00:00Z" } Note: In all the sample JSON objects shown in this post, ids were redacted with zeros. You can learn more about how SharePoint uses WebTemplateId to identify the type of site at MGDC for SharePoint FAQ: Is OneDrive included? 5. Permissions The SharePoint Permissions dataset includes all the permissions granted for all the several types of SharePoint content, including OneDrive, Teams-connected sites, and SharePoint Embedded containers. Granular permissions by folder and individual files are also included. Here’s a typical object from the SharePoint Permissions dataset: { "ptenant": "00000001-0000-0000-0000-000000000000", "SiteId": "00000002-0000-0000-0000-000000000000", "WebId": "00000003-0000-0000-0000-000000000000", "ListId": "00000004-0000-0000-0000-000000000000", "ItemType": "File", "ItemURL": "contentstorage/CSP_00000009-0000-0000-0000-000000000000/Document Library/LoopAppData/ProjectBlue.loop", "FileExtension": "loop", "RoleDefinition": "Contribute", "LinkId": "00000005-0000-0000-0000-000000000000", "ScopeId": "00000006-0000-0000-0000-000000000000", "LinkScope": "Organization", "SharedWithCount": [], "SharedWith": [], "Operation": "Full", "SnapshotDate": "2025-01-11T00:00:00Z", "ShareCreatedBy": { "Type": "User", "Name": "Test User", "Email": "test@tenantname.onmicrosoft.com", "UPN": "test@tenantname.onmicrosoft.com" }, "ShareCreatedTime": "2025-01-09T22:55:51Z", "ShareLastModifiedBy": { "Type": "User", "Name": "Test User", "Email": "test@tenantname.onmicrosoft.com", "UPN": "test@tenantname.onmicrosoft.com" }, "ShareLastModifiedTime": "2025-01-09T22:55:51Z", "ListItemId": 4, "UniqueId": "00000007-0000-0000-0000-000000000000" } Note: The example above shows a single Contribute permission to a Loop file using a sharing link. Loop uses SharePoint Embedded to store files and it’s something you can easily configure and test in your tenant. MGDC for SharePoint will show all permissions granted in SharePoint Embedded containers, included permissions granted at various levels of the SharePoint hierarchy (site, web, library, folder, or file) and diverse types of security principals (users, SharePoint groups and Entra ID groups, internal or external). You can learn more about it at MGDC for SharePoint FAQ: What is in the Permissions dataset? 6. Files Finally, you can get a complete list of every file inside the SharePoint Embedded containers, including details like file name, size, author, last modifier and more. Here’s a typical object from the SharePoint Files dataset: { "ptenant": "00000001-0000-0000-0000-000000000000", "SiteId": "00000002-0000-0000-0000-000000000000", "Author": { "Name": "Test User", "Email": "test@tenantname.onmicrosoft.com" }, "DirName": "Document Library/LoopAppData", "Extension": "loop", "FileName": "ProjectBlue.loop", "IsLabelEncrypted": false, "ItemId": "00000008-0000-0000-0000-000000000000", "ListId": "00000004-0000-0000-0000-000000000000", "ListServerTemplate": "DocumentLibrary", "MajorVersion": 4, "MinorVersion": 0, "ModifiedBy": { "Name": "Test User", "Email": "test@tenantname.onmicrosoft.com" }, "Operation": "Full", "ScopeId": "00000006-0000-0000-0000-000000000000", "SensitivityLabelInfo": {}, "SiteUrl": "https://tenantname.sharepoint.com/contentstorage/CSP_00000009-0000-0000-0000-000000000000", "SizeInBytes": 24301, "SizeInBytesWithVersions": 57765, "TimeCreated": "2025-01-09T22:55:08Z", "TimeLastModified": "2025-01-09T22:56:13Z", "WebId": "00000003-0000-0000-0000-000000000000", "WebTemplateId": 70, "SnapshotDate": "2025-01-11T00:00:00Z" } Note: SharePoint Embedded does not support minor versions, so the “MinorVersion” column in the Files dataset will always show zero in this scenario. For details, check the schema for the SharePoint Files dataset. 7. Filtering You can easily download entire datasets and use Azure Data Tools to filter for rows where WebTemplateId is 70. For Sites and Files, there is also a mechanism to filter at the source, so you pull from Microsoft Graph Data Connect only the data related to SharePoint Embedded. The same filtering mechanism can be used to pull data just for a specific site, filtered by Id. For details, check this article about Filtering SharePoint dataset at the source. 8. Conclusion I hope this post helped you understand how to use MGDC for SharePoint to gather information about SharePoint Embedded containers and files. For more information, visit https://aka.ms/SharePointData.Understanding the Notebooks in the Oversharing Template v2 (Microsoft Fabric)
Introduction The Microsoft Graph Data Connect for SharePoint team published two notebooks used with Microsoft Fabric in the Information Oversharing v2 template. This blog explains what each code block inside these notebooks does. The goal was to help you understand what the notebooks do. Note that this document was written with help from Copilot, using simple prompts like “Analyze each section of this Jupyter notebook with PySpark and Scala code. Describe what each section does.” Notebook 1: Read Last Snapshot Dates This first notebook runs right as the pipeline starts. It checks the environment, verifies if the Sites and Permission tables exist in the Lakehouse, checks the last day data was gathered from MGDC and calculates the start and end date to use. It also cleans the staging tables and stores a few commands that are used in later steps. Section 0 – Set the Default Lakehouse for Notebook to Run %%configure { "defaultLakehouse": { "name": { "parameterName": "lakehouseName", "defaultValue": "defaultlakehousename" } } } This section uses the %%configure magic command to set a JSON configuration that defines a parameter (lakehouseName) with the default value "defaultlakehousename". This setting ensures that when the notebook is launched through a pipeline, it dynamically selects the target Lakehouse. Section 1 – Initialize Parameters import java.time.LocalDateTime import java.time.format.DateTimeFormatter import java.time.temporal.ChronoUnit import java.util.UUID import java.text.SimpleDateFormat import java.time.{LocalDate, LocalDateTime, Period} import java.time.format.DateTimeFormatter import java.time.temporal.ChronoUnit import java.util.Calendar import java.sql.Timestamp val runId = "00000000-0000-0000-0000-000000000000" val workspaceId = spark.conf.get("trident.workspace.id") val workspaceName = "LakeHouseTesting" val lakehouseId = spark.conf.get("trident.lakehouse.id") val lakehouseName = "IMAXDefault" val sitesStagingTableName = "Sites_Staging" val sitesFinalTableName = "Sites" val permissionsStagingTableName = "Permissions_Staging" val permissionsFinalTableName = "Permissions" val endTime = "2024-11-15T00:00:00Z" spark.conf.set("spark.sql.caseSensitive", true) This section imports various libraries for date/time handling and initializes key parameters for the ETL process. These include a run identifier (runId), workspace and Lakehouse information (with some values coming from Spark configuration), table names for staging and final datasets, and a fallback endTime. It also enforces case sensitivity in Spark SQL. Section 2 – Checking Required Final Tables Exist or Not val lakehouse = mssparkutils.lakehouse.get(lakehouseName) val lakehouseId = lakehouse.id val workspaceName = notebookutils.runtime.context("currentWorkspaceName") val permissionsStagingLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${permissionsStagingTableName}" val sitesStagingLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${sitesStagingTableName}" val sitesFinalLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${sitesFinalTableName}" val permissionsFinalLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${permissionsFinalTableName}" val tables = spark.catalog.listTables() val siteTableCount = tables.filter(col("name") === lit(sitesFinalTableName) and array_contains(col("namespace"), lakehouseName) ).count() val permissionsTableCount = tables.filter(col("name") === lit(permissionsFinalTableName) and array_contains(col("namespace"), lakehouseName)).count() val siteStagingTableCount = tables.filter(col("name") === lit(sitesStagingTableName) and array_contains(col("namespace"), lakehouseName) ).count() val permissionsStagingTableCount = tables.filter(col("name") === lit(permissionsStagingTableName) and array_contains(col("namespace"), lakehouseName)).count() This section retrieves the Lakehouse object and uses it to construct ABFS paths for both staging and final tables (for Sites and Permissions). It then checks for the existence of these tables by listing them in Spark’s catalog and filtering by name and namespace. Section 3 – Getting Snapshot Dates from Last Successful Extracts import org.apache.spark.sql.functions.{col, _} import org.apache.spark.sql.types._ import org.apache.spark.sql.{DataFrame, Row, SparkSession} import org.apache.spark.storage.StorageLevel val dtCurrentDateFormatt = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S") val dtRequiredtDateFormatt = DateTimeFormatter.ofPattern("yyyy-MM-dd'T'HH:mm:ss'Z'") var siteDataExists: Boolean = false var permissionsDataExists: Boolean = false val siteSnapshotDate = { if (siteTableCount == 1) { val dfSites = spark.sql(s"SELECT MAX(SnapshotDate) AS SnapshotDate FROM ${lakehouseName}.${sitesFinalTableName} ") val rowSites: Row = dfSites.select("SnapshotDate").head(1)(0) if (rowSites.get(0) == null) endTime else { siteDataExists = true println(s"Sites data Exists: ${siteDataExists}") LocalDateTime.parse(rowSites.get(0).toString(), dtCurrentDateFormatt) .format(dtRequiredtDateFormatt) } } else { endTime } } val permissionsSnapshotDate = { if (permissionsTableCount == 1) { val dfPermissions = spark.sql(s"SELECT MAX(SnapshotDate) AS SnapshotDate FROM ${lakehouseName}.${permissionsFinalTableName} ") val rowPermissions: Row = dfPermissions.select("SnapshotDate").head(1)(0) if (rowPermissions.get(0) == null) endTime else { permissionsDataExists = true println(s"Permissions data Exists: ${permissionsDataExists}") LocalDateTime.parse(rowPermissions.get(0).toString(), dtCurrentDateFormatt) .format(dtRequiredtDateFormatt) } } else { endTime } } This section queries the final tables to retrieve the latest SnapshotDate for both Sites and Permissions. It then reformats the date into an ISO-compliant format. If no snapshot date is found, it defaults to the predefined endTime, and two boolean flags (siteDataExists and permissionsDataExists) are toggled accordingly. Section 4 – Generate View Script for Sites val sitesView: String = s""" CREATE OR ALTER VIEW vw${sitesFinalTableName} AS SELECT *, [StorageQuotaFriendly] = (case when StorageQuota < 1048576 then concat(ceiling(StorageQuota / 1024.0), ' KB') when StorageQuota < 1073741824 then concat(ceiling(StorageQuota / 1048576.0), ' MB') when StorageQuota < 1099511627776 then concat(ceiling(StorageQuota / 1073741824.0), ' GB') when StorageQuota < 1125899906842624 then concat(ceiling(StorageQuota / 1099511627776.0), ' TB') else concat(ceiling(StorageQuota / 1125899906842624.0), ' PB') end ), [StorageUsedFriendly] = (case when StorageUsed < 1048576 then concat(ceiling(StorageUsed / 1024.0), ' KB') when StorageUsed < 1073741824 then concat(ceiling(StorageUsed / 1048576.0), ' MB') when StorageUsed < 1099511627776 then concat(ceiling(StorageUsed / 1073741824.0), ' GB') when StorageUsed < 1125899906842624 then concat(ceiling(StorageUsed / 1099511627776.0), ' TB') else concat(ceiling(StorageUsed / 1125899906842624.0), ' PB') end ) FROM ${sitesFinalTableName} """.stripMargin.replaceAll("[\n\r]"," ") println(sitesView) Here a SQL view (vwSites) is dynamically generated for the Sites final table. The view adds two computed columns (StorageQuotaFriendly and StorageUsedFriendly) that convert byte values into more digestible units such as KB, MB, GB, etc. This script will be stored and executed later. Section 5 – Generate View Script for Permissions val permissionsView: String = s""" CREATE OR ALTER VIEW vw${permissionsFinalTableName} AS SELECT *, ShareeDomain = CASE WHEN CHARINDEX('@', SharedWith_Email) > 0 AND CHARINDEX('.', SharedWith_Email) > 0 THEN SUBSTRING(SharedWith_Email,CHARINDEX('@', SharedWith_Email)+1,LEN(SharedWith_Email)) ELSE '' END, ShareeEMail = CASE WHEN CHARINDEX('@', SharedWith_Email) > 0 THEN SharedWith_Email ELSE '' END, PermissionsUniqueKey = CONCAT(SiteId,'_',RoleDefinition,'_',ScopeId,'_',COALESCE(LinkId,'00000000-0000-0000-0000-000000000000')), EEEUPermissionsCount = SUM(CASE WHEN SharedWith_Name LIKE 'Everyone except external users' THEN 1 ELSE NULL END ) OVER( PARTITION BY CONCAT(SiteId,'_',RoleDefinition,'_',ScopeId,'_',COALESCE(LinkId,'00000000-0000-0000-0000-000000000000'),SharedWith_Name) ), ExternalUserCount = SUM(CASE WHEN SharedWith_TypeV2 LIKE 'External' THEN 1 ELSE NULL END ) OVER( PARTITION BY CONCAT(SiteId,'_',RoleDefinition,'_',ScopeId,'_',COALESCE(LinkId,'00000000-0000-0000-0000-000000000000'),SharedWith_Name) ), B2BUserCount = SUM(CASE WHEN SharedWith_TypeV2 LIKE 'B2BUser' THEN 1 ELSE NULL END ) OVER( PARTITION BY CONCAT(SiteId,'_',RoleDefinition,'_',ScopeId,'_',COALESCE(LinkId,'00000000-0000-0000-0000-000000000000'),SharedWith_Name) ) FROM ${permissionsFinalTableName} """.stripMargin.replaceAll("[\n\r]"," ") println(permissionsView) This section builds a SQL view (vwPermissions) for the Permissions final table. It derives additional columns like ShareeDomain, ShareeEMail, and a composite key (PermissionsUniqueKey) while applying window functions to compute counts (e.g., for external or B2B users). This script will also be stored and executed later. Section 6 – Truncate the Staging Tables from Previous Runs if (siteStagingTableCount == 1) { spark.sql(s"DELETE FROM ${lakehouseName}.${sitesStagingTableName} ") println(s"Staging table deleted: ${lakehouseName}.${sitesStagingTableName}") } else { println(s"Staging table ${lakehouseName}.${sitesFinalTableName} not found") } if (permissionsStagingTableCount == 1) { spark.sql(s"DELETE FROM ${lakehouseName}.${permissionsStagingTableName} ") println(s"Staging table deleted: ${lakehouseName}.${permissionsStagingTableName}") } else { println(s"Staging table ${lakehouseName}.${permissionsStagingTableName} not found") } This section checks if the staging tables exist (by count) and, if found, issues a SQL DELETE command to remove existing data so that new data can be loaded. It prints messages indicating the action taken. Section 7 – Return Snapshot Dates Back to Pipeline import mssparkutils.notebook val returnData = s"""{\"LakehouseId\": \"${lakehouseId}\", \"SitesStagingTableName\": \"${sitesStagingTableName}\", \"SitesFinalTableName\": \"${sitesFinalTableName}\", \"SitesSnapshotDate\": \"${siteSnapshotDate}\", \"SitesDataExists\": ${siteDataExists}, \"SitesView\": \"${sitesView}\", \"PermissionsStagingTableName\": \"${permissionsStagingTableName}\", \"PermissionsFinalTableName\": \"${permissionsFinalTableName}\", \"PermissionsSnapshotDate\": \"${permissionsSnapshotDate}\", \"EndSnapshotDate\": \"${endTime}\", \"PermissionsDataExists\": ${permissionsDataExists}, \"PermissionsView\": \"${permissionsView}\"}""" println(returnData) mssparkutils.notebook.exit(returnData) This concluding section aggregates the key metadata—including Lakehouse information, table names, snapshot dates, existence flags, and the generated view scripts—into a JSON string. It then exits the notebook by returning that JSON to the pipeline. Notebook 2: Merge Sites and Permissions to Final Table This notebook runs after the Sites and Permissions data from MGDC has been collected successfully into the staging tables. If this is the first collection, it handles them as full datasets, storing the data directly in the final tables. If this is using MGDC for SharePoint delta datasets, it merges the new, updated or deleted objects from the staging tables into the final tables. Note: The word "Delta" here might refer to Delta Parquet (an efficient data storage format used by tables in a Microsoft Fabric Lakehouse) or to the MGDC for SharePoint Delta datasets (how MGDC can return only the objects that are new, updated or deleted between two dates). It can be a bit confusing, so be aware of the two interpretations of the word. Section 0 – Set the Default Lakehouse for Notebook to Run %%configure { "defaultLakehouse": { "name": { "parameterName": "lakehouseName", "defaultValue": "defaultlakehousename" } } } This section uses the same Lakehouse configuration as in Notebook 1. It sets the default Lakehouse through a parameter (lakehouseName) to support dynamic running of the notebook in different environments. Section 1 – Initialize Parameters import java.time.LocalDateTime import java.time.format.DateTimeFormatter import java.time.temporal.ChronoUnit import java.util.UUID import java.text.SimpleDateFormat import java.time.{LocalDate, LocalDateTime, Period} import java.time.format.DateTimeFormatter import java.time.temporal.ChronoUnit import java.util.Calendar val runId = "00000000-0000-0000-0000-000000000000" val workspaceId = spark.conf.get("trident.workspace.id") val workspaceName = "LakeHouseTesting" val lakehouseId = spark.conf.get("trident.lakehouse.id") val lakehouseName = spark.conf.get("trident.lakehouse.name") val sitesStagingTableName = "Sites_Staging" val sitesFinalTableName = "Sites" val permissionsStagingTableName = "Permissions_Staging" val permissionsFinalTableName = "Permissions" spark.conf.set("spark.sql.caseSensitive", true) This section is like Notebook 1’s Section 1 but here lakehouseName is retrieved from the configuration. It initializes variables needed for merging, such as run IDs, workspace/Lakehouse identifiers, and table names. Section 2 – Read Sites Dataset from Staging Table val lakehouse = mssparkutils.lakehouse.get(lakehouseName) val lakehouseId = lakehouse.id val workspaceName = notebookutils.runtime.context("currentWorkspaceName") println("Started reading Sites dataset") val sitesStagingLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${sitesStagingTableName}" val dfSitesStaging = spark.read.format("delta").load(sitesStagingLocation) println("Completed reading Sites dataset") This section constructs the ABFS path for the Sites staging table and reads the dataset into a DataFrame using the Delta Parquet format. It includes print statements to track progress. Section 3 – Read Permissions Dataset from Staging Table println("Started reading Permissions dataset") val permissionsStagingLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${permissionsStagingTableName}" val dfPermissionsStaging = spark.read.format("delta").load(permissionsStagingLocation) println("Completed reading Permissions dataset") This section performs the analogous operation for the Permissions staging table, loading the dataset into a DataFrame and providing console output for monitoring. Section 4 – Check Final Tables Exist or Not import io.delta.tables.DeltaTable val sitesFinalLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${sitesFinalTableName}" val permissionsFinalLocation = s"abfss://${workspaceName}@onelake.dfs.fabric.microsoft.com/${lakehouseName}.Lakehouse/Tables/${permissionsFinalTableName}" val sitesFinalTableExists = DeltaTable.isDeltaTable(spark, sitesFinalLocation) if (!sitesFinalTableExists) { println("Final Sites table not exists. Creating final Sites table with schema only") dfSitesStaging.filter("1=2").write.format("delta").mode("overwrite").save(sitesFinalLocation) println("Final Sites table created") } else { println("Final Sites table exists already") } val permissionsFinalTableExists = DeltaTable.isDeltaTable(spark, permissionsFinalLocation) if (!permissionsFinalTableExists) { println("Final Permissions table not exists. Creating final Permissions table with schema only") dfPermissionsStaging.filter("1=2").write.format("delta").mode("overwrite").save(permissionsFinalLocation) println("Final Permissions table created") } else { println("Final Permissions table exists already") } This section checks whether the final tables for Sites and Permissions exist. If a table does not exist, it creates an empty table (schema only) from the staging DataFrame by filtering out data (filter("1=2")). Section 5 – Merge Sites Data from Staging Table to Final Table import io.delta.tables._ import org.apache.spark.sql.functions._ import org.apache.spark.sql.expressions.{Window, WindowSpec} import org.apache.spark.sql.functions.{coalesce, lit, sum, col, _} import org.apache.spark.sql.types.{StructField, _} import org.apache.spark.sql.{DataFrame, Row, SparkSession} import org.apache.spark.storage.StorageLevel val deltaTableSource = DeltaTable.forPath(spark, sitesStagingLocation) val deltaTableTarget = DeltaTable.forPath(spark, sitesFinalLocation) import spark.implicits._ val dfSource = deltaTableSource.toDF //Delete records that have Operation as Deleted println("Merging Sites dataset from current staging table") deltaTableTarget .as("target") .merge( dfSource.as("source"), "source.Id = target.Id") .whenMatched("source. Operation = 'Deleted'") .delete() .whenMatched("source.Operation != 'Deleted'") .updateAll() .whenNotMatched("source.Operation != 'Deleted'") .insertAll() .execute() println("Merging of Sites dataset completed") This section performs a Delta Lake merge (upsert) operation on the Sites data. The merge logic deletes matching records when the source’s Operation is 'Deleted', updates other matching records, and inserts new records that are not marked as 'Deleted'. Section 6 – Merge Permissions Data from Staging Table to Final Table import io.delta.tables._ import org.apache.spark.sql.functions._ import org.apache.spark.sql.expressions.{Window, WindowSpec} import org.apache.spark.sql.functions.{coalesce, lit, sum, col, _} import org.apache.spark.sql.types.{StructField, _} import org.apache.spark.sql.{DataFrame, Row, SparkSession} import org.apache.spark.storage.StorageLevel val deltaTablePermissionsSource = DeltaTable.forPath(spark, permissionsStagingLocation) val deltaTablePermissionsTarget = DeltaTable.forPath(spark, permissionsFinalLocation) import spark.implicits._ val dfPermissionsSource = deltaTablePermissionsSource.toDF //Delete records that have Operation as Deleted println("Merging Permissions dataset from current staging table") deltaTablePermissionsTarget .as("target") .merge( dfPermissionsSource.as("source"), """source.SiteId = target.SiteId and source.ScopeId = target.ScopeId and source.LinkId = target.LinkId and source.RoleDefinition = target.RoleDefinition and coalesce(source.SharedWith_Name,"") = coalesce(target.SharedWith_Name,"") and coalesce(source.SharedWith_TypeV2,"") = coalesce(target.SharedWith_TypeV2,"") and coalesce(source.SharedWith_Email,"") = coalesce(target.SharedWith_Email,"") and coalesce(source.SharedWith_AADObjectId,"") = coalesce(target.SharedWith_AADObjectId,"") """) .whenMatched("source. Operation = 'Deleted'") .delete() .whenMatched("source.Operation != 'Deleted'") .updateAll() .whenNotMatched("source.Operation != 'Deleted'") .insertAll() .execute() println("Merging of Permissions dataset completed") This section performs a merge operation on the Permissions data. The merge condition is more complex—comparing multiple columns (including handling nulls with coalesce) to identify matching records. The operation applies deletion for rows marked as 'Deleted', updates others, and inserts records where no match exists. Section 7 – Read and Display Sample TOP 10 Rows var sqlQuery = s"SELECT * FROM ${lakehouseName}.${sitesFinalTableName} order by SnapshotDate DESC LIMIT 10" val dfSitesAll = spark.sql(sqlQuery) display(dfSitesAll) sqlQuery = s"SELECT * FROM ${lakehouseName}.${permissionsFinalTableName} order by SnapshotDate DESC LIMIT 10" val dfPermissionsAll = spark.sql(sqlQuery) display(dfPermissionsAll) This final section executes SQL queries to retrieve and display the top 10 rows from both the Sites and Permissions final tables. The rows are ordered by SnapshotDate in descending order. This is typically used for sample or debugging purposes. Conclusion I hope this article helped you understand the notebooks included in the template. This might help you customize it later. These templates are intended as starting points for your work with many scenarios. Read more about MGDC for SharePoint at https://aka.ms/SharePointData.
