Microsoft Azure Data Fundamentals Practice Test (DP-900)

Document databases are designed to store semi-structured data such as JSON without requiring a fixed relational schema. Azure Cosmos DB using the Core (SQL) API lets you ingest records that differ in structure and still query them efficiently on properties like device ID and timestamp. Relational databases expect a predefined table schema, graph databases are optimized for relationship traversal rather than time-series ingestion, and data warehouses focus on aggregated analytical workloads rather than rapid operational writes from devices.

Transactional workloads are designed to support frequent, small data modifications that must be completed reliably with full ACID guarantees. The need for many short insert, update, or delete operations that commit atomically is therefore a hallmark of a transactional system. Long-running aggregations, nightly batch loading, and column-oriented storage are all typical of analytical (OLAP) workloads that focus on large-scale reading and reporting rather than real-time data modification.

Designing and operating data ingestion pipelines is a core duty of a data engineer. Data engineers create, monitor, and optimize data loading and transformation processes that move data between storage and analytics systems. A data analyst focuses on exploring data and building reports, while a database administrator manages the operational health, security, and backups of database systems. A security engineer concentrates on overall security posture and access control, not on data pipeline design.

Transactional (OLTP) workloads typically handle a large number of short, interactive operations such as inserts, updates, and deletes, and they must preserve ACID (atomicity, consistency, isolation, durability) properties to ensure data integrity for each individual business transaction. Analytical (OLAP) workloads, in contrast, focus on reading large volumes of historical data, executing complex aggregations, and are often batch-oriented or read-only in nature. Therefore, the presence of many small, concurrent write operations that require strict consistency is a strong signal that the workload is transactional. The other options describe characteristics more typical of analytical workloads, such as large-scale aggregations, columnar storage optimized for scans, or infrequent, read-only dashboard refreshes.

Azure SQL Managed Instance is a platform-as-a-service (PaaS) offering that delivers nearly 100 percent compatibility with the SQL Server engine. It supports SQL Server Agent, cross-database queries, and native restoration of .bak files from on-premises SQL Server. Because it is a managed service, Microsoft handles operating-system patching and most routine maintenance tasks.

Azure SQL Database (single database) lacks SQL Server Agent and native restore capabilities. Running SQL Server on an Azure virtual machine would preserve full compatibility and native restore, but the customer-not Microsoft-remains responsible for OS patching and maintenance. Azure Database for PostgreSQL is a PostgreSQL service and does not support SQL Server workloads. Therefore, Azure SQL Managed Instance is the most appropriate choice.

Parquet is an open-source, columnar storage format that stores data by column rather than by row. This layout enables efficient compression and allows scan operations to read only the columns that a query needs, reducing both I/O and storage costs-advantages that make Parquet a common choice for big-data analytics engines such as Apache Spark.

CSV and JSON store data in a row-oriented manner and provide no built-in column pruning or advanced compression, so they offer lower analytical performance and typically larger file sizes. Avro is a binary, row-based format that supports schema evolution but is not columnar, so it does not provide the same scan-time benefits required in this scenario.

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 Cosmos DB is Microsoft's globally distributed, multimodel database service. It automatically indexes JSON documents, supports active multi-region replication with multi-master (multi-write) capabilities, and is engineered for single-digit millisecond latency at the 99th percentile. Traditional relational services such as Azure SQL Managed Instance and Azure Database for PostgreSQL offer robust transactional support but require predefined schemas and do not natively provide multi-region writes with the same latency guarantees. Azure Blob Storage is optimized for large binary objects, not for querying JSON documents with automatic indexing or sub-millisecond read performance.

Azure Database for MySQL is Microsoft's fully managed implementation of the open-source MySQL relational engine. It provides built-in high availability, automated backups, and scaling while maintaining full MySQL compatibility. Azure SQL Database is based on Microsoft SQL Server, not MySQL. Azure Database for PostgreSQL uses the PostgreSQL engine, and Azure Cosmos DB for NoSQL is a multi-model, non-relational database service. Therefore, only Azure Database for MySQL satisfies the requirement.

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.

