DP-900: Core Data Concepts

Course: Microsoft Azure Data Fundamentals (DP-900) – Describe Core Data Concepts Certification: DP-900 Azure Data Fundamentals

Table of Contents

1. The Three Data Roles
2. Data Types and Formats
   • Structured Data
   • Semi-structured Data
   • Unstructured Data
3. Common File Formats
   • Standard Formats
   • Optimized Formats
4. Evolution of Data Warehouses
5. Azure Data Services
6. Workload Types
   • OLTP vs OLAP
   • Big Data
7. Summary and Key Points

1. The Three Data Roles

Three main roles in a data ecosystem:

Key differences:

DBA           → "Ensure the database is available and secure"
Data Engineer → "Build pipelines to move/transform data"
Data Analyst  → "Explore data to find business insights"

2. Data Types and Formats

2.1 Structured Data

Structured data = schema defined upfront (schema-on-write).

Characteristics:

• Organized in rows and columns (tables)
• Explicit data types
• Relationships between tables (primary/foreign keys)

Azure examples:

• Azure SQL Database (PaaS)
• SQL Server on VM (IaaS)
• Azure SQL Managed Instance
• Azure Database for PostgreSQL / MySQL / MariaDB

SQL Example:

CREATE TABLE Employees (
    EmployeeId INT PRIMARY KEY,
    FirstName  VARCHAR(50) NOT NULL,
    LastName   VARCHAR(50) NOT NULL,
    DeptId     INT FOREIGN KEY REFERENCES Departments(DeptId)
);

2.2 Semi-structured Data

Semi-structured data = flexible structure (schema-on-read).

Common formats:

JSON Example:

{
  "employeeId": 1001,
  "firstName": "John",
  "lastName": "Doe",
  "contact": {
    "email": "john.doe@company.com",
    "phone": "206-555-7890"
  },
  "skills": ["C#", "Azure", "Blazor"]
}

XML Example:

<Employee id="1001">
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    <Skills>
        <Skill>C#</Skill>
        <Skill>Azure</Skill>
    </Skills>
</Employee>

YAML Example:

employee:
  id: 1001
  firstName: John
  lastName: Doe
  skills:
    - C#
    - Azure
    - Blazor

Azure Cosmos DB — NoSQL database for semi-structured data:

• Document model (JSON)
• Key-value model
• Wide-column model
• Graph model

2.3 Unstructured Data

Unstructured data = no defined schema.

Examples:

• Images, videos, audio
• PDFs, presentations
• Emails, messages
• Binary files

Azure Blob Storage = unstructured data storage:

• Containers → Blobs
• Tiers: Hot, Cool, Cold, Archive

3. Common File Formats

3.1 Standard Formats

CSV (Comma-Separated Values):

EmployeeId,FirstName,LastName,Department
1,John,Doe,IT
2,Jane,Smith,HR

Advantages: Universal, human-readable Disadvantages: No types, no compression, no schema

3.2 Optimized Formats

Formats designed for analytical performance:

Avro (row-based):

JSON Header (schema):
{
  "type": "record",
  "name": "Employee",
  "fields": [
    {"name": "id", "type": "int"},
    {"name": "firstName", "type": "string"}
  ]
}
→ Compressed binary data

Avro advantages:

• Compact thanks to JSON schema + binary data
• Excellent compression
• Ideal for Kafka, streaming

ORC (Optimized Row Columnar):

Stripe 1:
  - Column stats (min, max, sum for each column)
  - Compressed data by column
Stripe 2:
  ...
Footer:
  - Index per stripe

ORC advantages:

• Statistics per stripe → skip entire blocks
• Excellent for Hive and Spark
• Very efficient compression

Parquet (columnar):

Row Group 1:
  Column "EmployeeId": [1, 2, 3, ...]  (compressed separately)
  Column "FirstName":  ["John", "Jane", ...]
  Column "Department": ["IT", "HR", ...]
  Column metadata (min, max, count)
Row Group 2:
  ...
File Footer:
  - Schema
  - Number of rows
  - Column encoding

Parquet advantages:

• Selective reading (only the necessary columns)
• Excellent compression per column (similar values together)
• De facto standard for Azure Synapse, Data Lake
• Supports nested data (JSON-like in Parquet)

When to use which format:

Data ingestion (streaming, Kafka)  → Avro
Hive queries, batch processing     → ORC
Azure Synapse, Power BI, Spark     → Parquet

4. Evolution of Data Warehouses

Historical evolution of data architectures:

1990s: Star Schema (Data Warehouse)
    ↓
2000s: OLAP Cubes (Analysis Services)
    ↓
2010s: Data Lakes (raw storage)
    ↓
2015+: Modern Data Warehouse (Azure Synapse)
    ↓
2020+: Data Lakehouse (Delta Lake, Fabric)

Star Schema:

            Dim_Time
                |
Dim_Product — Fact_Sales — Dim_Customer
                |
           Dim_Region

• Fact table: measures (quantity sold, amount)
• Dimension tables: context (when, who, what, where)
• MDX: query language for OLAP cubes

Data Lakes:

All raw data → Data Lake (Azure Data Lake Gen2)
Advantage: "Store now, schema later"
Risk: "Data Swamp" if poorly managed

Modern Data Warehouse (Azure Synapse):

Sources → Ingest (ADF) → Data Lake → Transform (Spark/SQL) → Serve (SQL Pool) → Power BI

Data Lakehouse (Databricks Delta Lake + Microsoft Fabric):

