DP-900 Practice Test 32 — 15 Questions

Question 1

A small business needs a cost-effective relational database for a new web application. The workload is light and predictable. They want to minimize administrative overhead. Which Azure service should they choose?

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

Accepted Answer

Azure SQL Database serverless. Azure SQL Database serverless is the correct choice because it automatically pauses the database during periods of inactivity, charging only for storage and compute used. This aligns perfectly with the small business's need for a cost-effective, low-administration relational database for a light, predictable workload, as it eliminates the need to manage infrastructure or pay for idle compute.

Answer

Azure Database for PostgreSQL

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Database (provisioned DTU)

Question 2

Your organization has a data warehouse in Azure Synapse Analytics. You need to load data from Azure Blob Storage daily, transforming it using a data flow. Which Azure service should you use for the ETL process?

Accepted Answer

Azure Data Factory. Azure Data Factory (ADF) is the correct choice because it provides native integration with Azure Synapse Analytics and Azure Blob Storage, and it includes a visual data flow designer for transforming data without writing code. ADF's mapping data flows execute at scale on Spark clusters, making it ideal for daily ETL workloads that require both ingestion and transformation.

Answer

Azure Databricks

Answer

Azure Logic Apps

Answer

Azure Synapse Pipelines

Question 3

A database designer wants to reduce data redundancy and improve data integrity by splitting a large table into multiple related tables based on functional dependencies. This process is known as:

Accepted Answer

Normalization. Normalization is the process of organizing a relational database into multiple related tables to reduce data redundancy and improve data integrity by eliminating functional dependencies that cause anomalies. This is a core concept in relational database design, directly aligning with the scenario described in the question.

Answer

Denormalization

Answer

Partitioning

Answer

Indexing

Question 4

A retail company uses Azure SQL Database to store customer transactions. They need to analyze sales trends over time. Which Azure service should they use to build interactive dashboards and reports without moving data out of Azure?

Accepted Answer

Power BI. Power BI is the correct choice because it is a business analytics service that can connect directly to Azure SQL Database to build interactive dashboards and reports without requiring data movement. It supports DirectQuery mode, which queries the source database in real-time, enabling live analysis of sales trends while data remains in Azure.

Answer

Azure Analysis Services

Answer

Azure Synapse Analytics

Answer

Microsoft Purview

Question 5

A small business wants to migrate their on-premises SQL Server database to Azure. They have limited budget and want to minimize ongoing management overhead. The database is less than 50 GB and is used by a single application with low concurrent users. The application requires compatibility with SQL Server features such as T-SQL, stored procedures, and functions. The business does not require high availability or disaster recovery. Which Azure relational database service should they choose?

Accepted Answer

Azure SQL Database (serverless tier). Azure SQL Database serverless tier is the best choice because it provides a fully managed, PaaS relational database service that supports T-SQL, stored procedures, and functions, while automatically pausing during idle periods to reduce costs. With a database under 50 GB, low concurrency, and no HA/DR requirements, the serverless tier minimizes both management overhead and cost, as it charges only for compute used per second and storage consumed.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Answer

Azure Database for MySQL

Question 6

A company uses Azure Databricks to process data stored in Azure Data Lake Storage Gen2. They need to enforce fine-grained access control on files and folders based on user identity. Which security feature should they implement?

Accepted Answer

Access control lists (ACLs). Access control lists (ACLs) on Azure Data Lake Storage Gen2 provide POSIX-compliant, fine-grained permissions at the file and folder level. This allows you to grant read, write, or execute permissions to specific users or groups, which is exactly what is needed for enforcing identity-based access control on individual files and folders.

Answer

Storage account firewall rules

Answer

Shared access signatures (SAS)

Answer

Azure RBAC roles on the storage account

Question 7

Which TWO Azure services can be used to orchestrate and automate data pipelines? (Choose two.)

Accepted Answer

Azure Synapse Pipelines. Azure Data Factory (E) is a dedicated cloud-based ETL and data integration service that allows you to create, schedule, and orchestrate data pipelines at scale. Azure Synapse Pipelines (B) is built on the same engine as Azure Data Factory and provides native pipeline orchestration within the Synapse Analytics workspace, enabling you to move and transform data across various sources and sinks. Both services offer visual design tools, code-free transformations, and robust scheduling capabilities for automating data workflows.

Answer

Azure SQL Database

Answer

Power BI

Answer

Azure Databricks

Question 8

A healthcare organization must build an analytics solution that processes streaming patient vitals data and provides real-time dashboards. The solution must also store historical data for compliance audits. Which combination of Azure services should the organization use?

Accepted Answer

Azure Stream Analytics for real-time processing and Azure SQL Database for historical storage and dashboards.. Azure Stream Analytics is purpose-built for real-time processing of streaming data, such as patient vitals, and can output directly to Power BI for live dashboards. Azure SQL Database provides a relational store for historical data, supporting compliance audits with point-in-time restore and long-term retention. This combination meets both real-time and historical requirements without unnecessary complexity.

Answer

