DP-900 Practice Exam 63 — 20 Questions

Question 1

A company uses Azure Databricks to process data stored in Azure Data Lake Storage Gen2. They need to enforce fine-grained access control on files and folders based on user identity. Which security feature should they implement?

Accepted Answer

Access control lists (ACLs). Access control lists (ACLs) on Azure Data Lake Storage Gen2 provide POSIX-compliant, fine-grained permissions at the file and folder level. This allows you to grant read, write, or execute permissions to specific users or groups, which is exactly what is needed for enforcing identity-based access control on individual files and folders.

Answer

Storage account firewall rules

Answer

Shared access signatures (SAS)

Answer

Azure RBAC roles on the storage account

Question 2

A company uses Azure Blob Storage to store video files for a streaming service. The files are accessed frequently for the first 30 days after upload, then rarely after. The company wants to minimize storage costs while maintaining fast access for frequently accessed files. What should they implement?

Accepted Answer

Blob lifecycle management policies. Blob lifecycle management policies allow you to automatically transition blobs to cooler, cheaper access tiers (e.g., from Hot to Cool or Archive) based on age. This directly addresses the requirement: after 30 days of frequent access, the policy moves the video files to a lower-cost tier, reducing storage costs while keeping the Hot tier available for the initial high-access period.

Answer

Azure Content Delivery Network (CDN)

Answer

Azure Files shares

Answer

Geo-redundant storage (GRS)

Question 3

Your company uses Azure SQL Database to power a global application. You need to ensure that users in Europe and Asia have low-latency read access to product data, while writes are synchronized across all regions. What should you configure?

Accepted Answer

Configure Active Geo-Replication with readable secondaries in Europe and Asia.. Active Geo-Replication for Azure SQL Database allows you to configure readable secondary replicas in different Azure regions. This provides low-latency read access for users in Europe and Asia by directing their read traffic to the nearest secondary, while writes are synchronized asynchronously to all secondaries, ensuring data consistency across regions.

Answer

Use Azure Traffic Manager to route users to the nearest Azure SQL Database instance.

Answer

Migrate to Azure Cosmos DB for multi-region writes.

Answer

Create a failover group that includes all regions.

Question 4

Refer to the exhibit. You create an external table in Azure SQL Database. Which data source is being used?

Accepted Answer

Azure Blob Storage. The exhibit shows an external table referencing a data source with the LOCATION set to 'https://mystorage.blob.core.windows.net/...', which is the endpoint for Azure Blob Storage. In Azure SQL Database, external tables are created over external data sources that point to Azure Blob Storage or Azure Data Lake Storage, but the URL format 'blob.core.windows.net' specifically indicates Azure Blob Storage. The CREATE EXTERNAL TABLE statement uses this data source to read data stored as files (e.g., CSV, Parquet) in the blob container.

Answer

Azure Files

Answer

Azure SQL Database

Answer

Azure Data Lake Storage Gen2

Question 5

A company has an Azure SQL Database and needs to run a weekly data aggregation job that takes several hours. They want to minimize cost and avoid impacting production workload. Which approach should they use?

Accepted Answer

Create a read-only replica and run the aggregation on the replica. Creating a read-only replica allows the weekly aggregation job to run against a separate copy of the database without affecting the production workload. Since the replica is read-only, it incurs additional compute costs only during the aggregation window, and you can scale it down or stop it when not in use, minimizing overall cost.

Answer

Migrate the database to the Hyperscale service tier

Answer

Use Azure Elastic Jobs to run the aggregation during off-peak hours

Answer

Increase the DTU or vCore size of the database to handle the load

Question 6

A financial services company runs a critical application on Azure SQL Managed Instance. They need to ensure that a recent transaction can be recovered within 15 minutes of a user error. Which feature should they configure?

Accepted Answer

Point-in-time restore (PITR). Point-in-time restore (PITR) is the correct feature because it allows you to restore an Azure SQL Managed Instance to a specific point in time within the retention period (default 7 days, configurable up to 35 days). This directly addresses the requirement to recover a recent transaction after a user error, such as an accidental data modification or deletion, within 15 minutes. PITR creates a new database from automated backups, enabling precise recovery to the moment just before the error occurred.

Answer

Geo-restore

Answer

Automatic failover groups

Answer

Long-term retention (LTR)

Question 7

A company needs to create a relational database in Azure that is compatible with existing SQL Server applications and provides built-in high availability without requiring configuration. Which service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that is built on the latest stable version of the Microsoft SQL Server engine, ensuring compatibility with existing SQL Server applications. It provides built-in high availability with a 99.99% SLA through automatic failover groups and zone-redundant configurations, requiring no manual setup or configuration from the user.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MariaDB

Answer

Azure Cosmos DB

Question 8

Which TWO security features are available in Azure SQL Database to help protect data at rest?

Accepted Answer

