DP-900 Practice Test 47 — 15 Questions

Question 1

Refer to the exhibit. You are reviewing an ARM template snippet for an Azure storage account. You need to ensure that the storage account supports POSIX-like permissions for data lake workloads. Which property must be enabled?

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

Accepted Answer

"hierarchicalNamespace": {"enabled": true}. The correct option is B, "hierarchicalNamespace": {"enabled": true}, because enabling a hierarchical namespace on an Azure Storage account is what upgrades it to Data Lake Storage Gen2 capabilities, including POSIX-like access control lists (ACLs) and directory-level permissions required for data lake workloads. Without this property set to enabled, the account remains a flat Blob Storage namespace and cannot apply POSIX-style permissions. Option A (allowBlobPublicAccess: false) only restricts anonymous public read access and has no bearing on POSIX permissions. Option C (kind: DataLakeStorageGen2) is not a valid value for the kind property in ARM templates; the valid kind is StorageV2 (or BlockBlobStorage/BlobStorage/FileStorage), and Data Lake behavior is enabled via the hierarchical namespace flag. Option D (keySource: Microsoft.Storage) only specifies that encryption keys are managed by Microsoft, which is unrelated to POSIX-like permissions.

Answer

"allowBlobPublicAccess": false

Answer

"kind": "DataLakeStorageGen2"

Answer

"keySource": "Microsoft.Storage"

Question 2

A retail company processes petabytes of sales transaction data stored in Azure Data Lake Storage Gen2. They need to run recurring complex queries that involve large joins and aggregations. The queries must consistently complete within a fixed time window overnight. The company wants predictable performance and costs. Which Azure service should they use?

Accepted Answer

Azure Synapse Analytics Dedicated SQL pool. Azure Synapse Analytics Dedicated SQL pool provides reserved, fixed compute resources that ensure predictable performance and cost for recurring complex queries involving large joins and aggregations. It is designed for petabyte-scale data warehousing workloads with consistent SLAs, making it ideal for overnight batch processing within a fixed time window.

Answer

Azure Synapse Analytics Serverless SQL pool

Answer

Azure SQL Database

Answer

Azure Analysis Services

Question 3

Refer to the exhibit. You are configuring a custom role in Azure RBAC for a team that needs to read and list blobs in a storage account. The JSON snippet shows the permissions assigned. After assigning this role to a user, they report they cannot see the storage account in the Azure portal. What is the most likely cause?

Accepted Answer

The role does not include read permission on the storage account resource.. The custom role definition only includes dataActions for reading and listing blobs, but lacks any actions that grant read permission on the storage account resource itself. In Azure RBAC, viewing a storage account in the Azure portal requires the 'Microsoft.Storage/storageAccounts/read' action at the resource scope. Without this, the user cannot see the storage account in the portal, even though they can interact with blobs via APIs or tools that bypass the portal.

Answer

The dataActions should be actions instead of dataActions.

Answer

The role is not assigned at the subscription scope.

Answer

The user needs the Contributor role to view the storage account.

Question 4

You are storing log files from multiple applications in Azure Blob Storage. Each log file is a text file with timestamp data. You need to query logs for a specific date range using SQL. Which Azure service can query these files directly?

Accepted Answer

Azure Synapse Serverless SQL. Azure Synapse Serverless SQL pool can query text files in Azure Blob Storage directly using T-SQL with OPENROWSET or external tables, without loading the data. It supports querying CSV, JSON, and Parquet files and can filter by timestamp columns. This makes it ideal for ad-hoc SQL queries over log files stored in Blob Storage.

Answer

Azure Stream Analytics

Answer

Azure Data Lake Storage

Answer

Azure Analysis Services

Question 5

You are designing a solution to store large binary files (videos) for a media company. The solution must support tiered storage to optimize costs based on access frequency. Which Azure storage option should you use?

Accepted Answer

