Home > Best Practices > Decision Guide

🧭 Decision Guide: Choosing the Right Tool¶

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

📑 Table of Contents¶

• Overview
• Quick Decision Matrix
• Tool Comparison
• Decision Tree: Data Movement
• Decision Tree: Storage Destination
• Scenario-Based Recommendations
• Tool Selection by Persona
• Performance Considerations
• Cost Considerations
• Hybrid Patterns
• Anti-Patterns to Avoid
• Quick Reference Card
• Decision Checklist
• Related Documents

📋 Overview¶

Microsoft Fabric offers multiple tools for data movement and transformation. This guide helps you choose the right tool based on your specific requirements, data volumes, and team capabilities.

⚡ Quick Decision Matrix¶

🔍 Tool Comparison¶

Data Movement Tools¶

Storage Tools¶

🔀 Decision Tree: Data Movement¶

flowchart TD
    A[Data Movement Need] --> B{Real-time?}
    B -->|Yes| C[Eventstream]
    B -->|No| D{Data Volume?}

    D -->|Small < 1GB| E{Transform Complexity?}
    D -->|Medium 1-100GB| F{CDC Required?}
    D -->|Large > 100GB| G[Pipeline Copy Activity]

    E -->|Low| H[Dataflow Gen2]
    E -->|High| I{Code Preferred?}

    I -->|Yes| J[Spark Notebook]
    I -->|No| K[Dataflow with Staging]

    F -->|Yes| L[Copy Job]
    F -->|No| M{Transform Needed?}

    M -->|Yes| N{In-flight Transform?}
    M -->|No| O[Pipeline Copy Activity]

    N -->|Yes| P[Dataflow Gen2]
    N -->|No| Q[Pipeline + Spark]

    G --> R{Parallel Load?}
    R -->|Yes| S[Pipeline with ForEach]
    R -->|No| T[Single Copy Activity]

🗄️ Decision Tree: Storage Destination¶

flowchart TD
    A[Storage Decision] --> B{Primary Workload?}

    B -->|Data Engineering| C{Team Skill?}
    B -->|Business Analytics| D{Query Pattern?}
    B -->|Real-time| E[Eventhouse]

    C -->|Spark/Python| F[Lakehouse]
    C -->|SQL| G{Transaction Needs?}

    G -->|Full ACID| H[Warehouse]
    G -->|Read-heavy| I[Lakehouse SQL Endpoint]

    D -->|Complex SQL| J[Warehouse]
    D -->|Mixed| K[Both + Shortcuts]
    D -->|BI Direct Query| L{Performance Priority?}

    L -->|Speed| M[Warehouse]
    L -->|Flexibility| N[Lakehouse + Direct Lake]

💡 Scenario-Based Recommendations¶

Scenario 1: Enterprise Data Warehouse Migration¶

Context: Migrating from on-premises Oracle/SQL Server to Fabric

Recommendation:

Source → Pipeline Copy → Lakehouse (Bronze) → Spark (Transform) → Warehouse (Gold)

Scenario 2: Real-time Dashboard¶

Context: Casino floor monitoring with live updates

Recommendation:

IoT Devices → Eventstream → Eventhouse → Real-time Dashboard
                    ↓
              Lakehouse (archive)

Scenario 3: Self-Service Analytics¶

Context: Business analysts need to prepare and analyze data

Recommendation:

Various Sources → Dataflow Gen2 → Lakehouse → Semantic Model → Power BI

Scenario 4: CDC Replication¶

Context: Keep Fabric in sync with operational SQL Server

Recommendation:

SQL Server (CDC) → Copy Job → Lakehouse/Warehouse

Scenario 5: Complex Data Science Workload¶

Context: ML model training with feature engineering

Recommendation:

Multiple Sources → Lakehouse (Bronze) → Spark (Feature Engineering) → Lakehouse (Features) → ML Model

👥 Tool Selection by Persona¶

📈 Performance Considerations¶

By Data Volume¶

By Latency Requirement¶

💰 Cost Considerations¶

Compute Cost Ranking (Lower = Less Expensive)¶

1. Dataflow Gen2 - Efficient for small-medium volumes
2. Pipeline Copy - Optimized for bulk transfer
3. Copy Job - Similar to Pipeline
4. Spark - Higher cost, more powerful
5. Eventstream - Cost based on throughput

Storage Cost¶

All Fabric storage uses OneLake with the same pricing: - Lakehouse Tables (Delta) - Warehouse Tables (Delta) - Eventhouse (KQL Database)

🔗 Hybrid Patterns¶

Pipeline + Dataflow¶

Large Source → Pipeline Copy → Staging Lakehouse → Dataflow (cleanse) → Final Lakehouse

Use when: Need bulk copy performance AND Power Query transformations

Pipeline + Spark¶

Source → Pipeline Copy → Bronze Lakehouse → Spark Notebook → Silver/Gold Lakehouse

Use when: Need parallel copy AND complex transformations

Eventstream + Lakehouse¶

Real-time Events → Eventstream → Eventhouse (hot) → Lakehouse (cold)

Use when: Need real-time AND historical analytics

🚫 Anti-Patterns to Avoid¶

📇 Quick Reference Card¶

When to Use Each Tool¶

Pipeline Copy Activity - ✅ Bulk data movement - ✅ High volume (10GB+) - ✅ Scheduled batch loads - ❌ Real-time streaming - ❌ Complex transformations

Copy Job - ✅ CDC/incremental sync - ✅ Automated replication - ✅ Simple setup - ❌ Complex transformations - ❌ Real-time (near-real-time only)

Dataflow Gen2 - ✅ Business user data prep - ✅ 150+ connectors - ✅ Power Query interface - ❌ Very large volumes (100GB+) - ❌ Real-time

Eventstream - ✅ Real-time ingestion - ✅ Event processing - ✅ IoT/streaming scenarios - ❌ Batch processing - ❌ Historical bulk loads

Spark Notebooks - ✅ Complex transformations - ✅ ML/Data Science - ✅ Code flexibility - ❌ Simple copy operations - ❌ Business user self-service

✅ Decision Checklist¶

Before choosing a tool, answer these questions:

1. Volume: How much data? (GB/TB)
2. Latency: Real-time or batch?
3. Complexity: Simple copy or complex transforms?
4. Team: Data engineers or business analysts?
5. Destination: Lakehouse, Warehouse, or both?
6. Pattern: Full load, incremental, or CDC?
7. Sources: How many connectors needed?
8. Code: Code-first or no-code preferred?

🔗 Related Documents¶

⬆️ Back to Top | 📚 Best Practices | 🏠 Home