• Combines Data Lake advantages (flexible storage) + DW (ACID, performance)
• Delta Lake: Parquet format + transaction log
• Microsoft Fabric: unified analytics platform

5. Azure Data Services

Azure services by category:

IaaS vs PaaS:

6. Workload Types

6.1 OLTP vs OLAP

OLTP (Online Transaction Processing):

Characteristics:
- Short, fast, precise transactions
- Strong consistency (ACID)
- Many writes and reads
- Current data

Examples:
- Reservation system (airline tickets)
- Banking transactions (ATM)
- e-Commerce (orders)

Azure: Azure SQL Database, Azure SQL MI, Cosmos DB

OLAP (Online Analytical Processing):

Characteristics:
- Complex queries on large volumes
- Mostly reads
- Eventual consistency acceptable
- Historical data

Examples:
- Annual sales reports
- Trend analysis
- BI dashboards

Azure: Azure Synapse Analytics, Azure Databricks

OLTP vs OLAP Comparison:

HTAP (Hybrid Transactional/Analytical Processing):

• Combines OLTP and OLAP in the same system
• Example: Azure Cosmos DB Analytical Store (HTAP via Synapse Link)

6.2 Big Data

The 3 Vs of Big Data:

Big Data Processing:

Batch Processing:
  - Large amounts of collected data → processed periodically
  - Example: daily sales report
  - Azure: Azure Databricks (Spark), Azure Synapse

Streaming Processing:
  - Data processed as it arrives
  - Example: real-time fraud detection
  - Azure: Azure Stream Analytics, Event Hubs, Databricks Streaming

Lambda Architecture:

Source → Hot Path (streaming, fast results)  → Serving Layer
       → Cold Path (batch, precise results)   →

Kappa Architecture:

Source → Stream (single path, simplified) → Serving Layer

7. Summary and Key Points

Azure service hierarchy by type:

Transactional (OLTP):
  Relational: Azure SQL DB, SQL MI, PostgreSQL, MySQL
  NoSQL:      Cosmos DB (key-value, document, graph, column)

Analytical (OLAP):
  DW:         Azure Synapse Analytics
  Big Data:   Azure Databricks
  Integration: Azure Data Factory

Storage:
  Unstructured: Azure Blob Storage, Data Lake Gen2
  Semi-structured: Azure Table Storage, Cosmos DB

Advanced Sections – Deep Dive DP-900

Section A – Relational Data and SQL: Deep Dive

A.1 Objects in a Relational Database

A relational database contains several types of objects:

mindmap
  root((Relational\nDatabase))
    Tables
      Heap (unordered)
      Clustered Index
    Indexes
      Clustered Index
      Non-Clustered Index
    Views
      Standard Views
      Indexed Views
    Stored Procedures
    Functions
    Triggers
    Constraints
      Primary Key
      Foreign Key
      Unique
      Check
      Not Null

A.2 Data Normalization

Normalization decomposes data into tables to avoid redundancy and improve integrity.

Example: denormalized data (problem):

-- Denormalized table with redundancy
Orders:
  OrderID | CustomerName  | CustomerEmail      | ProductName | ProductPrice | Qty
  1001    | Alice Johnson | alice@email.com    | Laptop      | 999.99       | 2
  1002    | Alice Johnson | alice@email.com    | Mouse       | 29.99        | 1
  -- Problem: if Alice changes her email, we must update 2 rows!

Normalized data (solution):

-- Customers table (1NF, 2NF, 3NF)
Customers:
  CustomerID | Name          | Email
  1          | Alice Johnson | alice@email.com

-- Products table
Products:
  ProductID | Name   | Price
  101       | Laptop | 999.99
  102       | Mouse  | 29.99

-- Orders table (join of both)
Orders:
  OrderID | CustomerID | ProductID | Qty
  1001    | 1          | 101       | 2
  1002    | 1          | 102       | 1

Normal forms:

A.3 Primary and Foreign Keys

-- Primary key = unique identifier of a row
CREATE TABLE Customers (
    CustomerID   INT PRIMARY KEY IDENTITY(1,1),  -- auto-increment
    Name         NVARCHAR(100) NOT NULL,
    Email        NVARCHAR(255) UNIQUE NOT NULL
);

-- Foreign key = reference to another table
CREATE TABLE Orders (
    OrderID      INT PRIMARY KEY IDENTITY(1,1),
    CustomerID   INT NOT NULL,
    OrderDate    DATE NOT NULL,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    -- Prevents inserting an OrderID with a non-existent CustomerID
);

Index types:

-- Clustered Index: physically sorts data (only 1 per table)
-- Automatically created on the Primary Key

-- Non-Clustered Index: index separate from data (up to 999 per table)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
    ON Orders (CustomerID);
-- Improves queries WHERE CustomerID = X

-- Composite index
CREATE INDEX IX_Orders_Customer_Date
    ON Orders (CustomerID, OrderDate);

A.4 Views

A view is a saved SQL query that can be used like a virtual table.

-- Create a view
CREATE VIEW vw_CustomerOrders AS
SELECT
    c.Name AS CustomerName,
    c.Email,
    o.OrderID,
    o.OrderDate,
    p.Name AS ProductName,
    p.Price,
    oi.Qty,
    (p.Price * oi.Qty) AS LineTotal
FROM Customers c
JOIN Orders o ON c.CustomerID = o.CustomerID
JOIN OrderItems oi ON o.OrderID = oi.OrderID
JOIN Products p ON oi.ProductID = p.ProductID;

