DP-900 Practice Test 50 — 15 Questions

Question 1

A company uses Azure SQL Database for a customer relationship management (CRM) application. The database has a table named Orders that stores order details. The company needs to ensure that the OrderDate column is automatically set to the current date and time when a new row is inserted, without any application-side logic. Which T-SQL construct should be used?

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

Accepted Answer

DEFAULT constraint with GETDATE(). A DEFAULT constraint with GETDATE() automatically populates the OrderDate column with the current date and time on each insert, without requiring application-side logic. Option A is incorrect because a CHECK constraint enforces data integrity by validating conditions but does not provide default values. Option B is incorrect because a UNIQUE constraint ensures all values in a column are unique, not auto-populate dates. Option C is incorrect because a PRIMARY KEY constraint uniquely identifies each row and does not set default values.

Answer

CHECK constraint

Answer

UNIQUE constraint

Answer

PRIMARY KEY constraint

Question 2

A gaming application stores player profiles as JSON documents. Each profile has standard fields like playerId, username, and email, but also optional fields such as achievements and gamePreferences. The application needs to query profiles by playerId with low latency and also run SQL-like queries to find players with specific achievements. Which Azure Cosmos DB API should they choose?

Accepted Answer

C: SQL (Core) API. The SQL (Core) API is the best choice because it natively supports JSON documents with flexible schemas (including optional fields like achievements and gamePreferences), provides low-latency point reads by playerId using the id field as the partition key, and enables SQL-like queries (e.g., SELECT * FROM c WHERE ARRAY_CONTAINS(c.achievements, 'specific_achievement')) without requiring a separate indexing or translation layer.

Answer

A: Table API

Answer

B: MongoDB API

Answer

D: Cassandra API

Question 3

Your organization has a data lake on Azure Data Lake Storage Gen2 containing petabytes of raw clickstream data. Data scientists need to run exploratory analysis using Python and Spark, but they are not experienced with cluster management or infrastructure. The IT team wants to minimize administrative overhead while providing a collaborative notebook environment. Additionally, the solution must integrate with Microsoft Purview for data cataloging and lineage. Which Azure service should you recommend?

Accepted Answer

Azure Databricks. Azure Databricks is the correct choice because it provides a fully managed, collaborative notebook environment optimized for Apache Spark, allowing data scientists to run Python and Spark-based exploratory analysis without managing clusters. It integrates natively with Azure Data Lake Storage Gen2 for accessing petabytes of clickstream data and supports Microsoft Purview for automated data cataloging and lineage tracking, minimizing administrative overhead.

Answer

Azure Data Science Virtual Machine

Answer

Azure Synapse Analytics (Synapse Studio)

Answer

Azure HDInsight (Spark cluster)

Question 4

A company maintains a database of customer orders that are updated frequently. They also store aggregated monthly sales reports that are generated once and then only read. Which statement correctly distinguishes these two types of data workloads?

Accepted Answer

Transactional data is optimized for write operations, and analytical data is optimized for read operations.. Transactional workloads (like the frequently updated customer orders) are optimized for write-heavy operations, ensuring ACID compliance and data integrity, while analytical workloads (like the read-only monthly sales reports) are optimized for read-heavy operations, often using columnar storage or pre-aggregated data to speed up queries. This distinction aligns with the core difference between OLTP (Online Transaction Processing) and OLAP (Online Analytical Processing) systems in Azure, such as Azure SQL Database for transactional data and Azure Synapse Analytics for analytical data.

Answer

Transactional data must always be stored in non-relational databases, and analytical data in relational databases.

Answer

Analytical data always requires real-time processing, whereas transactional data is batch-processed.

Answer

Transactional data is read-only and analytical data is frequently updated.

Question 5

A company runs a mission-critical SQL Server database on-premises. They plan to migrate to Azure SQL Database and need to choose the appropriate service tier. The database is currently 500 GB and is expected to grow to 8 TB within two years. The workload is read-heavy with many concurrent users, and they require fast scaling of compute resources without significant downtime. Which Azure SQL Database service tier should they choose?

Accepted Answer

