DP-900 Practice Test 38 — 15 Questions

Question 1

A company plans to migrate an on-premises SQL Server database to Azure. The database currently uses SQL Server Agent jobs for scheduled maintenance tasks, cross-database queries, and query store for performance tuning. The database size is 500 GB and needs to scale to 10 TB eventually. They want a managed service that requires minimal application changes. Which Azure relational database service should they choose?

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

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, cross-database queries, and Query Store, while being a fully managed PaaS service. It allows scaling up to 10 TB (up to 16 TB with some configurations) with minimal application changes, as it uses the same T-SQL surface area and network configuration (VNet) as on-premises SQL Server.

Answer

Azure SQL Database (single database)

Answer

Azure SQL Database Hyperscale

Answer

Azure Database for SQL Server

Question 2

A company is designing a multi-tenant SaaS application. Each tenant has its own relational database, but the total number of tenants is expected to grow rapidly. The company wants to manage all databases efficiently and optimize costs by sharing resources among tenants with low usage. What should they use?

Accepted Answer

Azure SQL Database elastic pools. Elastic pools in Azure SQL Database allow sharing resources among multiple databases, optimizing cost for low-usage tenants in a multi-tenant SaaS application. Option A (SQL Server on Azure Virtual Machines) requires manual resource management and does not offer elastic pools. Option B (Azure SQL Managed Instance) is designed for single large databases, not for sharing resources across many databases. Option C (Azure Database for MySQL) does not have elastic pools; elastic pools are a feature of Azure SQL Database.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Answer

Azure Database for MySQL with elastic pools

Question 3

A data analyst needs to create a real-time dashboard in Power BI that refreshes every second from an Azure Stream Analytics job. Which Power BI feature should they use?

Accepted Answer

Streaming dataset. B is correct because a streaming dataset in Power BI is designed to ingest real-time data from sources like Azure Stream Analytics and automatically update visuals as new data arrives. This feature supports push-based updates at sub-second intervals, making it ideal for a dashboard that refreshes every second without requiring manual or scheduled refresh cycles.

Answer

Scheduled refresh

Answer

DirectQuery

Answer

Import mode

Question 4

A financial services company stores petabytes of transaction data in Parquet format in Azure Data Lake Storage Gen2. Data analysts need to run complex SQL queries that join multiple large tables and aggregate billions of rows, with results expected within seconds. The company wants to use a massively parallel processing (MPP) engine that supports T-SQL and can be paused to reduce costs during off-hours. They also need native integration with Azure Data Factory and Power BI. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics (formerly SQL DW) is the correct choice because it provides a massively parallel processing (MPP) engine that distributes data across 60 distributions, enabling complex T-SQL queries on petabyte-scale Parquet data with results in seconds. It supports native T-SQL, can be paused to reduce costs during off-hours, and offers built-in integration with Azure Data Factory and Power BI through its SQL endpoints and linked service connectors.

Answer

Azure HDInsight

Answer

Azure Databricks

Answer

Azure SQL Database

Question 5

A company uses Azure Synapse Analytics to run a data warehouse. They need to load 500 GB of historical data from Azure Blob Storage into a staging table. They want the fastest load performance with minimal administrative overhead. Which method should they use?

Accepted Answer

Use PolyBase with the COPY INTO statement. PolyBase with the COPY INTO statement is the fastest method for loading large volumes of data into Azure Synapse Analytics because it leverages the Massively Parallel Processing (MPP) architecture to read data directly from Azure Blob Storage in parallel across all compute nodes, bypassing any single-node bottleneck. It also requires minimal administrative overhead as it is a native T-SQL command with automatic schema inference and no external tools or orchestration to manage.

Answer

Use SQL Server Integration Services (SSIS)

Answer

Use Azure Data Factory with Copy activity

Answer

Use the bcp utility

Question 6

