DP-900 Practice Questions

Question 1

Which THREE of the following are characteristics of a data lake compared to a data warehouse?

Accepted Answer

Data lakes store data in its native or raw format.. A data lake stores data in its native or raw format, meaning it does not require transformation or schema definition at the time of ingestion. This allows organizations to retain the original fidelity of the data, which is a fundamental distinction from a data warehouse that typically transforms and structures data before loading (ETL). In Azure, Azure Data Lake Storage (ADLS) Gen2 supports storing any file format (e.g., Parquet, CSV, JSON, binary) without preprocessing.

Answer

Data lakes guarantee ACID transactions across all data.

Answer

Data lakes store only structured data.

Question 2

Refer to the exhibit. An administrator is configuring aggregations in Power BI Premium to improve performance on a large dataset. The aggregation is defined on the Sales table with SUM(Amount) grouped by ProductCategory, Region, and Date at the monthly level. However, some reports that query daily data are still slow. What is the most likely reason?

Accepted Answer

The aggregation level is monthly, but queries need daily granularity. The aggregation is defined at the monthly level (grouping by month), but the slow reports are querying daily data. Power BI aggregations work by pre-aggregating data at a specified granularity; when a query requests a lower level of detail (daily), the aggregation cannot satisfy the query, so Power BI falls back to scanning the full detailed dataset, causing slow performance. To improve daily queries, an additional aggregation at the daily level would be needed.

Answer

The dataset is too large for aggregations

Answer

The aggregation is not in DirectQuery mode

Answer

The aggregation has too many dimensions

Question 3

A company stores user session data for a web application. Each session has a unique SessionID, UserID, start time, end time, and a variable set of attributes (e.g., pages visited, clicks, device type). The workload requires low-latency reads by SessionID and occasional queries by UserID and time range. Schema flexibility is critical because the attributes evolve over time. The team wants a fully managed NoSQL database that supports secondary indexing. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB (NoSQL API). Azure Cosmos DB (NoSQL API) is correct because it is a fully managed NoSQL database that offers low-latency reads by SessionID (using a partition key), supports secondary indexing for queries by UserID and time range, and provides schema flexibility for evolving session attributes. Its multi-model API and global distribution meet the workload requirements without manual indexing or schema management.

Answer

Azure SQL Database

Answer

Azure Table Storage

Answer

Azure Blob Storage

Question 4

A company plans to migrate a 500 GB SQL Server database from on-premises to Azure SQL Database. They require minimal downtime during the migration. Which approach should they use?

Accepted Answer

Use Azure Database Migration Service with online migration mode. Azure Database Migration Service (DMS) with online migration mode is the correct approach because it supports minimal-downtime migrations by continuously replicating ongoing changes from the source SQL Server to Azure SQL Database using the transactional replication technology. This allows the source database to remain operational during the migration, and only a brief cutover is needed at the end to switch applications to the target.

Answer

Export a BACPAC file and import to Azure SQL Database

Answer

Use Azure Site Recovery to replicate the database

Answer

Create a full database backup and restore to Azure SQL Database

Question 5

A company operates an online store where customers place orders and the system immediately updates inventory and records payments. This workload is best described as:

Accepted Answer

OLTP (Online Transaction Processing). This workload is best described as OLTP because it involves real-time, high-frequency transactions that immediately update inventory and record payments. OLTP systems are designed for concurrent, atomic operations that maintain data integrity, which is exactly what an online store's order processing requires.

Answer

OLAP (Online Analytical Processing)

Answer

Batch processing

Answer

Data warehousing

Question 6

A social media application stores user posts in Azure Cosmos DB. Each post has fields: PostID (unique), UserID, Timestamp, Content, LikesCount. The application frequently queries for all posts by a specific UserID ordered by Timestamp descending. To minimize Request Unit (RU) consumption, which partition key and indexing strategy should be used?

Accepted Answer

Partition key: UserID, and create a composite index on (UserID, Timestamp DESC). UserID is the most frequently filtered attribute, making it an ideal partition key to distribute data evenly and avoid cross-partition queries. Adding a composite index on (UserID, Timestamp DESC) allows the query to be served from a single physical partition with an index seek, minimizing RU consumption by avoiding a full scan or sort operation.

