DP-900 Practice Exam 2 — 20 Questions

Question 1

A company stores customer data in a relational database with columns like CustomerID, Name, and Email. They also store product images as JPEG files in Azure Blob Storage, and customer feedback as JSON documents that contain varying fields such as rating, comments, and optional metadata. Which of the following correctly orders these data types from most structured to least structured?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Relational data, JSON, images. Relational data (CustomerID, Name, Email) is the most structured because it enforces a fixed schema with defined data types and constraints. JSON documents (customer feedback) are semi-structured: they have a flexible schema with optional fields like metadata, but still use key-value pairs. Images (JPEG files) are unstructured binary data with no inherent schema. Option C correctly orders them from most structured (relational) to least structured (images).

Answer

Relational data, images, JSON

Answer

Images, JSON, relational data

Answer

JSON, relational data, images

Question 2

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

Question 3

A company is migrating an on-premises SQL Server database to Azure. They want to ensure that database administrators (DBAs) can perform administrative tasks but cannot view sensitive customer data in query results. Which Azure SQL feature should they implement?

Accepted Answer

Always Encrypted. Always Encrypted ensures that sensitive data is encrypted at all times, including during query processing, and that the encryption keys are never revealed to the database engine. This allows DBAs to perform administrative tasks (e.g., backups, index maintenance) while being unable to view the plaintext data in query results, because the decryption occurs only on the client side.

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption

Answer

Row-Level Security

Question 4

A data engineering team needs to build a batch ETL pipeline that transforms large volumes of clickstream data stored as CSV files in Azure Data Lake Storage Gen2. The transformations require running distributed Python and Scala code using Apache Spark. The transformed data will be loaded into a data warehouse for reporting. The team wants a serverless compute environment that automatically scales and charges per second. Which Azure service should they use to run the Spark transformations?

Accepted Answer

Azure Synapse Analytics (Spark pools). Azure Synapse Analytics (Spark pools) is the correct choice because it provides a serverless Apache Spark compute environment that automatically scales and charges per second, perfectly matching the requirement for running distributed Python and Scala transformations on large volumes of clickstream data stored in Azure Data Lake Storage Gen2. The service integrates directly with the data lake and can load transformed results into a dedicated SQL pool for data warehouse reporting.

Answer

Azure Data Factory

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Question 5

A company runs a customer-facing web application that uses an Azure SQL Database. The database experiences highly variable workloads: high traffic during business hours and low traffic at night and on weekends. The company wants to pay only for the compute resources consumed and automatically scale compute capacity based on demand, while maintaining the ability to pause during inactivity. Which Azure SQL Database service tier should they choose?

Accepted Answer

Serverless. The Serverless tier is designed for workloads with variable traffic and idle periods, as it automatically scales compute resources based on demand and can pause the database during inactivity, charging only for consumed compute and storage. This matches the requirement to pay only for resources used and to pause when there is no traffic, such as at night and weekends.

Answer

Hyperscale

Answer

Provisioned (General Purpose)

Answer

Business Critical

Question 6

A company runs an e-commerce application on Azure SQL Database. The Orders table has millions of rows. Queries that filter on CustomerID and order by OrderDate DESC are slow. The table currently has a clustered index on OrderID (the primary key). Which indexing strategy will most improve performance for these queries?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) with included columns for other needed columns.. Creating a nonclustered index on (CustomerID, OrderDate DESC) with included columns allows the query to filter on CustomerID and sort by OrderDate in descending order using a single index seek, avoiding a sort operation. This leverages the index's key order to directly return rows in the desired order, which is critical for performance on large tables in Azure SQL Database.

Answer

Create a nonclustered index on CustomerID including OrderDate.

Answer

Rebuild the clustered index to be on CustomerID.

Answer

Create a nonclustered index on OrderDate.

Question 7

A data engineer needs to query data stored in CSV files in Azure Data Lake Storage Gen2 using T-SQL in Azure Synapse Analytics, without loading the data into the database. Which feature should they use?

Accepted Answer

External tables. External tables in Azure Synapse Analytics allow you to query data stored in files (such as CSV in Azure Data Lake Storage Gen2) using T-SQL without loading the data into the database. They use the PolyBase engine to read the files directly, enabling on-the-fly querying of external data sources.

Answer

Materialized views

Answer

Stored procedures

Answer

Indexed views

Question 8

You are designing a relational database for an IoT application that ingests high volumes of time-stamped sensor data. The queries frequently filter by device ID and time range. Which index strategy would optimize query performance?

Accepted Answer

Create a composite index on (DeviceID, Timestamp). A composite index on (DeviceID, Timestamp) directly supports the two most common filter predicates in the query workload: DeviceID (for equality) and Timestamp (for range scans). In SQL Server, a composite index with DeviceID as the leading column allows the query engine to perform an index seek on DeviceID and then a range scan on Timestamp, minimizing I/O and avoiding key lookups. This strategy is optimal for time-series IoT data where queries almost always specify a device and a time window.

Answer

Create a non-clustered index on Timestamp only

Answer

Create a clustered index on DeviceID only

