What is data?

Data is any collection of facts a business stores and processes. It can be structured (fits neatly into rows and columns), semi-structured (has some organisation but no fixed schema, like JSON or XML) or unstructured (no defined format, like images, video or free text).

Structured data

This lives in tables with a fixed schema - every row has the same columns, and each column has a set data type. Relational databases (SQL Server, Azure SQL Database) are the classic home for this. Good for data with clear relationships, enforced by primary and foreign keys.

Semi-structured data

Think JSON, XML, key-value pairs, or graph data. It has tags or markers that separate elements, but no rigid table shape. Azure Cosmos DB is the go-to service here, along with Table Storage. Common mistake: assuming semi-structured means unorganised - it still has internal structure, just a flexible one.

Unstructured data

Images, video, audio, PDFs, free-text documents. Stored as blobs (Azure Blob Storage) or files. No inherent schema, so you need extra tools (like Azure AI services) to extract meaning from it.

Data workloads

DP-900 splits workloads into three types.

• Transactional (OLTP) - fast, frequent, small read/write operations, e.g. an online shop taking orders. Needs low latency and high concurrency.
• Analytical (OLAP) - large-scale queries over historical data for reporting and trends, e.g. a data warehouse. Reads are large, writes are batch-loaded.
• Batch vs streaming - batch processes data in scheduled chunks (e.g. nightly ETL); streaming processes data continuously in near real time (e.g. IoT telemetry via Azure Stream Analytics or Event Hubs).

Data roles

Know the three core roles Microsoft tests.

• Database Administrator (DBA) - designs, implements and maintains databases; manages security, backups and performance.
• Data Engineer - builds and manages the data pipelines that move and transform data (ETL/ELT).
• Data Analyst - explores and visualises data to enable business decisions, often using Power BI.

Common mistakes

• Confusing OLTP with OLAP - OLTP is operational (day-to-day transactions), OLAP is analytical (reporting on history).
• Thinking semi-structured data has zero structure - it does, just not a fixed relational schema.
• Mixing up the DBA and Data Engineer roles - DBA owns the database itself, Data Engineer owns the pipeline.
• Forgetting that batch processing is not the same as real-time streaming - exam questions often give a scenario and ask you to pick the right processing type.

Relational data on Azure

Relational data lives in tables made of rows and columns, with a fixed schema defined before you load data. Tables link together using primary keys (unique row identifier) and foreign keys (a reference to another table's primary key), and you query them with SQL. Azure gives you several ways to run relational workloads, each trading off control against management effort.

The main services

• Azure SQL Database: a fully managed PaaS database, one database (or elastic pool of databases) per app, built on the SQL Server engine, always patched to the latest version.
• Azure SQL Managed Instance: near-100% SQL Server engine compatibility, good for lift-and-shift migrations that need cross-database queries, SQL Agent, or linked servers.
• SQL Server on Azure VMs: full IaaS control, you manage the OS and SQL Server yourself; pick this when you need OS-level access or unsupported features.
• Azure Database for PostgreSQL and Azure Database for MySQL: managed open-source relational engines, same PaaS convenience as SQL Database but for open-source workloads.
• Azure SQL Edge: a small-footprint SQL engine for IoT and edge devices, supports time-series and streaming data close to the source.

Deployment and purchasing models

• DTU (Database Transaction Unit) model: bundles compute, memory and storage into one blended metric, simplest for predictable small workloads.
• vCore model: lets you scale compute and storage independently, and lets you use Azure Hybrid Benefit to reuse existing SQL Server licences for a discount.
• Serverless compute tier: auto-scales and can auto-pause during inactivity, billed per second of actual use, ideal for spiky or intermittent workloads.
• Elastic pools: share resources across many databases with unpredictable usage patterns, cheaper than provisioning each database for peak load.

Resilience and access

• Built-in automated backups with point-in-time restore, retained for 7-35 days depending on tier, extendable with long-term retention.
• Active geo-replication and auto-failover groups give readable secondary copies in other regions for disaster recovery.
• Access is via SQL authentication or Microsoft Entra ID authentication; connections are secured with firewall rules and private endpoints.

Common mistakes to avoid

• Don't confuse Managed Instance (near-full SQL Server compatibility, instance-scoped features) with SQL Database (single/pooled database scope, some SQL Server features unsupported).
• Don't assume normalisation always wins - it reduces redundancy but can mean more joins and slower reads for reporting workloads.
• Remember elastic pools are for many databases with variable, non-simultaneous peaks, not for a single large database.
• Serverless is about auto-scaling and auto-pausing compute, not about removing the database server concept entirely.

What is non-relational data?

Non-relational (NoSQL) data does not fit neatly into rows and tables with a fixed schema. It covers key/value pairs, documents, graphs, columnar stores, and object/blob storage. Azure's main non-relational services are Azure Cosmos DB and Azure Storage (Blob, Table, File, Queue).

Azure Cosmos DB

Cosmos DB is Microsoft's globally distributed, multi-model NoSQL database. It supports several APIs so you can pick the data model that suits your app:

• NoSQL (Core) API - native document model, stores JSON
• MongoDB API - document model, compatible with MongoDB drivers
• Cassandra API - wide-column model
• Gremlin API - graph model (nodes and edges)
• Table API - key/value, upgrade path from Azure Table Storage

Cosmos DB guarantees single-digit millisecond latency at the 99th percentile, offers 99.999% read/write availability with multi-region writes, and gives you five tunable consistency levels: Strong, Bounded Staleness, Session (the default), Consistent Prefix, and Eventual - trading consistency for lower latency and higher throughput as you move down the list. Throughput is measured in Request Units (RU/s), which can be provisioned manually or set to autoscale, or you can use serverless mode for spiky/unpredictable workloads.

Azure Storage

Azure Storage is the go-to for unstructured and semi-structured data:

• Blob Storage - unstructured objects (images, video, backups, logs). Three access tiers: Hot (frequent access), Cool (infrequent, min 30-day storage), and Archive (rare access, min 180-day storage, hours to rehydrate)
• Table Storage - simple NoSQL key/value store using PartitionKey and RowKey, cheap and schema-less
• File Storage (Azure Files) - fully managed file shares accessible via SMB/NFS, good for lift-and-shift
• Queue Storage - simple messaging between application components, decoupling producers and consumers

Common exam mistakes

• Don't confuse Table Storage (Azure Storage) with Cosmos DB's Table API - the latter has better throughput/global distribution guarantees.
• Blob storage tiers are about access frequency and cost, not about structure - blobs are always unstructured.
• Cosmos DB is multi-model but each container uses ONE API - you choose it at container creation and cannot mix APIs freely.
• Remember RU/s is the Cosmos DB currency for throughput, not raw CPU or storage size.
• Session consistency (not Strong) is the DEFAULT consistency level in Cosmos DB.

What data warehousing and analytics means in Azure

A data warehouse stores large volumes of historical, structured data optimised for reading and reporting, not for fast single-record updates. Azure's flagship warehouse service is Azure Synapse Analytics, which combines big data and data warehousing into one workspace.

Key services to know

• Azure Synapse Analytics: unifies SQL pools (dedicated and serverless), Spark pools, and pipelines in one workspace with Synapse Studio as the single UI.
• Dedicated SQL pool: provisioned, pay-for-what-you-allocate compute measured in Data Warehouse Units (DWUs); best for predictable, heavy workloads.
• Serverless SQL pool: pay-per-query, no infrastructure to manage; ideal for ad-hoc querying of files in a data lake.
• Azure Data Factory: orchestrates ETL/ELT pipelines, moving and transforming data from many sources into a warehouse or lake.
• Azure Databricks: Apache Spark-based platform for large-scale data engineering and machine learning.
• Azure Analysis Services: enterprise-grade tabular semantic models for fast analytical queries.
• Power BI: visualisation and reporting layer that sits on top of these data stores, built around datasets, reports, dashboards and workspaces.

ETL vs ELT

• ETL (Extract, Transform, Load): data is transformed before loading into the warehouse, common with traditional on-premises tools.
• ELT (Extract, Load, Transform): raw data is loaded first, then transformed inside the target system — the more common pattern with cloud-scale compute like Synapse and Databricks, because it exploits cheap distributed compute for transformation.

Data warehouse vs data lake vs lakehouse

• Data warehouse: structured, schema-on-write, optimised for known business queries.
• Data lake: stores raw structured, semi-structured and unstructured data cheaply, schema-on-read (Azure Data Lake Storage Gen2).
• Lakehouse: combines lake storage with warehouse-like management and performance, the pattern Synapse and Databricks both support.

Batch vs streaming analytics

• Batch processing: large volumes processed on a schedule (e.g. nightly loads via Data Factory or Synapse pipelines).
• Stream processing: near real-time data processed as it arrives, using Azure Stream Analytics or Spark Structured Streaming in Databricks. Azure Stream Analytics uses a SQL-like query language and can output to Power BI for live dashboards.

Common mistakes

• Confusing Synapse dedicated SQL pool (provisioned, DWU-based) with serverless SQL pool (pay-per-TB-scanned, no provisioning).
• Thinking a data lake requires a fixed schema — it doesn't, that's the whole point of schema-on-read.
• Assuming Power BI is only a visual tool — it also has its own data modelling and transformation layer (Power Query, DAX).
• Forgetting Stream Analytics is the exam's named service for real-time streaming, not Data Factory (which is batch/orchestration-focused).

Power BI and data visualisation

Power BI is Microsoft's business intelligence tool for turning raw data into interactive reports and dashboards. In the DP-900 exam it sits under the 'data analytics workloads' area, and you need to know its building blocks and how they fit together, not how to build reports yourself.

The three core building blocks

• Power BI Desktop - the free Windows app used to connect to data, model it, and build report pages. This is where the authoring happens.
• Power BI Service (app.powerbi.com) - the cloud platform used to publish, share, and collaborate on reports. Reports get uploaded here from Desktop.
• Power BI Mobile - apps for phones and tablets so people can view dashboards and reports on the move.

Key terms - learn the difference

• Dataset - the connected and modelled data a report is built from.
• Report - one or more pages of visuals built from a dataset, fully interactive (filter, drill down, cross-highlight).
• Dashboard - a single canvas of pinned visuals ('tiles') pulled from one or more reports, giving an at-a-glance summary. Dashboards are Service-only - you cannot create a dashboard in Desktop.
• Tile - a single pinned visual on a dashboard.
• Workspace - a container in the Service where related dashboards, reports, and datasets are grouped and shared with a team.

The Power BI workflow

The standard flow is: connect to data sources in Desktop, transform and model the data with Power Query and DAX, build report visuals, then publish (upload) to the Service, where colleagues view it via browser or pin visuals to a dashboard.

Common mistakes to avoid

• Mixing up report and dashboard - a report is multi-page and interactive; a dashboard is a single-page summary of pinned tiles and lives only in the Service.
• Forgetting dashboards cannot be authored in Desktop - Desktop only makes reports.
• Thinking Power BI can only use Microsoft data sources - it connects to a huge range including Excel, SQL Server, Azure SQL, Azure Synapse Analytics, and many third-party and cloud sources.
• Confusing Power BI with Azure Synapse or Data Factory - those are for ingesting/transforming/warehousing data; Power BI is the visualisation and reporting layer on top.
• Assuming a licence is always needed - Power BI Desktop is free; sharing and collaboration features in the Service typically need a Pro or Premium licence.

Why it matters for the exam

Expect scenario questions asking which Power BI component to use for a given need (e.g. 'summary view combining multiple reports' = dashboard) and questions distinguishing Power BI's role from other Azure analytics services like Synapse Analytics or Data Factory.

Governance & security in Azure data services

Data governance and security is about controlling who can see what, protecting data at rest and in transit, and proving compliance. DP-900 tests whether you know which Azure feature solves which problem.

Authentication vs authorisation

• Authentication (AuthN) proves who you are - Azure Active Directory (Microsoft Entra ID) is the main identity provider for Azure data services.
• Authorisation (AuthZ) decides what you can do once verified - handled through Azure role-based access control (RBAC).
• SQL-based services also support SQL authentication (username and password) as an alternative to Entra ID.

Azure RBAC

• RBAC assigns roles (like Reader, Contributor, Owner, or service-specific roles such as SQL DB Contributor) to users, groups, or service principals at a scope: management group, subscription, resource group, or resource.
• Roles are additive - there is no explicit deny by default, though Azure now supports deny assignments for exceptions.
• Principle of least privilege: always grant the minimum role needed.

Encryption

• Encryption at rest protects data on disk - Azure Storage and Azure SQL Database use Transparent Data Encryption (TDE) by default, with AES 256-bit encryption.
• Encryption in transit protects data moving over networks - enforced using TLS (Transport Layer Security), typically TLS 1.2 minimum.
• Always Encrypted (SQL feature) keeps sensitive columns encrypted even from database administrators, using client-side keys.
• Transparent Data Encryption keys can be Microsoft-managed or customer-managed (stored in Azure Key Vault) for extra control.

Network security

• Firewalls restrict access by IP address range - Azure SQL Database has a server-level and database-level firewall.
• Virtual network (VNet) service endpoints and Private Link let resources connect over Microsoft's private backbone rather than the public internet, reducing exposure.
• Private Link assigns a private IP address from your VNet directly to the PaaS resource.

Auditing and monitoring

• Azure SQL Auditing tracks database events (logins, queries, schema changes) and writes them to a storage account, Log Analytics workspace, or Event Hub.
• Microsoft Defender for Cloud (formerly Advanced Threat Protection) detects anomalous activity like SQL injection attempts or unusual access patterns.
• Dynamic Data Masking hides sensitive data (like full credit card numbers) from non-privileged users in query results without changing the stored data - it is not a security boundary on its own.

Common mistakes

• Confusing authentication (identity) with authorisation (permissions) - these are two separate steps.
• Thinking Dynamic Data Masking encrypts data - it does not; it only obscures display output.
• Assuming TDE protects data in transit - it only protects data at rest.
• Forgetting that RBAC in Azure is scope-based and inherits downward from higher scopes.
• Mixing up Always Encrypted (column-level, client-side keys) with TDE (whole database, transparent to apps).</br>