DP-900 Practice Exam 53 — 20 Questions

Question 1

A company has a suite of 20 e-commerce applications, each with its own SQL Server database. The databases vary in size from 5 GB to 100 GB and have unpredictable usage patterns with bursty peaks. The company wants to migrate to Azure SQL Database to benefit from built-in high availability and automatic backups. They need to minimize costs by only paying for the resources each database actually uses, and they want to avoid over-provisioning for peak loads. Which Azure SQL Database deployment option should they choose?

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

Accepted Answer

Azure SQL Database Elastic Pool. Azure SQL Database Elastic Pool is the correct choice because it allows multiple databases to share a fixed pool of resources (DTUs or vCores), enabling cost efficiency by pooling and reallocating resources across databases with unpredictable, bursty usage patterns. This avoids over-provisioning for peak loads while still providing built-in high availability and automatic backups, as each database in the pool benefits from these features without needing individual resource reservations.

Answer

Azure SQL Database (single database) with Serverless compute tier

Answer

Azure SQL Managed Instance

Answer

Azure SQL Database Hyperscale

Question 2

A retail company is designing a product catalog for its e-commerce website. Each product has a unique ProductID, a name, a price, and a variable number of attributes (e.g., size, color, weight) that differ across product categories. The application requires ability to read a product's details by ProductID with single-digit millisecond latency from any Azure region globally. The schema must be flexible to accommodate new attributes without schema changes. Which Azure data store should the company choose?

Accepted Answer

Azure Cosmos DB using the NoSQL API. Azure Cosmos DB with the NoSQL API is correct because it provides a fully managed, globally distributed NoSQL database that supports flexible schemas (allowing variable product attributes without schema changes) and guarantees single-digit millisecond read latency at any scale from any Azure region via its multi-region write and read replicas. The unique ProductID serves as a natural partition key, enabling efficient point reads with consistent low latency.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 3

A company plans to migrate a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance and requires automatic failover across Azure regions. The company wants a fully managed service with minimal application changes. Which Azure SQL service should they choose?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is correct because it provides near 100% compatibility with SQL Server, including support for SQL Server Agent jobs, and offers automatic failover across Azure regions via failover groups. It is a fully managed service that requires minimal application changes, unlike Azure SQL Database which lacks SQL Server Agent and has limited cross-region failover capabilities.

Answer

Azure SQL Database

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Synapse Analytics

Question 4

You are a data engineer for a financial services company. The company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. They have a fact table named Transactions that contains 2 billion rows. The table is hash-distributed on the AccountID column. Users run reports that aggregate transaction amounts by date and account type. The reports are slow. Upon investigation, you find that the distribution is highly skewed because a few accounts have millions of transactions. You need to improve query performance without redesigning the entire schema. Which action should you take?

Accepted Answer

Change the distribution key to a column with more unique values, such as TransactionID. Changing the distribution key to TransactionID, which has far more unique values than AccountID, will eliminate the data skew that is causing performance degradation. In a hash-distributed table, a skewed distribution key leads to some distributions holding a disproportionate amount of data, causing parallel query execution to be bottlenecked by the largest distribution. By using a highly unique column like TransactionID, the data will be evenly distributed across all 60 distributions, enabling balanced parallelism and faster aggregation queries.

Answer

Change the distribution to round-robin

Answer

Create a clustered columnstore index on the table

Answer

Replicate the Transactions table to all distributions

Question 5

A retail company wants to run real-time analytics on streaming clickstream data from their website. Which Azure service should they use to ingest and process the data?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is a real-time analytics and event-processing engine designed to ingest, process, and analyze high-velocity streaming data, such as clickstream data from a website. It can directly consume data from Azure Event Hubs or IoT Hub and output results to sinks like Power BI, Azure SQL Database, or Azure Data Lake Storage, making it the correct choice for real-time analytics on streaming data.

Answer

Azure Analysis Services

Answer

Azure Data Lake Storage

Answer

Azure SQL Database

Question 6

A retail company uses Azure SQL Database to store transactional data. They need to ensure that reporting queries do not impact the performance of the transactional workload. Which solution should you recommend?

Accepted Answer

Configure a read replica in Azure SQL Database. A read replica in Azure SQL Database allows reporting queries to be offloaded to a read-only copy of the database, isolating them from the primary transactional workload. This ensures that reporting activities do not consume resources (CPU, IO, memory) on the primary instance, preventing performance degradation for transactional operations.

Answer

Increase the DTU or vCore limit of the database

Answer

Add indexes to the reporting tables

Answer

Partition the largest tables by date

Question 7

You are designing a data solution for a healthcare application that requires ACID transactions for patient records and needs to run complex analytics queries. Which combination of Azure services should you recommend?

Accepted Answer