Transparent data encryption (TDE). Transparent Data Encryption (TDE) is a feature in Azure SQL Database that encrypts data at rest, including backups and transaction log files, using an AES-256 encryption algorithm. It performs real-time I/O encryption and decryption of the data without requiring changes to the application, ensuring that the physical storage media is protected against unauthorized access. Always Encrypted also protects data at rest by encrypting sensitive columns within the database, but it additionally protects data in transit and during query processing by keeping the encryption keys on the client side.

Answer

Firewall rules

Answer

Dynamic data masking

Answer

Azure AD authentication

Question 9

A startup is building a new application and expects rapid growth in data volume. They want a fully managed relational database service with built-in high availability and automatic backups. Which Azure service should they choose?

Accepted Answer

Azure SQL Database. Azure SQL Database is a fully managed Platform-as-a-Service (PaaS) relational database that provides built-in high availability with a 99.99% SLA and automatic backups with point-in-time restore. It meets the startup's need for rapid growth by offering elastic scaling and minimal administrative overhead, unlike Infrastructure-as-a-Service (IaaS) options.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure Database for MySQL

Answer

Azure Cosmos DB

Question 10

A retail company uses Azure SQL Database to store inventory data. They notice excessive blocking and deadlocks during peak hours. Which design change would best reduce these issues?

Accepted Answer

Add appropriate indexes to reduce lock duration. Adding appropriate indexes reduces the number of rows scanned during queries, which shortens lock duration and lowers the chance of blocking and deadlocks. In Azure SQL Database, indexes help queries become more efficient by using seeks instead of scans, minimizing the time locks are held on resources.

Answer

Implement read replicas for reporting queries

Answer

Use the READ UNCOMMITTED isolation level

Answer

Scale up to a higher service objective

Question 11

Your organization uses Azure Cosmos DB for a real-time inventory application. The data includes a container with items that have a `category` property. The operations team frequently queries for all items in a specific category. To optimize query performance and minimize request unit (RU) consumption, you decide to implement a materialized view. Which Azure Cosmos DB feature should you use to achieve this?

Accepted Answer

Materialized views (preview). Option D is correct because Azure Cosmos DB's materialized views (preview) feature allows you to pre-join, aggregate, and transform data from a source container into a separate container optimized for specific query patterns, such as filtering by `category`. This reduces RU consumption by avoiding full scans or expensive cross-partition queries, as the view is pre-computed and indexed according to the target query.

Answer

Partition key design

Answer

Change feed

Answer

Composite indexes

Question 12

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 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

You are a data architect at a global retail company. The company has an Azure Data Lake Storage Gen2 account that stores petabytes of clickstream data. They need to provide near real-time analytics dashboards for regional managers. The data arrives in batches every 5 minutes. Currently, they use Azure Databricks to transform the data and load it into Azure Synapse Analytics, but the dashboards show data that is 30 minutes old. The business requires dashboards to reflect data within 10 minutes of ingestion. You propose a new solution. Which approach should you recommend?

Accepted Answer

Ingest data into Azure Event Hubs, use Azure Stream Analytics to process and output to Power BI for real-time dashboards.. Option C is correct because it uses Azure Event Hubs for low-latency ingestion and Azure Stream Analytics for real-time processing, enabling near real-time dashboards in Power BI with sub-minute latency. This architecture bypasses the batch-oriented pipeline that causes the current 30-minute delay, meeting the 10-minute requirement.

Answer

Keep current pipeline but replace Synapse with Azure Analysis Services for faster query performance.

Answer

Use Azure Data Factory with tumbling window triggers every 5 minutes to load data from Data Lake to Synapse.

Answer

Increase the number of Databricks clusters and use Auto Loader to speed up transformations.

Question 15

A small business wants to migrate their on-premises SQL Server database to Azure. They have limited budget and want to minimize ongoing management overhead. The database is less than 50 GB and is used by a single application with low concurrent users. The application requires compatibility with SQL Server features such as T-SQL, stored procedures, and functions. The business does not require high availability or disaster recovery. Which Azure relational database service should they choose?

Accepted Answer

Azure SQL Database (serverless tier). Azure SQL Database serverless tier is the best choice because it provides a fully managed, PaaS relational database service that supports T-SQL, stored procedures, and functions, while automatically pausing during idle periods to reduce costs. With a database under 50 GB, low concurrency, and no HA/DR requirements, the serverless tier minimizes both management overhead and cost, as it charges only for compute used per second and storage consumed.

Answer

SQL Server on Azure Virtual Machines

Answer

Azure SQL Managed Instance

Answer

Azure Database for MySQL

Question 16

A marketing team needs to analyze customer sentiment from social media posts in real time. The solution must ingest a stream of tweets, perform sentiment analysis using a pre-built AI model, and store the results in a dashboard for immediate visualization. The team has limited coding experience and prefers a low-code/no-code approach. Which combination of Azure services should you recommend?

Accepted Answer

Azure Event Hubs, Azure Stream Analytics, and Power BI. Option C is correct because Azure Event Hubs ingests the real-time tweet stream, Azure Stream Analytics performs sentiment analysis using its built-in machine learning functions (a low-code/no-code approach), and Power BI provides the dashboard for immediate visualization. This combination meets the real-time, low-code requirement without custom coding.

