TLDR

This guide demonstrates how to connect Microsoft Fabric Warehouse to Mage Pro for automated data pipelines. The integration requires creating an Azure service principal for authentication, granting it access to your Fabric workspace, and configuring Mage with the connection credentials. Once set up, you can build data pipelines that pull data from external APIs (like weather services) and automatically load it into your Fabric warehouse.

Table of contents

• Introduction

• Understanding the integration

• Azure authentication and Fabric workspace setup

• Configure Mage Pro for Fabric integration

• Creating Fabric-connected data pipelines in Mage Pro

• Conclusion

Introduction

Modern data teams need efficient ways to connect cloud data warehouses with pipeline orchestration tools. This guide demonstrates how to integrate Microsoft Fabric Warehouse with Mage Pro, creating automated data workflows that can pull from both warehouse sources and external APIs.

Understanding the integration

Microsoft Fabric provides a unified analytics platform that combines data warehousing capabilities with other data services. Mage Pro serves as a data pipeline tool that simplifies ETL operations through a visual interface and code-based blocks.

The integration between these platforms rely on Azure Active Directory (Microsoft Entra ID) service principals for authentication. This approach provides secure, programmatic access without requiring individual user credentials in your data pipelines.

Azure authentication and Fabric workspace setup

Step 1: Create the service principal: Create a new app registration in Microsoft Entra ID through Azure Portal, giving it a descriptive name and setting it to "Accounts in this organizational directory only" for proper tenant access control.

Step 2: Copy the Azure Client ID: Obtain the Application (client) ID from the app registration overview page for your authentication configuration.

Step 3: Create client secret: Generate a client secret with appropriate expiration period to serve as your authentication credentials. Make sure to copy the client secret, you will need to add it as a secret to your Mage io_config.yml file.

Step 4: Grant Fabric workspace access: Click "Add people or groups" in your Fabric workspace access management, search for your service principal using its name or application id, and add it to the workspace.

Step 5: Assign workspace permissions: Grant either "Admin" or "Contributor" permissions to enable your service principal to connect to warehouse resources and execute queries through the Mage integration.

For more detailed setup instruction see the Azure documentation for creating an Azure service principal and granting Fabric workspace access.

Configure Mage Pro for Fabric integration

Complete the integration by configuring Mage with your Azure authentication credentials and updating the io_config.yml file with your Fabric connection details. This two-step configuration enables secure communication between Mage and your Fabric warehouse.

Step 1: Create a data pipeline: Create a new data pipeline in Mage by navigating to the pipelines page, and clicking “New pipeline.” Next select “Start from Scratch” and then choose “Batch data pipeline.” Give the data pipeline a name and then click “Create new pipeline.” After creating the new pipeline you will be navigated to the data pipeline editor page where you can begin your integration with Microsoft Fabric.

Step 2: Enter your secrets: Add your Azure service principal credentials and Fabric warehouse connection details to your Mage environment's secret management system for secure storage. From the pipeline editor page, hover over the right popout navigation menu and select “Secrets.” Add your key value pair in the text box and then click enter.

Step 3: Complete the Mage integration with Fabric: by defining the warehouse connection parameters MICROSOFT_FABRIC_WAREHOUSE_NAME, MICROSOFT_FABRIC_WAREHOUSE_ENDPOINT, and MICROSOFT_FABRIC_WAREHOUSE_SCHEMA in your io_config.yml file. Use Mage's secret interpolation to reference your stored credentials securely. Your configuration should follow the format shown below.

version: 0.1.1
default:
  AZURE_CLIENT_ID: "{{ mage_secret_var('AZURE_CLIENT_ID') }}"
  AZURE_CLIENT_SECRET: "{{ mage_secret_var('AZURE_CLIENT_SECRET') }}"
  MICROSOFT_FABRIC_WAREHOUSE_NAME: 'mage-demo'
  MICROSOFT_FABRIC_WAREHOUSE_ENDPOINT: "{{ mage_secret_var('MICROSOFT_FABRIC_WAREHOUSE_ENDPOINT') }}"
  MICROSOFT_FABRIC_WAREHOUSE_SCHEMA: 'dbo'

Creating Fabric-connected data pipelines in Mage Pro

Once your authentication and workspace configuration is complete, building data pipelines in Mage involves adding data loader, transformer, and exporter blocks to handle the flow from external sources to your Fabric warehouse.

Step 1: Add a data loader block: Click the "Add block" button in your pipeline editor and select "Data loader." Choose "Generic (no template)" to create a custom loader for external APIs. This block will pull real-time data from sources like weather APIs, replacing static datasets with dynamic, up-to-date information. Copy the code below into your newly created data loader block, click the “Run” button and then look for a data return in the output of the block.

