DP-900 Practice Test 15 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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 8

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 9

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 10

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 11

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

Question 12

A gaming company stores player session data as JSON documents. Each document contains fields like sessionId, userId, startTime, and a varying set of optional fields such as deviceType or campaignId. The application needs to query sessions by userId and startTime range using SQL-like queries, and also by sessionId with low latency. Which Azure Cosmos DB API should the company choose?

Accepted Answer

SQL (Core) API. The SQL (Core) API is the correct choice because it natively supports SQL-like queries over JSON documents, enabling efficient filtering by userId and startTime range. It also provides low-latency point reads by sessionId when a well-designed partition key (e.g., /userId) is used, and it offers automatic indexing of all JSON properties, including optional fields like deviceType or campaignId.

Answer

MongoDB API

Answer

Table API

Answer

Gremlin (Graph) API

Question 13

A company stores customer names and addresses in a relational table, product descriptions as JSON files, and product images as JPEG files. Which of the following correctly classifies these data types from most structured to least structured?

Accepted Answer

Structured (customer table), Semi-structured (JSON), Unstructured (JPEG). A is correct because structured data (customer table) has a fixed schema with rows and columns, semi-structured data (JSON) uses tags or key-value pairs without a rigid schema, and unstructured data (JPEG) has no predefined structure. The question tests the standard classification hierarchy from most to least structured.

Answer

Structured (customer table), Unstructured (JSON), Semi-structured (JPEG)

Answer

Semi-structured (customer table), Structured (JSON), Unstructured (JPEG)

Answer

Unstructured (customer table), Structured (JSON), Semi-structured (JPEG)

Question 14

A company uses Azure Synapse Analytics to run large-scale batch processing jobs every night. The jobs currently take 6 hours to complete, but the business requires completion within 4 hours. Which action should the company take to improve job performance?

Accepted Answer

Increase the data warehouse units (DWUs) for the dedicated SQL pool.. Increasing the data warehouse units (DWUs) for the dedicated SQL pool scales the compute resources (CPU, memory, and I/O bandwidth) available to the Synapse SQL pool. This directly reduces the execution time of batch processing jobs by allowing more parallel processing, enabling the 6-hour job to complete within the required 4-hour window.

Answer

Replace PolyBase with Azure Data Factory for data movement.

Answer

Migrate from serverless SQL pool to dedicated SQL pool.

Answer

Move the underlying data to Azure Data Lake Storage Gen2.

Question 15

A travel booking application stores booking data in Azure Cosmos DB using the NoSQL API. Each booking document contains: BookingID (unique), UserID, Destination, TravelDate, Price. The most common query is: 'Retrieve all bookings for a specific UserID, sorted by TravelDate descending.' To minimize Request Unit (RU) consumption, which property should be chosen as the partition key?

Accepted Answer

UserID. UserID is the correct partition key because the most common query filters on UserID, and Cosmos DB routes queries to the exact physical partition(s) containing that UserID's data. This minimizes cross-partition fan-out, reducing RU consumption. A partition key should align with the primary query filter to enable efficient point-read or single-partition query execution.

Answer

BookingID

Answer

Destination

Answer

TravelDate