Answer

Create a non-clustered index on SensorType

Question 9

A social networking application stores user profiles as JSON documents in Azure Cosmos DB. Each profile includes fields such as 'userName', 'email', 'followersCount', and optional 'interests'. The application needs to perform fast point reads by 'userName' (under 10 ms) and also run queries to find all users with a 'followersCount' greater than a certain value. The development team prefers to use a query syntax similar to SQL. Which Azure Cosmos DB API should they choose?

Accepted Answer

Azure Cosmos DB for NoSQL (SQL API). Azure Cosmos DB for NoSQL (SQL API) is the correct choice because it natively supports SQL-like query syntax for querying JSON documents, enabling the required queries such as filtering by 'followersCount'. It also provides fast point reads (under 10 ms) by using the 'userName' field as the partition key, ensuring efficient direct access to individual documents.

Answer

Azure Cosmos DB for MongoDB

Answer

Azure Cosmos DB for Table

Answer

Azure Cosmos DB for Apache Cassandra

Question 10

A retail company stores three types of customer data: (1) a table with columns for CustomerID, Name, and Email; (2) product reviews as JSON documents with varying fields such as rating and comment; (3) product demonstration videos stored in MP4 format. Which of the following correctly classifies these data types in order from first to third?

Accepted Answer

Structured, semi-structured, unstructured. The customer table with fixed columns (CustomerID, Name, Email) is structured data, product reviews as JSON documents with varying fields are semi-structured data (schema-on-read, flexible fields), and MP4 video files are unstructured data (no schema, binary format). This ordering matches the standard classification in Azure Data Fundamentals: structured → semi-structured → unstructured.

Answer

Structured, unstructured, semi-structured

Answer

Semi-structured, structured, unstructured

Answer

Unstructured, semi-structured, structured

Question 11

A company uses Azure SQL Database for an e-commerce application. The Orders table contains columns: OrderID (int, primary key), CustomerID (int), OrderDate (datetime), TotalAmount (decimal). Queries frequently filter by both CustomerID and OrderDate to retrieve orders for a specific customer within a date range. Which indexing strategy will most improve the performance of these queries?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate).. A nonclustered index on (CustomerID, OrderDate) directly supports the query filter that uses both columns. The index is ordered by CustomerID first, enabling SQL Server to quickly locate all rows for a specific customer, and then within that customer, the OrderDate column is ordered to efficiently scan the date range. This index covers the query's WHERE clause without needing to scan the entire table.

Answer

Create a clustered index on OrderID.

Answer

Create a nonclustered index on (OrderDate, CustomerID).

Answer

Create a nonclustered index on TotalAmount.

Question 12

A mobile gaming company stores player profiles in Azure Cosmos DB. Each profile document contains many optional fields, and queries frequently filter by the player's locale (a field present in about 30% of documents). Which approach will optimize query performance for these filters?

Accepted Answer

Create a composite index that includes the locale field. Creating a composite index that includes the locale field allows Azure Cosmos DB to efficiently filter queries by locale without scanning every document. Since locale is present in only 30% of documents, a composite index reduces the query RU cost by directly locating matching documents, leveraging the index's sorted structure for faster lookups.

Answer

Embed all fields in a single document and rely on automatic indexing

Answer

Normalize the data by storing locale in a separate container and use cross-container queries

Answer

Define a fixed schema for all documents to ensure every document has the locale field

Question 13

Refer to the exhibit. You are reviewing an ARM template that deploys a SQL database in Azure Synapse. The template sets the storageAccountType to GRS. What is a valid concern regarding cost and performance?

Accepted Answer

GRS will increase storage costs and may cause higher latency. Geo-redundant storage (GRS) replicates your data to a secondary region, which increases storage costs because you are paying for both the primary and secondary copies. Additionally, when using GRS with Azure Synapse SQL, read requests may experience higher latency if they are directed to the secondary region, especially during a failover scenario or when using read-access geo-redundant storage (RA-GRS). This makes cost and performance valid concerns when choosing GRS over locally redundant storage (LRS).

Answer

The collation setting is not compatible with Azure Synapse

Answer

The database cannot be part of a failover group

Answer

The database will not support Transparent Data Encryption

Question 14

A company plans to migrate an on-premises SQL Server database to Azure. The database uses SQL Server Agent to run scheduled jobs and performs cross-database queries within the same instance. The company wants a fully managed PaaS solution that requires minimal application changes. Which Azure SQL deployment option should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides full SQL Server Agent support for scheduled jobs and enables cross-database queries within the same instance, while being a fully managed PaaS service. It offers near 100% compatibility with on-premises SQL Server, minimizing application changes during migration.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database elastic pool

Answer

SQL Server on Azure Virtual Machines

Question 15

