Skip to content
datastudyguides

DP-900 · 4 Describe an analytics workload

Analytical data stores

Exam objective: Describe options for analytical data stores

A data warehouse is a relational store with a fixed, analytics-ready schema. A data lake is file storage that applies schema on read.

Once data is ingested, it needs somewhere built for analysis rather than for day to day transactions. Two store types cover most scenarios.

A data warehouse is a relational database whose schema is shaped for reporting. Numeric values, such as order totals, sit in a central fact table, while the entities you slice them by, such as product or customer, sit in related dimension tables. That fact and dimension layout is a star schema. Adding further detail tables off a dimension, say splitting products into categories, turns it into a snowflake schema.

A data lake is plain file storage, usually on a distributed file system. Instead of enforcing a schema when data is written, engines such as Apache Spark apply schema on read: the structure is defined at query time, which is why a lake can hold structured, semi-structured and unstructured files side by side.

A lakehouse borrows from both: files stay in the lake, but a SQL endpoint, enabled by the Delta Lake format, lets you query them as if they were tables with an enforced schema and transactional consistency.

On the exam, "fixed schema, SQL, fact and dimension tables" points to a warehouse, while "files, mixed formats, schema on read" points to a lake.

Key points

  • A data warehouse is relational, with a schema built for analytics rather than for transactions.
  • A star schema has one or more fact tables of measures linked to dimension tables you aggregate by.
  • A snowflake schema extends a star schema by relating a dimension table to further detail tables.
  • A data lake stores files and applies schema on read, so no schema is enforced when data is written.
  • A data lakehouse adds a SQL endpoint and schema enforcement on top of lake files, using the Delta Lake format.

Exam trap

Schema on read belongs to data lakes, not warehouses. A scenario that enforces a schema before data is written is describing a warehouse, even if the word lake appears elsewhere in it.

Check yourself

In a star schema, what does a dimension table store?

Practice all 6 on this topic

Go deeper on Microsoft Learn

Checked against Microsoft Learn on October 1, 2026.

How well do you know this?