Answer

Partition key: Timestamp, and sort by UserID in the query

Answer

Partition key: PostID, and use ORDER BY Timestamp

Answer

Partition key: UserID, and use ORDER BY PostID

Question 7

A company stores terabytes of customer support chat transcripts in JSON format. The data is rarely modified and needs to be accessed by analysts using SQL queries. The analysts do not want to manage servers or provision throughput. Which Azure service should be used to store and query this data?

Accepted Answer

Azure Blob Storage (with Azure Data Lake Storage Gen2) and query using Azure Synapse Serverless SQL. Azure Blob Storage with Azure Data Lake Storage Gen2 provides a cost-effective, scalable solution for storing large volumes of JSON data in its native format. By using Azure Synapse Serverless SQL, analysts can query this data directly with standard T-SQL without provisioning any infrastructure or managing throughput, meeting the requirement for serverless, on-demand querying of rarely modified data.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure SQL Database

Question 8

A company collects data from multiple sources: IoT sensor streams, social media feeds, and CSV files from legacy systems. They want to store all this data in its original format without any transformation, so that data scientists can later apply machine learning models or run ad-hoc queries. Which data storage pattern best describes this approach?

Accepted Answer

Data lake. A data lake is designed to store vast amounts of raw data in its native format (structured, semi-structured, or unstructured) without requiring upfront schema or transformation. This aligns perfectly with the scenario of ingesting IoT streams, social media feeds, and CSV files as-is, enabling data scientists to later apply machine learning or run ad-hoc queries directly against the raw data.

Answer

Data warehouse

Answer

Relational database

Answer

Data mart

Question 9

A financial services company is building a real-time fraud detection system. Transactions are streamed from multiple sources into Azure Event Hubs. The system must run a trained machine learning model (scored in near real-time) to flag suspicious transactions. The model is a Python pickle file that needs to be deployed as a web service with low latency (under 100 ms per prediction). The data engineering team wants to use a serverless compute option to run the scoring logic, and the solution must integrate with Azure Stream Analytics for alerting. Which Azure service should you use to deploy the model?

Accepted Answer

Azure Machine Learning managed online endpoint. Azure Machine Learning managed online endpoints are the correct choice because they are designed for deploying trained models (including Python pickle files) as low-latency web services (under 100 ms per prediction) with serverless compute. They natively integrate with Azure Stream Analytics for alerting, allowing real-time scoring of streaming transactions from Event Hubs without managing infrastructure.

Answer

Azure Functions

Answer

Azure Kubernetes Service (AKS)

Answer

Azure Databricks

Question 10

Your company is migrating an on-premises SQL Server data warehouse to Azure. The solution must support both historical analytics and real-time reporting. Which Azure service should you recommend as the primary data store?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it is a cloud-native analytics service that unifies big data and data warehousing, supporting both historical analytics (via dedicated SQL pools for large-scale relational data warehousing) and real-time reporting (via serverless SQL pools or Apache Spark pools for streaming and interactive queries). It is designed to handle the migration of an on-premises SQL Server data warehouse while providing integrated capabilities for batch and real-time workloads.

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage Gen2

Answer

Azure SQL Database

Question 11

A company uses Azure SQL Database and needs to audit all data modifications (INSERT, UPDATE, DELETE) for compliance purposes. The audit logs must be stored for 7 years. Which feature should they enable?

Accepted Answer

SQL Database auditing. SQL Database auditing captures all data modifications (INSERT, UPDATE, DELETE) and can store logs in Azure storage, Log Analytics, or Event Hubs with retention up to 7 years. Option A is wrong because Advanced Threat Protection detects suspicious activities, not audits modifications. Option C is wrong because vulnerability assessment scans for security weaknesses, not logging changes. Option D is wrong because Transparent Data Encryption (TDE) encrypts data at rest, it does not log modifications.

Answer

Advanced Threat Protection

Answer

Vulnerability assessment

