DP-900 Practice Test 17 — 15 Questions

Question 1

A gaming company stores player session data as JSON documents. Each document contains fields like sessionId, userId, startTime, and a varying set of optional fields such as deviceType or campaignId. The application needs to query sessions by userId and startTime range using SQL-like queries, and also by sessionId with low latency. Which Azure Cosmos DB API should the company choose?

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

Accepted Answer

SQL (Core) API. The SQL (Core) API is the correct choice because it natively supports SQL-like queries over JSON documents, enabling efficient filtering by userId and startTime range. It also provides low-latency point reads by sessionId when a well-designed partition key (e.g., /userId) is used, and it offers automatic indexing of all JSON properties, including optional fields like deviceType or campaignId.

Answer

MongoDB API

Answer

Table API

Answer

Gremlin (Graph) API

Question 2

A company stores customer names and addresses in a relational table, product descriptions as JSON files, and product images as JPEG files. Which of the following correctly classifies these data types from most structured to least structured?

Accepted Answer

Structured (customer table), Semi-structured (JSON), Unstructured (JPEG). A is correct because structured data (customer table) has a fixed schema with rows and columns, semi-structured data (JSON) uses tags or key-value pairs without a rigid schema, and unstructured data (JPEG) has no predefined structure. The question tests the standard classification hierarchy from most to least structured.

Answer

Structured (customer table), Unstructured (JSON), Semi-structured (JPEG)

Answer

Semi-structured (customer table), Structured (JSON), Unstructured (JPEG)

Answer

Unstructured (customer table), Structured (JSON), Semi-structured (JPEG)

Question 3

A company uses Azure Synapse Analytics to run large-scale batch processing jobs every night. The jobs currently take 6 hours to complete, but the business requires completion within 4 hours. Which action should the company take to improve job performance?

Accepted Answer

Increase the data warehouse units (DWUs) for the dedicated SQL pool.. Increasing the data warehouse units (DWUs) for the dedicated SQL pool scales the compute resources (CPU, memory, and I/O bandwidth) available to the Synapse SQL pool. This directly reduces the execution time of batch processing jobs by allowing more parallel processing, enabling the 6-hour job to complete within the required 4-hour window.

Answer

Replace PolyBase with Azure Data Factory for data movement.

Answer

Migrate from serverless SQL pool to dedicated SQL pool.

Answer

Move the underlying data to Azure Data Lake Storage Gen2.

Question 4

A data engineer needs to load 500 GB of CSV files from an on-premises server into Azure Data Lake Storage Gen2 daily. The data must be transferred securely over the internet. Which Azure tool should they use?

Accepted Answer

AzCopy. AzCopy is the correct tool because it is a command-line utility designed for high-performance, secure copying of data to and from Azure Blob Storage and Azure Data Lake Storage Gen2. It supports the required 500 GB daily transfer over the internet using HTTPS encryption, and can be scripted for automation without the overhead of a full orchestration service.

Answer

Azure Data Factory

Answer

Azure PowerShell

Answer

Azure Import/Export service

Question 5

A company uses Azure SQL Database and needs to implement data masking for sensitive columns like email addresses and credit card numbers, so that only authorized users can see the actual data. Which feature should they configure?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct feature because it limits exposure of sensitive data by obfuscating columns (e.g., email addresses, credit card numbers) in query results to non-privileged users, while authorized users (with EXEMPT or UNMASK permission) see the actual data. This directly meets the requirement of masking sensitive columns without altering the underlying stored data.

Answer

Row-Level Security

Answer

Auditing

Answer

Transparent Data Encryption (TDE)

Question 6

You are a data engineer for a large e-commerce company. The company uses Azure Data Lake Storage Gen2 to store customer transaction data. They also use Azure Databricks for data transformation and Azure Synapse Serverless SQL pool for ad-hoc queries. Recently, the data lake has grown to 10 TB, and query performance in Synapse Serverless has degraded significantly. Users complain that queries that used to take seconds now take minutes. You need to improve query performance without moving data to a dedicated SQL pool. The data is stored in Parquet format, partitioned by date. You notice that the queries often filter on CustomerID and Date. Current queries scan all partitions even when only a few days are needed. What is the most effective solution to improve performance?

Accepted Answer

Repartition the Parquet files by both date and CustomerID, and optimize file sizes to 1 GB each. Repartitioning the Parquet files by both date and CustomerID enables partition pruning in Azure Synapse Serverless SQL pool. When queries filter on CustomerID and Date, the engine can skip irrelevant partitions entirely, drastically reducing the amount of data scanned. Optimizing file sizes to around 1 GB ensures efficient parallelism and avoids the overhead of many small files, which degrades performance in a serverless environment.

Answer

Create materialized views in the Serverless SQL database on the partitioned data

Answer

Convert all Parquet files to CSV and use row-level security to limit data access

Answer

Increase the service level of the Synapse workspace to improve query concurrency

Question 7

Match each Azure storage redundancy option to its description.

Accepted Answer

LRS: Data is replicated three times within a single datacenter in the primary region.. Azure storage redundancy options differ in durability and availability. LRS is cost-effective, ZRS protects against zone failures, GRS adds geo-replication, and RA-GRS enables read access to the secondary region.

Answer

LRS: Data is replicated across multiple geographical regions.

Answer

ZRS: Data is replicated within a single datacenter.

Question 8