-- Use the view like a table
SELECT CustomerName, SUM(LineTotal) AS TotalSpent
FROM vw_CustomerOrders
GROUP BY CustomerName
ORDER BY TotalSpent DESC;

View advantages:

• Simplify complex queries
• Security: expose only certain columns
• Abstraction: users don’t see the internal structure

A.5 The 7 Essential SQL Commands

-- 1. SELECT: read data
SELECT Name, Email FROM Customers WHERE CustomerID = 1;

-- 2. INSERT: add data
INSERT INTO Customers (Name, Email) VALUES ('Mary Martin', 'mary@email.com');

-- 3. UPDATE: modify data
UPDATE Customers SET Email = 'mary.martin@email.com' WHERE CustomerID = 5;

-- 4. DELETE: remove data
DELETE FROM Orders WHERE OrderDate < '2023-01-01';

-- 5. CREATE: create an object
CREATE TABLE Categories (CategoryID INT PRIMARY KEY, Name NVARCHAR(50));

-- 6. ALTER: modify an object
ALTER TABLE Products ADD CategoryID INT FOREIGN KEY REFERENCES Categories(CategoryID);

-- 7. DROP: remove an object
DROP TABLE TempData;

A.6 SQL JOINs

-- INNER JOIN: only rows with a match in both tables
SELECT c.Name, o.OrderID
FROM Customers c
INNER JOIN Orders o ON c.CustomerID = o.CustomerID;

-- LEFT JOIN: all rows from the left, even without a match
SELECT c.Name, o.OrderID
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;
-- Includes customers without orders (OrderID will be NULL)

-- RIGHT JOIN: all rows from the right
-- FULL OUTER JOIN: all rows from both tables

-- Difference between JOIN types:
-- INNER : ∩ (intersection)
-- LEFT  : A + (A ∩ B)
-- RIGHT : B + (A ∩ B)
-- FULL  : A ∪ B (union)

A.7 DDL vs DML

-- ACID transaction with TCL
BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 500 WHERE AccountID = 101;
UPDATE Accounts SET Balance = Balance + 500 WHERE AccountID = 102;

-- If everything went well:
COMMIT TRANSACTION;

-- On error:
-- ROLLBACK TRANSACTION;

A.8 SQL Tools for Azure

Section B – Azure Cosmos DB: Multi-Model NoSQL

B.1 Architecture and APIs

flowchart TD
    subgraph COSMOS["Azure Cosmos DB"]
        ENGINE["ARS Engine\n(Atom-Record-Sequence)"]
        ENGINE --> NOSQL["NoSQL API\n(JSON Documents)"]
        ENGINE --> MONGO["MongoDB API\n(Wire Protocol)"]
        ENGINE --> CASS["Cassandra API\n(CQL)"]
        ENGINE --> GREM["Gremlin API\n(Graphs)"]
        ENGINE --> TABLE["Table API\n(Key-Value OData)"]
    end

Choosing the right Cosmos DB API:

B.2 Partition Key: Fundamental Concept

The Partition Key is the most important architectural choice in Cosmos DB.

// Example: "Products" container with partition key "/category"
{
  "id": "prod-001",
  "category": "electronics",
  "name": "Laptop Pro 15",
  "price": 1299.99,
  "brand": "TechBrand"
}

{
  "id": "prod-002",
  "category": "clothing",
  "name": "T-Shirt XL",
  "price": 24.99,
  "size": "XL"
}

Good partition key:
  ✓ High cardinality (many unique values)
  ✓ Uniform query distribution
  ✓ Often used in WHERE filters

Bad partition key:
  ✗ /gender (only 2-3 values → hot partition)
  ✗ /country (if 90% is USA → imbalance)
  ✗ Constant (everything in one partition → no scalability)

B.3 Consistency Levels (5 levels)

flowchart LR
    S["Strong\n100% precise"] --> BS["Bounded\nStaleness"] --> SES["Session\n(default)"] --> CP["Consistent\nPrefix"] --> EV["Eventual\n100% fast"]

B.4 Request Units (RUs) – The “Currency” of Cosmos DB

1 RU = cost of reading a 1 KB document

Typical costs:
  Read 1 KB document    = 1 RU
  Write 1 KB document   = 5 RUs
  Delete                = 3 RUs
  Simple query          = 2-5 RUs
  Full scan query       = 10-100+ RUs (depending on volume)

Provisioning modes:
  Provisioned throughput : reserve X RU/s (constant billing)
  Autoscale              : 10%-100% of max (proportional billing)
  Serverless             : pay per RU consumed (dev/test)

B.5 Global Distribution

Multi-region Cosmos DB account:
  Primary region    : East US (read + write)
  Replica 1         : West Europe (read)
  Replica 2         : Southeast Asia (read)

  Multi-region writes enabled:
    → Write in any region
    → Conflict resolution (Last Write Wins or custom)
    → SLA: < 10ms read and write guaranteed (99th percentile)

Section C – Azure Blob Storage and Data Lake Gen2

C.1 Hierarchy and Blob Types

flowchart TD
    ACCOUNT["Storage Account\n(mystorageaccount)"]
    ACCOUNT --> C1["Container\n(images)"]
    ACCOUNT --> C2["Container\n(backups)"]
    ACCOUNT --> C3["Container\n(logs)"]
    C1 --> B1["Block Blob\nphoto.jpg"]
    C2 --> B2["Block Blob\ndb-backup.bak"]
    C3 --> B3["Append Blob\napp.log"]

