DP-900 Practice Test 39 — 15 Questions

Question 1

Your team is migrating a data warehouse to Azure Synapse Analytics. You need to ensure that the data model supports both historical trend analysis and current-day reporting with minimal storage redundancy. Which table design pattern should you use?

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

Accepted Answer

Star schema with dimension and fact tables. The star schema is the correct choice because it separates business processes into fact tables (for measures like sales quantities) and dimension tables (for descriptive attributes like customer or date). This design directly supports both historical trend analysis (by joining facts with the date dimension) and current-day reporting (by filtering on the latest date) while minimizing storage redundancy through normalized dimensions. Azure Synapse Analytics is optimized for star schemas, leveraging columnstore indexes and distributed tables to accelerate such queries.

Answer

Single flat table containing all attributes

Answer

Wide table with repeated customer attributes per order

Answer

Highly normalized design with many tables

Question 2

A company has 12 SQL Server databases, each about 30 GB. The databases experience unpredictable load spikes during the day. The company wants to migrate to Azure SQL Database to reduce administrative overhead and optimize costs by sharing resources among the databases. Which deployment option should they choose?

Accepted Answer

Elastic pool. Elastic pools are designed to share resources (eDTUs or eVCores) among multiple databases with unpredictable, overlapping load spikes. By pooling resources, the company can optimize costs because databases do not all peak simultaneously, and the pool’s total resource allocation is lower than the sum of individual peak requirements. This reduces administrative overhead by providing a single management point for scaling and monitoring all databases in the pool.

Answer

Single database with provisioned DTU

Answer

Managed Instance

Answer

SQL Server on Azure Virtual Machine

Question 3

A company receives data from a point-of-sale system. Each row contains TransactionID, ProductID, Quantity, and Price. The data has a fixed schema and is stored in a table. How should this data be classified?

Accepted Answer

Structured data. The data has a fixed schema with clearly defined columns (TransactionID, ProductID, Quantity, Price) and each row follows the same structure, which is the definition of structured data. In Azure, this would map directly to a table in Azure SQL Database or a fixed-schema table in Azure Synapse Analytics. The rigid schema and consistent data types make it ideal for relational storage and querying.

Answer

Semi-structured data

Answer

Unstructured data

Answer

Transactional data

Question 4

You are creating an Azure SQL Database and need to connect using Microsoft Entra ID authentication. Which user type must you create in the database to represent the authenticated Microsoft Entra ID identity?

Accepted Answer

Contained database user mapped to a Microsoft Entra ID identity. To connect to Azure SQL Database using Microsoft Entra ID authentication, you must create a contained database user that is mapped to a Microsoft Entra ID identity (user, group, or service principal). This contained user resides in the database and is authenticated via Entra ID, allowing token-based or integrated authentication without a SQL login. The syntax is CREATE USER [name] FROM EXTERNAL PROVIDER.

Answer

SQL login with password

Answer

External user from Microsoft Entra ID

Answer

Database user without login

Question 5

A transportation company collects real-time GPS data from thousands of delivery vehicles. They need to process this streaming data to detect delays and generate alerts when a vehicle is behind schedule. Which Azure service should they use for the stream processing?

Accepted Answer

B. Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is a fully managed, real-time stream processing engine designed to handle high-velocity data from sources like IoT devices and GPS sensors. It can ingest streaming data from Azure Event Hubs or IoT Hub, apply SQL-based queries to detect patterns such as delays, and output alerts to sinks like Azure Functions or Power BI in near real-time.

Answer

A. Azure Data Factory

Answer

C. Azure Data Lake Analytics

Answer

D. Azure Analysis Services

Question 6

A software company develops a multi-tenant SaaS application. They deploy a separate Azure SQL Database for each tenant. The databases are small (2-5 GB) and have highly variable loads — some tenants use the app heavily during the day, others at night. The company wants to maximize resource utilization and minimize costs by allowing databases to share a pool of resources, while still maintaining a predictable performance per database. Which Azure SQL Database deployment option should they choose?

Accepted Answer

Elastic pool. C is correct because an elastic pool allows multiple Azure SQL databases with variable and unpredictable usage patterns to share a fixed pool of resources (eDTUs or vCores), maximizing resource utilization and minimizing cost. The pool provides a predictable performance per database through per-database min/max resource limits, which is ideal for the described multi-tenant SaaS scenario with small databases and highly variable loads.

Answer

Single database with DTU purchasing model

Answer

Single database with vCore purchasing model

Answer

Azure SQL Managed Instance

Question 7

A data engineer needs to build a data pipeline that runs daily to copy sales data from an on-premises SQL Server to Azure Synapse Analytics. Which Azure service should they use to orchestrate the pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based data integration service specifically designed to orchestrate and automate data pipelines. It supports scheduled triggers (e.g., daily runs) and provides native connectors to copy data from on-premises SQL Server (via Self-Hosted Integration Runtime) to Azure Synapse Analytics, making it the ideal tool for this ETL/ELT workload.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure HDInsight

Question 8

A company is evaluating Azure database services for two different workloads. Workload A processes high-volume, low-latency transactions such as order entry and payment processing, where each transaction updates a few rows. Workload B involves running complex aggregations on terabytes of historical sales data to generate monthly business intelligence reports. Which Azure service is best suited for each workload?

Accepted Answer

