Microsoft Fabric Warehouse

​

Overview

• Connect to Microsoft Fabric Data Warehouse using Azure AD Service Principal authentication (with optional token-based auth via AZURE_TENANT_ID)
• Run SQL in pipelines via SQL blocks (transformer) or Python blocks
• Export data using batched row-based inserts (standard export)

​

Add credentials

1. Create a new pipeline or open an existing pipeline.
2. Expand the left side of your screen to view the file browser.
3. Scroll down and click on a file named io_config.yaml.
4. Enter the following keys and values under the key named default (you can have multiple profiles, add it under whichever is relevant to you)

version: 0.1.1
default:
  AZURE_CLIENT_ID: '12345678-1234-1234-1234-123456789012'
  AZURE_CLIENT_SECRET: 'your-azure-ad-service-principal-secret'
  MICROSOFT_FABRIC_WAREHOUSE_NAME: 'your_database_name'
  MICROSOFT_FABRIC_WAREHOUSE_ENDPOINT: 'your-endpoint-here'
  MICROSOFT_FABRIC_WAREHOUSE_SCHEMA: 'dbo'

  # Optional: use token-based auth (recommended for Fabric). Must be a valid GUID.
  # AZURE_TENANT_ID: '87654321-4321-4321-4321-210987654321'

​

Configuration Parameters

​

Service Principal Setup

1. Create a service principal in Microsoft Entra ID
2. Enable service principal access in Microsoft Fabric
3. Assign appropriate permissions to the service principal

• Register a Microsoft Entra app and create a service principal
• Service principal in Fabric Data Warehouse

​

Using SQL blocks

1. Add a Transformer block and set its language to SQL.
2. In the block’s connection settings, set Connection to Microsoft Fabric and Profile to your io_config.yaml profile (e.g. default).
3. Optionally set Schema (default dbo) and Use raw SQL to run statements as-is.
4. Write your SQL. Reference upstream block output with {{ df_1 }}, {{ df_2 }}, etc. The block uses the profile’s credentials and schema.

1. Materialized output (no “Use raw SQL”) When “Use raw SQL” is off, the block runs your query and exports the result to a table (creates or appends) by executing the query string in the warehouse. The warehouse runs the SQL directly.
2. Upstream data referenced in SQL ({{ df_1 }}, {{ df_2 }}, …) When your SQL references {{ df_1 }} (or {{ df_2 }}, etc.), the upstream block’s output must be available in the warehouse. If not already there, Mage uploads that DataFrame using batched inserts (see Export limits).

​

Using Python block

1. Create a new pipeline or open an existing pipeline.
2. Add a data loader, transformer, or data exporter block (the code snippet below is for a data loader).
3. Select Generic (no template).
4. Enter this code snippet (note: change the config_profile from default if you have a different profile):

from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.microsoft_fabric_warehouse import MicrosoftFabricWarehouse
from os import path

if 'data_loader' not in globals():
    from mage_ai.data_preparation.decorators import data_loader


@data_loader
def load_data_from_fabric_warehouse(*args, **kwargs):
    """
    Template for loading data from Microsoft Fabric Warehouse.
    Specify your configuration settings in 'io_config.yaml'.
    Set the following in your io_config:

    Docs: https://docs.mage.ai/integrations/databases/MicrosoftFabricWarehouse
    """
    query = 'SELECT * FROM your_table LIMIT 1000'  # Specify your SQL query here
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'

    with MicrosoftFabricWarehouse.with_config(ConfigFileLoader(config_path, config_profile)) as warehouse:
        return warehouse.load(query)

5. Run the block.

​

Export a dataframe

from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.microsoft_fabric_warehouse import MicrosoftFabricWarehouse
from pandas import DataFrame
from os import path

if 'data_exporter' not in globals():
    from mage_ai.data_preparation.decorators import data_exporter


@data_exporter
def export_data_to_fabric_warehouse(df: DataFrame, **kwargs) -> None:
    """
    Export to Microsoft Fabric Warehouse.
    Set credentials (and optional AZURE_TENANT_ID for token auth) in io_config.yaml.
    """
    schema_name = 'dbo'
    table_name = 'your_table_name'
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'

    with MicrosoftFabricWarehouse.with_config(ConfigFileLoader(config_path, config_profile)) as warehouse:
        warehouse.export(
            df,
            schema_name,
            table_name,
            if_exists='replace',
            index=False,
        )

​

Standard export

​

Export limits

• Column count: SQL Server limits the number of parameters per statement (2,000). If your DataFrame has more than 2,000 columns, export raises a clear error. Reduce the number of columns or use a different upload method (e.g. export a subset of columns, or stage data elsewhere and load via another path).