A manufacturing company ingests real-time sensor data from factory equipment via Azure Event Hubs. The data is a continuous stream of measurements (sensorId, timestamp, value). Additionally, historical maintenance records are stored as CSV files in Azure Data Lake Storage Gen2. The operations team needs to join the streaming data with the historical records in near real-time to detect anomalies. They also need to run complex T-SQL queries on the combined dataset for ad-hoc analysis. Which Azure service should they use as the primary analytics platform?

Accepted Answer

Azure Synapse Analytics. Azure Synapse Analytics is the correct choice because it provides a unified analytics platform that can ingest real-time streaming data from Azure Event Hubs via its built-in pipeline or Spark Structured Streaming, and simultaneously query historical CSV files in Azure Data Lake Storage Gen2 using serverless SQL or dedicated SQL pools. This allows the operations team to join streaming and batch data in near real-time for anomaly detection, and also run complex T-SQL queries for ad-hoc analysis, all within a single service.

Answer

Azure Stream Analytics

Answer

Azure Data Factory

Answer

Azure HDInsight with Spark

Question 7

A company uses Azure Cosmos DB with the SQL API. They need to implement a data partitioning strategy to optimize query performance and avoid hot partitions. Which THREE practices should they follow?

Accepted Answer

Use a synthetic partition key if natural keys are not suitable. Option B is correct because when no natural partition key provides good distribution, a synthetic partition key (for example, concatenating multiple properties or appending a random/hashed suffix) creates a well-distributed key that spreads items and throughput across logical partitions. Option C is correct because monotonically increasing values (such as timestamps or sequential IDs) funnel all new writes into the last logical partition, creating a hot partition; avoiding them distributes write load evenly. Option E is correct because a high-cardinality partition key yields many distinct logical partitions, enabling even data and RU/s distribution and allowing queries that filter on the partition key to target a single partition efficiently. Option A is wrong because using one partition key for all items collapses the container into a single logical partition, guaranteeing a hot partition and capping scalability at 10 GB and 10,000 RU/s per logical partition. Option D is wrong because partition key size is not the driver of performance; the key's cardinality and access-pattern alignment matter, and Cosmos DB enforces a 2 KB limit on the partition key value rather than rewarding minimal size.

Answer

Use the same partition key for all items

Answer

Keep partition key values as small as possible

Question 8

You are designing a solution to store relational data that requires support for graph relationships and JSON queries. Which Azure service should you choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is the correct choice because it natively supports graph relationships through graph tables (NODE and EDGE tables) and JSON queries via built-in JSON functions like JSON_VALUE, JSON_QUERY, and OPENJSON. This makes it ideal for storing relational data that also needs to handle graph traversals and semi-structured JSON data without requiring a separate service.

Answer

Azure Cosmos DB

Answer

Azure Table Storage

Answer

Azure Database for PostgreSQL

Question 9

A healthcare organization uses Azure SQL Database to store patient records. To comply with HIPAA regulations, they need to encrypt sensitive columns (e.g., Social Security numbers) at rest and control access to the encryption keys. Which feature should they use?

Accepted Answer

Always Encrypted. Always Encrypted. Always Encrypted is a feature designed to protect sensitive data, such as Social Security numbers, by encrypting it at rest and in transit, with the encryption keys stored outside of Azure SQL Database, providing client-side key management. This satisfies the HIPAA requirement for encrypting sensitive columns and controlling access to keys.

Option A: Dynamic Data Masking obfuscates data from non-privileged users but does not encrypt the data; it can be reversed by privileged users.

Option B: Row-Level Security restricts access to rows based on user characteristics but does not encrypt columns.

Option D: Transparent Data Encryption (TDE) encrypts the entire database at rest but does not provide column-level encryption or client-side key control.

Answer

Dynamic Data Masking

Answer

Row-Level Security

Answer

Transparent Data Encryption (TDE)

Question 10

You are designing a data storage solution for a social media application that stores user profile pictures and uploaded photos. The solution must support high throughput and be optimized for reading and writing large binary objects. Which Azure data service should you recommend?

