...

...

...

1. Axis — Load into Lakehouse Files

1.1 OneLake Shortcut (SharePoint / OneDrive)

Description
Logical link exposing SharePoint folders in OneLake without data duplication.

Functioning

• Shortcut points to a SharePoint folder (folder-level only)
• Data remains in SharePoint and is accessed virtually
• Accessible across Fabric workloads

Key capabilities

• Data virtualization (no physical copy)
• Automatic synchronization with source changes
• Unified access through OneLake

Advantages

• No pipelines or ETL required
• No data duplication
• Fast implementation
• Unified access layer

Limitations (decision drivers)

• Folder-level granularity only
• Performance dependent on SharePoint (latency, throttling)
• No control over ingestion (no filtering, no incremental logic)
• Runtime dependency on source availability
• Not suitable when strong data isolation or historization is required

1.2 Custom ingestion — API (Notebook or Pipeline) → Files

Description
Extraction via Microsoft Graph or SharePoint REST API and storage in Lakehouse Files.

Execution models

• Notebook (Spark / Python)
• Data Pipeline:
• Web Activity (REST calls)
• Copy Activity with API source

Functioning

• API calls to retrieve files or metadata
• Data written into OneLake Files

Key capabilities

• Supports full SharePoint surface (files, folders, metadata)
• Custom ingestion logic (filtering, incremental, structuring)
• Can be orchestrated via pipelines

Advantages

• Full flexibility on ingestion logic
• Ability to implement incremental loads (delta, watermark) at bronze layer
• Can handle complex folder structures and edge cases
• Works even when no native connector exists

Limitations (decision drivers)

• Requires handling:
• authentication (OAuth / Service Principal)
• pagination (@odata.nextLink)
• API rate limits / throttling
• More complex error handling and retry logic
• Development and maintenance effort
• Pipeline Web Activity is stateless (no built-in transformation)
• Copy Activity / Web Activity require manual schema handling

...

2. Axis — Load into Lakehouse Tables

2.1 Shortcut with transformation → Delta Tables

Description
Use of SharePoint shortcut with transformation to project files into Delta tables.

Functioning

• Shortcut exposes files
• Transformation step converts them into structured tables
• Tables remain synchronized with source

Key capabilities

• Automatic file-to-table conversion
• Continuous synchronization
• Direct consumption in SQL / BI

Advantages

• No pipeline required
• Direct analytical usability
• Integrated with OneLake

Limitations (decision drivers)

• Strong dependency on source file structure and quality
• Limited transformation capabilities compared to ETL
• Limited control over schema evolution
• Debugging and lineage less explicit than pipeline-based ingestion

2.2 Mirroring (SharePoint Lists)

Description
Replication of SharePoint Lists into OneLake as Delta tables.

Functioning

• Connection to SharePoint list
• Continuous replication into Fabric tables
• Automatic synchronization

Key capabilities

• Near real-time data replication
• Native Delta format
• No ETL required

Advantages

• Continuous synchronization
• Simplified ingestion architecture
• Direct usability for analytics

Limitations (decision drivers)

• Limited to structured data (lists only)
• Limited transformation capabilities during ingestion
• Dependency on mirroring feature availability and scope
• Limited control over ingestion logic (filters, enrichment)
• Schema evolution handled automatically but with limited customization

2.3 Custom ingestion — API (Notebook or Pipeline) → Tables

Description
API-based extraction with transformation and direct load into Delta tables.

Same comments from Section 1.2 Custom ingestion — API (Notebook or Pipeline) → Files

...

3. Considerations

API usage (Notebook vs Pipeline)

Notebook

• Better suited for:
• complex transformations
• large data processing
• advanced logic (joins, enrichment)

Pipeline (Web / Copy Activity)

• Better suited for:
• orchestration
• simple ingestion patterns
• metadata-driven ingestion

Security

• Authentication methods:
• Organizational account
• Workspace identity

Service principal recommended

• API-based approaches require:
• token management
• permission configuration (e.g. Sites.Read.All)

...

4. MATRIX(s)

Synthesis

Criteria

...

4. Technical solutions (recommended - SharePoint Shortcuts)

• P1 : SharePoint Shortcuts, ​
  • Triggers on OneLake Events ? -> Trigger Events not working for shortcuts. 