Answer

Transparent Data Encryption (TDE)

Question 12

Refer to the exhibit. You create an external table in Azure SQL Database. Which data source is being used?

Accepted Answer

Azure Blob Storage. The exhibit shows an external table referencing a data source with the LOCATION set to 'https://mystorage.blob.core.windows.net/...', which is the endpoint for Azure Blob Storage. In Azure SQL Database, external tables are created over external data sources that point to Azure Blob Storage or Azure Data Lake Storage, but the URL format 'blob.core.windows.net' specifically indicates Azure Blob Storage. The CREATE EXTERNAL TABLE statement uses this data source to read data stored as files (e.g., CSV, Parquet) in the blob container.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 13

A company stores three types of data: 1) Customer orders in a SQL table with fixed columns for OrderID, CustomerID, and OrderDate. 2) Product reviews in XML files where each file contains varying tags such as and . 3) Video files of product demonstrations. Which of the following correctly classifies these data types in order from first to third?

Accepted Answer

Structured, Semi-structured, Unstructured. Customer orders in a SQL table with fixed columns (OrderID, CustomerID, OrderDate) are structured data because they conform to a rigid schema. Product reviews in XML files with varying tags like and are semi-structured data because they have tags/metadata but no fixed schema. Video files of product demonstrations are unstructured data because they lack any predefined data model or organization.

Answer

Semi-structured, Unstructured, Structured

Answer

Unstructured, Structured, Semi-structured

Answer

Structured, Unstructured, Semi-structured

Question 14

Which TWO factors should you consider when choosing between Azure SQL Database and SQL Server on Azure Virtual Machines?

Accepted Answer

Compatibility with on-premises SQL Server features. Option C is correct because SQL Server on Azure Virtual Machines runs the full SQL Server engine on an IaaS VM, so it supports the broadest compatibility with on-premises SQL Server features (for example, cross-database queries, SQL Server Agent, CLR, FILESTREAM, and specific legacy features) that Azure SQL Database may not fully support in its PaaS model. Option E is correct because the two services differ fundamentally in administrative control: SQL Server on Azure VMs gives you OS-level and instance-level control (patching, configuration, sysadmin rights, custom settings), while Azure SQL Database is a fully managed PaaS where Microsoft handles most administration and limits control. Options A, B, and D are not the deciding factors: auditing, geo-replication, and Transparent Data Encryption are capabilities available in both Azure SQL Database and SQL Server on Azure VMs, so they do not differentiate the two choices.

Answer

Need for database auditing

Answer

Support for geo-replication

Answer

Need for Transparent Data Encryption

Question 15

A global e-commerce company uses Azure SQL Database for its product catalog. The database is hosted in the West US region. To ensure the catalog remains available if West US experiences an outage, the company wants to configure a secondary database in East US that can be used for reads and can be automatically promoted to primary during a disaster. They require a Recovery Point Objective (RPO) of less than 5 seconds and a Recovery Time Objective (RTO) of less than 30 minutes. Which feature should they implement?

Accepted Answer

Auto-failover groups. Auto-failover groups (Option B) are the correct choice because they provide automatic, orchestrated failover of a primary Azure SQL Database to a secondary region (East US) during an outage, meeting the RPO of less than 5 seconds (typically 5–10 seconds for active geo-replication) and RTO of less than 30 minutes (usually under 1 hour). The secondary database can be used for read-only queries, and the failover group ensures the entire group of databases fails over as a unit, maintaining the same connection string.

Answer

Active geo-replication

Answer

Geo-restore

Answer

Transactional replication

Microsoft Azure Data Fundamentals DP-900 practice test

Three ways to study

Study DP-900 by domain

Study DP-900 by topic

Describe core data concepts practice questions

Describe an analytics workload on Azure practice questions

Identify considerations for relational data on Azure practice questions

Describe considerations for working with non-relational data on Azure practice questions

DP-900 fundamentals practice questions

DP-900 scenario practice questions

DP-900 troubleshooting practice questions

Top DP-900 questions

Microsoft Azure Data Fundamentals DP-900 practice questions