Accepted Answer

Azure Blob Storage. Azure Blob Storage is Microsoft's object storage service purpose-built for storing massive amounts of unstructured data such as images, videos, documents, and binary files. It offers high throughput via Block Blobs (optimized for streaming uploads) and supports hot/cool/archive tiers for cost optimization based on access patterns. For a social media app storing profile pictures and uploaded photos, Blob Storage provides the scalability, durability (11 nines), and HTTP/REST access needed for read/write of large binary objects.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 11

A data engineer needs to process streaming data from IoT devices in near real-time and store the results in Azure Cosmos DB. Which Azure service should they use for the stream processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed specifically for low-latency, near-real-time analytics on streaming data. It can ingest data from IoT devices via Event Hubs or IoT Hub, apply SQL-based transformations, and directly output the results to Azure Cosmos DB with millisecond latency, making it ideal for this scenario.

Answer

Azure Synapse Analytics

Answer

Azure Databricks

Answer

Azure Data Factory

Question 12

A data analyst needs to create interactive dashboards and reports from data stored in Azure Synapse Analytics. Which tool should they use?

Accepted Answer

Microsoft Power BI. Microsoft Power BI is the correct tool because it is designed specifically for creating interactive dashboards and reports from a wide range of data sources, including Azure Synapse Analytics. Power BI connects directly to Synapse SQL pools or serverless SQL endpoints using DirectQuery or import mode, enabling real-time visualizations and cross-filtering. This aligns with the requirement for interactive analytics, which is Power BI's core strength.

Answer

SQL Server Reporting Services (SSRS)

Answer

Microsoft Excel

Answer

Azure Data Studio

Question 13

You need to migrate an on-premises SQL Server database to Azure SQL Managed Instance with minimal downtime. Which tool should you use?

Accepted Answer

Azure Database Migration Service (DMS). Azure Database Migration Service (DMS) is designed for minimal-downtime migrations to Azure SQL Managed Instance. It supports online migration mode, which continuously replicates changes from the source SQL Server to the target while the source remains operational, allowing a cutover with minimal downtime. DMS handles schema and data migration and provides monitoring and validation.

Answer

Azure Data Factory

Answer

SQL Server Integration Services (SSIS)

Answer

BACPAC export and import

Question 14

A company receives real-time clickstream data from its website via Azure Event Hubs. They need to detect fraudulent clicks within seconds and also produce daily aggregate reports of visitor statistics for historical analysis. Which combination of Azure services should they use for the real-time detection and the daily aggregation, respectively?

Accepted Answer

Azure Stream Analytics for real-time detection; Azure Data Factory for daily aggregation. Azure Stream Analytics is purpose-built for real-time stream processing, making it ideal for detecting fraudulent clicks within seconds from Event Hubs. Azure Data Factory is a cloud-based ETL service that can orchestrate and execute daily aggregation jobs on historical data, such as producing visitor statistics reports from stored clickstream data.

Answer

Azure Databricks for both real-time detection and daily aggregation

Answer

Azure Synapse Analytics for real-time detection; Azure Blob Storage for daily aggregation

Answer

Azure Functions for real-time detection; Azure SQL Database for daily aggregation

Question 15

Refer to the exhibit. An administrator deploys this Azure Policy assignment. What is the most likely effect on storage account 'storage1'?

Accepted Answer

Public network access will be denied.. The Azure Policy assignment shown in the exhibit denies the creation or update of storage accounts that do not have public network access disabled. Since 'storage1' is subject to this policy, the policy will enforce the 'Deny' effect, preventing any configuration that allows public network access. If 'storage1' already exists and is compliant, it remains; if it is non-compliant, the policy will block changes that would enable public access, effectively denying public network access.

Answer

The storage account will be deleted.

Answer

Firewall rules will be added.

Answer

Soft Delete will be enabled.