Free DP-900 Practice Test

 By default, every Azure SQL Database is protected by a server-level firewall. When a logical server is created, this firewall blocks all access to the database to ensure a "secure by default" posture. This includes blocking connections from other Azure services. To allow traffic, an administrator must explicitly create firewall rules that specify allowed IP address ranges. While Network Security Groups (NSGs) and Azure Firewall can be used for defense-in-depth within a Virtual Network (VNet), the server-level IP firewall is the native, primary security mechanism that is automatically in place for every Azure SQL resource upon deployment.

Microsoft Learn: "Azure SQL Database and Azure Synapse IP firewall rules," Section: Server-level IP firewall rules. [Official Vendor Documentation]

Microsoft Learn: "Security overview - Azure SQL Database," Section: Network security - IP firewall rules. [Official Vendor Documentation]

Microsoft Training: Course AZ-900: Microsoft Azure Fundamentals, Module: "Describe Azure architecture and services - Azure SQL security." [Official Vendor Documentation]

In the Extract, Transform, and Load (ETL) pattern, data integration occurs through a middle engine or staging area. The process begins by extracting raw data from various sources. Crucially, the Transform phase occurs before the data reaches the destination. This involves cleaning, reformatting, and applying business logic to ensure the data is compatible with the target schema. Only once these transformations are complete is the data Loaded into the target store (such as a data warehouse). This differs from ELT (Extract, Load, Transform), where raw data is loaded directly into the target and processed using the destination's compute power.

Microsoft Azure Data Fundamentals: "Explore data integration" (Module: Explore fundamentals of data warehousing). Section: "ETL vs. ELT".

Vassiliadis, P. (2009). "A Survey of Extract-Transform-Load Technology," International Journal of Data Warehousing and Mining, vol. 5, no. 3, pp. 1-27. https://doi.org/10.4018/jdwm.2009070101

Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling (3rd ed.). John Wiley & Sons. Chapter 1: "The ETL System," pp. 24–28.

MIT OpenCourseWare: 6.830 Database Systems, Lecture Notes on Data Warehousing and ETL.

IEEE Standard for Information Technology: IEEE 1547.4-2011, Related concepts in data management systems and integration architecture.

A bar chart showing year-to-date sales by region is an example of descriptive analytics. This type of analytics focuses on summarizing historical data to provide a clear view of what has happened. The chart describes the past performance of sales across different regions without explaining the cause or predicting future outcomes. It answers the fundamental question, "What happened?". This is a common use case for business intelligence reports and dashboards, which are primary tools for descriptive analysis.

B. diagnostic: This is incorrect because the chart shows what happened, not why it happened. Diagnostic analytics would involve investigating the reasons behind the sales figures.
C. predictive: This is incorrect because the chart displays historical data. Predictive analytics uses this data to forecast what is likely to happen in the future.
D. prescriptive: This is incorrect because the chart does not recommend any actions. Prescriptive analytics suggests specific actions to achieve a desired outcome. ---

1. Microsoft Learn. (2024). Describe types of core data workloads - DP-900. Microsoft.

Section: Types of data analytics

Details: The documentation explicitly defines descriptive analytics as the process that answers the question, "What happened?". It states, "This is the most common type of data analytics... Business reports with visualizations such as pie charts, bar charts, line charts, tables, or generated narratives are all examples of descriptive analytics." This directly maps the question's scenario (a bar chart of sales) to the definition of descriptive analytics. The same document defines diagnostic, predictive, and prescriptive analytics in ways that exclude them as the correct answer.

A key-value data store is a non-relational database that uses a simple associative array as its fundamental data model. Data is stored as a collection of unique keys, each paired with a specific value. Because the system does not need to parse a complex schema or manage relational overhead (like ACID-compliant transactions across multiple tables or complex joins), it is highly optimized for simple lookups where the application provides a key and retrieves the associated value with minimal latency. This architecture allows for massive horizontal scalability and high-throughput performance, making it ideal for session management, caching, and user preferences.

Microsoft Azure Architecture Center: "Data store types - Key/value stores." This documentation specifies that key-value stores are highly optimized for simple lookups and perform basic read/write operations by key.

AWS Cloud Foundations: "Key-Value Databases." Section on performance highlights that these stores are designed for high-frequency, low-latency lookups using primary keys rather than complex relational queries.

ACM Digital Library: A Survey of NoSQL Databases by G. Hecht et al. (2014). This paper discusses the performance trade-offs of key-value stores, noting their optimization for point-access queries (lookups) over complex joins. https://doi.org/10.1145/2630607.2630614

