DP-900 Practice Test 16 — 15 Questions

Question 1

A data engineer needs to load 500 GB of CSV files from an on-premises server into Azure Data Lake Storage Gen2 daily. The data must be transferred securely over the internet. Which Azure tool should they use?

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

Accepted Answer

AzCopy. AzCopy is the correct tool because it is a command-line utility designed for high-performance, secure copying of data to and from Azure Blob Storage and Azure Data Lake Storage Gen2. It supports the required 500 GB daily transfer over the internet using HTTPS encryption, and can be scripted for automation without the overhead of a full orchestration service.

Answer

Azure Data Factory

Answer

Azure PowerShell

Answer

Azure Import/Export service

Question 2

A company uses Azure SQL Database and needs to implement data masking for sensitive columns like email addresses and credit card numbers, so that only authorized users can see the actual data. Which feature should they configure?

Accepted Answer

Dynamic Data Masking. Dynamic Data Masking (DDM) is the correct feature because it limits exposure of sensitive data by obfuscating columns (e.g., email addresses, credit card numbers) in query results to non-privileged users, while authorized users (with EXEMPT or UNMASK permission) see the actual data. This directly meets the requirement of masking sensitive columns without altering the underlying stored data.

Answer

Row-Level Security

Answer

Auditing

Answer

Transparent Data Encryption (TDE)

Question 3

You are a data engineer for a large e-commerce company. The company uses Azure Data Lake Storage Gen2 to store customer transaction data. They also use Azure Databricks for data transformation and Azure Synapse Serverless SQL pool for ad-hoc queries. Recently, the data lake has grown to 10 TB, and query performance in Synapse Serverless has degraded significantly. Users complain that queries that used to take seconds now take minutes. You need to improve query performance without moving data to a dedicated SQL pool. The data is stored in Parquet format, partitioned by date. You notice that the queries often filter on CustomerID and Date. Current queries scan all partitions even when only a few days are needed. What is the most effective solution to improve performance?

Accepted Answer

Repartition the Parquet files by both date and CustomerID, and optimize file sizes to 1 GB each. Repartitioning the Parquet files by both date and CustomerID enables partition pruning in Azure Synapse Serverless SQL pool. When queries filter on CustomerID and Date, the engine can skip irrelevant partitions entirely, drastically reducing the amount of data scanned. Optimizing file sizes to around 1 GB ensures efficient parallelism and avoids the overhead of many small files, which degrades performance in a serverless environment.

Answer

Create materialized views in the Serverless SQL database on the partitioned data

Answer

Convert all Parquet files to CSV and use row-level security to limit data access

Answer

Increase the service level of the Synapse workspace to improve query concurrency

Question 4

Match each Azure storage redundancy option to its description.

Accepted Answer

LRS: Data is replicated three times within a single datacenter in the primary region.. Azure storage redundancy options differ in durability and availability. LRS is cost-effective, ZRS protects against zone failures, GRS adds geo-replication, and RA-GRS enables read access to the secondary region.

Answer

LRS: Data is replicated across multiple geographical regions.

Answer

ZRS: Data is replicated within a single datacenter.

Question 5

A company uses Azure SQL Database for an e-commerce application. The Orders table has millions of rows. Queries frequently filter on OrderDate and OrderStatus, and sort by OrderDate descending. Which indexing strategy will most improve query performance?

Accepted Answer

Create a non-clustered index on (OrderDate DESC, OrderStatus) and keep the existing clustered index on OrderID. Creates a covering index for the most common query pattern: filtering on OrderDate and OrderStatus, and sorting by OrderDate descending. By specifying DESC in the index key, the index is ordered in the same direction as the sort, allowing SQL Server to avoid a sort operation and retrieve rows in order directly from the index. This non-clustered index can satisfy the query entirely without touching the clustered index (OrderID), reducing I/O and improving performance.

Answer

Create a clustered index on OrderDate and a non-clustered index on OrderStatus

Answer

Create a non-clustered index on (OrderDate, OrderStatus) and keep the existing clustered index on OrderID

Answer

Create a clustered index on OrderID and a non-clustered index on (OrderStatus, OrderDate)

Question 6

A healthcare organization stores patient records in a relational database table with fixed columns for PatientID, Name, and DateOfBirth. Additionally, they store clinical notes as free-form text files for each patient visit. Which statement correctly classifies these data types?

Accepted Answer

Patient records are structured data, and clinical notes are unstructured data.. Patient records stored in a relational database table with fixed columns (PatientID, Name, DateOfBirth) conform to a predefined schema, making them structured data. Clinical notes stored as free-form text files lack a fixed schema or organization, which classifies them as unstructured data. Option B correctly identifies this distinction.

Answer

Both patient records and clinical notes are examples of unstructured data.

Answer

Both patient records and clinical notes are examples of structured data.

Answer

Patient records are unstructured data, and clinical notes are semi-structured data.

Question 7

A small business wants to use Azure to store and analyze customer feedback from surveys. The surveys are collected via a web app and stored as JSON files. The business needs to run SQL-based queries on the data and generate reports in Power BI. They have a limited budget and prefer a serverless option to minimize management overhead. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics serverless SQL pool. Azure Synapse Analytics serverless SQL pool is the correct choice because it allows you to query JSON files directly from Azure Data Lake Storage or Blob Storage using standard T-SQL, without provisioning any infrastructure. It is serverless (pay-per-query), supports SQL-based queries, and integrates seamlessly with Power BI for reporting, making it ideal for a small business with a limited budget and minimal management overhead.

Answer

Azure Analysis Services

Answer

Azure Databricks

Answer

Azure SQL Database

