Microsoft Azure Data Fundamentals Practice Test (DP-900)

Azure Synapse Analytics dedicated SQL pool (formerly Azure SQL Data Warehouse) is built for large-scale analytics. It stores data in a distributed manner and uses Massively Parallel Processing (MPP) to run complex, high-performance SQL queries across many nodes, supporting petabyte-scale workloads. Azure SQL Database is optimized for transactional (OLTP) or smaller analytical workloads on a single engine, not for MPP at massive scale. Azure Cosmos DB is a globally distributed NoSQL database aimed at operational workloads rather than large SQL analytics. Azure Data Lake Storage Gen2 provides scalable, low-cost file storage but does not include a built-in SQL query engine or MPP compute layer.

A view is defined by a saved SELECT statement and behaves like a virtual table. Because the data is retrieved from the underlying tables at query time, no duplicate data is stored. An index stores only key information to speed up lookups, a trigger executes automatically in response to data-modifying events, and a table-valued function returns results through an invocable function rather than appearing as a standalone virtual table. Therefore, using a view is the most appropriate way for developers to reuse the complex join through a single object name without copying data.

Line charts are designed to plot numeric measures against an ordered axis, typically time, making it easy to observe and compare trends for multiple series such as product categories across years. Donut charts emphasize part-to-whole relationships at a single point in time, not changes over time. Scatter charts are ideal for showing correlation between two quantitative measures, not time-based trends. A card visual only shows a single aggregated value, so it cannot illustrate year-over-year changes.

Transactional (OLTP) workloads center on many small data modifications that must succeed or fail as a single unit, so they depend on ACID properties such as atomic commit and strong consistency. The other options describe analytical patterns: large-scale scans and aggregations, schema-on-read storage of raw data in a data lake, or periodic batch refreshes for dashboards. These scenarios emphasize read-heavy analytics over write-intensive, consistency-critical operations and therefore are not typical of transactional workloads.

An index stores key values and row pointers in an ordered structure that the query optimizer can quickly search, reducing the I/O needed to locate requested rows. Views are virtual tables that hold no persisted structure, triggers run automatically in response to data-modification events, and stored procedures are reusable batches of T-SQL code. None of these alternatives provide the persisted, searchable ordering that an index supplies, so they do not speed data access in the same way.

The Core (SQL) API (also called Azure Cosmos DB for NoSQL) is purpose-built for JSON documents. It automatically indexes all properties, requires no schema definition, and lets you query the items with a SQL-like language. The MongoDB, Cassandra, and Gremlin APIs expose wire protocols that mimic those respective databases and therefore use their own query languages rather than SQL-like syntax in Azure Cosmos DB.

Azure Blob storage is Microsoft's object storage service for unstructured data such as images and videos. Blobs are stored as individual objects, can hold associated metadata, and are directly accessible over HTTP/HTTPS via unique URLs, which makes them ideal for public web or mobile access. Blob storage also supports built-in lifecycle management policies that automatically transition data between hot, cool, and archive tiers, helping control long-term storage costs.

Azure File shares are optimized for SMB/NFS file shares rather than public web access. Azure Table storage is a NoSQL key-value store for structured datasets, not large binary files. Azure Managed Disks provide block storage for virtual machines and are not designed for direct URL access or automatic tiering across hot, cool, and archive layers.

A data lake built on Azure Data Lake Storage Gen2 is designed for large-scale analytics scenarios that require cost-effective storage of raw data in many formats. Because storage is separated from the compute engines that later process the data, the lake can scale independently and remain available to multiple analytics services that understand open standards like Parquet. Azure SQL Database, Azure Synapse dedicated SQL pools, and Azure Cosmos DB are optimized for structured or operational workloads and do not store raw files in open formats while keeping compute fully decoupled from storage, so they do not satisfy all stated requirements.

Azure Blob storage lets you change a blob's access tier. After the initial period of frequent downloads, assigning the Cool tier lowers the per-GB storage price while accepting a higher per-access charge, making it cost-effective for infrequently used data that still needs online availability. Azure Files caching does not reduce storage cost for blobs, Table storage cannot hold binary large objects efficiently, and choosing geo-redundant replication addresses durability rather than tiered cost management.

Power BI Q&A lets users type natural-language questions and instantly get answers displayed as visuals that can be pinned to dashboards. Quick Insights automatically searches for statistical patterns, Power Query Editor shapes and transforms data, and row-level security restricts data access; none of those features support real-time natural-language querying.

