This domain is worth 25 to 30% of DP-900 and it sets the vocabulary for everything else. Questions are short descriptions (“a retailer stores order rows and needs every change to succeed or fail together”) and you pick the term or the workload type. Nothing here needs hands-on skill; you need clean definitions and the ability to tell look-alike terms apart.
Ways to represent data
Data is a set of facts: numbers, text, images, sensor readings. How much structure it carries decides which store and which tools suit it.
| Type | Shape | Examples | Typical home |
|---|---|---|---|
| Structured | Fixed schema: every row has the same columns with defined types | Rows in a relational table, a CSV with a header, a spreadsheet | Relational database (Azure SQL), data warehouse |
| Semi-structured | Has organisation (tags, keys) but no rigid schema; records can differ | JSON, XML, YAML, key-value pairs, graph data | Document or key-value store (Cosmos DB, Table storage), data lake |
| Unstructured | No predefined model | Photos, video, audio, PDFs, Word files, free text, emails | Blob storage, data lake |
Structured data
- Rows and columns. Each column has a data type (integer, decimal, text, date) and each row is one entity instance (a customer, an order).
- Tables relate to each other with keys. The schema is defined before data is written (schema on write).
- Easy to query with SQL and to enforce rules on (not null, unique, ranges).
Semi-structured data
- Self-describing: field names travel with the values. Two records in the same collection can have different fields, and values can be nested.
- JSON is the most common. XML and YAML are others. Key-value stores hold a unique key and an opaque or structured value. Graph data stores nodes (entities) and edges (relationships).
- Often read with schema on read: structure is applied when you query, not when you store.
Unstructured data
- Stored as files (blobs). The content has meaning but the store cannot see inside without extra processing (image recognition, text analytics, search indexing).
- Largest share of data volume in most organisations.
Trap: a CSV is structured (tabular), but a pile of JSON documents with varying fields is semi-structured. Questions often use “no fixed schema, nested” to cue semi-structured.
Common data file formats
| Format | Kind | Key facts |
|---|---|---|
| Delimited text (CSV, TSV) | Row-based text | Human readable, easy, no data types, no nesting, larger than binary formats |
| JSON | Text, hierarchical | Objects with name/value pairs and arrays; web APIs and document databases |
| XML | Text, hierarchical | Tags and attributes; older systems, config, SOAP |
| BLOB | Raw binary | Images, video, audio, any file; stored as an opaque object |
| Parquet | Binary, columnar | Compressed, stores column values together, schema embedded; excellent for analytics where queries read few columns |
| ORC | Binary, columnar | Similar to Parquet; common in Hive/Hadoop workloads |
| Avro | Binary, row-based | Schema stored in JSON header; good for streaming and write-heavy exchange (Event Hubs, Kafka) |
| Delta Lake | Parquet plus a transaction log | Adds ACID transactions, versioning (time travel) and updates to files in a lake; the default table format in Fabric and Databricks |
Why columnar helps analytics: a query that sums one column reads only that column, and similar values compress well. Row-based formats suit reading or writing whole records.
Data stores
Databases
A database is an organised collection of data with software (a DBMS) that manages storage, access, security and concurrency.
| Kind | Model | Use when |
|---|---|---|
| Relational | Tables with rows, columns, keys, SQL | Transactions, consistent schema, joins |
| Key-value | Unique key to a value | Fast lookup by key: sessions, shopping carts, profiles |
| Document | JSON-like documents in collections | Flexible records, product catalogues, content |
| Column-family | Rows with dynamic column groups | Massive write-heavy, wide data (Cassandra model) |
| Graph | Nodes and edges | Relationships are the question: social networks, fraud rings, recommendations |
| Time series | Timestamped measurements | IoT and telemetry |
Non-relational databases are often called NoSQL: they trade rigid schema and joins for flexibility and horizontal scale.
File stores and object stores
- Files on a share, accessed by path (Azure Files, SMB/NFS).
- Object (blob) storage holds objects in flat containers with metadata, accessed over HTTP (Azure Blob Storage).
- A data lake is a store of raw data in many formats at any scale, usually on object storage (Azure Data Lake Storage Gen2 is Blob storage with a hierarchical namespace). A data warehouse holds cleaned, modelled, relational data optimised for analytics. A lakehouse combines lake storage with warehouse-style tables and SQL (Fabric Lakehouse, Databricks).
Azure datastores for common use cases
| Need | Azure service |
|---|---|
| Relational OLTP, fully managed | Azure SQL Database |
| Relational with near-full SQL Server compatibility, lift and shift | Azure SQL Managed Instance |
| Relational with OS-level control | SQL Server on Azure Virtual Machines |
| Open-source relational | Azure Database for PostgreSQL, Azure Database for MySQL |
| Globally distributed NoSQL (document, key-value, graph, column-family) | Azure Cosmos DB |
| Unstructured objects, images, backups, data lake | Azure Blob Storage, Azure Data Lake Storage Gen2 |
| Lift-and-shift file shares (SMB/NFS) | Azure Files |
| Simple key/attribute NoSQL at low cost | Azure Table storage |
| Analytics over big data | Microsoft Fabric, Azure Synapse Analytics, Azure Databricks |
| Real-time event ingest | Azure Event Hubs |
Transactional workloads (OLTP)
OLTP (online transaction processing) records the day-to-day events of a business: sales, bookings, payments, stock changes.
- Many users, many short reads and writes, each touching few rows.
- Needs low latency and high concurrency.
- Data is normalised to avoid duplication and keep updates consistent.
- Must guarantee ACID properties:
| Property | Meaning |
|---|---|
| Atomicity | A transaction is all or nothing; if any step fails, everything rolls back |
| Consistency | A transaction moves the database from one valid state to another, keeping rules (constraints) true |
| Isolation | Concurrent transactions do not see each other’s half-finished work |
| Durability | Once committed, the change survives a crash |
Classic example: transferring money. Debit and credit must both happen or neither. Typical stores: Azure SQL Database, Azure Database for PostgreSQL/MySQL, Cosmos DB (for non-relational OLTP).
Analytical workloads (OLAP)
Analytical workloads turn data into insight: trends, forecasts, reports and dashboards.
- Fewer, long-running queries that scan and aggregate many rows.
- Read-heavy, loaded in batches or streams rather than updated row by row.
- Data is modelled for reading, often denormalised in a star schema: a central fact table (measurable events such as sales amounts) linked to dimension tables (who, what, when, where).
- Often built on a data warehouse, lakehouse or OLAP cube / semantic model that pre-aggregates measures.
OLTP versus OLAP
| OLTP | OLAP | |
|---|---|---|
| Purpose | Run the business | Analyse the business |
| Operations | Insert, update, delete, small lookups | Large scans, aggregations |
| Query size | Few rows | Millions of rows |
| Schema | Normalised | Star or snowflake, denormalised |
| Data | Current | Historical, integrated from many sources |
| Users | Applications, many concurrent | Analysts, BI tools, fewer |
| Azure examples | Azure SQL Database | Fabric Warehouse, Synapse dedicated SQL pool |
The analytical data flow
- Ingest from operational databases, files, APIs, streams.
- Transform (clean, join, aggregate), using ETL (transform before loading) or ELT (load first, transform in the analytical store).
- Store in a lake, lakehouse or warehouse.
- Model into tables and measures.
- Visualise and report (Power BI).
Trap: do not run heavy reporting on the live OLTP database; that is exactly why analytical stores exist.
Roles and responsibilities
| Role | Focus | Typical tasks | Tools |
|---|---|---|---|
| Database administrator (DBA) | Operational health of databases | Install/configure, backup and restore, security and permissions, monitor and tune performance, patching, high availability and disaster recovery, manage users | SSMS, Azure portal, Azure Data Studio / VS Code MSSQL extension, T-SQL |
| Data engineer | Pipelines and platforms | Design and build data pipelines, ingest and transform data, build and manage data lakes and warehouses, ensure quality and scale, apply security and governance to data | Azure Data Factory, Fabric Data Factory, Synapse, Databricks, Spark, SQL, Python |
| Data analyst | Insight from data | Explore and clean data, build data models, create reports and dashboards, find trends, communicate findings to the business | Power BI, SQL, Excel |
Quick separators: DBA = keep databases running and secure; data engineer = move and shape data; analyst = explain data. A question about “restoring a database” or “granting access to tables” is a DBA task; “building a pipeline that loads sales data nightly” is data engineering; “creating a dashboard of monthly revenue” is analysis. A data scientist builds predictive models and sits beyond the DP-900 scope.
Exam checklist
- Classify an example as structured, semi-structured or unstructured; know JSON, XML and key-value are semi-structured, images and video unstructured.
- Know CSV, JSON, XML, Parquet, ORC, Avro, Delta Lake and BLOB, and which are columnar (Parquet, ORC) and which row-based (Avro, CSV).
- Know the database kinds (relational, key-value, document, column-family, graph) and a use case for each.
- Match the Azure datastore to the need (SQL, Cosmos DB, Blob, Files, Table, Fabric).
- Recite the four ACID properties and tie them to OLTP.
- Contrast OLTP and OLAP on query size, schema and purpose; know fact and dimension tables.
- Distinguish data lake, data warehouse and lakehouse, and ETL from ELT.
- Assign tasks to DBA, data engineer and data analyst correctly.