Free Azure DP-900 Exam Flashcards

Data that fits a fixed, predefined schema of rows and columns — usually stored in relational databases such as Azure SQL Database. Each column has a declared data type, and rows must conform to that schema before insertion.

Data that carries organizational tags or keys but no rigid schema. JSON, XML, YAML, and Avro are typical examples. Stored well in Azure Cosmos DB, Azure Blob Storage, or Azure Data Lake Storage Gen2.

Data with no defined schema — images, video, audio, PDFs, free-form text. Best stored in object storage such as Azure Blob Storage; analyzed with services like Azure AI Services or Synapse Spark rather than T-SQL.

Workloads optimized for many small read/write transactions with low latency and strict consistency. Characterized by ACID guarantees, normalized schemas, and indexed point-lookups. Azure SQL Database and Cosmos DB are typical OLTP stores.

Workloads optimized for large aggregations over historical data — typically read-heavy, columnar, denormalized (star/snowflake schemas), and tolerant of slightly stale data. Azure Synapse Analytics, Microsoft Fabric warehouse, and Databricks SQL are OLAP engines.

Atomicity (all or nothing), Consistency (valid state to valid state), Isolation (concurrent transactions do not interfere), Durability (committed data survives crashes). Relational engines like Azure SQL guarantee ACID by default.

Collecting data over a window (minutes to days) and processing the whole set at once. Higher latency but higher throughput. Azure Data Factory pipelines, Synapse pipelines, and Databricks jobs are batch-oriented.

Processing each event as it arrives, in seconds or sub-seconds. Used for IoT, fraud detection, telemetry. Azure Stream Analytics, Event Hubs, and Microsoft Fabric Real-Time Intelligence (eventstreams) are the core Azure stream services.

Descriptive answers "what happened" (BI reports). Diagnostic answers "why" (drilldowns, correlation). Predictive answers "what will happen" (ML forecasts). Prescriptive answers "what should we do" (optimization, recommendations).

Analyst explores and visualizes data (Power BI, T-SQL). Engineer builds and operates ingestion and transformation pipelines (Data Factory, Synapse, Databricks, Fabric). DBA configures, secures, tunes, and backs up databases (Azure SQL, SQL Managed Instance).

Process of organizing relational tables to reduce redundancy, typically through 1NF, 2NF, and 3NF. Trades storage and update efficiency for more joins at read time. OLTP schemas are normalized; OLAP star schemas are deliberately denormalized.

Primary key uniquely identifies each row and cannot be null. Foreign key enforces referential integrity by pointing to another table's primary key. Index is a separate structure (typically B-tree or columnstore) that speeds up lookups and joins.

Fully managed PaaS relational database based on the latest stable SQL Server engine. Microsoft handles patching, backups, HA, and OS. Offered in DTU and vCore purchasing models with serverless and Hyperscale options. Best for new cloud-native apps.

PaaS option giving near 100% SQL Server feature compatibility, including SQL Agent, cross-database queries, CLR, and Service Broker. Deployed inside an Azure VNet. Ideal for lift-and-shift of on-prem SQL Server with minimal code changes.

IaaS option — you manage the OS, SQL Server version, patching, and HA. Provides 100% SQL Server compatibility and full control (e.g. specific cumulative updates, file layouts). Use when PaaS limits or third-party agents block Managed Instance.

DTU bundles compute, memory, and IO into one purchasing unit — simpler for small workloads. vCore exposes CPU, memory, and storage independently, supports Hyperscale and serverless, and lets you bring SQL Server licenses through Azure Hybrid Benefit.

vCore tier that auto-scales compute within a min/max range and auto-pauses after inactivity, billing only for storage while paused. Best for unpredictable, intermittent workloads such as dev/test or low-traffic apps; not for steady high-throughput OLTP.

Managed open-source PostgreSQL with zone-redundant HA, custom maintenance windows, stop/start, and read replicas. Flexible Server is the current recommended deployment option; Single Server is being retired in favor of it.