Azure SQL Database for transactions, Azure Synapse Analytics for analytics. Azure SQL Database provides full ACID (Atomicity, Consistency, Isolation, Durability) transaction support, which is essential for healthcare patient records where data integrity is critical. Azure Synapse Analytics is a cloud-based analytics service that can run complex queries against large datasets, including those from Azure SQL Database, using its massively parallel processing (MPP) architecture. This combination allows transactional and analytical workloads to coexist without compromising performance or consistency.

Answer

Azure Cosmos DB for transactions, Power BI for analytics

Answer

Azure Database for MySQL for transactions, Azure Analysis Services for analytics

Answer

Azure Blob Storage for transactions, Azure Machine Learning for analytics

Question 8

A retail company wants to store product catalog data in a non-relational format. The data includes product ID, name, description, price, and an array of tags. The data is frequently updated and must support low-latency reads and writes at global scale. Which Azure service should they use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is a globally distributed NoSQL database service that supports low-latency reads and writes at global scale. It can store JSON documents with arrays, making it suitable for product catalog data that includes tags. Azure Table Storage is a key-value store but lacks global distribution and support for complex queries. Azure Blob Storage is designed for unstructured blob data, not transactional updates. Azure Cache for Redis is an in-memory cache, not a durable data store for product catalogs.

Answer

Azure Table Storage

Answer

Azure Blob Storage

Answer

Azure Cache for Redis

Question 9

You are a business analyst at a manufacturing company. The company uses Azure SQL Database to store production data. You need to create a Power BI report that shows real-time machine efficiency. The report must refresh every 5 minutes to show current metrics. You have been granted read-only access to the database. The database is under heavy load from transactional applications, and you want to minimize additional impact. Which approach should you take to create the report?

Accepted Answer

Use Power BI Import mode with incremental refresh policy to load only new data every 5 minutes. B is correct because Power BI Import mode with an incremental refresh policy allows you to load only new or changed data every 5 minutes, minimizing the load on the heavily used Azure SQL Database. This approach avoids repeated full table scans, reduces query impact, and still provides near-real-time metrics for the report.

Answer

Disable the Power BI Query Cache to reduce database load

Answer

Configure Power BI to use DirectQuery mode to ensure real-time data

Answer

Export the data to a CSV file and import it into Power BI daily

Question 10

A company uses Azure Data Lake Storage Gen2 to store IoT sensor data. The data is partitioned by date and sensor ID. A data scientist needs to efficiently query only the last 7 days of data for a specific sensor. Which strategy minimizes the amount of data scanned?

Accepted Answer

Use a directory structure that enables partition elimination. Azure Data Lake Storage Gen2 supports hierarchical directory structures that enable partition elimination at the storage layer. By organizing data under a path like `/sensorID=123/date=2025-03-20/`, a query engine (e.g., Azure Synapse Serverless SQL or Spark) can skip entire directories that do not match the filter, drastically reducing the amount of data scanned.

Answer

Create a view that filters on date and sensor ID

Answer

Read all Parquet files and filter using a WHERE clause

Answer

Create an index on the date and sensor ID columns

Question 11

Which TWO Azure services can be used to build a data pipeline that moves data from on-premises SQL Server to Azure Synapse Analytics?

Accepted Answer

Azure Data Factory. Azure Data Factory (A) is correct because it is a cloud-based ETL and data integration service that provides built-in connectors for both on-premises SQL Server (via self-hosted integration runtime) and Azure Synapse Analytics, enabling you to create, schedule, and orchestrate data pipelines that move and transform data between these sources.

Answer

Azure Machine Learning

Answer

Azure Stream Analytics

Answer

Azure Analysis Services

Question 12

Refer to the exhibit. A developer is creating an ARM template for an Azure Synapse workspace. What is the purpose of the 'defaultDataLakeStorage' property?

Accepted Answer

Defines the primary storage account for the workspace. The 'defaultDataLakeStorage' property in an ARM template for Azure Synapse Analytics defines the primary Azure Data Lake Storage Gen2 account that the workspace uses as its default storage. This storage account is where the workspace stores its data, including the data lake files and the metadata for the SQL and Spark engines. It is essential for the workspace to function, as it provides the underlying storage for tables, pipelines, and other workspace assets.

Answer

Sets the location for pipeline execution history

Answer

Specifies the storage account for Apache Spark logs

Answer

Configures the storage for SQL pool backups

Question 13

A company uses Azure Synapse Analytics to run both interactive queries and large batch loads. The interactive queries must have consistent performance regardless of batch load activity. Which Synapse feature should the company use?

Accepted Answer

Workload management with workload isolation.. Workload management with workload isolation in Azure Synapse Analytics allows you to reserve resources for specific workloads, such as interactive queries, ensuring they have consistent performance even when large batch loads are running. By creating a workload group with 'REQUEST_MIN_RESOURCE_PERCENT' set to a non-zero value, you guarantee a minimum amount of resources are always available for that group, preventing contention from other workloads.

Answer

Result-set caching for frequently run queries.

Answer

Materialized views for aggregate data.

