Home > Best Practices > Performance & Parallelism

🚀 Performance & Parallelism Best Practices¶

Last Updated: 2026-04-15 | Version: 2.0 Status: ✅ Final | Maintainer: Documentation Team

📖 Overview¶

Performance optimization in Microsoft Fabric spans multiple workloads: Copy Activities, Spark notebooks, pipeline orchestration, Direct Lake semantic models, and KQL queries. This guide provides concrete configuration recommendations, benchmark guidelines, and cost-performance trade-off analysis for the casino gaming, federal agency, and healthcare workloads in this project.

🧭 Table of Contents¶

• 📦 Copy Activity Optimization
• ✨ Spark Notebook Performance
• 🔄 Pipeline Parallelism
• ⚡ Direct Lake Performance
• 🔍 KQL Query Performance
• 📈 Benchmark Guidelines by Data Volume
• 💰 Cost vs Performance Trade-Offs

📦 Copy Activity Optimization¶

Data Integration Units (DIU)¶

DIUs (formerly Data Movement Units) determine the compute power allocated to a Copy Activity. Higher DIU counts increase throughput for large data moves but also increase CU consumption.

Default behavior:

When to increase DIUs beyond default:

Configuration:

{
    "type": "Copy",
    "typeProperties": {
        "source": { "type": "DelimitedTextSource" },
        "sink": { "type": "LakehouseTableSink" },
        "dataIntegrationUnits": 32,
        "enableStaging": true
    }
}

Monitoring DIU effectiveness:

After a Copy Activity run, check the usedDataIntegrationUnits and throughput in the run output. If usedDataIntegrationUnits < dataIntegrationUnits, the source cannot saturate the allocated DIUs and you should reduce them.

Parallel Copy Settings¶

The parallelCopies setting controls how many threads read from the source simultaneously within a single Copy Activity.

Default: Auto (typically 4-5 for cloud sources)

When to increase beyond default:

{
    "typeProperties": {
        "parallelCopies": 16,
        "source": {
            "type": "SqlSource",
            "partitionOption": "DynamicRange",
            "partitionSettings": {
                "partitionColumnName": "transaction_date",
                "partitionUpperBound": "2026-03-12",
                "partitionLowerBound": "2026-01-01"
            }
        }
    }
}

Source Partitioning Strategies¶

Partitioning the source read is the single most impactful optimization for large table extraction.

Physical partitioning:

Use when the source table already has partitions (e.g., Oracle RANGE partitions, SQL Server partition schemes).

{
    "partitionOption": "PhysicalPartitionsOfTable"
}

Dynamic range partitioning:

Use when the source has a good distribution column (date, ID) but is not physically partitioned.

{
    "partitionOption": "DynamicRange",
    "partitionSettings": {
        "partitionColumnName": "player_id",
        "partitionUpperBound": "10000000",
        "partitionLowerBound": "1"
    }
}

Decision matrix:

flowchart TD
    A[Source Table] --> B{Has Physical Partitions?}
    B -->|Yes| C[Use PhysicalPartitionsOfTable]
    B -->|No| D{Has Good Distribution Column?}
    D -->|Yes| E{Column Type?}
    D -->|No| F[No Partitioning - Consider Gateway Scale-Up]

    E -->|Integer/BigInt| G[DynamicRange on Integer Column]
    E -->|Date/DateTime| H[DynamicRange on Date Column]
    E -->|String| I[Avoid - Poor Distribution]

    G --> J[Set Lower/Upper Bounds]
    H --> J
    J --> K[parallelCopies = Partition Count]

Staging with Compression¶

For large cross-network transfers, enable staging through an intermediate blob storage with compression.

{
    "typeProperties": {
        "enableStaging": true,
        "stagingSettings": {
            "linkedServiceName": {
                "referenceName": "ls_staging_blob",
                "type": "LinkedServiceReference"
            },
            "path": "staging/fabric-copy",
            "enableCompression": true
        }
    }
}

When to use staging:

Network Optimization¶

✨ Spark Notebook Performance¶

Partition Strategy¶

The number of partitions determines parallelism in Spark. Too few partitions underutilize the cluster; too many create overhead from task scheduling and small file problems.

Rule of thumb: partition count = 2-4x total cores