Managed open-source MySQL (community edition) on Azure. Supports burstable, general purpose, and business-critical tiers, zone-redundant HA, and read replicas. Microsoft retired Azure Database for MariaDB in September 2024 — migrate MariaDB workloads to MySQL Flexible Server.

DDL defines structure: CREATE, ALTER, DROP, TRUNCATE. DML manipulates data: SELECT, INSERT, UPDATE, DELETE, MERGE. DCL controls access: GRANT, REVOKE, DENY. Knowing the category helps map a use case to the right T-SQL statement on the DP-900 exam.

View is a stored SELECT that behaves like a virtual table. Stored procedure is precompiled T-SQL that can modify data, accept parameters, and return result sets. Function returns a value or table and cannot perform side effects like INSERT/UPDATE on base tables.

Globally distributed, multi-model NoSQL and relational database with single-digit-millisecond reads, automatic indexing, turnkey multi-region writes, and SLAs on throughput, latency, availability, and consistency. Throughput is provisioned in Request Units per second (RU/s) or as serverless.

NoSQL (formerly Core/SQL) — native JSON, recommended for new apps; MongoDB — wire-protocol compatibility; Cassandra — CQL; Gremlin — graph traversal; Table — successor to Azure Table Storage. Plus PostgreSQL (Citus) for distributed relational workloads in Cosmos DB for PostgreSQL.

Strong (linearizable, reads see latest committed write); Bounded Staleness (lag bounded by K versions or T time); Session (read-your-writes within a session — the default); Consistent Prefix (no out-of-order writes); Eventual (lowest latency, may read stale).

Property used to horizontally distribute items across logical and physical partitions. Choose a key with high cardinality and even access patterns; bad keys create hot partitions and throttling. Each logical partition has a 20 GB storage limit.

Cosmos DB's normalized cost unit combining CPU, memory, and IOPS. A 1 KB point read costs about 1 RU; writes, queries, and large reads cost more. You provision RU/s per container or database, or use serverless billing per RU consumed.

Object store for unstructured data such as images, logs, backups, and data-lake files. Three blob types: Block (most files), Append (logs), Page (VHD disks). Organized into storage account > container > blob. Accessed via HTTPS or SDKs.

Hot — frequent access, highest storage cost, lowest access cost. Cool — infrequent access, 30-day minimum. Cold — rare access, 90-day minimum. Archive — offline, hours to rehydrate, 180-day minimum. Lifecycle policies move blobs between tiers automatically.

Hierarchical-namespace layer on top of Azure Blob Storage that adds true directories, POSIX-style ACLs, and atomic directory operations. Optimized for big-data analytics with Synapse, Databricks, HDInsight, and Microsoft Fabric OneLake.

Both expose a key/value table API. Table Storage is a low-cost, single-region NoSQL store with eventual consistency. Cosmos DB for Table adds turnkey global distribution, five consistency levels, automatic indexing of every property, and an SLA on throughput and latency.

Fully managed SMB and NFS file shares accessible from cloud and on-prem clients. Useful for lift-and-shift of file-server workloads and shared application config. Supports Azure File Sync to cache shares on Windows Servers and ZRS/GRS redundancy.

Queue Storage is a simple HTTP-based FIFO-ish queue, up to 64 KB messages, billions of messages per account — cheap and basic. Service Bus is an enterprise broker with sessions, transactions, dead-letter queues, topics/subscriptions, and ordering up to 100 MB messages.

ETL extracts, transforms in a separate engine, then loads into the target — common when the destination is a small, costly OLTP store. ELT loads raw data into a scalable warehouse or lake first, then transforms in place using SQL or Spark — the modern cloud default in Synapse and Fabric.

Serverless data-integration service that orchestrates ETL/ELT pipelines across 100+ connectors. Key concepts: linked service (connection), dataset (data shape), activity (action), pipeline (workflow), and Mapping Data Flows (visual transformations executed on a managed Spark cluster).

A Data Factory or Synapse component installed on an on-prem or VNet-bound machine that lets pipelines reach private data sources behind firewalls. The default Azure integration runtime handles purely cloud sources.

