DP-900 Practice Test 29 — 15 Questions

Question 1

Your company uses Azure SQL Database and needs to ensure that transactions are durable even if the database instance fails. Which feature should you enable?

Accepted Answer

Zone-redundant storage. Zone-redundant storage (ZRS) replicates your Azure SQL Database transaction logs and data files synchronously across three Azure availability zones within the same region. This ensures that even if an entire zone fails, committed transactions are preserved and the database remains available, providing durability at the storage layer without requiring a separate database replica.

Answer

Active geo-replication

Answer

Transparent Data Encryption

Answer

Auto-failover groups

Question 2

You need to design a data storage solution for a global e-commerce application that must support ACID transactions and require minimal latency for point lookups by a unique key. Which Azure data service should you use?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the correct choice because it provides global distribution with multi-region writes, guarantees ACID transactions through its transactional batch API, and offers single-digit millisecond latency for point reads by a unique key (e.g., id and partition key). This makes it ideal for a global e-commerce application requiring both strong consistency and low-latency lookups.

Answer

Azure Table Storage

Answer

Azure SQL Database

Answer

Azure Blob Storage

Question 3

A healthcare organization must store patient health records for 7 years to meet regulatory requirements. After 7 years, data must be deleted immediately. They use Azure Blob Storage. Which policy should they implement?

Accepted Answer

Time-based retention policy. A time-based retention policy (immutability policy) in Azure Blob Storage ensures that blobs are stored in a WORM (Write Once, Read Many) state for a specified period, preventing modification or deletion. After the retention period expires, the data can be deleted immediately, meeting the 7-year regulatory requirement. This policy is designed specifically for compliance scenarios where data must be preserved for a fixed duration and then removed.

Answer

Soft delete policy

Answer

Legal hold policy

Answer

Lifecycle management policy with deletion after 7 years

Question 4

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?

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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 12

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 13

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 14

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 15

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