A mobile gaming startup needs to store player profiles that can have varying attributes (e.g., some players have a 'nickname', others have 'avatar URL'). The application must read a player's profile by PlayerID with very low latency (under 10 ms) from any location worldwide. The data does not require complex queries or joins. Which Azure data store should they choose?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it is a globally distributed, multi-model database service that guarantees single-digit-millisecond read latencies (under 10 ms) at any scale from any Azure region. Its schema-agnostic nature allows storing player profiles with varying attributes (e.g., nickname, avatar URL) without requiring a fixed schema, and it supports point reads by PlayerID with a consistency model that can be tuned for performance. This directly matches the requirements of low-latency global reads and flexible, non-relational data.

Answer

Azure SQL Database

Answer

Azure Table Storage

Answer

Azure Blob Storage

Question 16

A media company stores video metadata in Azure Table Storage. Each video has a unique VideoID, and the application frequently queries for videos uploaded on a specific date. The current table uses PartitionKey = VideoID and RowKey = UploadDate. Queries filtering by UploadDate are slow and consume many transactions. Which design change will most optimize queries that retrieve all videos from a given date?

Accepted Answer

C. Change the PartitionKey to a date-based value (e.g., YYYY-MM-DD) and use VideoID as the RowKey.. Azure Table Storage queries are most efficient when the PartitionKey is used as the primary filter. By changing the PartitionKey to a date-based value (e.g., YYYY-MM-DD), queries for all videos uploaded on a specific date become partition scans, which are fast and consume minimal transactions. Using VideoID as the RowKey still allows unique identification of each video within that date partition.

Answer

A. Use UploadDate as the RowKey only, but keep PartitionKey as VideoID.

Answer

B. Create a secondary index on UploadDate.

Answer

D. Migrate the data to Azure Cosmos DB Table API for better indexing.

Question 17

A retail chain collects daily sales data from hundreds of stores. The data is stored as CSV files in Azure Data Lake Storage Gen2. The analytics team needs to run complex SQL queries that join sales data with product dimensions and aggregate results across petabytes of data. Queries must return results within seconds. Which Azure service is best suited for this analytical workload?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL Data Warehouse) is the correct choice because it is a distributed query engine designed for petabyte-scale data warehousing. It uses Massively Parallel Processing (MPP) to distribute data across compute nodes, enabling complex SQL joins and aggregations on data stored in Azure Data Lake Storage Gen2 to return results in seconds via its SQL pool or serverless SQL endpoint.

Answer

Azure SQL Database

Answer

Azure Analysis Services

Answer

Azure HDInsight

Question 18

A company uses Azure Data Lake Storage Gen2 for a data lake. They implement a folder structure with access control lists (ACLs). A new data scientist needs to read data from a specific folder but not write to it. Which ACL permission should be assigned?

Accepted Answer

Execute. Execute (X) permission on a folder in Azure Data Lake Storage Gen2 is required to traverse the folder and access its contents. Without Execute, a user cannot list or read files inside the folder, even if Read permission is granted. Since the data scientist only needs to read data (not write), assigning Execute on the folder and Read on the files allows traversal and read access without write capability.

Answer

Modify

Answer

Write

Answer

Read

Question 19

A financial services company has raw transaction data stored in Azure Data Lake Storage Gen2 (ADLS Gen2) as Parquet files, partitioned by date. The analytics team needs to run complex SQL queries that join multiple datasets, including reference data from an Azure SQL Database, to generate risk reports. They require enterprise-grade security features such as row-level security (RLS) and column-level security. They also want to use the same service for data transformation and loading (ETL) into a curated layer. Which Azure service should they choose?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that combines enterprise data warehousing with big data analytics. It directly supports complex SQL queries across multiple datasets (including Parquet files in ADLS Gen2 and Azure SQL Database), offers built-in row-level security (RLS) and column-level security for enterprise-grade access control, and includes a built-in pipeline orchestration engine (via Synapse Pipelines) for ETL/ELT transformations into a curated layer. This single service eliminates the need to stitch together separate tools for querying, security, and data transformation.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Analysis Services

Question 20

A manufacturing company deploys IoT sensors on equipment in a factory. They need to monitor sensor data in real time to detect anomalies and trigger immediate alerts. They also need to store years of historical sensor data for monthly capacity planning reports that involve complex aggregations. The company wants a cost-effective solution that minimizes data movement between storage and compute. Which combination of Azure services should they use for real-time processing and historical batch analytics?

Accepted Answer

A. Azure Stream Analytics for real-time processing, Azure Data Lake Storage Gen2 for historical storage, and Azure Synapse Analytics for batch queries.. Azure Stream Analytics is purpose-built for real-time processing of streaming data from IoT sensors, enabling immediate anomaly detection and alerting. Azure Data Lake Storage Gen2 provides cost-effective, scalable storage for years of historical sensor data, while Azure Synapse Analytics (formerly SQL Data Warehouse) can run complex aggregations directly against that data without moving it, minimizing data movement and cost.

Answer

B. Azure Data Factory for real-time processing, Azure Cosmos DB for historical storage, and Power BI for batch queries.

Answer

C. Azure Functions for real-time processing, Azure Table Storage for historical storage, and Azure Analysis Services for batch queries.

Answer

D. Azure Event Hubs for real-time processing, Azure SQL Database for historical storage, and Azure Machine Learning for batch queries.