Setting partition count:

# Read with explicit partition count
df = spark.read.format("delta").load("abfss://bronze@onelake.dfs.fabric.microsoft.com/slot_telemetry/")

# Check current partition count
print(f"Current partitions: {df.rdd.getNumPartitions()}")

# Repartition for processing (use coalesce to reduce, repartition to increase)
df = df.repartition(128)  # Shuffle-based, use for increasing partitions
df = df.coalesce(64)       # No shuffle, use for reducing partitions

# Repartition by key column for join optimization
df = df.repartition(128, "player_id")

When to repartition:

flowchart TD
    A[Read Data] --> B{Partition Count?}
    B -->|< 2x cores| C[Repartition Up]
    B -->|2x-4x cores| D[Good - No Change]
    B -->|> 10x cores| E[Coalesce Down]

    C --> F{Need Key-Based Joins?}
    F -->|Yes| G["repartition(n, 'key_col')"]
    F -->|No| H["repartition(n)"]
    E --> I["coalesce(target_partitions)"]

Broadcast Joins for Small Tables¶

When joining a large table with a small table (< 100 MB by default, configurable up to 2 GB), use broadcast joins to avoid expensive shuffles.

from pyspark.sql import functions as F

# Automatic broadcast (Spark decides based on table size)
# Default threshold: spark.sql.autoBroadcastJoinThreshold = 10485760 (10 MB)
spark.conf.set("spark.sql.autoBroadcastJoinThreshold", 100 * 1024 * 1024)  # 100 MB

# Explicit broadcast hint
from pyspark.sql.functions import broadcast

large_df = spark.read.format("delta").load(".../slot_telemetry/")
small_df = spark.read.format("delta").load(".../machine_reference/")

result = large_df.join(
    broadcast(small_df),
    on="machine_id",
    how="left"
)

Broadcast join candidates in this project:

Caching Strategies¶

Cache DataFrames that are read multiple times in the same notebook.

# Cache when a DataFrame is used in multiple downstream operations
silver_df = spark.read.format("delta").load(".../silver_player_transactions/")
silver_df.cache()
silver_df.count()  # Trigger materialization

# Use in multiple operations
daily_summary = silver_df.groupBy("transaction_date").agg(F.sum("amount"))
player_summary = silver_df.groupBy("player_id").agg(F.sum("amount"))

# Unpersist when done
silver_df.unpersist()

Cache vs Persist:

Cache management rules:

1. Always unpersist() when done -- cached data consumes cluster memory
2. Never cache DataFrames larger than 50% of cluster memory
3. Cache after filters and projections, not before
4. Trigger materialization with .count() after caching

Predicate Pushdown with Delta Lake¶

Delta Lake supports predicate pushdown, which means filters are pushed down to the file level, skipping entire files that don't match.

# Good: Filter pushes down to Delta file-level statistics
df = spark.read.format("delta") \
    .load(".../bronze_slot_telemetry/") \
    .filter(F.col("event_date") == "2026-03-12")  # Only reads relevant files

# Bad: UDF prevents pushdown
from pyspark.sql.types import BooleanType
@F.udf(BooleanType())
def custom_filter(date_val):
    return str(date_val) == "2026-03-12"

df = spark.read.format("delta") \
    .load(".../bronze_slot_telemetry/") \
    .filter(custom_filter(F.col("event_date")))  # Reads ALL files

Maximizing pushdown effectiveness:

Z-ORDER Optimization¶

Z-ORDER co-locates related data in the same files, dramatically improving query performance for filtered reads.

-- Optimize slot telemetry for queries filtered by machine_id and event_date
OPTIMIZE bronze_slot_telemetry
ZORDER BY (machine_id, event_date);

-- Optimize USDA data for queries filtered by state and commodity
OPTIMIZE silver_usda_crop_production
ZORDER BY (state_code, commodity_code);

-- Optimize EPA data for geographic queries
OPTIMIZE silver_epa_aqi_readings
ZORDER BY (state_code, county_code, parameter_code);

Z-ORDER strategy by table:

V-ORDER for Direct Lake¶

V-ORDER is a write-time optimization that sorts data within Parquet files for optimal Direct Lake performance. It is enabled by default in Fabric Lakehouses.

