Sign in
Home/Microsoft/Microsoft Azure Data Fundamentals (DP-900)/Free questions

Microsoft Azure Data Fundamentals (DP-900) — Free Practice Questions

10 free sample questions from a bank of 221, with the correct answers and explanations. No signup required — start practising right now.

1Which T-SQL statement should be used to instruct a database management system to use an index instead of performing a full table scan?
  • SELECT
  • WHERE
  • JOIN
Answer: B

The short version

B — WHERE shapes index use through selective filtering. The predicate lets the optimizer seek an index instead of scanning every row in the settled key.

Key concepts in this question

  • WHERE clause: row filter that expresses search conditions and directly influences access path selection.
  • Index seek versus scan: seek navigates B-tree to matching keys while scan reads all pages, so selectivity drives efficiency.
  • SELECT contrast: projection list chooses columns to return, but does not by itself constrain which rows are touched.
  • JOIN contrast: combines tables on keys and can benefit from indexes, yet the filtering directive still comes from predicates.
  • Optimizer role: cost model picks seek when statistics show the filter is selective and a supporting index exists.

Why B is correct

B is correct because adding a restrictive WHERE condition gives the engine a sargable predicate that matches indexed columns. The optimizer can then choose an index seek or filtered lookup rather than a full table scan. In teaching terms, WHERE is presented as the instruction that narrows rows and unlocks index assisted retrieval.

Why the others are wrong

  • A. SELECT lists output columns without narrowing rows for index navigation.
  • C. JOIN defines table relationships but still relies on filters to drive seeks.

DP-900 exam tip

Link index avoidance of full scans to WHERE: filter first, seek second.

2Which Azure service provides the highest compatibility for databases migrated from Microsoft SQL Server 2019 Enterprise edition?
  • Azure SQL Database
  • Azure Database for MySQL
  • Azure SQL Managed Instance
  • an Azure SQL Database elastic pool
Answer: C

The short version

C — Managed Instance is closest to on premises SQL Server. It preserves engine surface, agents, and networking for Enterprise migrations in the settled key.

Key concepts in this question

  • Managed Instance: platform service with near complete SQL Server compatibility, including cross database queries and Agent jobs.
  • Single database contrast: Azure SQL Database excels at SaaS scale but trims instance scoped features needed for lift and shift.
  • MySQL contrast: entirely different engine family for open source workloads, irrelevant to SQL Server compatibility.
  • Elastic pool contrast: cost sharing model for many single databases, inheriting the same engine limits as single database.
  • Enterprise needs: high availability, Agent automation, and large compatibility surface favor instance level parity.

Why C is correct

C is correct because Managed Instance was designed for straightforward migration from SQL Server 2019 Enterprise with minimal refactoring. It supports most engine features, preserves endpoint compatibility, and integrates with virtual networks for hybrid identity. Among PaaS choices it therefore offers the highest fidelity target for existing Enterprise databases.

Why the others are wrong

  • A. single database drops Agent and cross database patterns common in Enterprise estates.
  • B. MySQL cannot run T-SQL Server workloads without rewrite.
  • D. elastic pools share resources but do not add engine compatibility.

DP-900 exam tip

Highest SQL Server compatibility always points to Managed Instance.

3Which setting can only be configured during the creation of an Azure Cosmos DB account?
  • geo-redundancy
  • multi-region writes
  • production or non-production account type
  • API
Answer: D

The short version

D — the API model is fixed at creation. Data Lake style, Core SQL, Mongo, Cassandra, Gremlin, or Table choice locks storage shape in the settled key.

Key concepts in this question

  • Cosmos DB API: selects document, graph, wide column, or table semantics that dictate query language and SDK behavior.
  • Creation time lock: API determines internal indexing and protocol endpoints, so it cannot flip after provisioning.
  • Geo redundancy contrast: read regions and failover priorities can be added or reordered during the account lifetime.
  • Multi region writes contrast: multi master capability can be enabled later subject to consistency and conflict policy.
  • Account type contrast: production versus non production guidance is operational metadata, not a structural data model lock.

Why D is correct

D is correct because choosing Core SQL versus Table versus Gremlin and others fixes how data is stored, indexed, and queried. Portal and automation block API changes after creation to protect application compatibility. Other settings remain mutable precisely because they control distribution and resilience rather than core data shape.

Why the others are wrong

  • A. geo redundancy regions can be added or removed after creation.
  • B. multi region writes can be toggled with proper conflict handling.
  • C. account type labeling does not constrain engine behavior like API does.

DP-900 exam tip

If it defines query language and data shape, it is locked at account birth: the API.

