Microsoft Azure Data Fundamentals Practice Test (DP-900)

Azure Data Factory is Microsoft's cloud-based data integration service. It can connect to on-premises data sources through self-hosted integration runtimes, schedule and orchestrate batch pipelines, and provides Mapping Data Flows so that transformations can be built in a drag-and-drop, code-free designer. Azure Event Hubs is optimized for real-time event streaming, not scheduled batch ETL. Azure Databricks is an analytics platform that requires writing notebooks rather than providing a visual orchestration interface. An Azure Synapse Analytics dedicated SQL pool is an analytical data store, not an ingestion and orchestration service.

Azure Table storage uses a composite primary key made up of the PartitionKey and RowKey values. The PartitionKey groups related entities so the service can route requests to the correct storage node, while the RowKey uniquely identifies the entity within that partition. Together, these two values allow an efficient point lookup. Other properties such as ETag or Timestamp are metadata fields and are not used as part of the primary key, and neither the PartitionKey alone nor the RowKey alone is sufficient to uniquely identify an entity across the table.

The Gremlin API in Azure Cosmos DB provides a property graph data model and supports the Gremlin traversal language, which is designed for working with highly connected data such as social graphs, recommendation engines, and network topologies. It enables efficient graph traversals like shortest-path and neighbor queries.

The Cassandra API focuses on wide-column workloads, the Table API offers a key-value store for schemaless data, and the Core (SQL) API targets JSON document workloads. None of these alternatives provide native graph structures or traversal operators, so they are less suitable for social-network relationship data.

SQL Server on Azure Virtual Machines provides Infrastructure-as-a-Service. Administrators manage the full SQL Server installation and the Windows or Linux OS, so they can use Windows authentication, SQL Server Agent, registry edits, file system access, and other custom components. Azure SQL Database (single database or elastic pool) is Platform-as-a-Service and hides the OS layer; these options do not expose SQL Server Agent or allow Windows logins. Azure SQL Managed Instance offers near-full SQL Server compatibility but still runs as PaaS without giving OS-level access, so custom registry or file system changes are not possible. Therefore, SQL Server on Azure Virtual Machines best fits the requirements.

Normalization reduces data redundancy and prevents update anomalies by ensuring each fact is stored exactly once. Repeating attributes that describe a product (its name and price) should be kept in a separate Products table that uses ProductID as its primary key. OrderDetails would then hold only the ProductID as a foreign-key reference, eliminating duplicated product information. Creating a composite column, adding extra price columns, or embedding attributes in JSON does not remove redundancy and therefore does not achieve the fundamental purpose of normalization.

Global distribution lets you associate your Azure Cosmos DB account with one or more Azure regions. The service automatically replicates data to every selected region, supports multi-region writes, and can perform automatic failover if a region goes offline. Change feed, Dedicated Gateway, and the analytical store add other benefits such as event streams, connection isolation, or analytical workloads, but none of them replicate data across regions or provide automatic regional failover.

A primary key constraint enforces entity integrity by requiring that the selected column or combination of columns contain unique, non-NULL values for every row. Foreign keys enforce referential integrity between tables but do not guarantee uniqueness in the referenced table. A unique index that allows NULLs can still store multiple NULLs and therefore does not fully meet the requirement. A default constraint only supplies a value when none is provided; it does not enforce uniqueness or prevent NULLs on its own.

The UPDATE statement is designed to modify the values stored in existing rows. It supports a SET clause to specify new column values and a WHERE clause to limit the rows that are altered. SELECT only reads data, INSERT adds new rows, and DELETE removes rows, so none of those statements can change data that is already stored.

Change data capture records insert, update, and delete operations on source tables, allowing an ingestion pipeline to move only the rows that have changed since the previous load. This minimizes the amount of data copied and lowers both network usage and storage costs. Sharding spreads data across multiple databases for scale, but it does not identify changed rows. Data masking obfuscates sensitive fields and is unrelated to incremental loads. A full database backup copies all data every time, which does not meet the requirement to ingest only new or modified rows.

A hopping window lets you specify both a window length and a hop size. Setting a five-second window length with a one-second hop creates overlapping windows: each new window starts one second after the previous one, but still covers the last five seconds of data. This produces a new, updated average every second. Tumbling windows are non-overlapping, so results would appear only once every five seconds. Sliding windows emit results only when events enter or leave the window, so they do not guarantee output on a fixed schedule. Session windows group bursts of activity separated by idle gaps and are not appropriate for fixed-interval aggregation.