Azure Blob storage is Microsoft's object storage service for unstructured data such as text, images, and log files. It is accessible through REST-based endpoints and SDKs, supports HTTPS, and offers built-in lifecycle management with hot, cool, and archive access tiers that automatically move data based on defined rules. Azure File storage provides SMB/NFS file shares, not optimized for massive unstructured data or lifecycle tiering. Azure Table storage is a NoSQL key-value store optimized for structured datasets, while Azure Queue storage is designed for message queuing, not bulk object storage.

A data lake built on Azure Data Lake Storage Gen2 is designed for schema-on-read scenarios, allowing any data type to be stored in its raw form. It offers petabyte-scale capacity and is priced for bulk storage, making it suitable for large-scale analytics. A dedicated SQL pool in Azure Synapse is a relational data warehouse that enforces schema-on-write and is optimized for structured data only. Azure Cosmos DB's Table API is a NoSQL key-value store tuned for operational workloads rather than analytical, large-volume data processing. Azure Cache for Redis is an in-memory cache intended for low-latency data retrieval, not for persistent, low-cost, petabyte-scale storage.

Append blobs are optimized for append-only workloads such as logging. Each append operation adds a new block to the end of the blob, avoiding the need to upload or rewrite the entire object. Block blobs are intended for general purpose object storage and require reuploading changed blocks. Page blobs are designed for frequent random reads/writes, such as virtual hard disks. Azure File shares are a different service that exposes the SMB protocol and are not blob types.

JSON documents contain tags or keys that describe each value, so the data carries some self-describing structure. However, the layout is not bound to rigid rows and columns like a relational table. Because it has an irregular but identifiable structure, JSON is considered semi-structured data. Structured data would exist in a predefined tabular schema, while unstructured data such as free-form text or images lacks recognizable fields. The term "binary large objects (BLOBs)" refers to a storage format rather than a data structure classification.

A hierarchy combines related columns into a single expandable structure. When you add the hierarchy to a visual, Power BI automatically provides drill-down and drill-up actions, letting users move from Year to Quarter, Month, and Day without manually placing each column. A calculated column or DAX measure would not provide interactive drill actions, and row-level security controls data access, not navigation.

Analytical workloads focus on discovering insights from large volumes of data that has already been collected. They are dominated by complex, read-intensive queries that scan many rows and return aggregated results such as trends and patterns. In contrast, transactional workloads process numerous small, short-lived write operations, often require sub-second response times, and depend on strict locking to ensure consistency for concurrent updates-all of which are less important for analytical scenarios.

Building and orchestrating data ingestion and transformation pipelines is a core responsibility of the data engineer role. Data engineers use services such as Azure Data Factory or Azure Synapse pipelines to extract data from sources, convert formats, cleanse it, and load it into analytical stores. Other roles focus on database administration, advanced analytics, or security rather than ETL workflows.

Azure Blob Storage is Microsoft's object store for unstructured data such as text or binary files. It supports Hot, Cool, and Archive tiers for cost optimization, can store individual objects up to several terabytes, provides HTTPS endpoints, and is natively supported as a source by Azure Data Factory. Azure Table Storage is a NoSQL key-value store, Azure Queue Storage is for message queuing, and Azure SQL Database is optimized for structured relational data rather than large log files.

The serverless compute tier of Azure SQL Database automatically scales up and down based on workload demand. After a configurable idle period (minimum 1 hour) the database pauses, and during that time you pay only for storage. When activity resumes, the service automatically restarts within about a minute, so you are charged for compute only while it is in use. Azure SQL Managed Instance and SQL Server on Azure Virtual Machines run continuously and accrue compute charges whether or not they are busy. Azure Database for PostgreSQL Flexible Server lets you stop the server manually or on a schedule to avoid compute charges, but it does not automatically pause in response to inactivity and therefore does not match the requirement for on-demand auto-pause and auto-resume.

Database administrators are responsible for the operational health of database systems. This includes setting up and monitoring backup and restore operations, configuring high availability, applying patches, and managing security and access. Building data ingestion pipelines is usually the duty of data engineers, while creating reports with Power BI is a data-analyst task. Training predictive models belongs to data scientists or machine-learning engineers, not database administrators.