Study guide · Summary
DP-900 Azure Data Fundamentals
The essentials of every exam domain, with the services the exam asks about most. Tap a service to see its details.
Fundamentals~50 min read4 domains
What the exam covers
D1 · 25–30% of the exam
Core data concepts
Ways to represent data
Data describes entities (customers, products, orders) and their attributes (name, price, date). Depending on how fixed its structure is, it falls into three types.
| Type | Structure | Examples |
|---|---|---|
| Structured | Fixed schema: every row has the same fields (tables) | Customer and order tables |
| Semi-structured | Has structure, but it varies between instances | JSON documents with different fields |
| Unstructured | No specific structure | Documents, images, audio, video, binaries |
Vector data (embeddings) is a numerical representation of content such as text or images. AI applications use it to find semantically similar content.
File formats
| Format | What it is | Typical use |
|---|---|---|
| CSV / TSV | Delimited text, human-readable | Structured data read by many apps |
| JSON | Hierarchical document of objects and collections | Structured and semi-structured data |
| XML | Readable tags; largely superseded by JSON | Systems that still use it |
| BLOB | Raw binary | Images, video, audio, documents |
| Parquet | Columnar, with row groups and metadata | Data lakes and lakehouses |
| Avro | Row-based; JSON header and binary data | Compressing data and saving bandwidth |
| Delta Lake | Parquet + transaction log | ACID transactions and versioning in a data lake |
Data stores: files and databases
There are two broad categories: file stores and databases. Relational databases store structured data in tables linked by keys and are queried with SQL. Non-relational (NoSQL) databases don't apply a relational schema.
Key-value
Each record is a unique key and a value in any format
Document
Key-value where the value is a JSON document that can be queried
Column family
Rows and columns grouped into related column families
Graph
Entities as nodes and relationships as links
| Use case | Azure data store |
|---|---|
| Transactional app with tables and relationships | Azure SQL, PostgreSQL or MySQL |
| JSON documents at global scale | Azure Cosmos DB |
| Images, video, backups | Azure Blob Storage |
| Data lake for analytics | ADLS Gen2 or OneLake |
| Network shared folder | Azure Files |
| Simple, fast key-value | Azure Table Storage |
Transactional and analytical workloads
A transactional system (OLTP) records many small transactions with CRUD operations. It guarantees ACID properties:
Atomicity
The transaction succeeds completely or fails completely
Consistency
It only takes the database from one valid state to another
Isolation
Concurrent transactions don't interfere with each other
Durability
Committed work stays committed even if the system shuts down
Analytics uses read-only (or read-mostly) systems holding large volumes of historical data:
ETL or ELT
Operational data is extracted and loaded; with ELT it's transformed after loading
Lakehouse or warehouse
Loaded into tables: a lakehouse over files or a relational warehouse
Semantic model
Measures aggregated by dimensions (historically called a cube)
Reports
Visualizations and dashboards over that data
Medallion architecture in a lakehouse: Bronze = raw data as it arrives, Silver = cleansed and conformed, Gold = aggregated and ready for reporting.
Roles in the world of data
| Role | Responsibilities |
|---|---|
| Database administrator | Database design, availability and performance; access permissions; backup and recovery |
| Data engineer | Ingestion and transformation pipelines, analytical stores, data privacy and load monitoring |
| Data analyst | Explore data, build analytical models and create reports and visualizations |
| AI engineer | Integrate AI features with LLMs and ML pipelines; uses Microsoft Foundry |
The same person can play several roles. Other related roles include data scientist and data architect.
D2 · 20–25% of the exam
Relational data on Azure
Relational concepts and normalization
In a relational database, each entity is a table, each row has a unique primary key, and tables are related through foreign keys. Normalization minimizes duplication and enforces integrity:
One table per entity
One column per attribute
A primary key for each row
Foreign keys to link entities
A key can be composite (several columns together). The engine can enforce referential integrity: it won't let you create an order for a customer that doesn't exist.
SQL statements and database objects
| Group | What it's for | Statements |
|---|---|---|
| DDL | Create, modify and remove objects | CREATE, ALTER, DROP, RENAME |
| DCL | Manage permissions | GRANT, DENY, REVOKE |
| DML | Manipulate rows | SELECT, INSERT, UPDATE, DELETE |
UPDATE and DELETE without WHERE affect every row in the table, and SQL doesn't ask "are you sure?". DROP removes the object permanently.
| Dialect | Used by |
|---|---|
| Transact-SQL (T-SQL) | SQL Server and the Azure SQL family |
| PL/pgSQL | PostgreSQL |
| PL/SQL | Oracle |
| Object | What it is |
|---|---|
| View | A virtual table based on a SELECT query |
| Stored procedure | Saved SQL statements run on command, with parameters |
| Index | A sorted copy of a column that speeds up searches; it uses storage and slows INSERT, UPDATE and DELETE |
The Azure SQL family
Tap a service to see its details.
| SQL on VMs | Managed Instance | SQL Database | |
|---|---|---|---|
| Type | IaaS | PaaS | PaaS |
| Compatibility | Full | Near 100% | Core database-level capabilities |
| Management | You manage everything | Automatic updates and backups | Automatic updates and backups |
| Best for | Full server control | Migrations with minimal changes | New cloud apps |
SQL Database comes as a single database or an elastic pool. The serverless tier scales on its own and, in General Purpose, pauses when inactive. Hyperscale goes up to 128 TB.
Open-source databases on Azure
Tap a service to see its details.
Azure Database for MariaDB retired on September 19, 2025; the migration target is Azure Database for MySQL Flexible Server. Current official material only covers MySQL and PostgreSQL.
D3 · 15–20% of the exam
Non-relational data on Azure
Azure Storage
Tap a service to see its details.
| Blob tier | Use | Minimum |
|---|---|---|
| Hot (default) | Frequent access | No minimum |
| Cool | Infrequent access | 30 days |
| Cold | Rare access but fast retrieval | 90 days |
| Archive | Historical; offline, rehydrate (up to 15 h) | 180 days |
| Blob type | What it's for |
|---|---|
| Block | Large files that change infrequently |
| Page | Random reads and writes; VM disks |
| Append | Add only at the end; logs |
| Redundancy | Copies |
|---|---|
| LRS | 3 copies in a single datacenter |
| ZRS | 3 availability zones in the region |
| GRS / GZRS | Asynchronous replication to a secondary region (RA- lets you read it) |
Azure Cosmos DB
Tap a service to see its details.
| API | Model | Query |
|---|---|---|
| NoSQL (native, formerly "SQL API") | JSON documents | SQL-like syntax |
| MongoDB | BSON documents | MongoDB Query Language (MQL) |
| Table | Key-value | Same model as Table Storage |
| Apache Cassandra | Column family | CQL |
| Apache Gremlin | Graphs (vertices and edges) | Gremlin |
To migrate a MongoDB app or use the full MongoDB Query Language, Microsoft recommends Azure DocumentDB, a separate service. Cosmos DB for PostgreSQL is on a retirement path and isn't recommended for new projects.
| Consistency | Guarantee |
|---|---|
| Strong | Every read sees the most recent write |
| Bounded staleness | Reads lag behind writes by at most a configurable interval |
| Session | Consistent within a client session; the most widely used |
| Consistent prefix | Never sees out-of-order writes, but may see stale data |
| Eventual | Replicas converge over time; the weakest and most available |
D4 · 25–30% of the exam
Analytics workloads on Azure
Data ingestion and processing
Tap a service to see its details.
| Fabric option | What it does |
|---|---|
| Pipelines | Orchestrate data movement and transformation activities |
| Dataflows Gen2 | Low-code transformations with Power Query |
| OneLake shortcuts | Live reference to ADLS, S3, Google Cloud Storage or another OneLake, without copying |
| Mirroring | Continuous replication of an external database into OneLake |
| Eventstream | Streaming ingestion from Event Hubs, Kafka, IoT Hub and more |
| Notebooks | Spark code for cases without a connector |
ETL transforms the data before loading it; ELT loads it first and transforms it in the destination, a common pattern in lakehouses.
Analytical data stores
| Store | How it works |
|---|---|
| Data warehouse | A relational database optimized for analytics: fact and dimension tables (star or snowflake schema) |
| Data lake | A file store processed by engines like Spark; schema-on-read |
| Lakehouse | Files in a data lake exposed as tables through a SQL endpoint, thanks to Delta Lake |
Tap a service to see its details.
| Microsoft Fabric | Choose it when |
|---|---|
| Lakehouse | Mixed or semi-structured data, Spark and machine learning; its SQL analytics endpoint queries but doesn't modify data |
| Warehouse | Structured data, a team that writes T-SQL, multi-table ACID transactions |
Microsoft Fabric is a unified SaaS analytics platform; Azure Databricks is the code-first option on Spark. Both use Delta Lake. Microsoft Purview adds data governance: a data map, classification and lineage.
Real-time analytics
| Aspect | Batch | Streaming |
|---|---|---|
| Scope | The whole dataset | Recent data or a time window |
| Size | Large volumes | Individual records or micro batches |
| Latency | Typically hours | Seconds or milliseconds |
| Analysis | Complex | Simple responses, aggregates, rolling averages |
Streaming architecture: an event generates data → it's captured in a source (often a queue) → a perpetual query processes it over time windows → the result goes to a sink.
Tap a service to see its details.
| Fabric Real-Time Intelligence | What it's for |
|---|---|
| Real-Time hub | Central catalog of data streams |
| Eventstream | Ingest, route and transform events |
| Eventhouse | Database optimized for events and time series, queried with KQL |
| Real-Time Dashboard | Live visualization |
| Activator | Automatic actions when a condition is met |
Spark Structured Streaming (in Fabric and Azure Databricks) treats the stream as a table that keeps growing. With Delta Lake, the same table can be a streaming sink and a source for batch queries.
Visualization with Power BI
Tap a service to see its details.
| Modeling concept | What it is |
|---|---|
| Semantic model | Related tables with measures (numeric values) and dimensions (what to group by) |
| Fact table | Events with numeric values, like sales |
| Dimension table | Entities to group by: product, customer, time |
| Star / snowflake schema | Facts linked to dimensions / dimensions with additional tables |
| Hierarchy | Enables drill-up and drill-down: year → month → day |
| Direct Lake | Loads Delta tables from OneLake into memory without copying them into the model |
| Goal | Visualization |
|---|---|
| Many related values or one key figure | Table or card |
| Compare categories | Bar or column |
| Trend over time | Line |
| Proportion of a total | Pie |
| Relationship between two measures | Scatter |
| Compare locations | Map |
Tips for exam day
- Structured = fixed schema (tables). Semi-structured = flexible structure (JSON). Unstructured = documents, images, audio, video. Vector data (embeddings) powers semantic search in AI apps.
- OLTP = many small transactions with ACID guarantees. OLAP = read queries that aggregate large volumes of historical data.
- DBA = permissions, backups and performance. Data engineer = pipelines and integration. Analyst = models and reports. AI engineer = LLM-powered solutions in Microsoft Foundry.
- DDL = CREATE, ALTER, DROP, RENAME. DCL = GRANT, DENY, REVOKE. DML = SELECT, INSERT, UPDATE, DELETE. UPDATE or DELETE without WHERE affects every row.
- SQL on a VM = IaaS, full control. Managed Instance = near-100% compatible, ideal for migrations. SQL Database = PaaS for new apps.
- Blob Storage: Cool minimum 30 days, Cold 90, Archive 180. Archive is offline and rehydration can take up to 15 hours.
- Cosmos DB has 5 APIs: NoSQL (native), MongoDB, Table, Cassandra and Gremlin (graphs). Cosmos DB for PostgreSQL is on a retirement path.
- Medallion: Bronze = raw data, Silver = cleansed, Gold = business-ready. Delta Lake = Parquet + transaction log (ACID).
- Batch = accumulated data, latency of hours, complex analysis. Streaming = each event as it arrives, latency of seconds or milliseconds, simple calculations.
- Line = trend over time. Bar/column = compare categories. Pie = part of a whole. Scatter = correlation between two measures. Map = data by location.
Was this guide helpful?