Copy and transform data in Microsoft Fabric Lakehouse using Azure Data Factory or Azure Synapse Analytics

APPLIES TO: Azure Data Factory Azure Synapse Analytics

Microsoft Fabric Lakehouse is a data architecture platform for storing, managing, and analyzing structured and unstructured data in a single location. In order to achieve seamless data access across all compute engines in Microsoft Fabric, go to Lakehouse and Delta Tables to learn more. By default, data is written to Lakehouse Table in V-Order, and you can go to Delta Lake table optimization and V-Order for more information.

This article outlines how to use Copy activity to copy data from and to Microsoft Fabric Lakehouse and use Data Flow to transform data in Microsoft Fabric Lakehouse. To learn more, read the introductory article for Azure Data Factory or Azure Synapse Analytics.

Supported capabilities

This Microsoft Fabric Lakehouse connector is supported for the following capabilities:

① Azure integration runtime ② Self-hosted integration runtime

This connector supports connecting to Microsoft Fabric Lakehouse in the workspace with a private link enabled. You can set up and use a private link in Microsoft Fabric by referring to this article.

To support workspace-level private link in the self-hosted integration runtime (version 5.58.9377.1 or above), you need to add *.dfs.fabric.microsoft.com to the allowlist to ensure Microsoft Fabric Lakehouse connector can access Onelake APIs through the network.

Get started

To perform the copy activity with a pipeline, you can use one of the following tools or SDKs:

• Copy Data tool
• Azure portal
• .NET SDK
• Python SDK
• Azure PowerShell
• REST API
• Azure Resource Manager template

Create a Microsoft Fabric Lakehouse linked service using UI

Use the following steps to create a Microsoft Fabric Lakehouse linked service in the Azure portal UI.

1. Browse to the Manage tab in your Azure Data Factory or Synapse workspace and select Linked Services, then select New:

   • Azure Data Factory
   • Azure Synapse

   

2. Search for Microsoft Fabric Lakehouse and select the connector.

   

3. Configure the service details, test the connection, and create the new linked service.

   

Connector configuration details

The following sections provide details about properties that are used to define Data Factory entities specific to Microsoft Fabric Lakehouse.

Linked service properties

The Microsoft Fabric Lakehouse connector supports the following authentication types. See the corresponding sections for details:

• Service principal authentication
• System-assigned managed identity authentication
• User-assigned managed identity authentication

Service principal authentication

To use service principal authentication, follow these steps.

1. Register an application with the Microsoft Identity platform and add a client secret. Afterwards, make note of these values, which you use to define the linked service:

   • Application (client) ID, which is the service principal ID in the linked service.
   • Client secret value, which is the service principal key in the linked service.
   • Tenant ID

2. Grant the service principal at least the Contributor role in Microsoft Fabric workspace. Follow the steps in Grant permissions in Microsoft Fabric workspace

These properties are supported for the linked service:

Example: using service principal key authentication

You can also store service principal key in Azure Key Vault.