Unified analytics platform combining a dedicated SQL pool (MPP data warehouse), a serverless SQL pool (pay-per-query over data-lake files), Apache Spark pools, and integrated pipelines and Power BI. Workspaces unify these engines over a shared Data Lake Storage Gen2 account.

Dedicated pool is a provisioned MPP warehouse billed by DWU — always on, scales to petabytes, best for predictable enterprise warehouses. Serverless pool is pay-per-TB-scanned T-SQL over Parquet/CSV/Delta in the lake — ideal for ad-hoc exploration of data-lake files.

First-party managed Apache Spark platform on Azure built around the lakehouse architecture and Delta Lake. Provides interactive notebooks, jobs, ML workflows (MLflow), Unity Catalog governance, and SQL warehouses. Strong for data engineering plus data science on the same data.

Managed open-source big-data clusters on Azure (Spark, Hadoop, Kafka, HBase, Interactive Query, Storm). Use when you need a specific OSS distribution or version not abstracted by Synapse/Databricks/Fabric. Generally a legacy choice for new projects.

Fully managed real-time analytics service that runs SQL-like queries over streams from Event Hubs, IoT Hub, or Blob Storage and writes to sinks like Power BI, SQL, Cosmos DB, or Data Lake. Billed in Streaming Units; supports windowing (tumbling, hopping, sliding, session, snapshot).

Unified SaaS analytics platform launched in 2023 covering data engineering, warehousing, real-time intelligence, data science, and Power BI on a single OneLake storage layer. Billed via Fabric capacity units (F SKUs); replaces stitching Synapse + Data Factory + Power BI Premium for new projects.

The single, tenant-wide data lake underlying Microsoft Fabric — sometimes called "OneDrive for data." Every Fabric workspace stores data in OneLake in open Delta-Parquet format with shortcuts that virtualize data from ADLS Gen2, S3, GCS, and Dataverse without copying.

Lakehouse stores Delta files for Spark/notebook engineering with a SQL analytics endpoint (read-only T-SQL). Warehouse is a full read/write T-SQL warehouse with multi-table transactions. KQL database (Eventhouse) is for real-time, log/event data queried with Kusto Query Language.

Desktop — free Windows authoring tool to build models and reports. Service — cloud SaaS for sharing, scheduled refresh, dashboards, apps, and workspaces (Pro or Premium/Fabric capacity). Mobile — iOS/Android apps for consuming reports and dashboards on the go.

Import — data is cached in the model for fastest performance, refreshed on a schedule. DirectQuery — queries run live against the source, no cache, freshest data but slower. Direct Lake (Fabric only) — reads Delta-Parquet from OneLake at import speed without copying or scheduled refresh.

Dimensional model with a central fact table (measures like sales amount) surrounded by denormalized dimension tables (date, product, customer). Optimized for OLAP aggregations and the recommended modeling pattern in Power BI, Synapse warehouses, and Fabric warehouses.

Warehouse — schema-on-write, curated relational data for BI (Synapse dedicated pool, Fabric warehouse). Lake — schema-on-read, raw files of any type (ADLS Gen2). Lakehouse — open table formats (Delta, Iceberg) layered on a lake to give warehouse-like ACID and BI on lake files (Databricks, Fabric lakehouse).

Open-source storage layer over Parquet that adds ACID transactions, time travel, schema enforcement, and MERGE/UPSERT to data-lake files. It is the default table format for Azure Databricks and Microsoft Fabric (OneLake), enabling reliable lakehouse warehousing.

Ingest with Data Factory or Fabric pipelines into ADLS Gen2/OneLake; process and transform with Synapse Spark, Databricks, or Fabric notebooks; serve curated data via Synapse dedicated SQL pool or Fabric warehouse; visualize with Power BI; orchestrate end-to-end with Data Factory or Fabric pipelines.

Lakehouse design pattern: Bronze holds raw ingested data as-is, Silver holds cleansed and conformed tables, Gold holds business-level aggregates ready for BI and ML. Promoted by Databricks and used in Microsoft Fabric lakehouses to organize Delta tables by quality and trust.