# V-ORDER is applied automatically when writing to Fabric Lakehouse tables
# Verify it is enabled:
spark.conf.get("spark.sql.parquet.vorder.enabled")  # Should be "true"

# Force V-ORDER on external writes:
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")

df.write.mode("overwrite").format("delta").saveAsTable("lh_gold.daily_revenue")

V-ORDER impact:

Adaptive Query Execution (AQE)¶

AQE dynamically optimizes query execution based on runtime statistics. It is enabled by default in Fabric Spark.

# Verify AQE is enabled (should be by default)
spark.conf.get("spark.sql.adaptive.enabled")  # "true"

# Key AQE settings
spark.conf.set("spark.sql.adaptive.coalescePartitions.enabled", "true")
spark.conf.set("spark.sql.adaptive.skewJoin.enabled", "true")
spark.conf.set("spark.sql.adaptive.localShuffleReader.enabled", "true")

# AQE automatically:
# 1. Coalesces small post-shuffle partitions
# 2. Splits skewed partitions for balanced processing
# 3. Converts sort-merge joins to broadcast joins when runtime stats show small tables

AQE benefits by scenario:

🔄 Pipeline Parallelism¶

ForEach Activity with Batch Count¶

The ForEach activity processes items in parallel up to the configured batchCount.

{
    "name": "ForEach_Ingest_Sources",
    "type": "ForEach",
    "typeProperties": {
        "isSequential": false,
        "batchCount": 20,
        "items": "@pipeline().parameters.source_tables"
    }
}

Batch count recommendations:

Maximum batch count: 50 (Fabric limit)

Concurrent Pipeline Runs¶

Control how many instances of the same pipeline can run simultaneously.

{
    "concurrency": 5,
    "pipelinePolicy": {
        "elapsedTimeMetric": {
            "duration": "1.00:00:00"
        }
    }
}

Concurrency by pipeline type:

Dependency Optimization: Fan-Out / Fan-In¶

Design pipeline dependencies to maximize parallel execution.

flowchart TD
    subgraph FanOut["Fan-Out: Parallel Ingestion"]
        A[Start] --> B[Copy: USDA]
        A --> C[Copy: NOAA]
        A --> D[Copy: EPA]
        A --> E[Copy: DOI]
        A --> F[Copy: SBA]
    end

    subgraph FanIn["Fan-In: Wait for All"]
        B --> G[Checkpoint: All Sources Complete]
        C --> G
        D --> G
        E --> G
        F --> G
    end

    subgraph Sequential["Sequential: Transform"]
        G --> H[Notebook: Silver USDA]
        G --> I[Notebook: Silver NOAA]
        G --> J[Notebook: Silver EPA]
        H --> K[Notebook: Gold Federal Summary]
        I --> K
        J --> K
    end

Anti-pattern: unnecessary sequential dependencies

BAD:  Copy A -> Copy B -> Copy C -> Transform A -> Transform B -> Transform C
GOOD: Copy A ─┐              Transform A ─┐
      Copy B ─┼─ Checkpoint ─ Transform B ─┼─ Gold Aggregation
      Copy C ─┘              Transform C ─┘

Pipeline Activity Limits¶

Be aware of Fabric pipeline limits when designing parallel flows:

⚡ Direct Lake Performance¶

Framing Optimization¶

Direct Lake reads Delta Lake files directly without importing data. "Framing" is the process of identifying which files to read. Optimize framing by keeping the number of Delta files manageable.

-- Check file count per table
SELECT COUNT(*) as file_count, SUM(size) / (1024*1024*1024) as size_gb
FROM delta_log('.../gold_daily_revenue/')
WHERE add IS NOT NULL;

Framing optimization targets:

-- Optimize and compact files
OPTIMIZE gold_daily_revenue;

-- Vacuum old files (default retention: 7 days)
VACUUM gold_daily_revenue RETAIN 168 HOURS;

Column Pruning¶

Direct Lake performance improves when queries request fewer columns. Design semantic models with this in mind.

Best practices:

1. Create Gold layer views with only the columns needed for reporting
2. Avoid SELECT * in semantic model table definitions
3. Use calculated columns sparingly -- prefer measures
4. Split wide tables into fact and dimension tables (star schema)