import pandas as pd
import requests
from datetime import datetime

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

@data_loader
def load_data_from_api(*args, **kwargs):
    """
    Load CURRENT temperature data for Ashburn, VA that updates hourly
    Uses Open-Meteo API - no API key required
    """
    # Ashburn, VA coordinates (zip code 20148)
    lat = 39.0437
    lon = -77.4875
    
    # Open-Meteo API for current weather conditions
    url = f'<https://api.open-meteo.com/v1/forecast?latitude={lat}&longitude={lon}&current_weather=true&hourly=temperature_2m,relative_humidity_2m,apparent_temperature,wind_speed_10m,wind_direction_10m,precipitation&timezone=America/New_York&temperature_unit=fahrenheit&wind_speed_unit=mph&precipitation_unit=inch>'
    
    response = requests.get(url)
    weather_data = response.json()
    
    # Extract current weather data (actual current conditions)
    current = weather_data['current_weather']
    hourly = weather_data['hourly']
    
    # Get current timestamp for exact timing
    current_time = datetime.now()
    
    # Find current hour in hourly data (index 0 is current hour)
    current_hour_data = {
        'humidity': hourly['relative_humidity_2m'][0],
        'feels_like': hourly['apparent_temperature'][0],
        'precipitation': hourly['precipitation'][0]
    }
    
    # Weather code descriptions
    weather_descriptions = {
        0: "Clear sky", 1: "Mainly clear", 2: "Partly cloudy", 3: "Overcast",
        45: "Fog", 48: "Depositing rime fog", 51: "Light drizzle", 53: "Moderate drizzle",
        55: "Dense drizzle", 61: "Slight rain", 63: "Moderate rain", 65: "Heavy rain",
        71: "Slight snow", 73: "Moderate snow", 75: "Heavy snow", 80: "Slight rain showers",
        81: "Moderate rain showers", 82: "Violent rain showers", 95: "Thunderstorm",
        96: "Thunderstorm with slight hail", 99: "Thunderstorm with heavy hail"
    }
    
    weather_desc = weather_descriptions.get(current['weathercode'], f"Code {current['weathercode']}")
    
    # Create structured data with current conditions
    data = {
        'timestamp': [current_time.isoformat()],
        'measurement_date': [current_time.strftime('%Y-%m-%d')],
        'measurement_time': [current_time.strftime('%H:%M:%S')],
        'measurement_hour': [current_time.hour],
        'city': ['Ashburn'],
        'state': ['VA'],
        'zip_code': ['20148'],
        'latitude': [lat],
        'longitude': [lon],
        
        # Current conditions that change hourly
        'current_temp_f': [current['temperature']],
        'feels_like_f': [current_hour_data['feels_like']],
        'humidity_percent': [current_hour_data['humidity']],
        'wind_speed_mph': [current['windspeed']],
        'wind_direction_deg': [current['winddirection']],
        'precipitation_inch': [current_hour_data['precipitation']],
        'weather_code': [current['weathercode']],
        'weather_description': [weather_desc],
        
        # Metadata
        'api_timestamp': [current['time']],
        'data_source': ['Open-Meteo Current Weather']
    }
    
    # Create DataFrame
    df = pd.DataFrame(data)
    
    print(f"Weather data loaded: {data['current_temp_f'][0]}°F in {data['city'][0]}, {data['state'][0]}")
    
    return df

Step 2: Add a data exporter block: Create a data exporter by selecting "Data exporter" from the Blocks menu and choosing "Microsoft Fabric Warehouse" from the Data warehouse selection. This block handles writing your processed data to the Fabric warehouse using the authentication credentials you configured earlier.

Step 3: Configure the export settings: Specify your target schema (typically 'dbo'), table name, and export behavior in the exporter block. Set the if_exists parameter to 'append' for incremental data loading or 'replace' for full refreshes. The index=False setting prevents pandas index columns from being included in your warehouse table.

The data flows seamlessly from external APIs through Mage's transformation capabilities into your Fabric warehouse, creating an automated pipeline that can run on scheduled intervals. This pattern scales to multiple data sources and supports complex transformation logic between the loader and exporter blocks.

Conclusion

This integration provides a foundation for building sophisticated data workflows that combine Microsoft Fabric's enterprise warehousing capabilities with Mage's flexible pipeline orchestration. The service principal authentication model ensures secure, scalable access while maintaining proper access controls.

The combination enables teams to build automated data pipelines that pull from multiple sources, transform data according to business requirements, and deliver insights through various output channels. With proper setup and monitoring, this integration supports reliable, production-scale data operations.