4You have an Azure Cosmos DB account that uses the Core (SQL) API. Which two settings can you configure at the container level? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.
  • the throughput
  • the read region
  • the partition key
  • the API
Answer: A, C

The short version

AC — throughput and partition key live at container scope. They control scale and distribution for Core SQL collections in the settled key.

Key concepts in this question

  • Container throughput: RU per second allocation, manual or autoscale, that bounds request rate for that collection.
  • Partition key: immutable distribution path that spreads documents across physical partitions for scale.
  • Read region contrast: account level geo replication setting that applies to all containers rather than one.
  • API contrast: account level data model choice fixed at creation, not a per container tunable.
  • Core SQL hierarchy: database groups containers while containers own indexing, TTL, and performance knobs.

Why AC is correct

A is correct because each container can carry dedicated or shared throughput tailored to its workload. C is correct because the partition key is defined when the container is created and governs how its documents distribute. Together they represent the two essential container level design decisions for scale and data placement.

Why the others are wrong

  • B. read regions are configured at account level for global distribution.
  • D. API is chosen once for the whole account and cannot vary per container.

DP-900 exam tip

Container asks about scale and spread: answer throughput plus partition key.

5Your company is designing a data store that will contain student data. The data has the following format. Which type of data store should you use?
Microsoft Azure Data Fundamentals (DP-900) question 5
  • graph
  • key/value
  • object
  • columnar
Answer: D

The short version

D — columnar fits analytic student workloads. Storing columns together compresses repeats and speeds scans over many rows in the settled key.

Key concepts in this question

  • Columnar store: persists each column contiguously, ideal for aggregations and filtering over large fact sets.
  • Graph contrast: excels at relationship traversals between entities, not bulk tabular scans.
  • Key value contrast: serves point lookups by key with opaque values, weak for analytic queries.
  • Object contrast: holds unstructured blobs like images and video, not structured tabular analytics.
  • Student data pattern: enrollment, grades, and attendance invite grouped reporting and cohort analysis suited to columns.

Why D is correct

D is correct because the depicted student format is structured and analysis oriented, where queries slice and aggregate across attributes. Columnar layout minimizes I/O by reading only needed columns and benefits from encoding and compression. That profile outperforms graph, key value, or object options for classroom analytics.

Why the others are wrong

  • A. graph helps traversals, not cohort aggregations.
  • B. key value targets single key fetches, not analytic scans.
  • C. object stores blobs, not queryable tabular columns.

DP-900 exam tip

Analytic scans over structured tables signal columnar every time.

6Which storage solution supports role-based access control (RBAC) at the file and folder level?
  • Azure Disk Storage
  • Azure Data Lake Storage
  • Azure Blob storage
  • Azure Queue storage
Answer: B

The short version

B — Data Lake Storage brings hierarchical ACLs. Namespace plus Azure RBAC and POSIX style permissions enable file and folder control in the settled key.

Key concepts in this question

  • Data Lake Gen2: Blob backed store with hierarchical namespace that supports directories, renames, and fine ACLs.
  • RBAC at folder level: role assignments plus access control lists scope read, write, and execute per directory and file.
  • Disk contrast: block devices attached to single VMs, without shared file folder ACL semantics.
  • Blob contrast: flat object namespace with container level RBAC, lacking true directory inheritance without Data Lake features.
  • Queue contrast: message transport for decoupling, with no file system permission model.

Why B is correct

B is correct because only Data Lake Storage combines Azure RBAC with filesystem ACLs evaluated at file and folder granularity. Administrators can grant a group access to one folder while denying sibling paths, which standard Blob cannot express cleanly. That capability is the exam discriminator for hierarchical RBAC questions.

Why the others are wrong

  • A. Disk exposes volume permissions to one VM, not shared folder RBAC.
  • C. Blob manages access at account or container scope, not nested folders.
  • D. Queue secures messages, not files and directories.

DP-900 exam tip

File plus folder RBAC wording always means Data Lake with hierarchical namespace.

7You need to store data in Azure Blob storage for seven years to meet your company's compliance requirements. The retrieval time of the data is unimportant. The solution must minimize storage costs. Which storage tier should you use?
  • Archive
  • Hot
  • Cool
Answer: A

The short version

A — Archive is cheapest for seven year retention with no hurry. Offline tier trades hours of rehydration for minimum monthly storage in the settled key.

Key concepts in this question

  • Archive tier: offline Blob storage with lowest at rest cost, designed for compliance retention rarely read.
  • Hot contrast: online tier for frequent access with higher storage cost but instant reads.
  • Cool contrast: online infrequent tier for short term retention with lower latency than Archive but higher cost.
  • Compliance horizon: seven year hold with unimportant retrieval time prioritizes price per gigabyte over speed.
  • Rehydration tradeoff: Archive requires hours to restore before reading, acceptable when access is exceptional.