-- Instead of one wide table with 50+ columns:
-- Create focused Gold tables

-- Fact: daily_slot_revenue (narrow, high-row)
CREATE TABLE gold_daily_slot_revenue AS
SELECT
    revenue_date,
    machine_id,
    property_id,
    coin_in,
    coin_out,
    gross_revenue,
    net_revenue,
    handle_pulls
FROM silver_slot_summary;

-- Dimension: machine_details (wide, low-row)
CREATE TABLE gold_dim_machine AS
SELECT
    machine_id,
    machine_type,
    manufacturer,
    denomination,
    floor_location,
    install_date,
    property_id
FROM silver_machine_master;

Aggregation Tables¶

Pre-aggregate common query patterns to reduce Direct Lake scan volume.

-- Pre-aggregated table for common dashboard queries
CREATE TABLE gold_agg_monthly_revenue AS
SELECT
    DATE_TRUNC('month', revenue_date) AS revenue_month,
    property_id,
    machine_type,
    SUM(gross_revenue) AS total_gross_revenue,
    SUM(net_revenue) AS total_net_revenue,
    SUM(handle_pulls) AS total_handle_pulls,
    COUNT(DISTINCT machine_id) AS active_machines,
    AVG(gross_revenue / NULLIF(handle_pulls, 0)) AS avg_revenue_per_pull
FROM gold_daily_slot_revenue
GROUP BY DATE_TRUNC('month', revenue_date), property_id, machine_type;

Aggregation table strategy:

Composite Models for Large Datasets¶

When a single Direct Lake model cannot hold all required data, use composite models.

flowchart LR
    subgraph DirectLake["Direct Lake (Hot Data)"]
        A[Current Year Facts]
        B[Active Dimensions]
    end

    subgraph Import["Import Mode (Cold Data)"]
        C[Historical Aggregates]
        D[Reference Tables]
    end

    subgraph DualStorage["Dual Storage"]
        E[Shared Dimensions]
    end

    DirectLake --> F[Composite Model]
    Import --> F
    DualStorage --> F
    F --> G[Power BI Reports]

When to use composite models:

🔍 KQL Query Performance¶

Materialized Views¶

Materialized views pre-compute and store aggregation results, dramatically improving repeated query patterns.

// Create materialized view for hourly slot telemetry aggregates
.create materialized-view SlotTelemetryHourly on table slot_telemetry
{
    slot_telemetry
    | summarize
        avg_coin_in = avg(coin_in),
        total_coin_in = sum(coin_in),
        total_coin_out = sum(coin_out),
        handle_pulls = count(),
        active_machines = dcount(machine_id)
        by bin(event_time, 1h), property_id, machine_type
}

// Create materialized view for EPA AQI hourly readings
.create materialized-view AqiHourly on table epa_aqi_readings
{
    epa_aqi_readings
    | summarize
        avg_aqi = avg(aqi_value),
        max_aqi = max(aqi_value),
        reading_count = count()
        by bin(reading_time, 1h), state_code, county_code, parameter_code
}

Update Policies¶

Update policies transform data at ingestion time, avoiding repeated transformation costs on query.

// Define a function that transforms raw data
.create function ParseSlotTelemetry()
{
    raw_slot_events
    | extend
        machine_id = tostring(parsed_data.machine_id),
        event_type = tostring(parsed_data.event_type),
        coin_in = todouble(parsed_data.coin_in),
        coin_out = todouble(parsed_data.coin_out),
        event_time = todatetime(parsed_data.timestamp)
    | project-away parsed_data
}

// Attach update policy to target table
.alter table slot_telemetry policy update
@'[{"IsEnabled": true, "Source": "raw_slot_events", "Query": "ParseSlotTelemetry()", "IsTransactional": true}]'

Hot / Warm Cache Tiers¶

Configure cache policies to keep frequently accessed data in hot (SSD) cache while older data moves to warm (blob) storage.

// Hot cache: last 30 days (fast queries)
.alter table slot_telemetry policy caching hot = 30d

// Retention: keep 2 years total
.alter table slot_telemetry policy retention softdelete = 730d