Answer

Data compression with columnstore indexes.

Question 14

Which THREE components are typically part of a modern data warehouse architecture on Azure? (Choose three.)

Accepted Answer

Azure Data Factory. Azure Data Factory is correct because it serves as the cloud-based ETL (Extract, Transform, Load) service that orchestrates and automates data movement and transformation across various sources and destinations. In a modern data warehouse architecture, Data Factory is used to ingest raw data from on-premises or cloud sources, transform it using mapping data flows or external compute (e.g., Azure Databricks), and load it into the data warehouse or data lake for analytics. It provides a code-free visual interface or SDK-based control for scheduling and monitoring pipelines, making it essential for the ingestion and preparation layer.

Answer

Azure Stream Analytics

Answer

Azure Cosmos DB

Question 15

A company uses Azure Databricks for data engineering. They need to ensure that only authorized users can access the workspace, and they want to use single sign-on (SSO) with their existing identity provider. Which integration should they configure?

Accepted Answer

Microsoft Entra ID (Azure AD). Microsoft Entra ID (Azure AD) is the identity and access management service that provides SSO capabilities for Azure Databricks. By integrating Azure Databricks with Entra ID, you can enforce conditional access policies and authenticate users via your existing identity provider using protocols like SAML 2.0 or OAuth 2.0, ensuring only authorized users access the workspace.

Answer

Microsoft Defender XDR

Answer

Microsoft Intune

Answer

Azure Key Vault

Question 16

A company uses Azure Data Lake Storage Gen2 for a data lake. They need to ensure that only authorized users can access files and that access is audited. Which two Azure services should they combine? (Choose two options that together form the solution.)

Accepted Answer

Azure RBAC. Azure RBAC (Role-Based Access Control) is correct because it provides fine-grained access management for Azure Data Lake Storage Gen2, allowing you to assign roles (e.g., Storage Blob Data Contributor) to users, groups, or service principals to control who can read, write, or delete files. Azure Monitor is correct because it can collect and analyze activity logs and diagnostic settings for the storage account, enabling auditing of access events such as successful and failed authentication attempts.

Answer

Azure Policy

Answer

Azure Key Vault

Answer

Microsoft Entra ID

Question 17

A data engineer needs to build a pipeline that runs every hour, copies new sales data from an on-premises SQL Server to Azure Data Lake Storage Gen2, transforms the data using PySpark, and then loads it into Azure Synapse Analytics dedicated SQL pool. Which Azure service should be used to orchestrate the entire pipeline?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it is a cloud-based ETL and data integration service designed to orchestrate complex pipelines. It can copy data from on-premises SQL Server via a self-hosted integration runtime, trigger the pipeline on an hourly schedule, execute PySpark transformations in Azure Databricks or HDInsight, and load the results into Azure Synapse Analytics dedicated SQL pool—all within a single, managed orchestration workflow.

Answer

Azure Stream Analytics

Answer

Azure Logic Apps

Answer

Azure Databricks

Question 18

An organization uses Azure SQL Database and needs to maintain a copy of the database for read-only reporting without affecting the production workload. Which feature should they use?

Accepted Answer

Active geo-replication. Active geo-replication (Option C) creates a readable secondary replica of an Azure SQL Database in a different Azure region. This secondary replica is continuously updated asynchronously from the primary and can be used for read-only query workloads, offloading reporting traffic without impacting the production database's performance or transaction throughput.

Answer

Azure SQL Database read replica

Answer

Automated backups

Answer

Failover groups

Question 19

A company develops an IoT device registry that stores device metadata as JSON documents. Each device has a unique DeviceID, and the attributes vary per device type (e.g., sensors, actuators). The application requires low-latency reads by DeviceID and needs global distribution to support devices worldwide. Which Azure Cosmos DB API should they choose to natively support JSON documents with flexible schema?

Accepted Answer

Azure Cosmos DB SQL API. The Azure Cosmos DB SQL API (formerly DocumentDB) is the correct choice because it provides native support for storing and querying JSON documents with flexible schema, allowing each device document to have a unique DeviceID and varying attributes per device type. It offers low-latency reads by DeviceID via direct point reads using the partition key, and supports global distribution through multi-region writes and automatic replication, meeting the worldwide deployment requirement.

Answer

Azure Cosmos DB Table API

Answer

Azure Cosmos DB for MongoDB API

Answer

Azure Cosmos DB Gremlin API

Question 20

A company ingests streaming data from social media feeds and needs to process and analyze the data in real time. Which Azure service should they use to capture the stream?

Accepted Answer

Azure Event Hubs. Azure Event Hubs is a fully managed, real-time data ingestion service designed to capture and process millions of events per second from sources like social media feeds. It provides a scalable, low-latency endpoint for streaming data, making it the correct choice for capturing the stream before further analysis.

Answer

Azure Stream Analytics

Answer

Azure IoT Hub

Answer

Azure Data Lake Storage