​

Custom types

from mage_ai.settings.repo import get_repo_path
from mage_ai.io.config import ConfigFileLoader
from mage_ai.io.microsoft_fabric_warehouse import MicrosoftFabricWarehouse
from pandas import DataFrame
from os import path

if 'data_exporter' not in globals():
    from mage_ai.data_preparation.decorators import data_exporter


@data_exporter
def export_data_to_fabric_warehouse(df: DataFrame, **kwargs) -> None:
    """
    Template for exporting data to Microsoft Fabric Warehouse.
    Specify your configuration settings in 'io_config.yaml'.
    Set the following in your io_config:

    Docs: https://docs.mage.ai/integrations/databases/MicrosoftFabricWarehouse
    """
    schema_name = 'dbo'  # Specify the name of the schema to export data to
    table_name = 'your_table_name'  # Specify the name of the table to export data to
    config_path = path.join(get_repo_path(), 'io_config.yaml')
    config_profile = 'default'
    overwrite_types = {'column_name': 'VARCHAR(255)'}

    with MicrosoftFabricWarehouse.with_config(ConfigFileLoader(config_path, config_profile)) as warehouse:
        warehouse.export(
            df,
            schema_name,
            table_name,
            if_exists='replace',  # Specify resolution policy if table name already exists
            overwrite_types=overwrite_types,
            index=False,  # Specifies whether to include index in exported table
        )

​

Troubleshooting errors

​

Connection Timeout Errors

• Ensure AZURE_CLIENT_ID is a valid GUID format
• Verify AZURE_CLIENT_SECRET is correct and not expired
• Check that the Service Principal has appropriate permissions on your Fabric workspace
• Ensure the Service Principal is not disabled in Azure AD
• Verify network connectivity to the endpoint
• Check if corporate firewall or proxy is blocking the connection
• Try increasing connection timeout settings

​

TCP Provider Errors

• Ensure MICROSOFT_FABRIC_WAREHOUSE_ENDPOINT is correct and accessible
• Check network connectivity to the endpoint (ping, telnet, etc.)
• Verify the endpoint is accessible from your current location
• Ensure the endpoint is not blocked by firewall rules
• Check if Azure region is experiencing connectivity issues
• Verify the Microsoft Fabric service is operational
• Try connecting from a different network to isolate the issue

​

Database Object Errors

• Ensure MICROSOFT_FABRIC_WAREHOUSE_NAME matches exactly (case-sensitive)
• Check that the Service Principal has access to the database
• Verify the database exists in your Fabric workspace
• Ensure the database is not paused or offline
• Verify the table exists in the specified schema
• Check if you’re using the correct schema name (default is ‘dbo’)
• Ensure table names are properly quoted if they contain special characters

​

Authentication Errors

• Ensure AZURE_CLIENT_ID is a valid GUID format
• Verify AZURE_CLIENT_SECRET is correct and not expired
• Check that the Service Principal has appropriate permissions on your Fabric workspace
• Ensure the Service Principal is not disabled in Azure AD
• Verify the Service Principal has the correct role assignments
• Check if the Service Principal’s permissions have been revoked

​

Export column limit

• Export a subset of columns (e.g. drop or select only the columns you need)
• Split the DataFrame into multiple tables
• Use a different load path if you have very wide tables (e.g. stage to storage and load via another tool)

​

Schema Errors

• Ensure MICROSOFT_FABRIC_WAREHOUSE_SCHEMA is correct
• Check that the Service Principal has access to the schema
• Verify the schema exists in your database
• Use ‘dbo’ as the default schema if unsure
• Check if the schema name is case-sensitive
• Verify the Service Principal has CREATE/ALTER permissions on the schema

​

Best Practices

1. Service Principal Management:
   • Use dedicated Service Principals for different environments
   • Rotate secrets regularly
   • Grant minimum required permissions
2. Connection Management:
   • Always use context managers (with statements) when possible
   • Close connections explicitly when not using context managers
   • Test connections before running large operations
3. Performance Optimization:
   • Export uses batched inserts; keep tables under the export column limit (2,000 columns). For very wide DataFrames, reduce columns or use another load path.
   • Use appropriate if_exists policies (‘replace’, ‘append’, ‘fail’)
   • Consider using overwrite_types for optimal column types
   • Use LIMIT clauses in queries when testing
4. Security:
   • Never hardcode credentials in your code
   • Use io_config.yaml for all configuration
   • Ensure io_config.yaml is not committed to version control
   • Use environment variables for sensitive values in production

​

Related Documentation

• Microsoft Fabric Documentation
• Microsoft Fabric Warehouse Documentation
• Azure AD Service Principal Setup
• ODBC Driver Installation