Azure Blob Storage. Azure Blob Storage is the correct choice because it is designed for storing large unstructured binary data (videos, images, documents) and supports tiered storage — Hot, Cool, Cold, and Archive access tiers — to optimize costs based on access frequency. Cosmos DB is a NoSQL database, Azure Files is a managed SMB/NFS file share, and Azure Disk Storage provides block-level disks for VMs, none of which offer blob-specific tiering for large media files.

Answer

Azure Cosmos DB

Answer

Azure Files

Answer

Azure Disk Storage

Question 6

Refer to the exhibit. You have a CSV file stored in Azure Blob Storage. You want to query this file using Azure Synapse Serverless SQL. Which OPENROWSET option should you use?

Accepted Answer

FORMAT = 'CSV'. OPENROWSET in Azure Synapse serverless SQL pools uses the FORMAT argument to tell the parser how to interpret the file. Since the file is a CSV, FORMAT = 'CSV' is the correct option — it invokes the CSV parser and allows you to specify FIELDTERMINATOR, ROWTERMINATOR, and FIRSTROW parameters. The other FORMAT values correspond to entirely different file structures and would cause parsing errors or empty result sets.

Answer

FORMAT = 'JSON'

Answer

FORMAT = 'PARQUET'

Answer

FORMAT = 'DELTA'

Question 7

A company uses Azure SQL Database for an e-commerce platform. The Orders table has columns: OrderID (primary key, clustered), CustomerID, OrderDate, TotalAmount. Queries frequently filter on CustomerID and a range of OrderDate, and then sort the results by OrderDate in descending order. The queries also return the TotalAmount column. Which indexing strategy will most improve query performance for these operations?

Accepted Answer

Create a nonclustered index on (CustomerID, OrderDate DESC) with included column TotalAmount. It creates a covering index that matches the query's filter predicates (CustomerID equality, OrderDate range) and sort order (OrderDate DESC). By including TotalAmount as an included column, the index fully satisfies the query without needing to access the clustered index (key lookup), minimizing I/O and improving performance.

Answer

Create a nonclustered index on (OrderDate DESC, CustomerID) with included column TotalAmount

Answer

Change the clustered index to be on (CustomerID, OrderDate DESC)

Answer

Create a nonclustered index on (OrderDate DESC) without including TotalAmount

Question 8

A company stores customer support chat transcripts as plain text files in Azure Blob Storage. The files are accessed frequently for the first 30 days, then infrequently for the next 2 years, and after that must be retained for 7 years for compliance but are rarely accessed. The company wants to minimize storage costs by automatically moving data through appropriate access tiers. Which Azure Blob Storage lifecycle management policy should they implement?

Accepted Answer

Move blobs from Hot to Cool after 30 days, then to Archive after 2 years. The lifecycle management policy matches the access pattern: move blobs from Hot (frequent access for first 30 days) to Cool (infrequent access for next 2 years) after 30 days, then to Archive (rare access for 7-year compliance) after 2 years. This minimizes storage costs by using the cheapest tier for each phase while retaining data for the required 7-year compliance period.

Answer

Store all data in Hot tier for the full retention period

Answer

Move blobs from Hot to Archive after 30 days and delete after 2 years

Answer

Store all data in Cool tier for the first 30 days, then move to Archive

Question 9

A company is designing a new application that will store customer orders in a relational database on Azure. The data includes order IDs, customer IDs, product IDs, quantities, and order dates. The application needs to support complex queries that join multiple tables and enforce referential integrity. Which Azure service should the company use?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed relational database engine (SQL Server-based) that supports T-SQL, complex multi-table joins, foreign keys, and referential integrity constraints. The described workload — orders with IDs, quantities, dates, and joins across customers/products — is a classic OLTP relational pattern, which Azure SQL Database is purpose-built for.

Answer

Azure Table Storage

Answer

Azure Cosmos DB

Answer

Azure Blob Storage

Question 10

A global social media application allows users to post updates and 'like' posts. The application is designed to prioritize availability and partition tolerance over strong consistency. As a result, when a user likes a post, the like count may not be immediately visible to all users, but it will eventually become consistent across all regions. Which consistency model does this application follow?

Accepted Answer

