Topic 2 of 6
Analytical data stores
Data ingested for analytics ends up in an analytical store: a relational data warehouse, a file-based data lake, or a lakehouse that combines both. The exam asks you to recognize when to use each, identify a star schema, and tell the Fabric Lakehouse from the Fabric Warehouse.
Contents
Types of analytical store
There are two common types of analytical store, plus a hybrid approach that combines their strengths:
Data warehouse
A relational database optimized for analytics
Data lake
A distributed file store
Lakehouse
Lake files with SQL queries
Data warehouse
A relational database whose schema is optimized for analytics rather than transactional workloads. Data from a transactional store is transformed into a schema with:
Fact tables
Central tables with the numeric values, for example sales orders.
Dimension tables
Represent the entities data is aggregated by: customer, product, store, time.
That makes it easy to get, for example, total monthly revenue by product for each store. A data warehouse is a good choice when transactional data can be organized into structured tables and you want to query them with SQL.
Star and snowflake schemas
Star schema
A fact table related to one or more dimension tables. The shape resembles a star: the fact in the center and the dimensions around it.
Snowflake schema
An extension of the star that adds tables related to the dimensions to represent hierarchies: product related to product categories.
DimCustomer
|
DimDate — FactSales — DimProduct — DimCategory ← snowflake
|
DimStoreData lake
A file store, usually on a distributed file system for high-performance access. Distributed processing engines like Apache Spark run queries on the files and return data for reporting and analytics.
- They often apply schema-on-read: the tabular schema is defined when the files are read for analysis, with no constraints applied when they're stored.
- They support a mix of structured, semi-structured, and even unstructured data.
- In Azure, a data lake is typically built with Azure Data Lake Storage Gen2 (D3) or OneLake.
Lakehouse and Delta Lake
A hybrid approach that combines lake and warehouse: raw data is stored as files in the data lake, and a SQL analytics endpoint exposes those files as tables you can query with SQL.
Delta Lake makes it possible
Delta Lake is the standard open-source lakehouse format used by Microsoft Fabric and Azure Databricks. It adds relational capabilities on top of Parquet files: tables with schema enforcement and transactional consistency, batch and streaming sources, and a SQL API for querying.
In Microsoft Fabric, all lakehouse data is stored in OneLake. Creating a Fabric Lakehouse automatically creates its SQL analytics endpoint; a Fabric Warehouse's data also lives in OneLake.
Fabric Lakehouse and Warehouse
| Fabric Lakehouse | Fabric Warehouse | |
|---|---|---|
| What it is | Data lake storage with SQL querying | A fully managed relational data warehouse |
| Format | Delta Lake on OneLake | Also backed by OneLake, in Delta format |
| How you work | Notebooks and Spark, plus a SQL endpoint | T-SQL |
| Choose it when | Mixed or semi-structured data, or machine learning workflows | Structured data, a team that writes SQL, strong schema, and many concurrent users |
Fabric also includes Data Factory for ingestion, Real-Time Intelligence for logs and telemetry, and mirroring to replicate Cosmos DB, Azure SQL Database, or Snowflake into OneLake.
Analytical stores in Azure Databricks
Azure Databricks uses Delta Lake as its native format, giving every table transaction support, schema enforcement, and versioning. For SQL-based reporting and BI it offers the Databricks SQL Warehouse, a SQL endpoint optimized for BI tools and concurrent query workloads.
Combining services
Each service acts as an analytical store because it provides a schema and interface for querying, even though the data often sits in a data lake. They can be combined: an ELT process copies data into the lake, an Azure Databricks notebook processes a large volume, and the results are loaded into tables in a Microsoft Fabric Warehouse.
Comparing stores
| Data warehouse | Data lake | Lakehouse | |
|---|---|---|---|
| Storage | Relational tables | Files | Files (Delta Lake) |
| Schema | On write | On read | Schema-enforced tables over files |
| Data types | Structured | Structured, semi-structured, and unstructured | Mixed |
| Querying | SQL | Engines like Spark | SQL and Spark |
Exam scenarios
| Situation | Answer | Why |
|---|---|---|
| Transactional data that fits in tables and is queried with SQL | Data warehouse | A relational schema optimized for analytics. |
| Sales in a central table related to customer, product, store, and time | Star schema | A fact table with dimensions. |
| Product related in turn to a categories table | Snowflake schema | Dimensions with hierarchies in extra tables. |
| Analyze structured, semi-structured, and unstructured data without enforcing a schema on write | Data lake | Schema-on-read. |
| Lake files queryable with SQL and with transactions | Lakehouse with Delta Lake | It combines lake and warehouse. |
| Mixed data and machine learning workflows with Spark in Fabric | Fabric Lakehouse | Notebooks, Spark, and a SQL endpoint. |
| Structured data, a SQL team, and many concurrent users | Fabric Warehouse | T-SQL with strong schema and concurrency. |
| Concurrent BI queries on Delta tables in Databricks | Databricks SQL Warehouse | A SQL endpoint for BI tools. |
Common exam traps
❓ In a star schema, are the numeric values in the dimension tables?
No. Numeric values are in the fact table; dimensions represent the entities you aggregate by.
❓ What sets a snowflake apart from a star?
The snowflake adds tables related to the dimensions to represent hierarchies.
❓ Does a data lake enforce the schema when data is stored?
No. It usually applies schema-on-read, with no constraints on write.
❓ Which format makes lakehouses possible in Fabric and Databricks?
Delta Lake, which adds transactions and schema on top of Parquet.
❓ Which language do you query a Fabric Warehouse with?
T-SQL.
❓ When is a Fabric Lakehouse better than a Warehouse?
With mixed or semi-structured data, or with machine learning workflows in Spark.
❓ Do you have to create a Fabric Lakehouse's SQL endpoint manually?
No. It's created automatically when you create the Lakehouse.
Did you get this topic?
Test what you just learned
A team has JSON files, CSV files, and images, and wants to analyze them with Spark without defining a schema when storing them. Which analytical store fits best?
Ready to test "Analytical data stores"?
Original questions, each with a full explanation.
Start a mock exam