• Directly to Silver Tables Lakehouse with auto transform in delta (see Référence) -> newly working for .xlsx to delta table 
• or to files zone Lakehouse (csv shortcut) then transformation to silver tables
• Prerequisites : folder hierarchy for files & Service Principal (or Workspace Identity). One shortcut = one folder
• See also Limitations  : https://learn.microsoft.com/en-us/fabric/onelake/create-onedrive-sharepoint-shortcut#limitations

Limitations

The following limitations apply to SharePoint shortcuts:

• OneLake doesn't support shortcuts to personal or OnPremise SharePoint sites. Shortcuts can only connect to enterprise SharePoint sites and OneDrive for Business.

• Based on Azure ACS retirement, Service Principal authentication will not work for SharePoint tenants created after Nov 1st, 2024.

• SharePoint and OneDrive Shortcuts are supported only at folder level and not at file levev

...

5. Security & Authentication Guide

SharePoint ↔ Microsoft Fabric (Shortcuts)

...

1. Authentication Overview

SharePoint / OneDrive shortcuts in Fabric support three authentication methods:

• Organizational account
• Workspace Identity
• Service Principal

This guide focuses on the two recommended enterprise patterns:

• Workspace identity : To use workspace identity authentication for OneDrive or SharePoint shortcuts, you need to grant your workspace identity access to the OneDrive or SharePoint site. 

• Service Principal :To use service principal authentication, register an application in Microsoft Entra ID and create a client secret. Then, grant the service principal access to your SharePoint site using Microsoft Graph. The service principal needs at least read permission on the SharePoint site

...

2. Summary — Workspace Identity vs Service Principal

...

3. Workspace Identity

3.1 Description

A Workspace Identity is a Fabric-managed service principal automatically created and maintained by the platform.

It allows Fabric to authenticate to external systems (including SharePoint) without managing credentials.

...

3.2 Configuration Steps (Shortcut context)

1. Create a Workspace Identity in Fabric
   • Workspace settings → Workspace Identity
   • Requires admin role
2. Retrieve the identity in Entra ID
   • Same name as workspace
   • Copy Application ID
3. Grant access to SharePoint site
   • Add the identity to the site permissions
   • Minimum: read access
4. Create the Shortcut
   • Select Workspace Identity as authentication method

...

3.3 Best Practices

• Use Workspace Identity when supported by the connector
• Restrict access to specific SharePoint sites (least privilege)
• Limit who can manage the workspace identity (admin role only)
• Monitor identity usage via Entra audit logs

...

3.4 Limitations (important for design)

• Not supported in all connectors or scenarios
• Not compatible with cross-tenant access
• Lifecycle tied to workspace (deletion = identity loss)
• Limited governance outside Fabric
• Must be excluded from certain Conditional Access policies to function properly

...

4. Service Principal

4.1 Description

A Service Principal is a non-interactive identity registered in Microsoft Entra ID, used for application-to-application authentication.

It provides full control over permissions and lifecycle, making it suitable for enterprise scenarios.

...

4.2 Configuration Steps (Shortcut context)

1. Create an App Registration in Entra ID
   • Generate:
     • Client ID
     • Client Secret or Certificate
2. Assign API permissions
   • SharePoint / Graph permissions
   • Prefer Sites.Selected
3. Grant access to SharePoint site
   • Explicitly authorize the Service Principal
   • Required in addition to API permissions
4. Configure authentication in Fabric
   • Select Service Principal in Shortcut
   • Provide credentials

...

4.3 Best Practices

• Use Sites.Selected instead of tenant-wide permissions
• Prefer certificate-based authentication over client secrets
• Store credentials in Azure Key Vault
• Rotate secrets regularly
• Use dedicated Service Principals per environment (Dev / Prod)

...

4.4 Limitations (important for design)

• Requires credential lifecycle management
• Higher setup complexity
• Risk of misconfiguration (permissions or secrets)
• Requires dual configuration:
  • Entra ID permissions
  • SharePoint site-level authorization
• SharePoint has its own authorization layer → API permission alone is not sufficient

...

5. Key Design Considerations

5.1 Authentication vs Authorization

• Authentication = identity (Workspace Identity / Service Principal)
• Authorization = access granted in SharePoint

Both must be configured correctly.

...

5.2 Choosing between Workspace Identity and Service Principal

Key decision drivers:

• Need for centralized governance → Service Principal
• Need for low operational overhead → Workspace Identity
• Need for cross-platform reuse → Service Principal
• Need for simplified setup → Workspace Identity