Blob URL:

https://mystorageaccount.blob.core.windows.net/images/photo.jpg
         ↑ account name       ↑ service    ↑ container ↑ blob

Blob types:

C.2 Access Tiers

flowchart LR
    HOT["Hot\nHigh storage cost\nLow access cost"] -- "30+ days inactive" --> COOL["Cool\nBalanced cost\nModerate access"]
    COOL -- "90+ days inactive" --> COLD["Cold\nLower storage cost\nSlower access"]
    COLD -- "180+ days inactive" --> ARCHIVE["Archive\nVery cheap storage\nVery slow access (hours)"]

Lifecycle Management Policy:

// Automatically move blobs to Archive after 90 days
{
  "rules": [{
    "name": "ArchiveOldBlobs",
    "enabled": true,
    "type": "Lifecycle",
    "definition": {
      "filters": { "blobTypes": ["blockBlob"] },
      "actions": {
        "baseBlob": {
          "tierToCool": { "daysAfterModificationGreaterThan": 30 },
          "tierToArchive": { "daysAfterModificationGreaterThan": 90 },
          "delete": { "daysAfterModificationGreaterThan": 2555 }
        }
      }
    }
  }]
}

C.3 Azure Data Lake Storage Gen2 (ADLS Gen2)

ADLS Gen2 = Azure Blob Storage + analytical capabilities (hierarchical namespace).

Azure Blob Storage vs ADLS Gen2:

  Blob Storage:
    mystorageaccount.blob.core.windows.net
    → No true folder hierarchy (simulated via "/" in the name)
    → Limited analytics performance

  ADLS Gen2 (Hierarchical Namespace enabled):
    mystorageaccount.dfs.core.windows.net
    → True hierarchy: folders, subfolders
    → Atomic operations (rename, move)
    → 10x superior Spark/Analytics performance
    → POSIX ACLs (Unix permissions per folder/file)

Typical Data Lake structure:

Data Lake Gen2:
  ├── raw/                     ← raw ingested data
  │   ├── year=2025/
  │   │   ├── month=01/
  │   │   │   └── sales_20250101.parquet
  ├── curated/                 ← cleaned and transformed data
  │   ├── sales/
  │   │   └── sales_monthly.parquet
  └── serving/                 ← data ready for Power BI
      └── aggregates/

C.4 Secure Access to Blob Storage

Access methods:
  1. Storage Account Key → full access, protect with Key Vault
  2. SAS Token (Shared Access Signature) → temporary URL with limited permissions
  3. Azure AD + RBAC (Storage Blob Data Reader/Contributor) → recommended
  4. Managed Identity → access without credentials stored in code

SAS Token example:
  https://account.blob.core.windows.net/container/file.jpg?
    sv=2023-01-03                         ← API version
    &se=2025-01-31T00%3A00%3A00Z          ← expiration
    &sr=b                                 ← scope (b=blob)
    &sp=r                                 ← permissions (r=read)
    &sig=xxxx                             ← HMAC signature

Section D – Azure Table Storage

D.1 Key-Value Data Model

Azure Table Storage is a NoSQL service for storing structured entities as key-value pairs.

Azure Table Storage entity:
  PartitionKey + RowKey = unique composite Primary Key

  Entity:
    PartitionKey : "Seattle"    ← partition key (grouping)
    RowKey       : "user-1001"  ← unique identifier within partition
    Timestamp    : (auto)
    FirstName    : "Alice"
    LastName     : "Johnson"
    Email        : "alice@email.com"
    Age          : 32

Access URL:
  https://account.table.core.windows.net/Users(PartitionKey='Seattle',RowKey='user-1001')

Comparison with Cosmos DB Table API:

DP-900 tip: Cosmos DB Table API = transparent migration from Azure Table Storage with better performance.

Section E – Azure Synapse Analytics: The Complete Analytics Platform

E.1 Unified Architecture

flowchart TD
    subgraph SOURCES["Data Sources"]
        SQL["(Azure SQL)"]
        LAKE["(Data Lake Gen2)"]
        COSMOS["(Cosmos DB)"]
        BLOB["(Blob Storage)"]
        ON_PREM["(On-Premises)"]
    end

    subgraph SYNAPSE["Azure Synapse Analytics Workspace"]
        ADF["Synapse Pipelines\n(ETL/ELT)"]
        SPARK["Apache Spark Pools\n(Big Data, ML)"]
        DED["Dedicated SQL Pool\n(Traditional DW)"]
        SLESS["Serverless SQL Pool\n(Ad hoc queries)"]
        LINK["Synapse Link\n(HTAP)"]
    end

    PBI["Power BI\n(Dashboards)"]

    SOURCES --> ADF
    COSMOS -- "Zero ETL" --> LINK
    ADF --> SPARK
    ADF --> DED
    LINK --> SPARK
    LINK --> SLESS
    DED --> PBI
    SLESS --> PBI
    SPARK --> PBI

E.2 Dedicated SQL Pool vs. Serverless SQL Pool

-- Serverless SQL Pool: query Parquet files directly
SELECT year, country, SUM(revenue) AS total_revenue
FROM OPENROWSET(
    BULK 'https://mydatalake.dfs.core.windows.net/sales/**/*.parquet',
    FORMAT = 'PARQUET'
) AS data
GROUP BY year, country
ORDER BY total_revenue DESC;

E.3 Dedicated SQL Pool – MPP Architecture

Massively Parallel Processing (MPP) architecture:

Control Node (brain)
  → Receives SQL query
  → Breaks it into parallel tasks
  → Sends to Compute Nodes
  → Aggregates results

Compute Node 1 → processes 1/N of data → partial results
Compute Node 2 → processes 1/N of data → partial results
Compute Node N → processes 1/N of data → partial results

Storage: Azure Data Lake Gen2 (decoupled from compute)
Scale: 100 DWU to 30,000 DWU (Data Warehouse Units)

Table distribution:

-- Fact table: distribute on a key to balance load
CREATE TABLE FactSales (
    SaleID      INT NOT NULL,
    CustomerID  INT NOT NULL,
    ProductID   INT NOT NULL,
    Amount      DECIMAL(10,2)
)
WITH (
    DISTRIBUTION = HASH(CustomerID),      -- distribute on CustomerID
    CLUSTERED COLUMNSTORE INDEX           -- columnar index for analytics
);

-- Dimension table: replicate on all nodes
CREATE TABLE DimDate (
    DateID      INT PRIMARY KEY,
    Year        INT,
    Month       INT,
    Quarter     INT
)
WITH (
    DISTRIBUTION = REPLICATE              -- copy on each compute node
);

E.4 Synapse Link (HTAP)

Synapse Link = real-time analytics on operational data without ETL.

Without Synapse Link:
  Cosmos DB OLTP → ETL (ADF) → Synapse DW → Analysis
  Delay: hours, impact on OLTP performance

With Synapse Link:
  Cosmos DB OLTP → Analytical Store (auto, columnar) → Synapse Spark/SQL
  Delay: few minutes, ZERO impact on OLTP

Section F – Azure Databricks: Big Data and Machine Learning

F.1 Overview

Azure Databricks is a managed Apache Spark platform, optimized for Azure.

flowchart TD
    DATABRICKS["Azure Databricks"]
    DATABRICKS --> ETL["ETL/ELT\n(Data Engineering)"]
    DATABRICKS --> ML["Machine Learning\n(Integrated MLflow)"]
    DATABRICKS --> STREAM["Structured Streaming\n(real-time)"]
    DATABRICKS --> SQL["Databricks SQL\n(BI Analytics)"]
    DATABRICKS --> DELTA["Delta Lake\n(lakehouse format)"]

F.2 Apache Spark in Databricks

# PySpark - massive data processing in Databricks
from pyspark.sql import SparkSession
from pyspark.sql.functions import col, sum as spark_sum, count

# Spark is initialized automatically in Databricks
df = spark.read.parquet("abfss://raw@datalake.dfs.core.windows.net/sales/")

# Transformation: sales by region and product
df_result = (
    df
    .filter(col("year") == 2025)
    .groupBy("region", "product_category")
    .agg(
        spark_sum("amount").alias("total_sales"),
        count("order_id").alias("order_count")
    )
    .orderBy("total_sales", ascending=False)
)

# Write in Delta format (lakehouse)
df_result.write.format("delta").mode("overwrite").save(
    "abfss://curated@datalake.dfs.core.windows.net/sales_by_region/"
)

F.3 Delta Lake

Delta Lake is an open-source storage format that adds ACID capabilities to Parquet.

Delta Lake = Parquet files + Transaction Log (JSON)

Transaction Log (_delta_log/):
  000000000000000000000.json → Initial data add
  000000000000000001.json → UPDATE of some rows
  000000000000000002.json → DELETE of old rows
  ...

Advantages:
  ✓ ACID transactions (atomic commit)
  ✓ Time travel (read a past version)
  ✓ Schema enforcement (reject invalid data)
  ✓ Schema evolution (add columns without breaking)
  ✓ Upsert with MERGE

-- Delta Lake Time Travel
SELECT * FROM sales_delta VERSION AS OF 5;   -- version 5
SELECT * FROM sales_delta TIMESTAMP AS OF '2025-01-15';  -- point in time

-- MERGE (Upsert)
MERGE INTO customers_delta AS target
USING new_customers AS source
ON target.customer_id = source.customer_id
WHEN MATCHED THEN UPDATE SET target.email = source.email
WHEN NOT MATCHED THEN INSERT *;

Section G – Azure Data Factory (ADF): ETL/ELT

G.1 ADF Components

flowchart LR
    subgraph ADF["Azure Data Factory"]
        PIPE["Pipeline\n(orchestration)"]
        ACT["Activities\n(pipeline steps)"]
        DS["Datasets\n(data definitions)"]
        LS["Linked Services\n(connections)"]
        TRIG["Triggers\n(schedulers)"]
        IR["Integration Runtime\n(execution engine)"]
    end

    PIPE --> ACT
    ACT --> DS
    DS --> LS
    TRIG --> PIPE
    IR --> ACT

Main activity types:

G.2 ETL vs ELT

flowchart LR
    subgraph ETL["ETL (Extract Transform Load)"]
        E1["Extract\n(read source)"] --> T1["Transform\n(clean, join\non ETL engine)"] --> L1["Load\n(write to target)"]
    end

    subgraph ELT["ELT (Extract Load Transform)"]
        E2["Extract\n(read source)"] --> L2["Load\n(write raw\nto Data Lake)"] --> T2["Transform\n(in target\nSpark/SQL)"]
    end

G.3 Integration Runtime

Azure IR (Auto-resolve)      : Azure-to-Azure, no configuration
Azure IR (Specific region)   : Specify region for data

Self-hosted IR               : Installed on-premises or in another cloud
                              To access sources behind firewall

