DP-900 Practice Questions

 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.

Azure offers two primary services for provisioning and managing Apache Spark clusters. Azure Databricks is a premium, managed analytics platform optimized for the Microsoft Azure cloud services platform, built around Apache Spark. It provides a collaborative environment with notebooks, automated cluster management, and enterprise-grade security. Azure HDInsight is a managed, full-spectrum, open-source analytics service that allows you to create clusters using various open-source frameworks, including Apache Spark, Hadoop, and Kafka. Both services enable users to deploy and manage Spark clusters for large-scale data processing and analytics.

B. Azure Log Analytics: This service is part of Azure Monitor and is used for collecting, correlating, and analyzing log and performance data from various sources, not for provisioning data processing clusters.

C. Azure Time Series Insights: This is a fully managed analytics, storage, and visualization service for IoT-scale time-series data. It does not provision general-purpose Spark clusters.

1. Microsoft Learn: "What is Azure Databricks?". This document states, "Azure Databricks is a data analytics platform optimized for the Microsoft Azure cloud services platform. Azure Databricks offers three environments for developing data intensive applications: Databricks SQL, Databricks Data Science & Engineering, and Databricks Machine Learning. Databricks Data Science & Engineering (sometimes called simply “Workspace”) provides an interactive workspace that enables collaboration between data engineers, data scientists, and machine learning engineers. For a big data pipeline, the data (raw or structured) is ingested into Azure through Azure Data Factory in batches, or streamed near real-time using Apache Kafka, Event Hub, or IoT Hub. This data lands in a data lake for long term persisted storage, in Azure Blob Storage or Azure Data Lake Storage. As part of your analytics workflow, use Azure Databricks to read data from multiple data sources and turn it into breakthrough insights using Spark."

2. Microsoft Learn: "What is Apache Spark in Azure HDInsight?". This document explicitly states, "Apache Spark is a parallel processing framework that supports in-memory processing to boost the performance of big-data analytic applications. Apache Spark in Azure HDInsight is the Microsoft implementation of Apache Spark in the cloud. HDInsight makes it easier to create and configure a Spark cluster in Azure."

3. Microsoft Learn: "DP-900: Describe the capabilities of Azure HDInsight". This module details the cluster types available in HDInsight, explicitly listing Spark as one of them. It states, "Azure HDInsight is a cloud distribution of Hadoop components... You can use HDInsight to create clusters of virtual machines for the following open-source frameworks: ... Apache Spark: A parallel processing framework that supports in-memory processing to boost the performance of big-data analysis applications."

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."

Serverless configuration: The Azure SQL Database single database is the primary deployment model that supports the serverless compute tier. This tier automatically scales compute resources based on workload demand and pauses the database during inactive periods to optimize costs.

Highest compatibility: Azure SQL Managed Instance is designed specifically for "lift-and-shift" migrations. It offers near 100% compatibility with the latest on-premises SQL Server engine, supporting instance-level features like SQL Agent and cross-database queries.

Low-cost, low-maintenance: Azure SQL Database single database provides the lowest entry cost (e.g., Basic or Serverless tiers) and the lowest management overhead, as Azure handles all patching, backups, and infrastructure updates in a fully managed PaaS environment.

Microsoft Learn: Serverless compute tier - Azure SQL Database. This document specifies that serverless is a compute tier for single databases that automatically scales and bills for compute used per second. Link

Microsoft Learn: What is Azure SQL Managed Instance? (Section: "Key capabilities"). States that Managed Instance offers "near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine." Link

Microsoft Learn: Azure SQL Database vs. SQL Managed Instance comparison. Highlights that Azure SQL Database is optimized for modern cloud-native applications with minimal management overhead and lower entry prices. Link

Microsoft Learn: What is a single database? (Section: "Dynamic scalability"). Describes how single databases

Database Administrator (DBA): Focuses on the operational aspects of data, including security, performance tuning, and ensuring data availability through backup and recovery strategies.

Data Analyst: Uses data to provide insights. They transform raw data into visualizations and reports to enable businesses to make informed, data-driven decisions.

Data Engineer: Responsible for the technical infrastructure. They build and monitor data pipelines (ETL/ELT processes) to ensure data flows reliably from source systems to analytical storage.

Microsoft Learn: Explore data roles and responsibilities (Topic: DP-900). Section: "What is a database administrator?", "What is a data engineer?", and "What is a data analyst?".

DAMA-DMBOK: Data Management Body of Knowledge (2nd Edition). Chapter 1: Data Management, Section 1.3.1 (Roles in Data Management).

ACM/IEEE CC2020: Computing Curricula 2020: Paradigms for Future Computing Curricula. Appendix A: Competency for Data Science and Data Engineering roles.

NIST Special Publication 800-181: National Initiative for Cybersecurity Education (NICE) Cybersecurity Workforce Framework. Work Role: Database Administrator (K0006: Backup and recovery procedures).

Azure Data Lake Storage Gen2 implements a hierarchical namespace on top of Blob storage. At the highest level is the Azure Storage account, which serves as the top-level container for all data. Within the account, you create a File system (also referred to as a "container" in standard Blob storage). This file system acts as the root directory. Unlike standard flat namespaces, ADLS Gen2 allows for actual directories and subdirectories within the file system to organize data, which is essential for big data analytics performance and security (ACLs). "File share" is typically associated with Azure Files, but in some legacy or hybrid contexts, it represents a specific mountable entry point.

Microsoft Documentation: Introduction to Azure Data Lake Storage Gen2. Section: "Hierarchical Namespace". Official Microsoft Docs

Microsoft Documentation: Structure of Azure Data Lake Storage Gen2. Section: "Account, File systems, and Paths". Official Microsoft Docs

Azure Architecture Center: Data Lake Storage Gen2 Hierarchy and ACLs. Official Microsoft Architecture Guide