Hyperscale. Hyperscale is the correct choice because it supports databases up to 100 TB, far exceeding the expected 8 TB growth, and provides fast scaling of compute resources without downtime by using a distributed architecture with separate compute and storage nodes. Its read-heavy workload with many concurrent users benefits from Hyperscale's multiple readable replicas and buffer pool extension, ensuring high performance and availability.

Answer

General Purpose

Answer

Business Critical

Answer

Serverless

Question 6

A company stores customer contact information in a table with columns for CustomerID, Name, Email, and Phone. They also store customer support chat transcripts as plain text files. Which of the following correctly classifies these data types?

Accepted Answer

Customer contact information is structured; chat transcripts are unstructured. Customer contact information stored in a table with columns like CustomerID, Name, Email, and Phone is structured data because it has a fixed schema with rows and columns. Chat transcripts stored as plain text files have no predefined schema or organization, making them unstructured data. Therefore, option C correctly classifies the contact info as structured and the chat transcripts as unstructured.

Answer

Both are structured data

Answer

Customer contact information is structured; chat transcripts are semi-structured

Answer

Both are semi-structured

Question 7

A media company stores video files in Azure Blob Storage. They want to use Azure Content Delivery Network (CDN) to serve these videos globally. However, they need to restrict access to only authorized users. What should you implement?

Accepted Answer

Implement shared access signatures (SAS) and token-based authentication on the CDN.. To restrict access to authorized users when serving video files from Azure Blob Storage via Azure CDN, you should implement token-based authentication using shared access signatures (SAS). SAS tokens grant time-limited, specific permissions to clients, and Azure CDN can be configured to require these tokens before delivering content. This ensures only authorized users can access the videos. Option A is incorrect because making the blob container public would allow unrestricted access to anyone. Option B is incorrect because Azure Active Directory authentication is not directly supported by Azure CDN for blob access; token-based authentication with SAS is the standard approach. Option D is incorrect because a storage account firewall restricting to CDN IP addresses only limits the network source, not individual user authorization; any request coming from the CDN IP would still be served without user-level authentication.

Answer

Set the blob container to public access.

Answer

Use Azure Active Directory (Microsoft Entra ID) authentication for the CDN endpoint.

Answer

Use a firewall on the storage account to allow only CDN IP addresses.

Question 8

A logistics company collects sensor data from delivery trucks. Each sensor sends a JSON message that includes a fixed set of core fields (truck ID, timestamp) but also includes optional fields such as temperature, humidity, and engine diagnostics depending on the sensor type. The JSON structure varies between messages. How should this data be classified?

Accepted Answer

Semi-structured data. The JSON messages contain a fixed set of core fields (truck ID, timestamp) but also include optional fields that vary per message, meaning the data has a flexible schema. This mixture of structured fields and variable attributes is the defining characteristic of semi-structured data, which does not require a rigid schema like a relational table but still has organizational properties (e.g., key-value pairs). In Azure, this type of data is commonly stored in services like Azure Cosmos DB or Azure Blob Storage with JSON format.

Answer

Structured data

Answer

Unstructured data

Answer

Relational data

Question 9

A manufacturing company stores IoT sensor data as JSON documents in Azure Cosmos DB. Each document contains a device ID, a timestamp, and a varying set of sensor readings. The application frequently queries data by device ID and a time range to retrieve all readings for a specific device over a period. The development team wants to use an API that supports SQL-like queries on this JSON data. Which Azure Cosmos DB API should they choose?

Accepted Answer

Azure Cosmos DB Core (SQL) API. The Azure Cosmos DB Core (SQL) API is the correct choice because it natively supports querying JSON documents using SQL-like syntax, which aligns with the requirement to run SQL-like queries on JSON data. This API provides a rich query language for filtering by device ID and timestamp ranges, making it ideal for the described IoT scenario where documents have varying sensor readings.

Answer

Azure Cosmos DB MongoDB API

Answer

Azure Cosmos DB Cassandra API

Answer

Azure Cosmos DB Gremlin API

Question 10

A data engineering team is building a batch analytics pipeline. Raw clickstream data is stored as Parquet files in Azure Data Lake Storage Gen2. The team needs to transform the data using Apache Spark (Python code) and then load the results into Azure Synapse Analytics for high-performance reporting. They want to use a serverless compute option for Spark to avoid managing clusters. Which combination of Azure services should they use for the transformation and loading?