Analytical (OLAP) workloads focus on reading and aggregating large volumes of historical data to discover patterns and trends. In contrast, transactional (OLTP) workloads emphasize frequent, small insert or update operations that must commit quickly, use highly normalized schemas, and guarantee strict ACID properties for each individual transaction. Therefore, the defining analytical characteristic in this scenario is the need to scan and summarize extensive historical data, not the requirement for rapid single-row updates or fully normalized storage.

Creating reports, dashboards, and other visualizations that turn prepared data into actionable business insights is a core responsibility of the data analyst role. Data analysts work closely with stakeholders to understand business questions, explore data, build visual models (often in Power BI), and share findings. Database administrators focus on the availability, performance, and security of databases; data engineers design and maintain data ingestion and transformation pipelines; security engineers concentrate on protecting information assets. Therefore, the duty described aligns with the data analyst role.

Relational databases organize information into two-dimensional tables called relations. Each table consists of rows (tuples) and columns (attributes). This tabular structure distinguishes relational systems from key-value, document, or wide-column stores. While primary keys, constraints, and fixed schemas are common practices, the defining feature of the model is storing data in tables of rows and columns that can be logically related through keys.

Azure Cosmos DB is a globally distributed, multi-model database service that automatically indexes all data by default, delivers single-digit-millisecond latency for reads and writes, and lets you select from several consistency levels (such as strong, bounded-staleness, session, or eventual). Azure SQL Database is a relational service that can store JSON but is optimized for structured, relational workloads and does not provide automatic indexing of every JSON property or the same global distribution and latency guarantees. Azure Blob Storage is object storage for unstructured data and does not provide automatic property indexing or transactional querying capabilities. A dedicated SQL pool in Azure Synapse Analytics is designed for large-scale analytical workloads, not for low-latency operational workloads with fine-grained document access.

Azure File storage provides fully managed file shares that can be mounted concurrently from Windows, Linux, and macOS clients by using the industry-standard SMB protocol, and it also offers NFS 4.1 shares. This lets users map the share as a network drive with no application changes. Azure Blob storage now supports NFS 3.0 on Premium block-blob accounts, but it lacks native SMB support and is intended for object storage rather than traditional file-share scenarios. Azure Table and Azure Queue storage are accessed through REST APIs and cannot be mounted via SMB or NFS. Therefore, Azure File storage is the correct choice for a cross-platform lift-and-shift file-server replacement.

Data analysts focus on exploring data and presenting insights. Creating interactive Power BI dashboards to illustrate business metrics is a core analytic activity that enables stakeholders to make data-driven decisions. Scheduling Azure Data Factory pipelines is an engineering responsibility focused on data movement. Tuning indexes and query plans is a database administration task aimed at operational performance. Configuring Azure RBAC policies is typically handled by administrators or security engineers who manage access control, not by analysts.

JSON documents embed a flexible, self-describing structure using key-value pairs that may differ across records. Such data is not strictly tabular, yet it contains tags and delimiters that provide partial structure, so it is classified as semi-structured. Structured data would enforce a fixed schema (for example, relational tables). Unstructured data lacks an inherent schema and does not use tags to describe its elements, while a binary classification is reserved for data such as images or audio rather than text-based JSON.

Designing, building, and maintaining data pipelines that ingest, transform, and load data for analytics is the primary responsibility of a data engineer. Database administrators focus on the operational health and security of database systems, data analysts focus on exploring and visualizing data to deliver business insights, and solutions architects provide high-level system design guidance rather than creating the detailed data pipelines themselves.

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.

The described data consists of free-form text and image files that do not follow a predefined structure such as fixed columns or labelled keys. Because there is no upfront schema and the information cannot be queried with standard SQL until it is transformed, the collection is classified as unstructured data. Semi-structured data would include self-describing tags (for example, JSON), relational data would reside in tables with relationships, and time-series data is organized primarily around time-stamped numerical values, none of which match the scenario.