DP-900 Practice Test 16 — 15 Questions

Question 1

A company is migrating a 2-TB on-premises SQL Server database to Azure. The database uses SQL Server Agent jobs for scheduled maintenance, relies on linked servers to query data from another SQL Server instance, and requires cross-database queries within the same instance. The company wants a fully managed PaaS service that minimizes application code changes and provides automatic backups and patching. Which Azure SQL service should they choose?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance is the correct choice because it provides near 100% compatibility with on-premises SQL Server, including support for SQL Server Agent jobs, linked servers, and cross-database queries within the same instance. As a fully managed PaaS service, it offers automatic backups, patching, and high availability while minimizing application code changes, unlike Azure SQL Database which lacks instance-scoped features.

Answer

Azure SQL Database (Single Database)

Answer

Azure SQL Database (Elastic Pool)

Answer

SQL Server on Azure Virtual Machine

Question 2

You need to design a data storage solution for an e-commerce platform that requires ACID transactions for order processing and high availability across regions. Which Azure service meets these requirements?

Accepted Answer

Azure SQL Database with active geo-replication. Azure SQL Database with active geo-region replication supports ACID transactions natively and provides automatic failover to a secondary region, ensuring high availability across regions. This meets the e-commerce platform's need for transactional consistency and regional resilience.

Answer

Azure Database for MySQL with read replicas

Answer

Azure Synapse Analytics

Answer

Azure Cosmos DB with multiple write regions

Question 3

A smart home company stores device telemetry in Azure Cosmos DB using the NoSQL API. Each document contains: deviceId (string), timestamp (datetime), temperature (float), humidity (float). The most common query retrieves all documents for a specific deviceId within a time range, ordered by timestamp descending. This query performs well. However, a new query that finds devices with temperature > 50 in the last hour (without specifying deviceId) is extremely slow and consumes many request units (RUs). What is the most likely cause?

Accepted Answer

The query does not specify the partition key (deviceId), causing a cross-partition query that scans every physical partition.. In Azure Cosmos DB NoSQL API, the partition key (deviceId) determines data distribution across physical partitions. Queries that do not include the partition key in the filter become cross-partition queries, which must fan out to every physical partition, scanning all documents. This is extremely slow and consumes many RUs, especially in large containers. The original query specifying deviceId performs well because it targets a single partition.

Answer

The temperature field is not indexed by default, so the query forces a full scan of all documents.

Answer

The time range filter on timestamp cannot be combined with the temperature filter efficiently.

Answer

The default indexing policy only indexes strings and numbers as range indexes, but temperature is stored as a number and is indexed.

Question 4

A company uses Azure Cosmos DB for a globally distributed application. They need to ensure low-latency reads and writes for users in multiple regions. Which consistency level provides the strongest guarantees without sacrificing availability?

Accepted Answer

Bounded staleness. Bounded staleness provides the strongest consistency guarantee that still maintains availability during a partition. It ensures that reads are guaranteed to be within a configurable staleness window (either K versions or a time interval) from the latest write, which is stronger than consistent prefix or eventual consistency, while avoiding the availability trade-offs of strong consistency in a globally distributed Azure Cosmos DB account.

Answer

Consistent prefix

Answer

Strong

Answer

Eventual

Question 5

A company uses Azure SQL Database and wants to implement row-level security so that sales managers can only see data for their own region. Which feature should they use?

Accepted Answer

Row-level security (RLS). Row-level security (RLS) is the correct feature because it allows you to control access to rows in a database table based on the characteristics of the user executing a query. In this scenario, RLS can be implemented using a security policy and a predicate function that filters rows based on the sales manager's region, ensuring they only see data for their own region.

Answer

Dynamic Data Masking

Answer

Transparent Data Encryption (TDE)

Answer

Microsoft Purview

Question 6

A manufacturing company stores two types of data: (1) real-time sensor readings from production machines used to monitor current machine status, and (2) historical daily production summaries used by managers to identify trends over months. Which statement accurately describes these workloads?

Accepted Answer

Sensor readings are an OLTP workload; daily summaries are an OLAP workload.. Real-time sensor readings involve frequent, small inserts and point lookups (typical of an OLTP workload), while historical daily summaries are aggregated data used for trend analysis over months (typical of an OLAP workload). OLTP systems handle high-volume transactional operations, whereas OLAP systems support complex queries and aggregations on large historical datasets.

Answer

Sensor readings are an OLAP workload; daily summaries are an OLTP workload.

Answer

Sensor readings are a NoSQL workload; daily summaries are a relational workload.

Answer

Sensor readings are a batch workload; daily summaries are a real-time workload.

Question 7

A company's data engineering team uses Azure Data Factory to orchestrate a pipeline that ingests data from Azure Blob Storage, transforms it using Azure Databricks, and loads it into Azure Synapse Dedicated SQL Pool. The pipeline fails intermittently due to transient errors. Which pattern should they implement to improve reliability?

Accepted Answer

Configure retry policy with exponential backoff on activities. Configuring a retry policy with exponential backoff on the Azure Data Factory activities directly addresses transient errors (e.g., network blips, throttling) by automatically retrying the failed activity after increasing delays. This pattern is specifically designed for intermittent failures and is a built-in feature of Azure Data Factory, improving pipeline reliability without architectural changes.

Answer

Replace Azure Databricks with Azure Functions

Answer

Increase the pipeline timeout to 24 hours

Answer

Split the pipeline into multiple smaller pipelines

Question 8

