Describe an Analytics Workload on Azure · Topic 2 of 4
Analytical Data Stores and Services
DP-900 analytical stores: data warehouse, data lake and lakehouse compared, plus Microsoft Fabric, OneLake, Azure Databricks, Azure Synapse Analytics and HDInsight.
- 6 min
- Includes a diagram
- A warehouse is structured and schema on write. A lake is raw and schema on read.
- A lakehouse brings table features to lake files.
- Microsoft Fabric is a SaaS analytics platform with OneLake as its single data lake.
What it is
Analytical data needs a home built for large reads. There are three main kinds of store: a data warehouse for structured, curated data, a data lake for raw files of any shape, and a lakehouse that combines the two ideas.
Microsoft offers several services around them, and the current skills outline names Microsoft Fabric and Azure Databricks first.
How it works
Data warehouse
A data warehouse stores cleaned, structured data from many sources, arranged for fast reporting. The schema is defined before data is loaded, which is called schema on write. Data is often organised as a star schema, with fact tables for measures and dimension tables for descriptions, and queried with SQL.
Data lake
A data lake stores huge amounts of data in its original form, structured, semi structured and unstructured. Nothing has to be shaped on the way in, and structure is applied when the data is read, which is called schema on read. A lake is flexible and low cost. In Azure it is usually built on Azure Data Lake Storage Gen2.
Lakehouse
A lakehouse keeps data in lake files but adds table features such as transactions and SQL access, so one copy of the data serves both data scientists and analysts. Lakehouse tables are commonly stored in the open Delta format, which is a layer over Parquet files.
| Data warehouse | Data lake | Lakehouse | |
|---|---|---|---|
| Data | Structured, curated | Any type, raw | Any type, with tables on top |
| Schema | On write | On read | Both |
| Main users | Analysts, reporting | Data engineers, data scientists | All of them |
| Query with | SQL | Spark, code | SQL and Spark |
Microsoft Fabric
Microsoft Fabric is a unified analytics platform delivered as software as a service. It covers the whole chain, with experiences for Data Engineering, Data Factory, Data Science, Real Time Intelligence, Data Warehouse and Databases, and Power BI for reporting. All of them work over a shared compute and storage model.
Fabric stores data in OneLake, a single logical data lake for the whole organisation, so different workloads read the same copy of the data instead of keeping their own. Because Fabric is SaaS, there is nothing to deploy or patch.
Azure Databricks
Azure Databricks is a managed analytics platform built on Apache Spark. Teams work in shared notebooks with Python, SQL, Scala or R. It is popular for large scale data engineering, machine learning and lakehouse designs, and it integrates with Azure storage and Microsoft Entra ID.
Azure Synapse Analytics
Azure Synapse Analytics is an earlier integrated service that combines data warehousing and big data analytics. It has a dedicated SQL pool for warehouse workloads, a serverless SQL pool that queries files in a lake and bills by data processed, Apache Spark pools and pipelines. It is still used in many solutions, and Fabric is the newer unified platform that builds on the same ideas.
Azure HDInsight
Azure HDInsight is a managed service for open source big data clusters, such as Apache Hadoop, Spark, Hive, HBase and Kafka. It suits organisations that want to run those open source tools in Azure.
Choosing a service
| The need | Service |
|---|---|
| One SaaS platform for the whole analytics chain | Microsoft Fabric |
| Spark notebooks, machine learning and lakehouse on Spark | Azure Databricks |
| T-SQL queries over files in a lake with no idle compute | Synapse serverless SQL pool |
| A SQL data warehouse for reporting | Fabric Data Warehouse or Synapse dedicated SQL pool |
| Open source Hadoop or Kafka clusters | Azure HDInsight |
| Raw files at low cost | Azure Data Lake Storage Gen2 |
A worked scenario
A company wants one platform with no servers to manage. Engineers land raw files in OneLake, a pipeline transforms them into lakehouse tables, analysts query a warehouse with T-SQL, and Power BI reads the same data for dashboards. Because Microsoft Fabric provides all of these, with OneLake underneath, the team needs no separate services. If the data scientists preferred Spark notebooks and an existing Databricks skill set, Azure Databricks would be the alternative.
A star schema in a warehouse
| Table | Role |
|---|---|
| FactSales | One row per sale, with keys and the measures Quantity and Amount |
| DimDate | Calendar attributes, so reports group by month or quarter |
| DimProduct | Product name, category and brand |
| DimCustomer | Customer name, city and segment |
Analysts query this layout with SQL, and Power BI reads it without difficulty. The fact table is large and the dimension tables are small.
Layers in a lakehouse
Lakehouses are often arranged in three layers. The bronze layer holds raw data as it arrived. The silver layer holds cleaned and joined data. The gold layer holds curated tables shaped for reports. Each layer improves the quality of the one before it, and reports read from gold.
Compute and storage are separate
In modern analytics services, storage and compute are separate. Data sits cheaply in the lake, and you start compute only when a query or job needs it. Serverless options charge for the data processed and not for idle time, while dedicated pools bill while they are provisioned. This is why a serverless SQL pool suits occasional exploration of lake files and a dedicated pool suits a steady, heavy reporting load.
Which store holds what
| Data | Best home |
|---|---|
| Raw logs, images and JSON from many sources | Data lake |
| Curated, structured data for company reporting | Data warehouse |
| Both raw and curated data used by engineers and analysts | Lakehouse |
| Live transactions of an application | A transactional database, not an analytical store |
What to remember for the exam
- A warehouse is structured and schema on write. A lake is raw and schema on read.
- A lakehouse brings table features to lake files.
- Microsoft Fabric is a SaaS analytics platform with OneLake as its single data lake.
- Azure Databricks is Spark based. Synapse has dedicated and serverless SQL pools.
- Azure Data Lake Storage Gen2 is the usual lake store.
Fabric is software as a service, so you do not provision servers. Databricks and Synapse need workspaces and compute that you configure.
How the exam asks about this
- "Store raw data of any type, apply structure later." A data lake.
- "A single SaaS platform covering data engineering to reporting." Microsoft Fabric.
- "Run T-SQL over Parquet files, paying only for data processed." A serverless SQL pool.
- "Which Spark based platform supports collaborative notebooks?" Azure Databricks.
Common mistakes
Calling a lake a warehouse. A warehouse is curated and structured. A lake is raw.
Thinking OneLake is a separate product. It is the built in storage of Fabric.
Assuming Synapse and Fabric are the same. They overlap, but Fabric is the newer unified SaaS platform.
Believing a lakehouse is only for engineers. It exists so that analysts and engineers share one copy.
Check yourself
What is schema on read?
Structure is applied when the data is read, as in a data lake.
What is OneLake?
The single logical data lake built into Microsoft Fabric.
Which Synapse resource queries lake files with T-SQL and bills by data processed?
The serverless SQL pool.
Topic 2 of 4 in Describe an Analytics Workload on Azure
Test yourself on Describe an Analytics Workload on Azure
The full Microsoft DP-900 question bank has 503 questions, with timed practice exams and an explanation for every answer.
This study guide is independent and is not affiliated with, endorsed by, or sponsored by Microsoft. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.