{
    "name": "MicrosoftFabricLakehouseLinkedService",
    "properties": {
        "type": "Lakehouse",
        "typeProperties": {
            "workspaceId": "",
            "artifactId": "",
            "tenant": "",
            "servicePrincipalId": "",
            "servicePrincipalCredentialType": "ServicePrincipalKey",
            "servicePrincipalCredential": {
                "type": "SecureString",
                "value": ""
            }   
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

System-assigned managed identity authentication

A data factory or Synapse pipeline can be associated with a system-assigned managed identity for Azure resources, which represents that resource for authentication to other Azure services. You can directly use this system-assigned managed identity for Microsoft Fabric Lakehouse authentication, which is similar to using your own service principal. It allows this designated resource to access and copy data from or to Microsoft Fabric Lakehouse. To learn more about managed identities for Azure resources, see Managed identities for Azure resources.

To use managed identities for Azure resource authentication, follow these steps:

1. Retrieve system-assigned managed identity information by copying the value of the system-assigned managed identity object ID generated along with your factory or Synapse workspace.

2. Grant the system-assigned managed identity at least the Contributor role in Microsoft Fabric workspace. Follow the steps in Grant permissions in Microsoft Fabric workspace

These properties are supported for a Microsoft Fabric Lakehouse linked service:

Example:

{
    "name": "MicrosoftFabricLakehouseLinkedService",
    "properties": {
        "type": "Lakehouse",
        "typeProperties": {            
            "workspaceId": "",
            "artifactId": "",
            "authenticationType": "SystemAssignedManagedIdentity"
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

User-assigned managed identity authentication

A data factory can be assigned with one or multiple user-assigned managed identities. You can use this user-assigned managed identity for Microsoft Fabric Lakehouse authentication, which allows to access and copy data from or to Microsoft Fabric Lakehouse. To learn more about managed identities for Azure resources, see Managed identities for Azure resources

To use user-assigned managed identity authentication, follow these steps:

1. Create one or multiple user-assigned managed identities.

2. Grant the user-assigned managed identity at least the Contributor role in Microsoft Fabric workspace. Follow the steps in Grant permissions in Microsoft Fabric workspace.

3. Assign one or multiple user-assigned managed identities to your data factory and create credentials for each user-assigned managed identity.

These properties are supported for a Microsoft Fabric Lakehouse linked service:

Example:

{
    "name": "MicrosoftFabricLakehouseLinkedService",
    "properties": {
        "type": "Lakehouse",
        "typeProperties": {            
            "workspaceId": "",
            "artifactId": "",
            "authenticationType": "UserAssignedManagedIdentity",
            "credential": {
                "referenceName": "credential1",
                "type": "CredentialReference"
            }
        },
        "connectVia": {
            "referenceName": "",
            "type": "IntegrationRuntimeReference"
        }
    }
}

Grant permissions in Microsoft Fabric workspace

You need to grant the service principal/system-assigned managed identity/user-assigned managed identity at least the Contributor role in Microsoft Fabric workspace. Follow these steps:

1. Go to your Microsoft Fabric workspace, select Manage access on the top bar. Then select Add people or groups.

   

2. In Add people pane, enter your service principal/system-assigned managed identity/user-assigned managed identity name, and select it from the drop-down list.

3. Specify the role as Contributor or higher (Admin, Member), then select Add.

   

4. Your service principal/system-assigned managed identity/user-assigned managed is displayed on Manage access pane.

Dataset properties

Microsoft Fabric Lakehouse connector supports two types of datasets, which are Microsoft Fabric Lakehouse Files dataset and Microsoft Fabric Lakehouse Table dataset. See the corresponding sections for details.

• Microsoft Fabric Lakehouse Files dataset
• Microsoft Fabric Lakehouse Table dataset

For a full list of sections and properties available for defining datasets, see Datasets.

Microsoft Fabric Lakehouse Files dataset

Microsoft Fabric Lakehouse connector supports the following file formats. Refer to each article for format-based settings.

• Avro format
• Binary format
• Delimited text format
• JSON format
• ORC format
• Parquet format

The following properties are supported under location settings in the format-based Microsoft Fabric Lakehouse Files dataset:

Example:

{
    "name": "DelimitedTextDataset",
    "properties": {
        "type": "DelimitedText",
        "linkedServiceName": {
            "referenceName": "",
            "type": "LinkedServiceReference"
        },
        "typeProperties": {
            "location": {
                "type": "LakehouseLocation",
                "fileName": "",
                "folderPath": ""
            },
            "columnDelimiter": ",",
            "compressionCodec": "gzip",
            "escapeChar": "\\",
            "firstRowAsHeader": true,
            "quoteChar": "\""
        },
        "schema": [ < physical schema, optional, auto retrieved during authoring > ]
    }
}

Microsoft Fabric Lakehouse Table dataset

The following properties are supported for Microsoft Fabric Lakehouse Table dataset:

Example:

{ 
    "name": "LakehouseTableDataset", 
    "properties": {
        "type": "LakehouseTable",
        "linkedServiceName": { 
            "referenceName": "", 
            "type": "LinkedServiceReference" 
        }, 
        "typeProperties": { 
            "schema": "",
            "table": ""   
        },
        "schema": [< physical schema, optional, retrievable during authoring >]
    } 
}

Copy activity properties

The copy activity properties for Microsoft Fabric Lakehouse Files dataset and Microsoft Fabric Lakehouse Table dataset are different. See the corresponding sections for details.

• Microsoft Fabric Lakehouse Files in Copy activity
• Microsoft Fabric Lakehouse Table in Copy activity

For a full list of sections and properties available for defining activities, see Copy activity configurations and Pipelines and activities.

Microsoft Fabric Lakehouse Files in Copy activity

To use Microsoft Fabric Lakehouse Files dataset type as a source or sink in Copy activity, go to the following sections for the detailed configurations.

Microsoft Fabric Lakehouse Files as a source type

Microsoft Fabric Lakehouse connector supports the following file formats. Refer to each article for format-based settings.

• Avro format
• Binary format
• Delimited text format
• JSON format
• ORC format
• Parquet format

You have several options to copy data from Microsoft Fabric Lakehouse using the Microsoft Fabric Lakehouse Files dataset:

• Copy from the given path specified in the dataset.
• Wildcard filter against folder path or file name, see wildcardFolderPath and wildcardFileName.
• Copy the files defined in a given text file as file set, see fileListPath.

The following properties are under storeSettings settings in format-based copy source when using Microsoft Fabric Lakehouse Files dataset:

Example:

"activities": [
    {
        "name": "CopyFromLakehouseFiles",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "
",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "DelimitedTextSource",
                "storeSettings": {
                    "type": "LakehouseReadSettings",
                    "recursive": true,
                    "enablePartitionDiscovery": false
                },
                "formatSettings": {
                    "type": "DelimitedTextReadSettings"
                }
            },
            "sink": {
                "type": ""
            }
        }
    }
]

Microsoft Fabric Lakehouse Files as a sink type

Microsoft Fabric Lakehouse connector supports the following file formats. Refer to each article for format-based settings.

• Avro format
• Binary format
• Delimited text format
• JSON format
• ORC format
• Parquet format

The following properties are under storeSettings settings in format-based copy sink when using Microsoft Fabric Lakehouse Files dataset:

Example:

"activities": [
    {
        "name": "CopyToLakehouseFiles",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": ""
            },
            "sink": {
                "type": "ParquetSink",
                "storeSettings": {
                    "type": "LakehouseWriteSettings",
                    "copyBehavior": "PreserveHierarchy",
                    "metadata": [
                        {
                            "name": "testKey1",
                            "value": "value1"
                        },
                        {
                            "name": "testKey2",
                            "value": "value2"
                        }
                    ]
                },
                "formatSettings": {
                    "type": "ParquetWriteSettings"
                }
            }
        }
    }
]

Folder and file filter examples

This section describes the resulting behavior of the folder path and file name with wildcard filters.

File list examples

This section describes the resulting behavior of using file list path in copy activity source.

Assuming you have the following source folder structure and want to copy the files in bold:

Some recursive and copyBehavior examples

This section describes the resulting behavior of the copy operation for different combinations of recursive and copyBehavior values.

Microsoft Fabric Lakehouse Table in Copy activity

To use Microsoft Fabric Lakehouse Table dataset as a source or sink dataset in Copy activity, go to the following sections for the detailed configurations.

Microsoft Fabric Lakehouse Table as a source type

To copy data from Microsoft Fabric Lakehouse using Microsoft Fabric Lakehouse Table dataset, set the type property in the Copy activity source to LakehouseTableSource. The following properties are supported in the Copy activity source section:

Example:

"activities":[
    {
        "name": "CopyFromLakehouseTable",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "
",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": "LakehouseTableSource",
                "timestampAsOf": "2023-09-23T00:00:00.000Z",
                "versionAsOf": 2
            },
            "sink": {
                "type": ""
            }
        }
    }
]