A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries frequently filter on OrderDate and OrderStatus, and sort by OrderDate descending. Which indexing strategy will most improve query performance?

Accepted Answer

Create a non-clustered index on (OrderDate DESC, OrderStatus) and keep the existing clustered index on OrderID. Creates a covering index for the most common query pattern: filtering on OrderDate and OrderStatus, and sorting by OrderDate descending. By specifying DESC in the index key, the index is ordered in the same direction as the sort, allowing SQL Server to avoid a sort operation and retrieve rows in order directly from the index. This non-clustered index can satisfy the query entirely without touching the clustered index (OrderID), reducing I/O and improving performance.

Answer

Create a clustered index on OrderDate and a non-clustered index on OrderStatus

Answer

Create a non-clustered index on (OrderDate, OrderStatus) and keep the existing clustered index on OrderID

Answer

Create a clustered index on OrderID and a non-clustered index on (OrderStatus, OrderDate)

Question 9

A healthcare organization stores patient records in a relational database table with fixed columns for PatientID, Name, and DateOfBirth. Additionally, they store clinical notes as free-form text files for each patient visit. Which statement correctly classifies these data types?

Accepted Answer

Patient records are structured data, and clinical notes are unstructured data.. Patient records stored in a relational database table with fixed columns (PatientID, Name, DateOfBirth) conform to a predefined schema, making them structured data. Clinical notes stored as free-form text files lack a fixed schema or organization, which classifies them as unstructured data. Option B correctly identifies this distinction.

Answer

Both patient records and clinical notes are examples of unstructured data.

Answer

Both patient records and clinical notes are examples of structured data.

Answer

Patient records are unstructured data, and clinical notes are semi-structured data.

Question 10

A small business wants to use Azure to store and analyze customer feedback from surveys. The surveys are collected via a web app and stored as JSON files. The business needs to run SQL-based queries on the data and generate reports in Power BI. They have a limited budget and prefer a serverless option to minimize management overhead. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool is the correct choice because it allows you to query JSON files directly from Azure Data Lake Storage or Blob Storage using standard T-SQL, without provisioning any infrastructure. It is serverless (pay-per-query), supports SQL-based queries, and integrates seamlessly with Power BI for reporting, making it ideal for a small business with a limited budget and minimal management overhead.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database

Question 11

You are reviewing the Azure Data Factory mapping data flow configuration above. Which transformation is missing to ensure that only sales from the current year are loaded?

Accepted Answer

Filter transformation. The Filter transformation is used in mapping data flows to restrict rows based on a condition. To load only sales from the current year, you would apply a filter condition such as `year(SalesDate) == year(currentDate())`, which removes all rows not matching the current year. This is the correct transformation for row-level filtering.

Answer

Derived column transformation

Answer

Aggregate transformation

Answer

Window transformation

Question 12

Your company has a data pipeline in Azure Data Factory that runs daily. Recently, the pipeline started failing with timeouts. You suspect a downstream database is slow. What should you do to monitor and alert on pipeline run duration?

Accepted Answer

Configure alerts in Azure Monitor based on pipeline run duration. Azure Monitor can be configured to create alerts based on metrics such as pipeline run duration. When the duration exceeds a threshold, an alert triggers, allowing proactive notification of slow downstream databases. This directly addresses the need to monitor and alert on pipeline performance issues.

Answer

Query the pipeline runs in Log Analytics

Answer

Check Azure Service Health for issues

Answer

Use Azure Advisor to check performance recommendations

Question 13

You are designing a data pipeline for a social media analytics platform. The pipeline needs to ingest posts from multiple sources (Twitter, Facebook) in real time, transform the data by adding sentiment scores, and store the results in a data store for later analysis. The transformation logic is simple and can be expressed as a SQL query. You want to minimize coding effort. Which Azure service should you use for the transformation step?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time data processing with SQL-like query language, allowing you to transform streaming data (e.g., from Twitter and Facebook) by adding sentiment scores using simple SQL expressions without writing custom code. It integrates natively with Azure Event Hubs or IoT Hub for ingestion and outputs to Azure SQL Database, Cosmos DB, or Blob Storage for analysis, minimizing coding effort.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Functions

Question 14

Your organization uses Azure Data Lake Storage Gen2 as a data lake. You need to enforce data retention policies automatically, such as deleting files older than 90 days. Which Azure feature should you use?

Accepted Answer

Azure Blob Storage lifecycle management. Azure Blob Storage lifecycle management allows you to define rules that automatically delete or tier blobs based on age. Since Azure Data Lake Storage Gen2 is built on top of Azure Blob Storage, you can use lifecycle management policies to delete files older than 90 days by setting a 'Delete blob' action with a 'daysAfterModificationGreaterThan' filter of 90.

Answer

Azure Policy

Answer

Azure Data Factory

Answer

Azure RBAC

Question 15

You need to migrate an on-premises SQL Server database to Azure. The database uses many stored procedures and CLR assemblies. Which Azure service is most compatible without requiring major application changes?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance (D) is the most compatible choice because it provides near 100% compatibility with the on-premises SQL Server engine, including full support for CLR assemblies and stored procedures. Unlike Azure SQL Database, Managed Instance does not require rewriting or refactoring code that relies on SQL Server Agent, cross-database queries, or CLR integration, making it ideal for lift-and-shift migrations without major application changes.

Answer

Azure Database for MySQL

Answer

Azure Virtual Machines with SQL Server

Answer

Azure SQL Database