Azure Table storage is a NoSQL key-value store that uses a two-part key consisting of PartitionKey and RowKey for fast retrieval. It is designed for semi-structured data, automatically scales to very large datasets, and costs less than relational services. Blob storage is optimized for unstructured binary objects, File storage exposes SMB shares, and Azure SQL Database is a relational service that requires a predefined schema and incurs higher cost for massive scale.

Normalization restructures data into separate, related tables so that each fact is stored only once. By moving employee details to their own table and referencing that table from the project-assignment table with a foreign key, redundant phone numbers are removed and update anomalies are avoided. Denormalization does the opposite by intentionally introducing redundancy. Indexing and horizontal partitioning can improve performance or manage large datasets, but neither addresses repeated data values or integrity concerns.

Semi-structured data, such as JSON or XML, does not rely on a rigid table schema. Instead, it carries its own self-describing structure through tags or key-value pairs, allowing records to contain optional or variable attributes. Structured data, by contrast, is organized into relational tables with predefined columns and data types. Unstructured data (for example, video files) lacks an internal organizational model beyond basic metadata, while graph data stores focus on nodes and edges rather than tagged elements. Therefore, the description referring to flexible, self-describing tags is the only accurate characterization of semi-structured data.

Azure Cosmos DB is a fully managed, globally distributed NoSQL database designed for JSON documents. It automatically indexes every property, offers configurable consistency with single-digit millisecond latency for reads and writes, and can replicate data to any Azure region with the click of a button. Azure SQL Database Hyperscale is a relational store that does not accept schemaless JSON as a native data model and cannot automatically index every property. Azure Blob Storage with Azure Cognitive Search can hold the JSON files but relies on a separate indexer service and does not guarantee low-latency transactional reads or writes. Azure Table storage is a key-attribute store limited to partition-key and row-key indexes and provides eventual consistency without the turnkey global distribution or SLA characteristics required.

In a relational database, a foreign key constraint links a column (or set of columns) in one table to the primary key of another table. This relationship ensures that any value inserted in the child table must first exist in the parent table, thereby maintaining referential integrity. A primary key uniquely identifies rows within its own table but does not enforce relationships across tables. Indexes improve query performance but do not guarantee integrity. Stored procedures contain executable code and likewise do not enforce referential rules automatically.

Normalization splits data into related tables so that each fact is stored only once. This minimizes duplication and the risk of update, insert, and delete anomalies, thereby protecting the consistency and accuracy of the data. Performance gains or support for unstructured data are not the primary objectives, and normalization does not intentionally increase duplication.

JSON documents store information as key-value pairs and often include nested objects or arrays. They have an inherent, flexible schema but do not require a rigid table structure with predefined columns, so they are considered semi-structured data. Structured data fits well into relational tables, while unstructured data (such as images or free-form text) lacks any consistent schema. The term "relational data" refers specifically to structured data managed in relational databases, not to JSON files.

Azure File storage provides fully managed file shares exposed through the SMB protocol and supports Windows access control lists (ACLs), so the application can continue using standard file APIs after migration.

Azure Blob storage is object storage accessed over HTTP rather than SMB, so the app would have to be rewritten. Azure Managed Disks are block-level volumes that attach to virtual machines; although a shared-disk feature allows a disk to be mounted by multiple VMs for clustering scenarios, the disk is still presented as a block device, not an SMB file share. Azure Table storage is a NoSQL key-value store and is unsuitable for file-based workloads.

A document database stores data as self-describing JSON (or BSON) documents, allowing each record to have its own shape. Because the schema is flexible, new properties can be added without altering a central definition, and queries can directly target nested values inside each document. Column-family databases can add columns dynamically but organize data in rows and column families rather than self-contained JSON documents, so querying nested JSON structures is not native. Relational databases rely on a fixed table schema, and graph databases are optimized for relationship traversal rather than flexible JSON storage. Therefore, a document database best meets the requirements.

Azure Stream Analytics is designed for real-time event processing. It connects directly to streaming sources such as Event Hubs or IoT Hub, lets users express analytics in a SQL-like language, supports processing with millisecond latency, and can output results to Power BI for live dashboards. Azure Synapse Analytics and Azure Data Factory are optimized for batch workloads, while HDInsight Hadoop clusters focus on large-scale distributed processing rather than low-latency streaming queries.