Microsoft Azure Data Fundamentals Practice Test (DP-900)

Azure Database for PostgreSQL is Microsoft's fully managed PaaS offering for the open-source PostgreSQL engine. Because it runs the community PostgreSQL code base, applications can use the same drivers, tools, and extensions after migration. Azure SQL Managed Instance and Azure SQL Database run the Microsoft SQL Server engine, not PostgreSQL. Azure Synapse Analytics is a data warehouse service, and Azure Database Migration Service is used for one-time or continuous migrations, not as a hosting platform.

Azure SQL Managed Instance is a Platform as a Service (PaaS) offering that provides near-100% compatibility with the SQL Server engine, including support for SQL Server Agent and cross-database queries. Because it is a managed service, Microsoft takes care of operating-system patching and in-place upgrades. Azure SQL Database (single or serverless) lacks some instance-level features such as SQL Server Agent and does not support cross-database transactions in the same way. SQL Server on Azure Virtual Machines offers full feature parity but leaves operating-system maintenance to the customer, which does not meet the stated requirement that Microsoft handle patching.

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.

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.

Session consistency provides a "read-your-writes" guarantee within a client session, so the writer sees its own committed updates immediately. Outside that session, reads can be slightly stale, which improves latency and throughput compared to strong or bounded-staleness consistency. Strong consistency forces all clients to see the latest version, increasing latency. Eventual and consistent prefix offer no read-your-writes guarantee, so even the writing session could receive older data. Therefore, session consistency is the appropriate choice.

Azure File storage provides fully managed file shares that support both SMB and NFS, letting you mount the share just like a traditional network file share from Windows or Linux VMs. In addition, Azure File Sync can replicate and cache an Azure file share on on-premises Windows servers for fast local access and centralized cloud-based storage. Azure Blob storage is optimised for object data and cannot be mounted by SMB/NFS in the same way. Azure Table storage is a key-value NoSQL store, and Azure Queue storage handles message queuing; neither offers file-system semantics or compatibility with Azure File Sync.

Azure SQL Managed Instance is a platform as a service (PaaS) offering that Microsoft manages for backups, patching, and automatic updates. It delivers built-in high availability and offers nearly 100 percent compatibility with the SQL Server engine, including support for SQL Agent jobs and cross-database queries. Azure SQL Database single databases lack several instance-level features such as SQL Agent and cross-database transactions. SQL Server on Azure Virtual Machines provides full SQL Server compatibility but is an infrastructure-as-a-service (IaaS) option, so customers must handle patching, backups, and high-availability configuration themselves. Azure Database for MySQL is a managed service for the MySQL engine and does not meet the SQL Server compatibility requirement.

Azure File storage exposes fully managed file shares that are accessible simultaneously from Windows, Linux, and macOS via the SMB protocol. Because the share appears as a standard network drive, applications can use familiar file system operations. Azure Blob storage, Table storage, and Queue storage do not provide an SMB-compatible file share and therefore cannot be mounted as a network drive by multiple VMs.

Azure Cosmos DB offers several APIs, each optimized for a different data model. The Gremlin API implements the Apache TinkerPop graph model, making it the natural choice for applications that need to store and traverse vertices and edges-common in social networks, recommendation engines, and other relationship-centric scenarios. The SQL API targets document (JSON) data, the Cassandra API targets wide-column data, and the Table API provides basic key-value storage. While these alternatives can store related data, none provide the native graph traversal operators and optimizations that the Gremlin API delivers for complex relationship queries.

A point-of-sale application focuses on rapidly capturing individual transactions, requires ACID guarantees, and supports high concurrency; these characteristics define a transactional workload. Weekly reporting over historical data performs large-scale reads, aggregates data, and seeks trends for business intelligence, which are hallmarks of an analytical workload. Therefore, the sales application is transactional, while the reporting process is analytical. The other options either misclassify one or both activities or overlook the differing workload patterns.

A measure is defined with DAX and is evaluated at query time for the current filter context, so it does not add persistent storage to the data model. A calculated column stores its results in the model, increasing its size. Hierarchies organize existing fields for drill-down but do not perform calculations. Relationships define how tables connect and likewise do not create dynamic aggregations.

A foreign key constraint enforces referential integrity between two tables by ensuring that values in the child table (Orders) match existing primary-key values in the parent table (Customers). If a corresponding parent row does not exist, the database engine blocks the insert or update, preventing orphaned rows. A view is a virtual table that does not enforce data integrity. An index speeds up data retrieval but cannot enforce relationships. A stored procedure can contain logic that checks data, but the enforcement would be manual and not inherent to the table definition.

Creating and maintaining data ingestion and transformation pipelines is a core duty of a data engineer. Data engineers focus on the technical aspects of acquiring, transforming, and loading data so that downstream users can analyze it. A database administrator concentrates on operational tasks such as backups, patching, and security of database systems. A data analyst consumes prepared data to create reports and visualizations, not to build the pipelines. A security engineer focuses on safeguarding infrastructure and data, not on data movement processes.

Azure Cosmos DB is a fully managed NoSQL service that stores semi-structured data such as JSON documents, offers automatic and virtually unlimited throughput and storage scaling, and can replicate data to any Azure region while guaranteeing single-digit millisecond latency. These capabilities align with the needs of a global, low-latency gaming workload. In contrast, Azure SQL Database and Azure Database for PostgreSQL are relational services without turnkey global distribution or the same latency guarantees, and Azure Data Lake Storage Gen2 is optimized for analytical rather than real-time transactional scenarios.

The scenario matches an analytical workload. Analytical workloads are characterized by large, read-intensive queries that aggregate historical data to support business intelligence and reporting. Transactional workloads focus on frequent inserts, updates, and deletes of small sets of current data. Streaming workloads handle continuous, real-time event ingestion. An operational workload is a general term for day-to-day transaction processing, not large-scale analysis.

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.

Designing, building, and maintaining data ingestion and transformation pipelines is a core responsibility of a data engineer. Data engineers choose appropriate storage solutions, develop code or notebooks that prepare and load the data, automate the workflows, and ensure performance and reliability. Data analysts focus on exploring and visualizing data, database administrators manage database availability, configuration, and backups, while security engineers concentrate on security controls. Therefore, the activity described belongs to a data engineer.

A donut (doughnut) chart is specifically designed to show how individual parts contribute to a whole at a single point in time. Each segment's arc length is proportional to its share of the total, making it easy for users to compare categories and understand their relative importance without focusing on exact values.

A 100 percent stacked column chart can also display part-to-whole relationships, but it generally emphasizes changes across multiple categories or periods and is less intuitive when there is only one period to show. A line chart highlights trends over time rather than composition at a single point. A gauge is intended for tracking progress toward a single target, not for comparing multiple categories simultaneously. Therefore, the donut chart is the most appropriate choice in this scenario.

Azure File storage is designed to provide fully managed file shares in the cloud that use standard SMB (and optional NFS) protocols. It also integrates with Azure File Sync, allowing Windows servers on-premises to cache and synchronize files locally while keeping the authoritative copy in Azure. Blob storage, Queue storage, and Table storage do not expose SMB file shares and cannot be used with Azure File Sync for on-premises caching, so they do not satisfy the stated requirements.

Scheduling and validating backups is a core operational duty of a database administrator because they are charged with protecting data and ensuring recoverability. Designing ETL pipelines is typically handled by data engineers, who focus on data ingestion and transformation. Exploring data and building visual reports is the domain of data analysts. Training predictive models is associated with data scientists or specialized analytics roles, not database administrators.