University of California, Berkeley (CS162): Operating Systems and Systems Programming courseware regarding distributed storage systems, which categorizes key-value stores as systems optimized for simple get(key) and put(key, value) operations.

The primary use case for a read-only database replica is to implement read scale-out. Transactional (OLTP) workloads are often impacted by long-running, resource-intensive analytical or reporting queries. By creating a read-only replica, these read-heavy workloads can be redirected away from the primary, transactional database. This isolation prevents reporting queries from consuming resources (CPU, I/O, memory) and holding locks that would otherwise slow down or block the main application's transactions, thus ensuring stable performance for the transactional workload.

B. Auditing is a built-in feature of Azure SQL Managed Instance that tracks database events and writes them to a log. It does not require a separate read-only replica to function.
C. A replica for a regional outage is primarily for disaster recovery (DR), not just read-scaling. While a geo-secondary replica can be readable, its main purpose is to provide a failover target to ensure business continuity.
D. The Recovery Point Objective (RPO) relates to the maximum acceptable data loss. While replication is involved, implementing a replica is not the direct method to improve RPO; that is typically managed by backup frequency and transaction log shipping intervals.

1. Microsoft Learn

Auto-failover groups - Azure SQL Database & Azure SQL Managed Instance.

Reference: Under the section "Read-only secondary

" the documentation states

"In an auto-failover group

the secondary database can be used to offload read-only workloads." This directly supports using a replica for workloads like reporting to avoid impacting the primary.

2. Microsoft Learn

High availability for Azure SQL Database and SQL Managed Instance.

Reference: The "Locally redundant availability" section explains that high availability is a fundamental

built-in part of the service architecture

managed automatically

and is distinct from user-configured replicas for disaster recovery or read-scaling. This clarifies why option C is incorrect in the context of HA.

3. Microsoft Learn

Business continuity overview with Azure SQL Database.

Reference: This document defines Recovery Point Objective (RPO) and Recovery Time Objective (RTO). It clarifies that RPO is about data loss tolerance

which is a characteristic of the replication and backup technology

not the primary reason for implementing a replica. This shows why option D is an incorrect justification.

4. Microsoft Learn

Auditing for Azure SQL Managed Instance.

Reference: This page describes how to configure auditing directly on the server or database instance to write audit logs to storage accounts

Log Analytics

or Event Hubs. This process is independent of database replicas

invalidating option B.

A treemap is a visual representation specifically designed to display hierarchical data through a series of nested rectangles. In this chart type, each branch of the hierarchy is given a rectangle, which is then tiled with smaller rectangles representing sub-branches. The size (area) of each rectangle is proportional to a specific quantitative value (measure), allowing for a quick comparison of relative proportions within and across categories. They are particularly effective when there are large amounts of hierarchical data that need to be viewed simultaneously to spot patterns or outliers.

Microsoft Power BI Documentation: "Treemaps in Power BI." This documentation defines treemaps as displaying hierarchical data as a set of nested rectangles where Power BI bases the size of the space inside each rectangle on the measured value.

Source: Create and use treemaps in Power BI (Accessed Dec 2025).

IBM Documentation (Cognos Analytics): "Treemaps." Describes treemaps as identifying patterns and exceptions in a large, complex data set by using size and color-coded rectangles.

Source: IBM Knowledge Center - Treemap visualizations.

Shneiderman, B. (1992): "Tree visualization with tree-maps: 2-d space-filling approach." ACM Transactions on Graphics (TOG). This seminal academic paper establishes the hierarchical nesting and area-proportional logic of the treemap.

DOI: https://doi.org/10.1145/102377.115768

A data warehouse is a central repository designed to support business intelligence (BI) and analytics. Unlike transactional databases (OLTP) that handle real-time CRUD operations, a data warehouse performs analytical processing (OLAP) by integrating heterogeneous data from various disparate sources—such as CRM systems, flat files, and relational databases—into a unified, consistent schema. This consolidation allows organizations to perform complex queries and historical trend analysis across the entire enterprise from a single point of truth. The process typically involves Extract, Transform, and Load (ETL) or Extract, Load, and Transform (ELT) pipelines to ensure data quality and structural alignment before storage.

Microsoft Learn (Official Documentation): Explore data warehouse concepts, "Describe the characteristics of data warehouses." Section: "What is a data warehouse?"

IBM Technology: What is a Data Warehouse?, IBM Cloud Education. (Updated 2023). Link: https://www.ibm.com/topics/data-warehousing

Inmon, W. H. (2005): Building the Data Warehouse, Fourth Edition. John Wiley & Sons. Page 29 (Definition of integration and subject-orientation).