Answer

Azure Event Hubs, Azure Functions, and Azure Cosmos DB

Answer

Azure IoT Hub, Azure Data Factory, and Power BI

Answer

Azure Event Hubs, Azure HDInsight, and Power BI

Question 17

Your organization has a data lake on Azure Data Lake Storage Gen2 containing petabytes of raw clickstream data. Data scientists need to run exploratory analysis using Python and Spark, but they are not experienced with cluster management or infrastructure. The IT team wants to minimize administrative overhead while providing a collaborative notebook environment. Additionally, the solution must integrate with Microsoft Purview for data cataloging and lineage. Which Azure service should you recommend?

Accepted Answer

Azure Databricks. Azure Databricks is the correct choice because it provides a fully managed, collaborative notebook environment optimized for Apache Spark, allowing data scientists to run Python and Spark-based exploratory analysis without managing clusters. It integrates natively with Azure Data Lake Storage Gen2 for accessing petabytes of clickstream data and supports Microsoft Purview for automated data cataloging and lineage tracking, minimizing administrative overhead.

Answer

Azure Data Science Virtual Machine

Answer

Azure Synapse Analytics (Synapse Studio)

Answer

Azure HDInsight (Spark cluster)

Question 18

You are a business analyst at a manufacturing company. The company uses Azure SQL Database to store production data. You need to create a Power BI report that shows real-time machine efficiency. The report must refresh every 5 minutes to show current metrics. You have been granted read-only access to the database. The database is under heavy load from transactional applications, and you want to minimize additional impact. Which approach should you take to create the report?

Accepted Answer

Use Power BI Import mode with incremental refresh policy to load only new data every 5 minutes. B is correct because Power BI Import mode with an incremental refresh policy allows you to load only new or changed data every 5 minutes, minimizing the load on the heavily used Azure SQL Database. This approach avoids repeated full table scans, reduces query impact, and still provides near-real-time metrics for the report.

Answer

Disable the Power BI Query Cache to reduce database load

Answer

Configure Power BI to use DirectQuery mode to ensure real-time data

Answer

Export the data to a CSV file and import it into Power BI daily

Question 19

You are a data architect for a logistics company. The company uses Azure Data Lake Storage Gen2 to store shipment tracking data. The data is ingested from IoT devices on trucks. Each record contains truck ID, timestamp, GPS coordinates, speed, and fuel level. The volume is 5 TB per day. The company wants to build a near-real-time dashboard to monitor truck locations and speeds. They also need to run daily batch analytics to compute fuel efficiency trends. You need to design a solution that minimizes latency for the dashboard and maximizes cost efficiency for batch processing. You plan to use Azure Event Hubs for ingestion. Which approach should you take?

Accepted Answer

Use Azure Stream Analytics to process the stream, output to Power BI for real-time dashboard, and simultaneously output raw data to Azure Data Lake Storage. Use Azure Databricks to process the data lake for batch analytics.. Option D is correct because it separates the real-time and batch processing paths to minimize latency and maximize cost efficiency. Azure Stream Analytics outputs directly to Power BI for near-real-time dashboard updates, while simultaneously writing raw data to Azure Data Lake Storage for cost-effective storage. Azure Databricks then processes the data lake for daily batch analytics, avoiding expensive real-time compute for historical analysis.

Answer

Use Azure Stream Analytics to process the stream and output directly to Azure SQL Database. Use Power BI to query SQL Database for both real-time dashboard and historical analytics.

Answer

Use Azure Event Hubs Capture to store data in Azure Blob Storage, then use Azure Data Factory to transform and load into Azure Synapse Analytics for both dashboard and batch.

Answer

Use Azure Databricks with Structured Streaming to process the stream, write to Delta Lake, and use Delta Lake to serve both real-time and batch queries.

Question 20

You are a data engineer for a financial services company. The company uses Azure Synapse Analytics dedicated SQL pool for its data warehouse. They have a fact table named Transactions that contains 2 billion rows. The table is hash-distributed on the AccountID column. Users run reports that aggregate transaction amounts by date and account type. The reports are slow. Upon investigation, you find that the distribution is highly skewed because a few accounts have millions of transactions. You need to improve query performance without redesigning the entire schema. Which action should you take?

Accepted Answer

Change the distribution key to a column with more unique values, such as TransactionID. The correct answer is A because changing the distribution key to TransactionID, which has far more unique values than AccountID, will eliminate the data skew that is causing performance degradation. In a hash-distributed table, a skewed distribution key leads to some distributions holding a disproportionate amount of data, causing parallel query execution to be bottlenecked by the largest distribution. By using a highly unique column like TransactionID, the data will be evenly distributed across all 60 distributions, enabling balanced parallelism and faster aggregation queries.

Answer

Change the distribution to round-robin

Answer

Create a clustered columnstore index on the table

Answer

Replicate the Transactions table to all distributions