Microsoft Fabric Lakehouse Table as a sink type

To copy data to Microsoft Fabric Lakehouse using Microsoft Fabric Lakehouse Table dataset, set the type property in the Copy Activity sink to LakehouseTableSink. The following properties are supported in the Copy activity sink section:

Example:

"activities":[
    {
        "name": "CopyToLakehouseTable",
        "type": "Copy",
        "inputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "outputs": [
            {
                "referenceName": "",
                "type": "DatasetReference"
            }
        ],
        "typeProperties": {
            "source": {
                "type": ""
            },
            "sink": {
                "type": "LakehouseTableSink",
                "tableActionOption ": "Append"
            }
        }
    }
]

Data type mapping for Microsoft Fabric Lakehouse table

When copying data from Microsoft Fabric Lakehouse table, the following mappings are used from Microsoft Fabric Lakehouse table data types to interim data types used by the service internally. See Schema and data type mappings to learn about how copy activity maps the source schema and data type to the sink.

When copying data to Microsoft Fabric Lakehouse table, the following mappings are used from interim data types used by the service internally to supported delta sink data types.

Delta Lake table support

In the sections below, you will find detailed information on Delta Lake table support for both the source and sink.

Source

Delta column mapping is supported when you apply reader version 2 or reader version 3 with columnMapping in readerFeatures in your Microsoft Fabric Lakehouse Table.

