← Writing

Automating Incremental Loads from SQL Server to Microsoft Fabric Using DLT + Shortcut API

· 5 min read
data-engineering microsoft-fabric dlt sql-server delta-lake

In this post, I’ll show you how to build a fully automated, config-driven pipeline that ingests tables from an on-prem SQL Server instance (or Docker container) into Microsoft Fabric OneLake, leveraging:

  • DLT (Data Load Tool) for extraction and Delta file generation
  • Delta format for efficient incremental loads
  • Microsoft Fabric REST API for programmatic shortcut creation
  • No Fabric capacity used — data lands in /Files and becomes queryable via /Tables

The beauty? It refreshes with a single script, and avoids manual setup of tables.

Tools and Architecture

  • Source: SQL Server (on-prem or Docker)
  • Destination: Microsoft Fabric OneLake
  • Ingestion Tool: dlt (Data Load Tool)
  • Shortcut Interface: Microsoft Fabric REST API
  • File Format: Delta Lake (Parquet + transaction log)
  • Package Manager: uv

Why This Architecture?

Fabric shortcuts let you expose data in /Files as queryable /Tables without copying or moving it. Combined with DLT’s filesystem destination and Delta Lake support, you get:

  • Incremental loads that only process changed rows
  • Delta transaction logs that Fabric can read natively
  • Shortcut registration that only needs to happen once per table

What This Pipeline Does

  1. Ingests 3 tables (Products, Customers, Orders) from SQL Server using dlt
  2. Writes Delta format files to OneLake/Files/mssql_tables_dataset/...
  3. Automatically creates shortcuts via Fabric REST API at OneLake/Tables/...
  4. Incremental loads based on a last_updated column
  5. Avoids re-registering tables on every run

Core Pipeline Code

The dlt pipeline uses the sql_database source with write_disposition="merge" and dlt.sources.incremental to track the last_updated column:

import dlt
from dlt.sources.sql_database import sql_database

TABLES = ["Products", "Customers", "Orders"]

@dlt.source
def mssql_source(
    connection_string: str = dlt.secrets.value,
):
    return [
        sql_database(
            connection_string,
            schema="dbo",
            table_names=[t],
        ).with_resources(t)
        for t in TABLES
    ]

def build_pipeline():
    return dlt.pipeline(
        pipeline_name="mssql_to_onelake",
        destination="filesystem",
        dataset_name="mssql_tables_dataset",
    )

if __name__ == "__main__":
    pipeline = build_pipeline()
    source = mssql_source()

    for resource in source.resources.values():
        resource.apply_hints(
            write_disposition="merge",
            incremental=dlt.sources.incremental("last_updated"),
        )

    load_info = pipeline.run(source)
    print(load_info)

This handles incremental syncs using the last_updated column. The result is stored in Delta format under /Files.

Creating Shortcuts Automatically

After the Delta files are generated, we use the Microsoft Fabric REST API to create table shortcuts dynamically. The function checks whether a shortcut already exists before attempting to create it:

import requests

def get_access_token(tenant_id: str, client_id: str, client_secret: str) -> str:
    url = f"https://login.microsoftonline.com/{tenant_id}/oauth2/v2.0/token"
    resp = requests.post(url, data={
        "grant_type": "client_credentials",
        "client_id": client_id,
        "client_secret": client_secret,
        "scope": "https://api.fabric.microsoft.com/.default",
    })
    resp.raise_for_status()
    return resp.json()["access_token"]

def shortcut_exists(
    token: str,
    workspace_id: str,
    lakehouse_id: str,
    table_name: str,
) -> bool:
    url = (
        f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}"
        f"/items/{lakehouse_id}/shortcuts/Tables/{table_name}"
    )
    resp = requests.get(url, headers={"Authorization": f"Bearer {token}"})
    return resp.status_code == 200

def create_shortcut(
    token: str,
    workspace_id: str,
    lakehouse_id: str,
    onelake_path: str,
    table_name: str,
) -> None:
    if shortcut_exists(token, workspace_id, lakehouse_id, table_name):
        print(f"Shortcut already exists for {table_name}, skipping.")
        return

    url = (
        f"https://api.fabric.microsoft.com/v1/workspaces/{workspace_id}"
        f"/items/{lakehouse_id}/shortcuts"
    )
    payload = {
        "path": "Tables",
        "name": table_name,
        "target": {
            "type": "OneLake",
            "oneLake": {
                "workspaceId": workspace_id,
                "itemId": lakehouse_id,
                "path": f"Files/{onelake_path}/{table_name}",
            },
        },
    }
    resp = requests.post(
        url,
        json=payload,
        headers={"Authorization": f"Bearer {token}"},
    )
    resp.raise_for_status()
    print(f"Shortcut created for {table_name}")

You only need to run this once per table — subsequent pipeline runs just update the Delta log, and the shortcut automatically reflects the latest data.

Environment Configuration

OneLake supports the same APIs as Azure Data Lake Storage (ADLS) Gen2, so dlt’s filesystem destination can target it directly. Configure your secrets.toml:

[sources.sql_database]
connection_string = "mssql+pyodbc://user:pass@host/dbname?driver=ODBC+Driver+17+for+SQL+Server"

[destination.filesystem]
bucket_url = "abfss://<workspace-name>@onelake.dfs.fabric.microsoft.com/<lakehouse-name>.Lakehouse/Files"

[destination.filesystem.credentials]
azure_client_id = "..."
azure_client_secret = "..."
azure_tenant_id = "..."

[fabric]
workspace_id = "..."
lakehouse_id = "..."
tenant_id = "..."
client_id = "..."
client_secret = "..."

Incremental Loading in Action

When you insert new rows or update the last_updated column in your SQL Server tables, all you need to do is rerun the pipeline script.

Behind the scenes:

  • dlt uses the last_updated column (configured via dlt.sources.incremental) to detect only new or changed rows
  • These changes are written as new Parquet files, and the Delta Lake transaction log (_delta_log) is updated to reflect the new state
  • Since Fabric shortcuts point directly to the /Files/mssql_tables_dataset/{table} folders, they automatically reflect the latest data — without any need to refresh or reload

There’s no need to manually re-register tables, no data duplication, and no reprocessing of unchanged rows. The shortcut acts as a real-time lens into your latest data.

Caveats and Gotchas

Admin Consent Required: The Fabric REST API requires Power BI service-level permissions such as Tenant.Read.All and Tenant.ReadWrite.All. These must be granted via admin consent in Entra ID — worth looping in your tenant admin early.

One Table, One Folder: Each Delta table must be stored in a dedicated folder within OneLake. Avoid merging multiple tables into a single folder to ensure proper shortcut creation and queryability.

Stable Schemas Are Critical: Delta tables must remain schema-consistent over time. If your source schema changes, implement schema evolution strategies to prevent load failures or shortcut inconsistencies.

Final Thoughts

This setup helped me:

  • Reduce costs — dlt writes Delta files directly to OneLake (storage cost only, no compute)
  • Simplify ingestion from legacy SQL Server systems
  • Avoid manual table creation or refresh in Fabric
  • Automate shortcut management via API
  • Keep capacity usage low — shortcut creation consumes no Fabric Compute Units

If you’re working with SQL Server and want a lightweight path into Fabric without standing up Spark clusters or Fabric Data Factory pipelines, this setup is worth exploring.