3.2.1. Choosing the Right Data Store
💡 First Principle: Fabric offers multiple data stores optimized for different workloads—like specialized containers for different goods. Choosing correctly impacts performance, cost, and query capabilities. A warehouse optimizes for T-SQL; a lakehouse optimizes for Spark; a KQL database optimizes for time-series.
Scenario: You need to store: (1) raw CSV files from external systems, (2) transformed relational data for SQL analysts, (3) real-time sensor data for operational dashboards. Each requires a different store.
Data Store Selection Guide
| Data Store | Best For | Query Language | Storage Format |
|---|---|---|---|
| Lakehouse | Big data, data science, flexible schema | Spark SQL, PySpark | Delta + Files |
| Data Warehouse | Structured analytics, SQL analysts | T-SQL | Delta |
| KQL Database | Real-time analytics, time-series | KQL | Optimized columnar |
| OneLake Files | Raw file storage, staging | N/A (file access) | Any |
Visual: Data Store Selection
Lakehouse vs. Warehouse details that exams test:
- SQL analytics endpoint: every lakehouse gets one automatically. It is a read-only T-SQL surface (SELECT, views, functions, stored procedures) over the same Delta files Spark writes: no copy, and new tables appear through automatic metadata sync. Only Delta tables in the Tables section appear; files in Files are not exposed as tables. Power BI connects to it like a warehouse.
- Warehouse: full T-SQL DML (INSERT, UPDATE, DELETE), stored procedures and multi-statement transactions. Choose it when SQL developers must write data with T-SQL; choose a lakehouse when Spark writes the tables and SQL users only read them.
- Power BI storage modes: Import caches a copy in memory (fastest, only as fresh as the last refresh); DirectQuery queries the source live (current, limited by source speed); Direct Lake reads Delta tables in OneLake directly (near-import speed without a scheduled copy); Dual tables act as cached or live per query.
⚠️ Exam Trap: Storing structured data as raw files when a lakehouse table is more appropriate creates unnecessary work. Files require manual schema management; Delta tables provide schema enforcement, ACID transactions, and SQL access automatically.
