Fabric Onelake 2025

Microsoft Fabric Integration with Azure Data Factory (2025)

Overview

Microsoft Fabric is a unified SaaS analytics platform combining Power BI, Azure Synapse Analytics, and Azure Data Factory capabilities. ADF provides native connectors for Fabric Lakehouse and Fabric Warehouse, enabling seamless data movement between ADF and Fabric workspaces.

Microsoft Fabric Lakehouse Connector

The Fabric Lakehouse connector enables read and write operations to Microsoft Fabric Lakehouse for tables and files.

Supported Activities

Quick Reference

• Linked service type: Lakehouse
• Authentication: Managed Identity (preferred) or Service Principal
• Required IDs: workspaceId and artifactId
• Sink types: LakehouseTableSink (tables), LakehouseFileSink (files)
• Source type: LakehouseTableSource
• Table action options: append or overwrite

Finding Workspace and Artifact IDs: 1. Navigate to Fabric workspace in browser 2. Copy workspace ID from URL: https://app.powerbi.com/groups/<workspaceId>/... 3. Open Lakehouse settings to find artifact ID 4. Or use Fabric REST API to enumerate workspace items

For complete linked service, dataset, and copy activity JSON examples, see references/lakehouse-examples.md.

Microsoft Fabric Warehouse Connector

The Fabric Warehouse connector provides T-SQL based data warehousing capabilities within the Fabric ecosystem.

Supported Activities

Quick Reference

• Linked service type: Warehouse
• Authentication: System-Assigned Managed Identity (recommended), User-Assigned MI, or Service Principal
• Required properties: endpoint, warehouse
• Sink type: WarehouseSink
• Write behaviors: insert or upsert
• Table option: autoCreate (creates table if missing)

For complete linked service, copy activity, stored procedure, and script activity JSON examples, see references/warehouse-examples.md.

OneLake Integration Patterns

ADF supports three integration patterns with OneLake:

OneLake Shortcuts are the preferred approach when data already exists in ADLS Gen2 -- they provide instant zero-copy access without data movement. Use ADF Copy Activity only when data transformation or format conversion is needed.

For complete pipeline JSON examples for all three patterns, see references/onelake-patterns.md.

Permission Configuration

Azure Data Factory Managed Identity Permissions in Fabric

For Fabric Lakehouse: 1. Open Fabric workspace 2. Go to Workspace settings -> Manage access 3. Add ADF managed identity with Contributor role 4. Or assign Workspace Admin for full access

For Fabric Warehouse: 1. Navigate to Warehouse SQL endpoint 2. Execute SQL to create user:

CREATE USER [your-adf-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [your-adf-name];
ALTER ROLE db_datawriter ADD MEMBER [your-adf-name];

Service Principal Permissions

App Registration Setup: 1. Register app in Microsoft Entra ID 2. Create client secret (store in Key Vault) 3. Add app to Fabric workspace with Contributor role 4. For Warehouse, create SQL user as shown above

Best Practices (2025)

1. Use Managed Identity -- System-assigned for single ADF, user-assigned for multiple. Avoid service principal keys when possible. Store any secrets in Key Vault.

2. Enable Staging for Large Loads -- Use staging with compression for data volumes > 1 GB, complex transformations, or Fabric Warehouse loads.

3. Leverage OneLake Shortcuts -- Use ADLS Gen2 -> OneLake Shortcut -> Direct Access instead of ADLS Gen2 -> Copy Activity -> Lakehouse. No data movement, instant availability, reduced costs.

4. Monitor Fabric Capacity Units (CU) -- Track CU consumption per pipeline run, peak usage, and throttling. Optimize with incremental loads, off-peak scheduling, and right-sized parallelism.

5. Use Table Option AutoCreate -- Set tableOption: "autoCreate" on WarehouseSink for automatic schema management and faster development.

6. Implement Error Handling -- Configure retry policies on Copy activities and add WebActivity-based failure logging with dependencyConditions: ["Failed"].

Common Issues and Solutions

Resources

• Fabric Lakehouse Connector
• Fabric Warehouse Connector
• OneLake Documentation
• Fabric Capacity Management
• ADF to Fabric Integration Guide

Progressive Disclosure References

• Lakehouse Examples: references/lakehouse-examples.md - Complete linked service, dataset, copy activity, and lookup JSON examples
• Warehouse Examples: references/warehouse-examples.md - Complete linked service, copy activity, stored procedure, and script activity JSON examples
• OneLake Patterns: references/onelake-patterns.md - Pipeline patterns for shortcuts, incremental loads, and cross-platform Invoke Pipeline

Fabric Lakehouse JSON Examples

Linked Service Configuration

Using Service Principal Authentication (Recommended):

{
  "name": "FabricLakehouseLinkedService",
  "type": "Microsoft.DataFactory/factories/linkedservices",
  "properties": {
    "type": "Lakehouse",
    "typeProperties": {
      "workspaceId": "12345678-1234-1234-1234-123456789abc",
      "artifactId": "87654321-4321-4321-4321-cba987654321",
      "servicePrincipalId": "<app-registration-client-id>",
      "servicePrincipalKey": {
        "type": "AzureKeyVaultSecret",
        "store": {
          "referenceName": "AzureKeyVault",
          "type": "LinkedServiceReference"
        },
        "secretName": "fabric-service-principal-key"
      },
      "tenant": "<tenant-id>"
    }
  }
}

Using Managed Identity Authentication (Preferred 2025):

{
  "name": "FabricLakehouseLinkedService_ManagedIdentity",
  "type": "Microsoft.DataFactory/factories/linkedservices",
  "properties": {
    "type": "Lakehouse",
    "typeProperties": {
      "workspaceId": "12345678-1234-1234-1234-123456789abc",
      "artifactId": "87654321-4321-4321-4321-cba987654321"
      // Managed identity used automatically - no credentials needed!
    }
  }
}

Dataset Configuration

For Lakehouse Files:

{
  "name": "FabricLakehouseFiles",
  "properties": {
    "type": "LakehouseTable",
    "linkedServiceName": {
      "referenceName": "FabricLakehouseLinkedService",
      "type": "LinkedServiceReference"
    },
    "typeProperties": {
      "table": "Files/raw/sales/2025"
    }
  }
}

For Lakehouse Tables:

{
  "name": "FabricLakehouseTables",
  "properties": {
    "type": "LakehouseTable",
    "linkedServiceName": {
      "referenceName": "FabricLakehouseLinkedService",
      "type": "LinkedServiceReference"
    },
    "typeProperties": {
      "table": "SalesData"  // Table name in Lakehouse
    }
  }
}

Copy Activity Examples

Copy from Azure SQL to Fabric Lakehouse:

{
  "name": "CopyToFabricLakehouse",
  "type": "Copy",
  "inputs": [
    {
      "referenceName": "AzureSqlSource",
      "type": "DatasetReference"
    }
  ],
  "outputs": [
    {
      "referenceName": "FabricLakehouseTables",
      "type": "DatasetReference",
      "parameters": {
        "tableName": "DimCustomer"
      }
    }
  ],
  "typeProperties": {
    "source": {
      "type": "AzureSqlSource",
      "sqlReaderQuery": "SELECT * FROM dbo.Customers WHERE ModifiedDate > '@{pipeline().parameters.LastRunTime}'"
    },
    "sink": {
      "type": "LakehouseTableSink",
      "tableActionOption": "append"  // or "overwrite"
    },
    "enableStaging": false,
    "translator": {
      "type": "TabularTranslator",
      "mappings": [
        {
          "source": { "name": "CustomerID" },
          "sink": { "name": "customer_id", "type": "Int32" }
        },
        {
          "source": { "name": "CustomerName" },
          "sink": { "name": "customer_name", "type": "String" }
        }
      ]
    }
  }
}

Copy Parquet Files to Fabric Lakehouse:

{
  "name": "CopyParquetToLakehouse",
  "type": "Copy",
  "inputs": [
    {
      "referenceName": "AzureBlobParquetFiles",
      "type": "DatasetReference"
    }
  ],
  "outputs": [
    {
      "referenceName": "FabricLakehouseFiles",
      "type": "DatasetReference"
    }
  ],
  "typeProperties": {
    "source": {
      "type": "ParquetSource",
      "storeSettings": {
        "type": "AzureBlobStorageReadSettings",
        "recursive": true,
        "wildcardFolderPath": "raw/sales/2025",
        "wildcardFileName": "*.parquet"
      }
    },
    "sink": {
      "type": "LakehouseFileSink",
      "storeSettings": {
        "type": "LakehouseWriteSettings",
        "copyBehavior": "PreserveHierarchy"
      }
    }
  }
}

Lookup Activity Example

{
  "name": "LookupFabricLakehouseTable",
  "type": "Lookup",
  "typeProperties": {
    "source": {
      "type": "LakehouseTableSource",
      "query": "SELECT MAX(LastUpdated) as MaxDate FROM SalesData"
    },
    "dataset": {
      "referenceName": "FabricLakehouseTables",
      "type": "DatasetReference"
    }
  }
}

OneLake Integration Patterns

Pattern 1: Azure Data Lake Gen2 to OneLake via Shortcuts

Concept: Use OneLake shortcuts instead of copying data

OneLake shortcuts allow you to reference data in Azure Data Lake Gen2 without physically copying it:

1. In Fabric Lakehouse, create shortcut to ADLS Gen2 container 2. Data appears in OneLake immediately (zero-copy) 3. Use ADF to orchestrate transformations on shortcut data 4. Write results back to OneLake

Benefits:

• Zero data duplication
• Real-time data access
• Reduced storage costs
• Single source of truth

ADF Pipeline Pattern:

{
  "name": "PL_Process_Shortcut_Data",
  "activities": [
    {
      "name": "TransformShortcutData",
      "type": "ExecuteDataFlow",
      "typeProperties": {
        "dataFlow": {
          "referenceName": "DF_Transform",
          "type": "DataFlowReference"
        },
        "compute": {
          "coreCount": 8,
          "computeType": "General"
        }
      }
    },
    {
      "name": "WriteToCuratedZone",
      "type": "Copy",
      "typeProperties": {
        "source": {
          "type": "ParquetSource"
        },
        "sink": {
          "type": "LakehouseTableSink",
          "tableActionOption": "overwrite"
        }
      }
    }
  ]
}

Pattern 2: Incremental Load to Fabric Lakehouse

{
  "name": "PL_Incremental_Load_To_Fabric",
  "activities": [
    {
      "name": "GetLastWatermark",
      "type": "Lookup",
      "typeProperties": {
        "source": {
          "type": "LakehouseTableSource",
          "query": "SELECT MAX(LoadTimestamp) as LastLoad FROM ControlTable"
        }
      }
    },
    {
      "name": "CopyIncrementalData",
      "type": "Copy",
      "dependsOn": [
        {
          "activity": "GetLastWatermark",
          "dependencyConditions": ["Succeeded"]
        }
      ],
      "typeProperties": {
        "source": {
          "type": "AzureSqlSource",
          "sqlReaderQuery": "SELECT * FROM dbo.Orders WHERE ModifiedDate > '@{activity('GetLastWatermark').output.firstRow.LastLoad}'"
        },
        "sink": {
          "type": "LakehouseTableSink",
          "tableActionOption": "append"
        }
      }
    },
    {
      "name": "UpdateWatermark",
      "type": "Script",
      "dependsOn": [
        {
          "activity": "CopyIncrementalData",
          "dependencyConditions": ["Succeeded"]
        }
      ],
      "linkedServiceName": {
        "referenceName": "FabricLakehouseLinkedService",
        "type": "LinkedServiceReference"
      },
      "typeProperties": {
        "scripts": [
          {
            "type": "Query",
            "text": "INSERT INTO ControlTable VALUES ('@{utcnow()}')"
          }
        ]
      }
    }
  ]
}

Pattern 3: Cross-Platform Pipeline with Invoke Pipeline

Invoke Pipeline Activity for Cross-Platform Calls

{
  "name": "PL_ADF_Orchestrates_Fabric_Pipeline",
  "activities": [
    {
      "name": "PrepareDataInADF",
      "type": "Copy",
      "typeProperties": {
        "source": {
          "type": "AzureSqlSource"
        },
        "sink": {
          "type": "LakehouseTableSink"
        }
      }
    },
    {
      "name": "InvokeFabricPipeline",
      "type": "InvokePipeline",
      "dependsOn": [
        {
          "activity": "PrepareDataInADF",
          "dependencyConditions": ["Succeeded"]
        }
      ],
      "typeProperties": {
        "workspaceId": "12345678-1234-1234-1234-123456789abc",
        "pipelineId": "87654321-4321-4321-4321-cba987654321",
        "waitOnCompletion": true,
        "parameters": {
          "processDate": "@pipeline().parameters.RunDate",
          "environment": "production"
        }
      }
    }
  ]
}

Fabric Warehouse JSON Examples

Linked Service Configuration

Using Service Principal:

{
  "name": "FabricWarehouseLinkedService",
  "type": "Microsoft.DataFactory/factories/linkedservices",
  "properties": {
    "type": "Warehouse",
    "typeProperties": {
      "endpoint": "myworkspace.datawarehouse.fabric.microsoft.com",
      "warehouse": "MyWarehouse",
      "authenticationType": "ServicePrincipal",
      "servicePrincipalId": "<app-registration-id>",
      "servicePrincipalKey": {
        "type": "AzureKeyVaultSecret",
        "store": {
          "referenceName": "AzureKeyVault",
          "type": "LinkedServiceReference"
        },
        "secretName": "fabric-warehouse-sp-key"
      },
      "tenant": "<tenant-id>"
    }
  }
}

Using System-Assigned Managed Identity (Recommended):

{
  "name": "FabricWarehouseLinkedService_SystemMI",
  "type": "Microsoft.DataFactory/factories/linkedservices",
  "properties": {
    "type": "Warehouse",
    "typeProperties": {
      "endpoint": "myworkspace.datawarehouse.fabric.microsoft.com",
      "warehouse": "MyWarehouse",
      "authenticationType": "SystemAssignedManagedIdentity"
    }
  }
}

Using User-Assigned Managed Identity:

{
  "name": "FabricWarehouseLinkedService_UserMI",
  "type": "Microsoft.DataFactory/factories/linkedservices",
  "properties": {
    "type": "Warehouse",
    "typeProperties": {
      "endpoint": "myworkspace.datawarehouse.fabric.microsoft.com",
      "warehouse": "MyWarehouse",
      "authenticationType": "UserAssignedManagedIdentity",
      "credential": {
        "referenceName": "UserAssignedManagedIdentityCredential",
        "type": "CredentialReference"
      }
    }
  }
}

Copy Activity to Fabric Warehouse

Bulk Insert Pattern:

{
  "name": "CopyToFabricWarehouse",
  "type": "Copy",
  "inputs": [
    {
      "referenceName": "AzureSqlSource",
      "type": "DatasetReference"
    }
  ],
  "outputs": [
    {
      "referenceName": "FabricWarehouseSink",
      "type": "DatasetReference"
    }
  ],
  "typeProperties": {
    "source": {
      "type": "AzureSqlSource",
      "sqlReaderQuery": "SELECT * FROM dbo.FactSales WHERE OrderDate >= '@{pipeline().parameters.StartDate}'"
    },
    "sink": {
      "type": "WarehouseSink",
      "preCopyScript": "TRUNCATE TABLE staging.FactSales",
      "writeBehavior": "insert",
      "writeBatchSize": 10000,
      "tableOption": "autoCreate",  // Auto-create table if doesn't exist
      "disableMetricsCollection": false
    },
    "enableStaging": true,
    "stagingSettings": {
      "linkedServiceName": {
        "referenceName": "AzureBlobStorage",
        "type": "LinkedServiceReference"
      },
      "path": "staging/fabric-warehouse",
      "enableCompression": true
    },
    "parallelCopies": 4,
    "dataIntegrationUnits": 8
  }
}

Upsert Pattern:

{
  "sink": {
    "type": "WarehouseSink",
    "writeBehavior": "upsert",
    "upsertSettings": {
      "useTempDB": true,
      "keys": ["customer_id"],
      "interimSchemaName": "staging"
    },
    "writeBatchSize": 10000
  }
}

Stored Procedure Activity

{
  "name": "ExecuteFabricWarehouseStoredProcedure",
  "type": "SqlServerStoredProcedure",
  "linkedServiceName": {
    "referenceName": "FabricWarehouseLinkedService",
    "type": "LinkedServiceReference"
  },
  "typeProperties": {
    "storedProcedureName": "dbo.usp_ProcessSalesData",
    "storedProcedureParameters": {
      "StartDate": {
        "value": "@pipeline().parameters.StartDate",
        "type": "DateTime"
      },
      "EndDate": {
        "value": "@pipeline().parameters.EndDate",
        "type": "DateTime"
      }
    }
  }
}

Script Activity

{
  "name": "ExecuteFabricWarehouseScript",
  "type": "Script",
  "linkedServiceName": {
    "referenceName": "FabricWarehouseLinkedService",
    "type": "LinkedServiceReference"
  },
  "typeProperties": {
    "scripts": [
      {
        "type": "Query",
        "text": "DELETE FROM staging.FactSales WHERE LoadDate < DATEADD(day, -30, GETDATE())"
      },
      {
        "type": "Query",
        "text": "UPDATE dbo.FactSales SET ProcessedFlag = 1 WHERE OrderDate = '@{pipeline().parameters.ProcessDate}'"
      }
    ],
    "scriptBlockExecutionTimeout": "02:00:00"
  }
}