Why A is correct

A is correct because requirements explicitly minimize cost while tolerating slow retrieval for regulatory retention. Archive satisfies multi year WORM style holds at the lowest price point among Blob tiers. Hot and Cool would charge more storage each month without any needed latency benefit.

Why the others are wrong

  • B. Hot optimizes frequent instant reads at premium storage rates.
  • C. Cool suits months of infrequent access, still pricier than Archive for years.

DP-900 exam tip

Years of retention plus retrieval unimportant equals Archive for lowest cost.

8Which type of non-relational data store supports a flexible schema, stores data as JSON files, and stores the all the data for an entity in the same document?
  • document
  • columnar
  • graph
  • time series
Answer: A

The short version

A — document stores whole JSON entities together. Flexible schema plus single document reads define the model in the settled key.

Key concepts in this question

  • Document store: JSON or BSON collections where each entity carries its attributes together under one key.
  • Flexible schema: fields can vary per document without migrations, suiting evolving app models.
  • Columnar contrast: splits columns for analytics, opposite of keeping one entity in one place.
  • Graph contrast: emphasizes edges and traversals rather than self contained entity blobs.
  • Time series contrast: optimizes timestamp ordered telemetry, not generic flexible JSON entities.

Why A is correct

A is correct because the prompt lists all three document hallmarks together, flexible fields, JSON encoding, and colocation of entity data. Cosmos DB Core SQL, Mongo API, and similar services implement exactly this pattern. No other non relational family combines all three traits in one definition.

Why the others are wrong

  • B. columnar separates attributes for scans, not single document colocation.
  • C. graph centers on relationships, not whole entity JSON blobs.
  • D. time series centers on time ordering, not flexible entity documents.

DP-900 exam tip

JSON plus flexible plus whole entity in one place spells document.

9When provisioning an Azure Cosmos DB account, which feature provides redundancy within an Azure region?
  • multi-master replication
  • Availability Zones
  • the strong consistency level
  • automatic failover
Answer: B

The short version

B — Availability Zones give in region redundancy. Physically separate datacenters with independent power and networking survive facility faults in the settled key.

Key concepts in this question

  • Availability Zones: three or more isolated locations inside one region that replicate synchronously for high availability.
  • Multi master contrast: global write replication across regions for active active apps, not single region facility resilience.
  • Strong consistency contrast: data correctness guarantee about read freshness, not physical redundancy.
  • Automatic failover contrast: cross region promotion logic for disaster recovery, distinct from zonal survival.
  • Provisioning scope: zonal choice is made at account creation for supported regions to pin replicas.

Why B is correct

B is correct because only Zones place synchronous copies in different buildings with separate failure domains inside the same region. If one zone loses power or networking, Cosmos DB continues serving from survivors without cross region failover. The other features address global writes, consistency, or region loss rather than campus level faults.

Why the others are wrong

  • A. multi master spans regions for write availability, not zonal faults.
  • C. strong consistency governs freshness, not placement redundancy.
  • D. automatic failover handles region loss, not in region zone survival.

DP-900 exam tip

Within a region means Zones, across regions means failover or multi master.

10What is a benefit of the Azure Cosmos DB Table API as compared to Azure Table storage?
  • provides resiliency if an Azure region fails
  • supports partitioning
  • provides a higher storage capacity
  • supports a multi-master model
Answer: D

The short version

D — Table API adds global multi master. It keeps Table storage semantics while enabling multi region writes and planet scale in the settled key.

Key concepts in this question

  • Table API: Cosmos DB interface compatible with Azure Table storage models plus global distribution.
  • Multi master: ability to write in multiple regions with conflict resolution, beyond single primary Table storage.
  • Resiliency contrast: both services replicate, so region survival alone does not differentiate them.
  • Partitioning contrast: both partition by key, so basic scale out is shared rather than unique.
  • Capacity contrast: storage limits differ, but the headline Table API benefit is active active writes.

Why D is correct

D is correct because classic Table storage restricts writes to one region while Table API on Cosmos DB supports multi master replication. Applications gain lower write latency worldwide and survival of regional write outages. That active active distinction is the documented advantage over standard Table storage.

Why the others are wrong

  • A. both options replicate, so basic resiliency is not unique.
  • B. both support partitioning by key.
  • C. capacity alone is not the defining Table API benefit.

DP-900 exam tip

Table API versus Table storage: choose multi master as the differentiator.

Want the full bank of 221 questions for Microsoft Azure Data Fundamentals (DP-900)? See all practice exams.