10 free Microsoft DP-300 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 373 questions. Work through them, then open each answer to check your reasoning.
Get all 373 questions (US$39) · Download these 10 as a PDF
Question 1
You have 20 Azure SQL databases provisioned by using the vCore purchasing model.
You plan to create an Azure SQL Database elastic pool and add the 20 databases.
Which three metrics should you use to size the elastic pool to meet the demands of your workload? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
Show answer and explanation
Correct answer: A, C, E
A. total size of all the databases C. number of concurrently peaking databases * peak CPU utilization per database E. total number of databases * average CPU utilization per database To properly size an Azure SQL Database elastic pool, you need three key metrics: A) the total size of all databases to determine storage requirements, C) the number of concurrently peaking databases multiplied by peak CPU utilization per database to understand peak computational demand, and E) the total number of databases multiplied by average CPU utilization per database to understand baseline resource consumption. These three metrics together provide the storage, peak load, and average load dimensions needed for accurate pool sizing.
Why the other options are wrong
- B. Geo-replication support is a feature configuration, not a sizing metric for the elastic pool itself.
- D. While concurrent sessions matter for capacity planning, the specific metric of maximum concurrent sessions for all databases is less relevant than CPU utilization metrics for elastic pool sizing.
Question 2
You have an Azure SQL database that contains a table named factSales. FactSales contains the columns shown in the following table.
FactSales has 6 billion rows and is loaded nightly by using a batch process. You must provide the greatest reduction in space for the database and maximize performance.
Which type of compression provides the greatest space reduction for the database?
Show answer and explanation
Correct answer: D. columnstore archival compression
Columnstore archival compression provides the greatest space reduction for large fact tables in data warehouse scenarios. Given that factSales contains 6 billion rows and is batch-loaded nightly (typical data warehouse pattern), columnstore archival compression is optimal. It combines columnstore storage format with additional compression algorithms, achieving 10x or greater compression ratios compared to other methods. While columnstore compression alone (option C) offers significant benefits, archival compression adds an extra compression layer specifically designed for read-heavy, batch-loaded analytical tables, making it the best choice for maximum space reduction.
Why the other options are wrong
- A. Page compression provides moderate space reduction (typically 50-80%) but is far less effective than columnstore archival compression for large analytical tables with billions of rows.
- B. Row compression is the most basic compression type, offering minimal space reduction (typically 10-20%) and is least suitable for a 6 billion row fact table.
- C. Columnstore compression offers excellent compression ratios but columnstore archival compression builds upon it with additional compression techniques, providing even greater reduction for batch-loaded data warehouses.
Question 3
You have a Microsoft SQL Server 2019 database named DB1 that uses the following database-level and instance-level features.
• Clustered columnstore indexes
• Automatic tuning
• Change tracking
• PolyBase
You plan to migrate DB1 to an Azure SQL database.
What feature should be removed or replaced before DB1 can be migrated?
Show answer and explanation
Correct answer: B. PolyBase
PolyBase must be removed before migration to Azure SQL Database because Azure SQL Database does not support PolyBase functionality. Clustered columnstore indexes, automatic tuning, and change tracking are all supported features in Azure SQL Database, so they can remain in place during migration.
Why the other options are wrong
- A. Clustered columnstore indexes are fully supported in Azure SQL Database.
- C. Change tracking is a supported feature in Azure SQL Database and does not need to be removed.
- D. Automatic tuning is supported in Azure SQL Database and can be migrated without modification.
Question 4
You have a Microsoft SQL Server 2019 instance in an on-premises datacenter. The instance contains a 4-TB database named DB1.
You plan to migrate DB1 to an Azure SQL Database managed instance.
What should you use to minimize downtime and data loss during the migration?
Show answer and explanation
Question 5
You are designing a streaming data solution that will ingest variable volumes of data.
You need to ensure that you can change the partition count after creation.
Which service should you use to ingest the data?
Show answer and explanation
Correct answer: D. Azure Event Hubs Dedicated
Azure Event Hubs Dedicated tier allows you to change the partition count after creation, providing flexibility for variable data volumes. Event Hubs Standard tier has a fixed partition limit and does not allow partition count changes after creation. Stream Analytics and Data Factory are processing and orchestration services, not ingestion services for managing partition configuration.
Why the other options are wrong
- A. Azure Event Hubs Standard does not support changing the partition count after the event hub is created.
- B. Azure Stream Analytics is a stream processing service, not an ingestion service with partition management capabilities.
- C. Azure Data Factory is an orchestration service for data movement and transformation, not a streaming ingestion service with partition configuration options.
Question 6
You have an Azure Synapse Analytics Apache Spark pool named Pool1.
You plan to load JSON files from an Azure Data Lake Storage Gen2 container into the tables in Pool1. The structure and data types vary by file.
You need to load the files into the tables. The solution must maintain the source data types.
What should you do?
Show answer and explanation
Correct answer: A. Load the data by using PySpark.
PySpark is the optimal choice for loading JSON files with variable structures and data types into Spark tables while maintaining source data types. PySpark provides schema inference and flexibility to handle varying JSON structures, and it natively preserves data types during the load process. OPENROWSET requires predefined schemas, Data Factory's Get Metadata is for metadata retrieval only, and Conditional Split is for row-level transformations, not data loading.
Why the other options are wrong
- B. OPENROWSET in a serverless SQL pool requires predefined schemas and cannot effectively handle variable JSON structures and types.
- C. Get Metadata activity in Data Factory retrieves metadata information only and does not load data into tables.
- D. Conditional Split transformation in data flows is used for row-level branching logic, not for loading JSON files with variable schemas into tables.
Question 7
You are designing a date dimension table in an Azure Synapse Analytics dedicated SQL pool. The date dimension table will be used by all the fact tables.
Which distribution type should you recommend to minimize data movement?
Show answer and explanation
Correct answer: B. REPLICATE
REPLICATE distribution type is the best choice for a date dimension table that will be used by all fact tables. Replicating the dimension table to all compute nodes eliminates data movement during joins, as every node has a complete copy of the dimension table. This minimizes data shuffling and optimizes query performance across all joins with fact tables.
Why the other options are wrong
- A. HASH distribution on the date dimension would require data movement when joining with multiple fact tables distributed on different columns.
- C. ROUND_ROBIN distribution is inefficient for dimension tables that are repeatedly joined, as it causes excessive data movement during each join operation.
Question 8
You have an Azure Synapse Analytics workspace named WS1 that contains an Apache Spark pool named Pool1.
You plan to create a database named DB1 in Pool1.
You need to ensure that when tables are created in DB1, the tables are available automatically as external tables to the built-in serverless SQL pool.
Which format should you use for the tables in DB1?
Show answer and explanation
Correct answer: C. Parquet
Parquet is the format Microsoft specifies for Spark tables that must appear automatically as external tables in the built-in serverless SQL pool. With Synapse shared metadata, databases and tables created in a Spark pool sync to serverless SQL, and Parquet's columnar storage and type fidelity make DB1 queryable there with no extra definition work.
Why the other options are wrong
- A. JSON-backed Spark tables are not synchronized to serverless SQL, so they would not appear as external tables.
- B. CSV is a row-based text format with weaker typing and performance, so it is not the right choice here.
- D. ORC-backed Spark tables are not exposed to serverless SQL through Synapse shared metadata.
Question 9
You are designing an anomaly detection solution for streaming data from an Azure IoT hub. The solution must meet the following requirements:
• Send the output to an Azure Synapse.
• Identify spikes and dips in time series data.
• Minimize development and configuration effort.
Which should you include in the solution?
Show answer and explanation
Correct answer: C. Azure Stream Analytics
Azure Stream Analytics is the ideal choice for this solution. It has built-in anomaly detection capabilities for identifying spikes and dips in time series data, can send output directly to Azure Synapse, and requires minimal development effort with its declarative SQL-like query language. It is purpose-built for real-time streaming scenarios with anomaly detection functionality.
Why the other options are wrong
- A. Azure SQL Database is not suitable for processing streaming data from IoT hubs and lacks built-in anomaly detection capabilities for time series data.
- B. Azure Databricks, while powerful, requires more development and configuration effort compared to Stream Analytics for this specific anomaly detection scenario.
Question 10
You plan to build a structured streaming solution in Azure Databricks. The solution will count new events in five-minute intervals and report only events that arrive during the interval.
The output will be sent to a Delta Lake table.
Which output mode should you use?
Show answer and explanation
Correct answer: B. append
The 'append' mode is correct for this scenario because it writes only new rows that arrived in the current interval to the output sink. Since the requirement specifies reporting only events that arrive during each five-minute interval without updating previous results, append mode is the appropriate choice that minimizes data redundancy.
Why the other options are wrong
- A. Complete mode would rewrite the entire result set every interval, which is inefficient for this use case and contradicts the requirement to report only new events in each interval.
- C. Update mode is designed for stateful operations where existing rows need to be modified, but this scenario requires only appending new interval results.
That was 10 of 373.
The full Microsoft DP-300 pack has all 373 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.