Azure-SSIS IR                : Run existing SSIS packages

Section H – Real-Time Processing (Streaming)

H.1 Streaming Architecture

flowchart LR
    subgraph SOURCES["Event Generation"]
        IOT["IoT Sensors"]
        APPS["Applications"]
        LOGS["Server logs"]
        CLICK["Clickstream"]
    end

    subgraph INGEST["Ingestion"]
        EH["Azure Event Hubs\n(message queue)"]
        KAFKA["Kafka\n(compatible)"]
    end

    subgraph PROCESS["Processing"]
        ASA["Azure Stream Analytics\n(time windows)"]
        DBR["Databricks\nStructured Streaming"]
    end

    subgraph OUTPUT["Destination (Sink)"]
        SQL2["(Azure SQL)"]
        LAKE2["(Data Lake)"]
        PBI2["Power BI\n(real-time)"]
        COSMOS2["(Cosmos DB)"]
    end

    SOURCES --> EH
    SOURCES --> KAFKA
    EH --> ASA
    EH --> DBR
    ASA --> SQL2
    ASA --> PBI2
    DBR --> LAKE2
    DBR --> COSMOS2

H.2 Azure Event Hubs

Azure Event Hubs is a highly scalable event ingestion service.

Characteristics:
  Throughput : up to millions of events/second
  Retention  : 1 to 7 days (up to 90 days with Premium/Dedicated)
  Consumer groups : multiple consumers read the same stream independently
  Partitions : 1 to 32 (Standard) or 100+ (Premium/Dedicated)

  Kafka-compatible : Kafka applications can use Event Hubs
                     without code modification

H.3 Azure Stream Analytics (ASA)

Azure Stream Analytics runs SQL queries on continuous data streams.

-- ASA query: count errors per server in the last 5 minutes
SELECT
    ServerName,
    COUNT(*) AS ErrorCount,
    System.Timestamp AS WindowEnd
FROM LogStream TIMESTAMP BY EventTime
WHERE Level = 'ERROR'
GROUP BY
    ServerName,
    TumblingWindow(minute, 5)   -- 5-minute tumbling window
HAVING COUNT(*) > 10            -- alert if > 10 errors

Time window types:

Section I – Microsoft Fabric: The Unified Platform

I.1 Overview

Microsoft Fabric is Microsoft’s next-generation analytics platform. It unifies Synapse Analytics, Azure Data Factory, and Power BI under a single interface.

mindmap
  root((Microsoft Fabric))
    Data Engineering
      Lakehouses
      Spark Notebooks
      Pipelines
    Data Warehouse
      SQL Warehouses
      SQL Analytics Endpoints
    Real-Time Analytics
      KQL Databases
      Eventstream
    Data Science
      MLflow Experiments
      Models
    Data Factory
      Copy Activity
      Dataflows Gen2
    Power BI
      Semantic Models
      Reports
      Dashboards

OneLake: single storage layer for all of Fabric (ADLS Gen2 under the hood).

OneLake:
  → Single storage for the entire organization
  → Delta Lake format (Parquet + log)
  → No silos: all Fabric experiences share the same data
  → No duplication: data exists only once

I.2 Lakehouse Architecture

Lakehouse = combination of Data Lake (flexibility) + Data Warehouse (ACID, performance).

Data Lake        : Store everything, no imposed structure, cheap
Data Warehouse   : ACID, high performance, SQL, but expensive and rigid
Data Lakehouse   : Delta Lake on ADLS Gen2, ACID + SQL + flexibility

flowchart LR
    LAKE_ADV["Data Lake\nAdvantages:\n- Any format\n- Cheap\n- Flexible"]
    DW_ADV["Data Warehouse\nAdvantages:\n- ACID\n- SQL Performance\n- Governance"]
    LAKEHOUSE["Data Lakehouse\n✓ Any format\n✓ ACID (Delta)\n✓ SQL Performance\n✓ Flexible\n✓ Cheap"]

    LAKE_ADV --> LAKEHOUSE
    DW_ADV --> LAKEHOUSE

I.3 Fabric vs. Azure Synapse Analytics vs. Databricks

Section J – Power BI: Visualization and Analysis

J.1 Power BI Workflow

flowchart LR
    subgraph DESKTOP["Power BI Desktop"]
        CONN["Data\nConnection"]
        MODEL["Modeling\n(DAX, relationships)"]
        VIS["Create\nvisualizations"]
    end
    subgraph SERVICE["Power BI Service (cloud)"]
        PUBLISH["Publish"]
        SHARE["Sharing\n(workspaces)"]
        DASH["Dashboards\n(tile pins)"]
        SCHED["Scheduled\nrefresh"]
    end
    subgraph MOBILE["Power BI Mobile"]
        CONSUME["View\nreports"]
        ALERTS["Real-time\nalerts"]
    end

    DESKTOP --> SERVICE --> MOBILE

Essential Power BI components:

J.2 The 4 Types of Analytics

flowchart TD
    DESC["Descriptive\n'What happened?'\n→ Reports, dashboards"]
    DIAG["Diagnostic\n'Why did it happen?'\n→ Drill-down, causal analysis"]
    PRED["Predictive\n'What will happen?'\n→ ML, forecasting"]
    PRES["Prescriptive\n'What should we do?'\n→ Recommendations, optimization"]

    DESC --> DIAG --> PRED --> PRES

J.3 Common Visualizations

Section K – Decision Tree: Choosing the Right Azure Service

K.1 Which Service for Which Need?