B. Workload A: Azure Cosmos DB; Workload B: Azure Synapse Analytics. Workload A requires a low-latency, high-throughput transactional database capable of handling many small, row-level updates. Azure Cosmos DB is a NoSQL database designed for single-digit millisecond latency and horizontal scaling, making it ideal for order entry and payment processing. Workload B involves complex aggregations on terabytes of historical data, which is best handled by Azure Synapse Analytics, a distributed analytics service that uses massively parallel processing (MPP) to run large-scale queries efficiently.

Answer

A. Workload A: Azure SQL Database; Workload B: Azure Cosmos DB

Answer

C. Workload A: Azure Synapse Analytics; Workload B: Azure SQL Database

Answer

D. Workload A: Azure Cosmos DB; Workload B: Azure Cosmos DB

Question 9

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 is the data transformation and mashup engine in Power BI that connects to multiple data sources, including Azure SQL Database and Azure Cosmos DB, and combines them into a single model. It supports scheduled refresh so the report can update daily. This is the correct feature for combining disparate sources.

Answer

Quick Measures

Answer

Data Analysis Expressions (DAX)

Answer

Power BI Desktop

Question 10

A DBA runs the following KQL query in Azure Monitor for an Azure SQL Database: `AzureDiagnostics | where Category == 'QueryStoreRuntimeStatistics'`. The query returns no results. What is the most likely reason?

Accepted Answer

Query Store is not enabled on the database. Query Store runtime statistics are only emitted to Azure Monitor's AzureDiagnostics table when Query Store is actually enabled on the database. If Query Store is off, no QueryStoreRuntimeStatistics records are generated, so the query legitimately returns zero rows even though the KQL syntax and category name are correct.

Answer

The AzureDiagnostics table does not contain SQL data

Answer

The category name is misspelled

Answer

The KQL syntax is incorrect

Question 11

A company stores sensor data in Azure Blob Storage. The data is appended every minute and rarely modified. The compliance team requires that blobs older than 90 days be moved to a more cost-effective storage tier, and blobs older than 365 days be deleted. Which solution should you recommend?

Accepted Answer

Configure a Blob Storage lifecycle management policy.. Azure Blob Storage lifecycle management policies let you define rules that transition blobs to cooler tiers (Cool, Cold, Archive) after a specified number of days and delete them after another threshold. This directly satisfies the 90-day tier move and 365-day deletion requirements without custom code.

Answer

Use Azure Backup to set a retention policy for the storage account.

Answer

Enable Blob Soft Delete and set retention days to 365.

Answer

Move the blobs to Azure Files and set a file retention policy.

Question 12

A company stores IoT sensor data in Azure Table Storage. The data is accessed frequently for the first 30 days, then rarely. You need to minimize storage costs while ensuring data is available for queries within 24 hours of a request. What should you implement?

Accepted Answer

Move data older than 30 days to Azure Blob Storage Cool tier and use an Azure Data Factory pipeline to copy data back to Table Storage when requested.. Azure Table Storage does not support tiering (Hot/Cool/Archive) — it is a single-tier, NoSQL key-attribute store. To reduce cost for cold data while keeping it queryable, the standard pattern is to offload aged data to Blob Storage Cool tier (cheaper per GB) and use Azure Data Factory to copy it back on demand, satisfying the 24-hour retrieval SLA. This matches the question's requirement of minimizing cost with delayed availability.

Answer

Configure a lifecycle management policy on the Table Storage account to move data to Cool tier after 30 days.

Answer

Store all data in Azure SQL Database and use index maintenance to improve query performance.

Answer

Migrate the data to Azure Cosmos DB and use Time-to-Live (TTL) to expire old data.

Question 13

Your application stores millions of small log entries in Azure Table Storage. Queries by partition key and row key are fast, but you also need to query by timestamp across partitions. The query performance is slow. What is the best way to improve query performance?

Accepted Answer

Create a new table with the timestamp as the partition key and copy data there for time-based queries.. Azure Table Storage only supports a single clustered index on PartitionKey + RowKey, so queries filtering by timestamp across partitions force a full table scan. Creating a second table with timestamp as the partition key and duplicating the data allows efficient point/range queries against that key. This is the standard 'index table' or 'materialized view' pattern for Table Storage, since it lacks native secondary indexes.

Answer

Migrate the data to Azure Cosmos DB and use the SQL API.

Answer

Use Azure Cognitive Search to index the Table Storage data.

Answer

Add a secondary index on the timestamp column.

Question 14

A healthcare organization is planning a data analytics platform. They will ingest data from various sources: structured patient records from a relational database, semi-structured JSON logs from medical devices, and unstructured physician notes as plain text files. Which characteristic of big data describes the different formats of data being ingested?

Accepted Answer

Variety. The question describes data in three distinct formats: structured (relational database), semi-structured (JSON logs), and unstructured (plain text). In big data terminology, 'Variety' specifically refers to the different types and formats of data being processed. This is a core concept in the 4 V's of big data, where Variety captures the heterogeneity of data sources and structures.

Answer

Volume

Answer

Velocity

Answer

Veracity

Question 15

You are designing a solution to store and analyze large volumes of streaming data from social media feeds. The data is semi-structured (JSON) and will be used for real-time dashboards. You need to choose a storage solution that can handle high-ingestion throughput and support querying with Azure Synapse Serverless SQL. Which storage option should you choose?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 is the correct choice because it is optimized for big data analytics, supports hierarchical namespace, and can store semi-structured data like JSON at scale. It integrates natively with Azure Synapse Serverless SQL, allowing direct querying of files using T-SQL. Its high-ingestion throughput and support for various file formats make it ideal for streaming data landing and analysis.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Cache for Redis