A data analyst needs to query large datasets stored as Parquet files in Azure Data Lake Storage Gen2. The queries are ad-hoc and infrequent. The analyst wants to run SQL queries directly on the data without creating any storage or compute infrastructure, and only pay for the amount of data processed. They also need to create T-SQL views to simplify queries for Power BI reports. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool (Option B) is the correct choice because it allows querying data directly from Azure Data Lake Storage Gen2 using T-SQL without provisioning any compute or storage infrastructure. It supports ad-hoc, infrequent queries with a pay-per-query pricing model based on the amount of data processed, and it enables the creation of T-SQL views that can be used directly by Power BI for reporting. In contrast, Option A (Azure SQL Database) requires provisioning a database with compute resources, incurring continuous costs. Option C (Azure HDInsight with Spark) requires a persistent cluster and is not optimized for infrequent ad-hoc queries. Option D (Azure Databricks) also requires compute resources and is not a pay-per-query service.

Answer

Azure SQL Database

Answer

Azure HDInsight with Spark

Answer

Azure Databricks

Question 9

A consulting firm collects client information in two forms: a spreadsheet with columns for Name, Address, and Phone Number, and audio recordings of client meetings. Which of the following statements correctly categorizes these data types?

Accepted Answer

The spreadsheet data is structured, and the audio recordings are unstructured.. The spreadsheet data with columns for Name, Address, and Phone Number has a predefined schema (rows and columns), making it structured data. Audio recordings are binary files with no inherent schema or organization, fitting the definition of unstructured data. Option C correctly pairs these classifications.

Answer

Both the spreadsheet data and the audio recordings are examples of structured data.

Answer

The spreadsheet data is structured, and the audio recordings are semi-structured.

Answer

The spreadsheet data is semi-structured, and the audio recordings are unstructured.

Question 10

Which TWO Azure services can be used to store semi-structured data? (Choose two.)

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is a NoSQL database service that natively supports semi-structured data through its flexible schema model. It allows storing JSON documents, key-value pairs, and graph data without requiring a fixed schema, making it ideal for semi-structured data like user profiles, IoT telemetry, or product catalogs.

Answer

Azure SQL Database

Answer

Azure Synapse Analytics

Answer

Power BI

Question 11

A company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database has a large fact table that is partitioned by date (monthly partitions) to improve query performance and simplify data archiving. The company wants to maintain the same partitioning strategy in Azure to avoid rewriting queries. Which feature in Azure SQL Managed Instance should they use to achieve this?

Accepted Answer

Table partitioning with partition functions and schemes. Azure SQL Managed Instance supports table partitioning using partition functions and partition schemes, which is the same feature available in SQL Server. This allows you to define monthly partitions on the fact table using a date column, preserving the existing partitioning strategy and query logic without modification. The partition function maps rows to partitions based on the date boundary values, and the partition scheme assigns those partitions to filegroups.

Answer

Sharding across multiple Azure SQL Managed Instances

Answer

Index partitioning only

Answer

Federated tables

Question 12

A company wants to run complex analytics queries across petabytes of data stored in Azure Data Lake Storage. They need a serverless option that supports T-SQL. Which Azure service should they use?

Accepted Answer

Azure Synapse Serverless SQL pool. Azure Synapse Serverless SQL pool is the correct choice because it provides a serverless, on-demand query service that allows you to run T-SQL queries directly against data stored in Azure Data Lake Storage (ADLS). It supports complex analytics over petabytes of data without provisioning any infrastructure, and it uses T-SQL as the query language, meeting all the stated requirements.

Answer

Azure SQL Database serverless

Answer

Azure Analysis Services

Answer

Azure Databricks

Question 13

Your company stores sensitive customer data in Azure SQL Database. You need to implement column-level encryption for the 'SSN' column using a customer-managed key stored in Azure Key Vault. Which feature should you use?

Accepted Answer

Always Encrypted. Always Encrypted is the correct feature because it allows client-side encryption of sensitive columns, such as 'SSN', using a customer-managed key stored in Azure Key Vault. The encryption keys are never exposed to the database engine, ensuring that even database administrators cannot view the plaintext data. This meets the requirement for column-level encryption with customer-managed keys.

Answer

Azure Policy

Answer

Transparent Data Encryption (TDE)

Answer

Dynamic Data Masking

Question 14

A company is designing a data solution for their e-commerce platform. They need to store product catalogs with varying attributes, support high-throughput read/write operations, and ensure low-latency access globally. Which Azure data store is most appropriate?

Accepted Answer

Azure Cosmos DB. Azure Cosmos DB is the most appropriate choice because it is a globally distributed, multi-model database service that supports schema-agnostic storage of product catalogs with varying attributes. It offers guaranteed single-digit-millisecond latency for reads and writes at any scale, and its turnkey global distribution enables low-latency access from multiple regions, meeting the e-commerce platform's high-throughput and global requirements.

Answer

Azure SQL Database

Answer

Azure Redis Cache

Answer

Azure Data Lake Storage

Question 15

Your company stores IoT sensor data in Azure Blob Storage. Data analysts need to query the data using SQL without moving it. Which Azure service should you use?

Accepted Answer

Azure Synapse Serverless SQL. Azure Synapse Serverless SQL is the correct choice because it provides a SQL-based query engine that can directly query data stored in Azure Blob Storage using T-SQL, without requiring data movement or a dedicated data warehouse. It uses a pay-per-query model and supports reading various file formats like Parquet, CSV, and JSON, making it ideal for ad-hoc analytical queries on IoT sensor data.

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage

Answer

Azure SQL Database