Eventual consistency. The application prioritizes availability and partition tolerance, which aligns with the eventual consistency model. In this model, updates (like a 'like' count) are propagated asynchronously across replicas, and while reads may return stale data temporarily, all replicas will converge to the same value over time. This is typical of NoSQL systems like Apache Cassandra or Amazon DynamoDB when configured with eventual consistency.

Answer

Strong consistency

Answer

Consistent prefix

Answer

Bounded staleness

Question 11

A mobile app stores user preferences as JSON documents in Azure Cosmos DB. The document includes userId, theme, language, and notification settings. The most common query retrieves the document for a specific userId. To minimize cost and ensure even distribution, which property should be chosen as the partition key?

Accepted Answer

userId. The userId property is the ideal partition key because it provides high cardinality (each user has a unique ID) and ensures even request distribution across physical partitions. Since the most common query retrieves a document by userId, using it as the partition key makes those queries point reads (single-partition queries), which are the most cost-efficient and fastest in Azure Cosmos DB.

Answer

theme

Answer

language

Answer

a concatenation of userId and language

Question 12

A retail company analyzes customer purchase patterns. Every night, they run a batch job that aggregates millions of transactions from the past day into summary tables for reporting. Which type of data processing workload best describes this nightly job?

Accepted Answer

Batch processing. This nightly job processes a large volume of transactions accumulated over the past day in a single, scheduled run, which is the defining characteristic of batch processing. In Azure, this workload would typically be implemented using Azure Synapse Analytics or Azure Data Factory to orchestrate the aggregation of millions of rows into summary tables for reporting, without requiring immediate output.

Answer

Real-time processing

Answer

Streaming processing

Answer

Transactional processing

Question 13

Your company has a data lake in Azure Data Lake Storage Gen2 containing terabytes of parquet files. Data scientists need to explore and prepare this data using Python and SQL. They want to use a collaborative notebook environment that integrates with Git for version control. The solution should automatically scale compute resources based on workload demand and minimize management overhead. Which Azure service should you use?

Accepted Answer

Azure Databricks. Azure Databricks is the correct choice because it provides a collaborative notebook environment that natively supports Python and SQL, integrates with Git for version control, and offers auto-scaling clusters that dynamically adjust compute resources based on workload demand. It is purpose-built for big data analytics and data preparation on data lakes, minimizing management overhead through its serverless and managed Spark infrastructure.

Answer

Azure Machine Learning studio

Answer

Azure Data Studio

Answer

Azure Synapse Studio

Question 14

Which TWO are valid ways to secure data in transit for an Azure SQL Database?

Accepted Answer

Use the 'Encrypt connection' setting in connection strings. Option A is correct because the 'Encrypt connection' setting in a connection string (e.g., Encrypt=True;TrustServerCertificate=False) forces the client driver to negotiate an encrypted channel to Azure SQL Database, protecting data in transit. Option B is correct because requiring TLS 1.2 for client connections ensures that only modern, secure transport encryption is used between the client and the database, preventing downgrade to weaker protocols. Option C is not correct because Transparent Data Encryption protects data at rest by encrypting database files, not data moving over the network. Option D is not correct because firewall rules restrict which IP addresses can reach the server but do not encrypt the traffic itself. Option E is not correct because Always Encrypted protects sensitive columns at rest and in memory on the client side, not the transport channel.

Answer

Enable Transparent Data Encryption (TDE)

Answer

Configure firewall rules to allow only specific IPs

Answer

Use Always Encrypted

Question 15

A data analyst needs to create interactive reports from data stored in an Azure SQL Database. They want to use a self-service tool that requires minimal IT support. Which tool should they use?

Accepted Answer

Power BI Desktop. Power BI Desktop is a self-service business intelligence tool designed for creating interactive reports and dashboards with minimal IT support. It connects directly to Azure SQL Database, allowing analysts to import or query data using DirectQuery, and provides drag-and-drop visualizations without requiring database administration skills.

Answer

Azure Synapse Studio

Answer

SQL Server Management Studio

Answer

Azure Data Studio