flowchart TD
    START([Need to store data]) --> Q1{Type of data?}

    Q1 -- Structured\n(tables, schema) --> Q2{Migrating from\nSQL Server?}
    Q1 -- Semi-structured\n(JSON, documents) --> Q3{Global distribution\nrequired?}
    Q1 -- Unstructured\n(files, images) --> BLOB["Azure Blob Storage\n(or Data Lake Gen2\nif analytics)"]

    Q2 -- Yes, with SQL Agent\nLinked Servers --> MI["Azure SQL\nManaged Instance"]
    Q2 -- No, cloud-native --> Q4{Volume?}

    Q4 -- Standard < 4 TB --> SQLDB["Azure SQL Database\n(vCore or DTU)"]
    Q4 -- Very large > 4 TB --> HYPER["Azure SQL Database\nHyperscale"]
    Q4 -- Analytics DW --> SYNAPSE["Azure Synapse Analytics\nDedicated SQL Pool"]

    Q3 -- Yes --> COSMOS["Azure Cosmos DB\n(NoSQL/MongoDB API)"]
    Q3 -- No --> Q5{PostgreSQL or MySQL?}

    Q5 -- PostgreSQL --> PSQL["Azure DB for PostgreSQL\nFlexible Server"]
    Q5 -- MySQL --> MYSQL["Azure DB for MySQL\nFlexible Server"]
    Q5 -- Neither --> COSMOS

    BLOB --> Q6{Analytics\nrequired?}
    Q6 -- Yes --> ADLS["Azure Data Lake\nStorage Gen2"]
    Q6 -- No --> BLOB2["Azure Blob Storage\n(Hot/Cool/Archive tier)"]

K.2 Complete Summary Table

Section L – DP-900 Review Questions

20 Multiple Choice Questions

Question 1 What is the difference between a DBA and a Data Engineer according to DP-900 roles?

• A) The DBA builds pipelines; the Data Engineer manages infrastructure
• B) The DBA manages database infrastructure and security; the Data Engineer builds data pipelines and architectures
• C) They are two names for the same role
• D) The Data Engineer only works with NoSQL data

Answer: B — The DBA is responsible for infrastructure, security, monitoring, and performance. The Data Engineer builds pipelines, integrations, and data architectures. The Data Analyst explores and visualizes for the business.

Question 2 Which file format is optimized for streaming and uses a JSON header with binary data?

• A) Parquet
• B) ORC
• C) Avro
• D) CSV

Answer: C — Avro is row-based with a JSON header (schema) and compressed binary data. It is ideal for Kafka and streaming scenarios.

Question 3 You need to store IoT sensor data from 1000 devices with read latency below 10ms, global distribution, and horizontal scalability. Which service do you choose?

• A) Azure SQL Database Standard
• B) Azure Table Storage
• C) Azure Cosmos DB
• D) Azure Database for PostgreSQL

Answer: C — Cosmos DB guarantees < 10ms latency at the 99th percentile, native global distribution, and unlimited horizontal scalability. Azure Table Storage doesn’t have these latency guarantees or global distribution.

Question 4 What is the difference between schema-on-write and schema-on-read?

• A) Schema-on-write applies to NoSQL databases; schema-on-read to SQL databases
• B) Schema-on-write enforces the schema when writing data (rejected if not conforming); schema-on-read applies the schema at read time
• C) Schema-on-write is slower on write; schema-on-read is slower on read
• D) They are equivalent approaches

Answer: B — Schema-on-write (relational databases) validates data on INSERT (types, constraints). Schema-on-read (NoSQL, Data Lake) doesn’t validate during ingestion; the structure is interpreted at read time.

Question 5 In Azure Synapse Analytics, which option lets you query Parquet files in Azure Data Lake without provisioning infrastructure and paying per TB of data scanned?

• A) Dedicated SQL Pool
• B) Serverless SQL Pool
• C) Apache Spark Pool
• D) Synapse Link

Answer: B — The Serverless SQL Pool allows running T-SQL queries directly on files (Parquet, CSV, JSON) in the Data Lake, with no provisioning and billing per TB scanned.

Question 6 What is the main advantage of Parquet over CSV for analytical workloads?

• A) Parquet is human-readable; CSV is not
• B) Parquet is columnar: only necessary columns are read, with superior compression
• C) Parquet supports ACID transactions; CSV does not
• D) Parquet is older and therefore better supported

Answer: B — Parquet stores data by column (columnar), allowing only the columns needed by the query to be read (projection pushdown). Compression is better because similar values from the same column are stored together.

Question 7 A company wants to migrate their on-premises SQL Server that uses SQL Server Agent and linked servers. Which Azure service minimizes code and configuration changes?

• A) Azure SQL Database General Purpose
• B) Azure SQL Managed Instance
• C) Azure Database for PostgreSQL
• D) Azure Cosmos DB API SQL

Answer: B — Azure SQL Managed Instance offers near-total compatibility with on-premises SQL Server, including SQL Server Agent, Linked Servers, CLR, and cross-database queries.

Question 8 What is the difference between ETL and ELT?

• A) ETL = Extract-Transform-Load (transform before load); ELT = Extract-Load-Transform (load first, transform after)
• B) ETL is for structured data; ELT for unstructured data
• C) ETL is for batch; ELT is for streaming only
• D) There is no difference

Answer: A — ETL transforms data before loading into the target (common for traditional DW). ELT first loads raw data, then transforms in the target (common for Data Lakes and cloud environments).