Question 8

You are reviewing the Azure Data Factory mapping data flow configuration above. Which transformation is missing to ensure that only sales from the current year are loaded?

Accepted Answer

Filter transformation. The Filter transformation is used in mapping data flows to restrict rows based on a condition. To load only sales from the current year, you would apply a filter condition such as `year(SalesDate) == year(currentDate())`, which removes all rows not matching the current year. This is the correct transformation for row-level filtering.

Answer

Derived column transformation

Answer

Aggregate transformation

Answer

Window transformation

Question 9

Your company has a data pipeline in Azure Data Factory that runs daily. Recently, the pipeline started failing with timeouts. You suspect a downstream database is slow. What should you do to monitor and alert on pipeline run duration?

Accepted Answer

Configure alerts in Azure Monitor based on pipeline run duration. Azure Monitor can be configured to create alerts based on metrics such as pipeline run duration. When the duration exceeds a threshold, an alert triggers, allowing proactive notification of slow downstream databases. This directly addresses the need to monitor and alert on pipeline performance issues.

Answer

Query the pipeline runs in Log Analytics

Answer

Check Azure Service Health for issues

Answer

Use Azure Advisor to check performance recommendations

Question 10

You are designing a data pipeline for a social media analytics platform. The pipeline needs to ingest posts from multiple sources (Twitter, Facebook) in real time, transform the data by adding sentiment scores, and store the results in a data store for later analysis. The transformation logic is simple and can be expressed as a SQL query. You want to minimize coding effort. Which Azure service should you use for the transformation step?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time data processing with SQL-like query language, allowing you to transform streaming data (e.g., from Twitter and Facebook) by adding sentiment scores using simple SQL expressions without writing custom code. It integrates natively with Azure Event Hubs or IoT Hub for ingestion and outputs to Azure SQL Database, Cosmos DB, or Blob Storage for analysis, minimizing coding effort.

Answer

Azure Data Factory

Answer

Azure Databricks

Answer

Azure Functions

Question 11

Your organization uses Azure Data Lake Storage Gen2 as a data lake. You need to enforce data retention policies automatically, such as deleting files older than 90 days. Which Azure feature should you use?

Accepted Answer

Azure Blob Storage lifecycle management. Azure Blob Storage lifecycle management allows you to define rules that automatically delete or tier blobs based on age. Since Azure Data Lake Storage Gen2 is built on top of Azure Blob Storage, you can use lifecycle management policies to delete files older than 90 days by setting a 'Delete blob' action with a 'daysAfterModificationGreaterThan' filter of 90.

Answer

Azure Policy

Answer

Azure Data Factory

Answer

Azure RBAC

Question 12

You need to migrate an on-premises SQL Server database to Azure. The database uses many stored procedures and CLR assemblies. Which Azure service is most compatible without requiring major application changes?

Accepted Answer

Azure SQL Managed Instance. Azure SQL Managed Instance (D) is the most compatible choice because it provides near 100% compatibility with the on-premises SQL Server engine, including full support for CLR assemblies and stored procedures. Unlike Azure SQL Database, Managed Instance does not require rewriting or refactoring code that relies on SQL Server Agent, cross-database queries, or CLR integration, making it ideal for lift-and-shift migrations without major application changes.

Answer

Azure Database for MySQL

Answer

Azure Virtual Machines with SQL Server

Answer

Azure SQL Database

Question 13

Refer to the exhibit. A data engineer is reviewing an ARM template for a storage account. What does the property 'isHnsEnabled' set to true indicate?

Accepted Answer

The storage account supports Azure Data Lake Storage Gen2. The 'isHnsEnabled' property, when set to true, enables the Hierarchical Namespace (HNS) on the storage account. HNS is the core feature that differentiates Azure Data Lake Storage Gen2 from a standard blob storage account, allowing for a true file system hierarchy with POSIX-like access control lists (ACLs). This enables the storage account to support Data Lake Storage Gen2 workloads.

Answer

Blob soft delete is enabled

Answer

Versioning is enabled for blobs

Answer

Geo-redundant storage is configured

Question 14

Refer to the exhibit. You deploy this Azure Stream Analytics job. The job runs but no data is written to the Azure SQL Database table. What is the most likely cause?

Accepted Answer

The TumblingWindow has not yet elapsed. The TumblingWindow function in Azure Stream Analytics processes data in fixed, non-overlapping time intervals. Since the job runs but no data is written, the most likely cause is that the first window has not yet elapsed, meaning no aggregation has been triggered to output results. Stream Analytics only emits results when a tumbling window completes, so data will not appear in the SQL Database until the window duration passes.

Answer

The query syntax is invalid

Answer

The Event Hub name is incorrect

Answer

The SKU does not support SQL output

Question 15

A manufacturing company ingests real-time sensor data from assembly line machines into Azure Event Hubs. The company needs to calculate a 5-minute rolling average of temperature readings for each machine and compare it against a static threshold value stored in a CSV file in Azure Blob Storage. If the average exceeds the threshold, an alert must be triggered. Which Azure service should be used for this real-time data processing?

Accepted Answer

Azure Stream Analytics. Azure Stream Analytics is the correct choice because it is designed for real-time stream processing, including windowed aggregations like a 5-minute rolling average. It can directly ingest data from Azure Event Hubs, perform the calculation using a TumblingWindow or HoppingWindow function, and reference static data (the threshold CSV) from Azure Blob Storage via a reference data input. If the computed average exceeds the threshold, Stream Analytics can output the alert to a sink like Azure Functions or a notification service.

Answer

Azure Data Factory

Answer

Azure Synapse Analytics

Answer

Azure HDInsight