Accepted Answer

Use Azure Synapse Analytics serverless Spark pools for transformations and load into the Synapse dedicated SQL pool.. Azure Synapse Analytics serverless Spark pools provide a serverless compute option for running Apache Spark transformations without managing clusters, and the transformed data can be directly loaded into the Synapse dedicated SQL pool for high-performance reporting. This combination meets all requirements: serverless Spark for transformations, and Synapse dedicated SQL pool for optimized analytics workloads.

Answer

Use Azure Databricks with a serverless cluster for transformations and load into Azure SQL Database.

Answer

Use Azure Data Factory with a Spark activity to run transformations and load into Azure Synapse Analytics.

Answer

Use Azure HDInsight with Apache Spark for transformations and load into Azure Blob Storage.

Question 11

A company uses Azure Synapse Analytics dedicated SQL pool to store a large fact table with billions of rows. The table is distributed using hash distribution on the SaleDate column. Queries that join this fact table with a small dimension table (Product) on ProductID are slow because the join requires shuffling data across distributions. Which design change would most improve the performance of these join queries?

Accepted Answer

Replicate the Product dimension table to all distributions.. Replicating the Product dimension table to all distributions eliminates the need to shuffle data across distributions during the join. In Azure Synapse dedicated SQL pool, hash distribution distributes rows across 60 distributions based on the hash of the distribution column (SaleDate). When joining on ProductID, which is not the distribution column, data must be moved between distributions. Replicating the small dimension table ensures each distribution has a local copy, allowing the join to be performed without data movement, significantly improving performance.

Answer

Change the distribution of the fact table to round-robin.

Answer

Partition the fact table by SaleDate.

Answer

Create a nonclustered index on ProductID in the fact table.

Question 12

A company uses Azure SQL Database for an e-commerce platform. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and a range of OrderDate, and then sort the results by OrderDate in descending order. The queries also return the TotalAmount column. Which indexing strategy will most improve query performance for these operations?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) with included column TotalAmount. It creates a covering index that matches the query's filter predicates (CustomerID equality, OrderDate range) and sort order (OrderDate DESC). By including TotalAmount as an included column, the index fully satisfies the query without needing to access the clustered index (key lookup), minimizing I/O and improving performance.

Answer

Create a nonclustered index on (OrderDate DESC, CustomerID) with included column TotalAmount

Answer

Change the clustered index to be on (CustomerID, OrderDate DESC)

Answer

Create a nonclustered index on (OrderDate DESC) without including TotalAmount

Question 13

A financial services company runs a critical application on Azure SQL Managed Instance. They need to ensure that in the event of a regional outage, the database can be failed over to a secondary region with minimal data loss and automatic failover. The secondary region should not be used for read traffic during normal operations. Which configuration meets these requirements?

Accepted Answer

Configure a failover group with automatic failover policy and set the secondary to non-readable. A failover group with an automatic failover policy and a non-readable secondary meets all requirements: it provides automatic failover to a secondary region during an outage, minimizes data loss through synchronous replication (within the same region) and asynchronous replication across regions, and ensures the secondary is not used for read traffic during normal operations by disabling the readable secondary option.

Answer

Create an automatic failover group with readable secondary enabled

Answer

Use Azure SQL Database backup to blob storage and restore to another region

Answer

Implement active geo-replication and manually initiate failover

Question 14

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 15

A data analyst needs to create a report in Power BI that combines sales data from Azure SQL Database and inventory data from Azure Cosmos DB. The report should refresh daily. Which Power BI feature should be used to combine these data sources?

Accepted Answer

Power Query. Power Query allows connecting to multiple data sources (like Azure SQL Database and Azure Cosmos DB) and combining them through merge or append queries. This is the correct feature for combining data from different sources. Option A (Quick Measures) is for creating quick calculations within a single table, not for combining sources. Option B (DAX) is a formula language used for creating calculated columns or measures, not for data ingestion or combining sources. Option C (Power BI Desktop) is the application itself, not a specific feature for combining data.

Answer

Quick Measures

Answer

Data Analysis Expressions (DAX)

Answer

Power BI Desktop