🔗 OneLake Shortcuts - Multi-Cloud Data Federation¶

# Notebook: Join Dynamics 365 customer data with external analytics

# Dataverse shortcut provides customer records
customers = spark.read.format("delta").load(
    "abfss://bronze@onelake.dfs.fabric.microsoft.com/lh_bronze.Lakehouse/Tables/dynamics_customers"
)

# S3 shortcut provides external behavioral data
behavior = spark.read.format("delta").load(
    "abfss://bronze@onelake.dfs.fabric.microsoft.com/lh_bronze.Lakehouse/Tables/s3_customer_behavior"
)

# Join without any data copying
enriched = customers.join(behavior, "customer_id", "left")
enriched.write.format("delta").mode("overwrite").save(
    "abfss://silver@onelake.dfs.fabric.microsoft.com/lh_silver.Lakehouse/Tables/enriched_customers"
)

# Force metadata refresh programmatically
from notebookutils import mssparkutils

mssparkutils.lakehouse.refreshShortcut(
    lakehouse="lh_bronze",
    shortcut_name="s3_iot_data"
)

def estimate_shortcut_cost(
    read_gb_per_day: float,
    source: str,
    days: int = 30
) -> dict:
    """Estimate monthly cost of shortcut vs copy."""
    egress_rates = {
        "s3": 0.09,      # USD per GB
        "gcs": 0.12,
        "adls_cross_region": 0.02,
        "adls_same_region": 0.0,
        "onelake": 0.0,
    }

    storage_rate = 0.023  # OneLake per GB/month (if copied)

    total_gb = read_gb_per_day * days
    egress_cost = total_gb * egress_rates.get(source, 0)
    copy_storage_cost = (read_gb_per_day * 1.5) * storage_rate  # Assume 1.5x for Delta overhead

    return {
        "shortcut_monthly_egress": round(egress_cost, 2),
        "copy_monthly_storage": round(copy_storage_cost, 2),
        "recommendation": "shortcut" if egress_cost < copy_storage_cost else "copy",
        "total_gb_read": total_gb
    }

# Example: 5 GB/day from S3
print(estimate_shortcut_cost(5, "s3"))
# {'shortcut_monthly_egress': 13.5, 'copy_monthly_storage': 0.17, 'recommendation': 'copy'}

# Example: 0.5 GB/day from S3
print(estimate_shortcut_cost(0.5, "s3"))
# {'shortcut_monthly_egress': 1.35, 'copy_monthly_storage': 0.02, 'recommendation': 'copy'}

# Example: 5 GB/day from ADLS same region
print(estimate_shortcut_cost(5, "adls_same_region"))
# {'shortcut_monthly_egress': 0, 'copy_monthly_storage': 0.17, 'recommendation': 'shortcut'}

# RLS is transparent to shortcuts
# If a Direct Lake model has RLS on zone_id,
# queries to shortcut tables are filtered the same way

# 1. Always filter on partition columns first
df = spark.read.format("delta").load(shortcut_path) \
    .filter(F.col("date") == "2026-04-27")  # Partition pruning

# 2. Select only needed columns
df = df.select("machine_id", "bet_amount", "win_amount")  # Column pruning

# 3. For cross-cloud joins, broadcast the smaller table
from pyspark.sql.functions import broadcast
local_lookup = spark.read.format("delta").load(local_path)
result = df.join(broadcast(local_lookup), "lookup_key")

# 4. Cache shortcut data if reading multiple times
shortcut_df = spark.read.format("delta").load(shortcut_path).cache()
analysis_1 = shortcut_df.groupBy("zone").agg(F.sum("amount"))
analysis_2 = shortcut_df.groupBy("player").agg(F.avg("amount"))
shortcut_df.unpersist()

import requests

base_url = "https://api.fabric.microsoft.com/v1"
workspace_id = "your-workspace-id"
lakehouse_id = "your-lakehouse-id"
token = "your-bearer-token"

headers = {
    "Authorization": f"Bearer {token}",
    "Content-Type": "application/json"
}