// Different policies by table importance
.alter table compliance_ctr_filings policy caching hot = 365d    // Keep compliance data hot for 1 year
.alter table noaa_weather_observations policy caching hot = 90d  // 3 months hot for weather
.alter table epa_aqi_readings policy caching hot = 90d           // 3 months hot for AQI
.alter table usda_crop_production policy caching hot = 180d      // 6 months hot for crop data

Cache tier sizing:

KQL Query Best Practices¶

// GOOD: Filter early, project early
slot_telemetry
| where event_time > ago(24h)           // Filter first
| where machine_type == "slot"           // Narrow further
| project event_time, machine_id, coin_in, coin_out  // Select only needed columns
| summarize total_coin_in = sum(coin_in) by bin(event_time, 1h), machine_id

// BAD: Late filtering, wide scan
slot_telemetry
| summarize total_coin_in = sum(coin_in) by bin(event_time, 1h), machine_id
| where event_time > ago(24h)           // Filter AFTER aggregation -- scans all data

// GOOD: Use has/has_cs instead of contains for string matching
slot_telemetry
| where machine_id has "SLOT-A"         // Uses term index, fast

// BAD: contains scans all strings
slot_telemetry
| where machine_id contains "SLOT-A"    // Full string scan, slow

// GOOD: Limit joins to filtered datasets
let active_machines = machine_reference | where status == "active" | project machine_id;
slot_telemetry
| where event_time > ago(1h)
| where machine_id in (active_machines)

// GOOD: Use materialized views for repeated patterns
SlotTelemetryHourly  // Pre-computed materialized view
| where event_time > ago(7d)
| summarize daily_revenue = sum(total_coin_in - total_coin_out) by bin(event_time, 1d)

Query performance checklist:

📈 Benchmark Guidelines by Data Volume¶

Small (< 1 GB daily ingestion)¶

Typical for: SBA loan data (weekly), DOI resource metadata, reference tables.

Medium (1-10 GB daily ingestion)¶

Typical for: USDA crop data, NOAA weather, EPA AQI, casino daily summaries.

Large (10-100 GB daily ingestion)¶

Typical for: Casino slot telemetry (all properties), NOAA historical backfill, healthcare encounter data.

Very Large (> 100 GB daily ingestion)¶

Typical for: Multi-property casino telemetry, real-time streaming at scale, full federal data warehouse.

💰 Cost vs Performance Trade-Offs¶

CU Consumption by Activity Type¶

Microsoft Fabric uses Capacity Units (CUs) as the universal billing metric. Understanding CU consumption helps optimize cost.

Cost Optimization Strategies¶

flowchart TD
    A[Cost Optimization] --> B[Right-Size Compute]
    A --> C[Optimize Data Layout]
    A --> D[Schedule Intelligently]
    A --> E[Monitor and Adjust]

    B --> B1[Match Spark pool to workload]
    B --> B2[Use Starter Pools for dev]
    B --> B3[Autoscale for variable loads]

    C --> C1[OPTIMIZE and VACUUM regularly]
    C --> C2[Partition by query patterns]
    C --> C3[V-ORDER for Direct Lake]

    D --> D1[Off-peak scheduling]
    D --> D2[Stagger competing workloads]
    D --> D3[Incremental over full loads]

    E --> E1[Monitor CU utilization]
    E --> E2[Review throttling events]
    E --> E3[Track cost per pipeline]

Cost-Performance Matrix¶

F64 Capacity Budget Planning¶

For this project's F64 SKU:

⭐ Summary¶

Performance optimization in Microsoft Fabric follows a layered approach:

1. Copy Activity: Right-size DIUs, enable parallel copies with source partitioning, use staging for cross-network transfers
2. Spark: Optimize partitions (2-4x cores), broadcast small tables, cache reused DataFrames, enable AQE
3. Delta Lake: OPTIMIZE + ZORDER for query patterns, V-ORDER for Direct Lake, VACUUM to manage file count
4. Pipelines: Maximize parallelism with ForEach batch count and fan-out/fan-in dependency patterns
5. Direct Lake: Column pruning, aggregation tables, composite models for mixed hot/cold data
6. KQL: Materialized views, update policies, hot/warm cache tiers, filter-first query patterns
7. Cost: Right-size compute, schedule off-peak, prefer incremental loads, monitor CU utilization

📚 Related Documents¶

Back to Top | Best Practices | Home