Lakehouse vs. Warehouse

Fabric offers two primary storage artifacts. Choose based on your workload:

Notebook Best Practices

• Parameterize notebooks: Use Fabric's built-in parameters for environment-specific values (file paths, dates, flags)
• Modular design: Break complex logic into reusable functions or separate notebooks using %run
• Error handling: Use try/catch blocks and log errors to a monitoring table
• Avoid hardcoded paths: Use relative paths within OneLake or parameterized paths
• Version control: Connect notebooks to Git for versioning and code review

Spark Optimization

• V-Order optimization: Enable V-Order (a Fabric-native columnar sort optimization) for write-optimized Delta tables — significantly faster reads for Power BI and SQL
• Optimize file sizes: Target 128 MB – 1 GB files. Use OPTIMIZE (bin-compaction) to merge small files
• Partition wisely: Partition by high-cardinality columns used in filters (e.g., year, region). Avoid over-partitioning
• Use Delta caching: SSD caching for repeated reads on the same data
• Right-size sessions: Don't default to large Spark clusters — start small and scale up based on the Spark UI metrics

File Format Guidance

✅ Delta (Parquet + transaction log)  — Default for all Fabric tables
✅ Parquet                             — Good for external/read-only data
⚠️  CSV                                — Only for Bronze ingestion, convert to Delta ASAP
❌ JSON (nested)                       — Flatten before storing in tables
❌ Excel/XML                           — Convert during ingestion pipeline

Shortcuts Strategy

Shortcuts are pointers to data in external locations — they appear as folders in your lakehouse but don't copy data. Use them to:

• Access data in other lakehouses or workspaces without duplication
• Connect to external ADLS Gen2, AWS S3, or Google Cloud Storage
• Enable cross-domain data sharing without moving data
• Create a virtual data mesh where each domain manages its data but shares via shortcuts

Overview

Fabric's Real-Time Intelligence experience is designed for high-volume streaming and time-series data. It's powered by the same engine behind Azure Data Explorer (Kusto) and provides sub-second query latency on billions of records.

Key Components

Streaming Ingestion Patterns

When to Use Real-Time vs. Batch

• Real-Time Intelligence: IoT telemetry, application logs, clickstream analytics, fraud detection, operational monitoring
• Batch (Lakehouse/Warehouse): Historical reporting, data warehousing, regulatory reporting, large-scale transformations
• Hybrid: Ingest in real-time to Eventhouse for operational use, then copy to Lakehouse for long-term analytics

Direct Lake Mode

Direct Lake is a game-changing connectivity mode exclusive to Fabric. It combines the performance of Import mode with the freshness of DirectQuery — without copying data into the semantic model.

• Reads data directly from Delta tables in OneLake
• No data refresh needed — reports always show the latest data
• Performance comparable to Import mode for most scenarios
• Falls back to DirectQuery if data exceeds memory (configurable behavior)

Semantic Model Best Practices

• Build star schemas in your Gold layer — separate facts and dimensions
• Use surrogate keys (integer) instead of natural keys for relationships
• Minimize calculated columns — push computations to the Lakehouse/Warehouse layer
• Use calculation groups for reusable time intelligence (YTD, MTD, YoY)
• Enable automatic aggregations for large tables to speed up high-level queries

Report Governance

• Centralize semantic models: Create shared models that multiple reports connect to (avoid dataset proliferation)
• Use endorsement: Certify production-ready models and promote development models
• Apply RLS: Configure row-level security on semantic models for multi-tenant reports
• Monitor usage: Use the usage metrics to track report adoption

Performance Optimization

Copilot for Power BI — Prep Data for AI Toolkit

Power BI provides a unified "Prep data for AI" experience (available in both Desktop and the Power BI service) with three key features to optimize your semantic model for Copilot interactions.

1. AI Data Schema — Simplify Your Model

Define a focused subset of your model's fields for Copilot to prioritize. By selecting only the most relevant fields, you reduce ambiguity and help Copilot deliver clearer, more accurate responses.

• Open Prep data for AI → Simplify data schema tab
• Select fields you want Copilot to reason over — prioritize clean columns with limited ambiguity
• Remove any fields that could be confusing (duplicate names, internal IDs, staging columns)
• Hidden fields in the semantic model are automatically excluded in the initial schema
• Relationships between fields are still respected even if one side is excluded

2. AI Instructions — Add Business Context

Provide context, business logic, and specific guidance directly on the semantic model. Copilot uses these instructions to better interpret user questions by incorporating organizational language, terminology, and analytical priorities.

Good vs. Bad: AI Instructions

Good vs. Bad: Semantic Model Design for AI

3. Verified Answers — Curated Responses

Verified answers are human-approved, visual responses triggered by predefined phrases. They provide consistent, reliable answers to common business questions and help Copilot learn from effective responses over time.

• Select a visual → "Set up a verified answer" from the … menu
• Add 5–7 trigger phrases per answer that reflect how users naturally ask about the data
• Use Copilot-generated suggestions for additional trigger phrases
• Add up to 3 filters that users can adjust via natural language (e.g., "for the Northeast region")
• Copilot uses both exact and semantic matching — users don't need to match trigger phrases word-for-word
• Stored at the semantic model level — works across all reports using that model

4. Mark Model "Approved for Copilot"

After preparing your semantic model, mark it as Approved for Copilot in the model's settings in the Power BI service. This removes the friction treatment (confidence warnings) from answers in the standalone Copilot experience. Changes can take up to 24 hours to propagate for models with many reports.

Fabric Data Agents — Data Preparation

Fabric Data Agents are configurable AI artifacts that enable conversational Q&A over your organizational data. They use NL-to-SQL, NL-to-DAX, and NL-to-KQL to query data across OneLake, semantic models, and KQL databases.

Selecting Data Sources & Tables

• A Data Agent supports up to 5 data sources in any combination: Lakehouses, Warehouses, KQL databases, Power BI semantic models, Ontologies, and Microsoft Graph
• For each source, select only the relevant tables — focus the agent on data that matters
• If your data starts as files (CSV, JSON), ingest into tables first — agents query tables, not individual files
• For Power BI semantic models, users only need Read permission to interact via the agent

Agent Instructions & Examples

Good vs. Bad: Data Agent Setup

Governance & Security Layers

Data Agents enforce a strict intent precedence model — higher layers always override lower ones:

• Agents enforce read-only data access — no writes, no mutations
• All queries run with the requesting user's credentials (least-privilege)
• Microsoft Purview policies apply: DLP, access restrictions, risk discovery, and audit
• Optional Azure AI Content Safety integration for content risk controls

AI Readiness Checklist

A combined checklist for preparing your data for both Copilot for Power BI and Fabric Data Agents: