DP-900 Cheat Sheet: Azure Data Services at a Glance

DP-900 is a service-selection exam: nearly every question reduces to 'scenario → correct service or concept.' This cheat sheet organizes everything into decision tables.

Data types

• Structured — fixed schema, rows/columns (SQL tables)
• Semi-structured — organizational markers, no rigid schema (JSON, XML)
• Unstructured — no structure (images, video, free text)
• Schema-on-write = enforce structure on ingest (warehouse); schema-on-read = interpret at query time (lake)

Workloads

• OLTP — many small transactions, low latency, current data (order entry) → operational databases
• OLAP — large scans/aggregations over history (reporting) → data warehouse
• Batch — accumulated data on a schedule (minutes–hours latency)
• Streaming — continuous processing as data arrives (sub-second to seconds)
• ETL = transform then load; ELT = load raw first, transform in the target (modern lakehouse pattern)

Relational services — pick by requirement

• Azure SQL Database — PaaS, single database, managed everything, no instance features
• Azure SQL Managed Instance — PaaS with ~full SQL Server compat (Agent, linked servers, CLR, cross-DB) — migration target
• SQL Server on Azure VMs — IaaS, full control (any version, OS agents) — you manage it
• Azure Database for PostgreSQL / MySQL — managed open-source engines
• Elastic pools — share DTU/vCore capacity across many databases with variable load

Relational concepts (quick)

• Primary key — uniquely identifies a row; foreign key — references a PK in another table
• Normalization — removes redundancy/update anomalies (OLTP); denormalization/star schema — fewer joins for analytics
• Index — speeds filtered lookups at the cost of write overhead
• View — saved query as a virtual table (abstraction + column/row restriction)
• SELECT reads; INSERT/UPDATE/DELETE write; CREATE/ALTER/DROP are DDL

Non-relational — pick by model

• Azure Cosmos DB — globally distributed multi-model NoSQL: NoSQL (JSON), MongoDB, Cassandra, Table, Gremlin APIs; tunable consistency; single-digit-ms latency
• Azure Table Storage — simple key-attribute store (PartitionKey + RowKey)
• Azure Blob Storage — object storage for files/media; tiers: Hot / Cool / Cold / Archive (archive = offline, hours to rehydrate)
• Azure Data Lake Storage Gen2 — Blob + hierarchical namespace (real directories, POSIX ACLs) — the analytics lake foundation
• Azure Files — managed SMB/NFS shares (file-server replacement)

Cosmos DB consistency levels (strong → weak)

• Strong — linearizable, always latest write (lowest availability during partitions)
• Bounded staleness — lag bounded by time or versions
• Session — consistent within a client session (default, the sweet spot)
• Consistent prefix — reads never out of order, but may lag
• Eventual — fastest/most available, no ordering guarantee

Analytics stack — pick by role

• Azure Synapse Analytics — integrated analytics workspace: dedicated SQL pools (warehouse), serverless SQL, Spark, pipelines
• Microsoft Fabric — unified SaaS analytics: OneLake, lakehouse, warehouse, real-time (KQL), Power BI — the newer consolidated platform
• Azure Databricks — managed Spark notebooks for big-data engineering/ML
• Azure Data Factory — ETL/ELT orchestration: Copy activity (90+ connectors), data flows, triggers
• Azure Event Hubs — event ingestion front door (millions/sec, Kafka-compatible)
• Azure Stream Analytics — SQL queries over streams; windowing (tumbling/hopping/session) bounds calculations
• Azure IoT Hub — Event Hubs + device management for IoT scenarios
• Power BI — dashboards/reports; the visualization layer at the pipeline's end
• Azure HDInsight — managed open-source clusters (Hadoop/Spark/HBase) — legacy-heavy option

Analytics concepts

• Data warehouse — curated, structured, BI-optimized (schema-on-write, star schemas, MPP)
• Data lake — raw data at scale in native format (schema-on-read)
• Lakehouse — lake storage + warehouse semantics; medallion layers: bronze (raw) → silver (cleaned) → gold (business aggregates)
• Fact table = measurable events (sales, clicks); dimension table = descriptive context (customer, product, date)
• Star schema = fact table surrounded by denormalized dimensions — the analytics standard