Delta table's column mapping capability allows for more flexible schema evolution, ensuring that changes in table structure do not disrupt data workflows. With column mapping, you can read data from an existing delta Lake table with delta.columnMapping.mode set to name or id.

Deletion vectors is supported when you apply reader version 3 with deletionVectors in readerFeatures in your Microsoft Fabric Lakehouse Table. Rows that are soft deleted are marked in deletion vector files and skipped when reading the delta lake table.

Change Data Feed is supported.

Sink

Delta column mapping is supported. This capability allows for more flexible schema evolution, ensuring that changes in table structure do not disrupt data workflows. With column mapping, you can:

• Write data to an existing delta lake table with delta.columnMapping.mode set to name.
• Auto-create a table with delta.columnMapping.mode set to name when the sink table does not exist and the source columns include special characters and whitespaces.
• Auto-create a table with delta.columnMapping.mode set to name when the table action is overwrite and the source dataset columns include special characters and whitespaces.

Deletion vectors is supported.

Change Data Feed is supported.

Mapping data flow properties

When transforming data in mapping data flow, you can read and write to files or tables in Microsoft Fabric Lakehouse. See the corresponding sections for details.

• Microsoft Fabric Lakehouse Files in mapping data flow
• Microsoft Fabric Lakehouse Table in mapping data flow

For more information, see the source transformation and sink transformation in mapping data flows.

Microsoft Fabric Lakehouse Files in mapping data flow

To use Microsoft Fabric Lakehouse Files dataset as a source or sink dataset in mapping data flow, go to the following sections for the detailed configurations.

Microsoft Fabric Lakehouse Files as a source or sink type

Microsoft Fabric Lakehouse connector supports the following file formats. Refer to each article for format-based settings.

• Avro format
• Delimited text format
• JSON format
• ORC format
• Parquet format

To use Fabric Lakehouse file-based connector in inline dataset type, you need to choose the right Inline dataset type for your data. You can use DelimitedText, Avro, JSON, ORC, or Parquet depending on your data format.

Microsoft Fabric Lakehouse Table in mapping data flow

To use Microsoft Fabric Lakehouse Table dataset as a source or sink dataset in mapping data flow, go to the following sections for the detailed configurations.

Microsoft Fabric Lakehouse Table as a source type

There are no configurable properties under source options.

Microsoft Fabric Lakehouse Table as a sink type

The following properties are supported in the Mapping Data Flows sink section:

Example: Microsoft Fabric Lakehouse Table sink

sink(allowSchemaDrift: true, 
    validateSchema: false, 
    input( 
        CustomerID as string,
        NameStyle as string, 
        Title as string, 
        FirstName as string, 
        MiddleName as string,
        LastName as string, 
        Suffix as string, 
        CompanyName as string,
        SalesPerson as string, 
        EmailAddress as string, 
        Phone as string, 
        PasswordHash as string, 
        PasswordSalt as string, 
        rowguid as string, 
        ModifiedDate as string 
    ), 
    deletable:false, 
    insertable:true, 
    updateable:false, 
    upsertable:false, 
    optimizedWrite: true, 
    mergeSchema: true, 
    autoCompact: true, 
    skipDuplicateMapInputs: true, 
    skipDuplicateMapOutputs: true) ~> CustomerTable

For Fabric Lakehouse table-based connector in inline dataset type, you only need to use Delta as dataset type. This will allow you to read and write data from Fabric Lakehouse tables.

The following table depicts the behavior of Mapping Data Flows when interacting with Fabric Lakehouse tables, based on whether the Lakehouse is schema-enabled or schema-less, and whether a schema is explicitly defined.

Note: All scenarios are supported when selecting the table name using the dropdown.

Lookup activity properties

To learn details about the properties, check Lookup activity.

GetMetadata activity properties

To learn details about the properties, check GetMetadata activity

Delete activity properties

To learn details about the properties, check Delete activity

Related content

For a list of data stores supported as sources and sinks by the copy activity, see Supported data stores.