Azure Synapse Analytics for real-time processing and Azure Blob Storage for archival.

Answer

Azure Event Hubs for ingestion and Azure Data Lake Storage for storage, with Power BI for dashboards.

Answer

Azure HDInsight with Apache Spark for streaming and Azure Cosmos DB for storage.

Question 9

A data engineer needs to transform and clean data from multiple sources before loading it into Azure Synapse Analytics. Which Azure service should they use for this ETL process?

Accepted Answer

Azure Data Factory. Azure Data Factory is the correct service because it is a cloud-based ETL (Extract, Transform, Load) service designed specifically to orchestrate and automate data movement and transformation from multiple sources. It provides built-in connectors for various data stores and supports data flows for cleaning and transforming data before loading it into Azure Synapse Analytics.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure Stream Analytics

Question 10

A company stores customer data in three formats: a relational table with fixed columns for CustomerID, Name, and Email; product reviews stored as JSON documents with varying fields such as rating and comment; and product demonstration videos in MP4 format. Which of the following correctly lists these data types from most structured to least structured?

Accepted Answer

Relational table, JSON documents, MP4 videos. Data structuredness is determined by schema rigidity. A relational table has a fixed schema with predefined columns (CustomerID, Name, Email), making it the most structured. JSON documents have a flexible schema where fields like rating and comment can vary per document, placing them in the semi-structured category. MP4 videos are unstructured binary data with no inherent schema, making them the least structured.

Answer

Relational table, MP4 videos, JSON documents

Answer

JSON documents, relational table, MP4 videos

Answer

MP4 videos, JSON documents, relational table

Question 11

An e-commerce company uses Azure SQL Database for its product catalog. During promotional events, the database experiences unpredictable spikes in traffic. The company wants a solution that automatically adjusts compute resources based on demand without manual intervention. Which Azure SQL Database option should they use?

Accepted Answer

C) Serverless compute tier. The Serverless compute tier for Azure SQL Database automatically scales compute resources based on workload demand and pauses the database during idle periods, charging only for storage and used compute. This matches the requirement for handling unpredictable traffic spikes without manual intervention, as it provides instant scaling and cost efficiency for intermittent workloads.

Answer

A) Read replicas

Answer

B) Active geo-replication

Answer

D) Elastic pool

Question 12

A company wants to build a data lake on Azure for storing structured, semi-structured, and unstructured data. The solution must support fast queries on structured data without moving data to a separate store. Which Azure service should they use?

Accepted Answer

Azure Data Lake Storage Gen2. Azure Data Lake Storage Gen2 (ADLS Gen2) is the correct choice because it combines a hierarchical namespace with Azure Blob Storage, enabling both a data lake for unstructured and semi-structured data and fast, SQL-like queries on structured data using Azure Synapse Analytics or PolyBase without moving data. It supports the ABFS (Azure Blob File System) driver for high-performance analytics and integrates directly with query engines like Apache Spark and Azure Synapse SQL.

Answer

Azure Blob Storage

Answer

Azure SQL Database

Answer

Azure Cosmos DB

Question 13

A company wants to build a real-time analytics dashboard for IoT sensor data. Which combination of Azure services should they use?

Accepted Answer

Azure Stream Analytics and Power BI. Azure Stream Analytics is a real-time event processing engine that can ingest IoT sensor data from sources like Azure Event Hubs, apply SQL-based queries to detect patterns or anomalies, and output results directly to Power BI for live dashboard visualization. This combination provides end-to-end streaming analytics with sub-second latency, which is essential for real-time dashboards.

Answer

Azure HDInsight and Azure Databricks

Answer

Azure Logic Apps and Azure SQL Database

Answer

Azure Data Factory and Azure Analysis Services

Question 14

A logistics company uses an online system to process incoming delivery requests one at a time, updating the database immediately upon each transaction. They also run a weekly job that analyzes thousands of delivery records to identify average delivery times and trends. Which set of terms correctly classifies these two workloads?

Accepted Answer

OLTP and OLAP. The first workload processes individual delivery requests with immediate database updates, which is the definition of Online Transaction Processing (OLTP). The second workload runs a weekly job analyzing thousands of records for trends and averages, which is Online Analytical Processing (OLAP). These two terms correctly classify the transactional and analytical workloads described.

Answer

Batch processing and real-time processing

Answer

Relational and non-relational

Answer

Structured and semi-structured

Question 15

A multinational e-commerce company uses Azure SQL Database for its order processing system. They need to ensure that if an entire Azure region becomes unavailable, the database remains available with minimal data loss and automatic failover. Which feature should they implement?

Accepted Answer

Active geo-replication. Active geo-replication (Option A) is correct because it creates readable secondary replicas of an Azure SQL Database in a paired Azure region, enabling automatic failover during a regional outage. This feature provides a recovery point objective (RPO) of as low as 5 seconds and a recovery time objective (RTO) of under 1 hour, ensuring minimal data loss and high availability.

Answer

Automatic tuning

Answer

Elastic pools

Answer

Serverless compute