Question 9 In Azure Cosmos DB, which consistency level is enabled by default and guarantees that within the same session, you can always read what you just wrote?

• A) Strong
• B) Bounded Staleness
• C) Session
• D) Eventual

Answer: C — Session is Cosmos DB’s default level. It guarantees “read-your-own-writes” in the context of a client session, offering a good balance between consistency and performance.

Question 10 What is the difference between Azure Blob Storage and Azure Data Lake Storage Gen2?

• A) ADLS Gen2 is only for JSON data; Blob Storage is for all formats
• B) ADLS Gen2 enables the Hierarchical Namespace (true folder hierarchy, POSIX ACLs, better analytics performance)
• C) Blob Storage supports Spark operations; ADLS Gen2 does not
• D) There is no difference

Answer: B — ADLS Gen2 is Blob Storage with the Hierarchical Namespace enabled, adding a true folder hierarchy, POSIX ACLs, and 10x superior performance for Spark/analytical workloads.

Question 11 What is the “currency” of Azure Cosmos DB for measuring processing capacity?

• A) DTU (Database Transaction Units)
• B) vCore
• C) RU (Request Units)
• D) DWU (Data Warehouse Units)

Answer: C — Request Units (RU) measure the cost of each Cosmos DB operation. 1 RU = reading a 1 KB document. Writes cost approximately 5 RU/KB.

Question 12 What are the 3 Vs of Big Data?

• A) Volume, Velocity, Variety
• B) Validity, Volume, Visibility
• C) Value, Velocity, Visualization
• D) Volume, Variety, Value

Answer: A — The 3 Vs of Big Data are: Volume (massive quantity), Velocity (speed of data arrival), Variety (diversity of formats).

Question 13 In Azure Stream Analytics, you want to count the number of events per server every 5 minutes (non-overlapping windows). Which window type do you use?

• A) Sliding Window
• B) Hopping Window
• C) Tumbling Window
• D) Session Window

Answer: C — The Tumbling Window divides time into consecutive non-overlapping windows (0-5 min, 5-10 min, 10-15 min…). It is the most common window for periodic aggregations.

Question 14 Microsoft Fabric is based on which open-source storage format for its lakehouse?

• A) ORC (Optimized Row Columnar)
• B) Avro
• C) Delta Lake (Parquet + Transaction Log)
• D) Apache Iceberg

Answer: C — Microsoft Fabric uses Delta Lake (Parquet files + JSON transaction log) as its native format for its lakehouse architecture, stored in OneLake (Azure Data Lake Gen2).

Question 15 What is the difference between a Report and a Dashboard in Power BI?

• A) A Report is for admins; a Dashboard for end users
• B) A Report is multi-page with visuals from a single dataset; a Dashboard is an aggregated view with tiles from multiple reports/datasets
• C) A Dashboard can have multiple pages; a Report is limited to a single page
• D) There is no difference

Answer: B — A Report (multi-page) is built from one or more datasets for detailed analysis. A Dashboard aggregates tiles (pins) from multiple different reports/datasets for an overview.

Question 16 Which Delta Lake feature allows querying data as it was at a past point in time?

• A) Schema Enforcement
• B) Time Travel
• C) MERGE (Upsert)
• D) Data Lineage

Answer: B — Delta Lake’s Time Travel allows reading past versions of a table via VERSION AS OF or TIMESTAMP AS OF. This is made possible by the transaction log which keeps a history of all operations.

Question 17 What are the 4 types of analytics according to Microsoft (Power BI)?

• A) Quantitative, Qualitative, Predictive, Prescriptive
• B) Descriptive, Diagnostic, Predictive, Prescriptive
• C) Historical, Real-time, Predictive, Simulation
• D) Reactive, Proactive, Predictive, Cognitive

Answer: B — The 4 types: Descriptive (what happened), Diagnostic (why), Predictive (what will happen), Prescriptive (what should be done).

Question 18 Which Azure service is designed for high-performance ingestion of streaming events (millions of events/second), compatible with Apache Kafka?

• A) Azure Service Bus
• B) Azure Queue Storage
• C) Azure Event Hubs
• D) Azure IoT Hub

Answer: C — Azure Event Hubs is designed to ingest millions of events per second. It is compatible with the Apache Kafka protocol and is the standard component for Azure streaming architectures.

Question 19 In Power BI, which component is the foundation of all reports and visualizations?

• A) Dashboard
• B) Report
• C) Dataset / Semantic Model
• D) Workspace

Answer: C — The Dataset (Semantic Model) is the foundation. Reports are built from datasets. Dashboards assemble tiles from reports. Without a dataset, there are no reports or dashboards.

Question 20 What is the difference between OLTP and OLAP?

• A) OLTP is for large enterprises; OLAP for SMBs
• B) OLTP handles short, fast transactions (INSERT/UPDATE/DELETE); OLAP runs complex analytical queries on large volumes of historical data
• C) OLTP uses Parquet; OLAP uses CSV
• D) They are two names for the same type of workload

Answer: B — OLTP (Online Transaction Processing) = short transactions, write-intensive, ACID, low latency (e.g., e-commerce order). OLAP (Online Analytical Processing) = complex queries, read-intensive, large historical volumes, BI aggregations.

DP-900 Glossary

Search Terms

dp-900 · core · data · concepts · fundamentals · platforms · databases · sql · azure · architecture · analytics · formats · storage · types · blob · databricks · lake · synapse · access · adf · cosmos · dedicated · elt · etl