IEEE Xplore: "Data warehousing and OLAP: A core technology for strategic decision making," in Proceedings of the 1996 ACM SIGMOD international conference on Management of data. DOI: 10.1145/235968.233355

Oracle Documentation: Database Data Warehousing Guide, 19c. Chapter 1: "Introduction to Data Warehousing Concepts."

The Azure SQL Database serverless compute tier is specifically designed for workloads with intermittent and unpredictable usage patterns. It automatically scales compute resources based on real-time workload demand. A key feature of the serverless tier is its ability to automatically pause the database during periods of inactivity. When paused, compute billing stops, and only storage is charged. The database automatically resumes when the next connection request is received. This combination of autoscaling and autopause provides significant cost optimization for suitable workloads.

A. Azure SQL Database Hyperscale: This tier is for very large databases (up to 100 TB) and offers rapid scaling of compute and storage, but it does not support automatic pausing.
B. Azure SQL managed instance: This is an "always-on" service designed for modernizing on-premises SQL Server applications. It does not have an automatic pausing feature.
D. Azure SQL Database elastic pool: Elastic pools allow multiple databases to share a set of resources for cost-effectiveness but do not support the automatic pausing of individual databases. ---

1. Microsoft Learn, Azure SQL Database Documentation. "Serverless compute tier for Azure SQL Database." This document states, "The serverless compute tier also automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns."

Reference: Introduction section, Paragraph 2.

2. Microsoft Learn, Azure SQL Database Documentation. "Compare vCore and DTU-based purchasing models of Azure SQL Database." This document includes a comparison table showing that "Auto-pause/resume" is a feature available only in the "Serverless" compute tier.

Reference: "Service Tiers" section, Feature comparison table.

3. Microsoft Learn, Azure SQL Managed Instance Documentation. "What is Azure SQL Managed Instance?." This document describes Managed Instance as a fully managed, "always-on" SQL Server Database Engine instance, which does not align with the concept of automatic pausing.

Reference: "Key features" section.

Azure Synapse Analytics employs an MPP architecture to achieve high-performance data processing. When a user submits a query, the Control Node optimizes it into a distributed execution plan. The MPP engine then distributes the processing across multiple compute nodes. Each compute node manages a portion of the data (shards) and executes its assigned tasks in parallel with other nodes. This approach allows the system to scale performance linearly; by adding more compute nodes, you increase the number of processors working on a single query, significantly reducing execution time for massive datasets compared to traditional single-node systems.

Microsoft Official Documentation: "Synapse SQL architecture - Azure Synapse Analytics." (2024). Section: Compute nodes.

Ref: "Compute nodes provide the power for Synapse SQL... The MPP engine distributes processing across the compute nodes."

Microsoft Learn: "What is dedicated SQL pool (formerly SQL DW) in Azure Synapse Analytics?" Section: MPP architecture components.

Ref: Identifies the Control Node as the optimizer and Compute Nodes as the units that perform the parallelized tasks.

Microsoft Azure Architecture Center: "Data warehousing in Microsoft Azure." Section: Massively parallel processing (MPP).

Ref: Explains the distribution of data and processing tasks across independent nodes.

Azure Stream Analytics uses a specific query language to define the logic for processing streaming data. This language is a subset of the standard Transact-SQL (T-SQL) language, making it familiar to developers and analysts who have experience with SQL. The query defines how data is read from one or more inputs, transformed, and then written to one or more outputs. This SQL-like syntax is used to express complex temporal logic and aggregations over streams of events.

A. XML: XML is a markup language used for encoding data in a structured format, not for defining real-time data processing logic or queries.
B. KQL: Kusto Query Language (KQL) is used for querying data in Azure Data Explorer, Azure Monitor Logs, and Microsoft Sentinel, not Azure Stream Analytics.
C. YAML: YAML is a data serialization language commonly used for configuration files and data exchange, not for writing data transformation queries.

1. Microsoft Learn, "Stream Analytics Query Language (SAQL) overview": In the introduction, it states, "Azure Stream Analytics offers a SQL query language for performing transformations and computations over streams of events."

2. Microsoft Learn, "DP-900: Explore stream processing with Azure Stream Analytics": Under the "Write queries for Azure Stream Analytics" unit, the first paragraph states, "Azure Stream Analytics jobs are defined with a SQL-like query to transform a stream of data from one or more inputs and write the results to one or more outputs."

3. Microsoft Learn, "Get started with Azure Stream Analytics": In the "Define the transformation query" section of the tutorial, it explicitly states, "The job uses a query language that's a subset of SQL to define the transformation."