Azure DP-900 cheat sheet

Structured data fits a fixed schema of tables, rows, and columns; semi-structured data (JSON, XML) is self-describing through tags or keys; unstructured data (images, audio, free text) has no predefined schema.

OLTP workloads run many small, concurrent read/write transactions and require ACID guarantees; OLAP workloads run large aggregate reads over historical data and favor scan throughput.

ACID = Atomicity, Consistency, Isolation, Durability. Many NoSQL stores instead offer BASE (Basically Available, Soft state, Eventual consistency) as a scalability trade-off.

Normalization splits data into related tables to remove redundancy and update anomalies; denormalization deliberately duplicates data to reduce joins and speed reads.

A primary key uniquely identifies each row in a table; a foreign key references a primary key in another table; a composite key combines two or more columns.

Batch processing handles bounded data sets on a schedule; stream processing handles unbounded event flows continuously with low latency.

Common file formats: CSV is delimited text; JSON is nested and self-describing; Parquet and ORC are columnar and compressed for analytics; Avro is row-based with an embedded schema.

Data is commonly characterized by volume, velocity, variety, veracity, and value.

Role split: a DBA manages availability, security, and backup; a data engineer builds pipelines and integration; a data analyst builds reports; a data scientist builds models.

Azure SQL Database is PaaS (single database or elastic pool) and hides the OS and engine configuration; Azure SQL Managed Instance is PaaS with a near-complete SQL Server surface area inside a virtual network; SQL Server on Azure VMs is IaaS with full OS and engine control.

Elastic pools share eDTUs or vCores across databases whose usage peaks at different times; a fixed single-database size suits steady, predictable load.

The DTU model bundles compute, memory, and I/O into preset tiers; the vCore model selects compute and storage separately and qualifies for Azure Hybrid Benefit.

Serverless compute auto-scales and can auto-pause, billing per second of compute used, which fits intermittent or unpredictable workloads.

Automated backups enable point-in-time restore (default retention 7 days, configurable up to 35) plus long-term retention; active geo-replication and auto-failover groups provide regional disaster recovery.

Built-in high availability uses a primary replica plus secondary replicas, so no extra HA configuration or licensing is required in the PaaS tiers.

Security controls: Transparent Data Encryption encrypts data at rest; Always Encrypted keeps keys client-side; dynamic data masking hides values in results; row-level security filters rows; Microsoft Entra ID provides authentication and managed identities.

Access is gated by server-level and database-level firewall rules, virtual network service endpoints, and private endpoints.

Azure Database for PostgreSQL, MySQL, and MariaDB are PaaS alternatives for open-source engines; Azure Database Migration Service moves on-premises databases to these targets.

NoSQL stores typically scale horizontally by partitioning, are schema-flexible, and often trade strict ACID guarantees for availability and low latency.

Four common NoSQL shapes: key-value (Table Storage, Azure Cache for Redis), document (Cosmos DB for NoSQL or MongoDB), graph (Gremlin), column-family (Cassandra).

Blob Storage tiers trade storage cost against access cost: Hot, Cool (30-day minimum), Cold (90-day minimum), Archive (offline, requires rehydration). Blob types are block, append, and page.

Azure Data Lake Storage Gen2 is Blob Storage with a hierarchical namespace, enabling directory- and file-level POSIX ACLs and efficient access by analytics engines.

Table Storage entities require a PartitionKey and RowKey; entities sharing a partition are stored together and queried efficiently, so a high-cardinality key spreads load.

Cosmos DB APIs include NoSQL (SQL syntax), MongoDB, Cassandra, Gremlin (graph), and Table; throughput is provisioned as request units per second (RU/s) or autoscale max RU/s.

Cosmos DB partition key choice determines scalability and hot-partition risk; the key should have high cardinality and appear in most query filters.

Cosmos DB consistency, strongest to weakest: strong, bounded staleness, session, consistent prefix, eventual. Weaker levels give lower latency and higher availability.

Azure Files provides managed SMB and NFS shares; Azure Cache for Redis provides in-memory caching rather than durable primary storage.

A Synapse Analytics workspace can contain dedicated SQL pools (provisioned MPP data warehouse), a serverless SQL pool (T-SQL over lake files, billed per data processed), Spark pools, Data Explorer pools, and pipelines.

Azure Data Factory orchestrates ETL/ELT: pipelines contain activities, activities read and write datasets built on linked services, and integration runtimes (Azure, self-hosted, SSIS) supply the compute for movement.

Azure Databricks is a managed Apache Spark platform with collaborative notebooks and Delta Lake; Synapse Spark pools provide Spark inside the Synapse workspace instead.

Streaming path: Event Hubs or IoT Hub ingests events, Stream Analytics or Spark Structured Streaming applies SQL-like queries and windowing, and results sink to storage, a database, or Power BI.

Medallion lakehouse layering: bronze holds raw ingested data, silver holds cleansed and conformed data, gold holds curated aggregates consumed by reporting.

Dimensional modeling: fact tables hold measures plus foreign keys, dimension tables hold descriptive attributes; a star schema needs one join hop while a snowflake normalizes dimensions.

Power BI is the visualization layer: it connects to semantic models, defines measures in DAX, and refreshes on a schedule; it is not the storage engine.

Microsoft Fabric is a SaaS analytics platform that unifies OneLake storage, Data Factory, Synapse data engineering and data warehouse, real-time analytics, and Power BI under one capacity.

Azure HDInsight provides managed open-source clusters (Hadoop, Spark, Hive, Kafka) when specific framework versions or control are required.