# Create an S3 shortcut
payload = {
    "name": "s3_iot_sensor_data",
    "path": "Tables",
    "target": {
        "amazonS3": {
            "location": "https://my-iot-bucket.s3.us-east-1.amazonaws.com",
            "subpath": "/sensor-data/2026/",
            "connectionId": "s3-connection-id"
        }
    }
}

response = requests.post(
    f"{base_url}/workspaces/{workspace_id}/lakehouses/{lakehouse_id}/shortcuts",
    headers=headers,
    json=payload
)
print(response.status_code, response.json())

payload = {
    "name": "adls_reference_data",
    "path": "Tables",
    "target": {
        "adlsGen2": {
            "location": "https://refdata.dfs.core.windows.net",
            "subpath": "/reference/casino-zones/",
            "connectionId": "adls-connection-id"
        }
    }
}

payload = {
    "name": "gcs_weather_observations",
    "path": "Tables",
    "target": {
        "googleCloudStorage": {
            "location": "https://storage.googleapis.com/noaa-weather-public",
            "subpath": "/observations/2026/",
            "connectionId": "gcs-connection-id"
        }
    }
}

payload = {
    "name": "dynamics_customers",
    "path": "Tables",
    "target": {
        "dataverse": {
            "environmentDomain": "org12345.crm.dynamics.com",
            "tableName": "account"
        }
    }
}

response = requests.get(
    f"{base_url}/workspaces/{workspace_id}/lakehouses/{lakehouse_id}/shortcuts",
    headers=headers
)

for shortcut in response.json()["value"]:
    print(f"  {shortcut['name']} → {shortcut['target']}")

response = requests.delete(
    f"{base_url}/workspaces/{workspace_id}/lakehouses/{lakehouse_id}/shortcuts/{shortcut_name}",
    headers=headers
)

# Shortcuts for the casino POC:

# 1. Cross-workspace reference data
#    Source: Central reference workspace → Casino workspace
shortcuts = [
    {
        "name": "ref_casino_zones",
        "source": "onelake://ref-workspace/ref-lakehouse/Tables/casino_zones"
    },
    {
        "name": "ref_game_types",
        "source": "onelake://ref-workspace/ref-lakehouse/Tables/game_type_mappings"
    },
]

# 2. Reading shortcut data in notebooks
zones = spark.read.format("delta").load(
    "abfss://bronze@onelake.dfs.fabric.microsoft.com/lh_bronze.Lakehouse/Tables/ref_casino_zones"
)
# Works identically to native tables

# NOAA weather data from GCS (public dataset)
noaa_shortcut = {
    "name": "noaa_ghcn_daily",
    "target": {
        "googleCloudStorage": {
            "location": "https://storage.googleapis.com/gcp-public-data-noaa",
            "subpath": "/ghcn-d/",
            "connectionId": "gcs-noaa-public"
        }
    }
}

# USDA data from S3 (USDA open data)
usda_shortcut = {
    "name": "usda_crop_data",
    "target": {
        "amazonS3": {
            "location": "https://usda-open-data.s3.amazonaws.com",
            "subpath": "/nass/crop-production/",
            "connectionId": "s3-usda-public"
        }
    }
}

# EPA AQS data from ADLS (Azure Open Datasets)
epa_shortcut = {
    "name": "epa_aqs_daily",
    "target": {
        "adlsGen2": {
            "location": "https://azureopendatastorage.dfs.core.windows.net",
            "subpath": "/epaaqsdaily/",
            "connectionId": "adls-azure-open"
        }
    }
}

# In a federal bronze notebook, shortcut paths are transparent:
noaa_df = spark.read.format("parquet").load(
    "abfss://bronze@onelake.dfs.fabric.microsoft.com/lh_bronze.Lakehouse/Tables/noaa_ghcn_daily"
)

# Filter and process as if data were local
east_coast = noaa_df.filter(
    (F.col("longitude") > -85) & (F.col("longitude") < -65)
)

print(f"East Coast weather observations: {east_coast.count()}")